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

￡神魔★判官ぃ 2023-06-14 10:37 19阅读 0赞

沃趣科技作为国内领先的数据库云平台解决方案提供商，一直致力于企业级数据库云平台产品的研发，为用户提供高性能、高可用、可扩展的的数据库云环境及不同业务场景需求的数据库平台，满足客户对极致性能、数据安全、容灾备份、业务永续等需求。沃趣科技凭借专业的团队，优质的产品，前沿的技术，贴心的服务赢得了客户的信任与尊重，也获得了市场的认同。

--------------------

在上一期《复制信息记录表|全方位认识 mysql 系统库》中，我们详细介绍了mysql系统库中的复制信息记录表，本期我们将为大家带来系列第八篇《日志记录等混杂表|全方位认识 mysql 系统库》，下面请跟随我们一起开始 mysql 系统库的系统学习之旅吧！

**一、日志信息记录表**

**1.1. 日志信息概述**

MySQL的日志系统包含：general query log、slow query log、error log（记录MySQL Server启动时、运行中、停止时的错误信息）、binary log(记录MySQL Server运行过程中的数据变更的逻辑日志)、relay log（记录从库IO线程从主库获取的主库数据变更日志）、DDL log(记录DDL语句执行时的元数据变更信息。5.7中只支持写入到文件，8.0中支持写入到innodb\_ddl\_log表中，注意，ddl log与online ddl的alter log不同，不要搞混了)，其中，在MySQL 5.7中，只有general query log、slow query log支持写入到表中（也支持写入到文件中），其他日志类型在MySQL 5.7版本中只支持写入到文件中，所以，下文中对于日志系统表主要介绍 general query log、slow query log表。

默认情况下，除Windows上的错误日志之外，其他平台的所有日志默认情况下不启用 （DDL日志只在需要时创建，并且无用户可配置选项）。

默认情况下，所有日志均写在datadir目录下，但可以使用每种日志对应的路径参数自行更改路径。

*  general query log：general\_log\_file=/home/mysql/data/mysqldata1/mydata/localhost.log
 *  error log：log\_error=/home/mysql/data/mysqldata1/log/error.log
 *  slow query log：slow\_query\_log\_file=/home/mysql/data/mysqldata1/slowlog/slow-query.log
 *  binary log：log\_bin\_basename=/home/mysql/data/mysqldata1/binlog/mysql-bin、log\_bin\_index=/home/mysql/data/mysqldata1/binlog/mysql-bin.index
 *  relay log：relay\_log\_basename=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin、relay\_log\_index=/home/mysql/data/mysqldata1/relaylog/mysql-relay-bin.index

默认情况下，所有的日志都写入到磁盘文件，但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大小滚动)，其他的日志类型不会滚动，总是使用同一个文件，所以其他日志类型增长过大之后，需要自行做切割。

*  一般使用mv file file.bak;然后执行刷新命令，刷新命令可以登录实例使用flush logs命令刷新重新产生新的日志文件，但是该命令是刷新所有的日志类型，要针对具体的日志类型，可以使用：flush binary logs;刷新二进制日志、flush error logs;刷新错误日志、flush general logs;刷新普通查询日志、flush slow logs;刷新慢查询日志、flush relay logs;刷新中继日志、flush engine logs;刷新存储引擎相关的任何可刷新的日志。
 *  也可以使用Server的flush tables;语句或者flush table with read lock;语句。
 *  刷新操作也可以使用一些命令行工具的选项实现，例如：使用mysqladmin命令的flush-logs选项，或者mysqldump的flush-logs选项与--master-data选项。

日志表实现具有以下特征：

通常，日志表的主要目的是为程序提供一个访问接口，以便查看Server内的SQL运行情况，所以，日志记录存放在表中比存放在磁盘文件中会更加方便，因为存储在表中可以远程访问这些日志记录，而不需要登录操作系统去访问磁盘文件。

日志表可以使用CREATE TABLE，ALTER TABLE和DROP TABLE语句，但前提是需要先使用对应的开关关闭掉表，不能在使用期间操作（例如：set global general\_log=0，然后操作general\_log表）。

