From DreamHost
(Redirected from Microsoft SQL Server)
Jump to: navigation, search


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 article 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

As of September 2015, DreamPress and private MySQL servers are being upgraded to MySQL version 5.6.

Shared and Dedicated servers are scheduled to be upgraded in the future. 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
FEDERATED NO Federated MySQL storage engine NULL NULL NULL
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.

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 are available.

To backup your entire account (ftp users, mail users and databases) check out the 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.

  1. Navigate to the (Panel > 'Goodies' > 'MySQL Databases') page.
    01 Restore MySQL.png
  2. Click the Restore DB button to the right of your database.
    02 Restore MySQL.png
  3. 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 are 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
  4. 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 DreamHost encourages its 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.

Field Number Field Name Action and Description
1callout.png Database Name Enter a unique name for the database. Database names must be unique across the entire DreamHost system, and can only contain numbers, the letters a-z, and underscores (_). Picking a name that is longer and descriptive is recommended. For clarity, DreamHost suggests putting your domain in the database name such as "blog_example_com" instead of a less clear example of "blog12345".
2callout.png Use Hostname / New Hostname Here you can create a new hostname or select an existing one from a drop-down menu if any exist. Creating a hostname for each domain is generally recommended to prevent downtime should you remove a domain from your account in the future, and find out that other sites used a hostname on it and no longer functions.
3callout.png First User / New Username Here you can create a new MySQL user or select an existing one from a drop-down menu if any exist. For security reasons, having a MySQL user for each database is recommended so that any compromised site or credentials cannot be used to access your other databases.
4callout.png New Password If you are creating a new user, enter a password for that user here. If you select an existing user, this field is disabled.
5callout.png New Password Again If you are creating a new user, re-enter the same password as above here. If you select an existing user, this field is disabled.
6callout.png Database Comment Enter a description of your database here for organizational use. Optional

When all required fields have been entered, click Add new database now! to begin the database creation process.

Dh-kb-note-icon.fw.png Notes:
  • Adding new databases or users, changing MySQL user passwords, and deleting databases or passwords generally takes affect within 5 minutes.
  • Newly added hostnames will function for websites within 5 minutes, due to all of your hostnames being written to your webserver(s) /etc/hosts file.
  • Using a new hostname for phpMyAdmin may take up to 4 hours to function due to DNS propagation time.

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.

Dh-kb-important-icon.fw.png Important: The red text in the items below indicate possible destructive commands. Please be careful when using these commands.

Button Number Button Name Action and Description
1callout.png Add New Hostname Click here to display the new hostname page that allows entering a name and selecting a domain for your new hostname.
2callout.png Delete Hostname Click here to delete the hostname listed to the left of the button. Deleting a hostname stops database access for any sites still utilizing it. There must always be at least one hostname, so if only one exists this button is disabled until another hostname is added.
3callout.png Add a user Click here to display a new page that allows creating a new MySQL user or selecting an existing user that should have privileges to the database.
4callout.png Restore DB Click here to display the database restoration page that allows you to select from a drop-down list of available database backups, and how the tables should be restored. Tables from the backup can be restored with a timestamp into the database, or restored with original table names with existing tables renamed with a timestamp. Restoring a database backup to the original table names is not recommended as it can cause a disruption in service to your site if it doesn't expect the older data.
5callout.png Modify DB This allows you to modify the optional description text of your database.
6callout.png Delete DB Click to delete the database from your account. Access to the database is disabled, and remains in the MySQL Recycle Bin for an additional 30 days until being permanently deleted. Please make a backup of the database before deleting, as DreamHost cannot guarantee availability of backups for deleted databases.

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.


Dh-kb-important-icon.fw.png Important: The red text in the items below indicate possible destructive commands. Please be careful when using these commands.

