MySQL高级之日志

一、二进制日志

1. 基本知识

1.1 基本概念

• 二进制日志，英文简称binlog，即binary log，也叫作变更日志（update log）。它记录了数据库所有执行的DDL和DML等数据库更新事件的语句，但是不包含没有修改任何数据的语句（如数据查询语句select、show等）。

1.2 作用

• 数据恢复
  如果MySQL数据库意外停止，可以通过二进制日志文件来查看用户执行了哪些操作，对数据库服务器文件做了哪些修改，然后根据二进制日志文件中的记录来恢复数据库服务器。
• 数据复制
  由于日志的延续性和时效性，master把它的二进制日志传递给slaves来达到master-slave数据一致的目的。

1.3 三种记录方式

• binlog_format=STATEMENT：每一条会修改数据的sql语句会记录到binlog中。这是默认的binlog格式。
• binlog_format=ROW：不记录每条sql语句的上下文信息，仅记录哪条数据被修改了，修改成什么样了。
• binlog_format=MIXED：一般的语句修改使用statment格式保存binlog。对于一些函数而言，statement无法完成主从复制的操作，则采用row格式保存binlog。

2. 相关指令

2.1 查看默认情况

show variables like '%log_bin%';

# 查看二进制日志文件列表及其大小
show binary logs;

2.2 设置日志参数

方式一：永久性修改

[mysqld]
# 启用二进制日志
# 设置二进制文件所在目录和文件名
log-bin="/var/lib/mysql/binlog/atguigu-bin"
# 设置二进制文件过期时间
binlog_expire_logs_seconds=600
# 设置二进制文件大小
max_binlog_size=100M

方式二：暂时性修改

# 全局启用二进制文件
set global sql_log_bin=0;
# 会话启用二进制文件
set sql_log_bin=0;

2.3 查看日志内容

• 当MySQL创建二进制日志文件时，先创建一个以“filename”为名称、以“.index”为后缀的文件，再创建一个以“filename”为名称、以“.000001”为后缀的文件。
• MySQL服务重新启动一次 ，以“.000001”为后缀的文件就会增加一个，并且后缀名按1递增。即日志文件的个数与MySQL服务启动的次数相同；如果日志长度超过了max_binlog_size的上限（默认是1GB），就会创建一个新的日志文件。但也并不完全保证必须小于1GB，如果日志文件大小已满1GB但是当前事务未存储完成，仍会存储完当前事务内容，再开启新的日志进行存储。

方式一

mysqlbinlog [-v] 'log_name';
# -v表示以伪SQL语句形式显示

方式二

 show binlog events [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count];
 # IN 'log_name' ：指定要查询的binlog文件名（不指定就是第一个binlog文件）　
# FROM pos ：指定从哪个pos起始点开始查起（不指定就是从整个文件首个pos点开始算）
# LIMIT [offset] ：偏移量(不指定就是0)
# row_count :查询总条数（不指定就是所有行）

2.4 进行数据恢复

mysqlbinlog [option] filename|mysql –uuser -ppass;
# filename：是日志文件名。
# option：可选项，比较重要的两对option参数是--start-date、--stop-date 和 --start-position、--stop-position。
# --start-date 和 --stop-date：可以指定恢复数据库的起始时间点和结束时间点。
# --start-position和--stop-position：可以指定恢复数据的开始位置和结束位置。

2.5 删除日志文件

删除指定日志文件

# 删除指定日志文件名前的日志文件
PURGE {MASTER | BINARY} LOGS TO ‘指定日志文件名’
# 删除指定日期前的日志文件
PURGE {MASTER | BINARY} LOGS BEFORE ‘指定日期’

删除全部日志文件（不许用）

reset master;

3. binlog的写入机制

3.1 写入机制

• binlog的写入时机也非常简单，事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。因为一个事务的binlog不能被拆开，无论这个事务多大，也要确保一次性写入，所以系统会给每个线程分配一个块内存作为binlog cache。
  

3.2 参数设置

• write和fsync的时机，可以由参数sync_binlog控制，默认是0。为0的时候，表示每次提交事务都只write，由系统自行判断什么时候执行fsync。虽然性能得到提升，但是如果系统宕机，page cache里面的binglog会丢失。
  

• 为了安全起见，可以设置参数sync_binlog为 1 ，表示每次提交事务都会执行fsync，就如同redo log刷盘流程一样。
  

• 还可以设置参数sync_binlog为N（N>1），这是一种折中方式，表示每次提交事务都write，但累积N个事务后才fsync。在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。同样的，如果机器宕机，会丢失最近N个事务的binlog日志。
  

3.3 两阶段提交

3.3.1 问题的引入

在执行更新语句过程，会记录redo log与binlog两块日志，以基本的事务为单位，进行日志的写入。这里就会存在一个问题，如果已经写完了redo log日志但还未写入binlog日志时，发生了故障，此时重启系统，就会造成两个日志内的内容不同，从而导致主机和从机（备机）数据不一致，即产生数据一致性的问题。因此，提出了两阶段提交来解决此问题。

3.3.2 两阶段提交

所谓的两阶段提交，就是在写入binlog前后分别增加一个阶段，分别称为redo log的prepare阶段和redo log的commit阶段。此时，如果出现故障重启之后，要根据阶段判断事务要进行回滚还是提交，来保证数据的一致性。

4. 注意：个人理解

• 通常我们说在提交事务的时候，进行binlog和redo log日志的写入。但是，实际上也不能完全这么描述，因为从根本来说，只有当redo log日志书写完成之后，事务才算是真正的进行了提交，才算完成了commit。而binlog由于采用了两阶段提交的技术，实际上binlog的写入要在redo log的commit之前进行。
• 实际上，当我们写完一个事务，输入commit按下回车的瞬间，MySQL内部先进行redo log的prepare阶段，然后进行binlog的写入，随后进行redo log的commit阶段。等到上述阶段都完成之后，事务才算被提交了，否则，事务就是处于未提交的状态。

5. 附加：binlog和redo log的区别

• redo log它是物理日志，记录内容是“在某个数据页上做了什么修改”，属于InnoDB存储引擎层产生的。
• binlog是逻辑日志，记录内容是语句的原始逻辑，类似于“给ID=2这一行的c字段加1”，属于MySQL Server层。

二、中继日志

1. 基础知识

1.1 基本概念

• 中继日志只在主从服务器架构的从服务器上存在。从服务器为了与主服务器保持一致，要从主服务器读取二进制日志的内容，并且把读取到的信息写入本地的日志文件中，这个从服务器本地的日志文件就叫中继日志。
• 搭建好主从服务器之后，中继日志默认会保存在从服务器的数据目录下。文件名的格式是：从服务器名 -relay-bin.序号。中继日志还有一个索引文件： 从服务器名 -relay-bin.index，用来定位当前正在使用的中继日志。

1.2 作用

• 从服务器读取中继日志，并根据中继日志的内容对从服务器的数据进行更新，完成主从服务器的数据同步。

三、慢查询日志

• 记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。

四、通用查询日志

• 通用查询日志用来记录用户的所有操作，包括启动和关闭MySQL服务、所有用户的连接开始时间和截止时间、发给MySQL数据库服务器的所有SQL指令等。当我们的数据发生异常时，查看通用查询日志，还原操作时的具体场景，可以帮助我们准确定位问题。

五、错误日志

• 记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。

六、REDO和UNDO日志

• 前几篇文章做了详细的介绍，在此不再叙述。