文章目录
一.二进制日志格式(Binary Logging Formats)
1.1 binlog介绍
MySQL binlog 不仅仅记录了数据库发生更改的event，例如表创建或者变更表数据的操作；还记录了sql语句执行花费时间；其中2个核心目的在于便于目标数据库复制mysql数据库数据和进行数据恢复
MySQL binlog不记录SELECT或 SHOW语句 ，要记录所有语句，使用The General Query Log；因为只记录完整的事务，所以binlog属于事务安全型，crash-safe；
binlog包含两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。
注意：启用MySQL binlog会使稍微降低性能，但是从进行复制、恢复操作上看 利大于弊
1.2 启用binlog
修改配置文件 my.cnf,添加配置
server_id=6
log-bin=mysql-bin
log-bin-index=mysql-bin.index
注意：
（1）
binlog配置说明书指明在my.cnf配置文件中，只设置了全局系统变量log_bin，没有设置全局系统变量server_id的话，MySQL启动不了，server-id 必须唯一；
（2）
log-bin 是指生成的 Binlog 文件的前缀，文件添加数字后缀按顺序创建，如上，那么文件就将会是mysql-bin.000001、mysql-bin.000002 等。二进制日志文件的默认所在目录是数据目录
log-bin-index 则指 binlog index 文件的名称，可以不配置。
重启mysql服务
1.3 binlog几十种配置说明
启动选项：
1.
2.
3.
4.
指定binlog日志记录的db（注意Statement-based logging以及Row-based logging的不同）
5.
6.
为事件生成校验和
测试选项，不适用于正常操作：
系统变量:
1.
设置缓存大小，经常使用大型事务，则增加此缓存大小获得更好的性能
2.
3.
4.
5.
6.
待续
1.4 清理binlog日志
（1）清理所有的binlog日志
RESET reset_option [, reset_option] ...
reset_option: {
MASTER
| QUERY CACHE
| SLAVE
}
（2）
删除指定日志文件名或日期之前的日志索引文件中列出的所有二进制日志文件
PURGE { BINARY | MASTER } LOGS {
TO 'log_name'
| BEFORE datetime_expr
}
1.5 sql命令查看配置
（1）查看binlog位置信息
mysql> show variables like '%log_bin%';
+---------------------------------+--------------------------------+
| Variable_name | Value |
+---------------------------------+--------------------------------+
| log_bin | ON |
| log_bin_basename | /var/lib/mysql/mysql-bin |
| log_bin_index | /var/lib/mysql/mysql-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
| sql_log_bin | ON |
+---------------------------------+--------------------------------+
（2）查看binlog文件列表
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 177 |
| mysql-bin.000003 | 177 |
| mysql-bin.000004 | 154 |
+------------------+-----------+
(3)查看日志状态
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000004 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
(4)刷新日志
mysql> flush logs;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 177 |
| mysql-bin.000002 | 177 |
| mysql-bin.000003 | 177 |
| mysql-bin.000004 | 201 |
| mysql-bin.000005 | 154 |
+------------------+-----------+
5 rows in set (0.00 sec)
自此刻开始产生一个新编号的binlog日志文件
每当mysqld服务重启时，会自动执行此命令，刷新binlog日志；在mysqldump备份数据时加 -F 选项也会刷新binlog日志；
(5)show binlog events查看binlog日志
mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000002 | 4 | Format_desc | 6 | 123 | Server ver: 5.7.26-log, Binlog ver: 4 |
| mysql-bin.000002 | 123 | Previous_gtids | 6 | 154 | |
| mysql-bin.000002 | 154 | Anonymous_Gtid | 6 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mysql-bin.000002 | 219 | Query | 6 | 289 | BEGIN |
| mysql-bin.000002 | 289 | Table_map | 6 | 337 | table_id: 219 (di.user) |
| mysql-bin.000002 | 337 | Write_rows | 6 | 380 | table_id: 219 flags: STMT_END_F |
| mysql-bin.000002 | 380 | Table_map | 6 | 428 | table_id: 219 (di.user) |
| mysql-bin.000002 | 428 | Write_rows | 6 | 473 | table_id: 219 flags: STMT_END_F |
| mysql-bin.000002 | 473 | Table_map | 6 | 521 | table_id: 219 (di.user) |
| mysql-bin.000002 | 521 | Write_rows | 6 | 567 | table_id: 219 flags: STMT_END_F |
| mysql-bin.000002 | 567 | Xid | 6 | 598 | COMMIT /* xid=361 */ |
| mysql-bin.000002 | 598 | Rotate | 6 | 645 | mysql-bin.000003;pos=4 |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
12 rows in set (0.00 sec)
(6）清空日志
mysql> reset master;
Query OK, 0 rows affected (0.00 sec)
mysql> show binary logs;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154 |
+------------------+-----------+
1 row in set (0.01 sec)
二. mysqlbinlog使用
2.1 查看binlog文件
mysqlbinlog是mysql官方提供的一个binlog查看工具，其配置参数mysqlbinlog — Utility for Processing Binary Log Files
基础语法：
mysqlbinlog [options] log_file ...
[root@di bin]# mysqlbinlog /var/lib/mysql/mysql-bin.000004
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#211201 14:44:26 server id 6 end_log_pos 123 CRC32 0xf66d4142 Start: binlog v 4, server v 5.7.26-log created 211201 14:44:26
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
yhmnYQ8GAAAAdwAAAHsAAAABAAQANS43LjI2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AUJBbfY=
'/*!*/;
# at 123
#211201 14:44:26 server id 6 end_log_pos 154 CRC32 0xa4b6ecf8 Previous-GTIDs
# [empty]
# at 154
#211201 14:45:03 server id 6 end_log_pos 219 CRC32 0x626744dd Anonymous_GTID last_committed=0 sequence_number=1 rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#211201 14:45:03 server id 6 end_log_pos 441 CRC32 0x993ad376 Query thread_id=11 exec_time=0 error_code=0
use `di`/*!*/;
SET TIMESTAMP=1638341103/*!*/;
SET @@session.pseudo_thread_id=11/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=45,@@session.collation_connection=45,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/* ApplicationName=IntelliJ IDEA 2019.1.4 */ CREATE TABLE t
(
id INT NOT NULL,
name VARCHAR(20) NOT NULL,
date DATE NULL
) ENGINE = InnoDB
/*!*/;
# at 441
#211201 14:45:03 server id 6 end_log_pos 506 CRC32 0x6c88337c Anonymous_GTID last_committed=1 sequence_number=2 rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 506
#211201 14:45:03 server id 6 end_log_pos 576 CRC32 0x3bfddcda Query thread_id=11 exec_time=0 error_code=0
SET TIMESTAMP=1638341103/*!*/;
BEGIN
/*!*/;
# at 576
#211201 14:45:03 server id 6 end_log_pos 622 CRC32 0x6748964e Table_map: `di`.`t` mapped to number 220
# at 622
#211201 14:45:03 server id 6 end_log_pos 668 CRC32 0x431da35c Write_rows: table id 220 flags: STMT_END_F
BINLOG '
7xmnYRMGAAAALgAAAG4CAAAAANwAAAAAAAEAAmRpAAF0AAMDDwoCFAAETpZIZw==
7xmnYR4GAAAALgAAAJwCAAAAANwAAAAAAAEAAgAD//wBAAAABWFwcGxlXKMdQw==
'/*!*/;
# at 668
#211201 14:45:03 server id 6 end_log_pos 714 CRC32 0x0ba4957a Table_map: `di`.`t` mapped to number 220
# at 714
#211201 14:45:03 server id 6 end_log_pos 774 CRC32 0x6d707394 Update_rows: table id 220 flags: STMT_END_F
BINLOG '
7xmnYRMGAAAALgAAAMoCAAAAANwAAAAAAAEAAmRpAAF0AAMDDwoCFAAEepWkCw==
7xmnYR8GAAAAPAAAAAYDAAAAANwAAAAAAAEAAgAD///8AQAAAAVhcHBsZfgBAAAABHBlYXIhsg+U
c3Bt
'/*!*/;
# at 774
#211201 14:45:03 server id 6 end_log_pos 820 CRC32 0xfe57af6b Table_map: `di`.`t` mapped to number 220
# at 820
#211201 14:45:03 server id 6 end_log_pos 868 CRC32 0x498fb5bd Delete_rows: table id 220 flags: STMT_END_F
BINLOG '
7xmnYRMGAAAALgAAADQDAAAAANwAAAAAAAEAAmRpAAF0AAMDDwoCFAAEa69X/g==
7xmnYSAGAAAAMAAAAGQDAAAAANwAAAAAAAEAAgAD//gBAAAABHBlYXIhsg+9tY9J
'/*!*/;
# at 868
#211201 14:45:03 server id 6 end_log_pos 899 CRC32 0xb798db89 Xid = 505
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
2.2 mysqldump + mysqlbinlog用于数据备份和恢复
Using mysqlbinlog to Back Up Binary Log Files
三. binlog的3种格式
注意：row-based logging使用主键确保有效识别行
查询数据库使用的格式
mysql> show variables like'%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW |
+---------------+-------+
1 row in set (0.01 sec)
修改binlog格式
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
四. 代码实现对binlog的处理（仅提供思路）
引入maven依赖
<dependency>
<groupId>com.github.shyiko</groupId>
<artifactId>mysql-binlog-connector-java</artifactId>
<version>0.13.0</version>
</dependency>
核心代码
（1）创建客户端
（2）实现接口EventListener，重写onEvent(Event event) 方法
（3）注册监听对象
（3）连接
注意
1922
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
