MySQL 基礎操作完整速查手冊：常用命令、安全配置、導入導出與 max_allowed_packet 調優

MySQL 核心操作命令分類速查

資料庫與表結構操作

-- 顯示所有資料庫
SHOW DATABASES;

-- 建立資料庫
CREATE DATABASE mydb CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

-- 選擇資料庫
USE mydb;

-- 顯示所有表
SHOW TABLES;

-- 建立表
CREATE TABLE users (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    created_at DATETIME(6) DEFAULT CURRENT_TIMESTAMP(6)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 檢視表結構
DESCRIBE users;
SHOW CREATE TABLE users\G
SHOW COLUMNS FROM users;

-- 修改表
ALTER TABLE users ADD COLUMN phone VARCHAR(20);
ALTER TABLE users MODIFY COLUMN name VARCHAR(100);
ALTER TABLE users DROP COLUMN phone;

-- 刪除表
DROP TABLE users;

-- 清空表 (保留結構、重置自增、速度快)
TRUNCATE TABLE users;

資料操作 (DML)

-- 插入
INSERT INTO users (name, email) VALUES ('張三', 'zhang@example.com');
INSERT INTO users (name, email) VALUES ('李四', 'li@example.com'), ('王五', 'wang@example.com');

-- 查詢
SELECT * FROM users;
SELECT id, name FROM users WHERE id > 10 ORDER BY created_at DESC LIMIT 20;

-- 更新
UPDATE users SET name = '張三豐' WHERE id = 1;

-- 刪除
DELETE FROM users WHERE id = 1;

-- 從檔案載入資料 (高效批次匯入)
LOAD DATA LOCAL INFILE '/path/data.csv' 
INTO TABLE users 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES 
(name, email);

安全配置標準流程

-- 1. 刪除匿名用戶
DELETE FROM mysql.user WHERE User = '';

-- 2. 設定 root 密碼 (8.0+ 語法)
ALTER USER 'root'@'localhost' IDENTIFIED BY '強密碼123!';
-- 或 5.7 語法
UPDATE mysql.user SET authentication_string = PASSWORD('強密碼123!') WHERE User = 'root' AND Host = 'localhost';

-- 3. 限制 root 僅本地登入
UPDATE mysql.user SET Host = 'localhost' WHERE User = 'root' AND Host = '%';
DELETE FROM mysql.user WHERE User = 'root' AND Host NOT IN ('localhost', '127.0.0.1', '::1');

-- 4. 移除測試資料庫
DROP DATABASE IF EXISTS test;
DELETE FROM mysql.db WHERE Db = 'test' OR Db = 'test\\_%';

-- 5. 刷新權限
FLUSH PRIVILEGES;

-- 6. 建立應用專用用戶 (最小權限)
CREATE USER 'app_user'@'192.168.1.%' IDENTIFIED BY '應用密碼456@';
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'app_user'@'192.168.1.%';

服務管理

導入導出 (備份/遷移/同步)

-- 邏輯備份：單庫
mysqldump -u root -p --single-transaction --routines --triggers --events --hex-blob mydb > mydb_$(date +%F).sql

-- 邏輯備份：多庫
mysqldump -u root -p --databases db1 db2 > multi_db.sql

-- 邏輯備份：全實例 (含系統庫)
mysqldump -u root -p --all-databases > full_backup.sql

-- 壓縮備份
mysqldump -u root -p --single-transaction mydb | gzip > mydb_$(date +%F).sql.gz

-- 還原：單庫
mysql -u root -p mydb < mydb_backup.sql

-- 還原：壓縮檔
gunzip -c mydb_backup.sql.gz | mysql -u root -p mydb

-- 還原：進入 MySQL 後 source
mysql -u root -p
USE mydb;
SOURCE /path/mydb_backup.sql;

-- 遷移：直接管道 (跨伺服器)
mysqldump -h src_host -u root -p --single-transaction mydb | mysql -h dst_host -u root -p mydb

max_allowed_packet 調優 (解決 "Packet too large" 錯誤)

錯誤：ERROR 1153 (08S01): Got a packet bigger than 'max_allowed_packet' bytes

-- 1. 檢查當前值
SHOW VARIABLES LIKE 'max_allowed_packet';

-- 2. 臨時調整 (重啟失效)
SET GLOBAL max_allowed_packet = 1073741824;  -- 1GB

-- 3. 永久配置 (my.cnf [mysqld] 區段)
[mysqld]
max_allowed_packet = 1G

-- 4. 客戶端也需調大 (mysqldump/mysql 等)
[client]
max_allowed_packet = 1G

-- 5. 重啟生效
systemctl restart mysqld

建議值：256M~1G，視最大 BLOB/TEXT/JSON 欄位大小與網路 MTU 而定。

old_passwords 相容性設定 (5.7+ 建議關閉)

-- 檢查
SHOW VARIABLES LIKE 'old_passwords';

-- 永久關閉 (my.cnf)
[mysqld]
old_passwords = 0

-- 重啟後驗證
SHOW VARIABLES LIKE 'old_passwords';  -- 應為 OFF/0

核心狀態監控指令

-- 全域狀態
SHOW GLOBAL STATUS;

-- 關鍵指標
SHOW GLOBAL STATUS LIKE 'Max_used_connections';      -- 歷史峰值連線
SHOW GLOBAL STATUS LIKE 'Threads_connected';         -- 當前連線數
SHOW GLOBAL STATUS LIKE 'Slow_queries';              -- 慢查詢累計
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';  -- 物理讀取 (未命中)
SHOW GLOBAL STATUS LIKE 'Questions';                 -- 查詢總數

-- 變數配置
SHOW VARIABLES;
SHOW VARIABLES LIKE 'max_connections';
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

表結構檢視多種方式

DESC users;
DESCRIBE users;
SHOW COLUMNS FROM users;
SHOW CREATE TABLE users\G

此手冊涵蓋 MySQL 日常運維 90% 以上高頻操作。建議收藏並根據實際版本 (5.7/8.0) 微調語法差異。配合 mysql --help、mysqld --help --verbose 官方文件，構建個人化運維知識庫。