管理資料庫

管理資料庫要知道哪些事

前幾課都在「使用」資料庫：建表格、新增資料、查詢。資料庫上線之後，管理者還要隨時掌握伺服器的情況：

兩邊做得到的事大多相同。SQL 指令適合在 mysql> 裡邊查邊做；命令列工具適合寫進備份或監控的排程腳本，不必先進入 mysql>。

本課前半段在 SQL 直譯器裡練習 SHOW 指令與 information_schema，後半段用導覽介紹命令列工具。 直譯器裡的 employee 資料庫和前幾課相同。注意：直譯器沒有真的在運轉的伺服器，SHOW STATUS、SHOW TABLE STATUS、SHOW PROCESSLIST 的數字是模擬值， 只用來說明欄位的意思，不代表真實伺服器的數據。

查看資料庫、表格與欄位

SHOW DATABASES 只列出這個帳號有權限的資料庫；information_schema、performance_schema 是 MySQL 內建的系統資料庫，每個帳號都看得到。 mysql、sys 也是系統資料庫，只有管理者看得到。

原課程是 MySQL 4.1／5 的時代，有幾處寫法在 MySQL 8 已經不同：

• SHOW TABLE TYPES 已移除，改用 SHOW ENGINES
• 原講義 SHOW CREATE TABLE 的結果是 TYPE=InnoDB CHARSET=latin1、int(11)。MySQL 8 會顯示 ENGINE=InnoDB、 預設字元集 utf8mb4，而且從 8.0.19 起整數型別不再顯示寬度，只寫 int

SHOW STATUS 與 SHOW VARIABLES 的差別

兩個指令的結果都是 Variable_name 和 Value 兩欄，很容易混淆：

兩個指令都可以加 LIKE 篩選，% 代表任意字元：

SHOW VARIABLES LIKE 'max_connections';
SHOW STATUS LIKE 'Com_%';
SELECT @@max_connections;          -- 系統變數也可以用 @@ 讀取

SHOW STATUS 預設是 SESSION 範圍：計數只算目前這條連線；要看整個伺服器開機以來的總數，寫 SHOW GLOBAL STATUS。 Uptime 這類只有全域值的變數，兩種寫法結果相同。

SHOW TABLE STATUS 列出每個表格的引擎、列數（Rows）、資料與索引占用的位元組（Data_length、Index_length）、下一個自動編號（Auto_increment）。 InnoDB 的 Rows 是估計值，資料多的時候和 SELECT COUNT(*) 不一定相同；要精確的列數一定要 COUNT(*)。

連線、權限與訊息

SHOW PROCESSLIST 列出目前連到伺服器的每一條連線（MySQL 稱為 thread）：

有人執行了跑不完的查詢，拖慢整台伺服器時，管理者可以中斷它：

KILL QUERY 18;   -- 只停止連線 18 正在執行的敘述，連線保留
KILL 18;         -- 直接中斷連線 18（等於 KILL CONNECTION 18）

一般帳號只看得到、也只能中斷自己的連線；要看到所有人的連線需要 PROCESS 權限，要中斷別人的連線需要 CONNECTION_ADMIN（或舊的 SUPER）權限。 Id 不存在時會出現 ERROR 1094 (HY000): Unknown thread id: 18。

SHOW GRANTS 列出帳號的權限：SHOW GRANTS; 是自己，SHOW GRANTS FOR 'fred'@'localhost'; 是別人（需要讀取 mysql 系統資料庫的權限）。 SHOW PRIVILEGES 列出這個版本的 MySQL 有哪些權限可以授予，以及各自的適用範圍。以下節錄自真實的 MySQL 8.0.44：

+-------------------------+---------------------------------------+-------------------------------------------------------+
| Privilege               | Context                               | Comment                                               |
+-------------------------+---------------------------------------+-------------------------------------------------------+
| Alter                   | Tables                                | To alter the table                                    |
| Create                  | Databases,Tables,Indexes              | To create new databases and tables                    |
| Drop                    | Databases,Tables                      | To drop databases, tables, and views                  |
| File                    | File access on server                 | To read and write files on the server                 |
| Grant option            | Databases,Tables,Functions,Procedures | To give to other users those privileges you possess   |
| Process                 | Server Admin                          | To view the plain text of currently executing queries |
| Select                  | Tables                                | To retrieve rows from table                           |
| …                       |                                       |                                                       |
| CONNECTION_ADMIN        | Server Admin                          |                                                       |
| …                       |                                       |                                                       |
+-------------------------+---------------------------------------+-------------------------------------------------------+

Context 說明這個權限能授予在哪一層：Server Admin 只能是全域權限（ON *.*）；Tables 可以授予到單一表格。 大寫的 CONNECTION_ADMIN 這類是 MySQL 8 新增的「動態權限」，用來把原本 SUPER 一個大權限拆細。

SHOW WARNINGS 與 SHOW ERRORS 顯示上一個敘述產生的訊息。看到 1 row in set, 1 warning 卻不知道警告是什麼時，馬上執行 SHOW WARNINGS;。 SHOW ERRORS 只列出等級是 Error 的訊息。執行下一個會產生訊息的敘述之後，舊的訊息就被清掉了。

用 SELECT 查 information_schema

SHOW 指令的結果只能看，不能再篩選、排序或和別的資料合併。 information_schema 是 MySQL 內建的唯讀資料庫，把「資料庫裡有什麼」整理成一般的表格，所以可以用 SELECT 查詢，WHERE、GROUP BY、JOIN 都能用：

查外鍵是最常見的用途：刪除一個表格之前，先查有哪些表格的外鍵指向它，

SELECT TABLE_NAME, COLUMN_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_SCHEMA = 'employee'
  AND REFERENCED_TABLE_NAME = 'employee';

information_schema 的表格名稱與欄位名稱習慣寫成大寫。查詢時一定要用 TABLE_SCHEMA 指定資料庫，否則會把其他資料庫的同名表格也查出來。 （直譯器只模擬了常用的幾個 information_schema 表格，欄位也比真實的少。）

重點整理

• STATUS 是統計、VARIABLES 是設定；InnoDB 的 Rows／TABLE_ROWS 是估計值
• 沒有 PROCESS 權限只看得到自己的連線
• information_schema 可以用 SELECT 查詢，要用 TABLE_SCHEMA 指定資料庫
• MySQL 8：SHOW TABLE TYPES 改為 SHOW ENGINES；TYPE= 改為 ENGINE=；Ubuntu 的 root 用 sudo 登入

下一課學習備份與復原：mysqldump、二進位日誌，以及檢查與修復表格。