MySQL

From DreamHost
Revision as of 05:32, 12 January 2011 by Rlparker (Talk | contribs)

Jump to: navigation, search

Also see http://wiki.dreamhost.com/Migrate_MySQL

MySQL is a multiuser database server released under the GNU General Public License (GPL). MySQL is now owned by Oracle (who purchased it from Sun, and Sun purchased MySQL from MySQL Ag. originally).


All new databases created with DreamHost are using MySQL 5.0. Older, existing databases are still running MySQL 4.1 (for now, though you can email support and request they upgrade your databases).

DreamHost uses phpMyAdmin, an open source tool written in PHP intended to handle the administration of MySQL over the Web.

Contents

Creating a MySQL Database with DreamHost

  • At the bottom of the page there is a form where you can input the information to create a new database.
    Mysql-newdbase.png
    • Database Name - Give your database a name. The name may contain only letters, decimal numbers, and the characters $ and _ and must be unique across our entire system. (consider maybe naming your db yourwebid_dbname)
    • Use Hostname - You will access your database via a subdomain. Select an already existing hostname, or create a new one. You can use any of your subdomains to access any of your databases.
    • First User - Select the initial user for the database, or create a new user.
    • New Username - When creating a new user, enter the unique username you will use to access your MySQL database (follows the same requirements as Database Name above).
    • New Password - The password you will use to login to MySQL.
    • Database Comment - Used to describe the database in your Account Panel.
  • Click Add New Database Now!

Connecting to Your Database

Connect to Your MySQL Database from Your Shell Account

  • First, login to your DreamHost account: 
 ssh -l yourlogin www.yourdomain.com
  • Then, login to your MySQL database. You can copy/paste the exact syntax for your database in the Account Panel under "Goodies" / "MySQL." 
 mysql -u yourdblogin -p -h yourdbdomain.yourdomain.com yourdb
  • You can also include your password in the command (notice that there is no space after "-p"). 
 mysql -u yourdblogin -pyourdbpassword -h yourdbdomain.yourdomain.com yourdb

You cannot use "localhost" to connect to your database, as DreamHost's MySQL servers are separate from the regular Web site servers.

Connect to Your MySQL Database using DreamHost's PhpMyAdmin at your own domain

Once your own database's hostname is in the DNS system, you can also access your database via PhpMyAdmin by going to that database's hostname on the web. For example, if your MySQL hostname is mysql.yourdomain.tld, point your browser to http://mysql.yourdomain.tld. You will then be prompted for the MySQL username and password. Note however that this method IS NOT SECURE! Your database username and password will be sent in clear text making them very easy to sniff. You should really use the previous method.

Connect to Your MySQL Database from Third-Party Programs

To connect to your MySQL Database remotely, using a third-party program like MySQL WorkBench, you must first grant your local machine rights to connect to the database server. This must be done for all MySQL user accounts you plan on logging in with from your local machine.

  • Login to your Dreamhost Control Panel
  • Select the MySQL Databases link under the Goodies section, or in the Toolbox on the left
  • Under the Database(s) on this server section, find the desired database and click the username you wish to grant access to.
  • Under the section titled Allowable Hosts, type in the domain or subdomain your local machine identifies itself as on the public internet. Follow the formatting conventions described on the web page. You will need either your public IP address or your public originating domain/subdomain (like rr.com, mindspring.com, comcast.com, etc).

To find your public IP address, go to What is My IP? (your IP is also displayed just below in the hosts input box after 'Your current computer is:'. Just cut and paste below the default). Note that enabling %.example.com does not enable the hostname example.com by itself.

  • Click the Modify [username] now! button.
  • Launch your third-party program, enter database and login information, and connect.

Information on connecting using Microsoft Access specifically is here.

How to use Visual Basic to connect to MySQL

How a client side script can connect to MySQL - Ex. Java Applet

Connect to Your MySQL Database from Third-Party Programs via a Secure SSH Tunnel

Using Putty v0.60

