招生网站模板,网络营销师报考条件,服装网站建设规划书需求分析,大型淘宝客返利网站建设1#xff0c;闪回原理 【binlog】MySQL binlog以event的形式#xff0c;记录了MySQL server从启用binlog以来所有的变更信息#xff0c;能够帮助重现这之间的所有变化。MySQL引入binlog主要有两个目的#xff1a;一是为了主从复制#xff1b;二是某些备份还原操作后需要重…1闪回原理 【binlog】MySQL binlog以event的形式记录了MySQL server从启用binlog以来所有的变更信息能够帮助重现这之间的所有变化。MySQL引入binlog主要有两个目的一是为了主从复制二是某些备份还原操作后需要重新应用binlog。 有三种可选的binlog格式各有优缺点 statement基于SQL语句的模式binlog数据量小但是某些语句和函数在复制过程可能导致数据不一致甚至出错row基于行的模式记录的是行的完整变化。很安全但是binlog会比其他两种模式大很多mixed混合模式根据语句来选用是statement还是row模式 利用binlog闪回需要将binlog格式设置为row。row模式下一条使用innodb的insert会产生如下格式的binlog # at 1129
#161225 23:15:38 server id 3773306082 end_log_pos 1197 Query thread_id1903021 exec_time0 error_code0
SET TIMESTAMP1482678938/*!*/;
BEGIN
/*!*/;
# at 1197
#161225 23:15:38 server id 3773306082 end_log_pos 1245 Table_map: test.user mapped to number 290
# at 1245
#161225 23:15:38 server id 3773306082 end_log_pos 1352 Write_rows: table id 290 flags: STMT_END_FBINLOG
muJfWBPiFOjgMAAAAN0EAAAAACIBAAAAAAEABHRlc3QABHVzZXIAAwMPEQMeAAAC
muJfWB7iFOjgawAAAEgFAAAAACIBAAAAAAEAAgAD//gBAAAABuWwji1tVhK1hH4AgAAAAblsI/p
krFYStYgAMAAAAG5bCP5a2ZWE/onPgEAAAABuWwjadjlhNeAD4BQAAAAJ0dFhRYJM
/*!*/;
# at 1352
#161225 23:15:38 server id 3773306082 end_log_pos 1379 Xid 5327954
COMMIT/*!*/; 【闪回原理】既然binlog以event形式记录了所有的变更信息因此闪回就是把需要回滚的event从后往前回滚回去即可。对于单个event的回滚以表test.user为例 mysql show create table test.user\G
*************************** 1. row ***************************Table: user
Create Table: CREATE TABLE user (id int(11) NOT NULL AUTO_INCREMENT,name varchar(10) DEFAULT NULL,PRIMARY KEY (id)
) ENGINEInnoDB AUTO_INCREMENT6 DEFAULT CHARSETutf8 对于delete操作从binlog提取出delete信息生成的回滚语句是insert。(注为了方便解释用binlog2sql将原始binlog转化成了可读SQL) 原始DELETE FROM test.user WHERE id1 AND name小赵;回滚INSERT INTO test.user(id, name) VALUES (1, 小赵); 对于insert操作回滚SQL是delete。 原始INSERT INTO test.user(id, name) VALUES (2, 小钱);回滚DELETE FROM test.user WHERE id2 AND name小钱; 对于update操作回滚sql应该交换SET和WHERE的值。 原始UPDATE test.user SET id3, name小李 WHERE id3 AND name小孙;回滚UPDATE test.user SET id3, name小孙 WHERE id3 AND name小李; 2闪回工具 【第一类】以patch形式集成到官方工具mysqlbinlog中。以彭提交的patch为代表。 上手成本低。mysqlbinlog原有的选项都能直接利用只是多加了一个闪回选项。闪回特性未来有可能被官方收录。支持离线解析。兼容性差、项目活跃度不高。由于binlog格式的变动如果闪回工具作者不及时对补丁升级则闪回工具将无法使用。目前已有多位人员分别针对mysql5.55.65.7开发了patch部分项目代码公开但总体上活跃度都不高。难以添加新功能实战效果欠佳。在实战中经常会遇到现有patch不满足需求的情况比如要加个表过滤很简单的一个需求代码改动也不会大但对大部分DBA来说改mysql源码还是很困难的事。安装稍显麻烦。需要对mysql源码打补丁再编译生成。 【第二类】独立工具通过伪装成slave拉取binlog来进行处理。以binlog2sql为代表。 兼容性好。伪装成slave拉binlog这项技术在业界应用的非常广泛多个开发语言都有这样的活跃项目MySQL版本的兼容性由这些项目搞定闪回工具的兼容问题不再突出。添加新功能的难度小。更容易被改造成DBA自己喜欢的形式。更适合实战。安装和使用简单。必须开启MySQL server。 【第三类】简单脚本。先用mysqlbinlog解析出文本格式的binlog再根据回滚原理用正则进行匹配并替换。 脚本写起来方便往往能快速搞定某个特定问题。安装和使用简单。支持离线解析。通用性不好。可靠性不好。 【关于DDL的闪回】如果误操作是DDL的话是无法利用binlog做快速回滚的因为即使在row模式下binlog对于DDL操作也不会记录每行数据的变化。要实现DDL快速回滚必须修改MySQL源码使得在执行DDL前先备份老数据。目前有多个mysql定制版本实现了DDL闪回特性阿里林晓斌团队提交了patch给MySQL官方MariaDB预计在不久后加入包含DDL的flashback特性。DDL闪回的副作用是会增加额外存储。 3使用方式 真实的闪回场景中最关键的是能快速筛选出真正需要回滚的SQL。使用开源工具binlog2sql来进行实战演练。binlog2sql由美团点评DBA团队(上海)出品。 https://github.com/danfengcao/binlog2sql MySQL server必须设置以下参数 [mysqld]
server_id 1
log_bin /var/log/mysql/mysql-bin.log
max_binlog_size 1G
binlog_format row
binlog_row_image full systemctl restart mysqlduser需要的最小权限集合 select, super/replication client, replication slave建议授权
GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO select需要读取server端information_schema.COLUMNS表获取表结构的元信息拼接成可视化的sql语句。super/replication client两个权限都可以需要执行SHOW MASTER STATUS, 获取server端的binlog列表。replication slave通过BINLOG_DUMP协议获取binlog内容的权限。 【解析出标准SQL】 shell python binlog2sql.py -h127.0.0.1 -P3306 -uadmin -padmin -dtest -t test3 test4 --start-filemysql-bin.000002输出
INSERT INTO test.test3(addtime, data, id) VALUES (2016-12-10 13:03:38, english, 4); #start 570 end 736
UPDATE test.test3 SET addtime2016-12-10 12:00:00, data中文, id3 WHERE addtime2016-12-10 13:03:22 AND data中文 AND id3 LIMIT 1; #start 763 end 954
DELETE FROM test.test3 WHERE addtime2016-12-10 13:03:38 AND dataenglish AND id4 LIMIT 1; #start 981 end 1147 mysql连接配置 -h host; -P port; -u user; -p password 解析模式 --stop-never 持续解析binlog。可选。默认False同步至执行命令时最新的binlog位置。-K, --no-primary-key 对INSERT语句去除主键。可选。默认False-B, --flashback 生成回滚SQL可解析大文件不受内存限制。可选。默认False。与stop-never或no-primary-key不能同时添加。--back-interval -B模式下每打印一千行回滚SQL加一句SLEEP多少秒如不想加SLEEP请设为0。可选。默认1.0。 解析范围控制 --start-file 起始解析文件只需文件名无需全路径 。必须。--start-position/--start-pos 起始解析位置。可选。默认为start-file的起始位置。--stop-file/--end-file 终止解析文件。可选。默认为start-file同一个文件。若解析模式为stop-never此选项失效。--stop-position/--end-pos 终止解析位置。可选。默认为stop-file的最末位置若解析模式为stop-never此选项失效。--start-datetime 起始解析时间格式%Y-%m-%d %H:%M:%S。可选。默认不过滤。--stop-datetime 终止解析时间格式%Y-%m-%d %H:%M:%S。可选。默认不过滤。 对象过滤 -d, --databases 只解析目标db的sql多个库用空格隔开如-d db1 db2。可选。默认为空。-t, --tables 只解析目标table的sql多张表用空格隔开如-t tbl1 tbl2。可选。默认为空。--only-dml 只解析dml忽略ddl。可选。默认False。--sql-type 只解析指定类型支持INSERT, UPDATE, DELETE。多个类型用空格隔开如--sql-type INSERT DELETE。可选。默认为增删改都解析。用了此参数但没填任何类型则三者都不解析。 【解析出回滚SQL】
shell python binlog2sql.py --flashback -h127.0.0.1 -P3306 -uadmin -padmin -dtest -ttest3 --start-filemysql-bin.000002 --start-position763 --stop-position1147输出
INSERT INTO test.test3(addtime, data, id) VALUES (2016-12-10 13:03:38, english, 4); #start 981 end 1147
UPDATE test.test3 SET addtime2016-12-10 13:03:22, data中文, id3 WHERE addtime2016-12-10 12:00:00 AND data中文 AND id3 LIMIT 1; #start 763 end 954 4闪回实战 背景小明在 17:00 时误删了ry库sys_dict_type表的全部数据需要紧急回滚。 mysql select * from sys_config;
...
10 rows in set (0.00 sec) mysql delete from sys_config;
Query OK, 10 rows affected (0.01 sec)
mysql select * from sys_config;
Empty set (0.00 sec) 直接查看二进制日志内容发现乱码。 mysqlbinlog --no-defaults mysql-bin.000001 登录mysql查看目前的binlog文件。 mysql show master logs;
-----------------------------
| Log_name | File_size |
-----------------------------
| mysql-bin.000001 | 154 |
-----------------------------
1 row in set (0.00 sec) 最新的binlog文件是mysql-bin.000001。目标是筛选出需要回滚的SQL由于误操作人只知道大致的误操作时间首先根据时间做一次过滤。只需要解析test库user表。(注如果有多个sql误操作则生成的binlog可能分布在多个文件需解析多个文件)。 python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p12345678 -dry -tsys_config --start-filemysql-bin.000001 --start-datetime2025-01-13 11:44:00 --stop-datetime2025-01-13 18:00:00 sql.log DELETE FROM ry.sys_config WHERE config_id1 AND config_name主框架页-默认皮肤样式名称 AND config_keysys.index.skinName AND config_valueskin-blue AND config_typeY AND create_byadmin AND create_time2018-03-16 11:33:00 AND update_byry AND update_time2018-03-16 11:33:00 AND remark蓝色 skin-blue、绿色 skin-green、紫色 skin-purple、红色 skin-red、黄色 skin-yellow LIMIT 1; #start 4 end 1014 time 2025-01-13 17:37:02
DELETE FROM ry.sys_config WHERE config_id2 AND config_name用户管理-账号初始密码 AND config_keysys.user.initPassword AND config_value123456 AND config_typeY AND create_byadmin AND create_time2018-03-16 11:33:00 AND update_byry AND update_time2018-03-16 11:33:00 AND remark初始化密码 123456 LIMIT 1; #start 4 end 1014 time 2025-01-13 17:37:02
DELETE FROM ry.sys_config WHERE config_id3 AND config_name主框架页-侧边栏主题 AND config_keysys.index.sideTheme AND config_valuetheme-dark AND config_typeY AND create_byadmin AND create_time2018-03-16 11:33:00 AND update_byry AND update_time2018-03-16 11:33:00 AND remark深黑主题theme-dark浅色主题theme-light深蓝主题theme-blue LIMIT 1; #start 4 end 1014 time 2025-01-13 17:37:02 着重关注上面的 #start 4 end 1014执行转换操作 python binlog2sql/binlog2sql.py -h127.0.0.1 -P3306 -uroot -p12345678 -dry -tsys_config --start-filemysql-bin.000001 --start-position4 --stop-position1014 -B rollback.sql INSERT INTO ry.sys_config(config_id, config_name, config_key, config_value, config_type, create_by, create_time, update_by, update_time, remark) VALUES (4, 账号自助-是否开启用户注册功能, sys.account.registerUser, false, Y, admin, 2018-03-16 11:33:00, ry, 2018-03-16 11:33:00, 是否开启注册用户功能); #start 4 end 1014 time 2025-01-13 17:37:02
INSERT INTO ry.sys_config(config_id, config_name, config_key, config_value, config_type, create_by, create_time, update_by, update_time, remark) VALUES (3, 主框架页-侧边栏主题, sys.index.sideTheme, theme-dark, Y, admin, 2018-03-16 11:33:00, ry, 2018-03-16 11:33:00, 深黑主题theme-dark浅色主题theme-light深蓝主题theme-blue); #start 4 end 1014 time 2025-01-13 17:37:02
INSERT INTO ry.sys_config(config_id, config_name, config_key, config_value, config_type, create_by, create_time, update_by, update_time, remark) VALUES (2, 用户管理-账号初始密码, sys.user.initPassword, 123456, Y, admin, 2018-03-16 11:33:00, ry, 2018-03-16 11:33:00, 初始化密码 123456); #start 4 end 1014 time 2025-01-13 17:37:02 与业务方确认回滚sql没问题执行回滚语句。登录mysql确认回滚成功。 mysql -h127.0.0.1 -P3306 -uroot -p12345678 /root/rollback.sql mysql select * from sys_config;
...
10 rows in set (0.00 sec)
