mysql 8.0双主_MySQL 8.0 Keepalived+双主架构——手把手搭建

最新推荐文章于 2024-03-29 10:23:59 发布

锦宁

最新推荐文章于 2024-03-29 10:23:59 发布

阅读量580

点赞数

文章标签： mysql 8.0双主

本文链接：https://blog.csdn.net/weixin_33778479/article/details/113168102

版权

我丑话说在前头：

192.168.1.200 主masterB, VMUbuntu18 MySQL8.0

192.168.1.123 主masterC, VMCentOS7 MySQL8.0

vip:192.168.1.199

B shell代表Ubuntu终端

C shell代表CentOS终端

shell代表两系统都需要操作

----------------------------------------------------------------------------------------------------------------

修改B的server-id为2，两个库的server-id不能相同

在B etc/mysqld.cnf里的[mysqld]添加:

# Server Id.

server-id=2

#开启GTID

gtid-mode=on

enforce_gtid_consistency=on

#开启binlog写二进制日志文件:

log_bin=on

保存

每次修改配置文件需要重启服务以便载入

B shell>service mysql restart

B mysql>show variables like "%id";

B mysql>show variables like "%slave";

查看相应值是否修改成功，否则说明配置文件不对

说明：如果etc/mysqld.cnf里新加的参数不对或打错什么的，重启数据库服务会出错

关闭AB防火墙，Windows控制面板，Ubuntu为：

B shell>service iptables stop

相应地，C也做相同操作，但是与CentOS略有区别，两Linux不同系别，Ubuntu是基于Debian，CentOS是基于Red Hat

在C etc/my.cnf里的[mysqld]添加:

# Server Id.

server-id=3

#开启GTID

gtid-mode=on

enforce_gtid_consistency=on

#开启binlog写二进制日志文件:

log_bin=on

保存

重启服务以便载入

C shell>service mysqld restart

注意是“mysqld”，已瞎，我什么都看不到，万恶的CentOS

关闭防火墙，CentOS为：

C shell>systemctl stop firewalld

----------------------------------------------------------------------------------------------------------------

创建主从复制账号并授权：

B mysql>create user 'lgl'@'%' identified by '123456';

B mysql>grant replication slave on *.* to 'lgl'@'%';

B mysql>flush privileges;

C mysql>create user 'lgl'@'%' identified by '123456';

C mysql>grant replication slave on *.* to 'lgl'@'%';

C mysql>flush privileges;

----------------------------------------------------------------------------------------------------------------

使用下面，保证双主库里的数据库、表和数据一致

必须参考：MySQL数据库备份

----------------------------------------------------------------------------------------------------------------

两两互从，即互主

B mysql>

CHANGE MASTER TO

MASTER_HOST='192.168.1.123',

MASTER_USER='lgl',

MASTER_PASSWORD='123456',

master_auto_position=1;

B mysql>start slave;

B mysql>show slave status\G;

C mysql>

CHANGE MASTER TO

MASTER_HOST='192.168.1.200',

MASTER_USER='lgl',

MASTER_PASSWORD='123456',

master_auto_position=1;

C mysql>start slave;

C mysql>show slave status\G;

----------------------------------------------------------------------------------------------------------------

安装keepalived

B shell>apt-get install libssl-dev

B shell>apt-get install openssl

B shell>apt-get install libpopt-dev

B shell>apt_get install keepalived

C shell>yum -y install keepalived

会看到安装完毕

----------------------------------------------------------------------------------------------------------------

两系统终端都新建shell脚本

shell>vim /etc/keepalived/checkmysql.sh

#!/bin/bash

mysqlstr=mysql

host=192.168.1.200

port=3306

user=lgl

password=123456

mysql_status=1

$mysqlstr -h $host -P $port -u $user -p$password -e "show status;" > /dev/null

if [ $? -eq 0 ] ;then

echo "mysql_status=1"

exit 0

else

/etc/init.d/keepalived stop

上述脚本意思是：当数据库服务(B mysql)正常运行时，回显mysql_status=1，否则停止keepalived

shell>chmod +x /etc/keepalived/checkmysql.sh

shell>/etc/keepalived/checkmysql.sh

mysql_status=1

----------------------------------------------------------------------------------------------------------------

B shell>ip addr

1:lo: 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