Binlog 简介
MySQL中一般有以下几种日志:
日志类型
写入日志的信息
错误日志
记录在启动,运行或停止mysqld时遇到的问题
通用查询日志
记录建立的客户端连接和执行的语句
二进制日志
记录更改数据的语句
中继日志
从复制主服务器接收的数据更改
慢查询日志
记录所有执行时间超过 long_query_time 秒的所有查询或不使用索引的查询
DDL日志(元数据日志)
元数据操作由DDL语句执行
本文主要介绍二进制日志 binlog。
MySQL 的二进制日志 binlog 可以说是 MySQL 最重要的日志,它记录了所有的 DDL 和 DML 语句(除了数据查询语句select、show等),以事件形式记录,还包含语句所执行的消耗的时间,MySQL的二进制日志是事务安全型的。binlog 的主要目的是复制和恢复。
Binlog日志的两个最重要的使用场景
MySQL主从复制:MySQL Replication在Master端开启binlog,Master把它的二进制日志传递给slaves来达到master-slave数据一致的目的
数据恢复:通过使用 mysqlbinlog工具来使恢复数据
启用 Binlog
注:笔者实验的MySQL版本为:5.7.22
一般来说开启binlog日志大概会有1%的性能损耗。
启用binlog,通过配置 /etc/my.cnf 或 /etc/mysql/mysql.conf.d/mysqld.cnf 配置文件的 log-bin 选项:
在配置文件中加入 log-bin 配置,表示启用binlog,如果没有给定值,写成 log-bin=,则默认名称为主机名。(注:名称若带有小数点,则只取第一个小数点前的部分作为名称)
[mysqld]
log-bin=my-binlog-name
也可以通过 SET SQL_LOG_BIN=1 命令来启用 binlog,通过 SET SQL_LOG_BIN=0 命令停用 binlog。启用 binlog 之后须重启MySQL才能生效。
常用的Binlog操作命令
# 是否启用binlog日志
show variables like 'log_bin';
# 查看详细的日志配置信息
show global variables like '%log%';
# mysql数据存储目录
show variables like '%dir%';
# 查看binlog的目录
show global variables like "%log_bin%";
# 查看当前服务器使用的biglog文件及大小
show binary logs;
# 查看主服务器使用的biglog文件及大小
# 查看最新一个binlog日志文件名称和Position
show master status;
# 事件查询命令
# IN 'log_name' :指定要查询的binlog文件名(不指定就是第一个binlog文件)
# FROM pos :指定从哪个pos起始点开始查起(不指定就是从整个文件首个pos点开始算)
# LIMIT [offset,] :偏移量(不指定就是0)
# row_count :查询总条数(不指定就是所有行)
show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
# 查看 binlog 内容
show binlog events;
# 查看具体一个binlog文件的内容 (in 后面为binlog的文件名)
show binlog events in 'master.000003';
# 设置binlog文件保存事件,过期删除,单位天
set global expire_log_days=3;
# 删除当前的binlog文件
reset master;
# 删除slave的中继日志
reset slave;
# 删除指定日期前的日志索引中binlog日志文件
purge master logs before '2019-03-09 14:00:00';
# 删除指定日志文件
purge master logs to 'master.000003';
写 Binlog 的时机
对支持事务的引擎如InnoDB而言,必须要提交了事务才会记录binlog。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。
如果设置为0,则表示MySQL不控制binlog的刷新,由文件系统去控制它缓存的刷新;
如果设置为不为0的值,则表示每 sync_binlog 次事务,MySQL调用文件系统的刷新操作刷新binlog到磁盘中。
设为1是最安全的,在系统故障时最多丢失一个事务的更新,但是会对性能有所影响。
如果 sync_binlog=0 或 sync_binlog大于1,当发生电源故障或操作系统崩溃时,可能有一部分已提交但其binlog