什么是binlog

MySQL的二进制日志binlog可以说是MySQL最重要的日志，它记录了所有的DDL(create alter drop)和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

DDL

• Data Definition Language 数据库定义语言
  主要的命令有CREATE、ALTER、DROP等，DDL主要是用在定义或改变表（TABLE）的结构，数据类型，表之间的链接和约束等初始化工作上，他们大多在建立表时使用。

DML

• Data Manipulation Language 数据操纵语言

主要的命令是SELECT、UPDATE、INSERT、DELETE，就象它的名字一样，这4条命令是用来对数据库里的数据进行操作的语言

mysqlbinlog常见的选项有以下几个：

• –start-datetime：从二进制日志中读取指定等于时间戳或者晚于本地服务器的时间
• –stop-datetime：从二进制日志中读取指定小于时间戳或者等于本地服务器的时间 取值和上述一样
• –start-position：从二进制日志中读取指定position 事件位置作为开始。
• –stop-position：从二进制日志中读取指定position 事件位置作为事件截至

binlog日志包括两类文件
1）二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件
2）二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句select)语句事件。

binlog备份数据

将anquan数据库备份到/tmp/anquan_$(date +‘%Y-%m-%d’ ).sql.gz文件中：

mysqldump -uroot -proot -B -F -x --master-data=2 anquan | gzip > /tmp/anquan_$(date +'%Y-%m-%d' ).sql.gz
1

参数说明：.sq
-B：指定数据库
-F：刷新日志
-R：备份存储过程等
-x：锁表
–master-data：在备份语句里添加CHANGE MASTER语句以及binlog文件及位置点信息

进入tmp目录下查看备份的数据

进入 /var/lib/mysql目录下查看，每备份一次就会产生一个mysql-bin文件

进入数据库通过show master status命令查看已覆盖最新的bin文件

binlog恢复数据

以图中数据库数据表为例

先对member表中插入一个元素
insert into member values(4,'litaibai',18,'jinyesi');
再将id2的name修改为sudongpo
update member set name='sudongpo' where id=2;
1
2
3
4

修改结果如下

假设将数据库删除了


先进 /var/lib/mysql/将最新一次的备份文件复制一份，在执行一次刷新日志索引操作，重新开始新的binlog日志记录文件，这个文件不会再有后续写入了，因为便于我们分析原因及查找ops节点，以后所有数据库操作都会写入到下一个日志文件。

[root@vm-002 ~]# cd /var/lib/mysql/
[root@vm-002 mysql]# cp -v mysql-bin.000009 /tmp/ 
'mysql-bin.000009' -> '/tmp/mysql-bin.000009'
[root@vm-002 mysql]# ls -al /tmp
mysql-bin.000009
1
2
3
4
5

在进入mysql中刷新一次数据

mysql > flush logs;

读取数据

读取数据

进入tmp目录下
cd /tmp
解压备份文件
gzip -d anquan_2022-07-16.sql.gz
导入备份文件
mysql -uroot -proot < anquan_2022-07-16.sql.gz 
1
2
3
4
5
6

读取数据有两种方法

方法一：使用mysqlbinlog读取binlog日志：

[root@vm-002 ~]# cd /var/lib/mysql/
[root@vm-002 mysql]# mysqlbinlog mysql-bin.000009

发现只恢复到之前未更新数据的状态的，所以更详细的恢复数据推荐第二种方法。

方法二：登录服务器，并查看(推荐此种方法)

mysql> show binlog events in 'mysql-bin.000003';

通过分析，造成数据库破坏的pos点区间是介于 896–943 之间（这是按照日志区间的pos节点算的），只要恢复到896前就可。

从binlog日志恢复数据
恢复命令的语法格式：
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

--------------------------------------------------------
常用参数选项解释：
–start-position=875 起始pos点
–stop-position=954 结束pos点
–start-datetime=“2016-9-25 22:01:08” 起始时间点
–stop-datetime=“2019-9-25 22:09:46” 结束时间点
–database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)
--------------------------------------------------------
不常用选项：
-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取binlog日志
–read-from-remote-server 从某个MySQL服务器上读取binlog日志
--------------------------------------------------------
小结：实际是将读出的binlog日志内容，通过管道符传递给mysql命令。这些命令、文件尽量写成绝对路径；

a）完全恢复(需要手动vim编辑mysql-bin.000009，将那条drop语句剔除掉)

[root@vm-002 backup]# cp /var/lib/mysql/mysql-bin.000009 /tmp
[root@vm-002 backup]# mysqlbinlog mysql-bin.000009 > 000009.sql
[root@vm-002 backup]# vim 000009.sql #删除里面的drop语句
[root@vm-002 backup]# mysql -uroot -p -v < 000009.sql

温馨提示：
在恢复全备数据之前必须将该binlog文件移出，否则恢复过程中，会继续写入语句到binlog，最终导致增量恢复数据部分变得比较混乱！
可参考：https://www.cnblogs.com/kevingrace/p/5904800.html

发现数据找回

b）指定pos结束点恢复(部分恢复)：

–stop-position=471 pos结束节点（按照事务区间算，是471）
注意：
此pos结束节点介于“member表原始数据”与更新“name=‘李四’”之前的数据，这样就可以恢复到更改插入litaibai之前的数据了。
操作如下：
[root@vm-002 ~]# mysqlbinlog --start-position=427 --stop-position=548 --database=anquan /var/lib/mysql/mysql-bin.000009 | mysql -uroot -proot -v

mysqlbinlog --start-position=621 --stop-position=734 --database=anquan /var/lib/mysql/mysql-bin.000009 | mysql -uroot -proot -v