Linux高可用(HA)之MySQL多主一從+Keepalived跨機房集群部署

本文章來為各位介紹一篇關於Linux高可用(HA)之MySQL多主一從+Keepalived跨機房集群部署例子，希望文章對各位有幫助．

添加host解析、時間同步和ssh互信（注：這裡的做ssh互信的時候使用到一個腳本借助expect實現了面交互操作了）

[root@DS-CentOS51 ~]# echo "172.16.0.51 mysql-master01
> 172.16.0.60 mysql-master02
> 172.16.0.63 mysql-slave01
> 172.16.0.69 mysql-slave02" >> /etc/hosts
[root@DS-CentOS51 ~]# echo "*/5 * * * * /usr/sbin/ntpdate pool.ntp.org >/dev/null 2>&1" >> /var/spool/cron/root && ntpdate pool.ntp.org
 8 Jan 04:12:25 ntpdate[64178]: adjust time server 128.138.141.172 offset -0.068359 sec
[root@DS-CentOS51 ~]# ssh-keygen  -t rsa -f ~/.ssh/id_rsa  -P ''
Generating public/private rsa key pair.
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
fc:77:72:4d:c0:ed:06:ad:09:8c:b3:d3:d4:95:dc:66 [email protected]
The key's randomart image is:
+--[ RSA 2048]----+
|              . +|
|           o o *E|
|          o + =oo|
|       .   = . * |
|        S o . o +|
|         . .   + |
|          . o o .|
|           . +   |
|                 |
+-----------------+
[root@DS-CentOS51 ~]# cat auto_auth.sh
#!/bin/bash
#########################################################################
# File Name: auto_auth.sh
# Author: LookBack
# Email: admin#dwhd.org
# Version:
# Created Time: 2016年01月08日 星期五 04時18分20秒
#########################################################################
 
password=$2
rundir=`pwd`
 
if ! which expect >/dev/null 2>&1; then yum install -y -q expect;fi
 
expect << EOF
set timeout 30
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@$1
expect {
    "(yes/no)" {send "yes\r"; exp_continue}
    "password:" {send "$password\r"}
}
expect eof
EOF
 
scp ${rundir}/$0 root@$1:~/
[root@DS-CentOS51 ~]# echo mysql-{master0{1,2},slave0{1,2}}| sed 's/ /\n/g'|xargs -i bash auto_auth.sh {} lookback
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-master01
root@mysql-master01's password:
Now try logging into the machine, with "ssh 'root@mysql-master01'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
auto_auth.sh                                                                                                                                  100%  606     0.6KB/s   00:00   
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-master02
root@mysql-master02's password:
Now try logging into the machine, with "ssh 'root@mysql-master02'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
auto_auth.sh                                                                                                                                  100%  606     0.6KB/s   00:00   
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-slave01
root@mysql-slave01's password:
Now try logging into the machine, with "ssh 'root@mysql-slave01'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
auto_auth.sh                                                                                                                                  100%  606     0.6KB/s   00:00   
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-slave02
root@mysql-slave02's password:
Now try logging into the machine, with "ssh 'root@mysql-slave02'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
auto_auth.sh                                                                                                                                  100%  606     0.6KB/s   00:00   
[root@DS-CentOS51 ~]#

[root@DS-CentOS60 ~]# sed -i '$d' auto_auth.sh
[root@DS-CentOS60 ~]# ssh-keygen  -t rsa -f ~/.ssh/id_rsa  -P ''
Generating public/private rsa key pair.
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
8e:05:99:63:7a:5e:f0:69:4f:65:23:84:16:fb:66:2e [email protected]
The key's randomart image is:
+--[ RSA 2048]----+
|        .o.      |
|       ooo       |
|      B.. . +    |
|     o = o + .   |
|    . . S =      |
|     o * *       |
|      o E o      |
|         .       |
|                 |
+-----------------+
[root@DS-CentOS60 ~]# echo mysql-{master0{1,2},slave0{1,2}}| sed 's/ /\n/g'|xargs -i bash auto_auth.sh {} lookback
warning: rpmts_HdrFromFdno: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY
Importing GPG key 0xC105B9DE:
 Userid : CentOS-6 Key (CentOS 6 Official Signing Key) <[email protected]>
 Package: centos-release-6-7.el6.centos.12.3.x86_64 (@CentOS/6.7)
 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-master01
