京东到家程序员删库跑路!讲一讲MySQL数据备份杀手锏binlog
网上也经常看到一些段子,某公司程序员对工作不满,删库跑路,老板损失惨重,欲哭无泪。这不最近又爆出一例,京东到家程序员离职当天删库跑路!
那么有没有什么解决方案?
即使数据库真的被删了,也有备份数据,能快速恢复。甚至可以做到实时热备,即使内部炸掉外部用户也感知不到,一片风平浪静。
MySQL 作为当下流行数据库,在 数据备份 、高可用 方面非常有竞争力,今天,我们就重点来讲下
什么是 MySQL 主备
情况一: 客户端的业务操作, 读、写 访问的是主库 主库通过某种机制,将数据实时同步给备库 主库由于有些原因,无法正常响应客户端的请求
情况二: 完成主备切换 客户端读写,访问的是备库(此时备库升级为新主库)
那么,这里面最核心的 数据同步 是如何实现的?
主从同步原理
1、在备库执行 change master 命令 ,绑定主库的信息 mysql> CHANGE MASTER TO MASTER_HOST = "192.168.1.1", MASTER_USER = "repl", MASTER_PASSWORD = "replpassword", MASTER_PORT = 3306, MASTER_AUTO_POSITION = 1, MASTER_RETRY_COUNT = 0, MASTER_HEARTBEAT_PERIOD = 10000; MASTER_HOST :master主机名(或IP地址) MASTER_PORT :mysql实例端口号 MASTER_USER:用户名 MASTER_PASSWORD:密码 MASTER_AUTO_POSITION:如果进行change master to时使用MASTER_AUTO_POSITION = 1,slave连接master将使用基于GTID的复制协议 MASTER_RETRY_COUNT:重连次数 MASTER_HEARTBEAT_PERIOD:复制心跳的周期
https://www.docs4dev.com/docs/zh/mysql/5.7/reference/change-master-to.html
2、备库执行 start slave 命令,备库启动两个线程:I/O thread 和 SQL thread
3、master主库,有数据更新,将此次更新的事件类型写入到主库的 binlog 文件中
4、主库会创建 log dump 线程 ,通知slave有数据更新
5、slave,向master节点的 log dump线程请求一份指定binlog文件位置的副本,并将请求回来的 binlog 存到本地的Relay log 中继日志中
6、slave 再开启一个 SQL 线程 读取Relay log 日志,解析出日志里的命令,并执行,从而保证主备库数据同步
binlog 有哪几种格式
现在,让我们近距离看下 binlog 日志。
binlog 格式有三种: row 、statement 、mixed
接下来,我们开始一个实验:
先创建一个表 CREATE TABLE `person` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT "自增主键", `income` bigint(20) NOT NULL COMMENT "收入", `expend` bigint(20) NOT NULL COMMENT "支出", PRIMARY KEY (`id`), KEY `idx_income` (`income`) ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT="个人收支表";
插入4条记录: insert into person values(50,500,500); insert into person values(60,600,600); insert into person values(70,700,700); insert into person values(80,800,800);
查看binlog模式:
查看当前正在写入的binlog文件:
查看 binlog 中的内容,我们先来看下 row 模式 show binlog events in "mysql-bin.000001";
说明: SET @@SESSION.GTID_NEXT="ANONYMOUS’ BEGIN 开始一个事务 Table_map 记录更新了哪个库、哪张表 Write_rows 记录做了什么操作,详细看binlog需要借助mysqlbinlog工具。 COMMIT /* xid=157 */ 结束一个事务
查找 binlog 文件的物理位置: root@167bfa3785f1:/# find / -name mysql-bin.000001 /var/lib/mysql/mysql-bin.000001
借助 mysqlbinlog 命令,查看具体内容: mysqlbinlog -vv mysql-bin.000001 --start-position=2986;
红框中的内容表示执行了插入命令, insert into person values(80,800,800);
其中,@1、@2、@3 表示 表 person 的第几个字段,不用原始名称,是为了节省空间。
修改 binlog 格式,设置为 STATEMENT ,查看日志格式: set global binlog_format="STATEMENT";
设置之后,需要退出mysql重新连接,才能看到生效 show binlog events in "mysql-bin.000001";
从图中我们可以看出,当 binlog_format=statement 时,binlog 里面记录的就是 SQL 语句的原文。
其中,use tomge :表示要先切到对应的数据库
如果想从指定位置查看binlog,可以增加 from 可选参数,如下: show binlog events in "mysql-bin.000001" from 5168;
statement 与 row 对比:
statement 格式的binlog记录的是sql语句;row 格式的binlog记录的是event(Table_map,Write_rows,Delete_rows)
当 binlog 在 statement 格式下,记录的是sql语句,在主库执行时可能使用的是索引 A;但是同步给备库执行时,可能用了 索引B。
索引不同,同一条sql语句,运行结果可能也不一样。
针对这个场景,我们建议采用 row 格式的 binlog。
即使我们使用了带where 条件(如:income>720)的delete语句,但 binlog 记录的是要删除的主键id(id =80 ),所以不会出现差错。
mixed 格式 的binlog 是个啥?
由于 statement 格式的binlog 可能会导致主库、备库间的数据同步不一致,因此我们会采用 row 格式。
但是, row 格式占用的空间很大,写 binlog 也要占用大量的 IO 资源。
所以,官方提出一种mixed混合模式,集成了两者的优点。
内容如下: mysql会自动判断 statement 格式,是否会引发主备不一致的问题 如果 statement 格式会引起主备不一致的问题,自动使用row 格式。 如果 statement 格式不会引起主备不一致的问题,那么就用statement 格式,
恢复数据
当然,我们还建议把MySQL 的binlog设置成 row 模式,因为它可以用于数据恢复。我们来看下 insert 、update 、delete 三种DML操作如何来恢复数据的。
1、delete:
当我们执行 delete 命令时,如果 binlog_row_image 设置了 "FULL",那么 Delete_rows 里面,包含了删掉的行的所有字段的值。
如果误删了,因为 binlog 记录了所有字段的值,反向执行 insert 就可以了。
当 binlog_row_image 设置为 MINIMAL ,只记录关键信息,比如 id=80
2、insert:
row 格式下,binlog 会 记录 insert 的所有字段值。
如果误操作,只需要根据这些值找到对应的行,再执行 delete 操作即可
3、update:
row 格式下,binlog 会 记录 update 修改前、修改后的整行数据。
如果误操作,只需要用修改前的数据覆盖即可。
通过命令来恢复数据:
如果要执行数据恢复,可以使用下面命令 mysqlbinlog mysql-bin.000001 --start-position=1 --stop-position=3000 | mysql -h192.168.0.1 -P3306 -u$user -p$pwd;
将 mysql-bin.000001 文件位置从 1到3000 的 binlog 在 192.168.0.1 机器的数据库上回放,还原。
未来的税源在哪里?经济高速发展了四十年,到了从增量向存量转换的时代。在这大背景下,很多制度都需要跟随改变。税收模式就是必改的,那么就需要先搞清楚未来的税源在哪里。我认为最大的税源有两块,一是房产税,
2000元的手机和4000元的手机用同一款处理器,钱到底花在哪了呢?导读2000元的手机和4000元的手机用同一款处理器,钱到底花在哪了呢?大家都知道现在的手机根据不同的配置和性能就有不同的价位,很多人都懂得一分价钱一分货这个道理,近几年的手机类型
Groovy脚本实现轻量级规则引擎对于业务复杂多变的项目存在的问题1)当业务规则变更时,对应的代码也得跟着更改,每次即使是小的变更都需要经历开发测试验证上线等过程,变更成本比较大。2)长时间系统变得越来越难以维护。
vivoNEX5霸气回归,181TB可能买不起在vivo这个手机品牌中,其实主打旗舰定位的机型很多,比如vivoNEX系列vivoX系列等,但因为发展策略改变,就目前来说,我们看到vivo中基本上只有X系列作为旗舰主打了。很显
千元洗地机实不实用?3款人气洗地机全面测评并非越贵越好我算是一个比较追求生活品质的90后,会喜欢用一些高科技产品,之前为了省事我就在小米有品买了一台智能扫地机器人,虽然用着是挺不错的,但是还是不够方便,感觉就缺点东西。比如,有时候只是
小康相册数字经济蓬勃发展位于上海市浦东新区的张江人工智能岛是国内首个5GAI全场景商用示范园区,拥有30多个智慧未来应用场景,智能岛以整个园区为载体,为技术与技术产品与产品间的交流提供平台。图为市民体验岛
如果把国家采购联想电脑换成华为你支持吗?支持换成华为,让华为有更大的市场更大的力量开发,去战胜美国的打压。支持太支持了!当然支持!国家采购应该不折不扣面向民族品牌,爱国品牌,民族企业,支持国货!支持,早该换了,关键是要公
跨境收款平台哪个好?东方汇通还是很一流的,可以一对一,并且非常便捷,去应用宝或者官网都可以下载。这要看你们需求了,不同平台优势不同。比如PayPal市场占有率最大,有上亿的用户在使用,电子钱包形式需要
定制式的助听器有功率区分吗?所有的助听器都有攻率的区别的,不论是耳内定制机还是耳背机都是这样的,通常是分为小功率中功率大功率和超大功率的,验配过程中验配师会根据顾客的听力情况来选配合适的功率的。有区分的,通常
司马南和联想柳传志的事,大家怎么看?仁者见仁,智者见智。我认为1。是贫富阶层的交锋。柳,是被富豪阶层尊称的企业改革的代表人物,其先公后私的身份,先公后私的企业,股份与奇高退休金共享的现况,独具特色,万众瞩目,成为众矢
你在用什么工具做知识库?曾经用过不少知识管理工具,例如EvernoteOneNote自建Wiki针式PKM有道云笔记,现在全迁移到了Notion。现在我的知识管理工作流也很简单,整个过程只用几个工具来完成