MySQL

From DreamHost
Jump to: navigation, search

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.
    Mysql-newdbase.png
  4. 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.
  5. 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
  2. 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
  3. 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
Important icon.png Important: 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 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
Important icon.png Warning: This method is not secure! Your database username and password is sent in clear text making them very easy to detect. We recommend using the previous method.


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:

  1. Log in to your Dreamhost Control Panel.
  2. Select the MySQL Databases link under the Goodies section.
  3. Under the Database(s) on this server section, find the desired database and click the username you wish to grant access.
  4. 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).
    Lightbulb wiki.png 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.
    Note2 icon.png Notes:
    • Enabling %.example.com does not enable the hostname example.com by itself.
    • If you are being denied with an error like "access denied to username@xxx.xxx.xxx.xxx-ip.provider.info" try adding a wildcard to the end of your ip (such as xxx.xxx.xxx.xxx%).
  5. Click Modify [username] now!
  6. 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

  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.
  2. Enter localhost under Server Host.
    Note: Entering your actual address (e.g., yourdbdomain.yourdomain.com) does not work here. You must use localhost.
  3. Enter 3306 as the port.
  4. Enter your db username and password.
  5. Click OK.

You should be in!

Note2 icon.png Notes:
  • Your PuTTY session must be active and you must be logged in for the tunnel to work.
  • If you are running a local MySQL db, you can specify a different port for your tunnel. Otherwise, you cannot access your local mysql connection while the tunnel is active.
To specify a different port:
  1. Enter 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.
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

To set up the port forwarding in your ~/.ssh/config:

  1. 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
    
  2. Enter the following to connect:
     ssh your_nickname_for_your_host
     mysql -h 127.0.0.1 -P 3307 -u db_user_name -p
    
  3. 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
    
  4. Connect with mysql as above.
Note2 icon.png Notes:
  • 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).
  • If connecting via command line, you must use the mysql -P flag and specify port 3307 as in the following:
mysql -u username -p -h 127.0.0.1 -P 3307


Using SQLyog

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.
    SqlYog2.png
  3. Enter the information under the MySQL tab.
    SqlYog1.png
  4. Enter the information under the SSH tab.
  5. 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:
    • Name: [optional]
    • Host: [your MySQL host name: mysql.domain.com]
    • Username: [your database user name]
    • Password: [your database user password]
    • Database: [optional]
    • Port: [3306]
  2. 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.

Error Messages

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");

See Also

MySQL Usage

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

Note2 icon.png Notes:
  • Conueries is a term used by DreamHost to measure MySQL database usage. The word is a combination of "connections" and "queries." which is 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.
  • We no longer impose any hard limits of conueries on DreamHost plans.
  • DreamHost reserves the right to make high-volume users curtail their database usage (or switch to DreamHost PS MySQL). This only applies to very high-volume customers who may be receiving thousands of database-driven visitors each day.


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.

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

External Links