The authenticity of host 'mysql-master01 (172.16.0.51)' can't be established.
RSA key fingerprint is 8d:44:a4:74:d1:69:08:0c:89:9b:71:ba:25:3c:ff:89.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'mysql-master01,172.16.0.51' (RSA) to the list of known hosts.
root@mysql-master01's password:
Now try logging into the machine, with "ssh 'root@mysql-master01'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-master02
The authenticity of host 'mysql-master02 (172.16.0.60)' can't be established.
RSA key fingerprint is 6a:ef:31:00:88:7f:3d:8d:56:73:e1:2b:d4:f9:61:9c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'mysql-master02,172.16.0.60' (RSA) to the list of known hosts.
root@mysql-master02's password:
Now try logging into the machine, with "ssh 'root@mysql-master02'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-slave01
The authenticity of host 'mysql-slave01 (172.16.0.63)' can't be established.
RSA key fingerprint is fb:7f:a8:df:a1:70:9b:88:68:d4:e1:20:2f:07:a4:78.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'mysql-slave01,172.16.0.63' (RSA) to the list of known hosts.
root@mysql-slave01's password:
Now try logging into the machine, with "ssh 'root@mysql-slave01'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-slave02
The authenticity of host 'mysql-slave02 (172.16.0.69)' can't be established.
RSA key fingerprint is 1b:5e:e7:cf:51:ed:b7:8d:f7:46:23:57:05:53:43:4b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'mysql-slave02,172.16.0.69' (RSA) to the list of known hosts.
root@mysql-slave02's password:
Now try logging into the machine, with "ssh 'root@mysql-slave02'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
[root@DS-CentOS60 ~]#

[root@DS-CentOS63 ~]# sed -i '$d' auto_auth.sh
[root@DS-CentOS63 ~]# ssh-keygen  -t rsa -f ~/.ssh/id_rsa  -P ''
Generating public/private rsa key pair.
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
9f:32:dd:3f:48:66:4e:53:5c:ac:c0:70:fa:9e:a7:d1 [email protected]
The key's randomart image is:
+--[ RSA 2048]----+
|          .o.  . |
|           oo   o|
|          .  o o |
|           .  +  |
|        S   ..   |
|         o +*o   |
|        o +*=oE  |
|         o  o=.  |
|            . .. |
+-----------------+
[root@DS-CentOS63 ~]# echo mysql-{master0{1,2},slave0{1,2}}| sed 's/ /\n/g'|xargs -i bash auto_auth.sh {} lookback
warning: rpmts_HdrFromFdno: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY
Importing GPG key 0xC105B9DE:
 Userid : CentOS-6 Key (CentOS 6 Official Signing Key) <[email protected]>
 Package: centos-release-6-7.el6.centos.12.3.x86_64 (@CentOS/6.7)
 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-master01
