通常说的双机热备是指两台机器都在运行,但并不是两台机器都同时提供服务。当提供服务的一台出现故障时,另外一台能够马上自动接管并且提供服务,而且切换的时间非常短。MySQL双主复制,即互为master-slave(只有一个master提供写操作),可以实现数据库服务器的热备,但是一个master宕机后不能实现自动切换。就要用到keepalived,可以通过虚拟IP,实现双主对外的统一接口以及自动检查、失败切换机制,从而实现MySQL数据库的高可用方案。下面说下MySQL+keepalived双主热备高可用方案的实施。
- 先实施master-slave的主主同步。主主是数据双向同步,主从是数据单向同步。一般情况下,主库宕机后,需要手动将连接切换到从库上。(但是用keepalived可以自动切换)
- 结合keepalived的使用,通过VIP实现MySQL双主对外连接的统一接口。即客户端通过VIP连接数据库,当其中一台主服务器宕机后,VIP会切换到另外一台上,这个过程对于客户端的数据连接几乎没有感觉,从而实现高可用。
架构图
双主高可用GTID模式实战
#前文已经有二进制安装的文档,本文档不再从新安装,如果没有安装请点击:二进制安装
一、环境要求
IP地址 | 主机名 |
---|---|
192.168.10.143 | master |
192.168.10.201 | backup |
二、启动主主复制(GTID)
[root@master ~]# mkdir -p /usr/local/mysql/binlog
[root@backup ~]# mkdir -p /usr/local/mysql/binlog
#master
[root@master ~]# vi /etc/my.cnf
[mysqld]
user=mysql
bind-address=0.0.0.0
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
server_id=1
log_bin=/usr/local/mysql/binlog/mysql-bin
binlog_format=row
sync_binlog=1
gtid_mode=on
enforce_gtid_consistency
[mysql]
socket=/tmp/mysql.sock
#--------------------------------------------------------------#
#backup
[root@backup ~]# vi /etc/my.cnf
[mysqld]
user=mysql
bind-address=0.0.0.0
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/tmp/mysql.sock
server_id=2
log_bin=/usr/local/mysql/binlog/mysql-bin
binlog_format=row
sync_binlog=1
gtid_mode=on
enforce_gtid_consistency
[mysql]
socket=/tmp/mysql.sock
授权
#master
[root@master ~]# chown -R mysql.mysql /usr/local/mysql/
#backup
[root@backup ~]# chown -R mysql.mysql /usr/local/mysql/
重启MySQL
#master
[root@master ~]# systemctl restart mysqld.service
#backup
[root@backup ~]# systemctl restart mysqld.service
创建主主同步用户
#master
[root@master ~]# mysql -uroot -p000000 -e "grant replication slave on *.* to giao@'%' identified by '000000';"
#backup
[root@backup ~]# mysql -uroot -p000000 -e "grant replication slave on *.* to giao@'%' identified by '000000';"
开启主主GTID模式同步
[root@master ~]# mysql -uroot -p000000
#master
mysql> change master to master_host='192.168.10.201',master_user='giao',master_password='000000',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
mysql> start slave;
Query OK, 0 rows affected (0.05 sec)
mysql> exit
Bye
#----------------------------------------------------------------
#backup
[root@backup ~]# mysql -uroot -p000000
mysql> change master to master_host='192.168.10.143',master_user='giao',master_password='000000',master_auto_position=1;
Query OK, 0 rows affected, 2 warnings (0.06 sec)
mysql> start slave;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
查看主主复制状态
#master
[root@master ~]# mysql -uroot -p000000 -e "show slave status\G;" |egrep 'Slave_IO_Running:|Slave_SQL_Running:|Seconds_Behind_Master:'
mysql: [Warning] Using a password on the command line interface can be insecure.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
#backup
[root@backup ~]# mysql -uroot -p000000 -e "show slave status\G;" |egrep 'Slave_IO_Running:|Slave_SQL_Running:|Seconds_Behind_Master:'
mysql: [Warning] Using a password on the command line interface can be insecure.
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0
创建MySQL远程连接用户
[root@master ~]# mysql -uroot -p000000 -e "grant all on *.* to 'ceshi'@'%' identified by '000000';"
#在master上创建用户就可以,会自动同步用户到backup上去。
三、部署keepalived高可用
#master
[root@master ~]# yum install -y keepalived
#backup
[root@backup ~]# yum install -y keepalived
编辑master主机配置文件
#配置keepalived为非抢占模式
[root@master ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
1035587908@qq.com
}
notification_email_from 1035587908@qq.com
smtp_server 192.168.10.254
smtp_connect_timeout 30
router_id master
}
vrrp_instance giao {
state BACKUP
nopreempt
interface ens32
virtual_router_id 55
priority 150
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.10.200/24
}
}
编辑backup主机配置文件
#配置keepalived为非抢占模式
[root@backup ~]# cat /etc/keepalived/keepalived.conf
! Configuration File for keepalived
global_defs {
notification_email {
1035587908@qq.com
}
notification_email_from 1035587908@qq.com
smtp_server 192.168.10.254
smtp_connect_timeout 30
router_id backup
}
vrrp_instance giao {
state BACKUP
nopreempt
interface ens32
virtual_router_id 55
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
192.168.10.200/24
}
}
启动keepalived
#master与backup执行以下命令启动keepalived
[root@master ~]# systemctl start keepalived
[root@master ~]# systemctl enable keepalived
查看master主机的VIP地址
#可以看到在ens32网卡上多一个192.168.10.200的地址
[root@master ~]# ip a
1: lo: <LOOPBACK,UP,LOWER_UP> mtu 65536 qdisc noqueue state UNKNOWN group default qlen 1000
link/loopback 00:00:00:00:00:00 brd 00:00:00:00:00:00
inet 127.0.0.1/8 scope host lo
valid_lft forever preferred_lft forever
inet6 ::1/128 scope host
valid_lft forever preferred_lft forever
2: ens32: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 00:0c:29:05:c4:56 brd ff:ff:ff:ff:ff:ff
inet 192.168.10.143/24 brd 192.168.10.255 scope global noprefixroute ens32
valid_lft forever preferred_lft forever
inet 192.168.10.200/24 scope global secondary ens32
valid_lft forever preferred_lft forever
inet6 fe80::2bfa:f690:727d:f130/64 scope link noprefixroute
valid_lft forever preferred_lft forever
四、测试
远程连接Mysql查看server_id
[root@backup ~]# mysql -uceshi -p000000 -h 192.168.10.200 -e "select @@server_id;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
关闭master主机的keepalived服务
#在backup主机执行,同样的命令获取不同的server_id值说明连接的不是同一个MySQL数据库
[root@master ~]# systemctl stop keepalived
[root@backup ~]# mysql -uceshi -p000000 -h 192.168.10.200 -e "select @@server_id;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
五、编写MySQL检测存活脚本
脚本作用:当其中一台主机的MySQL宕机,脚本会检测到3306端口被关闭,然后关闭keepalived服务,让VIP切换到另外一台主机,从而实现MySQL的高可用性。
#master
[root@master ~]# vim /usr/bin/mysql_keepalived.sh
#!/bin/bash
while true;do
port_num=`netstat -ntpl|grep -c 3306`
if [ ${port_num} -ne 1 ];then
systemctl stop keepalived
break
else
echo '' > /dev/null
fi
sleep 3
done
[root@master ~]# chmod +x /usr/bin/mysql_keepalived.sh
[root@master ~]# sh /usr/bin/mysql_keepalived.sh &
#backup
[root@backup ~]# vim /usr/bin/mysql_keepalived.sh
#!/bin/bash
while true;do
port_num=`netstat -ntpl|grep -c 3306`
if [ ${port_num} -ne 1 ];then
systemctl stop keepalived
break
else
echo '' > /dev/null
fi
sleep 3
done
[root@backup ~]# chmod +x /usr/bin/mysql_keepalived.sh
[root@backup ~]# sh /usr/bin/mysql_keepalived.sh &
模拟MySQL挂掉自动切换VIP
当前环境是两台主机的MySQL与keepalived都在正常运行,VIP在backup主机上。然后关闭backup主机上的MySQL服务(不要手动关闭keepalived服务,脚本会自动关闭)
[root@backup ~]# mysql -uceshi -p000000 -h 192.168.10.200 -e "select @@server_id;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 2 |
+-------------+
[root@backup ~]# systemctl stop mysqld.service
[root@backup ~]# mysql -uceshi -p000000 -h 192.168.10.200 -e "select @@server_id;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
注意事项:
当backup主机MySQL服务关闭后VIP切换到master主机上,要再次启动master主机服务器时要依次启动:
- systemctl start mysqld
- systemctl start keepalived
- sh /usr/bin/mysql_keepalived.sh &
keepalived的抢占和非抢占模式
- keepalived是基于vrrp协议在linux主机上以守护进程方式,根据配置文件实现健康检查
- VRRP是一种路由选择协议,它可以把一个虚拟路由器的责任动态分配到局域网上的VRRP路由器中的一台
- 控制虚拟路由器IP地址的VRRP路由器成为主路由器,它负责转发数据包到这些虚拟IP地址
- 一旦主路由器不可用,这种选择过程就提供了动态的故障转移机制,这就允许虚拟路由器的IP地址可以作为终端主机的默认第一路由器
- keepalived通过组播、单播等方式(自定义),实现keepalived主备选举。工作模式分为抢占和非抢占(通过参数nopreempt来控制)
- 抢占模式:
- 主服务正常工作时,虚拟IP会在主上,备不提供服务,当主服务优先级低于备的时候,备会自动抢占虚拟IP,这时,主不提供服务,备提供服务。
- 也就说,工作在抢占模式下,不分主备,只管优先级。
如上配置,不管keepalived.conf里的state配置成master还是backup,只看谁的priority优先级高(一般情况下,state为MASTER的优先级要高于BACKUP)。
- 非抢占模式:
- 这种方式通过参数nopreempt(一般设置在advert_int的那一行下面)来控制。不管priority优先级,只要MASTER机器发生故障,VIP资源就会被切换到BACKUP上。
- 并且当MASTER机器恢复后,也不会去将VIP资源抢占回来,直至BACKUP机器发生故障,才能自动切换回来。
千万注意:
- nopreempt这个参数只能用于state为backup的情况,所以在配置的时候要把master和backup的state都设置成backup,这样才会实现keepalived的非抢占模式!
也就是说:
- 当state状态一个为master,一个backup的时候,加不加nopreempt这个参数都是一样的效果。即都是根据priority优先级来决定谁抢占vip资源的,是抢占模式!
- 当state状态都设置成backup,如果不配置nopreempt参数,那么也是看priority优先级决定谁抢占VIP资源,即也是抢占模式。
- 当state状态都设置成backup,如果配置nopreempt参数,那么就不会去考虑priority优先级了,是非抢占模式!即只有VIP当前所在机器发生故障,另一台机器才能接管VIP,即使优先级高的那一台机器恢复,也不会主动抢回VIP,只能等对方发生故障,才会将VIP切回来。