MySQL database backup methods

It is important to back up your database when database tables are lost or damaged. In the event of a system crash, you must be able to restore as much data as possible from your table to the State at the time of the crash. Sometimes it is the MySQL administrator who causes damage. The Administrator knows that the table has been damaged and tries to edit it directly in an editor such as vi or Emacs. This is definitely not a good thing for the table!
The two main methods for backing up a database are to use the mysqldump program or directly copy database files (such as cp, cpio, or tar ). Each method has its advantages and disadvantages:
Work with the MySQL server. The direct copy method is performed outside the server, and you must take measures to ensure that no customer is modifying the table you will copy. If you want to use file system backup to back up the database, the same problem also occurs: if the database table is modified during file system backup, when you enter the inconsistent subject status of the backup table file, it will be meaningless for future table recovery. The difference between file system backup and direct file copy is that you have full control over the backup process for the latter, so that you can take measures to ensure that the server does not interfere with the table.
Mysqldump is slower than direct copy.
Mysqldump generates text files that can be transplanted to other machines, even those machines with different hardware structures. Directly copying files cannot be transplanted 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, you can directly copy files to machines with different hardware structures. Only two conditions are met: the other machine must also run MySQL 3.23 or a later version, and the file must be in MyISAM format, not ISAM format.


No matter which backup method you use, if you need to recover the database, there are several principles that should be followed to ensure the best results:
Perform regular backup. Establish a plan and strictly abide by it.
Let the server execute the Update log. When you need to recover data after a crash, the update log will help you. After you use the backup file to restore the data to the backup state, you can use the modifications following the backup again by running the query in the Update log, this restores the tables in the database to the state when a crash occurs.
In terms of file system backup, database backup files represent full dumping, while update logs represent progressive dumping ).
A unified and easy-to-understand backup file naming mechanism is used. Such as backup1 and buckup2 are not particularly meaningful. When you implement your recovery, you will waste time figuring out what is in the file. You may find it useful to create a backup file name using the database name and date. 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 they are generated. The backups are huge! You also need to set an expiration date for your backup files to prevent them from filling up your disk, just as you have set your log files to expire.
Back up your backup file with the file system. If a complete crash not only clears your data directory, but also clears the disk drive containing your database backup, you will be in real trouble.
Back up your update logs.
Place your backup file on a file system different from the one used for your database. This will reduce the possibility of filling up the file system that contains the data directory due to the generation of backups.

The technology used to create backups is also useful for copying databases to another machine. Most often, a database is transferred to a server running on another host, but you can also transfer data to another server on the same host.
1. Use mysqldump to back up and copy Databases

When you use the mysqldumo program to generate a database backup file, by default, the file content includes the CREATE statement for creating the table being dumped and the INSERT statement containing the row data in the table. In other words, the output produced by mysqldump can be used as mysql input to recreate the database.
You can dump the entire database into a separate text file, as shown below:
% 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 st
Ructure for table 'absence '# create table absence (student_id int (10)
Unsigned DEFAULT '0' not null, date DEFAULT '2017-00-00 'NOT NUL
L, primary key (student_id, date); # Dumping data for table 'absence'
# Insert into absence VALUES (3, '2017-09-03 '); insert into absence VALUE
S (5, '2017-09-03 '); insert into absence VALUES (10, '2017-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 want a large database, the output file will also be huge and may be difficult to manage. If you want to, you can list individual table names after the database names in the mysqldump command line to lean them to the contents, which divides the dumped files into smaller and easier-to-manage files. The following example shows how to roll some tables in the samp_db database to a separate file:
% Mysqldump samp_db student score event absence> grapbook. SQL
% Mysqldump samp_db member president> hist-league. SQL
If you generate a backup file that is used to regularly refresh the content 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. Then, when you extract the backup file and load it into the second database, IF the TABLE already EXISTS, you will not get an error.
If you pour out a database so that the database can be transferred to another server, you don't even have to create a backup file. Ensure that the database exists on another host and then dumped the database using pipelines, so that mysql can directly read the output of mysqldump. For example, if you want to copy the database samp_db from the host pit-viper.snake.net to boa.snke.net, you can easily do this:
% Mysqladmin-h boa.snke.net create samp_db
% Mysqldump samp_db | mysql-h boa.snke.net samp_db
In the future, if you want to refresh the database on boa.snke.net again, skip the mysqladmin command, but add -- add-drop-table to mysqldump to avoid table errors:
% Mysqldump -- add-drop-table samp_db | mysql-h boa.snke.net samp_db
Other useful options for mysqldump include:
The combination of -- flush-logs and -- lock-tables will be helpful for your database checkpoints. -- Lock-tables locks all the tables you are dumping, and -- flush-logs closes and re-opens the Update log file, the new update log will only include queries for modifying the database from the backup point. This will set the backup time of your Update log check point. (However, if you have a customer who needs to perform an update, it is not a good thing to lock all tables for customer access during backup .)
If you use -- flush-logs to set the Check Point to backup, it is best to dumped the entire database.
If you dumped individual files, it is difficult to synchronize the Update log checkpoint with the backup file. During the recovery period, you usually extract the updates based on the database, but you have no choice to extract updates for a single table. Therefore, you must extract them by yourself.
By default, mysqldump reads the entire content of a table into the memory before writing. This is usually unnecessary, and in fact, if you have a large table, it is almost a failure. You can use the -- quick option to tell mysqldump to write each row as long as it retrieves a row. To further optimize the dumping process, use -- opt instead of -- quick. -- Opt option opens other options to accelerate data dumping and read them back.
Using -- opt for backup may be the most common method, because of the advantages of backup speed. However, you should be warned that the -- opt option does have a price. -- opt optimizes your backup process rather than other customers accessing the database. The -- opt option prevents anyone from updating any table you are dumping by locking all tables at a time. You can easily see the effect of common database access. When your database is usually used very frequently, it only adjusts the backup once a day.
An option with the opposite effect of -- opt is -- dedayed. This option causes mysqldump to write the insert delayed statement instead of the INSERT statement. If you load data files into another database and want to minimize the impact of this operation on queries that may occur in the database, -- delayed is helpful.
The -- compress option is helpful when you copy a database to another machine because it reduces the number of bytes transmitted over the network. The following example shows that -- compress only gives the program for communicating with the server on the remote host, rather than the program connected to the local host:
% Mysqldump -- opt samp_db | mysql -- compress-h boa.snke.net samp_db

Mysqldump has many options. For details, see MySQL reference manual.
2. Backup and copy methods for directly copying Databases

Another method that does not involve mysqldump to back up databases and tables is to directly copy database table files. Typically, this uses utilities such as cp, tar, or cpio. The example in this article uses cp.
When using a direct backup method, you must ensure that the table is not used. If the server changes a table while you are copying it, the copy will be meaningless.
The best way to ensure your copy integrity is to close the server, copy files, and restart the server. If you do not want to shut down the server, you must lock the server while performing the table check. If the server is running, the same constraints apply to copying files, and you should use the same locking protocol to make the server "quiet ".
If the server is closed 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 indicates 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 it is disabled) or release the lock added to the table (if you want the server to run ).
To copy a database from one machine to another, just copy the file to the appropriate data directory of another server host. Make sure the file is in MyIASM format or the two machines have the same hardware structure. Otherwise, your database has strange content on another host. You should also ensure that the servers on the other machine do not access them when you are installing database tables.

