Mysql数据表损坏恢复

一、前言

某日登陆发现mysql表损坏，无法启动 ，可以借用如下方法恢复。

二、恢复

启动一个新库

[root@db data]# mysqld_safe --defaults-file=/data/3308/my.conf &
[root@db data]# ps -ef | grep 3308
root     25776 17533  0 10:11 pts/1    00:00:00 /bin/sh /application/mysql/bin/mysqld_safe --defaults-file=/data/3308/my.conf
mysql    25927 25776  0 10:11 pts/1    00:00:00 /application/mysql/bin/mysqld --defaults-file=/data/3308/my.conf --basedir=/application/mysql/ --datadir=/data/3308/data --plugin-dir=/application/mysql//lib/plugin --user=mysql --log-error=db.err --pid-file=db.pid --socket=/data/3308/data/mysql.sock --port=3308
root     25950 17533  0 10:12 pts/1    00:00:00 grep --color=auto 3308

登陆3308恢复数据

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

#创建world库
mysql> create database world;
Query OK, 1 row affected (0.00 sec)

#使用之前的建表语句创建数据表
mysql> CREATE TABLE `city` (
    ->   `ID` int(11) NOT NULL AUTO_INCREMENT,
    ->   `Name` char(35) NOT NULL DEFAULT '',
    ->   `CountryCode` char(3) NOT NULL DEFAULT '',
    ->   `District` char(20) NOT NULL DEFAULT '',
    ->   `Population` int(11) NOT NULL DEFAULT '0',
    ->   PRIMARY KEY (`ID`),
    ->   KEY `CountryCode` (`CountryCode`),
    ->   KEY `idx_district` (`District`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=4080 DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)

#目前是没有数据的
mysql> select * from city;
Empty set (0.00 sec)

#删除表空间
mysql> alter table world.city discard tablespace;
Query OK, 0 rows affected (0.00 sec)

#拷贝损坏表的表空间
[root@db ~]# cp -a /application/mysql/data/world/city.ibd /data/3307/data/world/
[root@db ~]# ll /data/3307/data/world/
total 736
-rw-rw---- 1 mysql mysql   8710 Nov  4 14:41 city.frm
-rw-rw---- 1 mysql mysql 737280 Nov  2 16:26 city.ibd
-rw-rw---- 1 mysql mysql     61 Nov  4 14:39 db.opt

#导入表空间
mysql> alter table world.city import tablespace;
Query OK, 0 rows affected, 1 warning (0.02 sec)

#数据恢复
mysql> select * from city limit 5;
+----+----------------+-------------+---------------+------------+
| ID | Name           | CountryCode | District      | Population |
+----+----------------+-------------+---------------+------------+
|  1 | Kabul          | AFG         | Kabol         |    1780000 |
|  2 | Qandahar       | AFG         | Qandahar      |     237500 |
|  3 | Herat          | AFG         | Herat         |     186800 |
|  4 | Mazar-e-Sharif | AFG         | Balkh         |     127800 |
|  5 | Amsterdam      | NLD         | Noord-Holland |     731200 |
+----+----------------+-------------+---------------+------------+
5 rows in set (0.00 sec)