主要记录数据库变化(DDL,DML,DCL)性质的日志
- 用于数据恢复：如果你的数据库出问题了，而你之前有过备份，那么可以看日志文件，找出是哪个命令导致你的数据库出问题了，想办法挽回损失。
- 主从服务器之间同步数据：主服务器上所有的操作都在记录日志中，从服务器可以根据该日志来进行，以确保两个同步。
MySQL 8.0 版本以前默认都没有开启，生产环境建议开启
配置方法
1
2
3
4
5
6
7
8
9
10
11
| [mysqld]
# 主机编号，主从中使用，5.7 版本中 开启 binlog 必须设置 server_id
server_id = 1
# binlog 日志名前缀，『/data/mysql/binlog』 binlog 存储目录， 『mysql-bin』 binlog 文件名前缀，便如 mysql-bin.000001 mysql-bin.000002
log_bin = /data/mysql/binlog/mysql-bin
# binlog 日志写入磁盘策略，双1参数中的其中一个
sync_binlog = 1
# binlog 日志记录格式为 row
binlog_format = row
# binlog 保存天数
expire_logs_days = 7
|
注意: binlog 日志最好和数据分开存储
binlog 是 SQL 层的功能。记录的是变更 SQL 语句，不记录查询语句。
- DDL：原封不动记录当前 DDL（statement 语句方式）
- DML：原封不动记录当前 DDL（statement 语句方式）
- DCL：只记录已提交事务的 DML (insert, update, delete)
- statement (5.6 默认) SBR (statement based replication): 原封不动的记录当前DML
- ROW (5.7 默认) RBR (ROW based replication): 记录数据行的变化，用户看不懂（需要工具分析）
- mixed (混合) MBR (mixed based replication): 以上两模式的混合
- STATEMENT 模式 可读性较高，日志量小，不够严谨
- ROW 模式 可读性很低，日志量大，足够严谨
建议使用 ROW 模式
二进制日志的最小单元，对于 DML,DCL，一条语句就是一个事件(event)
对于 DML 语句来讲，只记录已提交的事务
例如: 以下列子，分为4个事件
1
2
3
4
| begin; 101-320
DML; 320-630
DML; 630-740
commit; 740-810
|
三部分构成
- 事件的开始标识
- 事件的内容
- 事件的结束标识
Position
- 开始标记: at 1262
- 结束标记: end_log_pos 1312
作用: 为了方便截取日志
查看一共有几个日志文件
1
2
3
4
5
6
7
| mysql> show binary logs;
+-----------------+-----------+
| Log_name | File_size |
+-----------------+-----------+
| mybinlog.000001 | 177 |
| mybinlog.000002 | 1403 |
+-----------------+-----------+
|
查看当前在用的日志文件
1
2
3
4
5
6
| mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mybinlog.000002 | 1403 | | | |
+-----------------+----------+--------------+------------------+-------------------+
|
查看二进日志事件
1
2
3
4
5
6
7
8
9
| mysql> show binlog events in 'mybinlog.000002';
+-----------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+-----------------+------+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------+
| mybinlog.000002 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.28-log, Binlog ver: 4 |
| mybinlog.000002 | 123 | Previous_gtids | 1 | 154 | |
| mybinlog.000002 | 154 | Anonymous_Gtid | 1 | 219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS' |
| mybinlog.000002 | 219 | Query | 1 | 407 | CREATE USER 'repl'@'10.10.1.%' IDENTIFIED WITH 'mysql_native_password' AS '*6BB4837EB74329105EE4568DDA7DC67ED2CA2AD9' |
| mybinlog.000002 | 407 | Anonymous_Gtid | 1 | 472 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'
|
导出查看二进制日志
1
2
3
| root@db1:~# mysqlbinlog /data/mysql/3306/mybinlog.000002 > /tmp/2.sql
# 或者， --base64-outpu=decode-rows 解码日志信息
root@db1:~# mysqlbinlog --base64-outpu=decode-rows /data/mysql/3306/mybinlog.000002 > /tmp/2.sql
|
通过 position 号截取日志
1
| root@db1:~# mysqlbinlog --start-position=219 --stop-position=1403 /data/mysql/3306/mybinlog.000002 > /tmp/r.sql
|
- 每次重启 MySQL 时 BINLOG 日志会自动滚动生成并使用新的日志文件
- bin-log 文件大小达到参数 max_binlog_size 限制；
- 手动滚动更新
1
2
| mysql> flush logs;
root@db1:~# mysqladmin flush-logs
|
mysqldump 的 -F 参数也会触发自动滚动更新 BINLOG 日志文件，不建议使用
1
2
3
4
5
6
7
8
| root@db1:~# vim /etc/my.cnf
[mysqld]
expire_logs_days = 7 // 表示日志保留7天，超过7天则设置为过期的, 默认为 0 永不过期
root@db1:~# mysql -u root -p
mysql> show binary logs;
mysql> show variables like '%log%';
mysql> set global expire_logs_days = 7;
|
expire_logs_days 一般设置为一个全备周期 + 1，如果全备周期为7天，就设置为 7+1=8
一般生产中至少保留2个全备周期
如果没有主从复制，可以通过下面的命令重置数据库日志，清除之前所有的日志文件：
注: 此操作危险，请谨慎操作！！！
但是如果存在复制关系，应当通过 PURGE 的名来清理 bin-log 日志，语法如下：
1
2
3
4
| # mysql -u root -p
mysql> purge master logs to 'mysql-bin.010’; //清除 mysql-bin.010 之前所有的日志
mysql> purge master logs before '2016-02-28 13:00:00'; //清除2016-02-28 13:00:00前的日志
mysql> purge master logs before date_sub(now(), interval 3 day); //清除3天前的bin日志
|
注意，不要轻易手动去删除 binlog，会导致 binlog.index 和真实存在的 binlog 不匹配，而导致 expire_logs_day 失效