3. Copy a Database)

Replication is similar to copying a database to another server, but its exact meaning is to ensure full synchronization of the two databases in real time. This feature will be available in version 3.23 and is not very mature. Therefore, this article will not detail it.
4. Use Backup to restore Data

There are many causes for database corruption, and their severity varies. If you are lucky, you may only damage one or two tables (such as power loss). If you are unlucky, you may have to replace the entire data directory (such as disk damage ). Recovery is also required in some cases. For example, the user mistakenly deleted the database or table. Regardless of the cause of these unfortunate events, you will need to implement a recovery.
If the tables are corrupted but not lost, try to use myisamchk or isamchk to fix them. If such a corruption occurs, you may not need to use backup files at all. For the table repair process, see database maintenance and repair.
The recovery process involves two sources of information: Your backup file and an update log. The backup file restores the table to the status when the backup is implemented. However, generally, the table has been modified in the time between the backup and the occurrence of the problem. The update log contains queries for these changes. You can use the log file as the mysql input to repeat the query. This is why log update is enabled.
The recovery process varies depending on how much information you must restore. In fact, it is easier to restore the entire database than a single table, because it is easier to use the Update log for the database than a single table.
4.1 restore the entire database

First, if the database you want to restore is a mysql database containing an authorization table, you need to run the server using 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 labels and use them.
Copy the contents of the database directory to another place if you need them later.
Use the latest backup file to reinstall the database. If you use the file generated by mysqldump, use it as the mysql input. If you copy files directly from the database, copy them directly back to the database directory. However, you need to close the database before copying the files, and then restart it.
Use the Update log to repeat the backup and then modify the database table. Use any applicable update logs as mysql input. Specify the -- one-database option so that mysql can only query the databases you are interested in restoring. If you know that you need to use all the Update log files, you can use this command in the directory containing 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 and sorts them by the server's order (idea: If you modify any file, you will change the sorting order, this leads to the use of the wrong order of updated logs .)
You may use several updates. For example, the update logs generated since your backup are named update.392 and update.393. You can run them again like this:
% Mysql -- one-database db_name <update.392
% Mysql -- one-database db_name <update.393
.....
If you are performing recovery and use the Update log to recover lost information due to an error suggested drop database, drop table, or DELETE statement, before using the Update log, make sure to delete these statements from it.
4.2 restore a single table

Restoring a single table is complicated. If you use a backup file generated by mysqldump that does not contain the data of the tables you are interested in, you need to extract them from the relevant rows and use them as mysql input. This is an easy part. The hard part is to pull the fragments from the update logs that only apply to the table. You will find that the mysql_find_rows utility is very helpful for this, it extracts multi-row queries from the Update log.
Another possibility is to use another server to restore the entire database and then copy the expected table file to the original database. This may be really easy! When you copy files back to the database directory, make sure that the server of the original database is disabled.