MySQL

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

Overview

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 make backups daily, starting at midnight and completing after every single database has been done. There are generally 5 days of backups available, which can be restored via the Restore DB button in the Goodies > MySQL Databases control panel. 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 on our backups being available.

To backup your entire account (ftp users, mail users and databases) check out our account backup feature. This is available in the Billing & Account -> Backup Your Account section of the DreamHost Control Panel. The backups created can be downloaded to another offsite location for safe keeping.

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.
Mysql-menu.png
The MySQL Databases page appears.

Mysql-view.png

Creating a MySQL database

To create a database, scroll to the bottom of the MySQL Databases page and fill out the following fields.

Mysql-create-form.png
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, we suggest putting your domain in the database name such as "blog_myfancynewsite_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 will no longer function.
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 will be 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 will be disabled.
6callout.png Database Comment Enter a description of your database here for organizational use. Optional
Mysql-create-button.fw.png

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

Note2 icon.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.

Mysql-manage.png

Hostnames are displayed at the top, with helpful links for phpMyAdmin access. Databases are listed below the hostnames.

Important icon.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 will stop database access for any sites still utilizing it. There must always be at least one hostname, so if only one exists this button will be 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 will be disabled, and it will wait 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.

Mysql-user-list.png
The MySQL User page appears.

Mysql-user-edit.png

Important icon.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 %.dreamhost.com which will restrict 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 outside our network. Modifying or removing %.dreamhost.com 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 will stop 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 yourdomain.com
  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 yourdbdomain.yourdomain.com -u yourdbuser -p yourdb
  3. 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 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.


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

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., 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, such as MySQL WorkBench.
  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.


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:

  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


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

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: 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]

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

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 do not 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

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