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!).
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.
- 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.
- Enter your MySQL user name and password. The phpMyAdmin user interface appears.
- On the left pane, click the popup menu item (databases) .... A list of MySQL databases on this server appears.
- Select the database which you wish to back up from the list. Information on that database appears in the right pane.
- On the tabs across the top, click Export. A pane marked "View dump (schema) of database" appears.
- Underneath the list marked "Export", click the link Select All. All the table names will be highlighted.
- In the radio button set below, make sure SQL is selected. This selects the output format.
- 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
- 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.
- Check the box marked Save as file.
- 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.
- 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: . 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).
- 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?"
- Schedule automated MySQL backups to your PC Works with Dreamhost. Restores too!