MySQL database table fixes MyISAM

1. Summary of reasons for MyISAM table damage in MySQL:

1. Data files are damaged due to sudden power failure of the server; Forced shutdown without first shutting down mysql service; The mysqld process was killed while writing the table.
2, disk damage.
3. The server crashed.
4. bug itself.

2: Summary of symptoms of MyISAM table damage in MySQL:

Incorrect key file for table: '... 'Try to repair it
2. The query cannot find rows in the table or return incomplete data.
S 29en: S 30en... 'is marked as crashed and should be repaired
4. Table opening failed: Can't open file: '××× MYI' (errno: 145)

3: Prevention of MyISAM table damage in MySQL:

1. Check MyISAM table regularly. You can use myisamchk, mysqlcheck, or check table.
2. After a large number of update or delete operations, mysqlcheck or optimize table are used to optimize the table, which not only reduces file fragmentation, but also reduces the probability of table corruption. 3. Before shutting down the server, shut down mysqld (normally close the service, do not use kill-9 to kill the process).
4. Use ups power supply to avoid sudden power cut.
5. Use the latest stable release version mysql to reduce the table damage caused by mysql itself.
6. raid for disk to reduce disk errors and improve performance.
7, the database server should only run mysqld and other necessary services, do not run other business services, so as to reduce the possibility of table damage caused by the crash.

4. Repair summary of MyISAM table damage in MySQL:

1. If mysqld is down and cannot be started, the mysiamchk tool can be used to fix it. This tool is only available when the mysqld service is not started. The tool can examine and analyze repair MyISAM tables.
2. If mysqld is still running or can be restarted, you can fix it through the mysqlcheck tool. Or fix it directly with mysql's built-in repair SQL: CHECK TABLE, REPAIR TABLE, ANALYSE TABLE, OPTIMIZE TABLE. These two methods can achieve the same repair effect on the table. The above two methods have their own application scenarios.

Smart Fix for MySQL Table Corruption Before fixing the mysql table, first know which table is the problem

You can log in with root and look for the table name in question in information_schema

select TABLE_SCHEMA as DB_DBNAME,TABLE_NAME from INFORMATION_SCHEMA.tables where engine is null;

You can check if there is one in the event viewer

Application logs -- type "error "-- error logs from "Mysql", roughly

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

Or you can go directly to phpmyadmin and see what's wrong with the table like "in use"

If the problem table is found, use the command 1 to fix the MYISAM table in the mysql database

1, check, repair

For example, the test table for abc has a problem

# mysql -u root -p 'abc';   // The login mysql Console, and enter abc The database  

mysql> check table test;  // If there's a question Msg_type There will be warning or error . There are no problems with the results Status is OK 

mysql> repair table test;  //  repair test Table (multiple table names separated by commas)  

mysql> check table test; //  Check again to make sure the fix was successful 

2. myisamchk, isamchk

Where myisamchk applies to MYISAM type data tables and isamchk to MySQL type data tables. The two commands have the same main arguments, and most new systems use MYISAM as the default table type, illustrated here by the example of myisamchk. When you find a problem with a data table, use:

myisamchk tablename.MYI

Check and, if you need to fix it, use:

myisamchk -of tablename.MYI

For a detailed description of the myisamchk parameters, see its usage help. It is important to note that the MySQL server does not access the MySQL data table when making modifications, and it is better to drop the MySQL server Shutdown when testing in the case of safety.

mysqladmin -uroot -p shutdown

In addition, you can put the following command in your ES166en.local before starting the MySQL server:

[ -x /tmp/MySQL.sock ] & & /pathtochk/myisamchk -of /DATA_DIR/*/*.MYI

The/tmp MySQL sock is MySQL listening Sock file location, for users of RPM installation should be/var/lib/MySQL/MySQL sock, to use the source code is installed/tmp/MySQL sock may change according to their own actual situation, and the pathtochk is myisamchk location, DATA_DIR is where your MySQL database resides.

Note that if you intend to put this command in your ES198en.local, make sure that the MySQL server does not start when executing this command!

3. Detect and repair all databases (tables)

MySQLcheck -A -o -r -p 

mysqlcheck --all-databases -r