MySQL database backup (4)

mysql| Backup | data | Database Database corruption occurs for a number of reasons and varies in degree. If you are lucky, you can damage only one or two tables (such as power off), if you are unlucky, you may have to replace the entire data directory (such as disk corruption). In some cases, recovery is also required, such as when a user mistakenly deletes a database or table. Whatever the cause of these unfortunate events, you will need to implement some kind of recovery.

If the table is corrupted but not lost, try to fix them with myisamchk or isamchk, and if such damage can be repaired by a hotfix, 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 file and an update log. The backup file restores the table to the state when the backup was implemented, but the general table has been modified in the time between the backup and the problem, and the update log contains the queries for making these modifications. You can use the log file as a MySQL input to repeat the query. This is the reason why you should enable the update log.

The recovery process varies depending on how much information you have to recover. In fact, it is easier to recover the entire database than a single table because it is easier to use an update log for a database than a single table.

4.1 Restore the entire database

First, if the database you want to restore is a MySQL database that contains authorization tables, you need to run the server with the--skip-grant-table option. Otherwise, it complains that the authorization table cannot be found. After you have recovered the table, execute mysqladmin flush-privileges tell the server to mount the authorization label and use them.

Copy the contents of the database directory to somewhere else, if you need them later.

Reload the database with the latest backup file. If you use mysqldump to generate the file, use it as a MySQL input. If you copy files directly from the database to the database directory, then you need to close the database before copying it, and then restart it.

Use the update log to repeat the query that modifies the database table after the backup. For any applicable update logs, use them as input to MySQL. Specifying the--one-database option allows MySQL to execute only queries that you are interested in restoring the database. If you know that you need to use all the update log files, you can use this command in the directory containing the log:

% 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 by the order in which they were generated by the server (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.) ）

It is likely that you will be using a few update logs. For example, the update log that has been generated since you backed up is named update.392, update.393, and so on, so you can rerun:

%mysql--one-database db_name < update.392
%mysql--one-database Db_name < update.393
.....

If you are implementing a recovery and using the update log to recover information that is missing due to a bad recommended drop DATABASE, drop table, or DELETE statement, make sure to delete the statements from it before applying the update log.

4.2 Recover a single table

Recovering 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 need to extract them from the related rows and use them as MySQL input. This is the easy part. The hard part is pulling the fragments out of the update log that is used only in the table. You will find that the Mysql_find_rows utility is useful for extracting multiline queries from the update log.

Another possibility is to use another server to recover the entire database, and then copy the table files you want to the original database. It could be really easy! When you copy the file back to the database directory, make sure the server for the original database shuts down.