Backup MySQL

From DreamHost
(Redirected from Automated MySQL snapshots)
Jump to: navigation, search


This article illustrates how to back up your 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 panel backup tool

The account backup 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 article:

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.
    01 phpMyAdmin db user 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.
    04 phpMyAdmin hostnames.fw.png
  4. Open phpMyAdmin. Do this by opening the URL of your MySQL hostname in your browser (e.g., 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 Go.
    Dh-kb-note-icon.fw.png Notes: phpMyAdmin requires that the domain for your MySQL hostname is Fully Hosted and uses DreamHost's DNS. You can do this in one of two ways:
    • Point your Nameservers to DreamHost, or
    • Point your A record to your DreamHost IP. View the DNS article for details on how to retrieve that IP address.

    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
    Dh-kb-note-icon.fw.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 Structure box and the following three settings in this section:
    • 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 article 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.

Dh-kb-note-icon.fw.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 article for instructions on how to log into your server and download the .sql file:

See also

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