Keepalive-mysql automatic failover shell script

The MySQL architecture is master-slave (master-slave), and the master node is automatically switched to slave when the master node fails. You can also set it to a dual master, but there is a drawback: when the pressure on the master is very high, the latency from the top is very large, such as 2000 seconds behind, the master is down now, from takeover (VIP drifting to slave), the article just published by the user has not been copied because of the large synchronization latency, so the user published another article, after the original master is repaired, because the I/O and SQL threads are still on, the data that has not been synchronized and copied will continue to be synchronized, at this time, it is possible to change the user's new article, resulting in the loss of user data.
Considering this situation, I still use the master-slave architecture.

Keepalive is easy to install. Isn't it here ?? Long V train? Cong repeatedly raised his dream? Tornado? /P>

# More/etc/keepalived. conf
 
Global_defs {
Router_id KeepAlive_Mysql
 } 
 
Vrrp_script check_run {
Script "/root/sh/mysql_check.sh"
Integer 300
 }
 
Vrrp_sync_group VG1 {
Group {
VI_1
 }
 }
 
Vrrp_instance VI_1 {
State BACKUP
Interface eth0
Virtual_router_id 51
Priority100
Advert_int 1
Nopreempt
Authentication {
Auth_type PASS
Auth_pass 1111
     }
Track_script {
Check_run
     }
 
Notify_master/root/sh/master. sh
Notify_backup/root/sh/backup. sh
Notify_stop/root/sh/stop. sh
 
Virtual_ipaddress {
192.168.8.150
     }
 }

Notify_master <STRING> | <QUOTED-STRING> # script executed after the status changes to MASTER
Policy_backup <STRING> | <QUOTED-STRING> # script executed after the status changes to BACKUP
Policy_fault <STRING> | <QUOTED-STRING> # script executed after the status changes to FAULT
Policy_stop <STRING> | <QUOTED-STRING> # script executed after VRRP is stopped
Y <STRING> | <QUOTED-STRING> # (1) script executed after any state changes

The following describes the usage of the four scripts:

Mysql_check.sh (health check script. When mysql cannot be connected, the keepalive process is disabled and switched over .)
# More mysql_check.sh
 
#! /Bin/bash
 
./Root/. bash_profile
 
Count = 1
 
While true
Do
 
Mysql-e "show status;">/dev/null 2> & 1
I = $?
Ps aux | grep mysqld | grep-v grep>/dev/null 2> & 1
J = $?
If [$ I = 0] & [$ j = 0]
Then
Exit 0
Else
If [$ I = 1] & [$ j = 0]
Then
Exit 0
Else
If [$ count-gt 5]
Then
Break
Fi
Let count ++
Continue
Fi
Fi
 
Done

/Etc/init. d/keepalived stop
Master. sh (the script executed after the status changes to MASTER) (first, judge whether the synchronization replication has been completed. If the synchronization replication has not been completed, skip it after 1 minute, whether or not it has been completed, and stop the synchronization replication process .) (Second, change the admin permission and password of the business account connected to the front-end program, and record the logs and POS points after the current switch .)
# More master. sh
 
#! /Bin/bash
 
./Root/. bash_profile
 
Master_Log_File = $ (mysql-e "show slave status \ G" | grep-w Master_Log_File | awk-F ":" '{print $2 }')
Relay_Master_Log_File = $ (mysql-e "show slave status \ G" | grep-w Relay_Master_Log_File | awk-F ":" '{print $2 }')
Read_Master_Log_Pos = $ (mysql-e "show slave status \ G" | grep-w Read_Master_Log_Pos | awk-F ":" '{print $2 }')
Exec_Master_Log_Pos = $ (mysql-e "show slave status \ G" | grep-w Exec_Master_Log_Pos | awk-F ":" '{print $2 }')
 
I = 1
 
While true
Do
 
If [$ Master_Log_File = $ Relay_Master_Log_File] & [$ Read_Master_Log_Pos-eq $ Exec_Master_Log_Pos]
Then
Echo "OK"
Break
Else
Sleep 1
 
If [$ I-gt 60]
Then
Break
Fi
Continue
Let I ++
Fi
Done

Mysql-e "stop slave ;"
Mysql-e "flush logs; grant all privileges on *. * TO 'admin' @ '%' identified by 'admin'; flush privileges ;"
Mysql-e "show master status;">/tmp/master_status _ $ (date "+ % y % m % d-% H % M" cmd.txt
Backup. sh (script executed after the status changes to BACKUP)
# More backup. sh
 
#! /Bin/bash
 
./Root/. bash_profile
 
Mysql-e "grant all privileges on *. * TO 'admin' @ '%' identified by '1q2w3e4r '; flush privileges ;"
Stop. sh (script executed after keepalived is stopped) (change the admin password first) (second, set parameters to ensure no data loss) (Finally, check whether there are write operations, exit in 1 minute, regardless of whether the execution is complete .)
# More stop. sh
#! /Bin/bash
 
./Root/. bash_profile
 
Mysql-e "grant all privileges on *. * TO 'admin' @ '%' identified by '1q2w3e4r '; flush privileges ;"
Mysql-e "set global innodb_support_xa = 1 ;"
Mysql-e "set global sync_binlog = 1 ;"
Mysql-e "set global innodb_flush_log_at_trx_commit = 1 ;"
 
M_File1 = $ (mysql-e "show master status \ G" | awk-f': ''/File/{print $2 }')
M_Position1 = $ (mysql-e "show master status \ G" | awk-f': ''/Position/{print $2 }')
Sleep 1
M_File2 = $ (mysql-e "show master status \ G" | awk-f': ''/File/{print $2 }')
M_Position2 = $ (mysql-e "show master status \ G" | awk-f': ''/Position/{print $2 }')
 
I = 1
 
While true
Do
 
If [$ M_File1 = $ M_File2] & [$ M_Position1-eq $ M_Position2]
Then
Echo "OK"
Break
Else
Sleep 1
 
If [$ I-gt 60]
Then
Break
Fi
Continue
Let I ++
Fi
Done