mysql 系统库（四） —— 复制信息、日志记录表

Hehuyi_In

已于 2023-01-20 22:44:19 修改

阅读量3.3k

点赞数

分类专栏：灾备 MySQL 文章标签： mysql 数据库 java

于 2020-04-14 00:31:55 首次发布

本文链接：https://blog.csdn.net/Hehuyi_In/article/details/105329434

版权

MySQL 同时被 2 个专栏收录

55 篇文章 4 订阅

订阅专栏

灾备

22 篇文章 3 订阅

订阅专栏

一、复制信息记录表概述

1. 复制信息表简介

复制信息表用于在从库在复制主库数据期间，保存从主库转发到从库的二进制日志事件、中继日志当前状态和位置的信息。

master.info文件或者mysql.slave_master_info表：IO线程信息日志，用于保存从库IO线程连接主库的连接状态、帐号、IP、端口、密码以及IO线程当前读取主库binlog的file和position等信息。默认保存在master.info文件中，如果需要保存在mysql.slave_master_info表，需要在启动前设置master-info-repository = TABLE。
relay-log.info文件或者mysql.slave_relay_log_info表：SQL线程信息日志，用于记录最新的relay log的file和position以及SQL线程当前重放的事件对应主库binlog的file和position。默认保存在relay-log.info文件中，如果需要保存在mysql.slave_relay_log_info表，需要在启动前设置relay-log-info-repository = TABLE。
设置relay_log_info_repository和master_info_repository为TABLE可以提高数据库本身或者所在主机意外终止之后crash recovery的能力，且可以保证数据一致性。

从库crash时，SQL线程可能还有一部分relay log重放延迟，另外，IO线程的位置也可能正处于一个事务的中间，并不完整，所以必须在从库上启用参数relay-log-recovery=ON，启用该参数之后，从库crash recovery时会清理掉SQL线程未重放完成的relay log，并以SQL线程的位置为准重置掉IO线程的位置重新从主库请求。
如果这两张表在MySQL启动时无法被mysqld初始化，mysqld可以继续启动但会在错误日志中写入警告信息，这种情况常见于MySQL从不支持该表的版本升级到支持该表的版本。
不要尝试手动更新slave_master_info或slave_relay_log_info表，否则后果自负。
从库中复制线程在工作时，只允许对这两张表做查询不允许修改。

2. 什么是中继日志（relay log）

中继日志与二进制日志保存的event数据类似（中继日志中包含更多信息），也是由一组包含描述数据库变更的事件数据的文件组成，这些文件名后缀带连续编号，此外，还有一个包含所有正在使用的中继日志文件名称的索引文件。

中继日志中的数据存放格式与二进制日志相同，都可以使用mysqlbinlog命令来提取数据。中继日志默认保存在datadir下，文件名格式为：host_name-relay-bin.nnnnnn，连续的中继日志文件从000001开始的连续序列号创建。使用索引文件来跟踪当前正在使用的中继日志文件，索引文件默认保存在datadir下，文件名格式为：host_name-relay-bin.index。

中继日志文件和中继日志索引文件名称可分别使用--relay-log和--relay-log-index参数选项指定值覆盖默认值，如果文件名使用默认值，则要注意主机名称不能修改，否则会报无法打开中继日志的错误，建议使用参数选项指定固定的文件名称前缀。如果已经出现了这种情况了，需要修改index文件和datadir下的中继日志文件名前缀为新的主机名，然后重启从库。

3. 何时会产生新的中继日志文件

I/O线程启动时
执行FLUSH LOGS 或 mysqladmin flush-logs 命令时
当前中继日志文件变得“太大”时，日志滚动规则如下：

* 如果max_relay_log_size>0，按照此参数指定的大小进行滚动。

* 如果max_relay_log_size=0，按照max_binlog_size指定的大小进行滚动。

SQL线程在执行完relay log之后，会自行决定何时清理掉这些已经执行完成的relay log文件，但如果使用FLUSH LOGS语句或mysqladmin flush-logs命令强制滚动中继日志时，SQL线程可能会同时清理掉已经执行完成的relay log文件。

二、复制信息记录表详解

1. slave_master_info

该表提供查询IO线程读取主库的位置信息、从库连接主库的IP、账号、端口、密码等信息。

select * from slave_master_info\G;

*************************** 1. row ***************************
       Number_of_lines: 25
       Master_log_name: mysql-bin.000292
        Master_log_pos: 194
                  Host: 192.168.2.148
             User_name: qfsys
         User_password: letsg0
                  Port: 3306
         Connect_retry: 60
           Enabled_ssl: 0
                Ssl_ca:
            Ssl_capath:
              Ssl_cert:
            Ssl_cipher:
               Ssl_key:
Ssl_verify_server_cert: 0
             Heartbeat: 5
                  Bind:
    Ignored_server_ids: 0
                  Uuid: ec123678-5e26-11e7-9d38-000c295e08a0
           Retry_count: 86400
               Ssl_crl:
           Ssl_crlpath:
 Enabled_auto_position: 0
          Channel_name:
           Tls_version:
