简介
主要作用
- 主从复制
- 数据恢复 基于时间点的恢复。
3种格式
- STATEMENT 基于sql语句。
- ROW 基于row变化。
- MIXED 混杂模式。
可以运行过程种设定logging format：
mysql> SET GLOBAL binlog_format = 'STATEMENT';
mysql> SET GLOBAL binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'MIXED';
mysql> SET SESSION binlog_format = 'STATEMENT';
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET SESSION binlog_format = 'MIXED';
不过一般直接设置成row模式，运行不要去更改，特别是存在主从复制的场景下。
相关参数
- log_bin 控制是否打开binlog
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin | ON |
+---------------+-------+
1 row in set (0.00 sec)
- log_bin_basename 日志的存储路径及命名
mysql> show variables like 'log_bin_basename';
+------------------+-----------------------------+
| Variable_name | Value |
+------------------+-----------------------------+
| log_bin_basename | /usr/local/var/mysql/binlog |
+------------------+-----------------------------+
1 row in set (0.00 sec)
mysql> \q
Bye
jonathanliu@jonathanliu:/usr/local/var/mysql$ ls /usr/local/var/mysql/
auto.cnf binlog.000018 client-cert.pem ibtmp1 mysql server-cert.pem
binlog.000013 binlog.000019 client-key.pem jonathanliu.err mysql.ibd server-key.pem
binlog.000014 binlog.000020 ib_buffer_pool jonathanliu.local.err mytest sys
binlog.000015 binlog.index ib_logfile0 jonathanliu.local.pid performance_schema t1
binlog.000016 ca-key.pem ib_logfile1 jonathanliu.pid private_key.pem undo_001
binlog.000017 ca.pem ibdata1 mydb public_key.pem undo_002
jonathanliu@jonathanliu:/usr/local/var/mysql$
- innodb_flush_log_at_trx_commit
1 默认值 Logs are written and flushed to disk at each transaction commit.
0 logs are written and flushed to disk once per second.
2 logs are written after each transaction commit and flushed to disk once per second.
- sync_binlog
默认值:>= 5.7.7(1) ****** <=5.7.6(0)
0 Disables synchronization of the binary log to disk by the MySQL server.依赖操作系统将binary log 刷入磁盘，性能较佳，但是电源和操作系统故障可能丢失数据。
1 在事务提交前，将binary log写入磁盘。
N 收集 N binary log commit groups才写入磁盘
- 最稳妥和推荐的设置：
innodb_flush_log_at_trx_commit =1
sync_binlog = 1
mysql 这两个设置比较奇葩，高版本的mysql默认值都是1，1，作为一个数据库，底线是保证数据库不丢失，没必要搞出这两个设置。mysql还有很多设计不合理的地方，估计oracle接手之后，这些不符合逻辑的东西都会慢慢改过来，包括mysql的utf8.
