查询日志

记录查询操作：

# 是否启用查询日志：
#     ON：开启
#     OFF：关闭
general_log
# 查询日志文件位置，相对路径时的默认是相对 datadir 目录，也可指定绝对路径，默认文件名为 <HOSTNAME>.log
general_log_file
# 指定日志输出格式：
#     FILE：输出到文件；
#     TABLE：输出到表；
#     NONE：不保存；
# 可用 , 隔开以同时启用，如 FILE, TABLE
log_output

慢查询日志

执行时长超出指定时长的查询操作：

# 指定一个时长，单位为秒，执行时长超过这个时长的查询就视为慢查询，被记录在慢查询日志中
long_query_time
# 是否启用慢查询日志：
#     ON：开启；
#     OFF：关闭；
slow_query_log
# 查询日志文件位置，相对路径时的默认是相对 datadir 目录，也可指定绝对路径，默认文件名未 <HOSTNAME>-slow.log
slow_query_log_file
# 是否记录未走索引的查询日志，ON | OFF
log_queries_not_using_indexes

错误日志

错误日志会记录如下信息：

• mysqld 启动和关闭过程中输出的事件信息；
• mysqld 运行中产生的错误信息；
• event scheduler 运行一个 event 时产生的日志信息；
• 在主从复制架构中的从服务器上启动从服务器线程时产生的信息；

# 指定错误日志输出位置：
#     指定相对路径时默认记录在 datadir 目录；
#     可指定一个绝对路径；
#     也可指定 stderr 让日志输出到屏幕；
log_error
# 是否记录警告信息到错误日志文件中：
#     1：开启；
#     0：关闭；
log_warning

例：

log_error=/data/3306/logs/mysql.err

二进制日志

基本语句

记录导致数据改变或潜在导致数据改变的 SQL 语句，可通过 “重放”日志文件的事件生成数据副本。
查看目前正在使用的二进制日志文件：

-- 在 my.cnf 中开启：
--    log-bin=mysql-bin.log
--    必须要指定 server-id
-- 或 show master logs
mysql> show binary logs                                                                                                                                                                                                                                 
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 | 154       |
+------------------+-----------+
1 row in set
Time: 0.014s

查看指定日志文件的事件日志：

mysql> show binlog events in 'mysql-bin.000001'
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4   | Format_desc    | 1         | 123         | Server ver: 5.7.28-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1         | 154         |                                       |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

2 rows in set
Time: 0.015s

二进制日志文件可有多个，可以查看当前正在使用的日志文件：

mysql> show master status                                                                                                                                  
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 154      |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

1 row in set
Time: 0.014s

可手动来生成新的二进制文件：

mysql> flush logs;
Query OK, 0 rows affected (0.01 sec)
mysql> show binary logs;
+--------------+-----------+
| Log_name     | File_size |
+--------------+-----------+
| mysql.000001 |       197 |
| mysql.000002 |       154 |
+--------------+-----------+
2 rows in set (0.00 sec)

二进制日志的记录格式（可通过 select @@binlog_format; 查看）：

• 基于“语句”记录：statement；
• 基于“行”记录：row；
• 混合模式：mixed，让系统自行判定基于何种方式进行；

二进制日志文件的构成：

• 日志文件：mysql-bin.<文件名后缀>，二进制格式；
• 索引文件：mysql-bin.index，show binary logs 就是检索这个文件的内容；

相关变量

# 指定是否启用二进制日志：
#     ON：以滚动方式记录；
#     OFF：禁用；
log_bin
# 指定是否启用二进制日志，ON | OFF
sql_log_bin
# 二进制日志记录的格式：
#     STATEMENT：基于语句记录；；
#     ROW：基于行记录；
#     MIXED：混合模式；
binlog_format
# 单个二进制文件的最大体积，单位为字节，默认为 1G
# 注意：
#     1. 到达最大值会自动滚动；
#     2. 文件到达上限时的大小未必为指定的精确值；
max_binlog_size
# 语句日志缓存最大体积
max_binlog_stmt_cache_size
# 行日志缓存最大体积
max_binlog_cache_size
# 日志的过期时长
expire_logs_days
# 同步写入日志，设定是否启用二进制日志同步功能：
#     1：事务提交就立即同步日志；
#     0：禁用
sync_binlog
# binlog 文件除去版本号的全路径
log_bin_basename