1 row in set (0.00 sec)

表字段与show slave status输出、master.info文件中的行信息对应关系如下：

master.info中行数	slave_master_info表字段	show slave status输出字段	字段含义描述
1	Number_of_lines	[None]	表示master.info中的信息行数或者slave_master_info表中的信息字段数
2	Master_log_name	Master_Log_File	表示从库IO线程当前读取主库最新的binlog file名称
3	Master_log_pos	Read_Master_Log_Pos	表示从库IO线程当前读取主库最新的binlog position
4	Host	Master_Host	表示从库IO线程当前正连接的主库IO或者主机名
5	User_name	Master_User	表示从库IO线程用于连接主库用户名
6	User_password	[None]	表示从库IO线程用于连接主库的用户密码
7	Port	Master_Port	表示从库IO线程所连接主库的网络端口
8	Connect_retry	Connect_Retry	表示从库IO线程断线重连主库的间隔时间，单位为秒，默认值为60
9	Enabled_ssl	Master_SSL_Allowed	表示主从之间的连接是否支持SSL
10	Ssl_ca	Master_SSL_CA_File	表示CA(Certificate Authority )认证文件名
11	Ssl_capath	Master_SSL_CA_Path	表示CA(Certificate Authority )认证文件路径
12	Ssl_cert	Master_SSL_Cert	表示SSL认证证书文件名
13	Ssl_cipher	Master_SSL_Cipher	表示用于SSL连接握手中可能使用到的密码列表
14	Ssl_key	Master_SSL_Key	表示SSL认证的密钥文件名
15	Ssl_verify_server_cert	Master_SSL_Verify_Server_Cert	表示是否需要校验server的证书
16	Heartbeat	[None]	表示主从之间的复制心跳包的间隔时间，单位为秒
17	Bind	Master_Bind	表示从库可用于连接主库的网络接口，默认为空
18	Ignored_server_ids	Replicate_Ignore_Server_Ids	表示从库复制需要忽略哪些server-id，注意：这是一个列表，第一个数字表示需要忽略的实例server-id总数
19	Uuid	Master_UUID	表示主库的UUID
20	Retry_count	Master_Retry_Count	表示从库最大允许重连主库的次数
21	Ssl_crl	[None]	SSL证书撤销列表文件的路径
22	Ssl_crl_path	[None]	包含ssl证书吊销列表文件的目录路径
23	Enabled_auto_position	Auto_position	表示从库是否启用在主库中自动寻找位置的功能（使用1时启动自动寻找位置，如果使用auto_position=0，则不会自耦东找位置）
24	Channel_name	Channel_name	表示从库复制通道名称，一个通道代表一个复制源
25	Tls_Version	Master_TLS_Version	表示在Master上的TLS版本号

2. slave_relay_log_info

该表提供查询SQL线程重放的二进制文件对应的主库位置和relay log当前最新的位置。

select * from slave_relay_log_info\G;

*************************** 1. row ***************************
  Number_of_lines: 7
   Relay_log_name: /home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.000205
    Relay_log_pos: 14097976
  Master_log_name: mysql-bin.000060
   Master_log_pos: 21996812
        Sql_delay: 0
Number_of_workers: 16
               Id: 1
     Channel_name:
1 row in set (0.00 sec)

表字段与show slave status输出字段、relay-log.info文件中的行信息对应关系及其表字段含义如下：

relay-log.info文件中的行数	mysql.slave_relay_log_info表字段	show slave status命令输出字段	字段含义描述
1	Number_of_lines	[None]	表示relay-log.info中的信息行数或者slave_relay_log_info表中的信息字段数，用于版本化表定义
2	Relay_log_name	Relay_Log_File	表示当前最新的relay log文件名称
3	Relay_log_pos	Relay_Log_Pos	表示当前最新的relay log文件对应的最近一次完整接收的event的位置
4	Master_log_name	Relay_Master_Log_File	表示SQL线程当前正在重放的中继日志对应的主库binlog 文件名
5	Master_log_pos	Exec_Master_Log_Pos	表示SQL线程当前正在重放的中继日志对应主库binlog 文件中的位置
6	Sql_delay	SQL_Delay	表示延迟复制指定的从库必须延迟主库多少秒
7	Number_of_workers	[None]	表示从库当前并行复制有多少个worker线程
8	Id	[None]	用于内部唯一标记表中的每一行记录，目前总是1
9	Channel_name	Channel_name	表示从库复制通道名称，用于多源复制，一个通道对应一个主库源

3. slave_worker_info

该表提供查询多线程复制时的worker线程状态信息，与performance_schema.replication_applier_status_by_worker表的区别是：前者记录worker线程重放的relay log和主库binlog位置信息，而后者记录的是worker线程重放的GTID位置信息。

