您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

MySQL 常用管理命令速查手冊：備份、權限、服務控制、資料導入導出

編輯：MySQL 綜合教學

整理自實務高頻操作，涵蓋連線、密碼、服務控制、版本資訊、備份還原、權限管理、觸發器、資料載入、表操作等場景，適合貼在工位或存入筆記本隨時查閱。

連線與基礎資訊


# 遠端連線
mysql -h localhost -u root -p

# 修改密碼
mysqladmin -u root -p'oldpass' password 'newpass'
# 原密碼為空
mysqladmin -u root password 'newpass'

# 顯示版本、狀態、進程
mysqladmin version status processlist

# 查看版本與時間
SELECT VERSION(), CURRENT_DATE();
SELECT NOW();
SELECT USER();

服務控制


# Windows
net start mysql
net stop mysql
mysqladmin -u root -p shutdown

# Linux systemd
systemctl start mysqld
systemctl stop mysqld
systemctl restart mysqld
systemctl status mysqld

# Linux 傳統
service mysql start|stop|restart|status

資料備份與還原 (mysqldump)


# 備份單庫
mysqldump -u root -p dbname > backup.sql

# 備份單表
mysqldump -u root -p dbname tbl_name > tbl.sql

# 備份多表
mysqldump -u root -p dbname tbl1 tbl2 > multi.sql

# 備份全庫
mysqldump -u root -p --all-databases > full.sql

# 僅結構
mysqldump -u root -p -d --add-drop-table dbname > schema.sql

# 遠端備份
mysqldump -h 192.168.1.100 -u root -p dbname > backup.sql

# InnoDB 熱備一致性 (生產必加)
mysqldump -u root -p --single-transaction --master-data=2 dbname > backup.sql

# 還原 (source)
mysql -u root -p
mysql> USE dbname;
mysql> SOURCE /path/backup.sql;

# 還原 (管道)
mysql -u root -p dbname < backup.sql

# 還原指定庫 (從全庫備份)
mysql -u root -p -D dbname < full.sql

權限管理


# 建立用戶，全權限，任意主機 (慎用)
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;

# 建立用戶，僅本機，指定庫
GRANT SELECT, INSERT, UPDATE, DELETE ON dbname.* TO 'user'@'localhost' IDENTIFIED BY 'pass';

# 建立唯讀用戶
GRANT SELECT ON dbname.* TO 'readonly'@'%' IDENTIFIED BY 'pass';

# 撤銷權限
REVOKE ALL PRIVILEGES ON dbname.* FROM 'user'@'localhost';

# 刪除用戶
DROP USER 'user'@'localhost';

# 刷新權限
FLUSH PRIVILEGES;

# 查看用戶權限
SHOW GRANTS FOR 'user'@'localhost';

資料載入與表操作


# 從文本載入 (欄位以 Tab 分隔)
LOAD DATA LOCAL INFILE 'c:/data.txt' INTO TABLE tbl_name;

# 刪除 NULL 行
DELETE FROM tbl WHERE col IS NULL;

# 僅刪除 1 行
DELETE FROM tbl WHERE col='val' LIMIT 1;

# 修改欄位名
ALTER TABLE tbl CHANGE old_name new_name VARCHAR(50);

# 表自我複製 (擴充測試資料)
INSERT INTO msg (sender, getter, content, m_date) 
SELECT sender, getter, content, m_date FROM msg;

# 左連接查詢
SELECT * FROM a LEFT JOIN b ON a.id = b.id;

觸發器範例


DELIMITER ;;
CREATE TRIGGER tg_after_delete
AFTER DELETE ON test1
FOR EACH ROW
BEGIN
    DELETE FROM infos WHERE id = OLD.id;
END;;
DELIMITER ;

字元集與編碼檢查


SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';
-- 統一設為 utf8mb4
SET NAMES utf8mb4;
SET CHARACTER SET utf8mb4;

常用狀態計數器 (SHOW STATUS)


SHOW STATUS LIKE 'Threads_connected';    -- 當前連線數
SHOW STATUS LIKE 'Max_used_connections'; -- 歷史峰值連線
SHOW STATUS LIKE 'Slow_queries';         -- 慢查詢計數
SHOW STATUS LIKE 'Questions';            -- 總查詢數
SHOW STATUS LIKE 'Innodb_buffer_pool_reads'; -- 物理讀取