乐趣区

关于mysql:赵强老师MySQL的闪回

jiezi

MySQL DBA 或开发人员，有时会误删或者误更新数据，如果是线上环境并且影响较大，就须要能疾速回滚。传统复原办法是利用备份重搭实例，再利用去除谬误 sql 后的 binlog 来复原数据。此法费时费力，甚至须要停机保护，并不适宜疾速回滚。也有团队利用 LVM 快照来缩短复原工夫，但快照的毛病是会影响 mysql 的性能。MySQL 闪回 (flashback) 利用 binlog 间接进行回滚，能疾速复原且不必停机。

闪回的原理

MySQL binlog 以 event 的模式，记录了 MySQL server 从启用 binlog 以来所有的变更信息，可能帮忙重现这之间的所有变动。MySQL 引入 binlog 次要有两个目标：一是为了主从复制；二是某些备份还原操作后须要从新利用 binlog。有三种可选的 binlog 格局，各有优缺点：

  • statement：基于 SQL 语句的模式，binlog 数据量小，然而某些语句和函数在复制过程可能导致数据不统一甚至出错；
  • row：基于行的模式，记录的是行的残缺变动。很平安，然而 binlog 会比其余两种模式大很多；
  • mixed：混合模式，依据语句来选用是 statement 还是 row 模式；

利用 binlog 闪回，须要将 binlog 格局设置为 row。利用上面的语句能够查看以后 binlog 的模式。

show global variables like "%binlog_format%";

闪回的实战

实在的闪回场景中，最要害的是能疾速筛选出真正须要回滚的 SQL。咱们应用开源工具 binlog2sql 来进行实战演练。binlog2sql 由美团点评 DBA 团队 (上海) 出品，屡次在线上环境做疾速回滚。

① 装置 binlog2sql 工具

首先装置 Python 工具治理表 pip
yum -y install epel-release
yum -y install python-pip

装置 binlog2sql
git clone https://github.com/danfengcao/binlog2sql.git && cd binlog2sql
pip install -r requirements.txt

② 闪回案例实战

1、咱们应用之前的员工表数据，独自建设一个数据库

create database testflashback;
use testflashback;
source /root/tools/scott.sql

2、误操作，执行上面的事务。

start transaction;
delete from emp where sal>3000;
update emp set sal=6000;
delete from emp where job='CLERK';
commit;

3、查看目前的 binlog 文件

show master logs;

4、最新的 binlog 文件是 mysql-binlog.000001。咱们的指标是筛选出须要回滚的 SQL，因为误操作人只晓得大抵的误操作工夫，咱们首先依据工夫做一次过滤。只须要解析 testflashback 库 emp 表。(注：如果有多个 sql 误操作，则生成的 binlog 可能散布在多个文件，需解析多个文件)

python binlog2sql/binlog2sql.py -uroot -pWelcome_1 \
--start-file='mysql-binlog.000001' > /root/tools/raw.sql

下面的语句将列车 emp 表的所有 binlog 日志。如果可能确定大抵的工夫范畴，能够应用参数 –start-datetime 和 –stop-datetime 进行过滤。例如：

--start-datetime='2016-12-26 11:44:00' --stop-datetime='2016-12-26 11:50:00'

解析解决的 binlog 如下：

5、依据地位信息，咱们确定了误操作 sql 来自同一个事务，精确地位在 14956-16791 之间(binlog2sql 对于同一个事务会输入同样的 start position)。再依据地位过滤，应用 -B 选项生成回滚 sql，查看回滚 sql 是否正确。(注：实在场景下，生成的回滚 SQL 常常会须要进一步筛选。联合 grep、编辑器等)

python binlog2sql/binlog2sql.py -uroot -pWelcome_1 --start-file='mysql-binlog.000001' \
--start-position=14956 --stop-position=16791 -B > /root/tools/rollback.sql

上面是生成的闪回语句：

6、与业务方确认回滚 sql 没问题，执行回滚语句。登录 mysql，确认回滚胜利。

mysql -uroot -pWelcome_1 < /root/tools/rollback.sql

7、检查数据是否复原

退出移动版