general\_log和slow\_log表默认是CSV引擎，使用逗号分割的格式来存放日志记录，CSV数据文件可以很方便地导入其他程序进行处理，例如：excel电子表格。

日志表可以修改引擎为MyISAM，但修改之前必须先停止表的使用。合法的引擎为CSV和MyISAM，其他引擎不支持。

要禁用日志记录表以便进行相应的DDL语句操作，可以使用以下步骤（以慢查询表为例进行说明，slow\_log和general\_log表操作方式类似）。

SET @old_log_state = @@ global.general_log;
    SET GLOBAL general_log ='OFF';
    ALTER TABLE mysql.general_log ENGINE = MyISAM;
    SET GLOBAL general_log = @old_log_state;

可以使用TRUNCATE TABLE来清空日志记录。

可以使用RENAME TABLE来实现日志表的归档，新旧表做一个原子的名称互换操作，如下：

use mysql;
    DROP TABLE IF EXISTS general_log2;
    CREATE TABLE general_log2 LIKE general_log;
    RENAME TABLE general_log TO general_log_backup，general_log2 TO general_log;

注意事项

*  可以使用CHECK TABLE语句。
 *  不能使用LOCK TABLE语句。
 *  不能使用INSERT，DELETE和UPDATE语句，日志表的记录变更由Server内部维护，不能手动操作。
 *  FLUSH TABLES WITH READ LOCK和read\_only系统变量的设置对日志表没有影响。Server内部始终可以写日志表。
 *  日志表的数据变更操作不会记录到binlog，因此不会被复制到从库。
 *  可以使用FLUSH TABLES或FLUSH LOGS语句来刷新日志表或日志文件。
 *  日志表不支持分区表。
 *  mysqldump转储包含了重新创建这些表的语句，以便在重新加载转储文件后恢复日志表结构，但是日志表中的记录内容不会被转储。

PS：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.2. 日志表详解**

