MySQL

Also see Migrate MySQL

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

=Versions in use at DreamHost:=
 * All new databases created with DreamHost use MySQL 5.1.

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

= Creating a MySQL Database with DreamHost =

To create a MySQL database with DreamHost:


 * 1) Log into the Account Control Panel.
 * 2) Select the Goodies tab.
 * 3) Click the MySQL link.
 * The Create a new MySQL database form appears.
 * [[Image:Mysql-newdbase.png]]
 * 1) Enter the following information on the form at the bottom of the page to create a new database:
 * 2) * 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).
 * 3) *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.
 * 4) *First User - Select the initial user for the database, or create a new user.
 * 5) *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).
 * 6) *New Password - The password you will use to login to MySQL.
 * 7) *Database Comment - Used to describe the database in your Account Panel.
 * 8) Click Add new database now!

= Connecting to Your Database =

Connect to your MySQL database from your shell account

 * 1) Log in to your DreamHost account.
 * ssh -l yourlogin www.yourdomain.com
 * 1) 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
 * 1) 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):


 * 1) Open your browser and enter the MySQL hostname address, such as  http://mysql.yourdomain.tld 
 * 2) 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:


 * How to use Microsoft Access to connect to MySQL, and
 * How to use Visual Basic to connect to MySQL.

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
 * Launching PuTTy
 * Using the third-party tool

Enabling SSH on your account

 * 1) Go to your Dreamhost Account Control Panel.
 * 2) Edit the user you wish to grant SSH access.
 * 3) Check the Enable ssh/telnet checkbox.
 * 4) Make sure /bin/bash is your shell type.
 * 5) Click Save Changes.

Launching PuTTy

 * 1) Download and launch PuTTy.
 * 2) In the category tree on the left, click Connection:SSH:Tunnels near the bottom.
 * 3) Enter 3306 in the source port.
 * 4) Enter your mysql database address and port in the destination field (e.g., mysql.yourdomain.com:3306).
 * 5) Use all other defaults (local, auto) and click Add.
 * 6) Click Session in the category tree.
 * 7) Enter your shell account address in the Host Name field (e.g., psxxxxxx.dreamhostps.com).
 * 8) Ensure that port 22 and SSH are specified under prototcol.
 * 9) Give your session a name and click Save.
 * 10) Log into your shell account using your newly created session.
 * 11) Minimize the window.

Using the third-party tool

 * 1) Open the third-party tool.
 * We'll use the MySQL Adminstrator tool as an example.
 * 1) Enter localhost under Server Host.
 * Note: Entering your actual address (e.g., yourdbdomain.yourdomain.com) does not work here. You must use localhost.
 * 1) Enter 3306 as the port.
 * 2) Enter your db username and password.
 * 3) Click OK.

You should be in!

Using SSH
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</tt> as above. </ol>

Setting up Webyog
The following are important set up instructions and screenshots from the free version of Webyog:


 * 1) Make sure you use the correct username and allowed hosts within the Goodies > Manage MySQL panel.
 * 2) Download and install Webyog.
 * The Connect to MySQL Host dialog box appears when you open Webyog.
 * [[Image:sqlYog2.png]]
 * 1) Enter the information under the MySQL tab.
 * [[Image:sqlYog1.png]]
 * 1) Enter the information under the SSH tab.
 * 2) 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:


 * 1) Use the Standard connection tab and enter the following:
 * 2) *Name: [optional]
 * 3) *Host: [your MySQL host name: mysql.domain.com]
 * 4) *Username: [your database user name]
 * 5) *Password: [your database user password]
 * 6) *Database: [optional]
 * 7) *Port: [3306]
 * 8) 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: [22]

= 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.

= See Also =
 * 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

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

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 instead through the Dreamhost Control Panel described in the Creating a MySQL Database with DreamHost above.

These include:


 * 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.
 * GRANT
 * REVOKE

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

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.