The authenticity of host 'mysql-master01 (172.16.0.51)' can't be established.
RSA key fingerprint is 8d:44:a4:74:d1:69:08:0c:89:9b:71:ba:25:3c:ff:89.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'mysql-master01,172.16.0.51' (RSA) to the list of known hosts.
root@mysql-master01's password:
Now try logging into the machine, with "ssh 'root@mysql-master01'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-master02
The authenticity of host 'mysql-master02 (172.16.0.60)' can't be established.
RSA key fingerprint is 6a:ef:31:00:88:7f:3d:8d:56:73:e1:2b:d4:f9:61:9c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'mysql-master02,172.16.0.60' (RSA) to the list of known hosts.
root@mysql-master02's password:
Now try logging into the machine, with "ssh 'root@mysql-master02'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-slave01
The authenticity of host 'mysql-slave01 (172.16.0.63)' can't be established.
RSA key fingerprint is fb:7f:a8:df:a1:70:9b:88:68:d4:e1:20:2f:07:a4:78.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'mysql-slave01,172.16.0.63' (RSA) to the list of known hosts.
root@mysql-slave01's password:
Now try logging into the machine, with "ssh 'root@mysql-slave01'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-slave02
The authenticity of host 'mysql-slave02 (172.16.0.69)' can't be established.
RSA key fingerprint is 1b:5e:e7:cf:51:ed:b7:8d:f7:46:23:57:05:53:43:4b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'mysql-slave02,172.16.0.69' (RSA) to the list of known hosts.
root@mysql-slave02's password:
Now try logging into the machine, with "ssh 'root@mysql-slave02'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
[root@DS-CentOS63 ~]#

[root@DS-CentOS69 ~]# sed -i '$d' auto_auth.sh
[root@DS-CentOS69 ~]# ssh-keygen  -t rsa -f ~/.ssh/id_rsa  -P ''
Generating public/private rsa key pair.
Your identification has been saved in /root/.ssh/id_rsa.
Your public key has been saved in /root/.ssh/id_rsa.pub.
The key fingerprint is:
b6:60:fe:17:40:ae:69:de:48:2a:61:e0:9a:b2:02:5b [email protected]
The key's randomart image is:
+--[ RSA 2048]----+
|                 |
|        .        |
|       o         |
|.       o        |
|..    ooS.       |
|..E  o=o ..      |
|o= . =.o.  .     |
|* . . o.. .      |
|+. .    ..       |
+-----------------+
[root@DS-CentOS69 ~]# echo mysql-{master0{1,2},slave0{1,2}}| sed 's/ /\n/g'|xargs -i bash auto_auth.sh {} lookback
warning: rpmts_HdrFromFdno: Header V3 RSA/SHA1 Signature, key ID c105b9de: NOKEY
Importing GPG key 0xC105B9DE:
 Userid : CentOS-6 Key (CentOS 6 Official Signing Key) <[email protected]>
 Package: centos-release-6-7.el6.centos.12.3.x86_64 (@CentOS/6.7)
 From   : /etc/pki/rpm-gpg/RPM-GPG-KEY-CentOS-6
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-master01
The authenticity of host 'mysql-master01 (172.16.0.51)' can't be established.
RSA key fingerprint is 8d:44:a4:74:d1:69:08:0c:89:9b:71:ba:25:3c:ff:89.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'mysql-master01,172.16.0.51' (RSA) to the list of known hosts.
root@mysql-master01's password:
Now try logging into the machine, with "ssh 'root@mysql-master01'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-master02
The authenticity of host 'mysql-master02 (172.16.0.60)' can't be established.
RSA key fingerprint is 6a:ef:31:00:88:7f:3d:8d:56:73:e1:2b:d4:f9:61:9c.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'mysql-master02,172.16.0.60' (RSA) to the list of known hosts.
root@mysql-master02's password:
Now try logging into the machine, with "ssh 'root@mysql-master02'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-slave01
The authenticity of host 'mysql-slave01 (172.16.0.63)' can't be established.
RSA key fingerprint is fb:7f:a8:df:a1:70:9b:88:68:d4:e1:20:2f:07:a4:78.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'mysql-slave01,172.16.0.63' (RSA) to the list of known hosts.
root@mysql-slave01's password:
Now try logging into the machine, with "ssh 'root@mysql-slave01'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
spawn ssh-copy-id -i /root/.ssh/id_rsa.pub root@mysql-slave02
The authenticity of host 'mysql-slave02 (172.16.0.69)' can't be established.
RSA key fingerprint is 1b:5e:e7:cf:51:ed:b7:8d:f7:46:23:57:05:53:43:4b.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added 'mysql-slave02,172.16.0.69' (RSA) to the list of known hosts.
root@mysql-slave02's password:
Now try logging into the machine, with "ssh 'root@mysql-slave02'", and check in:
 
  .ssh/authorized_keys
 