**1.2.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按照接收请求的顺序将语句写入查询日志中(这可能与它们的执行顺序不同）。

在主从复制架构中。

*  主库上在使用基于语句的日志格式时，从库在在重放这些语句之后，会把这些语句记录自己的查询日志中（需要从库启用了查询日志记录功能），使用语句格式记录的binlog在使用mysqlbinlog命令解析之后导入数据库中时，如果实例开启了查询日志记录功能，则这些解析语句也会被记录到查询日志中。
 *  主库上使用基于row日志格式时，从库重放这些数据变更之后，这些语句不会被计入从库的查询日志中。
 *  在主库上使用基于mixed日志格式时，如果主库是以语句格式记录的，则从库重放这些数据变更之后会把语句记录到自己的查询日志中（需要从库启用了查询日志记录功能），如果主库在记录binlog时被转换为了row格式，则也跟row格式复制一样，从库重放这些数据变更之后不会把这些语句记录到自己的查询日志中。

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

查询日志开关general\_log变量和查询磁盘日志文件路径general\_log\_file变量都可以动态修改（如果已经有查询日志处于打开状态，则使用general\_log\_file变量修改查询日志路径时关闭旧的查询日志，打开新的查询日志），当启用查询日志时，查询日志将保持到系统变量log\_output指定的目的地。

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

shell> mv host_name.log host_name-old.log
    shell> mysqladmin flush-logs
    shell> mv host_name-old.log backup-directory
    # 在Windows上，请直接使用重命名，而不是mv命令

也可以在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变量值）。

**1.2.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（单位秒）。该值可以指定为微秒（使用小数），但微秒单位只对记录到文件有效。对于记录到表中的慢查询语句，不支持微秒，微秒部分被忽略。

默认情况下，慢查询日志不会记录管理语句，也不会记录未使用索引的语句，但可以使用log\_slow\_admin\_statements和log\_queries\_not\_using\_indexes系统变量更改默认行为，使MySQL Server把管理语句和未使用索引的语句也一并计入慢查询日志。

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

默认情况下，慢查询日志不启用。要启用可以使用--slow\_query\_log =1进行设置，要指定慢查询日志文件名称，可以使用--slow\_query\_log\_file = file\_name进行设置，要指定慢查询日志输出目标，可以使用--log-output=FILE|TABLE|NONE 进行设置。

*  如果启用了慢查询日志记录功能，但是未指定名称，则默认在datadir下命名为host\_name-slow.log，如果使用--log-output=TABLE 设置了报错在表中，则slow\_query\_log\_file = file\_name 设置的路径无效。
 *  要动态修改慢查询日志文件名称，可以使用slow\_query\_log=0先关闭慢查询日志文件，然后使用slow\_query\_log\_file=new\_file\_name指定新的慢查询日志文件名，然后使用slow\_query\_log=1重新启用慢查询日志日志文件。
 *  如果mysqld在启动是使用了--log-short-format选项，则MySQL Server会将较少的慢查询信息写入慢查询日志中。

如果使用了log\_slow\_admin\_statements=1 设置，则MySQL Server会在慢查询日志中记录如下管理语句：

*  ALTER TABLE，ANALYZE TABLE，CHECK TABLE，CREATE INDEX，DROP INDEX，OPTIMIZE TABLE和REPAIR TABLE

如果使用了log\_queries\_not\_using\_indexes=1 设置，则MySQL Server会把任何不使用索引的查询语句记录到慢查询日志中。

*  当记录这些查询语句时，慢查询日志可能会迅速增长。此时可以通过设置log\_throttle\_queries\_not\_using\_indexes系统变量来限制这些未使用索引的语句计入慢查询日志的速率（注意：该变量限制的是60秒内的未使用索引的语句数量，不是限制时间）。默认情况下，这个变量是0，表示没有速率限制。当启用限制时，第一个不使用索引的查询执行之后，将打开一个60秒的时间窗口，在该窗口内，将禁止其他未使用索引的查询记录到慢查询日志中，等待时间窗口结束之后，Server记录一个摘要信息，表示有多少次以及在这些执行次数总的花费时间。然后进入下一个60秒的窗口。

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。

写入慢查询日志的语句中的密码被服务器重写，不会以纯文本形式出现。如果需要记录原始语句，需要使用--log-raw选项。

**二、混杂表**

由于本系列不介绍企业版认证插件的audit\_log\_filter, audit\_log\_user表、防火墙插件的firewall\_users, firewall\_whitelis表，所以只剩下一个servers混杂表的篇幅不足够另起一期，所有我们强塞到本期里，主要是federated引擎使用的信息，如无兴趣可直接跳过本期后续内容。

**2.1. servers**

该表提供查询连接组合信息(远程实例的IP、端口、帐号、密码、数据库名称等信息，详见后续示例)，这些连接组合信息通常用于federated引擎（当然也可以作为在数据库中保存连接组合的一种方式，维护也较为方便），该表中的信息需要使用create server方式创建。

在介绍别字段含义之前，先看看dederated引擎的两种创建方式。

# 使用create server方式创建的连接组合
    Syntax:
    CREATE SERVER server_name
        FOREIGN DATA WRAPPER wrapper_name
        OPTIONS (option [, option] ...)
    option:
      { HOST character-literal
      | DATABASE character-literal
      | USER character-literal
      | PASSWORD character-literal
      | SOCKET character-literal
      | OWNER character-literal
      | PORT numeric-literal }
    # 直接使用CONNECTION选项指定完整的连接组合
    CONNECTION=scheme://user_name[:password]@host_name[:port_num]/db_name/tbl_name

下面是该表中存储的信息内容。

root@localhost Tue Jun 5 01:12:05 2018 01:12:05 [(none)]>CREATE SERVER fedlink_ip
        -> FOREIGN DATA WRAPPER mysql
        -> OPTIONS (USER 'test',PASSWORD 'test', HOST '127.0.0.1', PORT 3306, DATABASE 'test_table',Owner 'test_table1');
    Query OK, 1 row affected (0.00 sec)
    root@localhost Tue Jun 5 01:12:10 2018 01:12:10 [(none)]>CREATE SERVER fedlink_socket
        -> FOREIGN DATA WRAPPER mysql
        -> OPTIONS (USER 'test',PASSWORD 'test', SOCKET '/data/mysql/mysql3306/data/mysql.sock', PORT 3306, DATABASE 'test_table',Owner 'test_table2');
    Query OK, 1 row affected (0.00 sec)
    root@localhost Tue Jun 5 01:12:10 2018 01:12:10 [(none)]>CREATE SERVER fedlink_socket_ip
        -> FOREIGN DATA WRAPPER mysql
        -> OPTIONS (USER 'test',PASSWORD 'test', HOST '127.0.0.1',SOCKET '/data/mysql/mysql3306/data/mysql.sock', PORT 3306, DATABASE 'test_table',Owner 'test_table3');
    Query OK, 1 row affected (0.00 sec)
    root@localhost Tue Jun 5 01:12:10 2018 01:12:10 [(none)]>select * from mysql.servers;
    +-------------------+-----------+------------+----------+----------+------+---------------------------------------+---------+-------------+
    | Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner |
    +-------------------+-----------+------------+----------+----------+------+---------------------------------------+---------+-------------+
    | fedlink_socket_ip | 127.0.0.1 | test_table | test | test | 3306 | /data/mysql/mysql3306/data/mysql.sock | mysql | test_table3 |
    | fedlink_socket | | test_table | test | test | 3306 | /data/mysql/mysql3306/data/mysql.sock | mysql | test_table2 |
    | fedlink_ip | 127.0.0.1 | test_table | test | test | 3306 | | mysql | test_table1 |
    +-------------------+-----------+------------+----------+----------+------+---------------------------------------+---------+-------------+
    3 rows in set (0.00 sec)
    # 如果要删除连接组合记录，可以使用如下语句
    root@localhost Tue Jun 5 01:10:41 2018 01:10:41 [(none)]>drop SERVER fedlink;
    Query OK, 1 row affected (0.00 sec)
    root@localhost Tue Jun 5 01:11:30 2018 01:11:30 [(none)]>drop SERVER fedlink_socket ;
    Query OK, 1 row affected (0.00 sec)
    root@localhost Tue Jun 5 01:11:55 2018 01:11:55 [(none)]>drop SERVER fedlink_socket_ip;
    Query OK, 1 row affected (0.00 sec)

federated引擎的两种使用方式读写远程实例数据示例。

# 创建远程实例用户
    root@localhost Tue Jun 5 00:23:45 2018 00:23:45 [(none)]>grant all on *.* to test@'%' identified by 'test';
    Query OK, 0 rows affected (0.00 sec)
    # 创建用于存放远程实例表的库
    root@localhost Tue Jun 5 00:24:06 2018 00:24:06 [(none)]>create database test_table;
    Query OK, 1 row affected (0.00 sec)
    root@localhost Tue Jun 5 00:30:50 2018 00:30:50 [(none)]>use test_table
    Database changed
    # 创建远程实例表test_table1和test_table2
        root@localhost Tue Jun 5 00:31:03 2018 00:31:03 [test_table]>CREATE TABLE test_table1 (
        -> id INT(20) NOT NULL AUTO_INCREMENT,
        -> name VARCHAR(32) NOT NULL DEFAULT '',
        -> other INT(20) NOT NULL DEFAULT '0',
        -> PRIMARY KEY (id),
        -> INDEX name (name),
        -> INDEX other_key (other)
        -> );
    Query OK, 0 rows affected (0.06 sec)
    root@localhost Tue Jun 5 00:31:09 2018 00:31:09 [test_table]>CREATE TABLE test_table2 (
        -> id INT(20) NOT NULL AUTO_INCREMENT,
        -> name VARCHAR(32) NOT NULL DEFAULT '',
        -> other INT(20) NOT NULL DEFAULT '0',
        -> PRIMARY KEY (id),
        -> INDEX name (name),
        -> INDEX other_key (other)
        -> );
    Query OK, 0 rows affected (0.00 sec)
    # 创建存放federated引擎表的库
    root@localhost Tue Jun 5 00:31:16 2018 00:31:16 [test_table]>create database federated;
    Query OK, 1 row affected (0.00 sec)
    root@localhost Tue Jun 5 00:31:22 2018 00:31:22 [test_table]>use federated
    Database changed
    # 使用create server方式创建一个连接字符串组合，该记录会保存到mysql.servers表中
    root@localhost Tue Jun 5 00:31:25 2018 00:31:25 [federated]>CREATE SERVER fedlink
        -> FOREIGN DATA WRAPPER mysql
        -> OPTIONS (USER 'test',PASSWORD 'test', HOST '127.0.0.1', PORT 3306, DATABASE 'test_table');
    Query OK, 1 row affected (0.03 sec)
    # 查看mysql.servers表中的记录
    root@localhost Tue Jun 5 00:31:37 2018 00:31:37 [federated]>select * from mysql.servers;
    +-------------+-----------+------------+----------+----------+------+--------+---------+-------+
    | Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner |
    +-------------+-----------+------------+----------+----------+------+--------+---------+-------+
    | fedlink | 127.0.0.1 | test_table | test | test | 3306 | | mysql | |
    +-------------+-----------+------------+----------+----------+------+--------+---------+-------+
    1 row in set (0.00 sec)
    # 使用create server连接字符串组合方式，创建federated引擎表
    root@localhost Tue Jun 5 00:32:12 2018 00:32:12 [federated]>CREATE TABLE federated1 (
        -> id INT(20) NOT NULL AUTO_INCREMENT,
        -> name VARCHAR(32) NOT NULL DEFAULT '',
        -> other INT(20) NOT NULL DEFAULT '0',
        -> PRIMARY KEY (id),
        -> INDEX name (name),
        -> INDEX other_key (other)
        -> )
        -> ENGINE=FEDERATED
        -> CONNECTION='fedlink/test_table1';
    Query OK, 0 rows affected (0.04 sec)
    root@localhost Tue Jun 5 00:32:17 2018 00:32:17 [federated]>show create table federated1;
    ...
    | Table | Create Table |
    ...
    | federated1 | CREATE TABLE `federated1` (
      `id` int(20) NOT NULL AUTO_INCREMENT,
      `name` varchar(32) NOT NULL DEFAULT '',
      `other` int(20) NOT NULL DEFAULT '0',
      PRIMARY KEY (`id`),
      KEY `name` (`name`),
      KEY `other_key` (`other`)
    ) ENGINE=FEDERATED DEFAULT CHARSET=utf8 CONNECTION='fedlink/test_table1' |
    ...
    1 row in set (0.00 sec)
    # 往federated引擎表federated1中插入数据，然后可以在federated引擎表和远程实例表中都查询到相同的数据
    root@localhost Tue Jun 5 00:32:58 2018 00:32:58 [federated]>insert into federated1(name) values('federated1');
    Query OK, 1 row affected (0.00 sec)
    root@localhost Tue Jun 5 00:33:42 2018 00:33:42 [federated]>select * from federated1;
    +----+------------+-------+
    | id | name | other |
    +----+------------+-------+
    | 1 | federated1 | 0 |
    +----+------------+-------+
    1 row in set (0.00 sec)
    root@localhost Tue Jun 5 00:33:49 2018 00:33:49 [federated]>select * from test_table.test_table1;
    +----+------------+-------+
    | id | name | other |
    +----+------------+-------+
    | 1 | federated1 | 0 |
    +----+------------+-------+
    1 row in set (0.00 sec)
    # 使用CONNECTION方式完整的连接字符串创建federated引擎表
    root@localhost Tue Jun 5 00:32:32 2018 00:32:32 [federated]>CREATE TABLE federated2 (
        -> id INT(20) NOT NULL AUTO_INCREMENT,
        -> name VARCHAR(32) NOT NULL DEFAULT '',
        -> other INT(20) NOT NULL DEFAULT '0',
        -> PRIMARY KEY (id),
        -> INDEX name (name),
        -> INDEX other_key (other)
        -> )
        -> ENGINE=FEDERATED
        -> CONNECTION='mysql://test:test@127.0.0.1:3306/test_table/test_table2';
    Query OK, 0 rows affected (0.00 sec)
    # 往federated引擎表federated2中插入数据，然后可以在federated引擎表和远程实例表中都查询到相同的数据
    root@localhost Tue Jun 5 00:34:08 2018 00:34:08 [federated]>insert into federated2(name) values('federated2');
    Query OK, 1 row affected (0.00 sec)
    root@localhost Tue Jun 5 00:34:16 2018 00:34:16 [federated]>select * from test_table.test_table2;
    +----+------------+-------+
    | id | name | other |
    +----+------------+-------+
    | 1 | federated2 | 0 |
    +----+------------+-------+
    1 row in set (0.00 sec)
    root@localhost Tue Jun 5 00:34:22 2018 00:34:22 [federated]>select * from federated2;
    +----+------------+-------+
    | id | name | other |
    +----+------------+-------+
    | 1 | federated2 | 0 |
    +----+------------+-------+
    1 row in set (0.00 sec)
    root@localhost Tue Jun 5 00:34:28 2018 00:34:28 [federated]>select * from mysql.servers;
    +-------------+-----------+------------+----------+----------+------+--------+---------+-------+
    | Server_name | Host | Db | Username | Password | Port | Socket | Wrapper | Owner |
    +-------------+-----------+------------+----------+----------+------+--------+---------+-------+
    | fedlink | 127.0.0.1 | test_table | test | test | 3306 | | mysql | |
    +-------------+-----------+------------+----------+----------+------+--------+---------+-------+
    1 row in set (0.00 sec)
    # 使用socket方式类似，如果使用socket时，create server连接组合创建方式参照"表记录内容示例"

表字段含义。

*  Server\_name：连接组合唯一标识（即名称，使用drop server删除连接组合记录时，直接指定该表中存在的server\_name即可删除组合记录，如：drop server server\_name;）。
 *  Host：连接组合中的远程主机名（IP或域名），对应create server中的HOST，对应CONNECTION连接组合字符串中的host\_name。
 *  Db：连接组合中的远程实例的数据库名称，对应create server中的DATABASE ，对应CONNECTION连接组合字符串中的db\_name。
 *  Username：连接组合的远程实例用户名,对应create server中的USER ，对应CONNECTION连接组合字符串中的user\_name。
 *  Password：连接组合的远程实例用户密码，对应create server中的PASSWORD ，对应CONNECTION连接组合字符串中的password。
 *  Port：连接组合的远程实例端口，对应create server中的PORT ，对应CONNECTION连接组合字符串中的port\_num。
 *  Socket：连接组合的本地实例的socket路径，对应create server中的SOCKET ，对应CONNECTION连接组合字符串中的host\_name。
 *  Wrapper：类似一个协议名称，对应create server中的WRAPPER ，对应CONNECTION连接组合字符串中的scheme。

PS：

*  CONNECTION字符串方式不会在mysql.servers表中添加记录。

本期内容就介绍到这里，本期内容参考链接如下：

https://dev.mysql.com/doc/refman/5.7/en/federated-create-server.html

https://dev.mysql.com/doc/refman/5.7/en/server-logs.html

“

"翻过这座山，你就可以看到一片海！"。坚持阅读我们的"全方位认识 mysql 系统库"系列文章分享，你就可以系统地学完它。谢谢你的阅读，我们下期不见不散！

”

**| 作者简介**

**罗小波·沃趣科技高级数据库技术专家**

IT从业多年，主要负责MySQL 产品的数据库支撑与售后二线支撑。曾参与版本发布系统、轻量级监控系统、运维管理平台、数据库管理平台的设计与编写，熟悉MySQL体系结构，Innodb存储引擎，喜好专研开源技术，多次在公开场合做过线下线上数据库专题分享，发表过多篇数据库相关的研究文章。