| 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. 
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. 
| 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. 
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. 
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. 
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.
|
 Tips
You can click Start Backup (Mac) or Execute Backup Now (Windows) at any time in order to immediately back up the selected tables. Another way to back up your databases is to use the mysqlhotcopy Perl script that comes with Unix and Mac OS X MySQL installations. This is the fastest way to back up a database, but it must be run on the same computer as MySQL, can only be used to back up MyISAM tables, and assumes that you have Perl installed.
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). |
|