to make sure we haven't added extra keys that you weren't expecting.
 
[root@DS-CentOS69 ~]#

master01上安裝MariaDB

[root@DS-CentOS51 ~]# wget -q http://www.dwhd.org/wp-content/uploads/2016/01/install_mysql.sh && bash install_mysql.sh
#這是一個自動編譯安裝MySQL5.5 5.6 5.7、MariaDB5.5 10.0 10.1、MariaDB-Galear、Percona5.5 5.6支持主從復制集群的shell腳本

 

 

安裝結束會顯示相關信息

 

master02上安裝MariaDB(後面節點安裝都需要等Master01安裝結束才可以繼續)

 

1


wget -q http://www.dwhd.org/wp-content/uploads/2016/01/install_mysql.sh && bash install_mysql.sh

 

 

安裝結束會顯示相關信息

sslave01、slave02上安裝MariaDB，和Master02上一樣，由於這裡是自動安裝就不多演示了

Master01上安裝Keepalived

[root@DS-CentOS51 ~]# wget http://www.keepalived.org/software/keepalived-1.2.19.tar.gz
[root@DS-CentOS51 ~]# scp keepalived-1.2.19.tar.gz root@mysql-master02:~/
keepalived-1.2.19.tar.gz                                                                                                                      100%  322KB 322.4KB/s   00:00   
[root@DS-CentOS51 ~]# tar xf keepalived-1.2.19.tar.gz
[root@DS-CentOS51 ~]# cd keepalived-1.2.19/
[root@DS-CentOS51 ~/keepalived-1.2.19]# ./configure --prefix=/usr/local/keepalived --sysconfdir=/etc --with-kernel-dir=/usr/src/kernels/`uname -r` --enable-sha1
[root@DS-CentOS51 ~/keepalived-1.2.19]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ..
[root@DS-CentOS51 ~]# echo "export PATH=/usr/local/keepalived/sbin:\$PATH" > /etc/profile.d/keepalived.sh
[root@DS-CentOS51 ~]# . /etc/profile.d/keepalived.sh
[root@DS-CentOS51 ~]# sed -i "$(awk '/^MANPATH\t/{n=NR}END{print n}' /etc/man.config)a MANPATH\t/usr/local/keepalived/share/man" /etc/man.config
[root@DS-CentOS51 ~]# mv /etc/keepalived/keepalived.conf{,_`date "+%F-%T"`_backup}
[root@DS-CentOS51 ~]# mkdir -p /etc/keepalived/scripts
[root@DS-CentOS51 ~]# ln -sv /usr/local/keepalived/sbin/keepalived /usr/sbin/
[root@DS-CentOS51 ~]# chkconfig keepalived on

Master02上安裝Keepalived

[root@DS-CentOS60 ~]# tar xf keepalived-1.2.19.tar.gz
[root@DS-CentOS60 ~]# cd keepalived-1.2.19/
[root@DS-CentOS60 ~/keepalived-1.2.19]# ./configure --prefix=/usr/local/keepalived --sysconfdir=/etc --with-kernel-dir=/usr/src/kernels/`uname -r` --enable-sha1
[root@DS-CentOS60 ~/keepalived-1.2.19]# make -j $(awk '/processor/{i++}END{print i}' /proc/cpuinfo) && make install && cd ..
[root@DS-CentOS60 ~]# echo "export PATH=/usr/local/keepalived/sbin:\$PATH" > /etc/profile.d/keepalived.sh
[root@DS-CentOS60 ~]# . /etc/profile.d/keepalived.sh
[root@DS-CentOS60 ~]# sed -i "$(awk '/^MANPATH\t/{n=NR}END{print n}' /etc/man.config)a MANPATH\t/usr/local/keepalived/share/man" /etc/man.config
[root@DS-CentOS60 ~]# mv /etc/keepalived/keepalived.conf{,_`date "+%F-%T"`_backup}
[root@DS-CentOS60 ~]# mkdir -p /etc/keepalived/scripts
[root@DS-CentOS60 ~]# ln -sv /usr/local/keepalived/sbin/keepalived /usr/sbin/
[root@DS-CentOS60 ~]# chkconfig keepalived on

