mysql双机热备以及使用keepalived实现mysql双主高可用
环境描述:
OS:CentOS7.5_X64
MySQL-VIP:172.24.46.23(虚拟ip)
MySQL-master1:172.24.46.19
MySQL-master2:172.24.46.17
1、配置两台Mysql主主同步
[aaa@qq.com ~]# yum install mysql-server mysql -y
[aaa@qq.com ~]# service mysqld start
[aaa@qq.com ~]# mysqladmin -u root password 123.com
[aaa@qq.com ~]# vi /etc/my.cnf #开启二进制日志,设置id
[mysqld]
server-id = 1 #backup这台设置2
log-bin = mysql-bin
binlog-ignore-db = mysql,information_schema #忽略写入binlog日志的库
auto-increment-increment = 2 #字段变化增量值
auto-increment-offset = 1 #初始字段ID为1
slave-skip-errors = all #忽略所有复制产生的错误
[aaa@qq.com ~]# service mysqld restart
如果是Centos7,则卸载mariadb,安装mysql,参考文章:https://blog.51cto.com/sf1314/2073389
安装mysql,参考文章:
mysql5.6.37 https://blog.51cto.com/sf1314/2058754
#mysql -uroot -p’密码’
先查看下log bin日志和pos值位置
show master status;
172.24.46.19上:
![在这里插入图片描述](https://img-blog.csdnimg.cn/20201123161007166.png#pic_center
172.24.46.17上:
172.24.46.19上:
mysql> grant REPLICATION SLAVE on *.* to 'replication'@'172.24.46.17' with grant option;(mysql8.0用不了该命令,从下面输入即可)
mysql> CREATE USER 'replication'@'172.24.46.17' IDENTIFIED BY 'Abc.123456';
mysql> grant ALL PRIVILEGES on *.* to 'replication'@'172.24.46.17' with grant option;
mysql> flush privileges;
mysql> change master to
-> master_host='172.24.46.17',
-> master_user='replication',
-> master_password='Abc.123456',
-> master_log_file='mysql-bin.000001',
-> master_log_pos=155; #对端状态显示的值
mysql> start slave; #启动同步
172.24.46.17上:
mysql> grant REPLICATION SLAVE on *.* to 'replication'@'172.24.46.19' with grant option;
(mysql8.0用不了该命令,从下面输入即可)
mysql> CREATE USER 'replication'@'172.24.46.19' IDENTIFIED BY 'Abc.123456';
mysql> grant ALL PRIVILEGES on *.* to 'replication'@'172.24.46.19' with grant option;
mysql> flush privileges;
mysql> change master to
-> master_host='172.24.46.19',
-> master_user='replication',
-> master_password='Abc.123456',
-> master_log_file='mysql-bin.000002',
-> master_log_pos=578; #对端状态显示的值
mysql> start slave; #启动同步
GRANT REPLICATION SLAVE ON . TO ‘replication’@‘172.24.46.17’ IDENTIFIED BY ‘replication’;
//一般不用root帐号,“%”表示所有客户端都可能连,只要帐号,密码正确,此处可用具体客户端IP代替,如172.24.46.17,加强安全。
#主主同步配置完毕,查看同步状态Slave_IO和Slave_SQL是YES说明主主同步成功。
show slave status\G
在其中一台数据库插入数据测试下
create database test;
use test;
show tables;
create table user (number INT(10),name VARCHAR(255));
insert into user values(01,‘zhangsan’);
添加数据的数据库操作:
另一台数据库:
以上数据库双主数据库数据同步已完成,后续为keepalived实现数据库IP漂移。
#将keepalived配置开机自启服务
systemctl enable keepalived
配置keepalived
我们自己在新建一个配置文件,默认情况下keepalived启动时会去/etc/keepalived目录下找配置文件
[aaa@qq.com ~]# vi /etc/keepalived/keepalived.conf
global_defs {
notification_email {
aaa@qq.com
}
notification_email_from aaa@qq.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_HA #标识,双主相同
}
vrrp_instance VI_1 {
state BACKUP #两台配置此处均是BACKUP
interface eth0
virtual_router_id 51 #主备相同
priority 100 #优先级,另一台backup改为90
advert_int 1
nopreempt #不抢占,只在优先级高master的机器上设置即可,优先级低backup的机器不设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
172.24.46.23
}
}
virtual_server 172.24.46.23 3306 {
delay_loop 2 #每个2秒检查一次real_server状态
#lb_algo wrr #LVS算法,用不到,我们就关闭了
#lb_kind DR #LVS模式,如果不关闭,备用服务器不能通过VIP连接主MySQL
persistence_timeout 60 #会话保持时间,同一IP的连接60秒内被分配到同一台真实服务器
protocol TCP
real_server 172.24.46.17 3306 { #检测本地mysql,backup也要写检测本地mysql
weight 3
notify_down /usr/local/keepalived/mysql.sh #当mysq服down时,执行此脚本,杀死keepalived实现切换
TCP_CHECK {
connect_timeout 10 #连接超时时间
nb_get_retry 3 #重连次数
delay_before_retry 3 #重连间隔时间
connect_port 3306 #健康检查端口
}
}
}
编写检测服务down后所要执行的脚本
[aaa@qq.com ~]# vi /usr/local/keepalived/mysql.sh
#!/bin/bash
pkill keepalived
[aaa@qq.com ~]# chmod +x /usr/local/keepalived/mysql.sh
[aaa@qq.com ~]# /etc/init.d/keepalived start
注:此脚本是上面配置文件notify_down选项所用到的,keepalived使用notify_down选项来检查real_server 的服务状态,当发现real_server服务故障时,便触发此脚本;我们可以看到,脚本就一个命令,通过pkill keepalived强制杀死keepalived进程,从而实现了MySQL故障自动转移。另外,我们不用担心两个MySQL会同时提供数据更新操作, 因为每台MySQL上的keepalived的配置里面只有本机MySQL的IP+VIP,而不是两台MySQL的IP+VIP
启动keepalived
[aaa@qq.com ~]# systemctl start keepalived //启动keepalived
[aaa@qq.com ~]# systemctl enable keepalived //加入开机启动keepalived
[aaa@qq.com ~]# systemctl restart keepalived //重新启动keepalived
[aaa@qq.com ~]# systemctl status keepalived //查看keepalived状态
更改keepalived配置文件
找一台局域网PC,然后去ping MySQL的VIP,这时候MySQL的VIP是可以ping的通的
停止MySQL服务,看keepalived健康检查程序是否会触发我们编写的脚本
#backup服务器只修改priority为90、nopreempt不设置、real_server设置本地IP。
#授权两台Mysql服务器允许root远程登录,用于在其他服务器登陆测试!
mysql> grant all on . to’root’@‘192.168.0.%’ identified by ‘123.com’;
mysql> flush privileges;
3、测试高可用性
1、通过Mysql客户端通过VIP连接,看是否连接成功。
2、停止master这台mysql服务,是否能正常切换过去,可通过ip addr命令来查看VIP在哪台服务器上。
3、可通过查看/var/log/messges日志,看出主备切换过程
4、master服务器故障恢复后,是否主动抢占资源,成为活动服务器。
附:keepalived-1.2.7 keepalived实现服务高可用 http://down.51cto.com/data/2440924
针对网卡做bond的做VIP: https://blog.51cto.com/sf1314/2073519
[aaa@qq.com ~]# vi /etc/keepalived/keepalived.conf
global_defs {
notification_email {
aaa@qq.com
}
notification_email_from aaa@qq.com
smtp_server 127.0.0.1
smtp_connect_timeout 30
router_id MYSQL_HA #标识,双主相同
}
vrrp_instance VI_1 {
state BACKUP #两台配置此处均是BACKUP
interface bond0.101 #------->这边指定配置的聚合网卡bond0.101
virtual_router_id 51 #主备相同
priority 100 #优先级,另一台backup改为90
advert_int 1
nopreempt #不抢占,只在优先级高master的机器上设置即可,优先级低backup的机器不设置
authentication {
auth_type PASS
auth_pass 1111
}
virtual_ipaddress {
10.104.101.13/24 #------->这边设置虚拟的VIP地址
}
}
virtual_server 10.104.101.13/24 3306 { #--------->指定虚拟VIP地址的配置信息
delay_loop 2 #每个2秒检查一次real_server状态
#lb_algo wrr #LVS算法,用不到,我们就关闭了
#lb_kind DR #LVS模式,如果不关闭,备用服务器不能通过VIP连接主MySQL
persistence_timeout 60 #会话保持时间,同一IP的连接60秒内被分配到同一台真实服务器
protocol TCP
real_server 10.104.101.12 3306 { #10.104.101.12指本地配置的IP地址,检测本地mysql,backup也要写检测本地mysql
weight 3
notify_down /usr/local/keepalived/mysql.sh #当mysq服down时,执行此脚本,杀死keepalived实现切换
TCP_CHECK {
connect_timeout 10 #连接超时时间
nb_get_retry 3 #重连次数
delay_before_retry 3 #重连间隔时间
connect_port 3306 #健康检查端口
}
}
}
附:修改mysql账户下的replication的密码可以参考本文章:https://blog.51cto.com/sf1314/2094562
特别鸣谢:
https://blog.51cto.com/sf1314/2073383(基本都是这个上面的)
推荐阅读
-
Ubuntu搭建Mysql+Keepalived高可用的实现(双主热备)
-
关于使用Keepalived实现Nginx的自动重启及双主热备高可用问题
-
MySQL高可用性之Keepalived+MySQL(双主热备)
-
MySQL高可用性之Keepalived+MySQL(双主热备)
-
Mysql+Keepalived双主热备高可用操作记录
-
关于MySQL 的高可用性:Keepalived 双主热备
-
MySQL5.7双机热备及keepalived高可用
-
关于MySQL 的高可用性:Keepalived 双主热备
-
mysql5.7数据库主从同步、双机热备高可用集群的实现
-
mysql双机热备以及使用keepalived实现mysql双主高可用