Docker MySQL从.frm和.ibd文件恢复数据

启动MYSQL Docker容器

docker run -d -e REPLICATION_MASTER=true -e REPLICATION_PASS=admin -e MYSQL_PASS=USER_PASS -e MYSQL_USER=USER_NAME -p 3306:3306 -v /root/mysql/sock/master:/var/run/mysqld -v /root/mysql/data/master:/var/lib/mysql --name mysql wxsc/mysql:5.6

链接MYSQL容器

mysql -h MYSQL_IP -u USER_NAME -p

创建同名数据库

MySQL [(none)]> create database DATABASE_NAME;

创建同名表

MySQL [(none)]> use DATABASE_NAME;
MySQL [(DATABASE_NAME)]> CREATE TABLE TABLE_NAME(a int)ENGINE=InnoDB;

恢复表结构

1、关闭容器

docker kill CONTAINER_ID

2、替换FRM文件

rm -rf  /root/mysql/data/master/DATABASE_NAME/TABLE_NAME.frm
cp ORIG_TABLE_NAME.frm /root/workspace/mysql/data/master/DATABASE_NAME
chown -R [用户]:[组] ORIG_TABLE_NAME.frm

3、重启容器

docker start CONTAINER_ID

4、获取表结构

mysql -h MYSQL_IP -u USER_NAME -p
MySQL [(DATABASE_NAME)]> desc TABLE_NAME;

5、查看容器日志

docker logs CONTAINER_ID
错误信息：
InnoDB: table DATABASE_NAME/TABLE_NAME contains 1 user defined columns in InnoDB, but 20 columns in MySQL. 

发现原表拥有20个字段

6、删除当前表，新建一张拥有20个字段的同名表

MySQL [(DATABASE_NAME)]> drop table TABLE_NAME;

CREATE TABLE TABLE_NAME (a1 int,a2 int,a3 int,a4 int,a5 int,a6 int,a7 int,a8 int,a9 int,a10 int,a11 int,a12 int,a13 int,a14 int,a15 int,a16 int,a17 int,a18 int,a19 int,a20 int)ENGINE=InnoDB;

7、修改my.cnf

docker exec -ti CONTAINER_ID /bin/bash
root@CONTAINER_ID:/# vi /etc/mysql/my.cnf
在[mysqld]下添加
innodb_force_recovery=6

8、重新替换FRM文件

rm -rf  /root/mysql/data/master/DATABASE_NAME/TABLE_NAME.frm
cp ORIG_TABLE_NAME.frm /root/workspace/mysql/data/master/DATABASE_NAME
chown -R [用户]:[组] ORIG_TABLE_NAME.frm

9、重启容器，备份表结构

docker restart CONTAINER_ID
MySQL [(DATABASE_NAME)]> desc TABLE_NAME;
mysqldump -hMYSQL_IP -uUSER_NAME -pUSER_PASS DATABASE_NAME TABLE_NAME> /root/DATABASE_NAME/TABLE_NAME_table.sql

恢复表数据

1、修改my.cnf

docker exec -ti CONTAINER_ID /bin/bash
root@CONTAINER_ID:/# vi /etc/mysql/my.cnf
在[mysqld]下注释
# innodb_force_recovery=6

2、重启容器，导入表结构

docker restart CONTAINER_ID
MySQL [(DATABASE_NAME)]> source /root/DATABASE_NAME/TABLE_NAME_table.sql;

3、分离表空间

MySQL [(DATABASE_NAME)]> ALTER TABLE TABLE_NAME DISCARD TABLESPACE;

4、替换IBD文件

cp ORIG_TABLE_NAME.ibd /root/workspace/mysql/data/master/DATABASE_NAME
chown -R [用户]:[组] ORIG_TABLE_NAME.ibd

5、建立新的连接

MySQL [(DATABASE_NAME)]> ALTER TABLE TABLE_NAME IMPORT TABLESPACE;

6、备份表

mysqldump -hMYSQL_IP -uUSER_NAME -pUSER_PASS DATABASE_NAME TABLE_NAME> /root/DATABASE_NAME/TABLE_NAME.sql