[mysql] Binlog的配置和使用

2019-06-18 本文已影响0人 ericdejavu

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以这种格式

[mysql] Binlog的配置和使用

binlog简介

binlog的配置

server_id

log_bin

expire_logs_days

binlog_format

max_binlog_size

binlog_do_db

猜你喜欢

热点阅读