Also see Migrate MySQL
- 1 Versions in use at DreamHost:
- 2 Creating a MySQL Database with DreamHost
- 3 Connecting to Your Database
- 3.1 Connect to your MySQL database from your shell account
- 3.2 Connect to your MySQL database using DreamHost's PhpMyAdmin at your own domain
- 3.3 Connect to your MySQL database using third-party programs
- 3.4 Connect to your MySQL database using third-party programs via a secure SSH tunnel
- 4 Problems logging in?
- 5 Error Messages
- 6 See Also
Versions in use at DreamHost:
- All new databases created with DreamHost use MySQL 5.1.
Creating a MySQL Database with DreamHost
To create a MySQL database with DreamHost:
- Log into the Account Control Panel.
- Select the Goodies tab.
- Click the MySQL link.
- Enter the following information on the form at the bottom of the page to create a new database:
- Database Name - Give your database a name. The name must contain only letters, decimal numbers, and the characters $ and _ and must be unique across our entire system (consider naming your db yourwebid_dbname).
- Use Hostname - You 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
- Log in to your DreamHost account.
ssh -l yourlogin www.yourdomain.com
- Log in to your MySQL database. You can copy/paste the exact syntax for your database from the Account Panel under Goodies > MySQL.
mysql -u yourdblogin -p -h yourdbdomain.yourdomain.com yourdb
- You can also include your password in the command (do not include a space after "-p").
mysql -u yourdblogin -pyourdbpassword -h yourdbdomain.yourdomain.com yourdb
Connect to your MySQL database using DreamHost's PhpMyAdmin at your own domain
Once your 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.
In the following example using the MySQL hostname mysql.yourdomain.tld (replace with your actual hostname):
- Open your browser and enter the MySQL hostname address, such as http://mysql.yourdomain.tld
- Enter the MySQL username and password when prompted
Connect to your MySQL database using third-party programs
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).
- Click Modify [username] now!
- Launch your third-party program, enter the database and login information, and then connect.
You can find additional information on:
Connect to your MySQL database using third-party programs via a secure SSH tunnel
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.
- Check the Enable ssh/telnet checkbox.
- 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.
- 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) 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!
To set up the port forwarding in your ~/.ssh/config:
- 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.
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.
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: 
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.
PhpMyAdmin: export.php: Missing parameter: what (FAQ 2.8)
If you receive this error in phpmyadmin while trying to make a record change, list the contents of a table, or list lots of tables, it generally will indicate that your /tmp directory is full on the webserver. If you are on a webserver VPS, you can check in ssh with the "df -h" command to see if the /tmp partition is at 100%. To clear it, you could restart your webserver VPS, or contact support to have it investigated or enlarged.
PHP: Cannot load mysql extension. Please check your configuration.
All of our PHP versions by default contain the mysql extension, and seeing this error message generally indicates that our default php settings have been changed. A few things you can check:
- A custom php enabled in .htaccess for that domain. You can try turning it off to see if it changes the sites behavior.
- A custom phprc or php.ini enabled via the ~/.php/5.VERSION directory that perhaps is missing the extension load line.
- If you can find nothing else, please contact support in case it is indeed the rare case when the webserver itself has missing php.ini files.
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");
- 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
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|
Notes on Deleting (or Dropping) 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.