Mysql开启二进制日志（binlog）

前言

binlog即二进制日志 Binary Log，它记录了所有的DDL和DML(除了数据查询语句)语句，以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。一般来说开启二进制日志大概会有一定的性能损耗。
二进制日志有两个最重要的使用场景:

• Mysql主从复制模式下，Master（主机）把它的二进制日志传递给Slaves（从机）来达到master-slave数据一致的目的。
• 数据恢复，如果出现数据误删通过使用mysqlbinlog工具进行数据恢复。

二进制日志包括两类文件：二进制日志索引文件（文件名后缀为.index）用于记录所有的二进制文件，二进制日志文件（文件名后缀为.00000*）记录数据库所有的DDL和DML(除了数据查询语句)语句事件。

开启binlog日志

• 查看是否开启

mysql> show variables like '%log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | OFF   |
| log_bin_basename                |       |
| log_bin_index                   |       |
| log_bin_trust_function_creators | OFF   |
| log_bin_use_v1_row_events       | OFF   |
| sql_log_bin                     | ON    |
+---------------------------------+-------+

其中log_bin=OFF，表示未开启，需要手动开启

• 查找linux系统上的mysql可执行程序所在目录

[root@hidden ~] which mysql
/usr/bin/mysql

我这里的查找结果是/usr/bin/myql

• 查找mysql配置文件所在路径
  执行/usr/bin/mysql --verbose --help | grep -A 1 'Default options'

[root@hidden ~] /usr/bin/mysql --verbose --help | grep -A 1 'Default options'

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 

利用上一步查找到的mysql可执行程序目录，查找mysql配置文件所在目录，查找后会输出多个目录，mysql按照顺序中这些文件中读取配置，如果上一个配置文件不存在则会去读取下一个，依次类推。
尝试查看第一个配置文件/etc/my.cnf的内容

[root@hidden ~] cat /etc/my.cnf
[mysqld]
pid-file=/var/run/mysqld/mysqld.pid
log-error=/var/log/mysqld.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

配置文件存在，因此就在此文件中设置log-bin

• 查看数据库版本

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.7.29-32-log |
+---------------+
1 row in set (0.00 sec)

我的mysql版本是5.7，按照如下配置开启binlog

• 配置binlog参数
  vim /etc/my.cnf在[mysqld]下添加

#开启并指定二进制日志前缀
log_bin=mysql-bin
#唯一id
server_id=11234
#二进制日志保存时间
expire_logs_days=7
binlog_format=ROW

贴上一份修改后的配置文件

[mysqld]
pid-file=/var/run/mysqld/mysqld.pid
log-error=/var/log/mysqld.log
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log_bin=mysql-bin
#唯一id
server_id=11234
expire_logs_days=7
binlog_format=ROW

#symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

[mysqld_safe]
log-error=/var/log/mariadb/mariadb.log
pid-file=/var/run/mariadb/mariadb.pid

mysql8.0开启binlog的配置与5.7略有区别，以后实操后再补上。

• 重启mysql
  service mysqld restart
  执行后进入mysql查看配置是否生效

mysql> 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 |
| log_bin_trust_function_creators | OFF                            |
| log_bin_use_v1_row_events       | OFF                            |
| sql_log_bin                     | ON                             |
+---------------------------------+--------------------------------+
6 rows in set (0.00 sec)

可以看出，成功开启了binlog

---

二进制日志的使用

经过上面的配置后，对数据库进行DDL和DML操作都会记录在binlog中，我们需要使用mysql自带的mysqlbinlog对日志进行分析转储。

• 查看二进制日志

  
  查看二进制日志.png
  

  可以看出最新的二进制日志是mysql-bin.000002，最近一次修改时间是2021/3/12/15：29，下面对这个日志进行分析。

• 使用mysqlbinlog对日志进行转储
  进入mysqlbinlog所在目录执行，可以先执行下which mysqlbinlog确定其所在目录

mysqlbinlog --no-defaults --base64-output=decode-rows -v --database=voisee --start-datetime="2021-3-12 15:20:00" --stop-datetime="2021-3-12 15:30:00" /var/lib/mysql/mysql-bin.000002    > 2021-3-12-from-15.20-to-15.30.txt

结果如下：

转储后的二进制日志.png

由于我在指定的时间段内仅执行了一个删除动作，所以仅有一条Delete语句。这里转储时一定要加上--base64-output=decode-rows -v参数将DDL、DML语句解码，否则输出的时编码后的语句无法直接阅读。

• 数据恢复
  获取转储后的文件后，对文件进行过滤从而得到可执行的sql语句即可进行数据回复。

• mysqlbinlog参数

mysqlbinlog 命令的语法格式：
mysqlbinlog mysql-bin.0000xx | mysql -u用户名 -p密码 数据库名

--------------------------------------------------------
常用参数选项解释：
--start-position=875 起始pos点
--stop-position=954 结束pos点
--start-datetime="2016-9-25 22:01:08" 起始时间点
--stop-datetime="2019-9-25 22:09:46" 结束时间点
--database=zyyshop 指定只恢复zyyshop数据库(一台主机上往往有多个数据库，只限本地log日志)
-------------------------------------------------------- 
不常用选项： 
-u --user=name 连接到远程主机的用户名
-p --password[=name] 连接到远程主机的密码
-h --host=name 从远程主机上获取binlog日志
--read-from-remote-server 从某个MySQL服务器上读取binlog日志