MySQL repairs corrupted tables and writes scripts to automatically fix them

The mysql process is killed in a write, the computer is accidentally shut down, and hardware errors can cause mysql table damage.

Mysql table repair methods

1. mysqlcheck for table repair

Use the mysqlcheck command to repair the table

# Mysqlcheck-uuser-ppassword database table-c # check whether a single table is damaged

# Mysqlcheck-uuser-ppassword database-c # Check the tables in the entire database for corruption

First, check that the tables in the database are damaged. If the table is located damaged, you can repair the table directly.

# Mysqlcheck-uuser-ppassword database table-r # repair a data table

# Mysqlcheck-uuser-ppassword database-r # Fix the entire database

For more parameters, see mysqlcheck-help.

2. myisamchk fixed the mysql table

Myisamchk is a very useful tool for MyISAM Table maintenance. You can use the myisamchk utility to obtain information about database tables or to check, repair, and optimize them. Myisamchk applies to MyISAM tables (tables corresponding to. MYI and. MYD files ).

Description of the Myisamchk Option

-Debug = debug_options,-# debug_options
Output the debugging record file. The debug_options string is usually 'd: t: o, filename '.

-Silent,-s
Silence mode. Write output only when an error occurs.

-Wait,-w
If the table is locked, it does not indicate an error termination, but waits until the table is unlocked before it continues.
If you do not use-skip-external-locking, you can use myisamchk to check the table at any time. When you check the table, all clients that attempt to update the table will wait until myisamchk is ready to continue.
Note that if you use the-skip-external-locking option to run mysqld, you can only use another myisamchk command to lock the table.

-Var_name = value
You can use the-var_name = value option to set the following variables:

-Check,-c
Check table errors. If you do not specify the operation type option explicitly, This is the default operation.

-Check-only-changed,-C
Only the tables with changes after the last check are checked.

-Extend-check,-e
Check the table carefully. If the table has many indexes, it will be quite slow.

-Fast,-F
Only the tables that are not properly closed are checked.

-Force,-f
If myisamchk finds any errors in the table, it will be automatically repaired.

-Information,-I
Print the statistical information of the checklist.

-Medium-check,-m
This is faster than-extend-check. Only 99.99% errors can be found.

-Update-state,-U
Save the information in the. MYI file to indicate the table check time and whether the table crashes. This option is used to make full use of the-check-only-changed option,
However, if the mysqld server is using a table and is running with the-skip-external-locking option, this option is not used.

-Read-only,-T
Do not mark the table as checked. It is useful if you use myisamchk to check tables that are being used by other applications but are not locked.

-Backup,-B
Back up the. MYD file as a file_name-time.BAK

-Character-sets-dir = path
Character Set installation directory.

-Correct-checksum
Correct the checksum information of the table.

-Data-file-length = len,-D len
Maximum data file length

-Extend-check,-e
To restore each row from the data file. A large number of spam rows are usually found. Do not use this option unless you ignore the consequences.

-Force,-f
Overwrite the old intermediate file (the file name is similar to tbl_name.TMD), instead of interrupting

-Keys-used = val,-k val
For myisamchk, this option value is a bit value, indicating the index to be updated. Each binary digit of the option value corresponds to an index of the table, and the first index corresponds to 0.
If the option value is 0, all index updates are disabled to ensure quick insertion. You can use myisamchk-r to reactivate disabled indexes.

-Parallel-recover,-p
It is used in the same way as-r and-n, but uses different threads to create all keys in parallel.

-Quick,-q
Quickly fix the problem without modifying the data file.

-Recover,-r
It can fix almost all problems, unless the unique key is not unique (this is very impossible for the MyISAM table ). If you want to restore a table,
This is the first option to try. If the myisamchk report table cannot be restored using-r, you can only try-o.
-R fails when it is unlikely, and the data file remains intact ).

-Safe-recover,-o
Use an old restoration method to read, read all rows in sequence, and update all index Trees Based on the rows found. This is slower than-r,
But it can handle situations where-r cannot handle. This restoration method uses less disk space than-r. In general, you should first use-r for repairs. If-r fails, use-o.

-Sort-recover,-n
Force myisamchk to parse key values by sorting, even if the temporary file may be large.

-Analyze,-
Analyze the distribution of key values. This allows the connection optimizer to better select the order in which the table should be joined and the key to be used to improve the connection performance.
To obtain distribution information, run the myisamchk-description-verbose tbl_name command or the show keys from tbl_name statement.

-Sort-index,-S
Sort the index tree blocks in ascending order. This will optimize the search and make the table scan with key values faster.

-Set-auto-increment [= value],-A [value]
Force the AUTO_INCREMENT number to be used for the new record starting from the given value (or a higher value should be used if there is already a record with the AUTO_INCREMENT value ).
If no value is specified, the AUTO_INCREMENT number of the new record should add 1 to the maximum value of the current table.

-Description,-d
Print descriptive information about the table.

Checklist

[Root @ nagios test] # myisamchk-e wpusers. MYI
Checking MyISAM file: wpusers. myi
Data Records: 1 deleted blocks: 0
-Check file-size
-Check record Delete-chain
-Check key Delete-chain
-Check Index reference
-Check data record references index: 1
-Check data record references index: 2
-Check data record references index: 3
-Check records and index references

Myisanchk repair table

[Root @ Nagios test] # myisamchk-r wpusers. myi
-Recovering (With sort) MyISAM-Table 'wpusers. myi'
Data Records: 1
-Fixing Index 1
-Fixing index 2
-Fixing index 3

3. automatically fix MySQL table scripts

#! /Bin/bash
# This script edit by badboy connect leezhenhua17@163.com
# This script used by repair tables
Mysql_host = localhost
Mysql_user = root
Mysql_pass = 123456
Database = test

Tables = $ (mysql-h $ mysql_host-u $ mysql_user-p $ mysql_pass $ database-A-Bse "show tables ")
For arg in $ tables
Do
Check_status = $ (mysql-h $ mysql_host-u $ mysql_user-p $ mysql_pass $ database-A-Bse "check table $ arg" | awk '{print $4 }')
If ["$ check_status" = "OK"]
Then
Echo "$ arg is OK"
Else
Echo $ (mysql-h $ mysql_host-u $ mysql_user-p $ mysql_pass $ database-A-Bse "repair table $ arg ")

Fi
Echo $ (mysql-h $ mysql_host-u $ mysql_user-p $ mysql_pass $ database-A-Bse "optimize table $ arg ")
Done

I hope to help you. If there is any error in this article, please point it out.

Original works can be reprinted. During reprinting, you must mark the original source, author information, and this statement in hyperlink form. Otherwise, legal liability will be held. Http://www.colderboy.com/archives/28.html