How to fix bad tables in MySQL

Sometimes the database is damaged due to power loss or other reasons. We can use the MySQL command to quickly fix all databases or specific databases. For example:

Sometimes the database is damaged due to power loss or other reasons. We can use the MySQL command to quickly fix all databases or specific databases. For example:

Sometimes the database is damaged due to power loss or other reasons. We can use the MySQL command to quickly fix all databases or specific databases. For example

Check to optimize and fix all databases:

# Mysqlcheck-A-o-r-p
Enter password:
Database1 OK
Database2 OK
----------
Fix a specified database
# Mysqlcheck-A-o-r Database_NAME-p
You can.
In addition, you can use myisamchk or isamchk to fix a table.
Here, myisamchk applies to MYISAM data tables, while isamchk applies to ISAM data tables. The main parameters of these two commands are the same. Generally, new systems use MYISAM as the default data table type. Here we use myisamchk as an example. When a problem occurs in a data table, you can use:
Myisamchk tablename. MYI
To fix the issue, use:
Myisamchk-of tablename. MYI
For detailed parameter descriptions of myisamchk, see its help. Make sure that the data table is not accessed by the MySQL server when you modify the data table. It is safe to shut down the MySQL server during detection.
In addition, you can place the following command in your rc. local to start the MySQL server:
[-X/tmp/mysql. sock] & amp;/pathtochk/myisamchk-of/DATA_DIR/*. MYI
/Tmp/mysql. sock is the location of the Sock file monitored by MySQL. For RPM-based installation, it should be/var/lib/mysql. sock. For source code installation, it is/tmp/mysql. sock can be changed based on actual conditions, while pathtochk is the location where myisamchk is located, and DATA_DIR is the location where your MySQL database is stored.

1. Simple Repair Mode
Myisamchk-r-q path/database/bad table. MYI
Note:-r ---- recovery mode-q ---- quick fix

2. Use the Security Repair Mode

Myisamchk -- safe-recover path/database/bad table. MYI

3. Difficult Repair Modes

If the first 16 K block in the index file is damaged, or contains incorrect information, or if the index file is lost, you should only go to this stage. In this case, it is necessary to create a new index file. Follow these steps:

Move data files to a safer place.
Use the table description file to create new (null) data and index files:
Shell> mysql db_name
Mysql> Delete FROM tbl_name;
Mysql> quit

Copy the old data file to the newly created data file. (Do not just move the old file back to the new file; keep a copy to prevent some errors .)
Return to Stage 2. Now myisamchk-r-q should work. (This should not be an infinite loop ).

4. Very difficult Repair Modes

Only when the description file is damaged should you reach this stage. This should never happen, because after the table is created, the description file will not change.

Recover the description file from a backup and return to phase 3. You can also restore the index file and return to Stage 2. For the latter, you should start with myisamchk-r.
If you do not have a backup, but know exactly how the table is created, create a copy of the table in another database. Delete the new data file and move the description and index file from other databases to the damaged database. This gives you a new description and index file, but leaves the data file alone. Go back to Stage 2 and try to recreate the index file.

5. Optimize the table structure
Myisamchk-r table
You can also use SQL statements to OPTIMIZE TABLE.