共计 7370 个字符，预计需要花费 19 分钟才能阅读完成。
本篇内容主要讲解“mysql binlog 相关知识点有哪些”，感兴趣的朋友不妨来看看。本文介绍的方法操作简单快捷，实用性强。下面就让丸趣 TV 小编来带大家学习“mysql binlog 相关知识点有哪些”吧!
1.
– 逻辑日志, 记录的是数据库内部的所有变动（sql 语句 行的改变）
– server 层日志，binlog 不仅仅记录 innodb 的变动，也记录 myisam 存储引擎的变动。
– innodb redo 是存储引擎层，和 binlog 不是一层，redo 只记录 innodb 的变化。
– binlog 不记录 select show 等查询语句, 记录 dml ddl 等数据库变动的语句.
2.binlog 的作用
– 可以查看 mysql 变更
– mysql 的复制架构（主从）
– mysql 备份恢复（可以通过 binlog 进行数据的补齐或者回滚）
3.binlog 的位置
log_bin = /home/mysql3306/mysql3306/mysql-bin
#binlog 路径 +binlog 前缀名 如：mysql-bin.000001
4. 相关参数简介
(1).log-bin
决定了 msyql 的 binlog 的名字，生成的 binlog 名字为 mysql-bin.000001
(2).binlog_format
规定 binlog 的格式，binlog 有三种格式 statement，row 以及 mixed，默认使用默认使用 statement，建议使用 row 格式
(3).expire_logs_days
过期时间 建议大家根据业务设置 7D-30D
(4).binlog_do_db
此参数表示只记录指定数据库的二进制日志
(5).binlog_ignore_db
此参数表示不记录指定的数据库的二进制日志
(6).sync_binlog(binlog 落盘策略)
0— 每一秒刷一次磁盘
1— 每次事务提交都刷一次磁盘
n—（100 200 500）每 n 次提交落盘一次
innodb_flush_log_at_trx_commit =1
sync_binlog=1
双 1 配置，数据库的安全性是最高的，不会丢事务。
(7).binlog_checksum 为 {CRC32|NONE}
写 binlog 时，会将内容生成校验位，之后存储在 binlog 中。
默认情况下，服务器记录事件的长度以及事件本身，并使用它来验证事件是否正确写入。也可以通过设置 binlog_checksum 系统变量来使服务器为事件写入校验和。
(8).max_binlog_size
binlog 文件的最大值，默认和最大是 1GB，并不能严格限定二进制文件的大小
(9).max_binlog_cache_size
表示的是 binlog 能够使用的最大 cache 内存大小
当我们执行多语句事务的时候 所有 session 的使用的内存超过 max_binlog_cache_size 的值时
就会报错：“Multi-statement transaction required more than max_binlog_cache_size bytes ofstorage”
(10).binlog_cache_size = 4M
线程级参数，不能设置太大
5.binlog 格式
(1).statement
mysql show variables like %binlog_format%
+—————+———–+
| Variable_name | Value |
+—————+———–+
| binlog_format | STATEMENT |
+—————+———–+
执行语句:
insert into wwj.t1 values(2, mxt2
insert into wwj.t1 values(3, mxt3
insert into wwj.t1 values(4, mxt4
查看 binlog
点击 (此处) 折叠或打开
mysql show binlog events;
+——————+—–+—————-+———–+————-+—————————————+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+——————+—–+—————-+———–+————-+—————————————+
| mysql-bin.000001 | 4 | Format_desc | 12023307 | 123 | Server ver: 5.7.21-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 12023307 | 154 | |
| mysql-bin.000001 | 154 | Anonymous_Gtid | 12023307 | 219 | SET @@SESSION.GTID_NEXT= ANONYMOUS |
| mysql-bin.000001 | 219 | Query | 12023307 | 293 | BEGIN |
| mysql-bin.000001 | 293 | Query | 12023307 | 398 | insert into wwj.t1 values(13, mxt3) |
| mysql-bin.000001 | 398 | Xid | 12023307 | 429 | COMMIT /* xid=275 */ |
| mysql-bin.000001 | 429 | Anonymous_Gtid | 12023307 | 494 | SET @@SESSION.GTID_NEXT= ANONYMOUS |
| mysql-bin.000001 | 494 | Query | 12023307 | 568 | BEGIN |
| mysql-bin.000001 | 568 | Query | 12023307 | 673 | insert into wwj.t1 values(14, mxt4) |
| mysql-bin.000001 | 673 | Xid | 12023307 | 704 | COMMIT /* xid=276 */ |
+——————+—–+—————-+———–+————-+—————————————+
Log_name：binlog 文件名
Pos：binlog 的 position 点，写入 binlog 的日志的字节数，就是 pos 的大小，在每次新生成 binlog 的时候，pos 点会重置，但是在一个 binlog 内部，这个 pos 是单调递增的。
xid：分布式事务 ID，Innodb 支持分布式事务，Innodb 支持的单实例内部的，server 层和存储引擎层的分布式事务，还有多节点分布式事务（ndb）
Server_id：实例 server_id
解析 binlog 工具
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 /tmp/all.bin
点击 (此处) 折叠或打开
# at 293
#180325 4:14:20 server id 12023307 end_log_pos 398 CRC32 0xb9d2f949 Query thread_id=14 exec_time=0 error_code=0
SET TIMESTAMP=1521922460/*!*/;
insert into wwj.t1 values(13, mxt3)
/*!*/;
# at 398
查看当前的 binlog pos 点的位置
mysql show master status;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect…
Connection id: 15
Current database: *** NONE ***
+——————+———-+————–+——————+——————-+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+——————+———-+————–+——————+——————-+
| mysql-bin.000001 | 704 | | | |
+——————+———-+————–+——————+——————-+
应用 binlog
/usr/local/mysql/bin/mysqlbinlog mysql-bin.000001 /tmp/all.bin
mysql -uroot -proot -S /tmp/mysql3306.sock /tmp/all.bin
mysql source /tmp/all.bin
应用 binlog 需要注意的事项
如果应用多个 binlog
错误做法
mysqlbinlog mysql-bin.000001 | mysql -S -u -p
mysqlbinlog mysql-bin.000002 | mysql -S -u -p
正确做法
mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -S -u -p
或者
mysqlbinlog mysql-bin.000001 mysql-bin.000002 /tmp/all.bin
mysql -S -u -p /tmp/all.bin
(2).row
mysql show variables like binlog_format
+—————+——-+
| Variable_name | Value |
+—————+——-+
| binlog_format | ROW |
+—————+——-+
row 格式 binlog：row 格式 binlog 记录的是 mysql 的行的改变前后的数据
比如在 stament 格式中，update 数据，记录的是 update 语句
而且 row 格式中，记录的行的改变的前后，如果 update1000 数据，记录一千行的数据改变
mysqlbinlog -v mysql-bin.000001 /tmp/all.bin 增加伪 sql
mysqlbinlog -vv mysql-bin.000001 /tmp/all.bin 增加数据类型
点击 (此处) 折叠或打开
# at 334
#180325 5:51:04 server id 12023307 end_log_pos 458 CRC32 0x1be59376 Delete_rows: table id 108 flags: STMT_END_F
BINLOG
SMi2WhMLdrcALwAAAE4BAAAAAGwAAAAAAAEAA3d3agACdDEAAgMPArQAAsKvIYM=
SMi2WiALdrcAfAAAAMoBAAAAAGwAAAAAAAEAAgAC//wBAAAAA3d3avwCAAAABG14dDL8AwAAAARt
eHQz/AQAAAAEbXh0NPwFAAAABG14dDL8BgAAAARteHQz/AcAAAAEbXh0NPwNAAAABG14dDP8DgAA
AARteHQ0dpPlGw==
/*!*/;
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=1 /* INT meta=0 nullable=0 is_null=0 */
### @2= wwj /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=2 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt2 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=3 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt3 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=4 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt4 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=5 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt2 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=6 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt3 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=7 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt4 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=13 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt3 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
### DELETE FROM `wwj`.`t1`
### WHERE
### @1=14 /* INT meta=0 nullable=0 is_null=0 */
### @2= mxt4 /* VARSTRING(180) meta=180 nullable=1 is_null=0 */
# at 458
其他解析方法
解析不带行数据
/usr/local/mysql/bin/mysqlbinlog –base64-output=DECODE-ROWS -vv mysql-bin.000001
如果 binlog 解析出来需要恢复到数据库，那么需要注意不要加 –base64-output=DECODE-ROWS。
/usr/local/mysql/bin/mysqlbinlog
start-datetime 开始时间
stop-datetime 停止时间
/usr/local/mysql/bin/mysqlbinlog –start-datetime= 2017-04-20 9:00:00 –stop-datetime= 2017-07-20 18:00:00 /home/mysql3306/mysql3306/mysql-bin.000001 /tmp/mysql_binlog.sql
start-position 开始时间的 position
stop-position 停止时间的 position
row 格式优点：
- 记录行改变，而不是 SQL，能最大限度保证数据安全性
-row 格式可以用于闪回（恢复数据）
在线上的环境中，推荐使用 row 格式的 binlog
-row 格式 update delete 会对磁盘 网络造成一个比较大的压力
(3).mixed
一般情况下使用 statment 格式，在遇到特殊情况（造成主从不一致）改成使用 row 格式记录。
mixed 格式在旧版本 bug 比较多
不推荐使用
6. 清理 binlog
reset master; // 删除 master 的 binlog，线上不要使用
purge master logs before 2012-03-30 17:20:00 // 删除指定日期以前的日志索引中 binlog 日志文件
purge master logs to binlog.000002 // 删除指定日志文件的日志索引中 binlog 日志文件
如果想直接删除 binlog
rm binlog.000002
(不要删除当前正在使用的 binlog，也就是编号最大的 binlog)
[root@namenode mysql3306]# cat mysql-bin.index 不会自动更新
/home/mysql3306/mysql3306/mysql-bin.000001
/home/mysql3306/mysql3306/mysql-bin.000002
主从复制如果删掉了，容易从库报错（保证从库不再同步该 binlog 的数据）
7.binlog 和 redo 的区别
– redo 物理逻辑日志 物理：数据页 逻辑：数据页的改变
binlog 是逻辑日志 记录的 SQL 或者是行改变
– redo 是 innodb 存储引擎层
binlog 是 server 层
– redo 时时刻刻都在写入文件
binlog 是 提交事务的写入（具体写入策略跟具体参数有关）
到此，相信大家对“mysql binlog 相关知识点有哪些”有了更深的了解，不妨来实际操作一番吧！这里是丸趣 TV 网站，更多相关内容可以进入相关频道进行查询，关注我们，继续学习！
