created by Dejavu

mysql版本: mysql 5.7.26

操作系统: ubuntu 18.04

cd /var/log/mysql

sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001

binlog简介

binlog是一个二进制格式的文件，用于记录用户对数据库增量操作的SQL语句信息，例如更改数据库表和更改内容的SQL语句都会记录到binlog里，但是对库表等内容的查询不会记录。

默认情况下，binlog日志是二进制格式的，不能使用查看文本工具的命令(比如，cat，vi等)查看，而使用mysqlbinlog解析查看

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

用于数据恢复，主从复制及数据的增量恢复

binlog的配置

配置文件的各个option作用

server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
expire_logs_days = 10
binlog_format = "MIXED"
max_binlog_size = 100M
binlog_do_db = include_database_name
binlog_ignore_db = include_database_name
server_id
mysql> mysqlvariables like '%server_id%';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| server_id | 1 |
| server_id_bits | 32 |
+----------------+-------+

server_id 自然数，标识数据库id

log_bin

日志路径，填入后重启数据库，binlog启动并开始记录

expire_logs_days

日志过期时间，设置为0则永不过期

-- 修改过期时间

mysql> set global expire_logs_days=7;

-- 设置之后不会立即清除，触发条件是：

-- binlog大小超过max_binlog_size

-- 或者执行 flush logs

mysql> flush logs;

-- 清除000055和以前的binlog

mysql>purge binary logs to 'bin.000055';

binlog_format

① STATEMENT模式(SBR)

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式(RBR)

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式(MBR)

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

以下是操作实例

statement

执行插入操作

insert into role (name, value) values ('name1', 1), ('name2', 2);

命令行查看结果

sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
......
SET TIMESTAMP=1560862594/*!*/;
insert into role (name, value) values ('name1', 1), ('name2', 2)
......
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
更新操作
update role set value=3;
命令行查看结果
sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
......
SET TIMESTAMP=1560862624/*!*/;
update role set value=3
......
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
row
执行插入操作
insert into role (name, value) values ('name1', 1), ('name2', 2);
命令行查看结果
sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
......
### INSERT INTO `aprint`.`role`
### SET
### @1=1
### @2='name1'
### @3=1
### @4=0
### INSERT INTO `aprint`.`role`
### SET
### @1=2
### @2='name2'
### @3=2
### @4=0
......
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
更新操作
update role set value=3;
命令行查看结果
sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
......
### UPDATE `aprint`.`role`
### WHERE
### @1=1
### @2='name1'
### @3=1
### @4=0
### SET
### @1=1
### @2='name1'
### @3=3
### @4=0
### UPDATE `aprint`.`role`
### WHERE
### @1=2
### @2='name2'
### @3=2
### @4=0
### SET
### @1=2
### @2='name2'
### @3=3
### @4=0
......
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
mixed
执行插入操作
insert into role (name, value) values ('name1', 1), ('name2', 2);
命令行查看结果
sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
......
SET TIMESTAMP=1560862362/*!*/;
insert into role (name, value) values ('name1', 1), ('name2', 2)
......
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
更新操作
update role set value=3;
命令行查看结果
sudo mysqlbinlog --base64-output=decode-rows -v /var/log/mysql/mysql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
......
SET TIMESTAMP=1560862439/*!*/;
update role set value=3
......
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
max_binlog_size

MySQL实例空间内生成binlog日志的规则如下：

通常情况下，当前binlog大小超过max_binlog_size或超过6小时会切换到下一序号文件继续写入，即写满max_binlog_size或超过6小时就会生成新的binlog日志文件。新的binlog文件继续写入，老的binlog文件并不会立刻上传，会异步上传。

有些情况下，binlog日志不满max_binlog_size就不再写入，比如由于命令的执行、系统重启等原因。

有些情况下，会出现binlog文件尺寸超过max_binlog_size的情况，比如当时在执行大事务，不断写入binlog导致当前binlog文件尺寸超过max_binlog_size。

binlog_do_db

后更要记录的数据库名database1,database2以这种格式