binlog

一、什么是binlog?

• binlog是一个二进制格式的文件，可以说是MySQL最重要的日志，它记录了所有的DDL(create、alter、drop)和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。
• 默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令（比如，cat，vi等）查看，而使用mysqlbinlog解析查看。

总结：binlog记录数据库增删改,不记录查询的二进制日志.

二、binlog有什么作用？

• 主要作用是用于数据库的主从复制及数据的增量恢复。
• 当有数据写入到数据库时，还会同时把更新的SQL语句写入到对应的binlog文件里，这个文件就是上文说的binlog文件。使用mysqldump备份时，只是对一段时间的数据进行全备，但是如果备份后突然发现数据库服务器故障，这个时候就要用到binlog的日志了。

三、binlog日志分类

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

四、binlog与redolog的区别？

这两者使用方式不一样

• binlog 会记录表所有更改操作，包括更新删除数据，更改表结构等等，主要用于人工恢复数据，而 redo log 对于我们是不可见的，它是 InnoDB 用于保证 crash-safe 能力的，也就是在事务提交后MySQL崩溃的话，可以保证事务的持久性，即事务提交后其更改是永久性的。一句话概括：binlog 是用作人工恢复数据，redolog 是 MySQL 自己使用，用于保证在数据库崩溃时的事务持久性。
• redo log 是 InnoDB 引擎特有的，binlog 是 MySQL 的 Server 层实现的,所有引擎都可以使用。
• redo log 文件是固定大小的，是循环写的，写满了会从头继续写，而 binlog 是追加写的，写满了再新建文件接着写。

五、如何配置binlog？

注意：在kali系统中，binlog配置文件位于：

/etc/mysql/my.cnf

1、打开配置文件：

vim /etc/mysql/my.cnf

2、在配置文件的末尾添加如下代码：

[mysqld]
log-bin=mysql-bin

3、重启mysqld服务使配置生效

/etc/init.d/mysqld stop
/etc/init.d/mysqld restart

**注意：**每次服务器（数据库）重启，服务器会调用flush logs;，新创建一个binlog日志！

4、查看binlog日志是否开启

进入MySQL查看：

myslq -uroot -p

mysql> show variables like 'log_%';

如上图所示则说明binlog开启成功

六、常用的binlog日志操作命令

1、查看所有binlog日志列表

mysql> show master logs;

2、查看master状态，即最后(最新)一个binlog日志的编号名称，及其最后一个操作事件pos结束点(Position)值

mysql> show master status;

3、flush刷新log日志，自此刻开始产生一个新编号的binlog日志文件

注意：每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；

4、重置(清空)所有binlog日志

七、查看binlog日志内容

Ⅰ 使用mysqlbinlog自带查看命令法

在kali系统中，myslq的binlog位于 /var/lib/mysql/ 路径下

使用mysqlbinlog命令查看binlog日志内容，下面截取其中的一个片段分析：

mysqlbinlog	mysql-bin.000001

..............
# at 624
#160925 21:29:53 server id 1 end_log_pos 796 Query thread_id=3 exec_time=0 error_code=0
SET TIMESTAMP=1474810193/*!*/;
insert into member(`name`,`sex`,`age`,`classid`) values('wangshibo','m',27,'cls1'),('guohuihui','w',27,'cls2')     **#执行的sql语句**
/*!*/;
# at 796
#160925 21:29:53 server id 1 end_log_pos 823 Xid = 17          **#执行的时间**
.............

名称解释：
server id 1 ： 数据库主机的服务号；
end_log_pos 796： sql结束时的pos节点
thread_id=3： 线程号

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-R23nNCdV-1657975954231)(https://s2.loli.net/2022/07/16/UNslHQ5IiETdO9c.png)]

例子：

例：mysqlbinlog -d dadong dadong-bin.000001 dadong-bin.000002 -r bin.log   ##利用mysqlbinlog解析binlog文件到bin.log中。
利用mysqlbinlog  -d参数详解指定库的binlog日志
-d 指定解析dadong数据库，
-r 接卸成sql语句，指定生成的文件

按照位置截取binlog内容

参考文章：binlog介绍 - 芹溪 - 博客园 (cnblogs.com)

按照位置截取binlog内容的优点是精确，但是要花费时间选择位置，例如：要截取dadong-bin.000009文件从位置365到位置465的日志，命令如下：

[[email protected] ~]# mysqlbinlog dadong-bin.000009 --start-position=365 --stop-position=465 -r pos.sql
提示：开始位置必须存在binlog里，结尾位置点可以不存在。
若指定了开始位置，不指定结束位置，则会截取开始处到结尾的binlog日志：
mysqlbinlog dadong-bin.000009 --start-position=365  -r pos.sql
若指定了结束位置，不指定开始位置，则截取最开始到最后面的全部binlog日志：
mysqlbinlog dadong-bin.000009  --stop-position=465 -r pos.sql
所谓的位置点，就是mysqlbinlog解析文件里的不同行行首的“#at 数字”标识的数据。

例子：

mysqlbinlog dadong-bin.000009 --start-position=365 --stop-position=456 -r pos.sql

mysqlbinlog dadong-bin.000005 --start-position=2265 --stop-position=2552 -r pos.sql
mysqlbinlog dadong-bin.000009 --start-position=365 --stop-position=456 -r pos.sql
mysqlbinlog dadong-bin.000009 --start-position=365 -r pos.sql
mysqlbinlog dadong-bin.000009 --stop-position=456 -r pos.sql

截取部分binlog根据pos
mysqlbinlog dadong-bin.000009 --start-position=365 --stop-position=456 -r pos.sql
mysqlbinlog dadong-bin.000009 --start-position=365 -r pos.sql
mysqlbinlog dadong-bin.000009 --stop-position=456 -r pos.sql

截取部分binlog根据时间
mysqlbinlog dadong-bin.000009 --start-datetime='2014-10-16 17:14:15' --stop-datetime='2014-10-16 17:15:15' -r time.sql
mysqlbinlog dadong-bin.000009 --start-datetime='2014-10-16 17:14:15'  -r time.sql
mysqlbinlog dadong-bin.000009  --stop-datetime='2014-10-16 17:15:15' -r time.sql

Ⅱ 在MySQL中使用show命令查询

命令格式：

mysql> show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];