select * from slave_worker_info limit 1\G;

*************************** 1. row ***************************
                        Id: 1
            Relay_log_name:
             Relay_log_pos: 0
           Master_log_name:
            Master_log_pos: 0
 Checkpoint_relay_log_name:
  Checkpoint_relay_log_pos: 0
Checkpoint_master_log_name:
 Checkpoint_master_log_pos: 0
          Checkpoint_seqno: 0
     Checkpoint_group_size: 64
   Checkpoint_group_bitmap:
              Channel_name:
1 row in set (0.00 sec)

表字段含义：

Id：表中数据的ID（worker线程的ID）对应performance_schema.replication_applier_status_by_worker表的WORKER_ID字段。如果复制停止，该字段值仍然存在，而replication_applier_status_by_worker表中THREAD_ID字段值会清空。
Relay_log_name：每个worker线程当前最新执行到的relay log文件名。
Relay_log_pos：每个worker线程当前最新执行到的relay log文件中的position。
Master_log_name：每个worker线程当前最新执行到的主库binary log文件名。
Master_log_pos：每个worker线程当前最新执行到的主库binary log文件中的position。
Checkpoint_relay_log_name：每个worker线程最新检查点的relay log文件名。
Checkpoint_relay_log_pos：每个worker线程最新检查点的relay log文件中的position。
Checkpoint_master_log_name：每个worker线程最新检查点对应主库的binary log文件名。
Checkpoint_master_log_pos：每个worker线程最新检查点对应主库的binary log文件中的position。
Checkpoint_seqno：每个worker线程当前最新执行完成的事务号，这个事务号的大小值是相对于每个worker线程自己的最新检查点而言的，并不是真正的事务号。
Checkpoint_group_size：表示每个worker线程的执行队列大于这个字段值时，就会触发当前worker线程执行一次检查点。
Checkpoint_group_bitmap：用于从库crash之后recovery的关键值，它是一个位图值，表示每个worker线程在自己的最新检查点中已经执行的事务。
Channel_name：复制通道名称，多主复制时，显示指定的复制通道名称，单主复制时该字段为空。

该表中记录的内容对从库多线程复制crash recovery至关重要，所以下文对该表中记录的内容如何作用于crash recovery过程进行一些必要的说明。

4. 从库多线程复制

1）多线程复制如何做复制分发？

5.7中加入了基于事务的并行复制，主库在binlog的GTID事件中新加入了last_commit和sequence_number标记，用于表示在每个binlog中的每个group中的提交顺序。在每个给定的binlog中，每个group中的last_commit总是为上一个group中最大的sequence_number、为当前group中最小的sequence_number - 1。在每个binlog中，last_commit总是从0开始计数，sequence_number总是从1开始计数。
从库relay log中记录的主库binlog，不会改变主库的server id、时间戳信息以及last_commit和sequence_number值。这样，从库SQL线程在执行binlog重放时，就可以依据这些信息决定从库是否需要严格按照主库提交顺序进行提交（从库重放的事务只是分发顺序按照主库提交顺序，但是从库自己在提交这些事务时是否按照主库提交顺序进行提交，还需要看从库自己的slave_preserve_commit_order变量设置，设置为1则严格按照relay log中的顺序进行提交，设置为0从库会自行决定提交顺序）。
SQL线程并行分发原理：

* SQL协调器线程读取到一个新的事务，取出last_commit和sequence_number值。

* SQL协调器线程判断取出的新事务的当前last_commit是否大于当前已执行完成的sequence_number中的最小值（Low water mark，简称LWM，也叫低水位线标记）。

* 如果是，则说明上一个group中的事务还没有全部执行完成，此时SQL协调器线程需要等待所有的worker线程执行完成上一个group中的事务，等待LWM变大，直到当前读取到的事务的last_commit与当前已执行完成的事务的最小sequence_number值相等才可以继续分发新的事务给空闲的worker线程。

* SQL协调器线程通过统计worker线程返回的状态信息，寻找一个空闲的worker线程，如果没有空闲的线程，则SQL协调器线程需要进行等待，知道找到一个空闲的worker线程为止（如果有多个worker线程，则SQL协调器线程随机选择一个空闲的worker线程进行分发）。

* 将当前读取到的事务的binlog event分发给选定的空闲worker线程，之后worker线程会去应用这个事务，然后SQL协调器线程继续读取新的binlog event。

2）从库多线程复制的crash recovery

从前面多线程复制分发的原理我们知道，处于同一个group中的事务是并行应用的，且事务是随机分配的。在从库正常运行过程中，任意一刻，所有worker线程正在执行的事务中，哪些已经执行完成，哪些还未执行完成无法使用单个位置来确定，也就是说所有worker线程中正在执行的最大位置和最小位置之间可能有断点。
MySQL 为了解决这个问题，对worker线程的执行状态做了很多记录工作。首先，维护了一个队列 GAQ（Group Assigned Queue），当SQL协调器线程在分配某一个事务时，首先会将这个事务加入到队列，然后，才会去按照规则来寻找一个空闲的worker线程来执行，如下图：

