2020-03-15

MySQL的bin-log

什么是binlog？

mysql-binlog是MySQL数据库的二进制日志，用于记录用户对数据库操作的SQL语句（除了数据查询语句，即记录insert、update、delete的sql）信息。
可以使用mysqlbin命令查看二进制日志的内容。

MySQL bin-log 的格式

binlog的格式也有三种：STATEMENT、ROW、MIXED

1、 STATMENT模式：基于SQL语句的复制(statement-based replication, SBR)，每一条会修改数据的sql语句会记录到binlog中。
优点：不需要记录每一条SQL语句与每行的数据变化，这样子binlog的日志也会比较少，减少了磁盘IO，提高性能。
缺点：在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)
2、ROW模式：基于行的复制(row-based replication, RBR)：不记录每一条SQL语句的上下文信息，仅需记录哪条数据被修改了，修改成了什么样子了。
优点：不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。
缺点：会产生大量的日志，尤其是alter table的时候会让日志暴涨。
3、MIXED模式：混合模式复制(mixed-based replication, MBR)：以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

服务器上用哪种模式好呢？

我们来举例子说明：

1. insert into xxtable values (x,y,z)为例

影响：1行,且为新增1行, 对于其他行没有影响. 这个情况,用row格式,直接复制磁盘上1行的新增变化。

1. update xxtable set age=21 where name=’sss’为例
  影响：一般也只是影响1行. 用row也比较合适。
1. 过年发红包,全公司的人,都涨薪100元.
  update xxtable set salary=salary+100;
  影响: 这个语句带来的影响是针对每一行的, 因此磁盘上很多row都发生了变化.此处适合用statment格式的日志.

MySQL后来又提供了一个新的混合模式（MIXED），很好理解为上述两种的模式的自由切换。Mysql会根据执行SQL来选择使用STATEMENT还是ROW模式。
所以我们用这种模式是最好的，能兼顾两者的优点。

MySQL如何配置bin-log

在MySQL配置文件my.cnf文件中的mysqld节中添加下面的配置文件：

[mysqld]
#设置日志格式
binlog_format = mixed

#设置日志路径，注意路经需要mysql用户有权限写
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

重启MySQL生效，如果不方便重启服务，也可以直接修改对应的变量即可。

MySQL的bin-log如何查看

MySQL的bin-log是二进制格式的无法直接查看，MySQL提供了两种方式进行查看。

1、简单查看 show binlog events
语法：

SHOW BINLOG EVENTS
[IN 'log_name']
[FROM pos]
[LIMIT [offset,] row_count]

在MySQL客户端中执行


mysql> show binlog events in 'mysql-bin.000004' \G
*************************** 1. row ***************************
   Log_name: mysql-bin.000004
        Pos: 4
 Event_type: Format_desc
  Server_id: 1
End_log_pos: 107
       Info: Server ver: 5.5.48-log, Binlog ver: 4
*************************** 2. row ***************************
   Log_name: mysql-bin.000004
        Pos: 107
 Event_type: Query
  Server_id: 1
End_log_pos: 331
       Info: use `db01`; CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*************************** 3. row ***************************
   Log_name: mysql-bin.000004
        Pos: 331
 Event_type: Query
  Server_id: 1
End_log_pos: 399
       Info: BEGIN
*************************** 4. row ***************************
   Log_name: mysql-bin.000004
        Pos: 399
 Event_type: Intvar
  Server_id: 1
End_log_pos: 427
       Info: INSERT_ID=1
*************************** 5. row ***************************
   Log_name: mysql-bin.000004
        Pos: 427
 Event_type: Query
  Server_id: 1
End_log_pos: 563
       Info: use `db01`; insert into `db01`.`student` ( `id`, `student_name`) values ( '0', 'jim')
*************************** 6. row ***************************
   Log_name: mysql-bin.000004
        Pos: 563
 Event_type: Xid
  Server_id: 1
End_log_pos: 590
       Info: COMMIT /* xid=22 */
*************************** 7. row ***************************
   Log_name: mysql-bin.000004
        Pos: 590
 Event_type: Query
  Server_id: 1
End_log_pos: 842
       Info: use `db02`; CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(32) NOT NULL,
  `birthday` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
*************************** 8. row ***************************
   Log_name: mysql-bin.000004
        Pos: 842
 Event_type: Query
  Server_id: 1
End_log_pos: 910
       Info: BEGIN
*************************** 9. row ***************************
   Log_name: mysql-bin.000004
        Pos: 910
 Event_type: Intvar
  Server_id: 1
End_log_pos: 938
       Info: INSERT_ID=2
*************************** 10. row ***************************
   Log_name: mysql-bin.000004
        Pos: 938
 Event_type: Query
  Server_id: 1
End_log_pos: 1077
       Info: use `db01`; insert into `db01`.`student` ( `id`, `student_name`) values ( '0', '刘洋')
*************************** 11. row ***************************
   Log_name: mysql-bin.000004
        Pos: 1077
 Event_type: Xid
  Server_id: 1
End_log_pos: 1104
       Info: COMMIT /* xid=128 */
*************************** 12. row ***************************
   Log_name: mysql-bin.000004
        Pos: 1104
 Event_type: Query
  Server_id: 1
End_log_pos: 1172
       Info: BEGIN
*************************** 13. row ***************************
   Log_name: mysql-bin.000004
        Pos: 1172
 Event_type: Intvar
  Server_id: 1
End_log_pos: 1200
       Info: INSERT_ID=3
*************************** 14. row ***************************
   Log_name: mysql-bin.000004
        Pos: 1200
 Event_type: Query
  Server_id: 1