Using PuTTy, it is possible to access your MySQL database via a secure SSH tunnel. This method is peferred over insecure methods as it provides point-to-point encryption and does not expose your MySQL account to potential hacks from allowed networks.

To set up a tunnel in PuTTy, your account must first have SSH enabled. To do this...

  • go to your Dreamhost Account Control Panel
  • edit the user to whom you wish to grant SSH access
  • check the Enable ssh/telnet checkbox
  • Make sure /bin/bash is your shell type
  • click Save Changes

Next, download and launch PuTTy.

  • In the category tree on the left, click Tunnels near the bottom
  • Enter 3306 in the source port
  • Enter your mysql database address and port in the destination field (e.g. yourdbdomain.yourdomain.com:3306)
  • Use all other defaults (local, auto) and click the Add button
  • Click Session in the category tree
  • Enter your shell account address in the Host Name field (e.g. foo.dreamhost.com
  • Ensure that port 22 and SSH are specified under prototcol
  • Give your session a name and click the Save button.
  • Now log into your shell account using your newly created session
  • Minimize the window

Next, open the third-party tool. We'll use the MySQL Adminstrator tool as an example.

  • enter localhost under Server Host (note: entering your actual address (e.g. yourdbdomain.yourdomain.com) will not work here. You must use localhost)
  • enter 3306 as the port
  • enter your db username and password
  • click OK

You should be in!

Notes:

  • Your PuTTY session must be active and you must be logged in for the tunnel to be active
  • If you are running a local MySQL db, you can specify a different port for your tunnel. Otherwise, you will not be able to access your local mysql connection while the tunnel is active. To do this: 1) specify a different number (e.g. 3307) in the source port field under Tunnels in PuTTY. 2) Use the same number as your port in the MySQL Adminstrator Port field. Note that your destination port must remain 3306. The idea is that you're sending data to port 3307 on your end, the data is sent through the tunnel, and then is shunted to port 3306 on Dreamhost's end.

Using SSH

Set up the port forwarding once and for all in your ~/.ssh/config: 

Host your_nickname_for_your_host
    HostName        your_dh_host_name.dreamhost.com
    User            your_shell_account_user_name
    LocalForward    3307 your_full_db_host_name:3306

To connect just use: 

ssh your_nickname_for_your_host
mysql -h 127.0.0.1 -P 3307 -u db_user_name -p

Or single command line: 

ssh -4fNg -L 3307:your_full_db_host_name:3306 your_shell_account_user_name@your_dh_host_name.dreamhost.com

Then connect with mysql as above.

Gotchas:

Despite adding localhost as a valid host for your user in Goodies > Manage MySQL you may encounter this error message: 

ERROR 1045 (28000): Access denied for user 'db_user_name'@'localhost' (using password: YES)