每一个事务在分发到worker线程之后，都会分配一个编号。在事务被某个worker线程执行完成之后，它的位置信息就会被flush一次，这与5.5版本中的relay_log_info记录的原理是类似的（relay_log_info中存放了从库当前SQL线程重放的位置），但是现在是多线程，每个worker线程的执行位置不能直接存放在relay_log_info中了，relay_log_info中存放的是所有worker线程汇总之后的位置，每个worker线程独立的位置信息存放在了mysql.slave_worker_info表中，在该表中，有多少个并行复制线程，就有多少行记录（如果是多主复制，则每个复制通道都有slave_parallel_workers变量指定的记录数）。

mysql.slave_worker_info表中，Checkpoint开头的字段记录了每个worker线程的检查点相关的信息（这里与innodb存储引擎的检查点不同，但是概念相通），worker线程的检查点的作用是什么呢？

前面说了SQL协调器线程在分配事务给worker线程之前会将事务先存放到GAQ队列中，但是这个队列的长度是有限的（类似redo log大小是有限的），不可能无限制的增长下去，所以必须要在这个队列中，找到一个位置点（检查点）作为GAQ的起点位置，这个位置点之前的binlog就表示已经执行完成了。在多线程复制的执行过程中，随着每个worker线程不断应用事务的binlog，检查点在GAQ中被不断地向前推进，每个worker线程通过Checkpoint_point_bitmap字段记录自己已经执行过的事务和每个已执行事务与之对应的当时的最新检查点的相对位置，这样一来，当复制意外中断之后，重新开始复制时，就可以通过所有的worker线程记录的Checkpoint_point_bitmap字段来计算出哪些事务是已经执行过的，哪些事务是还未执行的。
查找检查点的大概过程如下：

* 在GAQ队列中，从尾部开始扫描，如果是已经执行过的事务，则直接将其从队列中删除。

* 持续扫描GAQ队列，直到找到一个未执行过的事务为止即停止扫描。

* 上述步骤中扫描动作停止前扫描到的最后一个事务被确定为检查点的最新位置，并且别标记为LWM（低水位线标记）。

* 将当前LWM这个事务对应的位置(master_log_pos和relay_log_pos位置)设置为此次检查点对应的位置。

* 通过所有的worker线程检查自己的检查点，也就是查看每个worker线程自己的Checkpoint_seqno字段值，这个字段值是每个worker线程在执行事务提交时更新的，更新的字段值为每个worker线程在做事务提交时对应的最新检查点的相对位置。

* 将本次执行检查点的位置记录到mysql.slave_relay_log_info表中，作为全局bin log应用的位置。
现在，我们来看从库crash recovery的过程：

* 首先，从mysql.slave_master_info表中找到主库的信息，从mysql.slave_relay_log_info表中找到全局最新的复制位置以及worker线程个数，从mysql.slave_worker_info表中找到每个worker线程对应的复制信息位置。

* 然后，根据mysql.slave_relay_log_info表中的位置（即全局最新的检查点位置）为准来判断所有worker线程的位置，，找出了哪些事务还没有执行之后，把这些事务串行地重新应用（应用一个更新一次mysql.slave_relay_log_info表，为什么要串行，这是为了在恢复过程中如果再次跪了，还可以正确地恢复位置），应用完成之后清空mysql.slave_worker_info表。然后启动复制线程，继续从主库拉取最新的binlog进行数据复制。

PS：如果在主从复制架构中，有2个以上的从库，且当前从库永远不做提升主库的操作时，可以使用如下方法优化从库延迟：

关闭log_slave_updates参数，减少从库binlog写入量（如果不做级联复制甚至可以同时关闭binlog）。
设置innodb_flush_log_at_trx_commit为0或者2，减少事务提交时redo log的等待频率。
设置sync_binlog为默认值或者更大的值，减少事务提交时binlog的等待频率。
设置slave_preserve_commit_order参数为OFF（默认为OFF，设置为ON时要求开启binlog和log_slave_updates参数），减少事务严格按照主库顺序提交时的提交等待时间。

5. gtid_executed

前面介绍的表中都不包括GTID信息，gtid_executed表才是GTID信息的持久表，用于存储所有事务分配的 GTID集合。GTID集合由UUID集合构成，每个UUID集合的组成为：uuid:interval[:interval]...，例如：28b13b49-3dfb-11e8-a76d-5254002a54f2:1-600401

GTID在整个复制拓扑中是全局唯一的，GTID中的事务号是一个单调递增的无间隙数字。正常情况下，客户端的数据修改在执行commit时会分配一个GTID，且会记录到binlog中，这些GTID通过复制组件在其他实例中进行重放时也会保留GTID来源不变。但是如果客户端自行使用sql_log_bin变量关闭了binlog记录或者客户端执行的是一个只读事务，那么server不会分配GTID，在binlog中也不会有GTID记录。
当某个从库接受到自己的GTID集合中已经包含的GTID时，会忽略这个已存在的GTID，不会报错，事务也不会被执行。