参数解释：

IN 'log_name' ：指定要查询的binlog文件名(不指定就是第一个binlog文件)
FROM pos	 ：指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
LIMIT [offset] ：偏移量(不指定就是0)
row_count ：查询总条数(不指定就是所有行)

MariaDB [test]> show binlog events in 'mysql-bin.000001'\G;

MariaDB [test]> show binlog events in 'mysql-bin.000001'\G;
*************************** 1. row ***************************
   Log_name: mysql-bin.000001
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 256
       Info: Server ver: 10.5.12-MariaDB-1-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000001
        Pos: 256
 Event_type: Gtid_list
  Server_id: 1
End_log_pos: 285
       Info: []
*************************** 3. row ***************************
   Log_name: mysql-bin.000001
        Pos: 285
 Event_type: Binlog_checkpoint
  Server_id: 1
End_log_pos: 328
       Info: mysql-bin.000001
*************************** 4. row ***************************
   Log_name: mysql-bin.000001
        Pos: 328
 Event_type: Gtid
  Server_id: 1
End_log_pos: 370
       Info: BEGIN GTID 0-1-1
*************************** 5. row ***************************
   Log_name: mysql-bin.000001
        Pos: 370
 Event_type: Intvar
  Server_id: 1
End_log_pos: 402
       Info: INSERT_ID=8
*************************** 6. row ***************************
   Log_name: mysql-bin.000001
        Pos: 402
 Event_type: Query
  Server_id: 1
End_log_pos: 565
       Info: use `test`; insert into member(`name`,`age`,`classid`) values('wangshibo',27,'cls1'),('guohuihui',27,'cls2')
*************************** 7. row ***************************
   Log_name: mysql-bin.000001
        Pos: 565
 Event_type: Xid
  Server_id: 1
End_log_pos: 596
       Info: COMMIT /* xid=440 */
7 rows in set (0.000 sec)

上面这条语句可以将指定的binlog日志文件，分成有效事件行的方式返回，并可使用limit指定pos点的起始偏移，查询条数！
如下操作示例：
a）查询第一个(最早)的binlog日志：

mysql> show binlog events\G;

b）指定查询 mysql-bin.000002这个文件：

mysql> show binlog events in 'mysql-bin.000002'\G;

c）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起：

mysql> show binlog events in 'mysql-bin.000002' from 624\G;

d）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，查询10条（即10条语句）

mysql> show binlog events in 'mysql-bin.000002' from 624 limit 10\G;

e）指定查询 mysql-bin.000002这个文件，从pos点:624开始查起，偏移2行（即中间跳过2个），查询10条

mysql> show binlog events in 'mysql-bin.000002' from 624 limit 2,10\G;

八、利用binlog日志恢复mysql数据

Ⅰ创建测试案例

1、进入MySQL

mysql -uroot -p 

2、创建一个test库

create database test;

3、创建一个member表

use test;
CREATE TABLE IF NOT EXISTS `member` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(16) NOT NULL,
`age` tinyint(3) unsigned NOT NULL,
`classid` varchar(6) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

4、查看表是否创建成功：

show tables;

查看表的结构：

desc member;

5、插入数据

insert into member(`name`,`age`,`classid`) values('wangshibo',27,'cls1'),('guohuihui',27,'cls2');

查看数据：

select * from member;

Ⅱ 进行场景模拟

1、先将test数据库进行备份

将test数据库备份到/opt/backup/ops_$(date +%F).sql.gz文件中：

┌──(rootkali)-[/var/lib/mysql]
└─# mysqldump -uroot -p -B -F -R -x --master-data=2 test|gzip >/opt/backup/test_$(date +%F).sql.gz 
Enter password: 

查看是否备份成功：

ls /opt/backup/

参数说明：

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

