MySQL 運維命令大全整理版：服務、權限、備份、優化、叢集與 Linux 部署

一、Linux 服務管理

# Systemd
systemctl start|stop|restart|status mysqld
systemctl enable mysqld

# SysVinit
/etc/init.d/mysql start|stop|restart|status
service mysql start|stop|restart|status

# mysqladmin
mysqladmin -u root -p shutdown
mysqladmin -u root -p start

二、登入與基本資訊

mysql -u root -p
mysql -h host -u user -p db_name

# 客戶端內
SHOW DATABASES;
USE db_name;
SHOW TABLES;
DESCRIBE tbl_name;
SHOW CREATE TABLE tbl_name;
SELECT VERSION(), USER(), NOW(), DATABASE();

三、密碼與編碼

# 修改密碼
mysqladmin -u root -p'old' password 'new'
ALTER USER 'root'@'localhost' IDENTIFIED BY 'newpass';

# 編碼查看
SHOW VARIABLES LIKE 'character_set_%';
SHOW VARIABLES LIKE 'collation_%';

# 修改編碼
ALTER DATABASE db_name CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

四、帳號權限體系

-- 8.0 建立用戶
CREATE USER 'app'@'%' IDENTIFIED BY 'pass';

-- 權限層級
GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' WITH GRANT OPTION;          -- 超級管理員
GRANT SELECT,INSERT,UPDATE,DELETE ON db.* TO 'rw'@'%';                 -- 讀寫
GRANT SELECT ON db.* TO 'ro'@'%';                                      -- 唯讀
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX,ALTER ON db.* TO 'dev'@'%'; -- 開發
GRANT RELOAD,PROCESS,SHOW DATABASES ON *.* TO 'monitor'@'localhost';   -- 監控

-- 特殊權限說明
-- FILE: 讀寫伺服器檔案
-- PROCESS: SHOW PROCESSLIST/KILL 他人進程
-- RELOAD: FLUSH PRIVILEGES/LOGS
-- SHUTDOWN: mysqladmin shutdown

-- 查看/撤銷/刪除
SHOW GRANTS FOR 'app'@'%';
REVOKE INSERT ON db.* FROM 'app'@'%';
DROP USER 'app'@'%';
FLUSH PRIVILEGES;

五、資料匯入匯出

# 備份
mysqldump -u root -p db_name > backup.sql
mysqldump -u root -p --single-transaction --master-data=2 db_name | gzip > backup.sql.gz
mysqldump -u root -p --databases db1 db2 > multi.sql
mysqldump -u root -p --all-databases > all.sql
mysqldump -u root -p -d db_name > schema.sql
mysqldump -u root -p -t db_name > data.sql

# 匯入
mysql -u root -p db_name < backup.sql
mysql -u root -p < all.sql
SOURCE /path/backup.sql;

# 文字檔
LOAD DATA LOCAL INFILE '/path/file.txt' INTO TABLE tbl
  FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n';
SELECT * INTO OUTFILE '/path/out.txt' FROM tbl;

六、DDL 結構變更速查

-- 欄位
ALTER TABLE tbl ADD COLUMN col VARCHAR(50) DEFAULT '';
ALTER TABLE tbl CHANGE old_col new_col INT UNSIGNED DEFAULT 0;
ALTER TABLE tbl MODIFY COLUMN col BIGINT;
ALTER TABLE tbl ALTER COLUMN col SET DEFAULT 'val';

-- 索引
ALTER TABLE tbl ADD INDEX idx_name (col);
ALTER TABLE tbl ADD UNIQUE INDEX uk_name (col);
ALTER TABLE tbl ADD PRIMARY KEY (col);
DROP INDEX idx_name ON tbl;
CREATE INDEX idx_name ON tbl (col);

-- 表
RENAME TABLE old TO new;
ALTER TABLE old RENAME new;
DROP TABLE tbl;
TRUNCATE TABLE tbl;

七、進階 SQL 與分析

-- 執行計劃
EXPLAIN SELECT * FROM tbl WHERE col=1;
-- 關鍵：type=ref/range/const、key=用到的索引、rows=預估掃描行、Extra=Using index/where/filesort

-- 索引使用條件
-- <, <=, >=, >, =, BETWEEN, IN, LIKE 'prefix%'

-- 索引缺點
-- 1. 寫入變慢 2. 佔空間 3. 優化器負擔

-- 分析索引效率
ANALYZE TABLE tbl;
SHOW INDEX FROM tbl;

-- 查詢快取（5.7 前）
SET GLOBAL query_cache_type=1;
SET GLOBAL query_cache_size=67108864;  -- 64M

-- 碎片整理
OPTIMIZE TABLE tbl;

-- 程序分析建議型別
SELECT * FROM tbl PROCEDURE ANALYSE();
SELECT * FROM tbl PROCEDURE ANALYSE(16,256);

八、系統資訊與監控

SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
SHOW VARIABLES;
SHOW STATUS;
SHOW TABLE STATUS;
SHOW TABLE STATUS FROM db_name;
SHOW ENGINES;
SHOW PLUGINS;

-- 當前狀態關鍵指標
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Questions';
SHOW GLOBAL STATUS LIKE 'Slow_queries';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
SHOW GLOBAL STATUS LIKE 'Innodb_row_lock%';

九、表類型轉換

ALTER TABLE tbl ENGINE=InnoDB;
ALTER TABLE tbl ENGINE=MyISAM;
ALTER TABLE tbl ENGINE=MEMORY;

十、Linux 下 MySQL 原始碼安裝（舊版流程參考）

# 1. 下載 RPM
wget https://dev.mysql.com/get/Downloads/MySQL-4.0/MySQL-server-4.0.16-0.i386.rpm
wget https://dev.mysql.com/get/Downloads/MySQL-4.0/MySQL-client-4.0.16-0.i386.rpm

# 2. 安裝
rpm -ivh MySQL-server-4.0.16-0.i386.rpm
rpm -ivh MySQL-client-4.0.16-0.i386.rpm

# 3. 重要目錄
/var/lib/mysql/          # 資料目錄
/usr/share/mysql/        # 配置檔、啟動腳本
/usr/bin/                # mysqladmin、mysqldump 等命令
/etc/rc.d/init.d/        # 啟動腳本

# 4. 修改資料目錄
mkdir /home/data/mysql
mysqladmin -u root -p shutdown
mv /var/lib/mysql /home/data/
# 修改 /etc/my.cnf socket 路徑
# 修改 /etc/rc.d/init.d/mysql datadir 路徑
/etc/rc.d/init.d/mysql start

# 5. 自動啟動
/sbin/chkconfig --add mysql
/sbin/chkconfig --list

十一、備份與還原實戰

# 備份
cd /home/data/mysql
mysqldump -u root -p --opt db_name > backup.sql

# 還原
mysql -u root -p new_db < backup.sql

十二、常用系統優化參數（my.cnf 範例）

[mysqld]
max_connections=1000
thread_cache_size=100
table_open_cache=2048
query_cache_type=1
query_cache_size=64M
tmp_table_size=64M
max_heap_table_size=64M
sort_buffer_size=4M
read_buffer_size=2M
read_rnd_buffer_size=8M
join_buffer_size=4M
key_buffer_size=256M
innodb_buffer_pool_size=4G
innodb_log_file_size=512M
innodb_flush_log_at_trx_commit=2
innodb_file_per_table=1
skip-name-resolve