一, 演示环境说明:
系统 CentOS Linux release 7.2.1511 (Core) X_86 64 位最小化安装
MySQL 版本是官方二进制版本 5.7.22-22,MySQL 采用的是二进制安装, 单机上开启 2 个 MySQL 实例, MySQL 实例要开启定时器 event_scheduler=ON. 而且 2 个 MySQL 实例都要开启 Gtid
xtrabackup 采用的是 rpm 包安装, 版本是 version 2.4.13
MySQL 备份方式采用每天一次全量备份和 binlog 增量备份
二, 模拟删除库, 进行数据恢复演示:
提示: 当然此处只是演示, 严禁生成环境删库, 删表模拟, 后果你懂得
故障模拟:
线上误删除一个 testdb 库下的 test1_event 表, 利用当天的 MySQL 的全量备份 + 当天生成的 MySQL 的 binlog 文件来恢复数据到误删的表 test1_event 之前的数据
恢复方式介绍:
官方推荐采用利用 MySQL binlog 方式恢复, 生产实践验证官方的这个方式已经是不严谨的做法了 (下面的方法是官方推荐的)
故障恢复过程如下:
2.1 接收到误删除之前, 第一时间确认大概误操作时间
2.2 登录主库查看当前的 binlog 位置点 (要记住此时的 binlog 文件, 后面恢复时会用到)
- ([email protected]'mgr01':mysql3306.sock)[testdb]>show master status\G
- *************************** 1. row ***************************
- File: MySQL-bin.000005
- Position: 15211
- Binlog_Do_DB:
- Binlog_Ignore_DB:
- Executed_Gtid_Set: bde7b592-b966-11e9-8c64-000c294f3e61:1-10445
- 1 row in set (0.00 sec)
2.3 最好是 flush logs 下, 让接下来的 sql 写入到新的 binlog 文件
2.4 定位 drop 表语句所在 binglog 文件的位置点:
- [[email protected] binlog]# mysqlbinlog -v --base64-output=decode-rows /data/MySQL/mysql3306/binlog/MySQL-bin.000005|grep -i -C 15 drop
- ### @1=10422
- ### @2='tomcat'
- ### @3='xiaohuahua'
- ### @4='2019-08-08 14:22:18'
- # at 14987
- #190808 14:22:18 server id 63306 end_log_pos 15018 CRC32 0x873943dd Xid = 20695
- COMMIT/*!*/;
- #at15018###################################
- #190808 14:22:19 server id 63306 end_log_pos 15083 CRC32 0xcc8773ce GTID last_committed=34 sequence_number=35 rbr_only=no
- SET @@SESSION.GTID_NEXT= 'bde7b592-b966-11e9-8c64-000c294f3e61:10445'/*!*/;
- #at 15083
- #190808 14:22:19 server id 63306 end_log_pos 15211 CRC32 0x8d445019 Query thread_id=7213 exec_time=0 error_code=0
- use `testdb`/*!*/;
- SET TIMESTAMP=1565245339/*!*/;
- SET @@session.sql_auto_is_null=0/*!*/;
- DROP TABLE `test1_event` /* generated by server */
- /*!*/;
- SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
- DELIMITER ;
- #End of log file
- /*!50003 SET [email protected]_COMPLETION_TYPE*/;
- /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
定位到 15018 这个位置点就是利用 binlog 的文件恢复时的结束点
2.5 恢复 xtrabackup 备份到 mysql3308 实例上:
提示: MySQL 3308 实例要开启 Gtid 的
恢复备份的命令:
- innobackupex --apply-log /data/backup/db_3306_20190808/
- innobackupex --defaults-file=/data/MySQL/mysql3308/my3308.cnf --copy-back /data/backup/db_3306_20190808/
给数据目录 data MySQL 权限:
chown -R MySQL.MySQL /data/MySQL/mysql3308/data/
启动 mysql3308 实例:
/usr/local/MySQL/bin/mysqld --defaults-file=/data/MySQL/mysql3308/my3308.cnf &
查看到恢复到 3308 实例的数据, 但是在备份 3306 库到删除表 test1_event 这时间段还存在很多缺失的数据未找回, 要从增量的 binlog 文件中找回:
- | 10273 | tomcat | xiaohuahua | 2019-08-08 14:17:16 |
- | 10274 | tomcat | xiaohuahua | 2019-08-08 14:17:18 |
- +-------+----------+------------+---------------------+
- ([email protected]'mgr01':mysql3308.sock)[testdb]>select count(*) from test1_event;
- +----------+
- | count(*) |
- +----------+
- | 10273 |
- +----------+
- 1 row in set (0.00 sec)
xtrabackup 备份是开启 Gtid 的, 所以下面使用 mysqlbinlog 命令 进行增量 binglog 文件恢复数据时, 要添加参数 --skip-gtids 忽略掉 binlog 文件的中的 Gtid 信息, 要是不加参数 --skip-gtids 进行恢复的话, 导致数据恢复不到 3308 库
正确的恢复命令如下:
- mysqlbinlog /data/MySQL/mysql3306/binlog/MySQL-bin.000001 --skip-gtids |MySQL -f --binary-mode -S /tmp/mysql3308.sock
- mysqlbinlog /data/MySQL/mysql3306/binlog/MySQL-bin.000002 --skip-gtids |MySQL -f --binary-mode -S /tmp/mysql3308.sock
- mysqlbinlog /data/MySQL/mysql3306/binlog/MySQL-bin.000003 --skip-gtids |MySQL -f --binary-mode -S /tmp/mysql3308.sock
- mysqlbinlog /data/MySQL/mysql3306/binlog/MySQL-bin.000004 --skip-gtids |MySQL -f --binary-mode -S /tmp/mysql3308.sock
- mysqlbinlog /data/MySQL/mysql3306/binlog/MySQL-bin.000005 --stop-position="15018" --skip-gtids |MySQL -f --binary-mode -S /tmp/mysql3308.sock
参数说明:
--skip-gtids 忽略 binlog 文件中的 Gtid 的信息
--binary-mode 主要是为了解决中文乱码或者是特殊字符串乱码的问题
-f 强制恢复, 忽略报错
执行完以上命令, 数据就恢复到删除表 test1_event 之前的了
下面的恢复命令会导致数据恢复不到 MySQL 3308 实例上:(原因是 my3308 实例开启了 Gtid 参数导致的)
- mysqlbinlog /data/MySQL/mysql3306/binlog/MySQL-bin.000001 |MySQL -f --binary-mode -S /tmp/mysql3308.sock
- mysqlbinlog /data/MySQL/mysql3306/binlog/MySQL-bin.000002 |MySQL -f --binary-mode -S /tmp/mysql3308.sock
- mysqlbinlog /data/MySQL/mysql3306/binlog/MySQL-bin.000003 |MySQL -f --binary-mode -S /tmp/mysql3308.sock
- mysqlbinlog /data/MySQL/mysql3306/binlog/MySQL-bin.000004 |MySQL -f --binary-mode -S /tmp/mysql3308.sock
- mysqlbinlog --stop-position="15018" /data/MySQL/mysql3306/binlog/MySQL-bin.000005|MySQL -f --binary-mode -S /tmp/mysql3308.sock
2.6 如果新的实例 mysql3308 启动前从 my3308.cnf 中关闭掉 Gtid 参数:
3.
这样的话采用 mysqlbinlog /data/MySQL/mysql3306/binlog/MySQL-bin.00000* |MySQL -f --binary-mode -S /tmp/mysql3308.sock 是可以将数据恢复到 MySQL 3308 实例上的, 但是恢复过程中报错. 这样恢复到 3308 的数据,
生成的 binlog 文件是不记录 Gtid 信息的
- [[email protected] backup]# mysqlbinlog --stop-position="15018" /data/MySQL/mysql3306/binlog/MySQL-bin.000005|MySQL -f --binary-mode -S /tmp/mysql3308.sock
- ERROR 1781 (HY000) at line 17: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.
- ERROR 1781 (HY000) at line 50: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.
- ERROR 1781 (HY000) at line 74: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.
- ERROR 1781 (HY000) at line 98: @@SESSION.GTID_NEXT cannot be set to UUID:NUMBER when @@GLOBAL.GTID_MODE = OFF.
当然也可以采用下面的方式来恢复, 同样 3308 的实例的 binglog 文件是不记录 Gtid 信息的, 这种方法在恢复的过程中可能会出现报错, 不能完全保证数据的完整性和正确性, 所以生产上最好不要使用这种方法来恢复找回数据
- [[email protected] backup]# mysqlbinlog --skip-gtids /data/MySQL/mysql3306/binlog/MySQL-bin.000001|MySQL -f --binary-mode -S /tmp/mysql3308.sock
- ERROR 1050 (42S01) at line 27: Table 'test1_event' already exists
- ERROR 1062 (23000) at line 92: Duplicate entry '1' for key 'PRIMARY'
- ERROR 1537 (HY000) at line 132: Event 'e_test' already exists
- mysqlbinlog --skip-gtids /data/MySQL/mysql3306/binlog/MySQL-bin.000002|MySQL -f --binary-mode -S /tmp/mysql3308.sock
- mysqlbinlog --skip-gtids /data/MySQL/mysql3306/binlog/MySQL-bin.000003|MySQL -f --binary-mode -S /tmp/mysql3308.sock
- mysqlbinlog --skip-gtids /data/MySQL/mysql3306/binlog/MySQL-bin.000004|MySQL -f --binary-mode -S /tmp/mysql3308.sock
- mysqlbinlog --skip-gtids --stop-position="15018" /data/MySQL/mysql3306/binlog/MySQL-bin.000005|MySQL -f --binary-mode -S /tmp/mysql3308.sock
友情提示: 生产上还是最好开启 Gtid. 这样在恢复数据和数据库同步, 以及解决同步错误是非常方便的
总结:
对于 DDL 语句像 drop database ,drop tables , drop tables , truncate table 这样的语句就可以采用 mysqlbinlog 来恢复. 同时也可以采用 binlog2sql 工具来闪回
对于这样的语句, 不管 binglog 格式为 row 格式, 还是 statement 格式, 还是 Mixed 格式, 记录的 binlog 格式都为 statement 格式为 row
利用 mysqlbinlog 只能支持到 database 级别的提取
全备 + 利用 mysqlbinlog 恢复到某个时间点
利用 mysqlbinlog --skip-gtids 存在很大风险点
来源: http://www.bubuko.com/infodetail-3158930.html