本文匯總 Linux 環境下 MySQL 的安裝部署、服務管理、賬號權限、編碼設置、數據導入導出、備份還原、密碼重置等高頻運維操作，命令經實測可直接生產使用。
# Systemd (CentOS 7+/Ubuntu 16.04+)
systemctl start mysqld
systemctl stop mysqld
systemctl restart mysqld
systemctl status mysqld
systemctl enable mysqld # 開機自啟
# SysVinit (舊版)
service mysqld start
service mysqld stop
service mysqld restart
/etc/init.d/mysqld start
# 手動安全啟動 (調試用)
mysqld_safe --user=mysql &
# 關閉 (推薦)
mysqladmin -u root -p shutdown# 本地 Socket 連接
mysql -u root -p
# 遠程 TCP 連接
mysql -h 192.168.1.100 -u root -p
# 指定端口
mysql -h 192.168.1.100 -P 3306 -u root -p
# 執行單條 SQL
mysql -u root -p -e "SHOW DATABASES;"
# 退出
exit / quit / \q# 首次設置 root 密碼 (無舊密碼)
mysqladmin -u root password 'NewPass123!'
# 修改密碼 (知舊密碼)
mysqladmin -u root -p'OldPass' password 'NewPass123!'
# SQL 方式修改 (8.0)
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass123!';
# 5.7
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('NewPass123!');
# 忘記 root 密碼重置
systemctl stop mysqld
mysqld_safe --skip-grant-tables --skip-networking &
mysql -u root
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass123!';
mysqladmin -u root -p'NewPass123!' shutdown
systemctl start mysqld# 創建用戶 (8.0)
CREATE USER 'app'@'%' IDENTIFIED BY 'AppPass123!';
# 授權常用模板
GRANT SELECT, INSERT, UPDATE, DELETE ON app_db.* TO 'app'@'%'; -- 業務讀寫
GRANT SELECT ON app_db.* TO 'readonly'@'%'; -- 只讀
GRANT ALL PRIVILEGES ON app_db.* TO 'dba'@'192.168.1.%'; -- 庫管理員
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.101'; -- 複製
FLUSH PRIVILEGES;# my.cnf [mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect = 'SET NAMES utf8mb4'
# [client]
default-character-set = utf8mb4
# 庫/表級修改
ALTER DATABASE db_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
ALTER TABLE tbl_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;# 單庫備份 (含結構+數據)
mysqldump -u root -p --single-transaction --routines --triggers --events db_name > db_backup.sql
# 多庫備份
mysqldump -u root -p --databases db1 db2 > multi_backup.sql
# 全量備份
mysqldump -u root -p --all-databases --single-transaction > full_backup.sql
# 僅結構
mysqldump -u root -p --no-data db_name > schema.sql
# 僅數據
mysqldump -u root -p --no-create-info db_name > data.sql
# 指定表
mysqldump -u root -p db_name tbl1 tbl2 > tables.sql
# 遠程備份
mysqldump -h 192.168.1.100 -u root -p db_name > remote_backup.sql
# 壓縮備份
mysqldump -u root -p db_name | gzip > db_backup.sql.gz# 單庫還原
mysql -u root -p db_name < backup.sql
# 多庫/全量還原 (文件含 CREATE DATABASE)
mysql -u root -p < full_backup.sql
# 交互式還原 (大文件推薦)
mysql -u root -p
SOURCE /path/backup.sql;# 文件格式：字段 Tab 分隔，NULL 為 \N
# data.txt
1 name1 2023-01-01
2 name2 \N
# SQL 方式
LOAD DATA LOCAL INFILE 'data.txt' INTO TABLE tbl
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'
IGNORE 1 LINES; -- 跳過標題行
# 命令行工具
mysqlimport -u root -p --local --fields-terminated-by='\t' db_name data.txt# 顯示數據庫/表
SHOW DATABASES;
SHOW TABLES;
SHOW CREATE TABLE tbl;
DESC tbl;
# 表結構變更
ALTER TABLE tbl ADD COLUMN col TYPE;
ALTER TABLE tbl MODIFY col NEW_TYPE;
ALTER TABLE tbl ENGINE=InnoDB;
# 索引管理
CREATE INDEX idx_name ON tbl (col);
DROP INDEX idx_name ON tbl;
# 表優化
OPTIMIZE TABLE tbl; -- 碎片整理
ANALYZE TABLE tbl; -- 更新統計
CHECK TABLE tbl; -- 檢查損壞
REPAIR TABLE tbl; -- 修復 (MyISAM)
# 進程管理
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;
KILL ;
# 狀態監控
SHOW GLOBAL STATUS LIKE 'Threads_connected';
SHOW GLOBAL STATUS LIKE 'Queries';
SHOW ENGINE INNODB STATUS;
SHOW VARIABLES LIKE 'innodb_buffer_pool%';
# 慢查詢日誌分析
mysqldumpslow -s t /var/log/mysql/slow.log | head -20
pt-query-digest /var/log/mysql/slow.log # /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:/usr/local/mysql/lib
# 生效
source /etc/profile.d/mysql.shmysqldump 單事務一致性備份需 --single-transaction (InnoDB) + --master-data=2 記錄 binlog 位置。ALTER TABLE 會鎖表重建，生產環境用 pt-online-schema-change 或 gh-ost 在線變更。LOAD DATA LOCAL INFILE 需客戶端與服務端均啟用 local_infile=ON。LOCK TABLES/UNLOCK TABLES 導入報權限錯誤時，刪除該兩行或用 --skip-lock-tables 導出。mysqlcheck -u root -p --optimize --all-databases 整理碎片。將上述命令製成團隊運維手冊，配合 Ansible/Shell 自動化腳本，可大幅降低人為錯誤，提升故障響應速度。
