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 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脚本
<table border="0" cellspacing="0" cellpadding="0">
<tbody>
<tr>
<td>
<td>
sslave01、slave02上安装MariaDB，和Master02上一样，由于这里是自动安装就不多演示了
<fck:hr>Master01上安装Keepalived</fck:hr>
<fck:hr>[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</fck:hr>
<fck:hr>Master02上安装Keepalived</fck:hr>
<fck:hr>
[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</fck:hr>
<fck:hr>配置Master01上的Keepalived</fck:hr>
<fck:hr>
[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 ~]#</fck:hr>
<fck:hr>配置Master02上的Keepalived</fck:hr>
<fck:hr>[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 ~]#</fck:hr>
<fck:hr>配置Master01上的mysql_check.sh</fck:hr>
<fck:hr>[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 ~]#</fck:hr>
<fck:hr>配置Master02上的mysql_check.sh</fck:hr>
<fck:hr>
[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</fck:hr>
<fck:hr>配置Master01上的master.sh脚本</fck:hr>
<fck:hr>[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脚本</fck:hr>
<fck:hr>[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</fck:hr>
<fck:hr>配置master01上的baskup.sh脚本</fck:hr>
<fck:hr>[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;"</fck:hr>
<fck:hr>配置master02上的backup.sh脚本</fck:hr>
<fck:hr>[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;"</fck:hr>
<fck:hr>配置master01上的stop.sh脚本</fck:hr>
<fck:hr>[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</fck:hr>
<fck:hr>配置master02上的stop.sh脚本</fck:hr>
<fck:hr>
[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</fck:hr>
<fck:hr>设置master01 02上Keepalived脚本有执行权限</fck:hr>
<fck:hr>
[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 ~]#</fck:hr>
您可能感兴趣的文章：
Linux高可用(HA)之MySQL多主一从 Keepalived跨机房集群部署
haproxy+keepalived负载均衡之主备切换(centos)
java中redis主-主实现方案
Linux高可用(HA)之Corosync Pacemaker DRBD MySQL/MariaDB实现高可用MySQ/MariaDB集群
支持多语言的微服务框架Tars-Go
MySQL双机高可用的负载均衡(读写分离、主从自动切换)架构设计
MySQL主从服务器配置的一些总结
mha是python写的吗
Linux高可用(HA)之Heartbeat Nginx MySQL NFS实现WEB SQL服务高可用
15k的php需要掌握什么技术