The two main methods for backing up the database are to use the mysqldump program or directly copy the database file (such as using cp, cpio or tar, etc.). This article details the MySQL platform database backup plan. In the event that a database table is lost or corrupted, it is important to back up your database. If a system crash occurs, you definitely want to be able to restore your tables to the state they were in when the crash occurred with the least amount of data loss possible. Sometimes, it's the MySQL administrator who causes havoc. The administrator already knows that the tables are corrupted and trying to edit them directly using an editor such as vi or Emacs is definitely not a good thing for the tables.
The two main methods for backing up the database are to use the mysqldump program or directly copy the database file (such as using cp, cpio or tar, etc.). Each method has its pros and cons:
mysqldump works with MySQL server. The direct copy method is performed outside the server, and you must take steps to ensure that no client is modifying the table you are copying. If you want to use file system backup to back up the database, the same problem will occur: if the database table is modified during the file system backup process, the subject of the backed up table file will be inconsistent, and the table will be meaningless for future recovery. The difference between a file system backup and a direct copy of the file is that with the latter you have complete control over the backup process, so you can take steps to ensure that the server leaves the table undisturbed.
mysqldump is slower than direct copying.
mysqldump generates text files that are portable to other machines, even those with different hardware architectures. Directly copying files cannot be ported to other machines unless the table you are copying uses the MyISAM storage format. ISAM tables can only be copied on machines with similar hardware structures. The MyISAM table storage format introduced in MySQL 3.23 solves this problem because the format is machine-independent, so directly copying the file can be transplanted to machines with different hardware structures. As long as two conditions are met: the other machine must also be running MySQL 3.23 or later, and the file must be represented in MyISAM format, not ISAM format.
No matter which backup method you use, if you need to restore your database, there are several principles that should be followed to ensure the best results:
Implement regular backups. Establish a plan and stick to it.
Let the server perform update logging. The changelog will help you when you need to recover data after a crash. After you use the backup file to restore the data to the state it was in at the time of the backup, you can reapply the changes made after the backup by running a query in the update log, which will restore the tables in the database to the state they were in when the crash occurred.
In file system backup terms, the database backup file represents a full dump, while the update log represents an incremental dump.
Use a consistent and understandable naming scheme for backup files. Things like backup1, buckup2, etc. are not particularly meaningful. When performing your recovery, you will waste time figuring out what is in the files. You may find it useful to use the database name and date to form the backup file name. For example:
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02
%mysqldump menagerie >/usr/archives/mysql/menagerie.1999-10-02
You may want to compress the backups after generating them. Backups tend to be big! You also need to expire your backup files to avoid them filling up your disk, just like you expire your log files.
Back up your backup files with a file system backup. If you experience a complete crash that not only clears your data directory, but also the disk drive containing your database backups, you're going to be in real trouble.
Also back up your changelog.
Place your backup files on a different file system than the one used for your database. This will reduce the possibility of filling up the file system containing the data directory as a result of generating the backup.
The techniques used to create backups are also useful for copying a database to another machine. Most commonly, a database is moved to a server running on another host, but you can also move the data to another server on the same host.
1 Use mysqldump to back up and copy the database
When you use the mysqldumo program to generate a database backup file, by default, the file contents contain the CREATE statement that creates the table being dumped and the INSERT statement that contains the row data in the table. In other words, the output produced by mysqldump can later be used as input to mysql to rebuild the database.
You can dump the entire database into a single text file as follows:
%mysqldump samp_db >/usr/archives/mysql/samp_db.1999-10-02
The beginning of the output file looks like this:
# MySQL Dump 6.0# # Host: localhost Database: samp_db
#---------------------------------------#
Server version 3.23.2-alpha-log## Table structure for table absence
#CREATE TABLE absence( student_id int(10) unsigned DEFAULT 0 NOT NULL, date date DEFAULT 0000-00-00 NOT NUL L,
PRIMARY KEY (student_id,date));## Dumping data for table absence #INSERT INTO absence VALUES (3,1999-09-03);INSERT INTO absence VALUE S (5,1999-09-03);INSERT INTO absence VALUES (10,1999-09-08);......
The rest of the file consists of more INSERT and CREATE TABLE statements. If you want to compress the backup, use a command similar to the following:
%mysqldump samp_db | gzip >/usr/archives/mysql/samp_db.1999-10-02.gz
If you have a large database, the output files will also be large and may be difficult to manage. If you wish, you can list the individual table names after the database name on the mysqldump command line to dump their contents, which will break the dump file into smaller, more manageable files. The following example shows how to dump some tables from the samp_db database into separate files:
%mysqldump samp_db student score event absence >grapbook.sql
%mysqldump samp_db member president >hist-league.sql
If you are generating backup files that are intended to be used to periodically refresh the contents of another database, you may want to use the --add-drop-table option. This tells the server to write the DROP TABLE IF EXISTS statement to the backup file, and then, when you take the backup file and load it into the second database, you won't get an error if the table already exists.
If you dump a database so that you can move the database to another server, you don't even have to create a backup file. Make sure the database exists on another host, and then use a pipe to dump the database so that mysql can directly read the output of mysqldump. For example: you want to copy the database samp_db from the host pit-viper.snake.net to boa.snake.net. You can do it easily like this:
%mysqladmin -h boa.snake.net create samp_db
%mysqldump samp_db | mysql -h boa.snake.net samp_db
In the future, if you want to refresh the database on boa.snake.net again, skip the mysqladmin command, but add --add-drop-table to mysqldump to avoid getting the table already exists error: %mysqldump --add- drop-table samp_db | mysql -h boa.snake.net samp_db
Other useful mysqldump options include: --flush-logs and --lock-tables combination will be helpful for checkpointing your database. --lock-tables locks all tables you are dumping, and --flush-logs closes and reopens the update log file. The new update log will only include queries that modified the database from the backup point. This will set your update log checkpoint backup time. (However, if you have clients who need to perform an update, locking all tables is not a good idea for client access during backup.)
If you use --flush-logs to checkpoint into a backup, it's probably best to dump the entire database.
If you dump separate files, it is more difficult to synchronize update log checkpoints with backup files. During recovery, you usually extract the update log contents on a per-database basis. There is no option to extract updates for individual tables, so you must extract them yourself.
By default, mysqldump reads the entire contents of a table into memory before writing. This is usually really unnecessary, and in fact is almost a fail if you have a large table. You can use the --quick option to tell mysqldump to write out each row whenever it retrieves one. To further optimize the pouring process, use --opt instead of --quick. The --opt option turns on additional options to speed up dumping of data and reading them back.
Implementing backups with --opt is probably the most common method because of the speed advantage of backups. However, be warned, the --opt option does come at a cost. --opt optimizes your backup process, not other clients' access to the database. The --opt option prevents anyone from updating any table you are dumping by locking all tables at once. You can easily see the effect on general database access. When your database is generally used very frequently, just adjust the backup once a day.
An option that has the opposite effect of --opt is --dedayed. This option causes mysqldump to write INSERT DELAYED statements instead of INSERT statements. --delayed is helpful if you are loading a data file into another database and you want this operation to have minimal impact on queries that may appear in that database.
The --compress option is helpful when you copy the database to another machine because it reduces the number of bytes transferred over the network. Here is an example. Note that --compress is given for programs that communicate with a server on a remote host, not for programs that connect to the local host:
%mysqldump --opt samp_db | mysql --compress -h boa.snake.net samp_db
mysqldump has many options, see "MySQL Reference Manual" for details.
2 Backup and copy method using direct copy database
Another way to back up the database and tables that does not involve mysqldump is to copy the database table files directly. Typically, this is done using utilities such as cp, tar or cpio. The examples in this article use cp.
When you use a direct backup method, you must ensure that the table is no longer in use. If the server changes a table while you are copying it, the copy is meaningless.
The best way to ensure the integrity of your copy is to shut down the server, copy the files, and then restart the server. If you don't want to shut down the server, lock the server while performing the table check. If the server is running, the same restrictions apply to copying files, and you should use the same locking protocol to "quiet" the server.
Assuming the server is down or you have locked the table you want to copy, the following shows how to back up the entire samp_db database to a backup directory (DATADIR represents the server's data directory): %cd DATADIR%cp -r samp_db /usr/archive/mysql
A single table can be backed up as follows:
%cd DATADIR/samp_db%cp member.* /usr/archive/mysql/samp_db%cp score.* /usr/archive/mysql/samp_db ....
When you have completed the backup, you can restart the server (if you shut it down) or release the locks placed on the table (if you left the server running).
To copy a database from one machine to another using direct copy files, simply copy the files to the appropriate data directory on the other server host. Make sure the file is in MyIASM format or both machines have the same hardware structure, otherwise your database will have strange contents on the other machine. You should also ensure that the server on another machine does not access the database tables while you are installing them.
3 Replicating Database
Replication is similar to copying a database to another server, but its exact meaning is to ensure that the two databases are fully synchronized in real time. This feature will appear in version 3.23 and is not very mature yet, so this article will not introduce it in detail.
4 Restore data from backup
Database corruption can occur for many reasons and to varying degrees. If you're lucky, you might only corrupt one or two tables (like a power outage), if you're unlucky, you might have to replace the entire data directory (like a disk corruption). Recovery is also required in certain situations, such as when a user deletes a database or table by mistake. Regardless of the cause of these unfortunate events, you will need to implement some kind of recovery.
If the tables are damaged but not lost, try to repair them with myisamchk or isamchk. If such damage can be repaired by a repair program, you may not need to use the backup file at all. For the process of table repair, see "Database Maintenance and Repair".
The recovery process involves two sources of information: your backup files and your change logs. The backup file restores the table to the state it was in when the backup was performed. However, typically the table has been modified in the time between the backup and the problem, and the update log contains the queries used to make these modifications. You can use log files as input to mysql to repeat queries. This is why you should enable the change log.
The recovery process varies depending on how much information you have to recover. In fact, it is easier to restore the entire database than a single table because it is easier to apply the update log to the database than to a single table.
4.1 Restore the entire database
First, if the database you want to restore is a mysql database that contains a grant table, you need to run the server with the --skip-grant-table option. Otherwise, it will complain that the authorization table cannot be found. After you have restored the table, execute mysqladmin flush-privileges to tell the server to load the authorization tokens and use them.
Copy the database directory contents to some other location if you need them later.
Reinstall the database with the latest backup file. If you use the file generated by mysqldump, use it as input to mysql. If you are using files copied directly from the database, copy them directly back to the database directory. However, in this case you will need to close the database and then restart it before copying the files.
Use the update log to repeat queries that modify database tables after the backup. For any applicable change logs, pass them as input to mysql. Specifying the --one-database option causes mysql to execute queries only for the database you are interested in restoring. If you know you need to apply all update log files, you can use this command in the directory containing the logs:
% ls -t -r -1 update.[0-9]* | xargs cat | mysql --one-database db_name
The ls command generates a single-column list of update log files, sorted according to the order in which the server generated them (Idea: If you modify any of the files, you will change the sort order, which causes the update log to be used in the wrong order.)
Most likely you will use certain change logs. For example, if the update logs generated since your backup are named update.392, update.393, etc., you can rerun like this:
%mysql --one-database db_name < update.392
%mysql --one-database db_name < update.393
.....
If you are performing a recovery and are using the update log to recover information lost due to an incorrectly recommended DROP DATABASE, DROP TABLE, or DELETE statement, be sure to delete these statements from the update log before using it.
4.2 Restoring a single table
Restoring a single table is more complex. If you use a backup file generated by mysqldump and it does not contain data for the tables you are interested in, you will need to extract them from the relevant rows and use them as input to mysql. This is the easy part. The hard part is pulling the fragment from the update log that only applies to that table. You may find the mysql_find_rows utility helpful for this, which extracts multi-row queries from the change log.
Another possibility is to use another server to restore the entire database and then copy the table files you want into the original database. This might be really easy! When you copy the files back to the database directory, make sure the original database server is shut down.