MySQL 8.0.26 集群-双主双从读写分离

一、环境

• 系统：Centos 7
• MySQL版本：8.0.26
• MyCat版本：1.6.7.3

名称	ip
mycat	192.168.68.130
master1	192.168.68.131
slave1	192.168.68.132
master2	192.168.68.133
slave2	192.168.68.134

二、架构

三、操作步骤

3.1 安装mysql

master1、slave1、master2、slave2分别安装mysql

systemctl stop firewalld.service
systemctl disable firewalld.service

#查询并删除系统自带的Mariadb
rpm -qa | grep mariadb
#程序名是上述命令查询到的结果
rpm -e --nodeps 程序名 

#创建/opt/mysql目录，并将上传的mysql包解压至该目录下
tar -vxf mysql-8.0.26-1.el7.x86_64.rpm-bundle.tar -C /opt/mysql/
cd /opt/mysql
rpm -ivh mysql-community-common-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-plugins-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-libs-compat-8.0.26-1.el7.x86_64.rpm
yum install -y openssl-devel
rpm -ivh  mysql-community-devel-8.0.26-1.el7.x86_64.rpm
rpm -ivh mysql-community-client-8.0.26-1.el7.x86_64.rpm
rpm -ivh  mysql-community-server-8.0.26-1.el7.x86_64.rpm

3.2 master1配置mysql

vim /etc/my.conf
#末尾添加以下内容
#mysql服务ID,保证唯一性
server-id=1
#在作为从库时，有更新操作也需要写入二进制文件
log-slave-updates=1
#指定同步的数据库
binlog-do-db=DB01

systemctl start mysqld
service mysqld status

#查询mysql为root用户生成的临时密码
grep 'temporary password' /var/log/mysqld.log
#使用临时密码登录mysql
mysql -u root -p
#修改root用户密码
mysql> alter user 'root'@'localhost' identified by 'Root12!@';
#创建远程连接的账号，并赋予复制权限
mysql> create user 'test'@'%' identified by 'Test12!@';
mysql> grant replication slave on *.* to 'test'@'%';
#查看二进制日志坐标
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000008 |      156 | DB01         |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

3.3 slave1配置mysql

vim /etc/my.conf
#末尾添加以下内容
#mysql服务ID,保证唯一性
server-id=2
#设置数据库是否只读，0是读写，1是只读
read-only=1
#relay-bin文件损坏，自动放弃未执行的relay-log，重新从master上获取日志
relay_log_recovery=1
#指定同步的数据库
binlog-do-db=DB01

systemctl start mysqld
service mysqld status

#查询mysql为root用户生成的临时密码
grep 'temporary password' /var/log/mysqld.log
#使用临时密码登录mysql
mysql -u root -p
#修改root用户密码
mysql> alter user 'root'@'localhost' identified by 'Root12!@';

配置master1（192.168.68.131）为主库

mysql> change master to master_host='192.168.68.131', master_port=3306,master_user='test', master_password='Test12!@', master_log_file='binlog.000008', master_log_pos=156;
#开启同步操作
mysql> start slave;
#查看主从同步状态
mysql> show slave status\G;

3.4 master2配置mysql

vim /etc/my.conf
#末尾添加以下内容
#mysql服务ID,保证唯一性
server-id=3
#在作为从库时，有更新操作也需要写入二进制文件
log-slave-updates=1
#指定同步的数据库
binlog-do-db=DB01

systemctl start mysqld
service mysqld status

#查询mysql为root用户生成的临时密码
grep 'temporary password' /var/log/mysqld.log
#使用临时密码登录mysql
mysql -u root -p
#修改root用户密码
mysql> alter user 'root'@'localhost' identified by 'Root12!@';
#创建远程连接的账号，并赋予复制权限
mysql> create user 'test'@'%' identified by 'Test12!@';
mysql> grant replication slave on *.* to 'test'@'%';
#查看二进制日志坐标
mysql> show master status;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| binlog.000003 |      156 | DB01         |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

3.5 slave2配置mysql

vim /etc/my.conf
#末尾添加以下内容
#mysql服务ID,保证唯一性
server-id=4
#设置数据库是否只读，0是读写，1是只读
read-only=1
#relay-bin文件损坏，自动放弃未执行的relay-log，重新从master上获取日志
relay_log_recovery=1
#指定同步的数据库
binlog-do-db=DB01

systemctl start mysqld
service mysqld status

#查询mysql为root用户生成的临时密码
grep 'temporary password' /var/log/mysqld.log
#使用临时密码登录mysql
mysql -u root -p
#修改root用户密码
mysql> alter user 'root'@'localhost' identified by 'Root12!@';

配置master2（192.168.68.133）为主库

mysql> change master to master_host='192.168.68.133', master_port=3306,master_user='test', master_password='Test12!@', master_log_file='binlog.000003', master_log_pos=156;
#开启同步操作
mysql> start slave;
#查看主从同步状态
mysql> show slave status\G;

3.6 master1、master2互为主从配置

master1执行以下命令

mysql> change master to master_host='192.168.68.133', master_port=3306,master_user='test', master_password='Test12!@', master_log_file='binlog.000003', master_log_pos=156;
mysql> start slave;
mysql> show slave status\G;

master2执行以下命令

mysql> change master to master_host='192.168.68.131', master_port=3306,master_user='test', master_password='Test12!@', master_log_file='binlog.000008', master_log_pos=156;
mysql> start slave;
mysql> show slave status\G;

3.7 安装mycat

3.7.1 JDK安装配置

#将jdk包解压至/usr/local目录下
tar -zxvf jdk-8u321-linux-x64.tar.gz -C /usr/local/

vim /etc/profile
#添加以下内容
#jdk
export JAVA_HOME=/usr/local/jdk1.8.0_321
export CLASSPATH=.:%JAVA_HOME%/lib/dt.jar:%JAVA_HOME%/lib/tools.jar
export PATH=$PATH:$JAVA_HOME/bin

#使配置文件生效
source /etc/profile
#查看jdk是否安装成功
java -version

3.7.2 mycat安装配置

#将mycat包解压至/usr/local目录下
tar -zxvf Mycat-server-1.6.7.3-release-20210913163959-linux.tar.gz  -C /usr/local/

需要将/usr/local/mycat/lib中的mysql-connector-java低版本升级至mysql-connector-java-8.0.22.jar高版本。

修改mycat/conf/schema.xml

修改mycat/conf/server.xml

启动mycat

/usr/local/mycat/bin/mycat start

启动日志查看

tail -f /usr/local/mycat/logs/wrapper.log

如下图，则表示启动成功

四、验证

登录mycat创建表，并去mysql双主双从中进行检查