MySQL Data table Damage Repair command

Symptom of table corruption

A typical symptom of a damaged table is as follows:
1. When selecting data from a table, you get the following 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 ' P ' is marked as crashed and should to be repaired.
4, open the table failed: Can ' t open file: ' xxx. Myi ' (errno:145).

mysql command repair database table

Repair with "Repair table"

Syntax: Repair table name [options]
The options are as follows:
Quick is the fastest when the datasheet has not been modified
EXTENDED trying to recover each row of data, will produce some garbage data rows, helpless
Use_frm used in. myi files are missing or damaged in the head. Use the definition of. frm to rebuild the index

In most cases, it's easy to use the "repair table tablename" without the option to fix the problem. But when. When the Myi file is lost or the head is damaged, this way, when the index file is lost or its head is damaged, the USE_FRM option is required in order to fix it using the relevant definition file.

REPAIR table is used to repair damaged tables

The code is as follows	Copy Code
REPAIR table ' table_name ' repair sheet (valid only for the MyISAM engine) OPTIMIZE table ' table_name ' optimization tables

Other types of corruption need to be recovered from the backup.

1,repair TABLE SQL statement (MySQL service must be running).
2, the command Mysqlcheck (the MySQL service can be in run state).
3, Command Myisamchk (the MySQL service must be stopped or the table being manipulated is inactive).

the restoration of MySQL database

Locate the Bin/myisamchk tool for the MySQL installation directory and enter at the command line:

The code is as follows	Copy Code
Myisamchk-c-R. /data/tablename/posts. Myi The Myisamchk tool will then help you recover the index of the datasheet. It doesn't seem like you need to restart MySQL, so the problem is solved. For example Check, optimize, and repair all databases by: # mysqlcheck-a-o-r-P Repair the specified database with the # mysqlcheck-a-o-r database_name-p

Can

phpMyAdmin Repair Table

Using phpMyAdmin to repair the MySQL database

First, modify the permissions of the phpMyAdmin.

chmod 775 phpMyAdmin//Open Modify permissions, then http://ip/***/phpmyadmin into the database management interface, login account and password that is the database when the account number and password.

chmod phpMyAdmin//repair must close the database modification interface

The server unexpectedly reboots or power off, MySQL instability, etc., may cause data table damage. This tutorial briefly describes how to use phpMyAdmin to repair a datasheet.

1, Login phpMyAdmin, enter the phpMyAdmin data list page;



2, select a data table before the box;



3, below the page "selected item" Drop-down Select "Fix table";

If we can't fix it, we can only recover it with backup data, and if we don't have backup data we could try to recover it with binary logs.

The use of ysqlbinlog tools , you can see the MySQL help manual. There's a detailed use in it,
In this example, the emphasis is on the use of--start-position parameters and--stop-position parameters.

?--start-position=n
Starts reading from the event that the 1th position in the binary log equals the n parameter.
?--stop-position=n
Stops reading from the event that the 1th position equals and is greater than the n parameter in the binary log.

OK, start now, to start binary logging,
To first add in the mysqld of the My.cnf/my.ini file.
log-bin= Log Name
Here, I'm setting it Log-bin=liangck
And then start the MySQL service, because I'm using the Windows system,
So execute net start mysql command.

Then, in a test database, create a table and add a record.

The code is as follows

Copy Code

SQL Code Mysql>createtabletest (Idintauto_incrementnotnullprimarykey, Valint,datavarchar (20)); Mysql>insertintotest (Val,data) VALUES ("Liang"); Query Ok,1row Affected (0.03SEC) Mysql>insertintotest (Val,data) VALUES (' Jia '); Query Ok,1row Affected (0.08SEC) Mysql>insertintotest (Val,data) VALUES (' hui '); Query Ok,1row Affected (0.03SEC) Mysql>flush logs;--produces a second log file query Ok,0rows affected (0.09SEC) Mysql>insertintotest (Val,data) VALUES ("AAA"); Query Ok,1row Affected (0.05SEC) Mysql>insertintotest (Val,data) VALUES ("BBB"); Query Ok,1row Affected (0.03SEC) Mysql>insertintotest (Val,data) VALUES ("CCC"); Query Ok,1row Affected (0.03SEC) mysql>deletefromtestwhereidbetween4and5;--Delete Records Query ok,2rows Affected (0.05SEC) Mysql>insertintotest (Val,data) VALUES ("ddd"); Query Ok,1row Affected (0.03SEC) Mysql>flush logs;--produces a third file file Query ok,0rows Affected (0.11SEC) Mysql>insertintotest (Val,data) VALUES ("dddd"); Query Ok,1row Affected (0.05SEC) Mysql>insertintotest (Val,data) VALUES ("Eeee"); Query Ok,1row Affected (0.03SEC) mysql>droptabletest;--Delete Table Query Ok,0row Affected (0.05SEC)

??????????????????????????????????
OK, now the test data has been built, what is the requirement?
is to recover all the data from the test table.

First use the Mysqlbinlog tool to generate the log file TXT file analysis.

The code is as follows	Copy Code
F:\Program files\mysql_data\data\log> Mysqlbinlog liangck.000001 > G:\001.txt F:\Program files\mysql_data\data\log> Mysqlbinlog liangck.000002 > G:\002.txt F:\Program files\mysql_data\data\log> Mysqlbinlog liangck.000003 > G:\003.txt

With these three commands, you can generate 3 files under G disk,
The contents of the log file are recorded separately,
That is, the user action step.
Because we need to redo all the operations of the first log file,
So you just need to restore the first log file as a whole.

The code is as follows	Copy Code
F:\Program files\mysql_data\data\log> Mysqlbinlog liangck.000001 | Mysql-uroot–p

Ok, then, what we need to analyze is the second log file. Why should we analyze it,
Because it did an operation in midstream is delete, because what we have to do is restore all the data,
That is, we don't want to redo this statement. So here we have to find a way to get around it.
Let's open the 002.txt file to analyze it first.

In this file, we can see that the delete action starts at 875, and the end position is 1008.
So we just redo the start of the second log file to 875, and then the operation from 1008 to the end,
We can restore the data back without having to delete the data. So execute two commands:

The code is as follows	Copy Code
F:\Program files\mysql_data\data\log> Mysqlbinlog liangck.000002--stop-pos=875 | Mysql-uroot-p F:\Program files\mysql_data\data\log> Mysqlbinlog liangck.000002--start-pos=1008 | Mysql-uroot-p mytest

OK, now the second log file data.
The third log file is the same, as long as you find the location of the drop table.

The code is as follows	Copy Code
F:\Program files\mysql_data\data\log> Mysqlbinlog liangck.000003--stop-pos=574 | Mysql-uroot–p

Now let's look at the data again:

The code is as follows	Copy Code
SQL Code mysql>select*fromtest;+----+------+-------+|id|val|data|+----+------+-------+|1|10|liang| | 2|20|jia| | 3|30|hui| | 4|40|aaa| | 5|50|bbb| | 6|60|ccc| | 7|70|ddd| |8| 80|dddd| | 9|90|eeee|+----+------+-------+9rowsinset (0.00sec)

As you can see, all the data is back.