前面文章配置过myql的主从复制，当时是一主一从，如果mysql写入并发大时，有可能导致mysql奔溃，导致服务停止，为了应对这种情况的发生，我们需要引用高可用的mysql服务，目前的解决方案有两种
一 主主+keepalived，两台服务器互为主从关系
正常情况下为一主两从，宕机后为一主一从
png
image.png
通过keepalived 在两台服务器上配置一个vip，默认情况下我们以master1为主，指定这个虚拟ip到master1，另一台为从库负责读，当master1由于种种原因宕机了，通过keepalived的监控脚本，自动将主服务器漂移到master2上，以达到服务的可用性。
二、mysql主主配置
三台服务器分别为
192.168.195.70 master1
192.168.195.71 master2
192.168.195.72 slave
192.168.195.100 vip
分别安装mysql5.7并做初始化设置
wget -i -c http://dev.mysql.com/get/mysql57-community-release-el7-10.noarch.rpm
yum -y install mysql57-community-release-el7-10.noarch.rpm
rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022 #密钥配置
yum -y install mysql-community-server
systemctl start mysqld.service # 启动
grep "password" /var/log/mysqld.log #查看初始密码
mysql -uroot -p #登录
set global validate_password_policy=LOW; #验证强度等级
set global validate_password_length=4;
ALTER USER 'root'@'localhost' IDENTIFIED BY 'XXXXXXXX'; #设置新密码
grant all privileges on *.* to 'root'@'%' identified by ‘XXXXXXXX' with grant option; #开启远程访问
flush privileges; #设置生效
exit #退出
firewall-cmd --zone=public --add-port=3306/tcp --permanent #防火墙放开3306端口
firewall-cmd --reload #防火墙重载
分别编辑vi /etc/my.cnf,增加如下信息
master1
log-slave-updates # 开启slave日志
server-id = 1 #设置id 全局不能重复
auto_increment_offset = 1 #初始1
auto_increment_increment = 2 #自增为2
log-bin = mysql-bin
relay-log = relay-log
relay-log-info-file=relay-log-info-file
master2
log-slave-updates # 开启slave日志
server-id = 2 #设置id 全局不能重复
auto_increment_offset = 2 #初始1
auto_increment_increment = 2 #自增为2
log-bin = mysql-bin
relay-log = relay-log
relay-log-info-file=relay-log-info-file
slave
server-id=3
read-only=on
relay-log=relay-bin
设置同步数据库
查看master1的数据库状态,进入mysql
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000007 | 154 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
CHANGE MASTER TO MASTER_HOST='192.168.3.71', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4847; //设置master1同步master2
start slave；
show SLAVE STATUS；
查看master2的数据库状态，进入mysql
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 | 4847 | | | |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
CHANGE MASTER TO MASTER_HOST='192.168.3.70', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=154; //设置master2同步master1
start slave；
show SLAVE STATUS；
slave上设置同步数据信息
CHANGE MASTER TO MASTER_HOST='192.168.3.71', MASTER_PORT=3306, MASTER_USER='root', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=4847; //设置slave同步master2
start slave；
show SLAVE STATUS；
然后
systemctl restart mysqld
systemctl enable mysqld
systemctl daemon-reload
mysql> show slave status \G #查看状态
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.3.70
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 4847
Relay_Log_File: relay-log.000014
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes //这里yes代表成功
Slave_SQL_Running: Yes //这里yes代表成功
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4847
Relay_Log_Space: 521
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: bbd4275f-1af0-11ee-911e-0800278b8ad6
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.01 sec)
ERROR:
No query specified
使用navicat连接数据库
image.png
安装keepalived
yum install keepalived
或者
wget https://www.keepalived.org/software/keepalived-2.2.8.tar.gz
tar -xzvf keepalived-2.2.8
cd keepalived-2.2.7
./configure --prefix=/usr/local/keepalived --sysconf=/etc
make && make install
报错
image.png
没有C编译环境，安装一下
yum -y install gcc
image.png
需要安装openssl和openssl-devel
yum -y install openssl openssl-devel
cd /etc/keepalived
mv keepalived.conf keepalived.conf.bak
vi keepalived.conf
如下
vi /etc/keepalived/keepalived.conf:
! Configuration File for keepalived //!表示注释
global_defs {
router_id MYSQL-1 //表示运行keepalived服务器的一个标识
}
vrrp_instance VI_1 {
state BACKUP //指定keepalived的角色, 两台配置此处均是BACKUP,设为BACKUP将根据优
先级决定主或从
interface enp0s3 //指定HA监测网络的接口,你的可能为eth0
virtual_router_id 51 //虚拟路由标识，这个标识是一个数字(取值在0-255之间,用来区分多个instance的VRRP组播)，同一个vrrp实例使用唯一的标识,确保和master2相同，同网内不同集群，此项必须不同,否则发生冲突。
priority 100 //用来选举master的，要成为master，该项取值范围是1-255（在此范围之外会被识别成默认值100）,此处master2上设置为90
advert_int 1 //发VRRP包的时间间隔，即多久进行一次master选举（可以认为是健康查检时间间隔）
nopreempt //不抢占，即允许一个priority比较低的节点作为master，即使有priority更高的节点启动
authentication { //认证区域，认证类型有PASS和HA（IPSEC），推荐使用PASS（密码只识别前8位）
auth_type PASS
auth_pass 123456
}
virtual_ipaddress { //VIP区域，指定vip地址
192.168.3.100
}
}
virtual_server 192.168.3.100 3306 { //设置虚拟服务器，需要指定虚拟IP地址和服务端口，IP与端口之间用空格隔开
delay_loop 2 //设置运行情况检查时间，单位是秒
lb_algo rr //设置后端调度算法，这里设置为rr，即轮询算法
lb_kind DR //设置LVS实现负载均衡的机制，有NAT、TUN、DR三个模式可选
persistence_timeout 60 //会话保持时间，单位是秒。这个选项对动态网页是非常有用的，为集群系统中的session共享提供了一个很好的解决方案。有了这个会话保持功能，用户的请求会被一直分发到某个服务节点，直到超过这个会话的保持时间。
protocol TCP //指定转发协议类型，有TCP和UDP两种
real_server 192.168.3.70 3306 { //配置服务节点1，需要指定real server的真实IP地址和端口，IP与端口之间用空格隔开，注：master 2上此处改为192.168.3.71(即master2本机ip)
weight 3 //配置服务节点的权值，权值大小用数字表示，数字越大，权值越高，设置权值大小为了区分不同性能的服务器
notify_down /etc/keepalived/mysql_check.sh //检测到realserver的mysql服务down后执行的脚本
TCP_CHECK {
connect_timeout 3 //连接超时时间
nb_get_retry 3 //重连次数
delay_before_retry 3 //重连间隔时间
connect_port 3306 //健康检查端口
}
}
}
192.168.3.71操作同上
只是keepalived.conf有一点不同
! Configuration File for keepalived
global_defs {
router_id mysql-1
}
vrrp_instance VI_1 {
state BACKUP
interface enp0s3
virtual_router_id 51
nopreempt
priority 90
advert_int 1
authentication {
auth_type PASS
auth_pass 123456
}
virtual_ipaddress {
192.168.3.100
}
}
virtual_server 192.168.3.100 3306 {
delay_loop 6
lb_algo rr
lb_kind DR
persistence_timeout 90
protocol TCP
real_server 192.168.3.71 3306 {
weight 1
notify_down /etc/keepalived/mysql_check.sh
TCP_CHECK {
connect_port 3306
connect_timeout 3
retry 3
delay_before_retry 3
}
}
}
创建 /etc/keepalived/mysql_check.sh
MYSQL=/usr/bin/mysql
MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASSWORD=123456
CHECK_TIME=3
#mysql is working MYSQL_OK is 1 , mysql down MYSQL_OK is 0
MYSQL_OK=1
function check_mysql_helth (){
$MYSQL -h $MYSQL_HOST -u $MYSQL_USER -p${MYSQL_PASSWORD} -e "show status;" >/dev/null 2>&1
if [ $? = 0 ] ;then
MYSQL_OK=1
else
MYSQL_OK=0
fi
return $MYSQL_OK
}
while [ $CHECK_TIME -ne 0 ]
do
let "CHECK_TIME -= 1"
check_mysql_helth
if [ $MYSQL_OK = 1 ] ; then
CHECK_TIME=0
exit 0
fi
if [ $MYSQL_OK -eq 0 ] && [ $CHECK_TIME -eq 0 ]
then
pkill keepalived
exit 1
fi
sleep 1
done
修改脚本权限为755
chmod 755 /etc/keepalived/mysql_check.sh
分别启动master1和master2的keepalived
systemctl restart keepalived.service
验证keepalived 的vip
思路：keepalived的vip生效后，验证是否能够通过VIP访问数据库即可
1，在master1 服务器输入 ip add 查看vip 如下图，此时vip在Master1上
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: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:74:50:82 brd ff:ff:ff:ff:ff:ff
inet 192.168.3.70/24 brd 192.168.3.255 scope global noprefixroute dynamic enp0s3
valid_lft 73630sec preferred_lft 73630sec
inet 192.168.3.100/32 scope global enp0s3
valid_lft forever preferred_lft forever
inet6 fe80::aea8:f1c:a689:4374/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::9c18:4e77:7ba8:11ed/64 scope link noprefixroute
valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
link/ether 52:54:00:60:f7:91 brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
link/ether 52:54:00:60:f7:91 brd ff:ff:ff:ff:ff:ff
5: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
link/ether 02:42:a1:9a:ea:f6 brd ff:ff:ff:ff:ff:ff
inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
valid_lft forever preferred_lft forever
6: br-761f14be5234: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
link/ether 02:42:cb:c5:cc:39 brd ff:ff:ff:ff:ff:ff
inet 172.18.0.1/16 brd 172.18.255.255 scope global br-761f14be5234
valid_lft forever preferred_lft forever
master2上查看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: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:8b:8a:d6 brd ff:ff:ff:ff:ff:ff
inet 192.168.3.71/24 brd 192.168.3.255 scope global noprefixroute dynamic enp0s3
valid_lft 65659sec preferred_lft 65659sec
inet6 fe80::aea8:f1c:a689:4374/64 scope link noprefixroute
valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
link/ether 52:54:00:60:f7:91 brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
link/ether 52:54:00:60:f7:91 brd ff:ff:ff:ff:ff:ff
5: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
link/ether 02:42:39:41:e0:cb brd ff:ff:ff:ff:ff:ff
inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
valid_lft forever preferred_lft forever
inet6 fe80::42:39ff:fe41:e0cb/64 scope link
valid_lft forever preferred_lft forever
6: br-761f14be5234: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
link/ether 02:42:38:25:3e:4c brd ff:ff:ff:ff:ff:ff
inet 172.18.0.1/16 brd 172.18.255.255 scope global br-761f14be5234
valid_lft forever preferred_lft forever
inet6 fe80::42:38ff:fe25:3e4c/64 scope link
valid_lft forever preferred_lft forever
3，使用虚拟ip登录输入库
打开navicat 输入虚拟IP以及账户密码信息 如下图
image.png
模拟数据写入到vip数据库上
image.png
查看master2以及slave上是否同步数据，多次测试均写入成功
如图
image.png
模拟数据库master1宕机，查看vip是否切换到master2上
master1上
systemctl stop mysqld
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: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:74:50:82 brd ff:ff:ff:ff:ff:ff
inet 192.168.3.72/24 brd 192.168.3.255 scope global noprefixroute dynamic enp0s3
valid_lft 75551sec preferred_lft 75551sec
inet6 fe80::aea8:f1c:a689:4374/64 scope link tentative noprefixroute dadfailed
valid_lft forever preferred_lft forever
inet6 fe80::9c18:4e77:7ba8:11ed/64 scope link noprefixroute
valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
link/ether 52:54:00:60:f7:91 brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
link/ether 52:54:00:60:f7:91 brd ff:ff:ff:ff:ff:ff
5: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
link/ether 02:42:a1:9a:ea:f6 brd ff:ff:ff:ff:ff:ff
inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
valid_lft forever preferred_lft forever
6: br-761f14be5234: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
link/ether 02:42:cb:c5:cc:39 brd ff:ff:ff:ff:ff:ff
inet 172.18.0.1/16 brd 172.18.255.255 scope global br-761f14be5234
valid_lft forever preferred_lft forever
查看master2,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: enp0s3: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP group default qlen 1000
link/ether 08:00:27:8b:8a:d6 brd ff:ff:ff:ff:ff:ff
inet 192.168.3.70/24 brd 192.168.3.255 scope global noprefixroute dynamic enp0s3
valid_lft 67740sec preferred_lft 67740sec
inet 192.168.3.100/32 scope global enp0s3
valid_lft forever preferred_lft forever
inet6 fe80::aea8:f1c:a689:4374/64 scope link noprefixroute
valid_lft forever preferred_lft forever
3: virbr0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default qlen 1000
link/ether 52:54:00:60:f7:91 brd ff:ff:ff:ff:ff:ff
inet 192.168.122.1/24 brd 192.168.122.255 scope global virbr0
valid_lft forever preferred_lft forever
4: virbr0-nic: <BROADCAST,MULTICAST> mtu 1500 qdisc pfifo_fast master virbr0 state DOWN group default qlen 1000
link/ether 52:54:00:60:f7:91 brd ff:ff:ff:ff:ff:ff
5: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
link/ether 02:42:39:41:e0:cb brd ff:ff:ff:ff:ff:ff
inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
valid_lft forever preferred_lft forever
inet6 fe80::42:39ff:fe41:e0cb/64 scope link
valid_lft forever preferred_lft forever
6: br-761f14be5234: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default
link/ether 02:42:38:25:3e:4c brd ff:ff:ff:ff:ff:ff
inet 172.18.0.1/16 brd 172.18.255.255 scope global br-761f14be5234
valid_lft forever preferred_lft forever
inet6 fe80::42:38ff:fe25:3e4c/64 scope link
valid_lft forever preferred_lft forever
vip已切换到master2，测试数据写入到vip数据库中，slave从库数据正常更新，当重启master1的mysql和keepalived，以及重启master2上的keepalived后，vip又切换回了master1上，这里应该有办法不重启master2上的keepalived,大家有更好的方法，可以一起探讨。
以上是通过主主+keepalived实现的数据库高可用
异步主从 主库和从库的数据之间难免会存在一定的延迟，这样存在一个隐患：当在主库上写入一个事务并提交成功，而从库尚未得到主库的BINLOG日志时，主库由于磁盘损坏、内存故障、断电等原因意外宕机，导致主库上该事务BINLOG丢失，此时从库就会损失这个事务，从而造成主从不一致。使得主从复制失去意义。，
下一篇分享使用增强型半同步复制优化I/O线程。
网友评论