MySQL 命令行大全：從入門到進階的完整操作指南

MySQL 命令行大全：從入門到進階的完整操作指南

本文彙整 MySQL 命令列操作精華，涵蓋服務管理、帳號權限、資料庫/資料表操作、資料匯入匯出、備份還原、效能優化等核心技能，適合 DBA 速查與開發者入門。

一、服務啟動與停止

# Windows
net stop mysql
net start mysql

# Linux (SysVinit)
/etc/rc.d/init.d/mysqld start
/etc/rc.d/init.d/mysqld stop

# Linux (systemd)
systemctl start mysqld
systemctl stop mysqld

二、登入 MySQL

# 本機登入
mysql -u root -p
# 指定主機、資料庫
mysql -h 192.168.1.100 -u root -p mydb
# 直接帶密碼 (不建議生產環境)
mysql -uroot -p123456

三、使用者與權限管理

建立使用者

# 允許本機登入，擁有所有資料庫增刪改查權限
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user1'@'localhost' IDENTIFIED BY 'password1';

# 允許任意主機登入 (百分號為萬用字元)
GRANT SELECT, INSERT, UPDATE, DELETE ON *.* TO 'user1'@'%' IDENTIFIED BY 'password1';

# 僅特定資料庫權限
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'user2'@'localhost' IDENTIFIED BY 'password2';

權限回收與刪除

REVOKE DELETE ON mydb.* FROM 'user2'@'localhost';
DROP USER 'user2'@'localhost';

修改 root 密碼

# 5.7+ 版本
UPDATE mysql.user SET authentication_string=PASSWORD('newpass') WHERE User='root' AND Host='localhost';
FLUSH PRIVILEGES;

# 或使用 mysqladmin
mysqladmin -u root -p password 'newpass'

四、資料庫與資料表操作

五、資料匯入匯出與備份還原

mysqldump 匯出

# 匯出整個資料庫 (含結構+資料)
mysqldump -u root -p dbname > backup.sql

# 僅匯出結構
mysqldump -u root -p -d dbname > schema.sql

# 匯出特定資料表
mysqldump -u root -p dbname tbl_name > table_backup.sql

# 壓縮匯出
mysqldump -u root -p dbname | gzip > backup.sql.gz

匯入/還原

# 方式 1: mysql 指令導入
mysql -u root -p dbname < backup.sql

# 方式 2: source 指令 (需先進入 mysql shell)
USE dbname;
SOURCE /path/to/backup.sql;

# 方式 3: 解壓縮並導入
gunzip < backup.sql.gz | mysql -u root -p dbname

文字檔匯入 (LOAD DATA)

# 欄位以 Tab 分隔，每行一筆
LOAD DATA LOCAL INFILE '/path/to/data.txt' INTO TABLE tbl_name
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';

六、進階維護指令

索引管理

# 建立索引
CREATE INDEX idx_name ON tbl_name (col_name);
ALTER TABLE tbl_name ADD INDEX idx_name (col_name);

# 唯一索引
CREATE UNIQUE INDEX uk_email ON users (email);

# 刪除索引
DROP INDEX idx_name ON tbl_name;
ALTER TABLE tbl_name DROP INDEX idx_name;

查詢效能分析

# 執行計畫
EXPLAIN SELECT * FROM tbl_name WHERE col = 'value';

# 關鍵欄位解讀:
# type: ALL(全掃) > index > range > ref > eq_ref > const > system (越右越好)
# key: 實際使用的索引
# rows: 預估掃描行數
# Extra: Using index, Using where, Using filesort 等

資料表優化

# 整理碎片、回收空間 (適用 MyISAM, InnoDB)
OPTIMIZE TABLE tbl_name;

# 分析資料分佈，更新統計資訊
ANALYZE TABLE tbl_name;

# 檢查表錯誤
CHECK TABLE tbl_name;

查詢快取 (MySQL 5.7 以前)

# 設定參數 (my.cnf)
query_cache_type = 1
query_cache_size = 64M

# SQL 中控制
SELECT SQL_CACHE * FROM tbl_name;      # 明確快取
SELECT SQL_NO_CACHE * FROM tbl_name;   # 明確不快取

七、常用系統資訊查詢

# 版本、時間、使用者、目前資料庫
SELECT VERSION(), NOW(), USER(), DATABASE();

# 連線程序
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

# 系統變數
SHOW VARIABLES LIKE '%char%';
SHOW VARIABLES LIKE '%buffer%';

# 資料表狀態
SHOW TABLE STATUS;
SHOW TABLE STATUS FROM dbname;

八、Linux 環境安裝與設定精要

1. RPM 安裝：rpm -ivh MySQL-server.rpm MySQL-client.rpm
2. 資料目錄遷移：停止服務 → 移動 /var/lib/mysql → 修改 my.cnf 的 datadir 與 socket → 修改啟動腳本 /etc/init.d/mysql 的 datadir → 重啟服務。
3. 自動啟動：chkconfig --add mysql / systemctl enable mysqld
4. 字元集設定：[mysqld] character-set-server=utf8mb4，重啟生效。

九、安全性最佳實務

• 刪除匿名帳號：DELETE FROM mysql.user WHERE User='';
• 禁止 root 遠端登入：UPDATE mysql.user SET Host='localhost' WHERE User='root';
• 最小權限原則：應用程式使用專屬帳號，僅授予必要資料庫的 SELECT/INSERT/UPDATE/DELETE。
• 啟用 SSL 連線、設定防火牆限制 3306 連線來源。
• 定期審核權限：SHOW GRANTS FOR 'user'@'host';

十、快速參考卡片

# 顯示建表語句
SHOW CREATE TABLE tbl_name;

# 修改表結構
ALTER TABLE tbl_name ADD COLUMN new_col INT;
ALTER TABLE tbl_name MODIFY col_name VARCHAR(100);
ALTER TABLE tbl_name CHANGE old_col new_col INT;
ALTER TABLE tbl_name RENAME TO new_tbl_name;

# 分頁查詢
SELECT * FROM tbl_name ORDER BY id LIMIT 10 OFFSET 20;  # 第 3 頁，每頁 10 筆

# 字串連接
SELECT CONCAT(id, ':', name) FROM tbl_name;

# 隨機取樣
SELECT * FROM tbl_name ORDER BY RAND() LIMIT 10;

熟練掌握上述指令，即能應付大多數 MySQL 日常運維與開發場景。建議搭配官方文件深入研究各參數細節。