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.

Contents

[edit] 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 database server in your browser, e.g. dbname.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 datbase 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 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?".

[edit] 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.

  1. Log into your old server with SSH or Telnet
  2. Type the following command: 
 mysqldump --opt -uusername -ppassword -h yourMySQLHostname dbname > output.sql
  1. Log into your account with FTP and download the newly created output.sql file.

[edit] 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).

[edit] See also

  • Backup is a portal to instructions on how to back up many different things
  • MySQL is a portal to information about many aspects of MySQL
  • Automatic Backup tells how to set up a process that automatically backs up your database at intervals
  • KB / Account Control Panel / Goodies :: MySQL is an old knowledge base article that includes "How do I back up my database?"
Retrieved from "http://wiki.dreamhost.com/Backup_MySQL"
Personal tools