日誌資訊記錄表|全方位認識 mysql 系統庫

在上一期《複製資訊記錄表|全方位認識 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儲存引擎，喜好專研開源技術，多次在公開場合做過線下線上資料庫專題分享，發表過多篇資料庫相關的研究文章。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28218939/viewspace-2664740/，如需轉載，請註明出處，否則將追究法律責任。