mysql binlog

binlog日志介绍

1. 什么是 binlog
   binlog是server层共有的，是记录的数据更新历史，主要用来做主从同步和数据的实时备份。

2. binlog 怎么开启
   mysql的配置文件添加相关配置并重启mysql

   # 1. linux打开mysql配置文件
   vi /etc/my.cnf
   1
   2

   # 2. 添加binlog配置
   [mysqld]
   server_id=2 #mysql5.7版本开启binlog强制需要添加该参数
   log_bin = mysql-bin #表示开启binlog并指定binglog文件名
   binlog_format = MIXED #默认
   expire_logs_days = 7 #binlog保留天数
   1
   2
   3
   4
   5
   6

   # 3. 重启mysql
   systemctl restart mysqld.service
   1
   2

3. binlog的内容
   3.1) binlog_format = ROW ，这种模式记录的是每条数据修改的细节。对于批量插入的数据，是以一条一条的insert语句保存，所以ROW模式的binlog文件会很大。

   insert into k_order.`user`(`name`,`number`,`age`) values( '王五', '2545214198902140145',20),( '赵六', '2545214198902140145',20);
   1

   
   3.2) binlog_format = STATEMENT，这种模式记录的就是sql原语句，对于一些公式字段可能会有问题。

   insert into k_order.`user`(`name`,`number`,`age`,update_time) values( '刘7', '2545214198902140145',20,NOW()),( '陈8', '2545214198902140145',20,NOW());
   1

   
   3.3) binlog_format = MIXED，会解析sql语句，对于批量插入采用statement模式，对于一些公式字段采用的是row模式。

   insert into k_order.`user`(`name`,`number`,`age`,update_time) values( '刘7', '2545214198902140145',20,NOW()),( '陈8', '2545214198902140145',20,NOW());
   insert into k_order.`user`(`name`,`number`,`age`) values( '郑9', '2545214198902140145',20);
   1
   2

   

4. binlog的常见操作

   
   -- 查看binlog的配置
   show variables like '%log_bin%'
   -- 查看当前生成了那些binlog日志
   show binary logs; //等价于show master logs;
   show master status;
   -- 查看binlog event
   show binlog events;
   show binlog events in 'mysql-bin.000001';
   1
   2
   3
   4
   5
   6
   7
   8
   9

   ## 将binlog日志解析成sql文件 -v:解析行语句
   mysqlbinlog -v mysql-bin.000001  > aa.sql
   mysqlbinlog -vv mysql-bin.000001  > bb.sql
   mysqlbinlog -vv --base64-output=decode-rows mysql-bin.000001 > cc.sql
   
   # 查看binlog日志文件内容
   mysqlbinlog "mysql-bin.000001" 
   
   # 把binlog日志文件转存为sql文件（建议）
   mysqlbinlog "mysql-bin.000001" > "test.sql"
   
   # 按指定时间恢复数据
   mysqlbinlog --start-datetime="2020-04-25 18:00:00" --stop-datetime="2020-04-26 00:00:00" mysqlbinlog.000002 | mysql -uroot -p1234
   
   # 按事件位置号恢复数据
   mysqlbinlog --start-position=154 --stop-position=957 mysqlbinlog.000002| mysql -uroot -p1234
   1
   2
   3
   4
   5
   6
   7
   8
   9
   10
   11
   12
   13
   14
   15
   16

5. 注意

   binlog的备份不是全量备份，binlog保存的只是从binlog开启之时的增量数据。
   如果在启用binlog之前数据库已经存在数据，那么需要用mysqldump命令先把已经存在的数据进行备份并迁移到从库，这样基于binlog的主从复制才能保证是全量的。