mysql binlog 浅谈

如何开启MySQL的binlog日志

在MySQL中，binlog指的是binary log，二进制日志文件。这个文件记录了MySQL所有的DML操作。通过binlog日志，我们可以做数据恢复，做主从复制等等。对于运维或架构人员来说，开启binlog日志功能非常重要。

那么，如何开启MySQL的binlog日志呢？下面将介绍两种方法。

方法一：在my.cnf主配置文件中添加参数

在my.cnf主配置文件中，找到[mysqld]模块，然后添加以下三行参数。

```
log_bin=ON
log_bin_basename=/var/lib/mysql/mysql-bin
log_bin_index=/var/lib/mysql/mysql-bin.index
```

参数解释：

- log_bin：开启binlog日志文件，默认值为OFF。
- log_bin_basename：binlog日志的基本文件名。MySQL会在该文件名后追加标识来表示每一个binlog文件。
- log_bin_index：binlog文件的索引文件，管理所有的binlog文件。

方法二：使用log-bin参数

如果你使用的是MySQL 5.7及以上版本，使用log-bin参数更加方便。在my.cnf配置文件中，找到[mysqld]模块，添加以下一行参数即可。

```
log-bin=/var/lib/mysql/mysql-bin
```

这个参数的作用和上面三个参数的作用是相同的。MySQL会根据这个配置自动开启binlog日志，自动设置log_bin_index文件为你指定的文件名后跟.index。参数log-bin指定了binlog文件的基本文件名。

需要注意的是，如果你使用MySQL 5.7及以上版本，必须添加一个额外的参数server-id=123454（随机指定一个不能重名的字符串），否则重启MySQL服务会报错。

然后，重启MySQL服务即可。

- 在CentOS 6上，使用以下命令重启MySQL服务：

```
service mysqld restart
```

- 在CentOS 7上，使用以下命令重启MySQL服务：

```
systemctl restart mysqld
```

验证是否开启binlog日志

开启binlog日志之后，我们可以登录MySQL终端，执行以下命令，查看是否成功开启binlog日志：

```
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 |
+---------------------------------+----------------------------------------+
```

同时，在/var/lib/mysql目录下，你可以看到多个mysql-bin的文件，还有一个mysql-bin.index的文件，这表明binlog日志已经成功启用。

以上就是如何开启MySQL的binlog日志的方法。对于运维或架构人员来说，开启binlog日志功能非常重要。你可以随时访问这些文件来查看有哪些操作被执行了。希望这篇文章对你有所帮助！

二进制日志（binnary log）以事件形式记录了对MySQL数据库执行更改的所有操作。

binlog是记录所有数据库表结构变更（例如CREATE、ALTER TABLE…）以及表数据修改（INSERT、UPDATE、DELETE…）的二进制日志。不会记录SELECT和SHOW这类操作，因为这类操作对数据本身并没有修改，但可以通过查询通用日志来查看MySQL执行过的所有语句。

需要注意的一点是，即便update操作没有造成数据变化，也是会记入binlog。

binlog有两个常用的使用场景：

• 主从复制：mysql replication在master端开启binlog,master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

• 数据恢复：通过mysqlbinlog工具来恢复数据。

2、关键参数

binLog默认是关闭的，可以通过参数log_bin控制：

mysql> show variables like 'log_bin'\G;
*************************** 1. row
Variable_name: log_bin
Value: OFF

参数max_binlog_size指定了单个二进制日志文件的最大值:

mysql> show variables like 'max_binlog_size'\G;
1. row
: max_binlog_size
Value: 1073741824

默认为1G，如果超过该值，会写入新的文件，并记录到.index文件。

InnoDB会将所有未提交的binLog写到一个缓存中，等事务提交后再将缓存刷新到文件。缓存大小有参数binlog_cache_size控制。

mysql> show variables like 'binlog_cache_size'\G;
1. row
: binlog_cache_size
Value: 32768

需要注意的是，该值是基于session的，每个事务都会分配一个大小为binlog_cache_size的缓存。当一个事务的记录大于该值，MySQL会把缓冲中的日志写入一个临时文件。因此，需要根据使用场景合理设置这个参数，过大或者过小都会影响性能。

参数sync_binlog表示每写缓冲多少次就同步到磁盘：

mysql> show variables like 'sync_binlog'\G;
1. row
: sync_binlog
Value: 1

• N=1：表示采用同步写磁盘的方式来写二进制日志，这时写操作不使用操作系统的缓冲来写二进制日志，每次事务提交都会写入文件。

• N=0：表示MySQL不控制binlog的刷新，由文件系统自己控制它的缓存的刷新。这时候的性能是最好的，但是风险也是最大的。因为一旦系统Crash，在binlog_cache中的所有binlog信息都会被丢失。

但是，即使将sync_binlog设为1，还是会有一种情况会导致问题的发生。当使用InnoDB存储引擎时，在一个事务发出COMMIT动作之前，由于sync_binlog设为1，因此会将二进制日志立即写入磁盘。如果这时已经写入了二进制日志，但是提交还没有发生，并且此时发生了宕机，那么在MySQL数据库下次启动时，因为COMMIT操作并没有发生，所以这个事务会被回滚掉。但是二进制日志已经记录了该事务信息，不能被回滚。这个问题可以通过将参数innodb_support_xa设为1来解决，虽然innodb_support_xa与XA事务有关，但它同时也确保了二进制日志和InnoDB存储引擎数据文件的同步。

binlog_format是一个非常重要的参数，决定了记录二进制日志的格式：

mysql> show variables like 'binlog_format'\G;
1. row
: binlog_format
Value: ROW

可选值有：

• STATEMENT

  记录SQL语句。日志文件小，节约IO，但是对一些系统函数不能准确复制或不能复制，如now()、uuid()等

• ROW

  记录表的行更改情况，可以为数据库的恢复、复制带来更好的可靠性，但是二进制文件的大小相较于STATEMENT会有所增加

• MIXED

  STATEMENT和ROW模式的混合。默认采用STATEMENT格式进行二进制日志文件的记录，但是在一些情况下会使用ROW格式。

业内目前推荐使用的是ROW模式，准确性高，虽然说文件大，但是现在有SSD和万兆光纤网络，这些磁盘IO和网络IO都是可以接受的。