由于上面在全备份的时候使用了-F选项，那么当数据备份操作刚开始的时候系统就会自动刷新log，这样就会自动产生一个新的binlog日志，这个新的binlog日志就会用来记录备份之后的数据库“增删改”操作

2、在工作中插入新的数据

insert into member(`name`,`age`,`classid`) values('demo0',22,'cls3'),('demo1',11,'cls4');

查看数据：

select * from member;

3、假如一不小心删除了test库

drop database test;

Ⅲ 恢复备份数据

1、这种时候，一定不要慌张！！！
先仔细查看最后一个binlog日志，并记录下关键的pos点，到底是哪个pos点的操作导致了数据库的破坏(通常在最后几步)；

先备份一下最后一个binlog日志文件：

┌──(rootkali)-[/]
└─# cd /var/lib/mysql/ ┌──(rootkali)-[/var/lib/mysql]
└─# ls 
aria_log.00000001  ib_buffer_pool  ibtmp1             mysql-bin.000001  mysql-bin.000004  mysql_upgrade_info
aria_log_control   ibdata1         multi-master.info  mysql-bin.000002  mysql-bin.000005  performance_schema
debian-10.5.flag   ib_logfile0     mysql              mysql-bin.000003  mysql-bin.index
                                                                                                
┌──(rootkali)-[/var/lib/mysql]
└─# cp -v mysql-bin.000005 /opt/backup/
'mysql-bin.000005' -> '/opt/backup/mysql-bin.000005'
                                                                                                      
┌──(rootkali)-[/var/lib/mysql]
└─# ls /opt/backup/
mysql-bin.000005  test_2022-07-16.sql.gz

2、接着执行一次刷新日志索引操作，重新开始新的binlog日志记录文件。

按理说mysql-bin.000005这个文件不会再有后续写入了，但是为了便于我们分析原因及查找ops节点，所以让之后所有数据库操作都写入到下一个日志文件。

刷新日志索引：

mysql> flush logs;

3、读取binlog日志，分析问题。
方法一：使用mysqlbinlog读取binlog日志：

cd /var/lib/mysql/
mysqlbinlog mysql-bin.000005

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

mysql> show binlog events in 'mysql-bin.000005';
或者
mysql> show binlog events in 'mysql-bin.000005'\G;

MariaDB [(none)]> show binlog events in 'mysql-bin.000005'\G;
*************************** 7. row ***************************
   Log_name: mysql-bin.000005
        Pos: 459
 Event_type: Query
  Server_id: 1
End_log_pos: 614
       Info: use `test`; insert into member(`name`,`age`,`classid`) values('demo0',00,'cls3'),('demo1',11,'cls4')
*************************** 8. row ***************************
.......
.......

*************************** 10. row ***************************
   Log_name: mysql-bin.000005
        Pos: 687
 Event_type: Query
  Server_id: 1
End_log_pos: 772
       Info: drop database test
*************************** 11. row ***************************
   Log_name: mysql-bin.000005
        Pos: 772
 Event_type: Rotate
  Server_id: 1
End_log_pos: 819
       Info: mysql-bin.000006;pos=4
11 rows in set (0.000 sec)

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

4、使用之前全备份的数据，恢复到当时备份时：

在kali中：

┌──(rootkali)-[/var/lib/mysql]
└─# cd /opt/backup/ 
┌──(rootkali)-[/opt/backup]
└─# ls 
mysql-bin.000005  test_2022-07-16.sql.gz                  
             
┌──(rootkali)-[/opt/backup]
└─# gzip -d test_2022-07-16.sql.gz 
┌──(rootkali)-[/opt/backup]
└─# ls
mysql-bin.000005  test_2022-07-16.sql                                                                                                                          
┌──(rootkali)-[/opt/backup]
└─# mysql -uroot -p -v < test_2022-07-16.sql 
Enter password: 

查看恢复的数据：

。

但是这仅仅只是恢复当时备份时的数据，备份之后插入的数据没有恢复过来

5、从binlog日志恢复数据
恢复命令的语法格式：

mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

常用参数选项解释：

--start-position=687 起始pos点
--stop-position=772 结束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命令。这些命令、文件尽量写成绝对路径；

6、让数据完全恢复

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

cd /opt/backup/  
cp /var/lib/mysql/mysql-bin.000006 /opt/backup
mysqlbinlog /opt/backup/mysql-bin.000006 > /opt/backup/000006.sql
vim /opt/backup/000006.sql #删除里面的drop语句
mysql -uroot -p -v < /opt/backup/000006.sql

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

b）指定pos结束点恢复(部分恢复)：
–stop-position=687 pos结束节点（按照事务区间算，是687）

/usr/bin/mysqlbinlog --stop-position=687 --database=test /var/lib/mysql/mysql-bin.000005 | /usr/bin/mysql -uroot -p  -v test

c）按照事务区间单独恢复,恢复[ 459,614]

/usr/bin/mysqlbinlog --start-position=459 --stop-position=614 --database=ops /var/lib/mysql/mysql-bin.000005 | /usr/bin/mysql -uroot -p -v ops