版权声明:本文为博主原创文章,未经博主允许不得转载。 https://cloud.tencent.com/developer/article/1433049
目录
一、架构设计
二、安装配置
1. 配置MySQL半同步复制
1.1半同步复制的基本概念
1.2半同步复制的潜在问题
1.3 半同步复制的安装部署
2. 下载安装LVS
3. 下载安装Keepalived
4. iptables配置
5. Keepalived配置
5. 编写RealServer的网络配置脚本
6. 启动RealServer和Keepalived
三、测试
参考:
通过前面两个实验,已经基本了解了Keepalived及LVS的工作原理。本篇我们要进行一个有别于前面的新实验:使用Keepalived+LVS+MySQL主从复制实现读写分离及高可用。
具体架构如图1所示。
图1
从图1中看到,使用两台主机做MySQL主从复制,实现读写分离,用于提高查询性能。采用MySQL 5.6.x的半同步实现数据复制和同步。使用Keepalived来监控MySQL,并提供读写VIP漂移。Keepalived在这里主要用作RealServer的健康状态检查以及LoadBalance主机和Backup主机之间failover的实现。任何一台主机宕机都不会影响对外提供服务(读写VIP可以漂移),保持MySQL数据库服务的高可用性。
Keepalived基于VRRP协议来实现高可用解决方案,利用其避免单点故障。通常这个解决方案中,至少有2台服务器运行Keepalived,一台为Master,另一台为Backup,但对外表现为一个或一组VIP。Master会发送特定消息给Backup,当Backup收不到该消息时,则认为Master出现故障,Backup会接管VIP,继续提供服务,从而保证了高可用性。LVS在本例的作用是提供读负载均衡。整体架构的设计原理和异常处理可描述如下:
环境:
172.16.1.126:Keepalived + LVS Master + MySQL Semisync-Replication Master
172.16.1.127:Keepalived + LVS Backup + MySQL Semisync-Replication Slave
172.16.1.100:R-VIP
172.16.1.210:W-VIP
从MySQL5.5开始,MySQL以插件的形式支持半同步复制。如何理解半同步呢?首先我们来看看MySQL中异步、全同步和半同步复制的概念。
图2是半同步复制的原理图:
图2
假设客户端事务在存储引擎层提交后,在得到从库确认的过程中,主库宕机了。此时,可能存在以下两种情况:
1. 先决条件
要想使用半同步复制,必须满足以下几个条件:
2. 加载插件
因用户需执行INSTALL PLUGIN, SET GLOBAL, STOP SLAVE和START SLAVE操作,所以用户需有SUPER权限。在172.16.1.126和172.16.1.127上执行:
install plugin rpl_semi_sync_master soname 'semisync_master.so';
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
这里考虑到以后修复问题后主从可能角色互换,所以在两个MySQL主机上都加载了半同步的两个插件。查看插件是否加载成功:
mysql> select plugin_name, plugin_status from information_schema.plugins where plugin_name like '%semi%';
+----------------------+---------------+
| plugin_name | plugin_status |
+----------------------+---------------+
| rpl_semi_sync_master | ACTIVE |
| rpl_semi_sync_slave | ACTIVE |
+----------------------+---------------+
2 rows in set (0.00 sec)
3. 启动半同步复制
(1)准备配置文件
172.16.1.126(master)上的my.cnf文件内容如下:
[mysqld]
basedir = /home/mysql/mysql-5.6.14
datadir=/data
skip-name-resolve
user=mysql
binlog_format=row
default-storage-engine=InnoDB
transaction_isolation = READ-COMMITTED
log-bin=/data/mysql-bin
log-bin-index = /data/mysql-bin.index
tmpdir = /data
server-id = 126
innodb_data_home_dir = /data
innodb_log_group_home_dir=/data
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
172.16.1.127(slave)上的my.cnf文件内容如下:
[mysqld]
basedir = /home/mysql/mysql-5.6.14
datadir=/data
skip-name-resolve
user=mysql
binlog_format=row
default-storage-engine=InnoDB
transaction_isolation = READ-COMMITTED
log-bin=/data/mysql-bin
log-bin-index = /data/mysql-bin.index
tmpdir = /data
server-id = 127
innodb_data_home_dir = /data
innodb_log_group_home_dir=/data
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
plugin-load=rpl_semi_sync_master=semisync_master.so
rpl_semi_sync_master_enabled=1
read-only # 设为只读模式,只能从master同步,不能直接写入
(2)重启主、从库
service mysql restart
(3)在主库上授权
grant replication slave on *.* to 'repl'@'%' identified by '123456';
reset master;
show master status;
(4)在从库上启动复制
change master to
master_host='172.16.1.126',
master_port=3306,
master_user='repl',
master_password='123456',
master_log_file='mysql-bin.000001',
master_log_pos=120;
start slave;
show slave status \G
(5)测试
在主上建立测试表,插入数据。
mysql> use test;
Database changed
mysql> create table t1 (a int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t1 values (1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
在从上查询。
mysql> select * from test.t1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
在从上执行数据更新则报错。
mysql> delete from test.t1;
ERROR 1290 (HY000): The MySQL server is running with the --read-only option so it cannot execute this statement
至此半同步复制配置完成。
在172.16.1.126和172.16.1.127上用root用户执行以下命令:
yum -y install ipvsadm
在172.16.1.126和172.16.1.127上安装Keepalived,详细步骤参见“[使用Keepalived实现MySQL主从高可用](https://blog.csdn.net/wzy0623/article/details/80916567)”。
在本架构中,由于要实现MySQL读写主机VIP的漂移,Keepalived、LVS和MySQL需要部署到相同主机上。当LVS架构中的director与realserver是同一台机器时,会出现两台director无限循环转发请求的情况。具体分析如下:
解决办法是,director使用iptables,对数据包做标记,LVS对fwm转发而不是直接转发TCP。在每台director上,对非其它director发送的数据包,做标记,ip\_vs转发这些被标记的包。亦即其它director发送的数据包,不再做ip\_vs转发,直接交给上层监听程序。
在172.16.1.126(主库)上执行:
iptables -t mangle -I PREROUTING -d 172.16.1.100 -p tcp -m tcp --dport 3306 -m mac ! --mac-source 00:50:56:a5:49:7f -j MARK --set-mark 0x1
在172.16.1.127(备库)上执行:
iptables -t mangle -I PREROUTING -d 172.16.1.100 -p tcp -m tcp --dport 3306 -m mac ! --mac-source 00:50:56:a5:0f:77 -j MARK --set-mark 0x2
其中172.16.1.100是读VIP,3306是VPORT,00:50:56:a5:49:7f是172.16.1.127主机网卡的MAC地址,00:50:56:a5:0f:77是172.16.1.126主机网卡的MAC地址。
172.16.1.126初始为keepalived的Master,其上的keepalived配置文件如下:
[root@hdp3~]#more /etc/keepalived/keepalived.conf
global_defs {
router_id LVS_DEVEL
}
vrrp_sync_group VG1 {
group {
VI_1
}
}
vrrp_instance VI_1 {
state BACKUP
interface ens32
virtual_router_id 51
priority 100
notify_master "/home/mysql/remove_slave.sh"
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1234
}
virtual_ipaddress {
172.16.1.100
172.16.1.210
}
}
# 写VIP virtual_server,只配置本地机器
virtual_server 172.16.1.210 3306 {# 定义虚拟服务器,地址与上面的virtual_ipaddress相同
delay_loop 3 # 健康检查时间间隔,3秒
lb_algo rr # 负载均衡调度算法:rr|wrr|lc|wlc|sh|dh|lblc
lb_kind DR # 负载均衡转发规则:NAT|DR|TUN
# persistence_timeout 5 # 会话保持时间5秒,动态服务建议开启
protocol TCP # 转发协议protocol,一般有tcp和udp两种
real_server 172.16.1.126 3306 {
weight 1 # 权重越大负载分越大,0表示失效
notify_down /home/mysql/mysql_down.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
# 读VIP virtual_server,配置fwmark转发
virtual_server fwmark 1 {
delay_loop 3
lb_algo rr
lb_kind DR
# persistence_timeout 5
protocol TCP
real_server 172.16.1.126 3306 {
weight 1
notify_down /home/mysql/mysql_down.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
real_server 172.16.1.127 3306 {
weight 1
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
[root@hdp1~]#
R-VIP绑定172.16.1.126、172.16.1.127,W-VIP绑定172.16.1.126。
172.16.1.127初始为keepalived的Backup,其上的keepalived配置文件如下:
[root@hdp4~]#more /etc/keepalived/keepalived.conf
global_defs {
router_id LVS_DEVEL
}
vrrp_sync_group VG1 {
group {
VI_1
}
}
vrrp_instance VI_1 {
state BACKUP
interface ens160
virtual_router_id 51
priority 90
notify_master "/home/mysql/remove_slave.sh"
advert_int 1
nopreempt
authentication {
auth_type PASS
auth_pass 1234
}
virtual_ipaddress {
172.16.1.100
172.16.1.210
}
}
# 写VIP virtual_server,只配置本地机器
virtual_server 172.16.1.210 3306 {# 定义虚拟服务器,地址与上面的virtual_ipaddress相同
delay_loop 3 # 健康检查时间间隔,3秒
lb_algo rr # 负载均衡调度算法:rr|wrr|lc|wlc|sh|dh|lblc
lb_kind DR # 负载均衡转发规则:NAT|DR|TUN
# persistence_timeout 5 # 会话保持时间5秒,动态服务建议开启
protocol TCP # 转发协议protocol,一般有tcp和udp两种
real_server 172.16.1.127 3306 {
weight 1 # 权重越大负载分越大,0表示失效
notify_down /home/mysql/mysql_down.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
# 读VIP virtual_server,配置fwmark转发
virtual_server fwmark 2 {
delay_loop 3
lb_algo rr
lb_kind DR
# persistence_timeout 5
protocol TCP
real_server 172.16.1.126 3306 {
weight 1
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
real_server 172.16.1.127 3306 {
weight 1
notify_down /home/mysql/mysql_down.sh
TCP_CHECK {
connect_timeout 3
nb_get_retry 3
delay_before_retry 3
connect_port 3306
}
}
}
[root@hdp4~]#
Master与Backup的keepalived配置文件中有以下四点不同:
配置文件中各段及其参数的配置说明参见“[Keepalived+LVS+MySQL双主复制实现读写负载均衡及高可用](https://blog.csdn.net/wzy0623/article/details/81015707)”。
/home/mysql/mysql\_down.sh文件内容如下:
#!/bin/bash
/etc/init.d/keepalived stop
当LVS检测到主库宕机,执行mysql\_down.sh脚本,停止主库上的keepalived服务,从而使得VIP漂移到从库。
/home/mysql/remove\_slave.sh文件内容如下:
#!/bin/bash
. /home/mysql/.bashrc
user=root
password=123456
log=/home/mysql/remove_slave.log
echo "`date`" >> $log
mysql -u$user -p$password -e "set global read_only=OFF;stop slave;reset master;reset slave all;" >> $log
/bin/sed -i 's#read-only#\#read-only#' /home/mysql/mysql-5.6.14/my.cnf
当主库宕机,172.16.1.127切换成为Master时执行remove\_slave.sh脚本,停止复制,重置MySQL的master、slave状态,关闭read\_only,将原来的主从复制中的slave变为master。
在172.16.1.126和172.16.1.127上建立/etc/init.d/realserver文件,内容如下:
#!/bin/sh
RVIP=172.16.1.100
WVIP=172.16.1.210
. /etc/rc.d/init.d/functions
case "$1" in
# 禁用本地的ARP请求、绑定本地回环地址
start)
/sbin/ifconfig lo down
/sbin/ifconfig lo up
echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
/sbin/sysctl -p >/dev/null 2>&1
# 在回环地址上绑定VIP,设定掩码,与Direct Server上自身的IP保持通信
/sbin/ifconfig lo:0 $RVIP netmask 255.255.255.255 up
/sbin/ifconfig lo:1 $WVIP netmask 255.255.255.255 up
/sbin/route add -host $RVIP dev lo:0
/sbin/route add -host $WVIP dev lo:1
echo "LVS-DR real server starts successfully.\n"
;;
stop)
/sbin/ifconfig lo:0 down
/sbin/ifconfig lo:1 down
/sbin/route del $RVIP >/dev/null 2>&1
/sbin/route del $WVIP >/dev/null 2>&1
echo "1" >/proc/sys/net/ipv4/conf/lo/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/lo/arp_announce
echo "1" >/proc/sys/net/ipv4/conf/all/arp_ignore
echo "2" >/proc/sys/net/ipv4/conf/all/arp_announce
echo "LVS-DR real server stopped.\n"
;;
status)
isLoOn=`/sbin/ifconfig lo:0 | grep "$RVIP"`
isRoOn=`/bin/netstat -rn | grep "$RVIP"`
if [ "$isLoON" == "" -a "$isRoOn" == "" ]; then
echo "LVS-DR real server has run yet."
else
echo "LVS-DR real server is running."
fi
exit 3
;;
*)
echo "Usage: $0 {start|stop|status}"
exit 1
esac
exit 0
执行下面的命令将该脚本加入开机自启动:
chmod +x /etc/init.d/realserver
echo "/etc/init.d/realserver" >> /etc/rc.d/rc.local
在172.16.1.126和172.16.1.127上执行:
service realserver start
/etc/init.d/keepalived start
命令执行后172.16.1.126和172.16.1.127上的IP地址分别如图3、4所示。
图3
图4
可以看到172.16.1.126和172.16.1.127两个主机的回环地址都绑定了读写两个VIP,而只有172.16.1.126的本地IP绑定了读写两个VIP。此时从172.16.1.126查看LVS集群状态如下:
[root@hdp3~]#ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP 172.16.1.210:3306 rr
-> 172.16.1.126:3306 Route 1 0 0
FWM 1 rr
-> 172.16.1.126:3306 Route 1 0 0
-> 172.16.1.127:3306 Route 1 0 0
[root@hdp3~]#
1. 验证通过172.16.1.100连接的读负载均衡转发策略
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.100 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 126 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.100 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 127 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.100 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 126 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.100 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 127 |
+---------------+-------+
2. 验证通过172.16.1.210连接的读负载均衡转发策略
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.210 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 126 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.210 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 126 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.210 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 126 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.210 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 126 |
+---------------+-------+
3. 模拟从库的mysqld crash
在172.16.1.127上执行以下命令:
pkill -9 mysqld
4. 再次使用两个VIP连接,数据库服务正常。
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.100 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 126 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.100 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 126 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.210 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 126 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.210 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 126 |
+---------------+-------+
此时从172.16.1.127查看LVS集群状态如下,可以看到读取地址只有172.16.1.126。
[root@hdp4~]#ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP 172.16.1.210:3306 rr
-> 172.16.1.126:3306 Route 1 0 0
FWM 2 rr
-> 172.16.1.126:3306 Route 1 0 0
[root@hdp4~]#
5. 重新启动从库的mysql服务
在172.16.1.127上执行以下命令:
service mysql start
6. 再次验证通过172.16.1.100连接的读负载均衡转发策略
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.100 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 127 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.100 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 126 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.100 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 127 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.100 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 126 |
+---------------+-------+
此时从172.16.1.127查看LVS集群状态如下,可以看到读取地址172.16.1.127被自动加回。
[root@hdp4~]#ipvsadm -Ln
IP Virtual Server version 1.2.1 (size=4096)
Prot LocalAddress:Port Scheduler Flags
-> RemoteAddress:Port Forward Weight ActiveConn InActConn
TCP 172.16.1.210:3306 rr
-> 172.16.1.126:3306 Route 1 0 0
FWM 2 rr
-> 172.16.1.126:3306 Route 1 0 0
-> 172.16.1.127:3306 Route 1 0 0
7. 模拟主库的mysqld crash
在172.16.1.126上执行以下命令:
pkill -9 mysqld
8. 再次验证通过VIP连接的读负载均衡转发策略
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.100 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 127 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.100 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 127 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.210 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 127 |
+---------------+-------+
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.210 -e "show variables like 'server_id'"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 127 |
+---------------+-------+
此时查看172.16.1.127上绑定的IP如图5所示。
图5
可以看到读写两个VIP已经漂移到172.16.1.127上。
9. 验证切换后的新主库可读写。
C:\WINDOWS\system32>mysql -utest -p123456 -h172.16.1.210
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2104
Server version: 5.6.14-log Source distribution
Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select * from test.t1;
+------+
| a |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)
mysql> delete from test.t1;
Query OK, 3 rows affected (10.00 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql>