二进制日志的作用
二进制日志主要记录 MySQL 数据库的变化。它会记录所有更改表数据和表结构的事件，比如 update、delete 等，它也会记录语句执行所用的时间。为了保证事件的准确性，会记录事件的长度。它不会记录 select、show 语句的执行。使用二进制日志主要有两种用途：
主从备份。主库上的二进制日志记录主库的所有变更，主库会把日志包含的事件发给从库，从库执行这些事件，从而与主库的数据保持一致；
从二进制日志恢复数据库。
Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order.
在 commit 执行之前，写入二进制日志。
配置方式
默认情况下，二进制日志是关闭的，可以通过修改 MySQL 的配置文件来启动和设置二进制日志。
查看 MySQL 配置文件的路径：
shell> mysql --help | grep my.cnf/etc/my.cnf /etc/mysql/my.cnf /usr/local/etc/my.cnf ~/.my.cnf
如果是用 brew 安装的 mysql，配置文件默认在 /usr/local/etc/my.cnf
在配置文件的 [mysqld] 下面添加：
# 由于 mysql 的 bug，必须配置 server-id，可以是任意值server-id=11# 开启二进制日志，日志文件名称是 mysql-binlog-bin=mysql-bin# 设置过期时间，10天后自动删除旧的日志。默认是 0，表示永不删除expire_logs_days = 10# 设置每个日志文件的大小，超过这个值，重新创建新的文件。最小值是4096B，最大值是 1GB，默认是1GBmax_binlog_size = 100M
注意：必须配置 server-id，否则启动 MySQL 会报错，官方文档有说明：
If you specify the
--log-binoption without also specifying a--server-id, the server is not allowed to start. (Bug #11763963, Bug #56739)
日志文件默认保存在datadir目录下，也可以设置为其他路径，比如 log-bin=/usr/local/var/mysql/mysql-bin。即使在文件名称后面添加后缀，mysql 也会忽略，mysql 会添加数字后缀。
也可以直接配置为 log-bin，不加任何参数。此时日志文件的默认名称是 host_name-bin，host_name 是你的主机名称。
除了二进制日志，mysql 还会创建一个 mysql-bin.index 文件，该文件保存所有的二进制日志的文件名称。可以直接使用 cat 命令查看，不要手动修改这个文件。
另外，每次重新启动 mysql，也会创建新的二进制日志。
当前客户端可以暂时关闭二进制日志： SET sql_log_bin=OFF。
查看方法
使用 show variables like 'log_bin%'; 查询二进制日志设置：
image
使用 show binary logs; 查看二进制日志文件个数及文件名称：
image
使用 show master status 查看当前正在使用的日志：
image
使用 SHOW SLAVE STATUS 可以在从库上查看它在读取哪个日志；
使用 mysqlbinlog 查看二进制日志的内容，红色方框里面是该文件的创建时间：
image
二进制日志的格式
有三种格式：row-based logging（ROW），statement-based logging（STATEMENT），mixed-base logging（MIXED）。
There are several reasons why a client might want to set binary logging on a per-session basis:
A session that makes many small changes to the database might want to use row-based logging.
A session that performs updates that match many rows in the
WHEREclause might want to use statement-based logging because it will be more efficient to log a few statements than many rows.Some statements require a lot of execution time on the master, but result in just a few rows being modified. It might therefore be beneficial to replicate them using row-based logging.
官方文档有这样两句话：
Statement may not be safe to log in statement format.
You can avoid these issues by using MySQL's row-based replication instead.
查看日志格式：
mysql> select @@session.binlog_format;mysql> select @@global.binlog_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 格式，选中的日志是 STATEMENT 格式：
image
删除二进制日志
使用 reset master; 删除所有的二进制日志文件。MySQL 会重新创建二进制日志，新的日志文件扩展名从 000001 开始编号。
MySQL 还提供了删除指定日志的方法：
purge master logs to 'log_name' 删除比 log_name 编号小的日志，比如 purge master logs to 'mysql-bin.000003';，编号小于 000003 的日志会被删除。
purge master logs before 'datatime' 删除 datatime 之前的日志，日期格式是 YYYY-MM-DD hh:mm:ss。
如果日志文件正在用于主从备份，它不会被删除。
使用二进制日志恢复数据库
在使用 mysql 的全量备份恢复数据库之后，可以再使用二进制日志恢复到指定时间点。比如当前时间是11点多，我不小心把一个重要的库删除了。幸好每天凌晨两点做了数据库全量备份，这时我就可以先恢复数据库到两点，然后再使用二进制日志恢复到删除之前的数据库。
直接使用日志文件恢复数据库：
shell> mysqlbinlog binlog_files | mysql -u root -p
可以把日志文件导出文本，然后编辑文本，删除一些不需要执行的语句，然后再恢复数据库：
shell> mysqlbinlog binlog_files > tmpfileshell> ... edit tmpfile ...shell> mysql -u root -p < tmpfile
如果有多个日志文件，在一个连接中使用它们：
shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p
恢复到指定时间点：
shell> mysqlbinlog --stop-datetime="2018-10-25 11:00:00" \ /var/log/mysql/bin.123456 | mysql -u root -p
从指定时间点恢复：
shell> mysqlbinlog --start-datetime="2018-10-25 10:01:00" \ /var/log/mysql/bin.123456 | mysql -u root -p
根据位置恢复：
shell> mysqlbinlog --stop-position=368312 /var/log/mysql/bin.123456 \ | mysql -u root -pshell> mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \ | mysql -u root -p
作者：flykyle
链接：https://www.jianshu.com/p/58e4e26a00ca
随时随地看视频
