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

评论
11,213 次

添加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 root@DS-CentOS51.dwhd.org
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 root@DS-CentOS60.dwhd.org
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) <centos-6-key@centos.org>
 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 root@DS-CentOS63.dwhd.org
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) <centos-6-key@centos.org>
 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 root@DS-CentOS69.dwhd.org
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) <centos-6-key@centos.org>
 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安装结束才可以继续)

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 {
        admin@dwhd.org
    }
 
    notification_email_from mysql-keepalived@ds.com
    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 {
		admin@dwhd.org
	}

	notification_email_from mysql-keepalived@ds.com
	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=MWI2ZjcxOTUyNDRk
replication_name="replication"
replication_pass="MDE5MTQzMWI3NDgz"
bindir="/usr/local/mariadb/bin"
slave_cluster=(mysql-slave01 mysql-slave02)
Mysql_VIP="172.16.51.100"
 
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;flush privileges;"
#${bindir}/mysql -u${username} -p${password} -e "show master status;" > /tmp/master_status_$(date "+%F-%T").txt
cat >>/tmp/master_status<<-EOF
	$(date "+%F-%T")
	$(${bindir}/mysql -u${username} -p${password} -e "show master status;" |awk 'NR>1')
	=========================================================

EOF

Local_Master_POS_file=$(${bindir}/mysql -u${username} -p${password} -e 'show master status\G'|awk '/File/{print $NF}')
Local_Master_POS_id=$(${bindir}/mysql -u${username} -p${password} -e 'show master status\G'|awk '/Pos/{print $NF}')

k=0 && j=0 && while :; do
	ssh root@${slave_cluster[$k]} "/bin/bash /script/change_master.sh ${Mysql_VIP} ${replication_name} ${replication_pass} ${Local_Master_POS_file} ${Local_Master_POS_id}"
	[ $? = 0 ] \
		&& { let k++ && [ $k = 2 ] && break; } \
		|| { [[ "$j" -lt 60 ]] && { sleep 3 && let j+=3; } || { echo "Email" && break; }; }
done

配置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
replication_name="replication"
replication_pass="MDE5MTQzMWI3NDgz"
bindir="/usr/local/mariadb/bin"
slave_cluster=(mysql-slave01 mysql-slave02)
Mysql_VIP="172.16.51.100"

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;flush privileges;"
#${bindir}/mysql -u${username} -p${password} -e "show master status;" > /tmp/master_status_$(date "+%F-%T").txt
cat >>/tmp/master_status<<-EOF
	$(date "+%F-%T")
	$(${bindir}/mysql -u${username} -p${password} -e "show master status;" |awk 'NR>1')
	=========================================================

EOF

Local_Master_POS_file=$(${bindir}/mysql -u${username} -p${password} -e 'show master status\G'|awk '/File/{print $NF}')
Local_Master_POS_id=$(${bindir}/mysql -u${username} -p${password} -e 'show master status\G'|awk '/Pos/{print $NF}')

k=0 && j=0 && while :; do
	ssh root@${slave_cluster[$k]} "/bin/bash /script/change_master.sh ${Mysql_VIP} ${replication_name} ${replication_pass} ${Local_Master_POS_file} ${Local_Master_POS_id}"
	[ $? = 0 ] \
		&& { let k++ && [ $k = 2 ] && break; } \
		|| { [[ "$j" -lt 60 ]] && { sleep 3 && let j+=3; } || { echo "Email" && break; }; }
done

配置slave01上的调度脚本

#!/bin/bash
#########################################################################
# File Name: /script/change_master.sh
# Author: LookBack
# Email: admin#dwhd.org
# Version:
# Created Time: 2016年01月15日 星期五 18时19分26秒
#########################################################################

bindir=/usr/local/mariadb/bin

${bindir}/mysql -e 'stop slave;'
${bindir}/mysql -e 'set global innodb_support_xa=0;'
${bindir}/mysql -e 'set global sync_binlog=0;'
${bindir}/mysql -e 'set global innodb_flush_log_at_trx_commit=0;'
${bindir}/mysql -e "CHANGE MASTER TO MASTER_HOST='$1', MASTER_PORT=3306, MASTER_USER='$2', MASTER_PASSWORD='$3', MASTER_LOG_FILE='$4', MASTER_LOG_POS=$5;"
${bindir}/mysql -e 'flush logs;flush privileges;'
${bindir}/mysql -e 'start slave;'
[[ "$(${bindir}/mysql -e 'show slave STATUS\G' | awk '$1~/^Slave_...?_Running/&&$NF=="Yes"' | wc -l)" = "2" ]] && exit 0 || exit 1

配置slave02上的调度脚本

#!/bin/bash
#########################################################################
# File Name: /script/change_master.sh
# Author: LookBack
# Email: admin#dwhd.org
# Version:
# Created Time: 2016年01月15日 星期五 18时19分26秒
#########################################################################

bindir=/usr/local/mariadb/bin

${bindir}/mysql -e 'stop slave;'
${bindir}/mysql -e 'set global innodb_support_xa=0;'
${bindir}/mysql -e 'set global sync_binlog=0;'
${bindir}/mysql -e 'set global innodb_flush_log_at_trx_commit=0;'
${bindir}/mysql -e "CHANGE MASTER TO MASTER_HOST='$1', MASTER_PORT=3306, MASTER_USER='$2', MASTER_PASSWORD='$3', MASTER_LOG_FILE='$4', MASTER_LOG_POS=$5;"
${bindir}/mysql -e 'flush logs;flush privileges;'
${bindir}/mysql -e 'start slave;'
[[ "$(${bindir}/mysql -e 'show slave STATUS\G' | awk '$1~/^Slave_...?_Running/&&$NF=="Yes"' | wc -l)" = "2" ]] && exit 0 || exit 1

配置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 "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 "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 "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 "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 ~]#