mysql日志binlog

xvpenghao
160 阅读5分钟

binlog（归档日志）用于记录，mysql的写入性操作，以二进制的形式保存到磁盘上，binlog是mysql的逻辑日志，有server层记录

参考链接

其他

  • binlog是mysql的逻辑日志理解

binlog记录的是执行的sql的逻辑，

  • 有server层记录

msyql从架构上可以分为， server层和存储引擎层（Innodb，MyISAM，Memory等），binlog是所有 引擎都有的，是在server端记录的

  • 查看相关变量信息

# 查看logbin的位置
show variables like "%log_bin%"; 

# 查看binlog的详细参数信息
show variables like "%binlog%" ; 

# 查看当前使用 binlog的文件
show master status; 
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000003 |      155 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
  • binlog的开启，修改配置文件,重启mysql
[mysqld]
#设置日志三种格式：STATEMENT、ROW、MIXED 。
binlog_format = mixed
#设置日志路径，注意路经需要mysql用户有权限写 , 可以 与log_bin_basename的地址一致
log-bin = /data/mysql/logs/mysql-bin.log
#设置binlog清理时间
expire_logs_days = 7
#binlog每个日志文件大小
max_binlog_size = 100m
#binlog缓存大小
binlog_cache_size = 4m
#最大binlog缓存大小
max_binlog_cache_size = 512m
  • 查看my.cnf的位置
mysql --help | grep 'Default options' -A 1

binlog使用场景

  • 主从复制

在 Master 端开启binlog日志，将 binglog发送到各个slave端，slave端重放biglog从而达到。

  • 数据恢复

使用 mysqlbinlog基于时间点还原。

什么时候写盘

对于InnoDB而言（Innodb才有事务），只有事务提交时才会记录 binlog，此时还在内存中，通过参数 sync_binlog 控制刷盘的时机。取值范围0-N

0：不去强制要求，有系统自行判断何时写入磁盘

1：每次commit都会将 binglog写入磁盘

N：每N个事务才会将 binlog写入磁盘

建议将 sync_binlog=1,这样可以保证mysql 异常重启之后，binlog不会丢失。

binlog日志格式

binlog有3中日志格式, statement, row，mixed 5.7之前是 statement, 5.7之后值 row

statement

基于sql语句的复制，每一个会修改数据的sql执行语句都会记录到 master的binlog中，

  • 优点

不需要记录每一行的数据变化，减少了binlog的日志量。

  • 缺点

为了保证复制的一致性，还要记录执行sql的一些上下文信息，

在某些情况可能会导致 主从不一致，从库无法复制的，【udf（用户自定义函数），sleep，uuid，last_insert_id等函数。】

row

基于行的复制，不记录执行sql的上下文，仅记录哪条数据被修改了。

  • 优点

不会出现某些特定情况下，函数和存储过程的调用，无法被复制的问题，

  • 缺点

会产生的大量的日志，因为记录的是 可能影响的行，所以 像update，alter，则会产生大量的日志

Mixed（混合）

在Mixed模式下，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，则采用row格式保存binlog

binglog 3中格式存储形式

测试sql语句

update t_device_bind set  utid=concat(?,utid) where id in (1000,1001,1002);

statemet

BEGIN
/*!*/;
# at 331
#211127 15:13:38 server id 1  end_log_pos 499 CRC32 0x561cb5f0 	Query	thread_id=8	exec_time=0	error_code=0
use `db_test`/*!*/;
SET TIMESTAMP=1637997218/*!*/;

update t_device_bind set  utid=concat('m',utid) where id in (1000,1001,1002)

/*!*/;
# at 499
#211127 15:13:38 server id 1  end_log_pos 530 CRC32 0x069a3ccf 	Xid = 12
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;

row

