mysql myisam index坏或者丢失的修复方法

mysql_myisam_missing_index_rebuild_index.log.txt

p1. create table t1
p2. insert into records
p3. rm t1.MYI
error ocur

1. create a new empty table n1
2. cp new tempty table's index file n1.MYI to t1.MYI
3. myisamcheck -r t1

 

mysql>  create table t1(id int not null auto_increment,  name varchar(32), age int, primary key (id), key (name), key(age)) ENGINE=myisam;
Query OK, 0 rows affected (0.06 sec)

mysql> show create table t1/G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) COLLATE utf8_unicode_ci DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `name` (`name`),
  KEY `age` (`age`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> insert into t1(name,age) values('a1',1),('a2',2),('a3',3),('a4',4),('a5',5);
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql>
mysql>
mysql>
mysql> select * from t1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a1   |    1 |
|  2 | a2   |    2 |
|  3 | a3   |    3 |
|  4 | a4   |    4 |
|  5 | a5   |    5 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | id          | A         |           5 |     NULL | NULL   |      | BTREE      |         |
| t1    |          1 | name     |            1 | name        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| t1    |          1 | age      |            1 | age         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

mysql> show table status like 't1'/G;
*************************** 1. row ***************************
           Name: t1
         Engine: MyISAM
        Version: 10
     Row_format: Dynamic
           Rows: 5
 Avg_row_length: 20
    Data_length: 100
Max_data_length: 281474976710655
   Index_length: 4096
      Data_free: 0
 Auto_increment: 6
    Create_time: 2009-08-14 14:18:30
    Update_time: 2009-08-14 14:20:54
     Check_time: NULL
      Collation: utf8_unicode_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> /q
Bye



/var/mysql/data/test>cp -p t1.frm t2.frm
/var/mysql/data/test>cp -p t1.MYD t2.MYD
/var/mysql/data/test>cp -p t1.MYI t2.MYI
/var/mysql/data/test>mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 12
Server version: 5.1.32-Yahoo-SMP-log  (RHEL 64-bit)

Type 'help;' or '/h' for help. Type '/c' to clear the buffer.

mysql> select count(*) from t2;
+----------+
| count(*) |
+----------+
|        5 |
+----------+
1 row in set (0.00 sec)

/var/mysql/data/test>mv t1.MYI /tmp

>mysql -uroot test
ysql> flush tables;
Query OK, 0 rows affected (0.00 sec)

mysql> desc t1;
ERROR 1017 (HY000): Can't find file: 't1' (errno: 2)
mysql> check table t1;
+---------+-------+----------+----------------------------------+
| Table   | Op    | Msg_type | Msg_text                         |
+---------+-------+----------+----------------------------------+
| test.t1 | check | Error    | Can't find file: 't1' (errno: 2) |
| test.t1 | check | error    | Corrupt                          |
+---------+-------+----------+----------------------------------+
2 rows in set (0.00 sec)

mysql> repair table t1;
+---------+--------+----------+----------------------------------+
| Table   | Op     | Msg_type | Msg_text                         |
+---------+--------+----------+----------------------------------+
| test.t1 | repair | Error    | Can't find file: 't1' (errno: 2) |
| test.t1 | repair | error    | Corrupt                          |
+---------+--------+----------+----------------------------------+
2 rows in set (0.00 sec)

mysql>create table n1(id int not null auto_increment,  name varchar(32), age int, primary key (id), key (name), key(age)) ENGINE=myisam;
Query OK, 0 rows affected (0.05 sec)

mysql> /q
Bye



/var/mysql/data/test>sudo -u mysql cp -p n1.MYI t1.MYI
/var/mysql/data/test>myisamchk -r t1.MYI
- recovering (with sort) MyISAM-table 't1.MYI'
Data records: 0
- Fixing index 1
- Fixing index 2
- Fixing index 3
Data records: 5
/var/mysql/data/test>mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or /g.
Your MySQL connection id is 15
Server version: 5.1.32-Yahoo-SMP-log  (RHEL 64-bit)

Type 'help;' or '/h' for help. Type '/c' to clear the buffer.

mysql> desc t1;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| name  | varchar(32) | YES  | MUL | NULL    |                |
| age   | int(11)     | YES  | MUL | NULL    |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> select * from t1;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a1   |    1 |
|  2 | a2   |    2 |
|  3 | a3   |    3 |
|  4 | a4   |    4 |
|  5 | a5   |    5 |
+----+------+------+
5 rows in set (0.00 sec)

mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t1    |          0 | PRIMARY  |            1 | id          | A         |        NULL |     NULL | NULL   |      | BTREE      |         |
| t1    |          1 | name     |            1 | name        | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
| t1    |          1 | age      |            1 | age         | A         |        NULL |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
3 rows in set (0.00 sec)

 

 

恢复create table的见:

http://www.mysqlperformanceblog.com/2008/12/17/recovering-create-table-statement-from-frm-file/