配置Master01上的Keepalived

[root@DS-CentOS51 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
    notification_email {
        [email protected]
    }
 
    notification_email_from [email protected]
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id MySQL-HA
}
 
vrrp_script check_mysql {
    script "/etc/keepalived/scripts/mysql_check.sh"
    interval 2
}
 
vrrp_sync_group VG1 {
    group {
        VI_1
    }
}
 
vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 88
    priority 100 
    advert_int 1
    nopreempt #不進行搶占操作
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        check_mysql
    }
 
    virtual_ipaddress {
        172.16.51.100 label eth0:0
    }
 
    notify_master /etc/keepalived/scripts/master.sh
    notify_backup /etc/keepalived/scripts/backup.sh
    notify_stop /etc/keepalived/scripts/stop.sh
}
[root@DS-CentOS51 ~]#

配置Master02上的Keepalived

[root@DS-CentOS60 ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
 
global_defs {
    notification_email {
        [email protected]
    }
 
    notification_email_from [email protected]
    smtp_server 127.0.0.1
    smtp_connect_timeout 30
    router_id MySQL-HA
}
 
vrrp_script check_mysql {
    script "/etc/keepalived/scripts/mysql_check.sh"
    #檢查mysqld進程是否存活的腳本，當發現連接不上mysql，自動把keepalived進程干掉，讓VIP進行漂移
    interval 2
}
 
vrrp_sync_group VG1 {
    group {
        VI_1
    }
}
 
vrrp_instance VI_1 {
    state BACKUP
    interface eth0
    virtual_router_id 88
    priority 90
    advert_int 1
    authentication {
        auth_type PASS
        auth_pass 1111
    }
    track_script {
        check_mysql
    }
 
    virtual_ipaddress {
        172.16.51.100 label eth0:0
    }
 
    notify_master /etc/keepalived/scripts/master.sh #狀態改變為master以後執行的腳本
    notify_backup /etc/keepalived/scripts/backup.sh #狀態改變為backup以後執行的腳本
    notify_stop /etc/keepalived/scripts/stop.sh #VRRP停止以後執行的腳本
    #notify_fault /etc/keepalived/scripts/fault.sh #狀態改變為fault後執行的腳本。
}
[root@DS-CentOS60 ~]#

配置Master01上的mysql_check.sh

[root@DS-CentOS51 ~]# cat /etc/keepalived/scripts/mysql_check.sh
#!/bin/bash
#########################################################################
# File Name: mysql_check.sh
# Author: LookBack
# Email: admin#dwhd.org
# Version:
# Created Time: 2016年01月08日 星期五 05時12分56秒
#########################################################################
 
username=root
password=ZWE4MDM3NTgwNjc2
bindir="/usr/local/mariadb/bin"
mysql_check() { ${bindir}/mysql -u${username} -p${password} -e "select version();"; }
mysql_pid_check() { ps aux | pgrep mysqld; }
 
while :; do
    [ -n "$(mysql_check)" ] && [ -n "$(mysql_pid_check)" ] && exit 0
    [ -n "$(mysql_check)" ] && exit 0 || {
        [[ "$i" -gt "4" ]] \
            && break \
            || { let i++ && sleep 0.3 && continue; }
    }
done
 
service keepalived stop
[root@DS-CentOS51 ~]#

配置Master02上的mysql_check.sh

[root@DS-CentOS60 ~]# cat /etc/keepalived/scripts/mysql_check.sh
#!/bin/bash
#########################################################################
# File Name: /etc/keepalived/scripts/mysql_check.sh
# Author: LookBack
# Email: admin#dwhd.org
# Version:
# Created Time: 2016年01月08日 星期五 06時30分53秒
#########################################################################
 
username=root
password=ZmE1MmYyZTIxNjU4
bindir="/usr/local/mariadb/bin"
 
mysql_check() { ${bindir}/mysql -u${username} -p${password} -e "select version();"; }
mysql_pid_check() { ps aux | pgrep mysqld; }
 
while :; do
    [ -n "$(mysql_check)" ] && [ -n "$(mysql_pid_check)" ] && exit 0
    [ -n "$(mysql_check)" ] && exit 0 || {
        [[ "$i" -gt "4" ]] \
            && break \
            || { let i++ && sleep 0.3 && continue; }
    }
done
 
service keepalived stop

配置Master01上的master.sh腳本

[root@DS-CentOS51 ~]# cat /etc/keepalived/scripts/master.sh
#!/bin/bash
#########################################################################
# File Name: /etc/keepalived/scripts/master.sh
# Author: LookBack
# Email: admin#dwhd.org
# Version:
# Created Time: 2016年01月08日 星期五 05時33分57秒
#########################################################################
 
username=root
password=ZWE4MDM3NTgwNjc2
bindir="/usr/local/mariadb/bin"
 
Master_Log_File=$(${bindir}/mysql -u${username} -p${password} -e "show slave status\G"| awk '/ Master_Log_File/{print $2}')
Relay_Master_Log_File=$(${bindir}/mysql -u${username} -p${password} -e "show slave status\G" | awk '/Relay_Master_Log_File/{print $2}')
Read_Master_Log_Pos=$(${bindir}/mysql -u${username} -p${password} -e "show slave status\G" | awk '/Read_Master_Log_Pos/{print $2}')
Exec_Master_Log_Pos=$(${bindir}/mysql -u${username} -p${password} -e "show slave status\G" | awk '/Exec_Master_Log_Pos/{print $2}')
 
while :; do
    [ "$Master_Log_File" = "$Relay_Master_Log_File" ] && [[ "$Read_Master_Log_Pos" -eq "$Exec_Master_Log_Pos" ]] && { echo "OK" && break; }
    [ "$Master_Log_File" != "$Relay_Master_Log_File" ] || [[ "$Read_Master_Log_Pos" -ne "$Exec_Master_Log_Pos" ]] && {
        sleep 10
        [[ "$i" -gt "60" ]] && break
        let i+=10 && continue; }
done
 
${bindir}/mysql -u${username} -p${password} -e "stop slave;"
${bindir}/mysql -u${username} -p${password} -e "set global innodb_support_xa=0;"
${bindir}/mysql -u${username} -p${password} -e "set global sync_binlog=0;"
${bindir}/mysql -u${username} -p${password} -e "set global innodb_flush_log_at_trx_commit=0;"
${bindir}/mysql -u${username} -p${password} -e "flush logs;GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456';flush privileges;"
${bindir}/mysql -u${username} -p${password} -e "show master status;" > /tmp/master_status_$(date "+%F-%T").txt
[root@DS-CentOS51 ~]#
配置Master02上的master.sh腳本

[root@DS-CentOS60 ~]# cat /etc/keepalived/scripts/master.sh
#!/bin/bash
#########################################################################
# File Name: /etc/keepalived/scripts/master.sh
# Author: LookBack
# Email: admin#dwhd.org
# Version:
# Created Time: 2016年01月08日 星期五 05時33分57秒
#########################################################################
 
username=root
password=ZmE1MmYyZTIxNjU4
bindir="/usr/local/mariadb/bin"
 
Master_Log_File=$(${bindir}/mysql -u${username} -p${password} -e "show slave status\G"| awk '/ Master_Log_File/{print $2}')
Relay_Master_Log_File=$(${bindir}/mysql -u${username} -p${password} -e "show slave status\G" | awk '/Relay_Master_Log_File/{print $2}')
Read_Master_Log_Pos=$(${bindir}/mysql -u${username} -p${password} -e "show slave status\G" | awk '/Read_Master_Log_Pos/{print $2}')
Exec_Master_Log_Pos=$(${bindir}/mysql -u${username} -p${password} -e "show slave status\G" | awk '/Exec_Master_Log_Pos/{print $2}')
 
while :; do
    [ "$Master_Log_File" = "$Relay_Master_Log_File" ] && [[ "$Read_Master_Log_Pos" -eq "$Exec_Master_Log_Pos" ]] && { echo "OK" && break; }
    [ "$Master_Log_File" != "$Relay_Master_Log_File" ] || [[ "$Read_Master_Log_Pos" -ne "$Exec_Master_Log_Pos" ]] && {
        sleep 10
        [[ "$i" -gt "60" ]] && break
        let i+=10 && continue; }
done
 
${bindir}/mysql -u${username} -p${password} -e "stop slave;"
${bindir}/mysql -u${username} -p${password} -e "set global innodb_support_xa=0;"
${bindir}/mysql -u${username} -p${password} -e "set global sync_binlog=0;"
${bindir}/mysql -u${username} -p${password} -e "set global innodb_flush_log_at_trx_commit=0;"
${bindir}/mysql -u${username} -p${password} -e "flush logs;GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456';flush privileges;"
${bindir}/mysql -u${username} -p${password} -e "show master status;" > /tmp/master_status_$(date "+%F-%T").txt

配置master01上的baskup.sh腳本

[root@DS-CentOS51 ~]# cat /etc/keepalived/scripts/backup.sh
#!/bin/bash
#########################################################################
# File Name: baskup.sh
# Author: LookBack
# Email: admin#dwhd.org
# Version:
# Created Time: 2016年01月08日 星期五 05時55分05秒
#########################################################################
 
username=root
password=ZWE4MDM3NTgwNjc2
bindir="/usr/local/mariadb/bin"
 
${bindir}/mysql -u${username} -p${password} -e "GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456';flush privileges;"
${bindir}/mysql -u${username} -p${password} -e "set global event_scheduler=0;"
${bindir}/mysql -u${username} -p${password} -e "set global innodb_support_xa=0;"
${bindir}/mysql -u${username} -p${password} -e "set global sync_binlog=0;"
${bindir}/mysql -u${username} -p${password} -e "set global innodb_flush_log_at_trx_commit=0;"

配置master02上的backup.sh腳本

[root@DS-CentOS60 ~]# cat /etc/keepalived/scripts/backup.sh
#!/bin/bash
#########################################################################
# File Name: backup.sh
# Author: LookBack
# Email: admin#dwhd.org
# Version:
# Created Time: 2016年01月08日 星期五 06時11分31秒
#########################################################################
 
username=root
password=ZmE1MmYyZTIxNjU4
bindir="/usr/local/mariadb/bin"
 
${bindir}/mysql -u${username} -p${password} -e "GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '123456';flush privileges;"
${bindir}/mysql -u${username} -p${password} -e "set global event_scheduler=0;"
${bindir}/mysql -u${username} -p${password} -e "set global innodb_support_xa=0;"
${bindir}/mysql -u${username} -p${password} -e "set global sync_binlog=0;"
${bindir}/mysql -u${username} -p${password} -e "set global innodb_flush_log_at_trx_commit=0;"

配置master01上的stop.sh腳本

[root@DS-CentOS51 ~]# cat /etc/keepalived/scripts/stop.sh
#!/bin/bash
#########################################################################
# File Name: /etc/keepalived/scripts/stop.sh
# Author: LookBack
# Email: admin#dwhd.org
# Version:
# Created Time: 2016年01月08日 星期五 06時13分20秒
#########################################################################
 
username=root
password=ZWE4MDM3NTgwNjc2
bindir="/usr/local/mariadb/bin"
 
${bindir}/mysql -u${username} -p${password} -e "GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '1q2w3e4r';flush privileges;"
${bindir}/mysql -u${username} -p${password} -e "set global innodb_support_xa=1;"
${bindir}/mysql -u${username} -p${password} -e "set global sync_binlog=1;"
${bindir}/mysql -u${username} -p${password} -e "set global innodb_flush_log_at_trx_commit=1;"
 
M_File1=$(${bindir}/mysql -u${username} -p${password} -e "show master status\G" | awk '/File/{print $2}')
M_Position1=$(${bindir}/mysql -u${username} -p${password} -e "show master status\G" | awk '/Position/{print $2}')
sleep 1
M_File2=$(${bindir}/mysql -u${username} -p${password} -e "show master status\G" | awk '/File/{print $2}')
M_Position2=$(${bindir}/mysql -u${username} -p${password} -e "show master status\G" | awk '/Position/{print $2}')
 
while :; do
    [ "$M_File1" = "$M_File1" ] && [[ "$M_Position1" -eq "$M_Position2" ]] && { echo "ok" && break; }
    [ "$M_File1" != "$M_File1" ] || [[ "$M_Position1" -ne "$M_Position2" ]] && {
        sleep 10
        [[ "$i" -gt "60" ]] && break
        let i+=10 && continue; }
done

配置master02上的stop.sh腳本

[root@DS-CentOS60 ~]# cat /etc/keepalived/scripts/stop.sh
#!/bin/bash
#########################################################################
# File Name: stop.sh
# Author: LookBack
# Email: admin#dwhd.org
# Version:
# Created Time: 2016年01月08日 星期五 06時25分55秒
#########################################################################
 
username=root
password=ZmE1MmYyZTIxNjU4
bindir="/usr/local/mariadb/bin"
 
${bindir}/mysql -u${username} -p${password} -e "GRANT ALL PRIVILEGES ON *.* TO 'admin'@'%' IDENTIFIED BY '1q2w3e4r';flush privileges;"
${bindir}/mysql -u${username} -p${password} -e "set global innodb_support_xa=1;"
${bindir}/mysql -u${username} -p${password} -e "set global sync_binlog=1;"
${bindir}/mysql -u${username} -p${password} -e "set global innodb_flush_log_at_trx_commit=1;"
 
M_File1=$(${bindir}/mysql -u${username} -p${password} -e "show master status\G" | awk '/File/{print $2}')
M_Position1=$(${bindir}/mysql -u${username} -p${password} -e "show master status\G" | awk '/Position/{print $2}')
sleep 1
M_File2=$(${bindir}/mysql -u${username} -p${password} -e "show master status\G" | awk '/File/{print $2}')
M_Position2=$(${bindir}/mysql -u${username} -p${password} -e "show master status\G" | awk '/Position/{print $2}')
 
while :; do
    [ "$M_File1" = "$M_File1" ] && [[ "$M_Position1" -eq "$M_Position2" ]] && { echo "ok" && break; }
    [ "$M_File1" != "$M_File1" ] || [[ "$M_Position1" -ne "$M_Position2" ]] && {
        sleep 10
        [[ "$i" -gt "60" ]] && break
        let i+=10 && continue; }
done

設置master01 02上Keepalived腳本有執行權限

[root@DS-CentOS51 ~]# chmod +x /etc/keepalived/scripts/*.sh
[root@DS-CentOS51 ~]# ssh root@mysql-master02 "chmod +x /etc/keepalived/scripts/*.sh && ls -l /etc/keepalived/scripts/"
總用量 16
-rwxr-xr-x 1 root root  833 1月   8 06:12 backup.sh
-rwxr-xr-x 1 root root 1826 1月   8 06:28 master.sh
-rwxr-xr-x 1 root root  764 1月   8 06:31 mysql_check.sh
-rwxr-xr-x 1 root root 1467 1月   8 06:26 stop.sh
[root@DS-CentOS51 ~]#