End_log_pos: 1342
       Info: use `db01`; insert into `db01`.`student` ( `id`, `student_name`) values ( '0', '张从容')
*************************** 15. row ***************************
   Log_name: mysql-bin.000004
        Pos: 1342
 Event_type: Xid
  Server_id: 1
End_log_pos: 1369
       Info: COMMIT /* xid=146 */
15 rows in set (0.00 sec)

mysql>

其实我在14和15行之间，进行了一次Select查询，但是这个操作在bin-log中并没有发现，印证了开头说的bin-log不记录Select查询。

上述出现的参数解释：

Log_name:The name of the file that is being listed.
Pos：The position at which the event occurs.
Event_type：An identifier that describes the event type.
Server_id：The server ID of the server on which the event originated.
End_log_pos：The position at which the next event begins, which is equal to Pos plus the size of the event.
Info：More detailed information about the event type. The format of this information depends on the event type.

1、详细查看 mysqlbinlog
1
语法： mysqlbinlog [bin-log-name]
问题1：无法识别的编码
1
2
Sam-Mac:data Sam$ /usr/local/mysql/bin/mysqlbinlog ./mysql-bin.000004
/usr/local/mysql/bin/mysqlbinlog: unknown variable 'default-character-set=utf8'
原因： mysqlbinlog这个工具无法识别binlog中的配置中的default-character-set=utf8mb4这个指令，使用参数–no-defaults

问题2：bin-log日志文件不存在

1	/usr/local/mysql/bin/mysqlbinlog: File './mysql-bin.000004' not found (Errcode: 13)

原因：权限问题使用sudo

最终命令： sudo /usr/local/mysql/bin/mysqlbinlog –no-defaults /usr/local/mysql/data/mysql-bin.000004

Sam-Mac:data Sam$ sudo /usr/local/mysql/bin/mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200315 13:18:28 server id 1  end_log_pos 107 	Start: binlog v 4, server v 5.5.48-log created 200315 13:18:28 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
pLptXg8BAAAAZwAAAGsAAAABAAQANS41LjQ4LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAACkum1eEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#200315 13:21:09 server id 1  end_log_pos 331 	Query	thread_id=5	exec_time=0	error_code=0
use `db01`/*!*/;
SET TIMESTAMP=1584249669/*!*/;
SET @@session.pseudo_thread_id=5/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!*/;
# at 331
#200315 13:22:39 server id 1  end_log_pos 399 	Query	thread_id=59	exec_time=0	error_code=0
SET TIMESTAMP=1584249759/*!*/;
BEGIN
/*!*/;
# at 399
#200315 13:22:39 server id 1  end_log_pos 427 	Intvar
SET INSERT_ID=1/*!*/;
# at 427
#200315 13:22:39 server id 1  end_log_pos 563 	Query	thread_id=59	exec_time=0	error_code=0
SET TIMESTAMP=1584249759/*!*/;
insert into `db01`.`student` ( `id`, `student_name`) values ( '0', 'jim')
/*!*/;
# at 563
#200315 13:22:39 server id 1  end_log_pos 590 	Xid = 22
COMMIT/*!*/;
# at 590
#200315 13:25:56 server id 1  end_log_pos 842 	Query	thread_id=5	exec_time=0	error_code=0
use `db02`/*!*/;
SET TIMESTAMP=1584249956/*!*/;
CREATE TABLE `student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_name` varchar(32) NOT NULL,
  `birthday` date NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
/*!*/;
# at 842
#200315 16:36:37 server id 1  end_log_pos 910 	Query	thread_id=1201	exec_time=0	error_code=0
SET TIMESTAMP=1584261397/*!*/;
BEGIN
/*!*/;
# at 910
#200315 16:36:37 server id 1  end_log_pos 938 	Intvar
SET INSERT_ID=2/*!*/;
# at 938
#200315 16:36:37 server id 1  end_log_pos 1077 	Query	thread_id=1201	exec_time=0	error_code=0
use `db01`/*!*/;
SET TIMESTAMP=1584261397/*!*/;
insert into `db01`.`student` ( `id`, `student_name`) values ( '0', '刘洋')
/*!*/;
# at 1077
#200315 16:36:37 server id 1  end_log_pos 1104 	Xid = 128
COMMIT/*!*/;
# at 1104
#200315 16:37:09 server id 1  end_log_pos 1172 	Query	thread_id=1201	exec_time=0	error_code=0
SET TIMESTAMP=1584261429/*!*/;
BEGIN
/*!*/;
# at 1172
#200315 16:37:09 server id 1  end_log_pos 1200 	Intvar
SET INSERT_ID=3/*!*/;
# at 1200
#200315 16:37:09 server id 1  end_log_pos 1342 	Query	thread_id=1201	exec_time=0	error_code=0
SET TIMESTAMP=1584261429/*!*/;
insert into `db01`.`student` ( `id`, `student_name`) values ( '0', '张从容')
/*!*/;
# at 1342
#200315 16:37:09 server id 1  end_log_pos 1369 	Xid = 146
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
Sam-Mac:data Sam$

总结

无论是增量备份还是主从复制，都是需要开启mysql-binlog日志，最好跟数据目录设置到不同的磁盘分区，可以降低io等待，提升性能；
并且在磁盘故障的时候可以利用mysql-binlog恢复数据。

作者: Sam
发布时间: 2020-03-15 15:22:12
最后更新: 2020-12-18 22:02:34
文章链接: https://ydstudios.gitee.io/post/d1d9a9.html
版权声明: 本网所有文章除特别声明外, 禁止未经授权转载，违者依法追究相关法律责任!

Sam的个人博客

一个程序员的成长记录