从MySQL 5.7.5开始，GTID存储在mysql数据库的名为gtid_executed的表中。对于每个GTID集合，默认情况下值记录每个GTID集合的起始和结束的事务号对应的GTID，该表只在数据库初始化或者执行update_grade升级的时候创建，不允许手工创建与修改。当实例本身有客户端访问数据写入或者有从其他主库通过复制插件同步数据的时候，该表中会有新的GTID记录写入，另外，该表中的记录还会在binlog滚动或者实例重启的时候被更新（日志滚动时该表需要把除了最新的binlog之外其他binlog中的所有GTID结合记录到该表中，实例重启时，需要把所有的binlog中的GTID集合记录到该表中）。

由于有mysql.gtid_executed表记录GTID，从5.7.5版本开始，在复制拓扑中的从库允许关闭binlog，也允许在binlog开启的情况下关闭log_slave_updates变量。

由于GTID必须在gtid_mode为ON或者为ON_PERMISSIVE时才会生成，自然该表中的记录也需要gtid_mode变量为ON或ON_PERMISSIVE时才会进行记录。另外，该表中是否实时存储GTID，取决于binlog日志是否开启，或者binlog启用时是否启用log_slave_updates变量，如下：

当禁用二进制日志记录（log_bin为OFF），或者启用binlog但禁用log_slave_updates，Server会在每个事务提交时把属于该事物的GTID同时更新到该表中。此时，该表的GTID周期性自动压缩功能激活，每达到gtid_executed_compression_period系统变量指定的事物数量压缩一次该表中的GTID集合(也就是把每个UUID对应的事务号的记录取一个最大值，取一个最小值，删除中间值)，要注意：周期性自动压缩功能仅针对从库，对主库无效，因为主库必须启用binlog，且log_slave_updates参数不影响主库。
如果启用二进制日志记录（log_bin为ON）且log_slave_updates参数也启用，则周期性自动压缩功能失效，该表中的记录只会在binlog日志滚动或者服务器关闭时才会进行压缩，且会把除了最后一个binlog之外，其他所有binlog中包含的GTID集合写入该表中。

注意：

如果启用二进制日志记录（log_bin为ON）且log_slave_updates参数也启用，那么该表不会实时记录GTID，也就是说，完整的GTID集合，有一部分记录在该表中，有一部分是记录在binlog中的，如果一旦server发生crash，那么在crash recovery时会读取binlog中最新的GTID集合并合并到该表中。
该表中的记录在执行reset master语句时会被清空。

# 假设表中有如下实时记录的GTID记录
mysql> SELECT * FROM mysql.gtid_executed;
+ -------------------------------------- + ---------- ------ + -------------- +
| source_uuid | interval_start | interval_end |
| -------------------------------------- + ---------- ------ + -------------- |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 37 | 37 |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 38 | 38 |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 39 | 39 |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 40 | 40 |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 41 | 41 |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 42 | 42 |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 43 | 43 |
...
# 那么，每达到gtid_executed_compression_period变量定义的事务个数时，激活压缩功能，GTID被压缩为一行记录，如下
+ -------------------------------------- + ---------- ------ + -------------- +
| source_uuid | interval_start | interval_end |
| -------------------------------------- + ---------- ------ + -------------- |
| 3E11FA47-71CA-11E1-9E33-C80AA9429562 | 37 | 43 |
...
# 注意：当gtid_executed_compression_period系统变量设置为0时，周期性自动压缩功能失效，你需要预防该表被撑爆的风险

表字段含义。

source_uuid：代表数据来源的GTID集合。
interval_start：每个UUID集合的最小事务号。
interval_end：每个UUID集合的最大事务号。

对该表的压缩功能由名为 thread/sql/compress_gtid_table 的专用前台线程执行。该线程使用SHOW PROCESSLIST无法查看，但它可以在performance_schema.threads表中查看到(线程 thread/sql/compress_gtid_table 大多数时候都处于休眠状态，直到每满gtid_executed_compression_period个事务之后，该线程被唤醒以执行前面所述的对mysql.gtid_executed表的压缩。然后继续进入睡眠状态，直到下一次满gtid_executed_compression_period个事务，然后被唤醒再次执行压缩，以此类推，无限重复此循环。但如果当关闭binlog或者启用binlog但关闭log_slave_updates变量时，gtid_executed_compression_period变量被设置为了0，那么意味着该线程会始终处于休眠状态且永不会唤醒)，如下所示：

SELECT * FROM performance_schema.threads WHERE NAME LIKE '%gtid%'\G;

