Also see Migrate MySQL
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).
- 1 Creating a MySQL Database with DreamHost
- 2 Connecting to Your Database
- 2.1 Connect to Your MySQL Database from Your Shell Account
- 2.2 Connect to Your MySQL Database using DreamHost's PhpMyAdmin at your own domain
- 2.3 Connect to Your MySQL Database from Third-Party Programs
- 2.4 Connect to Your MySQL Database from Third-Party Programs via a Secure SSH Tunnel
- 3 Problems logging in?
- 4 See Also
- 5 MySQL Usage
- 6 MySQL limitations due to shared hosting
- 7 Note on Dropping (Deleting) a Database
- 8 External Links
Creating a MySQL Database with DreamHost
- Log into the Account Control Panel, select the Goodies tab, and click on the MySQL link.
- At the bottom of the page there is a form where you can input the information to create a new database.
- 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.
- 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).
Hint: 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: enabling %.example.com does not enable the hostname example.com by itself.
Note: if you are being denied with an error like "access denied to firstname.lastname@example.org" try adding a wildcard to the end of your ip (xxx.xxx.xxx.xxx%)
- 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.
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!
- 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.
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.
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
You may want to also check into the free version of SQLyog. The screenshots below show how easy it is to setup:
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:
Host: [your MySQL host name: mysql.domain.com]
Username: [your database user name]
Password: [your database user password]
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. Also, make sure that your current IP address is added to the database's allowable hosts. This is in panel Goodies -> MySQL -> Database Username -> Allowable hosts
- Backup MySQL and Restore SQL backup
- Migrate MySQL databases from one host to another (or from one Database to another).
- MySQL and PHP
- Character sets in MySQL version 4.1
- MyISAM versus InnoDB tables
- Upgrading from MySQL 4.1 to 5.0 - What might break
- DNS - Accessing your Database before DNS change
"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 DreamHost PS MySQL), 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!
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.
- 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)
The following table summarizes the privileges a MySQL user at DreamHost may have, as compared to all available privileges.
|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 (Only if you are the user@host DEFINER that created the routine)||YES|
|CREATE||Enable database and table creation (Only tables can be created, Databases must be created in the control panel)||YES|
|CREATE ROUTINE||Enable stored routine creation (Cannot set DEFINER as non-root user, please remove DEFINER='user'@'host' combo to create routines)||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|
|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 (Only shows those databases you have access to)||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 (Cannot set DEFINER as non-root user, please remove DEFINER='user'@'host' combo to create trigger)||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.