MySQL DBA 實戰手冊：遠程導入導出、三種升級法、忘記密碼、慢查詢分析、緊急排查、主從管理

一、遠程導入導出（跨機器遷移）

# 導出（指定字符集、壓縮）
mysqldump --default-character-set=gb2312 -h255.255.0.0 -uroot -pxxxxxx mydatabase > d:\data.sql

# 導入
mysql --default-character-set=gb2312 -h255.255.0.0 -uroot -pxxxxxx mydatabase < d:\data.sql

二、MySQL 三大升級方案

方案一：通用邏輯導出導入（適用所有引擎）

1. 新版安裝在 3307 端口，啟動
2. 新版創建同名庫
3. 舊版導出：mysqldump -P3306 -uroot db > db.bak（建議加 --opt）
4. 導入新版：mysql -P3307 -uroot db < db.bak
5. 覆蓋 mysql 系統庫：cp -R /old/data/mysql /new/data/
6. 執行 mysql_upgrade（含 mysqlcheck、mysql_fix_privilege_tables）
7. 切換端口到 3306，重啟新版

方案二：帶表結構分離導出（適用所有引擎）

1. 舊版導出：mysqldump -P3306 -uroot --tab=/backup/db db（生成 .sql 結構 + .txt 數據）
2. 新版建庫、導入結構：cat /backup/db/*.sql | mysql -P3307 -uroot db
3. 導入數據：mysqlimport db /backup/db/*.txt
4. 後續步驟同方案一

方案三：MyISAM 物理文件直接拷貝（最快、僅限 MyISAM）

1. 拷貝 .frm .MYD .MYI 到新版數據目錄
2. 執行 mysql_upgrade、切換端口、重啟

三、遠程連不上排查

• 檢查用戶權限表：SELECT Host, User FROM mysql.user; 確認有 % 或具體 IP 授權
• 檢查防火牆/安全組放行 3306
• 檢查 bind-address 配置

四、忘記密碼重置

Windows

net stop mysql
mysqld-nt --skip-grant-tables
# 另開窗口
mysql
USE mysql;
UPDATE user SET Password=PASSWORD('new_pass') WHERE User='root';
FLUSH PRIVILEGES;
EXIT;
# 任務管理器殺掉 mysqld-nt，重新啟動服務

Linux

killall -TERM mysqld
bin/safe_mysqld --skip-grant-tables &
mysql
USE mysql;
UPDATE user SET Password=PASSWORD('new_pass') WHERE User='root';
FLUSH PRIVILEGES;
killall -TERM mysqld
service mysqld start

五、修改默認字符集

# 會話級
SET NAMES 'utf8';
# 或分項設置
SET character_set_client = utf8; SET character_set_connection = utf8; ...

# 全局永久（修改 my.cnf 重啟）
[mysqld]
default-character-set = utf8

六、慢查詢分析神器：mysqldumpslow

# 按查詢次數排序 Top 10
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# 按耗時排序 Top 10
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# 含左連接的耗時 Top 10
mysqldumpslow -s t -t 10 -g "left join" /var/log/mysql/slow.log

-s c/t/l/r：按次數/時間/查詢時間/返回行數排序；-t N：Top N；-g pattern：正則過濾。

七、查看參數值

SHOW VARIABLES LIKE '%var_name%';
SELECT @@var_name;

八、緊急排查：數據庫突然壓力大

# 1. 看當前非 Sleep 進程
mysql -uroot -ppwd db -e "SHOW FULL PROCESSLIST" | grep -v Sleep

# 2. 按執行時間倒序
mysql -uroot -ppwd db -e "SHOW FULL PROCESSLIST" | grep -v Sleep | sort -k6rn > sort.tmp

# 3. 高 IOWAIT 看臨時表
mysql -uroot -ppwd db -e "SHOW GLOBAL STATUS LIKE '%tmp%'"

關注 Creating tmp table、Sending data、Statistics 等狀態定位瓶頸。

九、主從復制日常管理

重做 Slave / 切換 Master

CHANGE MASTER TO
  MASTER_HOST='IP',
  MASTER_USER='rep_user',
  MASTER_PASSWORD='rep_pass',
  MASTER_LOG_FILE='binlog.000001',
  MASTER_LOG_POS=0;

導出數據（慎用，會鎖表）

nohup mysqldump --databases DB -uUser -pPass --lock-all-tables -F > DATA.sql &
# -F 刷新 Binlog，配合 CHANGE MASTER 實現無縫同步

只導結構 / 只導數據

mysqldump -d DB -uUser -pPass > struct.sql
mysqldump -t DB -uUser -pPass > data.sql

Binlog 定位錯誤

mysqlbinlog binlog_file --start-position=偏移量

授權復制權限

GRANT REPLICATION SLAVE ON *.* TO 'user'@'IP' IDENTIFIED BY 'pass';

查看狀態

SHOW SLAVE STATUS \G
SHOW MASTER STATUS;

跳過錯誤事務

STOP SLAVE;
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE;

十、Linux 系統級排查

# 抓取 3306 端口通信內容
tcpdump -A "dst port 3306"