初识MariaDB之9——keepalive+GTID双主实现高可用
一、背景介绍
创新互联成立于2013年，先为顺河等服务建站，顺河等地企业，进行企业商务咨询服务。为顺河企业网站制作PC+手机+微官网三网同步一站式服务解决您的所有建站问题。
传统的主从复制架构下，尽管可以使用半同步的方式进行读写分离，但是主节点仍然存在单点隐患，在规模不大的情况下可以采用keepalive+双主的模式对主节点进行高可用保护，客户端通过VIP访问MySQL服务器
二、实现方式
keepalive是通过vrrp协议实现，之前有过介绍这里不多赘述，要注意的是在云主机是禁用vrrp协议的。本次实验采用CentOS7.4，数据库版本为MariaDB-10.2.14，2台MySQL服务器互为主从，172.16.10.30/24为keepalived服务的Master主机，172.16.10.40/24为keepalived服务的Backup主机，可以使用半同步的方式保证数据一致性，缺点是始终有一个服务器处于待机状态
三、实验目的
采用keepalived+双主模型对MySQL服务器做高可用，模拟A主机宕机时B主机继续对外提供服务，当A主机上线后，重新成为Master节点
四、操作步骤
1.将MasterA与MasterB互为主从
（1）编辑AB主机配置文件并启动MySQL服务
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
skip_name_resolve=ON
relay_log=mysql-relaylog
relay_log_index=mysql-relaylog
relay_log_purge=OFF
slow_query_log=ON
server-id=10
innodb_file_per_table=ON
binlog_format=ROW
log_bin=mysql-binlog
log_slave_updates=ON
gtid_strict_mode=ON
（2）在A主机上创建复制账号并导入数据库
MariaDB [(none)]> grant replication slave on *.* to 'bak'@'172.16.10.%' identified by 'bakpassword';
MariaDB [(none)]> flush privileges;
MariaDB [(none)]> source /root/hellodb.sql;
MariaDB [hellodb]> show global variables like 'gtid%';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| gtid_binlog_pos | 0-10-37 |
| gtid_binlog_state | 0-10-37 |
| gtid_current_pos | 0-10-37 |
| gtid_domain_id | 0 |
| gtid_ignore_duplicates | OFF |
| gtid_slave_pos | |
| gtid_strict_mode | ON |
+------------------------+---------+
（3）将B主机的Master指向A主机
MariaDB [(none)]> CHANGE MASTER TO master_host='172.16.10.30', master_port=3306, master_user='bak', master_password='bakpassword',master_use_gtid=current_pos;
MariaDB [(none)]> start slave;
MariaDB [(none)]> show global variables like 'gtid%';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| gtid_binlog_pos | 0-10-37 |
| gtid_binlog_state | 0-10-37 |
| gtid_current_pos | 0-10-37 |
| gtid_domain_id | 0 |
| gtid_ignore_duplicates | OFF |
| gtid_slave_pos | 0-10-37 |
| gtid_strict_mode | ON |
+------------------------+---------+
（4）将A主机的Master指向B主机
MariaDB [(none)]> CHANGE MASTER TO master_host='172.16.10.40', master_port=3306, master_user='bak', master_password='bakpassword',master_use_gtid=current_pos;
MariaDB [(none)]> start slave;
此时双主模型已经构建完成，主机B之所以不用创建复制账号是因为已将A创建账号是的语句同步了过来
2.安装配置keepalived
（1）AB主机安装keepalived
（2）编辑A主机的故障转移脚本
[root@host3 ~]# vim /etc/keepalived/chk_mysql.sh
#!/bin/bash
mysqlStr=/usr/bin/mysql
hostIP=172.16.10.30
chkUser=chk
chkPassword=chkpassword
mysqlPort=3306
$mysqlStr -h$hostIP -u$chkUser -p$chkPassword -P$mysqlPort -e "show global variables like '%gtid%';" > /dev/null 2>&1
if [ $? != 0 ];then
/usr/bin/systemctl stop keepalived.service
fi
（3）编辑A主机配置文件，启动服务，让A成为对外提供服务的主机
[root@host3 ~]# vim /etc/keepalived/keepalived.conf
vrrp_script chk_mysql {
script "/etc/keepalived/chk_mysql.sh"
interval 10
}
vrrp_instance VI_1 {
state MASTER
interface ens32
virtual_router_id 100
priority 100
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.16.10.100
}
track_script {
chk_mysql
}
}
[root@host3 ~]# systemctl start keepalived.service
（4）编辑A主机配置文件，启动服务
[root@host4 ~]# vim /etc/keepalived/keepalived.conf
vrrp_instance VI_1 {
state BACKUP
nopreempt
interface ens32
virtual_router_id 100
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.16.10.100
}
}
[root@host4 ~]# systemctl start keepalived.service
五、效果验证
（1）在AB任意主机上创建一个可以远程管理的账号
MariaDB [hellodb]> grant all on *.* to 'chk'@'172.16.10.%' identified by 'chkpassword';
MariaDB [hellodb]> flush privileges;
（2）再使用另一个主机通过VIP发现当前gtid_binlog_pos在B主机上，说明之前对外提供服务的是B主机
[root@host5 ~]# mysql -h272.16.10.100 -uchk -pchkpassword -P3306 -e"show global variables like '%gtid%';"
+------------------------+-----------------+
| Variable_name | Value |
+------------------------+-----------------+
| gtid_binlog_pos | 0-20-42 |
| gtid_binlog_state | 0-10-40,0-20-42 |
| gtid_current_pos | 0-20-42 |
| gtid_domain_id | 0 |
| gtid_ignore_duplicates | OFF |
| gtid_slave_pos | 0-20-42 |
| gtid_strict_mode | ON |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+-----------------+
（3）从远程节点通过VIP对数据库执行任意DML操作后，发现gtid_binlog_pos回到了A主机，说明A主机已持有VIP
[root@host5 ~]# mysql -h272.16.10.100 -uchk -pchkpassword -P3306 -e"delete from hellodb.students where stuid=11;"
[root@host5 ~]# mysql -h272.16.10.100 -uchk -pchkpassword -P3306 -e"show global variables like '%gtid%';"
+------------------------+-----------------+
| Variable_name | Value |
+------------------------+-----------------+
| gtid_binlog_pos | 0-10-43 |
| gtid_binlog_state | 0-20-42,0-10-43 |
| gtid_current_pos | 0-10-43 |
| gtid_domain_id | 0 |
| gtid_ignore_duplicates | OFF |
| gtid_slave_pos | 0-10-43 |
| gtid_strict_mode | ON |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+-----------------+
（4）停止A主机MySQL服务，模拟故障，再通过VIP执行DML操作后发现gtid_binlog_pos回到了B主机，此时B主机对外提供服务，故障已切换
[root@host3 ~]# systemctl stop keepalived.service
[root@host5 ~]# mysql -h272.16.10.100 -uchk -pchkpassword -P3306 -e"delete from hellodb.students where stuid=7;"
[root@host5 ~]# mysql -h272.16.10.100 -uchk -pchkpassword -P3306 -e"show global variables like '%gtid%';"
+------------------------+-----------------+
| Variable_name | Value |
+------------------------+-----------------+
| gtid_binlog_pos | 0-20-45 |
| gtid_binlog_state | 0-10-44,0-20-45 |
| gtid_current_pos | 0-20-45 |
| gtid_domain_id | 0 |
| gtid_ignore_duplicates | OFF |
| gtid_slave_pos | 0-20-45 |
| gtid_strict_mode | ON |
| wsrep_gtid_domain_id | 0 |
| wsrep_gtid_mode | OFF |
+------------------------+-----------------+
（5）重启A主机MySQL服务，再重启keepalived服务，A主机重新持有VIP，此时再通过VIP执行DML操作后发现gtid_binlog_pos回到A主机，说明A主机重新持有VIP，至此所有操作完成
补充说明：
以本文为例，当A主机的MySQLd服务停止后，其keepalived服务也会跟着停止，重启MySQLd服务，keepalived服务并不会跟着启动，必须手动启动，否则主机A将无法持有VIP
网站名称：初识MariaDB之9——keepalive+GTID双主实现高可用
网站URL：http://scjbc.cn/article/gspohs.html