Button Number Button Name Action and Description
1callout.png Delete Clicking the Delete button removes this MySQL users access to the database listed at the top of the page. When a MySQL user is deleted from all databases, the MySQL user ceases to exist and is considered fully deleted.
2callout.png What may USERNAME do to tables in these databases? This section allows adding or removing specific privileges of the MySQL user, affecting its access to read, write or change data, as well as administrate the database contents.
  • Select — Grants the ability to read rows from tables.
  • Insert — Grants the ability to add new rows to tables.
  • Update — Grants the ability to modify existing rows in tables.
  • Delete — Grants the ability to remove rows in tables.
  • Create — Grants the ability to create new tables. This does not allow the creation of new databases on the command line.
  • Drop — Grants the ability to remove tables and views.
  • Index — Grants the ability to add or remove indexes from existing tables.
  • Alter — Grants the ability to change the structure of tables.
3callout.png Allowable Hosts A list of hosts that are allowed to login to this MySQL user. For security reasons, the default is which restricts connections from outside the DreamHost network, even is a correct username and password is provided. Additional IP addresses or wildcards can be added on new lines to allow access to an outside network. Modifying or removing may result in MySQL inaccessibility for DreamHost hosted websites.
4callout.png Current Password Click the Show button to be displayed the password for this MySQL user.
5callout.png New Password Enter a new password here to change the users password. Please keep in mind that all sites using this MySQL user stops functioning within minutes, until all configuration files are updated to the new password.
6callout.png New Password Again Confirm the new password by entering it again here.

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

  1. Log in to your DreamHost account.
    ssh -l yourlogin
    • Replace 'example' with your domain name.
  2. 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 -u yourdbuser -p yourdb
  3. Optionally, include your password in the command (do not include a space after "-p").
    mysql -h -u yourdbuser -pyourdbpassword yourdb
Dh-kb-important-icon.fw.png 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:

  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,,, and so on).
    Dh-kb-tip-icon.fw.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.

    Dh-kb-note-icon.fw.png Notes:
    • Enabling does not enable the hostname by itself.
    • If you are being denied with an error like "access denied to" try adding a wildcard to the end of your ip (such as
  5. Click Modify [username] now!
  6. Launch your third-party program, enter the database and login information, and then connect.

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

  1. Go to your Dreamhost Account Control Panel.
  2. Edit the user you wish to grant SSH access.
  3. Under "User Type:", choose "Shell user".
  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.,
  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.,
  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, such as MySQL WorkBench.
  2. Enter localhost under Server Host.
    Note: Entering your actual address (e.g., 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!

Dh-kb-note-icon.fw.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 Administrator 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.

Connecting using SSH

If you are running a local MySQL server on port 3306, you must set up port forwarding in your ~/.ssh/config, as follows:

  1. Enter the following settings:
     Host your_nickname_for_your_host
         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 -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
  4. Connect with mysql as above.
Dh-kb-note-icon.fw.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 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 -P 3307

Connecting 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.
  3. Enter the information under the MySQL tab.
  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.

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:

  1. Use the Standard connection tab and enter the following:
    • Name: [optional]
    • Host: [your MySQL host name:]
    • 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/favorite. You must first configure a user for SSH access under Users -> Manage Users in your DreamHost panel. With these details in hand, you can configure the following additional options:

  • SSH Host: [SSH host name]
  • SSH User: [Username of an SSH enabled user]
  • SSH Password: [Password for the account]
  • SSH Port: [22]

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.

Error messages

phpMyAdmin related errors

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:
  • 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');
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");

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1'

This error is caused by an incompatibility with DreamHost's older Debian operating system on dedicated and webserver VPS products when used with our newer MySQL 5.6.x server version. If you see this error please contact support to discuss operating system upgrade options.

Additional MySQL resource information

MySQL Usage

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

Dh-kb-note-icon.fw.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.
  • DreamHost does not impose any hard limits of conueries on its 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.

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 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 VIEW Enable views to be created or altered 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
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
SHOW DATABASES Enable SHOW DATABASES to show all databases (Only shows those databases you have access to) NO
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
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.

See also