- 1 Overview
- 2 All about DreamHost's MySQL offerings
- 3 How to manage DreamHost MySQL databases
- 4 How to connect to your database
- 4.1 Connecting from a shell account
- 4.2 Connecting to your MySQL database using DreamHost's PhpMyAdmin at your own domain
- 4.3 Connecting to your MySQL database using a third-party program
- 4.4 If you're using a third-party program via a secure SSH tunnel to connect
- 5 If you're having problems logging in
- 6 Error messages
- 7 Additional MySQL resource information
- 8 See Also
MySQL is a relational database management system (RDBMS) released under the GNU General Public License (GPL). It is one of the most widely used open-source database systems, and is compatible with a multitude of website applications. Other databases offered by DreamHost include SQLite and MongoDB (on DreamHost VPS and dedicated servers only), however they may not be supported by your desired website applications.
This wiki describes everything you need to know on how you can create, add, manage, access, restore, and much more for all of your DreamHost MySQL databases.
You can make changes to your MySQL databases on the (Panel > 'Goodies' > 'MySQL Databases').
All about DreamHost's MySQL offerings
All current DreamHost shared, MySQL VPS and dedicated servers provide MySQL version 5.1.56 (as of 2/17/2015). If a newer version is required, please contact support to be provided with other options.
The most common storage engines are provided with all DreamHost managed MySQL products. Here is a list of the available storage engines:
mysql> show engines; +------------+---------+------------------------------------------------------------+--------------+------+------------+ | Engine | Support | Comment | Transactions | XA | Savepoints | +------------+---------+------------------------------------------------------------+--------------+------+------------+ | CSV | YES | CSV storage engine | NO | NO | NO | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | NO | NO | NO | | InnoDB | YES | Supports transactions, row-level locking, and foreign keys | YES | YES | YES | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | +------------+---------+------------------------------------------------------------+--------------+------+------------+
Customers familiar with running their own MySQL server and who wish to use a newer version, may be interested in the trying out DreamCompute which offers multiple newer operating systems to choose from which have newer versions of MySQL available for install. It is also technically possible to install your own MySQL server on a DreamHost webserver VPS with an admin user enabled. Support for installing, managing or maintaining backups for either option is not provided by DreamHost and at the customers own risk.
Comparison of MySQL products
DreamHost shared and VPS products for MySQL run on specialized hardware to optimize disk i/o and therefore the speeds in which they return your query. These are networked products which run independent of the webserver hardware. Dedicated servers offer MySQL that is optimized for other use cases where the webserver and MySQL server co-existing makes more sense.
|Product||Description||Local/Networked||Suitable For Hosting Needs|
|Shared MySQL||Included with a shared hosting plan, and with a Webserver VPS.||Networked||Standard mysql for optimized and shared hosting appropriate databases.|
|MySQL VPS||An additional service similar to a webserver VPS that only runs the MySQL server and no other services.||Networked||High performance MySQL for customers who need higher uptime or have outgrown shared MySQL.|
|Dedicated MySQL||A dedicated server runs a MySQL server locally along with the other web services.||Local||A dedicated server is ideal for software that sends larges amounts of data between web applications and MySQL, as there is no network in between to limit throughput.|
DreamHost's daily backups
All DreamHost MySQL products backup daily. The backups start at midnight and complete after every single database has been done. It is HIGHLY RECOMMENDED to make and keep your own offsite backups of all databases to supplement the short term backups DreamHost creates. DreamHost makes no guarantee that backups will be available.
To backup your entire account (ftp users, mail users and databases) check out our account backup feature. This is available in the (Panel > 'Billing & Account' > 'Backup Your Account') section of the DreamHost panel. The backups created can be downloaded to another offsite location for safe keeping.
Restoring a database
There are generally 5 days of backups available. Please note that this is not guaranteed so you should always make your own personal backups just in case there are none available to restore in your panel.
- Navigate to the (Panel > 'Goodies' > 'MySQL Databases') page.
- Click the Restore DB button to the right of your database.
- Choose what you would like restored:
- Restore which backup? - Choose any available backup from the dropdown menu
- Tables to restore - If you want the entire database restored, leave this as *
- When restoring - You have 4 options of how your tables will be restored.
- Options 1 & 2 - The first two options restore your tables but the names are changed. This way, the restored files do NOT take the place of the current files. You'll either need to manually change the table names or adjust your site's config file to view the restored database
- Options 3 & 4 - The last two options immediately replace your database tables. The current tables are renamed. You most often want one of the two last options
- Click the Restore my backup now! button.
You'll see a success messages notifying you that your database will be restored within 10 minutes.
MySQL database disk usage
MySQL servers have very fast, high RPM and smaller disks deployed in them, to provide the speed boost required to make MySQL snappy. Due to this, there is limited space available on the MySQL servers and we encourage customers to develop as small and efficient databases as possible. For specifics on what size databases are allowed on a specific DreamHost MySQL product, please review the Unlimited Policy and contact support if you have further questions.
How to manage DreamHost MySQL databases
Viewing a MySQL database
- Once you have logged into the DreamHost Control Panel, click the MySQL Databases tab in the left-hand panel under Goodies.
- The MySQL Databases page appears.
Creating a MySQL database
To create a database, scroll to the bottom of the MySQL Databases page and fill out the following fields.
When all required fields have been entered, click Add new database now! to begin the database creation process.
Managing existing MySQL databases
The following is a description of the buttons found on the Goodies -> MySQL Databases page for managing existing databases.
Hostnames are displayed at the top, with helpful links for phpMyAdmin access. Databases are listed below the hostnames.
|Important:||The red text in the items below indicate possible destructive commands. Please be careful when using these commands.|
Managing MySQL users, allowable hosts, and privileges
The following is a description of the page displayed when clicking on an existing MySQL username in the "Users with Access" column.
- The MySQL User page appears.
|Important:||The red text in the items below indicate possible destructive commands. Please be careful when using these commands.|
When all desired changes have been made, click Modify USERNAME now! to confirm.
Note: Changes made may take up to 5 minutes to take affect.
How to connect to your database
Connecting from a shell account
- Log in to your DreamHost account.
ssh -l yourlogin yourdomain.com
- Log in to your MySQL database. You can find the necessary hostname, username, password and database names in the Control Panel under Goodies > MySQL Databases.
mysql -h yourdbdomain.yourdomain.com -u yourdbuser -p yourdb
- You can also include your password in the command (do not include a space after "-p").
mysql -h yourdbdomain.yourdomain.com -u yourdbuser -pyourdbpassword yourdb
|Important:||You cannot use "localhost" to connect to your database, as DreamHost's MySQL servers are separate from the regular web site servers.|
Connecting to your MySQL database using DreamHost's PhpMyAdmin at your own domain
Please see PhpMyAdmin.
Connecting to your MySQL database using a third-party program
To connect to your MySQL Database remotely using a third-party program like MySQL WorkBench, you must first give your local machine permission 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.
To grant permission for MySQL user accounts:
- Log in 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.
- Under the section titled Allowable Hosts, enter the domain or subdomain your local machine identifies itself as on the public internet. Follow the formatting conventions described on the web page. You must include either your public IP address or your public originating domain/subdomain (like rr.com, mindspring.com, comcast.com, and so on).
Tip: To find your public IP address, go to What is My IP?. Your IP address is also displayed just below in the hosts input box after 'Your current computer is:' You can copy and paste this text as needed.
- Click Modify [username] now!
- Launch your third-party program, enter the database and login information, and then connect.
You can find additional information on:
If you're using a third-party program via a secure SSH tunnel to connect
Connecting using Putty v0.63
You can use PuTTy to access your MySQL database via a secure SSH tunnel. This method is preferred over less secure methods as it provides point-to-point encryption and does not expose your MySQL account to potential hacks from allowed networks.
The following three phases set up a secure SSH tunnel:
Enabling SSH on your account
- Go to your Dreamhost Account Control Panel.
- Edit the user you wish to grant SSH access.
- Under "User Type:", choose "Shell user".
- Make sure /bin/bash is your shell type.
- Click Save Changes.
- Download and launch PuTTy.
- In the category tree on the left, click Connection:SSH:Tunnels near the bottom.
- Enter 3306 in the source port.
- Enter your mysql database address and port in the destination field (e.g., mysql.yourdomain.com:3306).
- Use all other defaults (local, auto) and click Add.
- Click Session in the category tree.
- Enter your shell account address in the Host Name field (e.g., psxxxxxx.dreamhostps.com).
- Ensure that port 22 and SSH are specified under prototcol.
- Give your session a name and click Save.
- Log into your shell account using your newly created session.
- Minimize the window.
Using the third-party tool
- Open the third-party tool, such as MySQL WorkBench.
- Enter localhost under Server Host.
- Note: Entering your actual address (e.g., yourdbdomain.yourdomain.com) does not work here. You must use localhost.
- Enter 3306 as the port.
- Enter your db username and password.
- Click OK.
You should be in!
Connecting using SSH
If you are running a local MySQL server on port 3306, you will need to set up port forwarding in your ~/.ssh/config, as follows:
- Enter the following settings:
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
- Enter the following to connect:
ssh your_nickname_for_your_host mysql -h 127.0.0.1 -P 3307 -u db_user_name -p
- Optionally, you can use the following single command line:
ssh -4fNg -L 3307:your_full_db_host_name:3306 your_shell_account_user_name@your_dh_host_name.dreamhost.com
- Connect with mysql as above.
Connecting using SQLyog
Setting up Webyog
The following are important set up instructions and screenshots from the free version of Webyog:
- Make sure you use the correct username and allowed hosts within the Goodies > Manage MySQL panel.
- Download and install Webyog.
- Enter the information under the MySQL tab.
- Enter the information under the SSH tab.
- Click Test Connection...
Other SQLyog tools
You can also try out the free version of the Navicat MySQL GUI.
Connecting using Sequel Pro (Mac OSX)
If you're using Apple Mac OSX, you can 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: 
- Click Connect.
Once you are connected, select the database from the drop-down menu on the top left.
A more secure method is to use the SSH tab when creating the connection/favourite. It will require you to first configure a user for SSH access under Users -> Manage Users in your Dreamhost panel. With these details in hand the following additional options can be configured:
- SSH Host: [SSH host name]
- SSH User: [Username of an SSH enabled user]
- SSH Password: [Password for the account]
- SSH Port: 
If you're having problems logging in
- If you receive numerous username/password error messages, and you're certain that you keyed them correctly, then try deleting the cache.
- Make sure that your current IP address is added to the database's allowable hosts. You can manage this setting in the panel under Goodies > MySQL > Database Username > Allowable hosts.
Please see phpMyAdmin - Error Messages section.
"The used command is not allowed with this MySQL version"
This error may occur when using the "LOAD DATA LOCAL INFILE" statement on Ubuntu 12.04 webservers. A change in the mysql library from Debian to Ubuntu requires code changes to work around this error and allow your statement to execute.
- mysql command line
- Add the --local-infile flag to your mysql connection statement, such as:
mysql --local-infile -h HOSTNAME -u USERNAME -pPASSWORD DATABASENAME
- PHP mysql_connect()
- Add 2 extra params to mysql_connect, a false for "new link" and then a client flag of 128 or CLIENT_LOCAL_FILES:
mysql_connect('HOSTNAME','USERNAME','PASSWORD', false, 128);
- PHP mysqli_connect()
- Initialize mysqli first via mysqli_init, and use mysqli_options() to specify local infile as true before connecting to the mysql server:
$link = mysqli_init(); mysqli_options($link, MYSQLI_OPT_LOCAL_INFILE, true); mysqli_real_connect($link, 'HOSTNAME','USERNAME','PASSWORD','DATABASENAME');
- PERL DBI
- The mysql_local_infile param can be added to your data source name variable, or directly in DBI->connect:
$dbh = DBI->connect("DBI:mysql:database=DATABASENAME;host=HOSTNAME;mysql_local_infile=1", "USERNAME", "PASSWORD");
Additional MySQL resource information
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 instead through the Dreamhost Control Panel described in the Creating a MySQL Database with DreamHost 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. You 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|
If you want to delete or drop a database
- You can delete a database created on DreamHost through the panel (Control Panel > Goodies > MySQL Databases).
- Deleted Databases are moved to the recycle bin shown on the same page, and are permanently deleted after 30 days. This allows for easy restoration of databases that are mistakenly deleted.
- If you wish to permanently delete a database, you can log into mysql using the command and issue a "drop database" statement to expedite the process.
- Backup MySQL
- Restore SQL backup
- Migrate MySQL – How to move databases from one host to another (or from one database to another).
- MySQL and PHP
- Accessing your database before DNS change