说起MySQL binlog,并不陌生,经常听到DBA们提到,可以说是如雷贯耳。
那binlog到底是什么,有什么用途,以及如何使用,本文将围绕这个主题展开。
1.binlog是什么
与MySQL Server相关的日志主要有4种:
- Error log, 用来记录MySQL发生的错误
- General Query log,记录MySQL执行的每条SQL,非常详细,但对MySQL性能有影响,一般不会开启,除非排查问题。
- Slow Query log,即慢查询日志,用来记录执行时间超过一定阀值的SQL信息,这个经常用到。
- Binary log, 即二进制日志,用来记录所有的数据变更操作,例如insert,update,delete等。但并不记录像select 、show这些查询操作。
在binlog中,这些变更操作统称为事件(Event)。
对于支持事务的存储引擎来说,例如InnoDB,只有事务提交后才会记录binlog。至于binlog什么时候刷新到磁盘,这个跟参数sync_binlog
有关。
- 当
sync_binlog
是0时,会禁用MySQL server的刷盘操作,而是依赖操作系统的刷盘。这种设置,具有最好的性能,但是当机器突然宕机等异常情况时,已提交的事务可能没有记录到 binlog文件中。 - 当
sync_binlog
是1时,在事务提交前及时将binlog刷新到磁盘。这种设置是最安全的,但可能存在性能影响。在机器突然宕机情况下,binlog中丢失的事务处于Prepared状态,自动恢复机制可以回滚掉这些事务,从而保证binlog中没有事务丢失。 - 当
sync_binlog
是N时,这里N不是0,也不是1。当N个binlog commit group分组(一个事务算作一组)凑齐时,才会刷新到磁盘。这种设置对性能很友好,但是数据丢失的风险大大提高。
在使用InnoDB时,为了保证数据一致性,推荐的设置是
sync_binlog=1
innodb_flush_log_at_trx_commit=1
innodb_flush_log_at_trx_commit=1
表示每次事务提交都会刷新到磁盘。
2.binlog的作用
当数据写入数据库后,会将变更操作写入binlog文件。
binlog的作用主要有两个:
- 主从同步
在主从同步的过程中,Binlog用于记录主库的数据变更。然后这些记录被主库内的线程发送至从库。从库接收到变更事件后,在从库上重放,完成数据同步。主从同步是提升数据库吞吐的一种方法。 - 数据恢复
在使用mysqldump或Xtrabackup 进行备份时,只是对一段时间内的数据进行全备。例如,mysqldump每天定时全备,mysqldump -A -B -F >/opt/$(date +%F).sql
。 但是如果备份后,数据库突然故障,数据恢复就要依赖binlog了。
3.binlog的格式
binlog有三种格式:
- 基于Statement
出现最早,记录每条执行的sql语句。 - 基于Row
记录变更的数据行 - 基于Mixed
前两者的混合。
由于基于Row的格式,数据准确性高等特点,是目前用的最多的。
4.binlog日志结构
binlog日志文件包括两种:索引文件和具体日志文件。
索引文件用于跟踪日志文件,每行一个日志文件。
默认情况下,索引文件名为{Host名}-bin.index
。
日志文件是由一系列事件(Binary Log Events)组成。默认情况下,文件名为{Host名}-bin.NNNNNN
。 后缀六个数字,是编号,用于区分不同的日志文件。
日志文件的开头是 Format_description
事件,这个事件记录主库的信息和日志文件的状态。如果主库突然宕机或者重启,会重新创建一个日志文件,并在开头写入Format_description
。
当需要创建一个新日志文件时,会写入Rotate
事件。Rotate
事件会指定下一个日志文件的文件名和读取事件的起始点。
日志文件会把除了Format_description
事件和Rotate
事件之外的其他变更事件进行分组(Group)。在MySQL中,每一个事务会被分成一组,组中包含了这个事务下执行的所有语句。一些非事务性语句会被单独分成一组,如create和drop语句等。如下图所示。
每个binlog事件由四个部分组成:
- 通用Header:存放事件的基本信息:事件类型和事件数据大小。
- Post Header:存放特定事件类型的相关信息
- 事件实体:存储事件的数据,如执行过的语句和变更的实际数据
- Checksum:MySQL5.6新增的功能,用作检查数据是否损坏。
5.什么时候会新建binlog文件
当遇到以下3种情况时会重新生成一个新的日志文件,文件序号递增:
- MySQL重启后,会生成一个新的日志文件
- 使用
flush logs
命令 - 当binlog文件大小超过
max_binlog_size
上限时
6.binlog的开启和关闭
在MySQL配置文件my.cnf中增加log-bin
参数即可开启binlog:
[mysqld]
log-bin=mysql-bin
也可指定具体的文件路径:
log_bin = /var/mysql/logs/mysql-bin
修改配置文件后,需要重启MySQL服务。
通过命令行的方式开启和关闭binlog:
- 关闭binlog:
SET SQL_LOG_BIN=0
- 开启binlog:
SET SQL_LOG_BIN=1
7.binlog的删除
(1)过期删除
mysql> show variables like 'expire_log_days';
mysql> set global expire_log_days=7;
(2)reset master 会删除所有binlog
(3) reset slave 会删除所有relay log
8.如何查看 binlog
查看日志文件
> show master logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 16899 |
+------------------+-----------+
1 row in set (0.00 sec)
或者
>show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 16899 |
+------------------+-----------+
1 row in set (0.01 sec)
查看日志文件位置
show master status;
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
| mysql-bin.000001 | 16899 | | | 10035945-aaaa-11e9-89f3-0022ac533a99:1-67|
+------------------+----------+--------------+------------------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
查看日志文件内容
binlog日志文件不是文本文件,不能直接通过vim,cat等查看。
需要使用mysql命令或者mysqlbinlog命令。
>show binlog events in 'mysql-bin.000001';
+------------------+-------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-------+----------------+-----------+-------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 20201066 | 120 | Server ver: 5.6.36-82.1-log, Binlog ver: 4 |
| mysql-bin.000001 | 120 | Previous_gtids | 20201066 | 151 | |
| mysql-bin.000001 | 151 | Gtid | 20201066 | 199 | SET @@SESSION.GTID_NEXT= '10035966-ddeb-11e9-89f3-0022ac533a2f:1' |
| mysql-bin.000001 | 199 | Query | 20201066 | 319 | create database if not exists app_test |
| mysql-bin.000001 | 319 | Gtid | 20201066 | 367 | SET @@SESSION.GTID_NEXT= '10035966-ddeb-11e9-89f3-0022ac533a2f:2' |
| mysql-bin.000001 | 367 | Query | 20201066 | 488 | create table if not exists app_test.abc (a int) |
| mysql-bin.000001 | 488 | Gtid | 20201066 | 536 | SET @@SESSION.GTID_NEXT= '10035966-ddeb-11e9-89f3-0022ac533a2f:3' |
| mysql-bin.000001 | 536 | Query | 20201066 | 776 | GRANT CREATE, SHUTDOWN, SUPER, LOCK TABLES, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD, PROCESS, TRIGGER ON *.* TO 'orange_manager'@'10.10.116.88' |
| mysql-bin.000001 | 776 | Gtid | 20201066 | 824 | SET @@SESSION.GTID_NEXT= '10035966-ddeb-11e9-89f3-0022ac533a2f:4' |
| mysql-bin.000001 | 824 | Query | 20201066 | 993 | GRANT SELECT ON `mysql`.`slave_master_info` TO 'orange_manager'@'10.10.116.88' |
| mysql-bin.000001 | 993 | Gtid | 20201066 | 1041 | SET @@SESSION.GTID_NEXT= '10035966-ddeb-11e9-89f3-0022ac533a2f:5' |
| mysql-bin.000001 | 1041 | Query | 20201066 | 1213 | GRANT SELECT ON `mysql`.`slave_relay_log_info` TO 'orange_manager'@'10.10.116.88' |
或者使用mysqlbinlog命令,在shell终端输入:
mysqlbinlog /var/logs/mysql-bin.000001
类似如下内容:
/*!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
#161020 11:07:29 server id 2 end_log_pos 107 Start: binlog v 4, server v 5.5.51-log created 161020 11:07:29
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
8TQIWA8CAAAAZwAAAGsAAAABAAQANS41LjUxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#161020 11:08:50 server id 2 end_log_pos 181 Query thread_id=162 exec_time=1 error_code=0
SET TIMESTAMP=1476932930/*!*/;
SET @@session.pseudo_thread_id=162/*!*/;
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=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
提取指定的binlog日志
例如,insert相关的:
# mysqlbinlog /opt/logs/mysql-bin.000001 | grep insert
或者提取指定位置的binlog日志
# mysqlbinlog --start-position="109" --stop-position="320" /opt/logs/mysql-bin.000001
提取指定位置的binlog日志并输出到压缩文件
# mysqlbinlog --start-position="109" --stop-position="320" /opt/logs/mysql-bin.000001 |gzip >tmp.sql.gz
提取指定位置的binlog日志导入数据库
# mysqlbinlog --start-position="108" --stop-position="226" /opt/logs/mysql-bin.000001 | mysql -uroot -p
提取指定开始时间的binlog并输出到日志文件
# mysqlbinlog --start-datetime="2020-07-02 22:16:23" /opt/log/mysql-bin.000002 --result-file=tmp02.sql
提取多个binlog日志文件的指定位置的日志
# mysqlbinlog --start-position="108" --stop-position="226" /opt/logs/mysql-bin.000001 /opt/logs/mysql-bin.000002|more
提取指定数据库binlog并转换字符集到UTF8
# mysqlbinlog --database=test --set-charset=utf8 /opt/logs/mysql-bin.000001 /opt/logs/mysql-bin.000002 >test.sql
远程提取日志,指定结束时间
# mysqlbinlog -uroot -p -h192.168.1.186 -P3306 --stop-datetime="2020-06-23 20:30:23" --read-from-remote-server mysql-bin.000033 |more