MYSQL check data tables and repair data tables _ MySQL

MYSQL checks the data table and repairs the data table. when the database is running, data corruption may occur due to human factors or some force majeure factors. Therefore, to protect data security and minimize downtime, we need to develop a detailed backup/recovery plan and regularly test the effectiveness of the plan.

This chapter describes how to back up, maintain, and repair databases based on the running mechanism of the MySQL server and the tools provided.

The following are some preventive measures:

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

Start the binary change log of the database server. The system overhead of this function is very small (about 1%). We have no reason not to do so.

Periodically check data tables to prevent data from being burned.

Regularly back up backup files to prevent invalid backup files.

Put the MySQL data directory and backup file in two different drives to balance disk I/O and increase data security.

Check/repair data tables
To maintain a data TABLE, it is best to issue the check table (CHECK data TABLE) or repaire table (repair data TABLE) command, so that the MySQL server automatically locks the TABLE to coordinate the data read/write consistency in the data TABLE.

You can also use the myisamchk tool to maintain data tables. However, it directly accesses the relevant data table files and does not pass through the server. Therefore, you need to coordinate the read/write consistency of data tables. Follow these steps to use myisamchk to check the data table:

Connect to the server using a mysql client program, and issue the lock table command to LOCK the data TABLE in read-only mode.

% Mysql
Mysql> use db
Mysql> lock table table_name READ; # LOCK a TABLE in READ-only mode
Mysql> flush table table_name; # Close the data TABLE file and write the information in the memory to the disk.
Keep the preceding status unchanged. open a shell window and run the following command to maintain (check) the data table.

% Myisamchk table_name

If the mysql session is exited without the above status, the table lock will be automatically canceled.

After the maintenance is complete, switch back to the shell window in mysql status and issue the following command to unlock the table.

Mysql> unlock tables;
Follow these steps to use myisamchk to repair a data table:

To fix the problem, you must lock the data table in read/write mode. the command is as follows:

% Mysql
Mysql> use db
Mysql> lock table table_name WRITE; # LOCK data tables in read/WRITE mode
Mysql> flush table table_name;
Maintain the mysql client connection status, switch to the second shell window, and run the repair command:

% Myisamchk -- recover table_name

You are advised to back up the data file before running the repair command.

After the repair is complete, switch back to the mysql client connection window and run the following command to unlock the data table:

Mysql> flush table table_name; # enable the server to detect the newly generated index file
Mysql> unlock table;
You can also use the following command to lock all tables. after locking, all users can only read and write data, so that we can securely copy data files.

Mysql> flush tables with read lock;
The following is the unlock statement:
Mysql> unlock tables;