How to fix MySQL database table after corruption

Source: Internet
Author: User

Steps:
1.sql statement: Check table tabtest;
If the result says status is OK, then there is no fix, if there is an error
2.Linux Execution:
Myisamchk-r-q/var/lib/mysql/db/test. MYI
3.sql Statement: Repair table tabtest;

4.sql statement: Check table tabtest; Status is OK, fix it.

On non-Linux: Direct

 


Reference:
There are two ways to use MySQL's check table and repair table SQL statements. Another approach is to use multiple myisamchk provided by MySQL, isamchk The Data detection recovery tool. The former is relatively simple to use. Recommended use. 1. Check table and Repair table login MySQL terminal: mysql-uxxxxx-p dbnamecheck table Tabtest, if the result says status is OK, then do not repair, if there is error, you can use: R Epair table tabtest; fix, after repair, can be checked with the Check Table command. You can also use the Check/repair feature in the new version of phpMyAdmin. 2. Myisamchk, Isamchk where myisamchk applies to data tables of the MyISAM type, and ISAMCHK applies to data tables of the ISAM type. The main parameters of these two commands are the same, and generally the new system uses MyISAM as the default data table type, which is illustrated with Myisamchk as an example. When you find a problem with a datasheet, you can use: Myisamchk tablename. Myi is tested and can be used if a repair is needed: myisamchk-of tablename. Myi the detailed parameter description of the MYISAMCHK, see its use help. Note that when making modifications, you must make sure that the MySQL server does not have access to the data sheet, and that it is best to shutdown the MySQL server in case of an inspection.
The 
 

1.MyISAM table is corrupted with the Myisamchk command to fix myisamchk-r-Q followed by your table name. Myi This command is dedicated to repairing Myism engine database tables. For example, if your database directory is/var/lib/mysql/db one of the tables is test, the method for repairing the test table is Myisamchk-r-q/var/lib/mysql/db/test. Myi If you fix all the tables, myisamchk-r-q/var/lib/mysql/db/* with this command. MYI2. InnoDB table Corruption InnoDB has an internal recovery mechanism, and if the database crashes, InnoDB attempts to repair the database by running the log file from the last timestamp. In most cases, the repair succeeds and the whole process is transparent. If InnoDB self-repair fails, then the database will not start. Before proceeding, browse to the MySQL log file to determine that the database crashed due to corruption of the InnoDB table. One way is to update the InnoDB log file counter to skip the query that caused the crash, in which case it will cause inconsistencies in the data and often interrupt the master-slave replication. Once you have determined that MySQL cannot start because the InnoDB table is damaged, you can fix it in the following 5 steps: 1. Edit the/etc/my.cnf file and add the following line: Innodb_force_recovery = 42. You can restart the database at this point, in the InnoDB _force_recovery configuration, all insert and update operations will be ignored; 3. Export all data tables; 4. Close the database and delete all data table files and directories, and then run mysql_install_db to create the MySQL default data table; 5. In/etc/ MY.CNF Delete Innodb_force_recovery this line, and then start MySQL (then mysql normal boot); 6. Recover all data from files backed up in step 3rd. The innodb_force_recovery can be set to 1-6, and the large number contains the effect of all previous numbers. When you set a parameter value greater than 0, you can perform a select,create,drop operation on the table, but the insert,update or delete operation is not allowed.  
1 (srv_force_ignore_corrupt): Ignores the corrupt page that is checked. 2 (Srv_force_no_background): Prevents the main thread from running, as the main thread needs to perform full purge operations, which can cause crash. 3 (Srv_force_no_trx_undo): Transaction rollback operation is not performed. 4 (Srv_force_no_ibuf_merge): Do not perform insert buffer merge operation. 5 (Srv_force_no_undo_log_scan): Do not look for the log, InnoDB storage engine will treat uncommitted transactions as committed. 6 (Srv_force_no_log_redo): Do not roll forward operations.

How to fix MySQL database table after corruption

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.