技术分享 | 只有.frm和.ibd文件时如何批量恢复InnoDB的表
作者:姚远
专注于 Oracle、MySQL 数据库多年,Oracle 10G 和 12C OCM,MySQL 5.6 ,5.7,8.0 OCP。现在鼎甲科技任顾问,为同事和客户提高数据库培训和技术支持服务。
背景
// RedHat
yum -y install mysql-server mysql-utilities
// Debian
apt install mysql-utilities
// 分析一个 .frm 文件生成建表的语句
mysqlfrm --diagnostic /var/lib/mysql/test/t1.frm
// 分析一个目录下的全部.frm文件生成建表语句
root@username:~# mysqlfrm --diagnostic /var/lib/mysql/my_db/bk/ >createtb.sql
root@username:~# grep "^CREATE TABLE" createtb.sql |wc -l
124
mysqldump --no-data --compact my_db>createtb.sql
mysql> create database my_db;
mysql> use my_db
Database changed
mysql> source createtb.sql
Query OK, 0 rows affected (0.07 sec)
......
导入旧的数据文件
root@username:/var/lib/mysql/my_db# ll *.ibd|wc
12411167941
root@username:/var/lib/mysql/my_db# mysql -e "show tables from my_db" \
| grep -v Tables_in_my_db \
| while read a; do mysql -e "ALTER TABLE my_db.$a DISCARD TABLESPACE"; done
root@username:/var/lib/mysql/my_db# ll *.ibd|wc
ls: cannot access '*.ibd': No such file or directory
000
root@username:/var/lib/mysql/my_db# mysql -e "show tables from my_db" \
| grep -v Tables_in_my_db \
| while read a; \
do mysql -e "ALTER TABLE my_db.$a import TABLESPACE"; done
导入完成后检查表
root@username:/var/lib/mysql/my_db# mysqlcheck -c my_db
my_db.cdp_backup_point OK
......
社区近期动态