MySQL 運維常見問題速查：二進制日志、datadir 遷移、備份恢復、密碼重置

1. 二進制日志佔用巨大空間

現象：/usr/local/mysql/data 下產生 localhost-bin.001 等文件，佔用數 GB。

本質：非錯誤日志，用於增量備份、主從同步，記錄所有 DDL/DML。

解決：編輯 my.cnf 註釋 log-bin=mysql-bin，重啟；或執行 FLUSH LOGS 切換新文件後手動清理舊文件（配合 PURGE MASTER LOGS）。

2. 遷移 datadir 目錄

1. 停庫：service mysqld stop
2. 拷貝：cp -a /usr/local/mysql/data /new/path/data
3. 改配置：vi /etc/my.cnf 修改 datadir=/new/path/data
4. 啟庫：service mysqld start

3. 備份恢復核心命令

# 完整備份
mysqldump -u root -p db > backup.sql
# 壓縮備份
mysqldump -u root -p db | gzip > backup.sql.gz
# 遠程備份
mysqldump -u root -p db -h remote_ip > backup.sql

# 恢復
mysql -u root -p db < backup.sql
gunzip < backup.sql.gz | mysql -u root -p db

4. 啟動腳本註冊開機自啟

cp /usr/local/mysql/share/mysql/mysql.server /etc/rc.d/init.d/mysqld
chkconfig --add mysqld
# 手動啟動
/usr/local/mysql/bin/mysqld_safe --user=mysql &

5. 忘記 root 密碼重置

Windows

net stop mysql
mysqld-nt --skip-grant-tables
-- 另開窗口
mysql -uroot
USE mysql;
UPDATE user SET password=PASSWORD('newpass') WHERE user='root';
FLUSH PRIVILEGES;
-- 殺掉 mysqld-nt 進程，net start mysql

Linux

killall -TERM mysqld
/usr/bin/mysqld_safe --skip-grant-tables &
mysql -uroot
USE mysql;
UPDATE user SET password=PASSWORD('newpass') WHERE user='root';
FLUSH PRIVILEGES;
killall mysqld
service mysqld start

6. Can't connect to local MySQL server through socket

原因：服務未啟動，mysql.sock 是啟動後生成的。

解決：killall mysqld → 按安裝路徑啟動 mysqld_safe --user=mysql & → 再連接。

7. Client does not support authentication protocol

原因：MySQL 4.1+ 密碼加密方式變更，舊客戶端不支持。

解決：登錄後執行 UPDATE mysql.user SET password=OLD_PASSWORD('yourpass') WHERE user='youruser'; FLUSH PRIVILEGES; 或升級客戶端驅動。