*************************** 1. row ***************************
          THREAD_ID: 26
               NAME: thread/sql/compress_gtid_table
               TYPE: FOREGROUND
     PROCESSLIST_ID: 1
   PROCESSLIST_USER: NULL
   PROCESSLIST_HOST: NULL
     PROCESSLIST_DB: NULL
PROCESSLIST_COMMAND: Daemon
   PROCESSLIST_TIME: 1509
  PROCESSLIST_STATE: Suspending
   PROCESSLIST_INFO: NULL
   PARENT_THREAD_ID: 1
               ROLE: NULL
       INSTRUMENTED: YES
            HISTORY: YES
    CONNECTION_TYPE: NULL
       THREAD_OS_ID: 18677

三、日志记录表

1. 日志信息概述

MySQL的日志系统包含：general query log、slow query log、error log、binary log、relay log、DDL log。在MySQL 5.7中，只有general query log、slow query log支持写入到表中（也支持写入到文件中），所以，下文中对于日志系统表主要介绍 general query log、slow query log表。

默认情况下：

除Windows上的错误日志之外，其他平台的所有日志默认情况下不启用（DDL日志只在需要时创建，并且无用户可配置选项）。
所有日志均写在datadir目录下磁盘文件，可以使用每种日志对应的路径参数自行更改路径。general query log和slow query log可以通过log_output=TABLE设置保存到表mysql.general_log和mysql.slow_log表中。DDL log在8.0中可以配置，可打印到错误日志中，也可以保存在表innodb_ddl_log中。
binary log根据max_binlog_size参数设置的大小自动滚动、relay log根据max_relay_log_size或者max_binlog_size自动滚动(如果max_relay_log_size没设置就按照max_binlog_size大小滚动)，其他的日志类型不会滚动，总是使用同一个文件，需要自行做切割。

2. 日志表特征

通常，日志表的主要目的是为程序提供一个访问接口，以便查看SQL运行情况，所以，日志记录存放在表中比存放在磁盘文件中会更加方便。
日志表可以使用CREATE TABLE，ALTER TABLE和DROP TABLE语句，但前提是需要先使用对应的开关关闭，不能在使用期间操作（例如：set global general_log=0，然后操作general_log表）。
general_log和slow_log表默认是CSV引擎，使用逗号分割的格式来存放日志记录。日志表可以修改引擎为MyISAM，修改之前必须先停止表的使用。合法的引擎为CSV和MyISAM，其他引擎不支持。
要禁用日志记录表以便进行相应的DDL语句操作，可以使用以下步骤（以慢查询表为例进行说明，slow_log和general_log表操作方式类似）。

注意事项

可以使用CHECK TABLE语句。
不能使用LOCK TABLE语句。
不能使用dml语句，日志表的记录变更由Server内部维护，不能手动操作。
FLUSH TABLES WITH READ LOCK和read_only系统变量的设置对日志表没有影响，Server内部始终可以写日志表。
日志表的数据变更操作不会记录到binlog，因此不会被复制到从库。
可以使用FLUSH TABLES或FLUSH LOGS语句来刷新日志表或日志文件。
日志表不支持分区表。

MySQL的查询日志、错误日志等是使用明文记录的，所以，这些日志中有可能会记录用户的明文密码信息，可以使用rewrite插件来使用原始格式记录，详见链接：

https://dev.mysql.com/doc/refman/5.7/en/plugin-types.html#query-rewrite-plugin-type
https://dev.mysql.com/doc/refman/5.7/en/rewriter-query-rewrite- plugin.html

四、日志表详解

1. general_log

提供查询普通SQL语句的执行记录信息，用于查找客户端到底在服务端上执行了什么SQL（当然，还可以使用企业版的audit log审计插件记录）。该表中的信息在SQL开始执行时就会进行记录，而不是等待SQL执行结束才记录。下面是该表中存储的信息内容。

root@localhost : (none) 07:25:50> set global log_output='TABLE';
Query OK, 0 rows affected (0.00 sec)

root@localhost : (none) 07:26:20> set global general_log=1;
Query OK, 0 rows affected (0.01 sec)

root@localhost : (none) 07:26:32> select * from mysql.general_log;
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| event_time | user_host | thread_id | server_id | command_type | argument |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
| 2018-06-19 19:26:32.891371 | root[root] @ localhost [] | 3 | 3306102 | Query | show databases |
| 2018-06-19 19:26:42.012064 | root[root] @ localhost [] | 3 | 3306102 | Query | select * from mysql.general_log |
+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+
2 rows in set (0.00 sec)

root@localhost : (none) 07:26:42> select connection_id();
+-----------------+
| connection_id() |
+-----------------+
| 3 |
+-----------------+
1 row in set (0.00 sec)

event_time：查询日志记录到表的那一刻的log_timestamps系统变量值，用于标记查询日志记录何时入库。
user_host：表示该查询日志记录的来源，其中有用户名和主机名信息。
thread_id：表示该查询日志记录执行时的process_id。
server_id：表示执行该查询的数据库实例ID。
command_type：表示该查询的command类型，通常都为query。
argument：表示执行查询的SQL语句文本。

