MySQL binlog

一、 Bin Log

Mysql的binlog日志作用是用来记录对mysql数据库有更新的内容的记录；

PS: 有更新的内容， 如果一条SQL没有更新任何数据，是没有binlog的？

MySQL 会把用户对所有数据库的内容和结构的修改情况记入binlog文件，而不会记录SELECT和没有实际操作意义的语句。

二、 如何启动

以下操作环境：
Mac OS 10.13.3
MySQL 5.7

1. 查看当前 binlog 状态

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

2. 修改 my.cnf 文件支持 binlog

2.1 查看 my.cnf 的位置

$ mysql --help --verbose | grep my.cnf
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf 

2.2 在/etc 下添加 my.cnf 文件, 加入以下内容

[mysqld]
log-bin = mysql-bin
binlog-format = ROW
server_id =1

2.3 重启 MySQL 服务， 查看状态。

mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+
1 row in set (0.01 sec)

三、 查看 binlog 文件

1. 查看日志目录

mysql> show variables like '%log_bin%';
+---------------------------------+---------------------------------------+
| Variable_name                   | Value                                 |
+---------------------------------+---------------------------------------+
| log_bin                         | ON                                    |
| log_bin_basename                | /usr/local/mysql/data/mysql-bin       |
| log_bin_index                   | /usr/local/mysql/data/mysql-bin.index |
| log_bin_trust_function_creators | OFF                                   |
| log_bin_use_v1_row_events       | OFF                                   |
| sql_log_bin                     | ON                                    |
+---------------------------------+---------------------------------------+
6 rows in set (0.00 sec)

• log_bin_index: 所有 binlog 文件的索引，这个文件保存了所有binlog的文件名
• log_bin_basename: binlog 文件位置及前缀

2. 查看 binlog

ROW 模式 查看 binlog, 把 binlog 输出到 mysql.log 文件

# mysqlbinlog --base64-output="decode-rows" -v mysql-bin.000001 -r mysql.log

四、 清理 binlog 文件

1. 手动清理（慎用，误操作就GG）

1.1 查看主库 & 从库正在使用的日志文件

show master status;
show slave status;

正在使用的 binlog 文件是删除不了的。

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     4801 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

mysql> show slave status;
Empty set (0.00 sec)

1.2 删除 binlog文件 （PS: 删除前先备份）######

按时间节点删除，删除某个时间之前的所有 binlog；

mysql> purge master logs before'2016-09-01 17:20:00';

按 binlog 文件删除；

mysql> purge master logs to 'mysql-bin.000001';

注意：
时间和文件名一定不可以写错，尤其是时间中的年和文件名中的序号，以防不小心将正在使用的binlog删除！！！

切勿删除正在使用的binlog！！！
使用该语法，会将对应的文件和mysql-bin.index中的对应路径删除。

2. 通过设置 binlog 过期的时间，使系统自动删除 binlog 文件

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> set global expire_logs_days = 30;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 30    |
+------------------+-------+
1 row in set (0.00 sec)

注意：
过期时间设置的要适当，对于主从复制，要看从库的延迟决定过期时间，避免主库binlog还未传到从库便因过期而删除，导致主从不一致！！！

五、MySQL binlog的三种工作模式

1. Row level

日志中会记录每一行数据被修改的情况，然后在slave端对相同的数据进行修改。
　　优点：能清楚的记录每一行数据修改的细节
　　缺点：数据量太大

2. Statement level（默认）

每一条被修改数据的sql都会记录到master的bin-log中，slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行
　　优点：解决了 Row level下的缺点，不需要记录每一行的数据变化，减少bin-log日志量，节约磁盘IO，提高性能
　　缺点：容易出现主从复制不一致

3. Mixed（混合模式）

结合了Row level和Statement level的优点

根据优缺点，在不同的业务场景使用不同的模式。
主从强一致性：ROW
效率要求较高：Statement Level
兼得：Mixed