使用 gtid

编辑 /etc/my.cnf，添加如下配置：

gtid_mode=ON                  #开关
enforce_gtid_consistency=ON   #强制GTID一致性
log_slave_updates=ON          #强制从库更新binlog

重启生效，查看当前使用的 gtid 编号：

mysql> show master status;
+--------------+----------+--------------+------------------+----------------------------------------+
| File         | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |
+--------------+----------+--------------+------------------+----------------------------------------+
| mysql.000004 |      323 |              |                  | 1ab44cc3-83d2-11ea-a51d-000c29b7d4cd:1 |
+--------------+----------+--------------+------------------+----------------------------------------+
1 row in set (0.00 sec)

通过 gtid 的方式截取日志：

$ mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:1-3' /data/3306/logs/mysql-bin.000016 >/tmp/gt1.sql

一定要包含 --skip-gtids，gtid 有“幂等性”检查。GTID 的生成，是通过 set gtid_next 命令实现的。执行 set 命令时，自动检查当前系统是否包含这个 GTID 信息，如果有就跳过。

跳过指定 GTID 的日志：

$ mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:1-10' --exclude-gtids='202628e9-9265-11ea-b4a0-000c29248f69:5'

也可多文件截取：

$ mysqlbinlog --skip-gtids --include-gtids='202628e9-9265-11ea-b4a0-000c29248f69:1-10'  bin001 bin002  bin003

binlog 清理

-- 清除 000004 之前的 binlog 文件，不包含 000004
mysql> PURGE BINARY LOGS TO 'mysql-bin.000004';
-- 清除指定时间之前的 binlog 信息
mysql> PURGE BINARY LOGS BEFORE '2020-05-11 18:14:47';

mysqlbinlog

mysqlbinlog 是一个用于查看二进制日志的客户端命令工具。

# 显示全部日志信息
$ mysqlbinlog /data/3306/data/mysql-bin.000002 
# 从指定事件开始显示，与之对应的还有 --stop-position
$ mysqlbinlog --start-position=378 /data/3306/data/mysql-bin.000002 
# 从指定事件开始显示，与之对应的还有 --stop-datetime
$ mysqlbinlog --start-datetime='2020-05-04 20:00:00' /data/3306/data/mysql-bin.000002 

...
# at 219
#200504 20:17:13 server id 1  end_log_pos 313 CRC32 0x0dc16ef0  Query   thread_id=2     exec_time=0     error_code=0
SET TIMESTAMP=1588594633/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=8/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
create database mydb
/*!*/;
# at 313
#200504 20:17:41 server id 1  end_log_pos 378 CRC32 0xa5a8973c  Anonymous_GTID  last_committed=1        sequence_number=2       rbr_only=no
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 378
#200504 20:17:41 server id 1  end_log_pos 493 CRC32 0x9ef38800  Query   thread_id=2     exec_time=0     error_code=0
use `mydb`/*!*/;
SET TIMESTAMP=1588594661/*!*/;
create table tb1(id int,name varchar(32))
/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

说明：

事件发生的日期和时间；(200504 20:17:13)
事件发生在服务器的标识（server id）
事件的结束位置：（end_log_pos 313
事件的类型：(Query)
事件发生时所在的服务器执行此事件的线程的ID：（thread_id=2）
语句的时间戳与将其写入二进制文件中的时间差：（exec_time=0）
错误代码：（error_code=0)
事件内容：（SET TIMESTAMP=1588594633/*!*/;
            ...
            create database mydb）                        

GTID：全局事务标识
事件所属的全局事务的 GTID：（GTID 0-1-2）

作用：
对于 DDL、DCL 语句，直接将 SQL 本身记录到 binlog 中。
对于 DML： insert、update、delete 受到 binlog_format 参数控制。

• SBR ： Statement，语句模式。之前版本，默认模式
• RBR ： ROW，行记录模式。5.7 以后，默认模式
• MBR ： miexd，混合模式。

SBR、RBR 区别：

以 update t1 set name='zhangsan' where id<100 为例：
SBR: 记录 SQL 本身，RBR 记录 100 个数据行的变化。
    SBR 日志量少，RBR 日志量大。
    SBR 记录不够准确，RBR 记录够准确。

中继日志

复制架构中，从服务器用于保存从主服务器的二进制日志中读取到的事件。

事务日志

事务性存储引擎自行管理和使用。