MySQL 命令列操作完整指南：連線、管理、備份與匯入匯出

MySQL 命令列操作完整指南

掌握 MySQL 命令列操作是 DBA 與後端工程師的基本功。本文系統整理從連線、帳號管理、日常 DDL/DML、到備份還原的核心指令，適用於 Linux/Windows 環境。

一、連線與退出

# 本機連線（無密碼）
mysql -u root -p

# 指定主機、埠、使用者、密碼（生產環境建議略過 -p 密碼，改用交互輸入）
mysql -h 127.0.0.1 -P 3306 -u root -p'password'

# 遠端連線
mysql -h 192.168.1.100 -u app_user -p

# 退出
mysql> EXIT;  -- 或 QUIT、\q、Ctrl+D

二、密碼與帳號管理

# 修改當前使用者密碼
mysqladmin -u root -p'oldpass' password 'newpass'

# 建立使用者並授權（MySQL 5.7+）
CREATE USER 'app'@'%' IDENTIFIED BY 'strongpass';
GRANT SELECT, INSERT, UPDATE, DELETE ON `mydb`.* TO 'app'@'%';
FLUSH PRIVILEGES;

# 僅允許本機連線（較安全）
CREATE USER 'app'@'localhost' IDENTIFIED BY 'strongpass';
GRANT ALL PRIVILEGES ON `mydb`.* TO 'app'@'localhost';

# 查看使用者權限
SHOW GRANTS FOR 'app'@'%';

# 回收權限
REVOKE INSERT ON `mydb`.* FROM 'app'@'%';

三、資料庫與資料表基本操作

# 顯示所有資料庫
SHOW DATABASES;

# 建立/切換/刪除資料庫
CREATE DATABASE `mydb` CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
USE `mydb`;
DROP DATABASE `mydb`;

# 建立資料表
CREATE TABLE `user` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` VARCHAR(50) NOT NULL,
  `email` VARCHAR(100) NOT NULL UNIQUE,
  `created_at` TIMESTAMP DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 查看表結構
DESC `user`;  -- 或 SHOW CREATE TABLE `user`;

# 修改表結構
ALTER TABLE `user` ADD COLUMN `phone` VARCHAR(20) AFTER `email`;
ALTER TABLE `user` MODIFY `name` VARCHAR(100) NOT NULL;
ALTER TABLE `user` DROP COLUMN `phone`;

# 清空表資料（保留結構、重置自增 ID）
TRUNCATE TABLE `user`;

四、資料操作 (DML)

# 插入
INSERT INTO `user` (`name`, `email`) VALUES ('Alice', 'alice@example.com');
INSERT INTO `user` (`name`, `email`) VALUES ('Bob', 'bob@example.com'), ('Carol', 'carol@example.com');

# 查詢
SELECT * FROM `user` WHERE `id` = 1;
SELECT `id`, `name` FROM `user` ORDER BY `created_at` DESC LIMIT 10;

# 更新
UPDATE `user` SET `name` = 'Alice Updated' WHERE `id` = 1;

# 刪除
DELETE FROM `user` WHERE `id` = 2;

五、批次執行 SQL 檔案

# 方式 1：重導向輸入（非互動模式）
mysql -u root -p < schema.sql
mysql -u root -p `mydb` < data.sql

# 方式 2：source 指令（互動模式中）
mysql> SOURCE /path/to/schema.sql;
mysql> \. /path/to/data.sql

六、資料匯出與匯入

mysqldump 匯出

# 匯出單一資料庫（結構+資料）
mysqldump -u root -p --single-transaction --routines --triggers --events `mydb` > mydb_$(date +%F).sql

# 僅匯出結構
mysqldump -u root -p --no-data `mydb` > mydb_structure.sql

# 匯出指定表
mysqldump -u root -p `mydb` `user` `order` > mydb_tables.sql

# 壓縮匯出
mysqldump -u root -p `mydb` | gzip > mydb_$(date +%F).sql.gz

匯入還原

# 一般匯入
mysql -u root -p `mydb` < mydb.sql

# 壓縮檔匯入
gunzip -c mydb.sql.gz | mysql -u root -p `mydb`

# 大檔案優化：關閉自動提交、外鍵檢查、唯一檢查
mysql -u root -p `mydb` --init-command="SET autocommit=0; SET foreign_key_checks=0; SET unique_checks=0;" < mydb.sql

七、文字檔匯入

# 資料檔格式：欄位以 Tab 分隔，NULL 用 \N 表示
# data.txt 內容：
# 1001    Alice   alice@example.com
# 1002    Bob     bob@example.com

LOAD DATA LOCAL INFILE '/path/to/data.txt' 
INTO TABLE `user` 
FIELDS TERMINATED BY '\t' 
LINES TERMINATED BY '\n' 
(`id`, `name`, `email`);

八、常用管理指令

# 查看程序列表
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;  -- 顯示完整 SQL

# 終止長執行查詢
KILL 12345;

# 查看狀態變數
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

# 線上修改參數（重啟失效，持久化需改 my.cnf）
SET GLOBAL max_connections = 500;
SET GLOBAL innodb_buffer_pool_size = 1073741824;  -- 1GB

九、Windows 環境特有操作

# 啟動/停止服務
net start mysql80
net stop mysql80

# 安裝為服務
mysqld --install MySQL80 --defaults-file="C:\ProgramData\MySQL\MySQL Server 8.0\my.ini"

# 移除服務
mysqld --remove MySQL80

# 忘記 root 密碼重置
# 1. 停止服務
# 2. mysqld --console --skip-grant-tables --shared-memory
# 3. 新開視窗：mysql -u root
# 4. FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpass';

十、效能調優常用查詢

# 慢查詢日誌分析
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 檢查索引使用情況
SELECT * FROM sys.schema_unused_indexes;

# 檢查資料表大小
SELECT TABLE_SCHEMA, TABLE_NAME, 
       ROUND(DATA_LENGTH/1024/1024,2) AS data_mb,
       ROUND(INDEX_LENGTH/1024/1024,2) AS index_mb
FROM information_schema.TABLES 
WHERE TABLE_SCHEMA = 'mydb'
ORDER BY (DATA_LENGTH+INDEX_LENGTH) DESC;