Centos7搭建基于GTID的MySQL的M-M-S-S架构
简介
M-M-S-S架构即双主双从结点,具体架构为两台主结点互为对方的从节点,且两台从节点机器均有两个主结点,可用于当其中一台主结点服务发生故障时,有备用主结点可以对业务进行支撑
主从复制
主从复制(也称 AB 复制)是将来自一个MySQL数据库服务器(主服务器)中的数据复制到一个或多个MySQL数据库服务器(从服务器)中,部署方法可参考:https://blog.csdn.net/xixixilalalahaha/article/details/106434408
GTID
从MySQL 5.6.5 开始新增了一种基于 GTID 的主从复制方式,GTID (Global Transaction ID)是全局事务ID,通过 GTID可以保证每一个在主库中提交的事务在整个数据库集群中有一个唯一的ID,因此当在主库上提交事务或者被从库应用时,可以通过ID定位和追踪每一个事务,不用再通过手工去可以找偏移量的值,这种方式强化了数据库的主备一致性,故障恢复以及容错能力,部署方法可参考:https://blog.csdn.net/xixixilalalahaha/article/details/107127855
前期准备
准备四台centos7虚拟机,配置ip和hostname,关闭防火墙和selinux
在/etc/hosts中添加主机名和IP地址映射
hostname | ip |
---|---|
master1 | 192.168.29.132 |
master2 | 192.168.29.138 |
slave1 | 192.168.29.131 |
slave2 | 192.168.29.133 |
配置MySQL
两个master节点添加主从复制账号
MySQL5.7:
mysql>grant replication slave on *.* to 'repl'@'%' identified by 'your_password';
mysql>flush privileges;
MySQL8:
mysql>create user 'repl'@'%' identified by 'your_password';
mysql>grant replication slave on *.* to 'repl'@'%';
mysql>flush privileges;
master1主机初始化数据
mysql> create database mydb;
mysql> use mydb;
mysql> create table test(id int primary key);
mysql> insert into test values(1);
mysql> insert into test values(2);
修改两个master结点的配置文件
master1
[root@master1 ~]# vi /etc/my.cnf
[mysqld]
#设置Master主机信息
server-id=1
log-bin=binlog
#基于事务的Replication
#可以实现基于库的多线程复制,减少主从复制的延迟
#主机配置
gtid_mode=ON
#强制执行GTID一致性
enforce_gtid_consistency=1
#设定把master主机信息保存在表中
#默认以文件形式保存
master-info-repository=table
relay-log-info-repository=table
master2
[root@master2 ~]# vi /etc/my.cnf
[mysqld]
server-id=2
log-bin=binlog
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=table
relay-log-info-repository=table
配置两台slave主机的配置文件
slave1
[root@slave1 ~]# vi /etc/my.cnf
[mysqld]
server-id=3
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=table
relay-log-info-repository=table
slave2
[root@slave2 ~]# vi /etc/my.cnf
[mysqld]
server-id=4
gtid_mode=ON
enforce_gtid_consistency=1
master-info-repository=table
relay-log-info-repository=table
备份master1主机的数据并导入到其余所有主机中
master1备份数据
[root@master1 ~]# mysqldump -u root -p -A > mydb.sql
[root@master1 ~]# scp mydb.sql root@master2:/tmp/
[root@master1 ~]# scp mydb.sql root@slave1:/tmp/
[root@master1 ~]# scp mydb.sql root@slave2:/tmp/
其余结点中导入数据
[root@master2 ~]# mysql -u root -p < /tmp/mydb.sql
[root@slave1 ~]# mysql -u root -p < /tmp/mydb.sql
[root@slave2 ~]# mysql -u root -p < /tmp/mydb.sql
配置两台master结点互为主从
master1
mysql>stop slave;
mysql>change master to
->master_host='192.168.29.138',
->master_user='repl',
->master_password='your_password',
->master_auto_position=1
参数含义:
master_host:主机IP地址
master_user:主从复制远程主机用户
master_password:主从复制远程主机用户的密码
master_auto_position:自动寻找偏移量
master2
mysql>stop slave;
mysql>change master to
->master_host='192.168.29.132',
->master_user='repl',
->master_password='your_password',
->master_auto_position=1
查看两台主机中slave状态
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
配置两台slave结点为两个master的从结点
slave1
mysql>stop slave;
mysql>change master to
->master_host='192.168.29.132',
->master_user='repl',
->master_password='your_password',
->master_auto_position=1 for channel 'repl-master-1';
参数for channel为指定主结点在不同的频道
mysql>stop slave;
mysql>change master to
->master_host='192.168.29.138',
->master_user='repl',
->master_password='your_password',
->master_auto_position=1 for channel 'repl-master-2';
slave2
mysql>stop slave;
mysql>change master to
->master_host='192.168.29.132',
->master_user='repl',
->master_password='your_password',
->master_auto_position=1 for channel 'repl-master-1';
mysql>stop slave;
mysql>change master to
->master_host='192.168.29.138',
->master_user='repl',
->master_password='your_password',
->master_auto_position=1 for channel 'repl-master-2';
查看两台slave中的slave状态
mysql>start slave;
mysql>show slave status\G;
#可以看到有两份主机信息
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
测试验证
master1结点操作数据库
mysql> insert into test values(1111);
master2结点操作数据库
mysql> insert into test values(2222);
slave1和slave2结点查看数据
mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 1111 |
| 2222 |
+------+
4 rows in set (0.00 sec)
模拟master1宕机
关闭master1的mysql服务
[root@master ~]# systemctl stop mysqld
查看其余结点的情况
Last_IO_Error: error reconnecting to master 'repl@192.168.29.132:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '192.168.29.132' (111)
master2结点操作数据库
mysql> insert into test values(3);
mysql> insert into test values(4);
slave1和slave2结点查看数据
mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 1111 |
| 2222 |
+------+
6 rows in set (0.00 sec)
重启master1的mysql服务
[root@master ~]# systemctl restart mysqld
#其余结点的从节点恢复正常
master1查询数据
mysql> select * from test;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
| 1111 |
| 2222 |
+------+
6 rows in set (0.00 sec)
故障
配置过程中报错
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Cannot replicate because the master purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new slave from backup. Consider increasing the master's binary log expiration period. To find the missing transactions, see the master's error log or the manual for GTID_SUBTRACT
原因是主结点数据库进行了过多数据操作或进行过重启等导致binlo*生了变化,从节点无法进行同步
解决方法
在master1中进行操作后在备份数据到其他从节点
mysql>reset master
mysql>reset slave
本文地址:https://blog.csdn.net/xixixilalalahaha/article/details/107130329
上一篇: 分布式事务之最终一致性
下一篇: 莴笋是油麦菜吗?莴笋怎么挑选?
推荐阅读
-
详解MySQL主从复制实战 - 基于GTID的复制
-
LAMP架构(2)——MySQL的搭建(适合小白)
-
在centos7上搭建mysql主从服务器的方法(图文教程)
-
基于Docker的MySQL主从复制环境搭建的实现步骤
-
MySQL 搭建MHA架构部署的步骤
-
linux 之centos7搭建mysql5.7.29的详细过程
-
详解基于centos7搭建Nginx网站服务器(包含虚拟web主机的配置)
-
容器化 | 基于 Kubernetes 的新一代 MySQL 高可用架构实现方案
-
Centos7搭建基于GTID的MySQL的M-M-S-S架构
-
基于Nginx0.8.54+PHP5.3.4+MySQL5.5.8的全新LNMP稳定版架构搭建的VPS