Backup MySQL

From DreamHost
Jump to: navigation, search

Overview

This article illustrates how to Backup MySQL databases hosted with DreamHost.

DreamHost provides a handful of backup methods for MySQL databases, with the actions ranging from extremely simple to mildly technical. The tools offered include the DreamHost panel, the provided phpMyAdmin interface, and using the server command line to generate a backup available for FTP downloading.

Backing up using the Account Backup tool

This tool is located on your (Panel > ‘Billing & Account’ > ‘Backup Your Account’) page. It downloads all the data in your account, including all users, mailboxes, and databases (MySQL stored procedures are captured as well), in a series of .zip archive files.

You can read more about this in the following wiki:

One Click Account Backup tool

Backing up using phpMyAdmin

phpMyAdmin is a MySQL database administration tool that provides a (relatively) easy-to-use UI through a web browser. Follow the instructions below to create a MySQL database backup using phpMyAdmin.

  1. Find the database user/password. Visit the (Panel > 'Goodies' > 'MySQL Databases') page where you can find a listing of your MySQL hostnames and existing databases.
    The specific username tied to the database is listed to the right of your databases.
    01 BackupMySQL db username.fw.png
  2. Click the MySQL username.
    02 BackupMysql db password.fw.png
  3. Click the Show button to obtain it’s password. The database’s MySQL user and password are your credentials when logging into phpMyAdmin.
    03 BackupMysql phpmyadmin link.fw.png
  4. Open phpMyAdmin. Do this by opening the URL of your MySQL hostname in your browser (e.g., mysql.domain.com). Or, click the phpMyAdmin link to the right of the hostname.
    This opens phpMyAdmin for your database server. A user name and password prompt appear.
    04 BackupMysql phpmyadmin login.fw.png
  5. Enter your MySQL username and password, and then click OK.
    Note2 icon.png Note: phpMyAdmin requires that the domain for your MySQL hostname is Fully Hosted and uses DreamHost's nameservers. For example, if your hostname is mysql.yourdomain.com, then yourdomain.com must be Fully Hosted by DreamHost and use the DreamHost nameservers.



    The phpMyAdmin user interface appears.
    05 BackupMysql phpmyadmin.fw.png
  6. To the left, click on the name of your database you wish to backup.
    06 BackupMysql choose database.fw.png
    Note2 icon.png Note: The “information_schema” database is automated by DreamHost and should be ignored. You are not allowed to modify the entries of this database.



  7. Click the Export tab at the top.
    A pane marked “View dump (schema) of database” appears.
    08 BackupMysql Export.fw.png
  8. Underneath the list marked "Export", click the 'Select All' link.
    All the tables are now highlighted.
  9. Make sure you select the SQL radio button in the Export panel. The phpMyAdmin interface provides various export formats, including CSV, SQL, XLS (MS Excel), and XML.
  10. Check the Stucture box and the following three settings in this section:
    • Add IF NOT EXISTS
    • Add AUTO_INCREMENT value
    • Enclose table and field names with backquotes.
  11. Check the Data box and the following three settings in this section:
    • Complete inserts: Exporting your database with “complete inserts” enabled allows for a more complete backup and is recommended if you plan to import the data into a new database with a differing storage engine. In any other case, leave it unchecked.
    • Extended inserts: Database exports exceeding 100MB should uncheck “extended inserts” as this prevents errors importing large database backups in the future.
    • Use hexadecimal for BLOB
    09 BackupMysql export go.fw.png
  12. Check the box marked Save as file on the bottom left.
    • In the box marked "File name template:", name the backup file.
    • phpMyAdmin expands certain abbreviations in this string: __SERVER__ expands to the server name, __DB__ expands to the database name, and a string like %Y%m%d expands according to the rules of PHP's strftime function.
  13. Click the Go button.

phpMyAdmin downloads a SQL file named as you specified. When the file download dialog appears in your browser, specify on your computer where you wish to store the file and complete the download.

Backing up through the SHELL

DreamHost shared hosting servers, VPS, and Dedicated hosting support MySQL commands which are entered from within the server command line.

To backup your database with a single command, log into your web server via SSH. Visit the following wiki for full instructions on how to do this:

Once logged into the server, run the following command. Make sure to change the username, password, yourMySQLHostname, and dbname to the correct credentials. You can find those in your panel on the (Panel > 'Goodies' > 'MySQL Databases') page:


mysqldump --opt --user=username --password=password --host=yourMySQLHostname dbname > nameofyourbackup.sql

You can use any name for "nameofyourbackup.sql" – just make sure it ends with .sql.

Note2 icon.png Notes: Please note the following when you run this command:
  • You only need to run the command once.
  • The command does not provide any output.
  • To an inexperienced user, it may appear that the server is not responding — Do not cancel the command action! If there is no response from the server, then you can assume that the command is continuing with the backup.
  • For large databases, a backup can take several minutes to complete.


You can now use the following FTP wiki for instructions on how to log into your server and download the .sql file:

See also

  • Backup – DreamHost’s wiki that lists different backup options
  • MySQL – DreamHost’s MySQL wiki