Use 127.0.0.1 instead of localhost (it's blocked by DH). (Thanks Shiva for the solution!)

Also if connecting via command line, you also require the mysql -P flag and specify port 3307 eg: 

 mysql -u username -p -h 127.0.0.1 -P 3307

Using SQLyog

You may want to also check into the free version of SQLyog. The screenshots below show how easy it is to setup:

SQLyog2.jpg

SQLyog1.jpg

You may also try out Navicat MySQL GUI Free version. You can find a tutorial on SSH and HTTP remote connection at: SSH connection tutorial.

Using Sequel Pro

On MacOS X, you can use the open source Sequel Pro software to connect to a Dreamhost MySQL database.

To connect with your DreamHost database using Sequel Pro, use the Standard connection tab and enter the following:

Name: [optional]

Host: [your MySQL host name: mysql.domain.com]

Username: [your database user name]

Password: [your database user password]

Database: [optional]

Port: [3306]

Click Connect

That's it! Once you are connected, select the database from the drop-down menu on the top left.

Problems logging in?

If you get an error message regarding username/password repeatedly - even though you're sure you've typed in the correct username/password - then probably you have to simply delete your cache.

See Also

MySQL Usage

You can monitor the usage on your account via the MySQL Usage section of the panel.

"Conueries" is a term used by DreamHost to measure MySQL database usage. The word is a combination of "connections" and "queries." Basically, the number of MySQL queries you have in a month, plus 25 times the number of connections you have is your total "conueries" for a month.

There are no longer any hard limits on conueries for any of DreamHost's plans. DreamHost reserve the right to have ultra-heavy users curtail their database usage (or switch to a dedicated server), but unless you've got a crazy (and crazily inefficient) database-driven site that gets thousands and thousands of visitors a day, don't sweat it!

MySQL limitations due to shared hosting

On Dreamhost shared accounts, some of the SQL commands listed in the MySQL documentation link below or in references such as the O'Reilly book Managing & Using MySQL by Reese, et al. are not available for scripting nor in phpMyAdmin.

Their functionality is available through the Dreamhost Control Panel described in the Creating a MySQL Database above.

Included are:

  • CREATE DATABASE only works from the shell if you previously created a database with the specified same name using your Dreamhost Panel and then dropped it at some point (in other words you can (and should) use --drop-database with mysqldump when backing up a database)
  • GRANT
  • REVOKE

The following table summarizes the privileges a MySQL user at DreamHost may have, as compared to all available privileges.

Privilege Meaning Availability
ALL [PRIVILEGES] Grant all privileges at specified access level except GRANT OPTION NO
ALTER Enable use of ALTER TABLE YES
ALTER ROUTINE Enable stored routines to be altered or dropped YES
CREATE Enable database and table creation YES
CREATE ROUTINE Enable stored routine creation YES
CREATE TEMPORARY TABLES Enable use of CREATE TEMPORARY TABLE YES
CREATE USER Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES NO
CREATE VIEW Enable views to be created or altered YES
DELETE Enable use of DELETE YES
DROP Enable databases, tables, and views to be dropped YES
EVENT Enable use of events for the Event Scheduler NO
EXECUTE Enable the user to execute stored routines YES
FILE Enable the user to cause the server to read or write files (unnecessary, if you get this error you are likely using "LOAD DATA INFILE" and should use "LOAD DATA LOCAL INFILE" instead) NO
GRANT OPTION Enable privileges to be granted to or removed from other accounts NO
INDEX Enable indexes to be created or dropped YES
INSERT Enable use of INSERT YES
LOCK TABLES Enable use of LOCK TABLES on tables for which you have the SELECT privilege YES
PROCESS Enable the user to see all processes with SHOW PROCESSLIST (shows only your specific mysql users processes) YES
REFERENCES Not implemented NO
RELOAD Enable use of FLUSH operations NO
REPLICATION CLIENT Enable the user to ask where master or slave servers are NO
REPLICATION SLAVE Enable replication slaves to read binary log events from the master NO
SELECT Enable use of SELECT YES
SHOW DATABASES Enable SHOW DATABASES to show all databases NO
SHOW VIEW Enable use of SHOW CREATE VIEW YES
SHUTDOWN Enable use of mysqladmin shutdown NO
SUPER Enable use of CHANGE MASTER TO, KILL, PURGE BINARY LOGS, and SET GLOBAL statements, the mysqladmin debug command; allows you to connect (once) even if max_connections is reached NO
TRIGGER Enable triggers to be created or dropped YES
UPDATE Enable use of UPDATE YES
USAGE Synonym for “no privileges” NO

Note on Dropping (Deleting) a Database

A database created on DreamHost that is deleted via the control panel (Control Panel -> Goodies -> MySQL Databases) is moved to the recycle bin shown on the same page, and may not be deleted for up to 30 days. This is to allow easy restoration of databases that were mistakenly deleted. If you wish for the database to be deleted forever, you may login to mysql via the command line and issue a "drop database" statement to expedite the process.

External Links

Retrieved from "http://wiki.dreamhost.com/index.php?title=MySQL&oldid=28011"
Personal tools