MySQL 命令列客戶端操作速查手冊：連線、庫表管理、CRUD、備份還原、密碼修改

MySQL 命令列客戶端操作速查手冊：連線、庫表管理、CRUD、備份還原、密碼修改

整理 mysql 互動模式與非互動模式最高頻操作指令，適合新手入門與資深運維離線速查。

1. 連線與基本導航

# 登入 (本機/遠程)
mysql -u root -p
mysql -h 192.168.1.100 -u app -p -P 3306 -D app_db

# 互動模式基本指令
SHOW DATABASES;
USE db_name;
SHOW TABLES;
DESCRIBE table_name;      -- 或 DESC table_name
SHOW CREATE TABLE table_name\G
SELECT DATABASE();        -- 當前庫
SELECT USER();            -- 當前用戶
SELECT VERSION();         -- 版本
EXIT / QUIT / \q          -- 退出

2. 庫與表 DDL

-- 建庫 (指定字元集)
CREATE DATABASE db_name DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 刪庫
DROP DATABASE db_name;

-- 建表範例
CREATE TABLE `users` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `username` VARCHAR(64) NOT NULL DEFAULT '',
  `email` VARCHAR(128) NOT NULL DEFAULT '',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_username` (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 刪表
DROP TABLE users;

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

3. 資料 CRUD

-- 插入
INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
INSERT INTO users (username, email) VALUES ('bob', 'bob@example.com'), ('carol', 'carol@example.com');

-- 查詢
SELECT * FROM users;
SELECT id, username FROM users WHERE username LIKE 'a%';
SELECT * FROM users ORDER BY created_at DESC LIMIT 10;

-- 更新
UPDATE users SET email = 'alice_new@example.com' WHERE username = 'alice';

-- 刪除
DELETE FROM users WHERE username = 'bob';

4. 匯入匯出 (備份/還原/遷移)

# 匯出整庫 (含建庫語句)
mysqldump -u root -p --databases db_name > /backup/db_name_$(date +%F).sql

# 匯出單表
mysqldump -u root -p db_name table_name > /backup/table_name.sql

# 匯出僅資料 (無建表語句)
mysqldump -u root -p -t db_name table_name > /backup/data_only.sql

# 還原 (需先建立目標庫)
mysql -u root -p db_name < /backup/db_name.sql

# 直接管道複製 (跨主機)
mysqldump -h src_host -u root -p db_name | mysql -h dst_host -u root -p db_name

5. 文字檔匯入 (LOAD DATA)

# 客戶端本地檔案 (需啟用 local_infile=1)
LOAD DATA LOCAL INFILE '/path/data.csv'
INTO TABLE users
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(username, email);

6. 修改 root 密碼

# 方法 1：mysqladmin (不進入互動模式)
mysqladmin -u root -p'oldpass' password 'newpass'

# 方法 2：SQL (進入後)
UPDATE mysql.user SET authentication_string = PASSWORD('newpass') WHERE User = 'root' AND Host = 'localhost';
FLUSH PRIVILEGES;

# 方法 3：5.7+ 官方推薦
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpass';

7. 權限管理常用指令

-- 建立應用帳號
CREATE USER 'app'@'%' IDENTIFIED BY 'StrongPass';

-- 授權 (最小權限)
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app'@'%';

-- 查看權限
SHOW GRANTS FOR 'app'@'%';

-- 回收
REVOKE INSERT ON app_db.* FROM 'app'@'%';

-- 刷新
FLUSH PRIVILEGES;

8. 進程與鎖監控

SHOW PROCESSLIST;                    -- 當前連線
SHOW FULL PROCESSLIST;               -- 完整 SQL 文本
SHOW ENGINE INNODB STATUS\G          -- InnoDB 內部狀態 (含鎖等待)
SELECT * FROM information_schema.INNODB_TRX;  -- 進行中事務
SELECT * FROM information_schema.INNODB_LOCKS; -- 當前鎖
SELECT * FROM information_schema.INNODB_LOCK_WAITS; -- 鎖等待

9. 變數與狀態檢查

SHOW VARIABLES LIKE 'max_connections';
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';