binlog2sql简介
binlog2sql是大众点评开源的一个 MySQL 闪回工具。可以实现数据的回滚。
闪回原理简析
开始之前,先说说闪回。我们都知道 MySQL binlog 以 event 为单位,记录数据库的变更信息,这些信息能够帮助我们重现这之间的所有变化,也就是所谓的闪回。
binlog 有三种可选的格式:
- statement:基于 SQL 语句的模式,binlog 数据量小,但是某些语句和函数在复制过程可能导致数据不一致甚至出错;
- mixed:混合模式,根据语句来选用是 statement 还是 row 模式;
- row:基于行的模式,记录的是行的完整变化。安全,但 binlog 会比其他两种模式大很多;
利用 binlog 做闪回,需要将 binlog 格式设置为 row,因为我们需要最详尽的信息来确定操作之后数据不会出错。
既然 binlog 以 event 形式记录了所有的变更信息,那么我们把需要回滚的 event,从后往前回滚回去即可。
回滚操作:
- 对于 delete 操作,我们从 binlog 提取出 delete 信息,反向生成 insert 回滚语句;
- 对于 insert 操作,反向生成 delete 回滚语句;
- 对于 update 操作,根据信息生成反向的 update 语句;
binlog2sql的安装
git clone https://github.com/danfengcao/binlog2sql.git
cd binlog2sql
pip install -r requirements.txt
binlog2sql的使用
MySQL server必须设置以下参数:
[mysqld]
server_id = 1
log_bin =mysql-bin.log
max_binlog_size = 1G
binlog_format = row
binlog_row_image = full
案例:先删除mysql库下test表的数据,然后通过回滚还原。
需要创建一个闪回用户,该用户的最小权限如下:
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO
#select:需要读取server端information_schema.COLUMNS表,获取表结构的元信息,拼接成可视化的sql语句
#replication client:两个权限都可以,需要执行'SHOW MASTER STATUS', 获取server端的binlog列表
#replication slave:通过BINLOG_DUMP协议获取binlog内容的权限
然后就可以恢复了,首先确认使用的哪一个binlog文件
show master status;
解析标准sql
python binlog2sql.py -P3306 -utest -p123456 -dmysql -ttest --start-file='mysql-bin.000002'
解析回滚sql
python binlog2sql.py -P3306 -utest -p123456 -dmysql -ttest --start-file='mysql-bin.000002' --start-position=4 --stop-position=433 -B > test_rollback.sql
最后应用回滚sql,完成恢复。
mysql>source /usr/local/bin/binlog2sql/binlog2sql/test_rollback.sql