问题：3306实例，数据量3TB，共10个业务，10个库，500张表，周三上午10点，误drop了xbk.order 核心业务表
20G；导致xbk库业务无法正常运行。
备份策略，周日全备，周一到周六inc，增备。binlog完整。
怎么快速恢复，还不影响业务？
提示：
alter table xbk.order sicard tablesoace;
alter table xbk.order import tablespace;
要求：
1.方案
1）进行全备增倍整合，恢复到周二的全备数据。
2）本地新建实例3307，进行全备数据恢复
3）截取binlog进行周三数据恢复
4）根据3307获取表结构，3306xbk库建立order表
5）卸载新建xbk.order表的表空间，将3307order表的表空间拷贝
2.重现故障
本地环境：
模拟周日全备
mysql> desc order_table;
| Field | Type | Null | Key | Default | Extra |
| id | int(11) | YES | | NULL | |
| name | varchar(20) | YES | | NULL | |
insert into order_table values(1,'我是周日的全备数据');
[root@later03 xbk]# innobackupex --user=root --password=luobiao123 --no-timestamp /data/xbk/full_2020-3-15
模拟周一增备
insert into order_table values(1,'我是周一的增备数据');commit;
innobackupex --user=root --password=luobiao123 --no-timestamp --incremental --incremental-basedir=/data/xbk/full_2020-3-15 /data/xbk/inc1_2020-3-16
模拟周二增备:
insert into order_table values(1,'我是周二的增备数据');commit;
innobackupex --user=root --password=luobiao123 --no-timestamp --incremental --incremental-basedir=/data/xbk/inc1_2020-3-16/ /data/xbk/inc2_2020-3-17
模拟周三数据的增加，表的误删除：
insert into order_table values(1,'我是周三的数据，该表被删除');commit;
drop table order_table;
3.恢复模拟
一.进行备份文件的备份。
二、合并全备加增备:
1.整理全备
innobackupex --apply-log --redo-only /data/xbk/full_2020-03-15
2.执行全备和周一的增备的合并，并prepare，redo-only
innobackupex --apply-log --redo-only --incremental-dir=/data/xbk/inc1_2020-3-16 /data/xbk/full_2020-03-15/
3.执行全备和周二的增备的合并，并prepare 最后一个增倍 不用redo-only
4.innobackupex --apply-log --incremental-dir=/data/xbk/inc2_2020-3-17 /data/xbk/full_2020-03-15/
5.整理合并后的备份
innobackupex --apply-log /data/xbk/full_2020-03-15
三、3307实例进行恢复
1.将3307的数据目录指定到该备份文件,并将3306mysqld的部分配置添加到3307，
如果出现:2020-03-30T11:16:53.954429Z 0 [ERROR] InnoDB: Tablespace size stored in header is 17664 pages, but the sum of data file sizes is only 4864 pages 此类报错；则添加
innodb_force_recovery = 6 到my.cnf
[root@later03 etc]# cd /data/3307/
[root@later03 3307]# vi my.cnf
datadir=/data/xbk/full_2020-03-15
innodb_force_recovery = 6
<<<nnodb_force_recovery=0 表示当需要恢复时执行所有的恢复操作；
innodb_force_recovery=1 表示忽略检查到的corrupt页；
innodb_force_recovery=2 表示阻止主线程的运行，如主线程需要执行full purge操作，会导致crash；
innodb_force_recovery=3 表示不执行事务回滚操作；
innodb_force_recovery=4 表示不执行插入缓冲的合并操作；
innodb_force_recovery=5 表示不查看重做日志，InnoDB存储引擎会将未提交的事务视为已提交；
innodb_force_recovery=6 表示不执行前滚的操作，强制重启！>>>>
2)并将该目录的用户以及用户组修改为mysql,并赋予755权限
chown -R mysql.mysql full_2020-3-15
chown -R mysql.mysql full_2020-3-15/*
[root@later03 xbk]# chmod -R 755 full_2020-3-15/*
[root@later03 xbk]# cd full_2020-3-15/
2.启动3307实例
systemctl start mysqld3307
登陆3307实例:
mysql -uroot -pluobiao123 -P 3307 -S /tmp/mysql3307.sock
查看数据：
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| bindb |
| bindb1 |
| keby |
| mdp |
| mysql |
| oldguo |
| performance_schema |
| school |
| sys |
| world |
| xbk |
+--------------------+
12 rows in set (0.00 sec)
mysql> use xbk
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql>
mysql>
mysql> ls
->
->
-> ;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ls' at line 1
mysql>
mysql> show tables;
+---------------+
| Tables_in_xbk |
+---------------+
| order_table |
| t1 |
| t2 |
| t3 |
| t4 |
| t5 |
+---------------+
6 rows in set (0.00 sec)
mysql> select * from order_table;
+------+-----------------------------+
| id | name |
+------+-----------------------------+
| 1 | 我是周日的全备数据 |
| 1 | 我是周一的增备数据 |
| 1 | 我是周二的增备数据 |
+------+-----------------------------+
3 rows in set (0.01 sec)
四，查看binlog日志点恢复
起点：
[root@later03 3307]# cd /data/xbk/full_2020-3-15/
[root@later03 full_2020-3-15]# ls
auto.cnf ibdata3 mysql xtrabackup_binlog_info
backup-my.cnf ib_logfile0 oldguo xtrabackup_binlog_pos_innodb
bindb ib_logfile1 performance_schema xtrabackup_checkpoints
bindb1 ibtmp1 school xtrabackup_info
ib_buffer_pool keby sys xtrabackup_logfile
ibdata1 later03.pid world xtrabackup_master_key_id
ibdata2 mdp xbk
[root@later03 full_2020-3-15]# cat xtrabackup_binlog_pos_innodb
start-position:mysql-bin.000022 6895
GTID: '21dd611c-7245-11ea-b482-00163e0477d5:27'
终点:
| mysql-bin.000022 | 6895 | Gtid | 6 | 6960 | SET @@SESSION.GTID_NEXT= '21dd611c-7245-11ea-b482-00163e0477d5:27' |
| mysql-bin.000022 | 7198 | Gtid | 6 | 7263 | SET @@SESSION.GTID_NEXT= '21dd611c-7245-11ea-b482-00163e0477d5:28' |
| mysql-bin.000022 | 7263 | Query | 6 | 7385 | use `xbk`; DROP TABLE `order_table` /* generated by server */
stop-position:mysql-bin.000022 7263
gtid:'21dd611c-7245-11ea-b482-00163e0477d5:27'
可以看到，我们找到了gtid和position号，这里我们选择gtid恢复
mysqlbinlog --include-gtids='21dd611c-7245-11ea-b482-00163e0477d5:27' --skip-gtids /data/binlog/mysql-bin.000022 >/tmp/order_table.sql
根据3307导出表结构
mysql> show create table order_table;
+-------------+-------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------------+-------------------------------------------------------------------------------------------------------------------------------------+
| order_table | CREATE TABLE `order_table` (
`id` int(11) DEFAULT NULL,
`name` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 |
3306 执行该语句;
卸载表空间：
alter table order_table discard tablespace;
拷贝order_table.ibd 从 3307到3306
p /data/xbk/full_2020-3-15/xbk/order_table.ibd /data/3306/xbk/
查看权限是否正确 略
[root@later03 xbk]# chown -R mysql.mysql order_table.ibd
挂载表空间：
alter table order_table import tablespace;
mysql> select * from order_table;
+------+-----------------------------+
| id | name |
+------+-----------------------------+
| 1 | 我是周日的全备数据 |
| 1 | 我是周一的增备数据 |
| 1 | 我是周二的增备数据 |
+------+-----------------------------+
3 rows in set (0.00 sec)
查询：数据恢复成功。
五、进行binlog的恢复
mysql> set sql_log_bin=0;
Query OK, 0 rows affected (0.00 sec)
mysql>
mysql>
mysql>
mysql> source /tmp/order_table.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Charset changed
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from order_table;
+------+-----------------------------------------+
| id | name |
+------+-----------------------------------------+
| 1 | 我是周日的全备数据 |
| 1 | 我是周一的增备数据 |
| 1 | 我是周二的增备数据 |
| 1 | 我是周三的数据，该表被删除 |
+------+-----------------------------------------+
4 rows in set (0.00 sec)
mysql> set sql_log_bin = 1 ;
Query OK, 0 rows affected (0.00 sec)
终于恢复成功。
总结：3307上只需要进行启动成功后，获取表结构，能查询数据即可，即得到表的ibd数据文件。
剩下的操作：将表结构在3306上建表，卸载表空间，拷贝，3307ibd文件，挂载表空间。此时合并的
备份数据操作完毕，下一步，将binlog日志恢复到表上即可。
存在问题：
mysqlbinlog并没有只截取order_table的日志，还截取了其他的表的？？？？
解决？？？暂时未解决
systemctl stop mysqld3307
网友评论