Learn how to fix MySQL High Availability error in SafeSync for Enterprise. This article enumerates the possible error codes and their respective solutions.
Identifying the specific MySQL error
To determine which MySQL HA error you encountered:
- Run the following commands in both appliance1 and appliance2:
root@appliance1:~# sudo mysql -uroot -p -e "show slave status\G"
- Check the values of these two (2) fields in both appliance1 and appliance2 to find which error occurs.
- Last_IO_Errno:
- Last_IO_Error:
- Perform the appropriate solution below based on the error code you got.
Solutions based on error code
The detailed error message below appears on the server:
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.'
To resolve this error, run the following commands:
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
The detailed error message below appears on the server:
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'
To resolve this error, run the following commands:
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
Alternative solution: Clone MySQL database to another server
You can also clone all MySQL databases from appliance1 to appliance2. Below is a sample script to run on 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"