mysqld按照接收请求的顺序将语句写入查询日志中（可能与它们的执行顺序不同），在主从复制架构中：

主库上在使用基于语句的日志格式时，从库在在重放这些语句之后，会把这些语句记录自己的查询日志中。使用mysqlbinlog命令解析之后导入数据库中时，这些解析语句也会被记录到查询日志中。
主库上使用基于row日志格式时，从库重放这些数据变更之后，这些语句不会被记入从库的查询日志中。
在主库上使用基于mixed日志格式时，如果主库是以语句格式记录的，同情况1，否则同情况2。

查询日志可以使用系统变量sql_log_off变量动态关闭当前会话或者所有会话的查询日志记录功能（与sql_log_bin系统变量的作用类似）。

如果启用了查询日志，则Server重新启动的时候会重新打开查询日志文件，如果查询日志存在，则直接重新打开，如果查询日志不存在，则重新创建，如果需要再Server运行时动态归档查询日志，则可以按照如下命令操作）。

shell> mv host_name.log host_name-old.log
shell> mysqladmin flush-logs
shell> mv host_name-old.log backup-directory

也可以在Server运行时通过语句先关闭查询日志功能，然后使用外部命令来归档，然后再重新启用查询日志，这样就不需要使用flush-logs命令来刷新日志文件了，此方法适用于任何平台，命令如下：

SET GLOBAL general_log ='OFF';
# 在禁用日志的情况下，从外部重命名日志文件;例如，从命令行。然后再次启用日志：SET GLOBAL general_log ='ON';# 此方法适用于任何平台，不需要重新启动服务器。

默认情况下，在Server中执行的语句如果带了用户密码，会被Server重写该语句之后再写入到查询日志中。如果需要记录明文密码，则需要使用--low-raw选项启动Server。

如果带密码的语句中，指定了密码是一个hash值时，则密码字符串不会被重写，例如：CREATE USER 'user1'@'localhost' IDENTIFIED BY PASSWORD 'not-so-secret';，但是如果去掉PASSWORD关键字CREATE USER 'user1'@'localhost' IDENTIFIED BY 'not-so-secret';，则在查询日志中就会被重写为：CREATE USER 'user1'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS ''。
一些语法错误的SQL默认情况下也不会被记录到查询日志中，使用--low-raw选项启动Server会记录所有的原始SQL语句。
查询日志表中的时间戳信息来源于系统变量log_timestamps（包括慢查询日志文件和错误日志文件中的时间戳都来自此系统变量的值），该时间戳值在查询时可以使用CONVERT_TZ（）函数或通过设置会话将从这些表中的时间戳信息从本地系统时区转换为任何所需时区（修改会话级别的time_zone变量值）。

2. slow_log

该表提供查询执行时间超过long_query_time设置值的SQL，或者未使用索引的（需要开启参数log_queries_not_using_indexes=ON）或者管理语句（需要开启参数log_slow_admin_statements=ON）。

root@localhost : test 08:46:04> set global long_query_time=0;
Query OK, 0 rows affected (0.01 sec)

root@localhost : test 08:55:14> set global slow_query_log=1;
Query OK, 0 rows affected (0.01 sec)

# 断开会话重新连接
root@localhost : (none) 08:56:12> use test
Database changed

root@localhost : test 08:56:13> show tables;
+----------------+
| Tables_in_test |
+----------------+
| customer |
| product |
| shares |
| test |
| transreq |
+----------------+

5 rows in set (0.01 sec)

root@localhost : test 08:56:16> select * from test;
+---+---+------+------+------+------+
| a | b | c | d | e | f |
+---+---+------+------+------+------+
| 1 | 1 | 1 | 1 | 1 | 1 |
| 2 | 2 | 2 | 2 | 2 | 2 |
| 3 | 3 | 3 | 3 | 3 | 3 |
| 4 | 4 | 4 | 4 | 4 | 4 |
| 5 | 5 | 4 | 4 | 5 | 5 |
+---+---+------+------+------+------+
5 rows in set (0.01 sec)

