Mysql5.6 main main cluster automatic synchronization script
Synchronization is invalid due to machine shutdown in the mysql master cluster. I wrote a shell script to facilitate future maintenance:
The script reads as follows:
#! / bin/bash
# set master node, slave node IP and mysql database administrator username password information to synchronize username password information
_ MASTERHOST=192.168.1.207
_ SLAVEHOST=192.168.1.208
_ USER=root
_ MASTERPASD=root
_ SLAVEPASD=root
_ TBUSER=ab
_ TBPASD=123
# configure synchronization from slave to master
Mysql-h $_ MASTERHOST-uroot-p$_MASTERPASD-e "flush tables with read lock;"
Master_log_file= `mysql-u root-h $_ MASTERHOST-p$_MASTERPASD-e "show master status;" | awk 'NR==2 {print $1}' `
Master_log_position= `mysql-u root-h $_ MASTERHOST-p$_MASTERPASD-e "show master status;" | awk 'NR==2 {print $2}' `
Mysql-h $_ MASTERHOST-uroot-p$_MASTERPASD-e "unlock tables;"
Mysql-h $_ SLAVEHOST-uroot-p$_SLAVEPASD-e "stop slave;"
Mysql-h $_ SLAVEHOST-uroot-p$_SLAVEPASD-e "CHANGE MASTER TO MASTER_HOST='$_MASTERHOST',MASTER_PORT=3306,MASTER_USER='$_TBUSER',MASTER_PASSWORD='$_TBPASD',MASTER_LOG_FILE='$master_log_file',MASTER_LOG_POS=$master_log_position;"
Mysql-h $_ SLAVEHOST-uroot-p$_SLAVEPASD-e "start slave;"
# configure master-to-slave synchronization
Mysql-h $_ SLAVEHOST-uroot-p$_SLAVEPASD-e "flush tables with read lock;"
Slave_log_file= `mysql-u root-h $_ SLAVEHOST-p$_SLAVEPASD-e "show master status;" | awk 'NR==2 {print $1}' `
Slave_log_position= `mysql-u root-h $_ SLAVEHOST-p$_SLAVEPASD-e "show master status;" | awk 'NR==2 {print $2}' `
Mysql-h $_ SLAVEHOST-uroot-p$_SLAVEPASD-e "unlock tables;"
Mysql-h $_ MASTERHOST-uroot-p$_MASTERPASD-e "stop slave;"
Mysql-h $_ MASTERHOST-uroot-p$_MASTERPASD-e "CHANGE MASTER TO MASTER_HOST='$_SLAVEHOST',MASTER_PORT=3306,MASTER_USER='$_TBUSER',MASTER_PASSWORD='$_TBPASD',MASTER_LOG_FILE='$slave_log_file',MASTER_LOG_POS=$slave_log_position;"
Mysql-h $_ MASTERHOST-uroot-p$_MASTERPASD-e "start slave;"
# check
Mysql-h $_ MASTERHOST-uroot-p$_MASTERPASD-e "show slave status\ G;" | awk'$0 ~ / Host/ | | $0 ~ / State/ | | $0 ~ / Running/'
Mysql-h $_ SLAVEHOST-uroot-p$_SLAVEPASD-e "show slave status\ G;" | awk'$0 ~ / Host/ | | $0 ~ / State/ | | $0 ~ / Running/'
