Mysql entry-level series: use MYSQL to back up and recover data _ MySQL

Mysql entry Series: There are many reasons for using MYSQL to back up and recover data to the database, and the degree of destruction varies. If you are lucky, it may be a small destruction of one or two tables (for example, if your machine is temporarily suspended due to power failure ). If this is not the case, you may need to replace the entire data directory (for example, if a disk crashes and the data directory is on it ). In other cases, recovery is also required. for example, when a user mistakenly deletes a database or table or the table content. Whatever the cause of these unfortunate events, they must be restored.

If tables are destroyed but not lost, try to use myisamchk or isamchk to fix them. If the repair utility can fix them, there is no need to use backup files at all. The table repair process will be discussed in Chapter 13th. If tables are lost or cannot be repaired, you need to restore them.

The recovery process includes two sources of information: backup files and update logs. The backup file restores the table to the status when the backup is performed. However, during the time of backup and failure, the table is usually modified. The update log contains the queries used to complete these changes. You can repeat these queries by using the update log as the input to mysql (that is why

The reason the log is allowed to be updated. If you have not made the update log valid, do it now and generate a new backup before further reading ).

The recovery process varies according to the amount of information that must be restored. In fact, it is easier to restore the entire database than to restore a single table, because it is easier to update logs for database applications than to restore a single table.

Restore the entire database

First, if the database to be restored is a mysql database containing an authorization table, you must use the--s k I p-g r a n t-tables option to run the server. Otherwise, the server will complain that the authorization table cannot be found. After the table is restored, execute mysqla d m I n flush-privileges to tell the server to load the authorization table and start it with them.

Copy the contents of the original database directory to other places. For example, you may use them later for post-mortem examination analysis ).

Reload the database with the latest backup file. If you want to use files loaded by mysqldump, you need to use them as mysql input. If you plan to use files directly copied from the database (for example, tar or c p), copy them directly to the Database Directory. However, in this case, you should disable the server before copying these files, and then restart it.

After the database is backed up with the update log, the query of the database table is modified. All available update logs can be used as mysql input. Specify the -- one-database option so that mysql can only query the database to be restored. If you know that you need to use all the update log files, you can use

The following commands:

% Ls-t-r-l update. (0-9) * | xargs cat | mysql -- one-database db_name

The ls command generates a single column list of update log files. the update log files are sorted by the order generated by the server. (you must know that if you modify any files, the sorting order will change, this will cause the update log to be used in the wrong order ).

You may have to use some update logs. For example, if the logs generated since the backup are named up date. 3 9 2 and update.393, you can run the following commands again:

% Mysql -- one-database db_name <updata.392

% Mysql -- one-database db_name <updata.393

...

If you are running recovery and intend to use the update log to recover the information lost due to the incorrect drop data B A S E, D R O P TABLE or DELETE statement, ensure that these statements are deleted from the update log first.

Restore a single table

It is very difficult to restore a single table. If there is a backup file generated using mysqldump and it does not exactly contain the table data you want, you need to extract related rows and use them as mysql input, which is easier. The difficulty is to extract the segment of the update log applied to the table. You will find that the mysql_find_rows utility is helpful in this regard. it can extract multiple rows from the update log for query.

Another possibility is to use another server to restore the entire database, and then copy the file of the table to the original database. This is actually easy! When copying files back to the Database Directory, ensure that the server of the original database is disabled.