Backup MySQL

From DreamHost
Jump to: navigation, search

This article tells you how to Backup MySQL databases.

The manner you go about getting a backup of your MySQL database will differ from host to host. If you have access to phpMyAdmin that is the probably most user-friendly manner of getting your backup, but for databases larger than 8MB, its recommended to use the shell method or the mysqldump method via Dreamhost's Cron job Interface (which can also be automated!).

Backing up all your databases using the DreamHost Account Backup tool

This tool is located on the {DreamHost -> Backup Your Account} page of the DreamHost panel. It lets you download all the data in your account, including all users, mailboxes and databases, in a series of .zip archive files.

One Click Account Backup tool

Getting a Backup From phpMyAdmin

phpMyAdmin is a MySQL database administration tool that provides a (relatively) easy-to-use UI through a web browser. Here is how to back up a MySQL database using phpMyAdmin.

  1. Start phpMyAdmin. Do this by opening the URL of your MySQL hostname in your browser, e.g. mysql.domain.com. At DreamHost, this invokes phpMyAdmin for your database server. A user name and password prompt appears.
  2. Enter your MySQL user name and password. The phpMyAdmin user interface appears.
  3. On the left pane, click the popup menu item (databases) .... A list of MySQL databases on this server appears.
  4. Select the database which you wish to back up from the list. Information on that database appears in the right pane.
  5. On the tabs across the top, click Export. A pane marked "View dump (schema) of database" appears.
  6. Underneath the list marked "Export", click the link Select All. All the table names will be highlighted.
  7. In the radio button set below, make sure SQL is selected. This selects the output format.
  8. In the section marked "Structure:", check the box marked Structure:. Within this section check boxes marked Add AUTO_INCREMENT value, and Enclose table and field names with backquotes
  9. In the section marked "Data:", check the box marked Data:. Within this section check boxes marked Use hexadecimal for binary fields. If you are backing up a large (how large???) database, uncheck extended inserts as when you restore the backup the server may not accept such long SQL commands. If you plan to apply the backup to a database with a different structure (e.g., you applied a mod to PhpBB) or to an updated version of a web application, check complete inserts, otherwise leave it unchecked. Leave other boxes unchanged.
  10. Check the box marked Save as file.
  11. In the box marked "File name template:", fill in the name you want the backup file to have. 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 is expanded according to the rules of PHP's strftime function.
  12. Click Go button.

phpMyAdmin will download a SQL file named as you specified. A file download dialog from the browser appears. Specify where on your local host you want to store the file.

See also the phpMyAdmin FAQ #6.4, "How can I backup my database or table?".

Getting a Backup From the Shell

You can use the mysqldump shell command to make a backup, also. This is more difficult to do the first time than the phpMyAdmin method above, but you can automate it so that the system backs up your database for you. See Automatic Backup for instructions on how to automate this.

  • Log into your server with SSH
   host name: servername.dreamhost.com
   login as: username (see Enabling Shell Access for users)
  • Type the following command using your database user:
   mysqldump --opt --user=username --password=password --host=yourMySQLHostname dbname > output.sql
  • Log into your account with FTP and download the newly created output.sql file, which should be located in the root. Refresh your FTP view if necessary.

Getting a Backup From mysqldump

Using the script mysqldump, put in the required variables and simply copy and paste it into a new cron job. You can also set your frequency of backup through Dreamhost's Cron Job interface, making it a regular automatic backup for small or large databases.

Getting a Backup From cPanel.net

These instructions from the Official cPanel site should guide you through the process: [1]. Please note that a Full Backup from cPanel cannot be used to migrate a database to DreamHost - you have to have cPanel available on the receiving system to make use of that (and cPanel is not available on DreamHost).

See also