MySQL Tutorial: Checking data tables and repairing data tables

Database in operation, because of human factors or some force majeure factors caused by data corruption. So in order to protect data security and minimize downtime, we need to develop detailed backup/recovery plans and regularly test the effectiveness of the plan.

This chapter describes how to make database backup, maintenance and repair, combining the running mechanism of MySQL server and the tools provided.

Here are a few precautions:

Develop a database backup/recovery plan and carefully test the plan.

Starting the database server's binary change log, which has a very small system overhead (about 1%), we have no reason not to.

Check the data sheet regularly to prevent from burning.

Backup files are backed up regularly to prevent files from being invalidated.

Separate MySQL data directories and backup files into two different drives to balance disk I/O and increase data security.

Check/Repair Data sheets

Maintenance of a datasheet is best done by issuing a check table (check datasheet) or Repaire table (repair Datasheet) command so that the MySQL server automatically locks the table to reconcile the read-write consistency of the data in the datasheet.

Myisamchk tool can also be used to do data table maintenance, but it directly access to the relevant data table files, not through the server, so you need to artificially coordinate data table data reading and writing consistency problem. The steps to check the datasheet using Myisamchk are as follows:

Connect the server with the MySQL client program and issue a lock table command to lock the datasheet in read-only mode.

% MySQL

mysql> Use DB

mysql> LOCK TABLE table_name READ; #以只读方式锁定表

mysql> FLUSH TABLE table_name; #关闭数据表文件, and write the information in memory to disk

To keep the above state from exiting, open a shell window to maintain (check) the data table under command.

% MYISAMCHK table_name

If you do not maintain the above state and exit the MySQL session, the table lock is automatically canceled.

Maintenance complete, switch back to the MySQL State Shell window and issue the following command to unlock the table.

Mysql> UNLOCK TABLES;

The steps to repair the datasheet using Myisamchk are as follows:

The data table must be locked in read/write mode for the repair operation, as follows:

% MySQL

mysql> Use DB

mysql> LOCK TABLE table_name WRITE; Lock data table #以读/write mode

mysql> FLUSH TABLE table_name;

To maintain the MySQL client connection status, switch to the Second Shell window and run the repair command:

% MYISAMCHK--recover table_name

It is best to back up the data file before running the repair command.

When the repair is complete, switch back to the MySQL Client Connection window and run the following command to unlock the datasheet:

mysql> FLUSH TABLE table_name; #使服务器觉察新产生的索引文件

Mysql> UNLOCK TABLE;

You can also use the command to lock all tables, and after locking, all users can only read and write data, which allows us to securely copy data files.

Mysql> FLUSH TABLES with READ LOCK;

The following is a unlock statement:

Mysql> UNLOCK TABLES;