BEGIN
/*!*/;
# at 321
#211127 12:00:55 server id 1  end_log_pos 389 CRC32 0x8cc8837c 	Table_map: `db_test`.`t_device_bind` mapped to number 87
# at 389
#211127 12:00:55 server id 1  end_log_pos 528 CRC32 0x193da57f 	Update_rows: table id 87 flags: STMT_END_F

BINLOG '
d62hYRMBAAAARAAAAIUBAAAAAFcAAAAAAAEAB2RiX3Rlc3QADXRfZGV2aWNlX2JpbmQAAgMPAsAA
AAEBAAIBIXyDyIw=
d62hYR8BAAAAiwAAABACAAAAAFcAAAAAAAEAAgAC//8A6AMAAAthYmNkZWYtMTAwMADoAwAADHBh
YmNkZWYtMTAwMADpAwAADGFiY2RkZGQtMTAwMQDpAwAADXBhYmNkZGRkLTEwMDEA6gMAAAlhYmNk
LTEwMDIA6gMAAApwYWJjZC0xMDAyf6U9GQ==
'/*!*/;

# at 528
#211127 12:00:55 server id 1  end_log_pos 559 CRC32 0x6510fba7 	Xid = 16
COMMIT/*!*/;


SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;

执行 mysqlbinlog --base64-output=AUTO -v mysql-bin.000003 对 binlog中的内容进行解码，查看详细的存储信息，可以发现在 row 模式下，记录的是操作影响的行。同时更新的字段，也会作为条件

BEGIN
/*!*/;
# at 321
#211127 12:00:55 server id 1  end_log_pos 389 CRC32 0x8cc8837c 	Table_map: `db_test`.`t_device_bind` mapped to number 87
# at 389
#211127 12:00:55 server id 1  end_log_pos 528 CRC32 0x193da57f 	Update_rows: table id 87 flags: STMT_END_F

BINLOG '
d62hYRMBAAAARAAAAIUBAAAAAFcAAAAAAAEAB2RiX3Rlc3QADXRfZGV2aWNlX2JpbmQAAgMPAsAA
AAEBAAIBIXyDyIw=
d62hYR8BAAAAiwAAABACAAAAAFcAAAAAAAEAAgAC//8A6AMAAAthYmNkZWYtMTAwMADoAwAADHBh
YmNkZWYtMTAwMADpAwAADGFiY2RkZGQtMTAwMQDpAwAADXBhYmNkZGRkLTEwMDEA6gMAAAlhYmNk
LTEwMDIA6gMAAApwYWJjZC0xMDAyf6U9GQ==
'/*!*/;
### UPDATE `db_test`.`t_device_bind`
### WHERE
###   @1=1000
###   @2='abcdef-1000'
### SET
###   @1=1000
###   @2='pabcdef-1000'
### UPDATE `db_test`.`t_device_bind`
### WHERE
###   @1=1001
###   @2='abcdddd-1001'
### SET
###   @1=1001
###   @2='pabcdddd-1001'
### UPDATE `db_test`.`t_device_bind`
### WHERE
###   @1=1002
###   @2='abcd-1002'
### SET
###   @1=1002
###   @2='pabcd-1002'
# at 528
#211127 12:00:55 server id 1  end_log_pos 559 CRC32 0x6510fba7 	Xid = 16
COMMIT/*!*/;
# at 559
#211127 15:00:58 server id 1  end_log_pos 582 CRC32 0xae82390a 	Stop
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;

mixed

BEGIN
/*!*/;
# at 331
#211127 15:06:08 server id 1  end_log_pos 499 CRC32 0xfb0cd712 	Query	thread_id=8	exec_time=0	error_code=0
use `db_test`/*!*/;
SET TIMESTAMP=1637996768/*!*/;

update t_device_bind set  utid=concat('w',utid) where id in (1000,1001,1002)

/*!*/;
# at 499
#211127 15:06:08 server id 1  end_log_pos 530 CRC32 0xa0c1eaa9 	Xid = 13
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
avatar
文章
阅读
粉丝