Mysql高可用架构之keepalived+mysql双主搭建
一、架构扑图
1、服务架构.
2、操作系统
#1、CentOS7.0+
3、其他环境
角色 | 主机名 | IP地址 | 系统版本 | 数据库版本 |
---|---|---|---|---|
主服务器1 | calvin-server | 10.0.2.200 | CentOS7.5 X86_64 | 5.7.26 |
主服务器2 | calvin-test | 10.0.2.201 | CentOS7.4 X86_64 | 5.7.26 |
三、开始部署
1、依赖安装
略...
2、服务安装
安装mysql数据库
略...
分别启动mysql服务
主服务器1上:
[root@buster-server opt]# systemctl start mysqld
主服务器2上:
[root@buster-test opt]# systemctl start mysqld
分别登录双主服务器的mysql数据库更改root密码,从mysql日志中找到root账号临时密码
[root@buster-server mysql]# grep ‘temporary password’ /var/log/mysqld.log
2019-06-28T09:46:42.402574Z 1 [Note] A temporary password is generated for root@localhost: +Gqrgm&yG6Qb
通过临时密码登录mysql数据库更改root密码
mysql>alter user ‘root’@’localhost’ identified by ‘Pass@123’; mysql>flush privileges;
2)搭建mysql双主
双主分别设置为只读状态
mysql> SET @@GLOBAL.read_only = ON;
分别停掉双主库
[root@calvin-server mysql]#systemctl stop mysqld
主库1上配置文件配置如下:
[root@calvin-server ~]# vim /etc/my.cnf [mysqld] server-id=200 log-bin=/var/lib/mysql/mysql-bin #binlog_format =row #mysql5.7默认使用row,无需设置 gtid_mode=ON #开启gtid模式 #log-slave-updates=true #在从服务器进入主服务器传入过来的修改日志所使用,在Mysql5.7之前主从架构上使用Gtid模式的话,必须使用此选项,在Mysql5.7取消了,会增加系统负载。 enforce-gtid-consistency=true #强制gtid一致性,用于保证启动gitd后事务的安全;
主库2上配置文件配置如下:
[mysqld] server-id=201 log-bin=/var/lib/mysql/mysql-bin #bin_log =/var/lib/mysql/mysql-bin #binlog_format =row #mysql5.7默认使用row,无需设置 gtid_mode=ON #开启gtid模式 #log-slave-updates=true #在从服务器进入主服务器传入过来的修改日志所使用,在MySQL5.7之前主从架构上使用gtid模式的话,必须使用此选项,在MySQL5.7取消了,会增加系统负载。 enforce-gtid-consistency=true #强制gtid一致性,用于保证启动gitd后事务的安全;
分别启动双主库
[root@calvin-server mysql]#systemctl start mysqld
在主库1上建立用于同步的用户
mysql> grant replication client,replication slave on *.* to repl@'10.0.2.%' identified by 'Pass@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
在主库2上建立用于同步的用户
mysql> grant replication client,replication slave on *.* to repl2@'10.0.2.%' identified by 'Pass@123'; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)
主库2上执行change master配置GTID复制
mysql>CHANGE MASTER TO MASTER_HOST = '10.0.2.200', MASTER_PORT = 3306, MASTER_USER = 'repl', MASTER_PASSWORD = 'Pass@123', MASTER_AUTO_POSITION = 1;
主库1上执行change master配置GTID复制
mysql>CHANGE MASTER TO MASTER_HOST = '10.0.2.201', MASTER_PORT = 3306, MASTER_USER = 'repl2', MASTER_PASSWORD = 'Pass@123', MASTER_AUTO_POSITION = 1;
3、启动服务
4、验证服务
启动双主的slave,并查看slave状态
主库2上:start slave
show slave status\G;
主库1上:start slave
show slave status\G;
四、服务监控
1.分别在双主上安装keepalived
[root@buster-server ~]#yum -y install keepalived
[root@buster-test ~]#yum -y install keepalived
2.双主上分别在/etc/keepalived创建监控mysql脚本chk_mysqld.sh
#!/bin/bash
n=$(ps -ef|grep "/usr/sbin/mysqld" | grep -v grep |wc -l)
if [ $n -eq 0 ]; then
systemctl stop keepalived
fi
3.双主上分别配置keepalived.conf
[root@buster-server keepalived]# vim /etc/keepalived/keepalived.conf #配置内容如下,最好先备份再建立空的keepalived.conf配置文件加入如下内容
主1上:
global_defs {
lvs_id LBL01
}
vrrp_sync_group SyncGroup01 {
group {
FloatIP1
}
}
vrrp_script chk_mysql {
script "/etc/keepalived/chk_mysqld.sh" #检查mysql进程脚本
interval 1
weight 2
}
vrrp_instance FloatIP1 {
state MASTER #keepalvied主
interface ens33
virtual_router_id 10
priority 101 #主keepalived优先级别,这个值要大于backup的值
advert_int 1
virtual_ipaddress {
10.0.2.202 #虚拟IP
}
track_script {
chk_mysql
}
}
主2上:
[root@calvin-test keepalived]# vim /etc/keepalived/keepalived.conf #配置内容如下,最好先备份再建立空的keepalived.conf配置文件加入如下内容
global_defs {
lvs_id LBL02
}
vrrp_sync_group SyncGroup01 {
group {
FloatIP1
}
}
vrrp_script chk_mysql {
script "/etc/keepalived/chk_mysqld.sh" #检查mysql进程脚本
interval 1
weight 2
}
vrrp_instance FloatIP1 {
state BACKUP #keepalvied从
interface ens33
virtual_router_id 10
priority 100 #从keepalived优先级别,这个值要小于master的值
advert_int 1
virtual_ipaddress {
10.0.2.202 #虚拟IP
}
track_script {
chk_mysql
}
}
4.双主上启动keepalived,并设置开机自启动
[root@calvin-server keepalived]# systemctl start keepalived
[root@calvin-server keepalived]# systemctl enable keepalived
[root@calvin-test keepalived]# systemctl start keepalived
[root@calvin-server keepalived]# systemctl enable keepalived
5.在一台装有mysql客户端的服务器上,通过VIP登录双主数据库
6.测试高可用性
查看VIP
停掉主库1上的mysqld,再次查看VIP
在主库2上查看VIP
可以看到VIP已漂移到主库2上
当主库1上的mysql启动后,VIP会再次漂移到主库1上