Backing Up Databases

A common reason to use the MySQL Administrator is to easily back up and restore your data. The process is simple, but there are quite a few options, so I'll lead you through the entire sequence in these next steps.

If you cannot use the MySQL Administrator or otherwise don't want to, you can use MySQL's built-in mysqldump utility or the SQL commands discussed in the sidebar.

Using mysqldump This utility can re-create the SQL queries used to buildand more important, to populateyour tables and data. An added benefit of this formal backup is that your databases can be easily transferred to another operating system or even another database application by importing the SQL queries there. Because mysqldump is a separate utility, it is run from the command line. The syntax for mysqldump is mysqldump [options] databasename The [options] section is, naturally, optional. In general, the easiest, most fail-safe configuration option for mysqldump is to use --opt. This setting will automatically initiate a standard set of parameters, and it will generate one large text file. In this case you should specify the filename to be created as a backup: mysqldump --opt databasename > /path/to/filename.sql You can back up every database at once using mysqldump --opt --all-databases > /path/to/filename.sql

To back up your database:

1.	Open the MySQL Administrator and connect to the MySQL server. You only need to connect as a user with permission to access the database you'll be backing up.
2.	Click Backup.
3.	Click New Project (Windows) or click the plus button at the bottom of the Backup Projects window (Mac OS X, Figure 13.6) to start creating a new backup project. Figure 13.6. You can create as many backup projects as you want or need. [View full size image] You can establish any number of projects, each of which performs a different backup (different databases or different backup options).
4.	In the Project Name window, give the backup a descriptive name (Figure 13.7). Figure 13.7. Use an appropriate title for each backup project. [View full size image]
5.	Select the tables and databases to back up (Figure 13.8). Figure 13.8. You can back up multiple databases or just individual tables. [View full size image] From the left-hand column, which lists the databases (also called the schemata), click a database name. Then click the "greater than" button to move the database into the Backup Content window. Then check or uncheck any individual table to include it in a backup. Repeat this simple sequence for any other databases to be included.
6.	Click Advanced Options to change any settings (Figure 13.9). Figure 13.9. Customize the backup to suit the storage engines used and what kind of output you want to generate. [View full size image] You should start by making sure that the Backup Execution value corresponds to the type of tables being backed up. Also note that, ironically, the "normal backup" should be avoided. In any case, read the nice descriptions and choose the right Backup Execution method. With the output file options, these will also affect how you restore a database using a backup. I personally like the "Add DROP Statements" option (or "Add DROP Table" option on Mac), which will drop any existing tables before going to re-create them (during a restore).
7.	Click Schedule to establish routine and automatic backups (Figure 13.10). Figure 13.10. Schedule frequent and regular backups to prevent data loss in case of a server problem. [View full size image] You can automatically schedule the execution of a backup project. If you are the kind of person who tends to forget these things until it's too late, you may want to strongly consider this option.
8.	On Windows, click Save Project to complete the process. You do not need to take this step on Mac OS X.

An SQL Equivalent, Part 1 With most versions of MySQL you can back up a table using a SELECT INTOquery: SELECT * INTO OUTFILE '/path/to/filename.txt' FROM tablename This command will allow you to create a more specialized query and store the results in a text file. This method will only store the data, not the structure of a table. Two caveats with using SQL to back up your databases are that MySQL must have permission to create the file in the directory that you name and that you must be logged in as a user with FILE permissions (which may also mean you can only do this while working directly on the server).