How to use the MySQL database for backup data recovery

Database destruction occurs for many reasons and varies in degree. If you're lucky, it could be a small damage to one or two tables (for example, if your machine is temporarily shut down due to a power outage). If this is not the case, you may need to replace the entire data directory (for example, if a disk is paralyzed and the data directory is on it). In other cases, recovery operations are also required, such as when a user mistakenly deletes a database or table, or when the contents of a table are incorrectly deleted. Whatever the causes of these unfortunate events, they need to be restored.

If the table is destroyed but not lost, try to fix them with myisamchk or isamchk. If the repair utility can fix them, there is no need to use backup files at all. If the tables are missing or cannot be repaired, they need to be restored.

The recovery process includes two information sources: Backup files and update logs. The backup file restores the table to its state when the backup was made. However, during the period of backup and failure, the table is usually modified. The update log contains the queries that are used to complete these modifications. You can repeat these queries by using the update log as input to MySQL (which is why you need to update the log). If you have not made the update log valid, do so now and generate a new backup before further reading.

The recovery process varies according to the amount of information that must be recovered. In fact, it is easier to recover an entire database than to recover a single table because it is easier to apply an update log to a database than to a table.

　　Restore the entire database

First, if the database you are restoring is a MySQL database with an authorization table, you will need to run the server using the--skip-grant-tables option. Otherwise, the server will complain that the authorization table cannot be found. After the table is recovered, execute mysqladmin flush-privileges to tell the server to load the authorization table and use them to start.

Copy the contents of the original database directory to another location. For example, you might later use them for postmortem analysis of the crash Table (Post-mortem examination).

Reload the database with the latest backup file. If you plan to use files that are loaded by mysqldump, you will need them as input to MySQL. If you intend to use files that are copied directly from the database (for example, tar or C p), copy them directly back to the database directory. However, in this case, you should shut down the server before copying the files, and then restart it.

Redo the query with the update log that modifies the database table after the backup. For all available update logs, use it as the input to MySQL. Specify the--one-database option so that MySQL executes queries only on the database that you want to recover. If you know that you need to use all of the update log files, you can use the following commands in the directory that contains the logs:

% ls-t-r-l update. (0-9) * | Xargs Cat | Mysql--one-database db_name

The LS command produces a single-column list of update log files, and the update log files are sorted according to the order in which the server was generated (you know, if you modify any of these files, the order of the sort will change, which will cause the update log to be used in the wrong order).

You will most likely have to use some update logs. For example, if the log generated since the self-owned is named update.392, pdate.393, and so on, you can rerun the commands in them:% mysql--one-database db_name < updata.392% Mysql--one-database Db_name < updata.393 ...

If you are running a recovery and intend to use the update log to recover information that was lost due to the blunder drop DATA BASE, droptable, or DELETE statement, make sure that you delete the statements from the update log first.

　　To recover a single table

It is difficult to recover a single table. If you have a backup file generated by mysqldump and it just doesn't contain the table data you want, it's easier to extract the related rows and use them as MySQL input. The difficulty is to extract the fragment of the update log that is applied to the table. You will find that the Mysql_find_rows utility is helpful in this way, and it can extract multiple rows of queries from the update log.

Another possibility is to use another server to recover the entire database, and then copy the desired table's files to the original database. This is actually very easy! When copying files back to the database directory, ensure that the original database's servers are shut down.