我们在使用和维护MySQL时,一定经常听到binlog这个概念。binlog在主从复制,数据恢复等场景都有着重要作用。本篇文章主要介绍binlog的概念,功能及常用操作,旨在帮助大家对binlog有更深的了解。
binlog是一个二进制格式的文件,记录所有对数据库表结构产生变更的操作(insert,update,delete,create,alter...)。binlog文件存放在/var/log/mysql/
目录下,包括两类文件,索引文件mysql-bin.index和日志文件mysql-bin.00000x。索引文件记录所有的binlog日志文件,日志文件则记录SQL语句。
数据恢复:可用于回档或故障迁移恢复。比如腾讯云MySQL实例的回档功能基于冷备数据+binlog实现,可以将云数据库或表回档到指定时间。
主从复制:MySQL通过主从复制保持所有主从节点的数据一致性,这个过程依赖于binlog的传输回放等。具体流程在下面有详细介绍。
数据审计:通过binlog记录的SQL语句,可用来审计追踪用户操作。
max_binlog_size
指定单个binlog文件的最大值。如果超过该值,就会创建一个新的binlog文件,后缀名+1,并记录到index索引文件中。腾讯云mysql实例默认值为256MB,开源mysql从5.0起默认值为1GB. 要注意的是,这个参数并不能严格控制Binlog的大小,如果一个binlog文件容量快满了,但正好遇到一个大事务写入。为保证事务的完整性,不能直接切换到新日志,只能将该事务的所有SQL都记录进当前binlog,直到事务结束。此时binlog文件大小就会超过设置最大值。
binlog_do_db
binlog_ingore_db
这两个参数表示需要写入或需要忽略写入哪些库的日志到binlog中。默认都为空,表示需要将所有库的日志都同步到binlog.
binlog_format
指定binlog记录模式,有statement,row,mixed三种。详细介绍见文章下一部分“binlog记录模式”。
binlog_cache_size
当使用支持事务的存储引擎(例如InnoDB引擎),所有未提交(uncommitted)的binlog首先会记录到一个缓存中,等该事务提交(committed)时,再将缓存中的数据写入binlog文件里。这个缓冲就是binlog_cache,binlog_cache_size则决定该缓冲的大小,默认是32KB. 此外,binlog_cache_size 参数是基于会话的,当线程开始一个事务时,MySQL会按照这个参数大小自动分配缓存,用于记录binlog。
sync_binlog
上面我们提到,事务提交时,Mysql将binlog_cache中的数据写入binlog文件,但不是每次写都会及时同步到磁盘。如果数据库异常宕机,可能会有最后一部分binlog没能写入磁盘,导致丢失。sync_binlog 参数决定了每写缓冲多少次就同步到磁盘。
sync_binlog=0
表示当事务提交后,不执行同步写磁盘指令,而是让文件系统自行决定什么时候来做同步。该参数默认值为0,这种方案性能最佳,但风险也最大。一旦数据库宕机,在文件系统缓存中的所有binlog数据都会丢失;
sync_binlog=1
表示每次事务提交, 均执行一次同步写磁盘指令,将binlog文件同步刷新到磁盘。这种方案风险最低,数据库宕机时,系统最多只会丢失1个事务的数据。高安全性的代价就是牺牲了性能,写binlog虽然是顺序IO,但是设置sync_binlog=1
,多个事务同时提交,还是对MySQL和IO性能有很大影响。所以通常sync_binlog
会设置为100或者0,通过牺牲一定的一致性,可以获得更高的并发和性能;
sync_binlog=n
表示在进行n次事务提交之后,执行一次同步写磁盘指令,将binlog文件同步刷新到磁盘。
在MySQL5.1版本之前,binlog的记录格式都是基于SQL语句的statement格式。5.1版本后开始引入binlog_format
参数,该参数可设的值也增加到了3种:statement,row,mixed.
statement:基于 SQL 语句的复制。
优点:binlog日志量占用小,节约了IO,提高性能。
缺点:有些SQL语句对于复制有一定要求,例如rand,uuid,now等函数,需要同时记录这些语句在执行的时候的某些相关信息,才能保证在主从节点上执行得到相同结果。这种情况下,只复制SQL语句可能导致数据不一致甚至出错。另外,statement格式下不支持将InnoDB的事务隔离级别设置为READ COMMITTED,有可能造成主从数据不一致。
row:基于行的复制,记录每行实际数据的变更。
优点:复制过程准确,不会出现某些特定情况下的存储过程,函数,以及触发器的调用和触发无法被正确复制的问题。且mysql5.1版本开始,row格式下可以将InnoDB的事务隔离级别设置为READ COMMITTED,从而获得更好的并发性。
缺点:大量增加binlog的磁盘占用,在一些大表中清除大量数据时在 binlog 中会生成很多条语句,可能导致从库延迟变大。
mixed:在该模式下,MySQL会根据执行的每一条具体的sql语句来灵活选择记录格式。一般的语句修改使用statment格式来提高性能,在遇到一些statement无法完成主从复制的操作时,则采用row格式保存binlog。
优点:综合其他两种模式的特点,准确性强,文件大小适中。
缺点:可能导致主从不一致。
为保证高可用(High Availability),目前MySQL多采用主从(master-slave)架构,一个主节点加若干从节点。主从架构不仅可以进行读写分离,还可以在主节点宕机时快速拉起一个从节点充当主节点,恢复高可用。MySQL通过主从复制保证数据在所有节点中的一致性。
MySQL主从复制涉及到三个线程,一个运行在主节点(log dump thread),其余两个(I/O thread, SQL thread)运行在从节点。
首先,当从节点连接主节点时,主节点会为每一个从节点创建一个log dump 线程,用于发送binlog. 每个从节点会创建一个I/O线程用来请求接收主节点上的binlog。成功接收到的更新会被I/O线程保存在本地的relay log中。从节点上的SQL线程负责读取relay log中的内容,解析成具体的操作在本地执行,完成整个复制过程。过程的原理图如下图所示。
MySQL主从复制有3种模式
1. 异步复制
异步复制如下图所示,这种模式下主节点将所有更新写入binlog文件之后就返回客户端结果,而不会主动推送binlog到从节点。一旦主节点宕机,从节点会因为没有同步到最新binlog导致数据丢失。相对地,该模式处理效率更高,响应速度快。mysql 主从复制默认是异步模式。
2. 半同步复制
半同步复制如下图所示,这种模式下主节点在本地写入binlog后,会主动推送binlog到从节点。在接收到其中一台从节点的返回信息后,再返回给客户端,否则需要等待直到超时时间然后切换成异步模式再提交。相比异步复制,提高了数据安全性,也降低了主从延迟。但性能上会有一定的降低,响应时间会变长。
半同步模式不是MySQL内置的,从MySQL 5.5开始集成,需要主从节点安装插件开启半同步模式。
3. 全同步复制
全同步复制是指主节点和全部从节点都执行了commit并确认才会向客户端返回成功。在牺牲了效率的前提下,保证了数据的强一致性。
介绍常见的操作命令,以下命令均在腾讯云MySQL实例上执行。
1. 查看是否开启binlog,云实例默认是开启状态,开源MySQL默认是关闭状态。
show variables like 'log_bin';
2. 查看、修改当前使用的binlog_format.
show variables like 'binlog_format';
修改全局binlog_format时需要谨慎操作,确保更改后不会对当前复制带来影响。为保证安全,腾讯云MySQL实例禁止修改该参数,默认为row.
set globle binlog_format='MIXED';
4.查看当前主库正在写入的binlog文件位置
show master status\G;
5. 查看指定binlog文件的内容
show binlog events in 'mysql-bin.00000x';
6. 获取binlog文件列表
show binary logs;
7. 查看单个binlog文件的最大值
show variables like '%max_binlog%';
8. 手动刷新binlog文件,生成新的binlog文件。注意,服务器在重启时,也会调用flush logs操作。所以重启服务器也会自动生成新的binlog文件。
flush logs;
9. 删除所有binlog,从001文件开始重新记录(谨慎操作!)
reset master;
参考文档:
[1] 姜成尧 . MySQL技术内幕:InnoDB存储引擎[M].北京:机械工业出版社,2010.11.
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。
原创声明:本文系作者授权腾讯云开发者社区发表,未经许可,不得转载。
如有侵权,请联系 cloudcommunity@tencent.com 删除。