Shulou(Shulou.com)06/01 Report--

The following together to understand the use of Xtarbakcup to achieve mysql master-slave synchronization detailed steps, I believe we will certainly benefit a lot after reading, the text is not more refined, I hope to use Xtarbakcup to achieve mysql master-slave synchronization detailed steps This short content is what you want.

I. Description of environment

This synchronization-mysql environment is not unified for the time being (it will be upgraded later, so it is recommended to maintain unity first)

II. Adjust the serverID of the master-slave node

(master opens binlog--slave closes binlog)

[root@backup~]#cat/etc/my.cnf| egrep "data| server "#Note: The master-slave state will follow serverID to determine that all masters must be minimum

Third, use xtrabackup on Master to make full database backup innobackup--user = root--password = mysqlpassword--socket =/data/mysqldata/mysql. sock--defaults-file =/etc/my. cnf/tmp/Fourth, in order to ensure consistent data in backup set, operation is required: synchronous application log innobackupex--apply-log/data/backup/innobackupex/2014 - 07 - 31_11 - 20 - 44 V. Master creates synchronization account and authorizes REPLICATION GRANT REPLICATION SLAVE ON *.* TO repl@'192.168.106.% 'IDENTIFIED BY' password'; VI. Test on Slave to see if you can log in to Master Cloud Virtual Machine using repl user:

mysql -h292.168.223.132 -urepl -ppassword

Seven, the master's full standby scp to the slave host scp-r./ 2014 - 07 - 31_11 - 20 - 44 192.168.223.133:/data/backup#backup the data in the data directory first to facilitate recovery Eight: view binlog location [root@ds1tmp]#cat/tmp/2017 - 12 - 29_12 - 33 - 01/xtrabackup_binlog_info mysql-bin. 000460 82773878 IX. Restore backup to slaveinnobackupex--user = root--password = password--apply-log/tmp/2017 - 12 - 27_14 - 36 - 43 innobackupex--user = root--password = password--socket =/tmp/mysql. sock--defaults-file =/etc/my. cnf--copy-back/tmp/X. Reauthorize chown-R mysql. mysql var/##var is mysql data directory XI. Start Slave Database [root@backup mysql]#/etc/init. d/mysql starting MySQL.. [OK] XII. Perform change master on slave to set master Cloud Virtual Machine replication information View binlog location above change master to master_host ='10.27.82. * ', master_user =' repl', master_password ='replpassword', master_port = 3306,master_log_file =' mysql-bin. 000460', master_log_pos = 82773878; XIII. Start replication on slave mysql> start slave; Query OK, 0 rows affected (0.00 sec) XIV. Check whether master-slave replication is normal

mysql> show slave status\G

XV. Error handling

1）1146

Treatment:

[root@backup ~]# cat /etc/my.cnf|egrep "lower_case_table_names"lower_case_table_names=1

Add a line (table names are case-insensitive) to the my.ini file in the Slave Cloud Virtual Machine: lower_case_table_names = 1. Restart the Slave Cloud Virtual Machine and it will be OK.

XVI. Check mysql master-slave synchronization status script #!/ bin/bashDATE =`date + %F- %H- %M` LOG ="/root/shell/ckslave/slave.log "##WeiXin ENV-----------------------------------------------------------CropID ='WeChat ID **<$'Secret ='HxTbS_-rNujfHBAqFt7XMifFKEHQTX_yTNK2ciGob43mHgfc_GJiNW2dwjlitOCS'APIURL="www.example.com corpid=$CropID&corpsecret=$Secret"TOKEN=$(/usr/bin/curl -s -G $APIURL | awk -F\" '{print $10}')POSTURL="https://qyapi.weixin.qq.com/cgi-bin/message/send? access_token=$TOKEN"##WeiXin body--------------------------------------------------------------------------------------function body() { local int AppID=1 local UserID = User local PartyID=2 printf '{\n' printf '\t"touser": "'"$UserID"\"",\n" printf '\t"toparty": "'"$PartyID"\"",\n" printf '\t"msgtype": "text",\n' printf '\t"agentid": "'"$AppID"\"",\n" printf '\t"text": {\n' printf '\t\t"content": "'59.110.0.51--"$Msg"\""\n" printf '\t},\n' printf '\t"safe":"0"\n' printf '}\n' }##check status----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -e "show slave status\G"|grep "Slave_.*_ Running" |awk '{print $2}'))if [ "${slave_is[0]}" = "Yes" -a "${slave_is[1]}" = "Yes" ] then echo "----------------------------------" echo "OK, From library status running normally... " echo "$DATE OK, From library status running normally... " >>$LOG echo "----------------------------------" exit 0else echo "----------------------------------" echo "Fail, the slave library status is not working properly, please log in to check!!! " echo "$DATE Fail, the slave library status is not working properly, please log in to check!!! " >>$LOG echo "----------------------------------" Msg ="Fail, slave library status is not normal, please log in to check!!! " echo "$(body guozhiheng0123 $2 )" $POSTURL /usr/bin/curl --data-ascii "$(body guozhiheng0123 $2)" $POSTURL exit 2

fi

After reading the detailed steps of using Xtarbakcup to achieve mysql master-slave synchronization, many readers will definitely want to know more related content. For more industry information, you can pay attention to our industry information column.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.