識別 MySQL 錯誤
請確定遇到的 MySQL HA 錯誤代碼:
- 在 appliance1 和 appliance2 執行以下命令:
root@appliance1:~# sudo mysql -uroot -p -e "show slave status\G"
- 檢查 appliance1 和 appliance2 這兩個字段的值，以查找是哪個錯誤。
- Last_IO_Errno:
- Last_IO_Error:
- 根據獲得的錯誤代碼，執行以下解決方案:
根據錯誤代碼的解決方案
以下詳細的錯誤訊息出現在伺服器上:
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position; the first event 'mysql-bin.003583' at 45997491, the last event read from './mysql-bin.003583' at 4, the last byte read from './mysql-bin.003583' at 4.'
解決此錯誤，請執行以下命令:
root@appliance1:~# sudo mysql -uroot -p
mysql> STOP SLAVE;
mysql> CHANGE MASTER TO master_log_file = 'mysql-bin.003584', master_log_pos = 0;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G
以下詳細的錯誤訊息出現在伺服器上:
Last_IO_Errno: 1060
Last_IO_Error: Error 'Duplicate column name 'encrypt_all'' on query. Default database: ''. Query: 'ALTER TABLE osdp.policies ADD COLUMN `encrypt_all` tinyint(1) unsigned DEFAULT '0' AFTER allow_download_infected_file'
解決此錯誤，請執行以下命令:
root@appliance1:~# sudo mysql -uroot -p
mysql> STOP SLAVE;
mysql> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G
替代解決方案: Clone MySQL 資料庫到另一台伺服器
您可以將所有的 MySQL 資料庫從 appliance1 clone到 appliance2，以下是在 appliance2 執行的範本: fix-mysql-ha-appliance2.sh:
#!/bin/bash
set -x
exec 3>&1
exec 1> >(tee --append fix-ha2-$(date | sed --expression='s/ /_/g').log >&3)
exec 2>&1
if [ $(hostname) != 'appliance2' ]; then
echo "This script must be run on appliance2"
exit 1
fi
ha1_ip='appliance1'
SSH="ssh -oBatchMode=yes -oStrictHostKeyChecking=no"
SCP=${SSH/ssh/scp}
if ! $SSH root@$ha1_ip 'echo hello'; then
echo "appliance2 cannot login appliance1 via ssh"
exit 1
fi
# stop all service except mysql
/opt/SingleInstaller/nodeControl/bin/restart_all_services --mgmtui --no-start
$SSH root@$ha1_ip '/opt/SingleInstaller/nodeControl/bin/restart_all_services --mgmtui --no-start'
$SSH root@$ha1_ip 'MYSQL_PWD=safesync mysql -uroot -e "STOP SLAVE; RESET SLAVE ALL; RESET MASTER;"'
MYSQL_PWD=safesync mysql -uroot -e "STOP SLAVE; RESET SLAVE ALL; RESET MASTER;"
/opt/SingleInstaller/nodeControl/bin/clone_mysql_from_appliance1 --yes
$SSH root@$ha1_ip 'MYSQL_PWD=safesync mysql -uroot -e "SHOW SLAVE STATUS \G"'
MYSQL_PWD=safesync mysql -uroot -e "SHOW SLAVE STATUS \G"
# start all services
$SSH root@$ha1_ip '/opt/SingleInstaller/nodeControl/bin/restart_all_services --all --no-stop'
/opt/SingleInstaller/nodeControl/bin/restart_all_services --all --no-stop
echo "Fixing is completed"