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.