Mysql database table Repair Myisam_mysql

One: MySQL myisam table damage Reason summary:

1, the server suddenly power off causing data file corruption, forced shutdown, did not first shut down the MySQL service; the mysqld process was killed while writing the table.
2, disk damage.
3, the server crashed.
4, the MySQL itself bug.

Second: MySQL in the MyISAM table damage Symptom Summary:

1, the query data times out of error: Incorrect key file for table: ' ... '. Try to repair it
2. The query cannot find rows in the table or return incomplete data.
3, error:table ' ... ' is marked as crashed and should as repaired.
4, open the table failed: Can ' t open file: ' xxx. Myi ' (errno:145).

Third: MySQL in the MyISAM Table damage Prevention:

1, regular inspection MyISAM table. You can use Myisamchk, you can also use Mysqlcheck, and you can use check table.
2, after a large number of updates or delete operations, using Mysqlcheck or optimize table to optimize tables, so that both reduce file fragmentation, but also reduce the probability of table damage. 3, before shutting down the server, first close the mysqld (normal shutdown service, do not use kill-9 to kill the process).
4, the use of UPS power to avoid sudden power failure.
5, using the latest stable release version of MySQL, reduce the MySQL itself bug caused the table damage.
6, raid on the disk, reduce disk errors and improve performance.
7, the database server best run only mysqld and necessary other services, do not run other business services, so as to reduce the crash caused the possibility of table damage.

Four: MySQL in the MyISAM table damage Repair Summary:

1, if the mysqld has been down, and cannot start, then you can use the Mysiamchk tool to repair. This tool can be used only when the MYSQLD service is not started. The tool can examine and analyze the repair MyISAM table.
2, if the mysqld is still running, or can be restarted, then the Mysqlcheck tool can be repaired. or fix it directly via MySQL's built-in fix SQL statement: CHECK table,repair table, analyse table,optimize table. Both of these methods can also be used to repair the table. Each of the above two methods have the application scene.

MySQL data table damage ingenious repair before you fix the MySQL table, you first need to know which table is out of the question.

You can use root to find the problematic table name in Information_schema

Select Table_schema as Db_dbname,table_name from information_schema.tables where engine is null;

You can see from the Event Viewer whether there are

Application Log--type "error"--the error log with the event source "Mysql", presumably

1 xxxxx:table '. xxxx ' is marked as crashed and should as repaired

Or look directly in the phpMyAdmin to see which tables are out of the question, such as "in use."

If you find the problem table, you can use the command to fix the MyISAM table in the MySQL database

1, check, repair

For example, there is a problem with the test table for ABC

Copy Code code as follows:

# mysql-u root-p ' abc '; Log in to the MySQL console and enter the ABC database
mysql> Check table test; If there is a problem msg_type will have warning or error. If there's no problem, the result is OK.
mysql> Repair table test; Fix the test table (multiple table names separated by commas)
mysql> Check table test; Check again to confirm that the repair was successful

2. Myisamchk, Isamchk

Where Myisamchk is suitable for myisam types of data tables, Isamchk applies to ISAM types of MySQL data tables. The main parameters of these two commands are the same, and generally the new system uses MyISAM as the default datasheet type, which is illustrated with Myisamchk as an example. When you find a problem with a data table, you can use:

MYISAMCHK TableName. Myi

For testing, if you need to fix it, you can use:

MYISAMCHK-OF TableName. Myi

For a detailed parameter description of MYISAMCHK, see its use help. Need to be aware of when making changes must ensure that the MySQL server does not access this MySQL data table, insurance is the best in the case of the MySQL server shutdown off.

Mysqladmin-uroot-p shutdown

In addition, you can put the following command in your rc.local to start the MySQL server before:

[-x/tmp/mysql.sock] &&/pathtochk/myisamchk-of/data_dir/*/*. Myi

The/tmp/mysql.sock is the sock file location that MySQL listens to, the user that uses RPM installs should be/var/lib/mysql/mysql.sock, to use the source code installs is/tmp/ Mysql.sock can be changed according to their actual situation, and Pathtochk is Myisamchk location, Data_dir is your MySQL database storage location.

To be aware, if you are going to put this command in your rc.local, you must confirm that the MySQL server must not be started when executing this instruction!

3, Detect and repair all databases (table)

Copy Code code as follows:

Mysqlcheck-a-o-r-P
Mysqlcheck--all-databases-r