root@localhost : test 08:56:18> select * from mysql.slow_log;
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+------+----------------+-----------+-----------+----------------------------------+-----------+
| start_time | user_host | query_time | lock_time | rows_sent | rows_examined | db | last_insert_id | insert_id | server_id | sql_text | thread_id |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+------+----------------+-----------+-----------+----------------------------------+-----------+
| 2018-06-19 20:56:12.254716 | root[root] @ localhost [] | 00:00:00.000286 | 00:00:00.000000 | 1 | 0 | | 0 | 0 | 3306102 | select @@version_comment limit 1 | 4 |
| 2018-06-19 20:56:12.258551 | root[root] @ localhost [] | 00:00:00.000153 | 00:00:00.000000 | 1 | 0 | | 0 | 0 | 3306102 | select USER() | 4 |
| 2018-06-19 20:56:13.975382 | root[root] @ localhost [] | 00:00:00.000247 | 00:00:00.000000 | 1 | 0 | | 0 | 0 | 3306102 | SELECT DATABASE() | 4 |
| 2018-06-19 20:56:13.975627 | root[root] @ localhost [] | 00:00:00.000095 | 00:00:00.000000 | 1 | 0 | test | 0 | 0 | 3306102 | Init DB | 4 |
| 2018-06-19 20:56:16.277207 | root[root] @ localhost [] | 00:00:00.000490 | 00:00:00.000264 | 5 | 5 | test | 0 | 0 | 3306102 | show tables | 4 |
| 2018-06-19 20:56:18.936831 | root[root] @ localhost [] | 00:00:00.000694 | 00:00:00.000400 | 5 | 5 | test | 0 | 0 | 3306102 | select * from test | 4 |
+----------------------------+---------------------------+-----------------+-----------------+-----------+---------------+------+----------------+-----------+-----------+----------------------------------+-----------+

6 rows in set (0.00 sec)

start_time：慢查询日志记录到表时的log_timestamps系统变量值。
user_host：带用户名和主机名（IP）格式的值，用于标记访问来源。
query_time：慢查询语句总的执行时间。
lock_time：慢查询语句持有锁的时间。
rows_sent：慢查询语句最终返回给客户端的数据记录数。
rows_examined：慢查询语句在存储引擎中的检查记录数。
db：慢查询语句执行时的默认库名。
last_insert_id：通常为0。
insert_id：通常为0。
server_id：产生慢查询语句的server id。
sql_text：慢查询日志的语句文本。
thread_id：产生慢查询日志的线程process_id。

慢查询日志包含了执行时间超过long_query_time系统变量设置的秒数的SQL语句，并且包含了需要检查行数超过min_examined_row_limit系统变量设置的值的SQL语句（默认情况下该变量为0，表示不限制检查行数）。long_query_time的最小值和默认值分别为0和10（单位秒）。该值可以指定为微秒，但微秒单位只对记录到文件有效。

慢查询日志中语句获取初始锁的时间不计入执行时间，包含时间范围为：获取锁之后，并在语句执行完成之后，将锁释放之前，将慢查询语句写入慢查询日志中。所以，在慢查询日志中记录的顺序可能与MySQL Server接收到的语句顺序（执行顺序）并不相同，因为可能有的先执行的语句最后才释放完所有的锁，有的后执行的语句先释放完所有的锁。

如果mysqld在启动是使用了--log-short-format选项，则MySQL Server会将较少的慢查询信息写入慢查询日志中。

MySQL Server按照以下顺序来判断语句是否需要计入慢查询：

判断参数 log_slow_admin_statements是否启用，如果启用，则判断语句是否是管理语句，是则计入慢查询，不是则进入下一轮判断。如果参数未启用，则进入下一步判断。
判断查询语句执行时间是否超过long_query_time秒，超过则计入慢查询，未超过则判断log_queries_not_using_indexes 参数是否启用，如果启用该参数且该语句未使用索引，则计入慢查询，否则进入下一步判断。
如果min_examined_row_limit变量设置非零值，则判断语句的检查行数是否超过该变量设置的值，如果超过则计入慢查询，如果未超过则不记录慢查询。

慢查询日志记录的时间戳由log_timestamps系统变量控制。默认情况下，复制架构中的从库不会将重放binlog产生的慢查询写入自己的慢速查询日志中，如果需要记录从库重放binlog的慢查询语句计入慢查询日志，需要启用变量log_slow_slave_statements=1。

参考

https://dev.mysql.com/doc/refman/5.7/en/replication-gtids-concepts.html#replication-gtids-gtid-executed-table

MySQL :: MySQL 5.7 Reference Manual :: 5.1.13 MySQL Server Time Zone Support

MySQL :: MySQL 5.7 Reference Manual :: 15.8.2.2 Creating a FEDERATED Table Using CREATE SERVER

MySQL :: MySQL 5.7 Reference Manual :: 5.4 MySQL Server Logs

复制信息记录表|全方位认识 mysql 系统库_ITPUB博客

时区信息记录表|全方位认识 mysql 系统库_ITPUB博客

【沃趣科技】日志信息记录表|全方位认识 mysql 系统库_ITPUB博客

Hehuyi_In

关注

0
点赞
踩
3

收藏

觉得还不错? 一键收藏
打赏
1
评论
mysql 系统库（四） —— 复制信息、日志记录表

一、时区信息记录表01 时区信息概述MySQL服务器维护几个时区设置：系统时区：当Server启动时，尝试确定主机的时区并使用它来设置Server的system_time_zone系统变量值。该变量为只读变量，此外，您还可以在Server启动时使用--timezone = timezone_name选项为mysqld_safe设置MySQL服务器的系统时区。或者在Server启动...
复制链接

扫一扫