目录
介绍
一个主机 Master1 用于处理所有写请求,它的从机 Slave1 和另一台主机 Master2 还有它的从 机 Slave2 负责所有读请求。当 Master1 主机宕机后,Master2 主机负责写请求,Master1 、 Master2 互为备机。架构图如下:
准备
我们需要准备5台服务器,具体的服务器及软件安装情况如下:
主机名 | IP地址 | 配置 | 预装软件 | 角色 | 系统 |
---|---|---|---|---|---|
mycat | 192.168.226.100 | 2核2G-20G磁盘 | MyCat、MySQL | MyCat中间件服务器 | Centos7-2009-mini |
master1 | 192.168.226.101 | 2核2G-20G磁盘 | MySQL | M1 | Centos7-2009-mini |
master2 | 192.168.226.102 | 2核2G-20G磁盘 | MySQL | M2 | Centos7-2009-mini |
slave1 | 192.168.226.103 | 2核2G-20G磁盘 | MySQL | S1 | Centos7-2009-mini |
slave2 | 192.168.226.104 | 2核2G-20G磁盘 | MySQL | S2 | Centos7-2009-mini |
五台主机关闭防火墙和selinux,进行时间同步并安装mysql,设置root密码为1234
#!/bin/bash
echo "=====系统环境初始化脚本====="
sleep 3
echo "——>>> 关闭防火墙与SELinux <<<——"
sleep 3
systemctl stop firewalld
systemctl disable firewalld &> /dev/null
setenforce 0
sed -i '/SELINUX/{s/enforcing/disabled/}' /etc/selinux/config
echo "——>>> 创建阿里仓库 <<<——"
sleep 3
rm -rf /etc/yum.repos.d/*
curl -o /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo
yum -y install wget
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
echo "——>>> 设置时区并同步时间 <<<——"
sleep 3
timedatectl set-timezone Asia/Shanghai
yum -y install chrony
systemctl start chronyd
systemctl enable chronyd
reboot
sudo yum remove mysql-server -y && sudo yum autoremove -y
sudo yum remove *mysql* -y
sudo rm -rf /var/lib/mysql/
sudo rm -rf /etc/mysql/
yum install -y yum-utils > /dev/null
yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-11.noarch.rpm > /dev/null
yum-config-manager --enable mysql80-community > /dev/null
yum-config-manager --disable mysql57-community > /dev/null
yum install -y mysql-server
systemctl start mysqld && systemctl enable mysqld
mysqladmin -p"`awk '/temporary password/{p=$NF}END{print p}' /var/log/mysqld.log`" password 'TianPFh@123'
mysql -p'TianPFh@123' -e "UNINSTALL COMPONENT 'file://component_validate_password'"
mysqladmin -p'TianPFh@123' password '1234'
搭建
主库配置
Master1(192.168.226.101)
在配置文件 /etc/my.cnf 追加下述配置
# mysql 服务ID,保证整个集群环境中唯一,取值范围:1 – 2^32-1,默认为1
server-id=10
# 指定同步的数据库
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
# 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
重启mysql
systemctl restart mysqld
登录创建账户并授权
# 创建it用户,并设置密码,该用户可在任意主机连接该MySQL服务
CREATE USER 'it'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';
# 为 'it'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'it'@'%';
# 查看第一个主库的二进制日志坐标
show master status ;
Master2(192.168.226.102)
在配置文件 /etc/my.cnf 追加下述配置
# mysql 服务ID,保证整个集群环境中唯一,取值范围:1 – 2^32-1,默认为1
server-id=11
# 指定同步的数据库
binlog-do-db=db01
binlog-do-db=db02
binlog-do-db=db03
# 在作为从数据库的时候,有写入操作也要更新二进制日志文件
log-slave-updates
重启mysql
systemctl restart mysqld
登录创建账户并授权
# 创建it用户,并设置密码,该用户可在任意主机连接该MySQL服务
CREATE USER 'it'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';
# 为 'it'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'it'@'%';
# 查看第一个主库的二进制日志坐标
show master status ;
从库配置
Slave1(192.168.226.103)
在配置文件 /etc/my.cnf 追加下述配置
# mysql 服务ID,保证整个集群环境中唯一,取值范围:1 – 2^32-1,默认为1
server-id=12
重启mysql
systemctl restart mysqld
从库关联主库
需要注意slave1对应的是master1,slave2对应的是master2。
CHANGE MASTER TO MASTER_HOST='192.168.226.101', MASTER_USER='it',
MASTER_PASSWORD='admin', MASTER_LOG_FILE='binlog.000003',
MASTER_LOG_POS=1345;
启动并查看状态
start slave;
show slave status \G;
创建账户并授权
# 创建it用户,并设置密码,该用户可在任意主机连接该MySQL服务
CREATE USER 'it'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';
# 为 'it'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'it'@'%';
Slave1(192.168.226.104)
在配置文件 /etc/my.cnf 追加下述配置
# mysql 服务ID,保证整个集群环境中唯一,取值范围:1 – 2^32-1,默认为1
server-id=12
重启mysql
systemctl restart mysqld
从库关联主库
需要注意slave1对应的是master1,slave2对应的是master2。
CHANGE MASTER TO MASTER_HOST='192.168.226.102', MASTER_USER='it',
MASTER_PASSWORD='admin', MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=874;
启动并查看状态
start slave;
show slave status \G;
创建账户并授权
# 创建it用户,并设置密码,该用户可在任意主机连接该MySQL服务
CREATE USER 'it'@'%' IDENTIFIED WITH mysql_native_password BY 'admin';
# 为 'it'@'%' 用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'it'@'%';
两台主库相互复制
在master1执行
CHANGE MASTER TO MASTER_HOST='192.168.226.102', MASTER_USER='it',
MASTER_PASSWORD='admin', MASTER_LOG_FILE='binlog.000002',
MASTER_LOG_POS=656;
启动并查看状态
start slave;
show slave status \G;
在master2执行
CHANGE MASTER TO MASTER_HOST='192.168.226.101', MASTER_USER='it',
MASTER_PASSWORD='admin', MASTER_LOG_FILE='binlog.000003',
MASTER_LOG_POS=1345;
启动并查看状态
start slave;
show slave status \G;
当show slave status \G;查看的master1,master2,slave1,slave2的Slave_IO_Running:为Yes,
Slave_SQL_Running:为Yes,即代表完成配置。
双主双从测试
因为在配置文件里,我们在两个master配置文件里指定了只有特定的db01,db02,db03库才会复制,因此执行下述sql创建库(该库名不是指定的),是不会进行同步的。
CREATE DATABASE your_database_name;
下面在master1中创建一个库db01,看其他三个库能否同步,正常情况下都可以同步。
CREATE DATABASE db01;
然后再到master2中对这个库进行一些操作,看其他三个库能否同步,正常情况下都能同步。
USE db01;
CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
position VARCHAR(50),
salary DECIMAL(10, 2)
);
INSERT INTO employees (name, position, salary) VALUES
('John Doe', 'Software Engineer', 75000.00),
('Jane Smith', 'Data Analyst', 65000.00),
('Emily Johnson', 'Product Manager', 80000.00);
如果上述测试都满足了,则双主双从即完成。
双主双从读写分离
在192.168.226.100主机安装mycat,详细步骤见:MySQ分库分表与MyCat安装配置-CSDN博客
schema.xml配置
将下述配置替换原文件里的内容即可。
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="IT_RW2" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema>
<dataNode name="dn1" dataHost="dhost1" database="db01" />
<dataHost name="dhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="jdbc" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="master1" url="jdbc:mysql://192.168.226.101:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="it" password="admin">
<readHost host="slave1" url="jdbc:mysql://192.168.226.103:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="it" password="admin" />
</writeHost>
<writeHost host="master2" url="jdbc:mysql://192.168.226.102:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="it" password="admin">
<readHost host="slave2" url="jdbc:mysql://192.168.226.104:3306?useSSL=false&serverTimezone=Asia/Shanghai&characterEncoding=utf8" user="it" password="admin" />
</writeHost>
</dataHost>
</mycat:schema>
个别属性说明:
balance="1"
- 代表全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡,简 单的说,当双主双从模式(M1->S1,M2->S2,并且 M1 与 M2 互为主备),正常情况下, M2,S1,S2 都参与 select 语句的负载均衡 ;
writeType
- 0 : 写操作都转发到第1台writeHost, writeHost1挂了, 会切换到writeHost2上;
- 1 : 所有的写操作都随机地发送到配置的writeHost上 ;
switchType
- -1 : 不自动切换
- 1 : 自动切换
server.xml配置
只需要修改下述模块位置的代码即可
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">IT_RW2</property>
<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>
<user name="user">
<property name="password">123456</property>
<property name="schemas">IT_RW2</property>
<property name="readOnly">true</property>
</user>
重启mycat
/usr/local/mycat/bin/mycat stop
/usr/local/mycat/bin/mycat start
测试
登录mycat查询
[root@mycat ~]# mysql -h 192.168.226.100 -P8066 -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 25
Server version: 5.6.29-mycat-1.6.7.3-release-20210913163959 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+----------+
| DATABASE |
+----------+
| IT_RW2 |
+----------+
1 row in set (0.00 sec)
mysql> use IT_RW2;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+----------------+
| Tables_in_db01 |
+----------------+
| employees |
+----------------+
1 row in set (0.00 sec)
mysql> select * from employees;
+------+---------------+-------------------+----------+
| id | name | position | salary |
+------+---------------+-------------------+----------+
| 1 | John Doe | Software Engineer | 75000.00 |
| 2 | Jane Smith | Data Analyst | 65000.00 |
| 3 | Emily Johnson | Product Manager | 80000.00 |
+------+---------------+-------------------+----------+
3 rows in set (0.06 sec)
注:
如果遇到查询表提示没有权限这类问题,如
mysql> show tables;;
ERROR 1044 (HY000): Access denied for user 'it'@'%' to database 'db01'就去检查是否在两个从库中创建了这个用户并授权,然后看schema.xml配置账户密码有无错误,如果还是提示这个报错就在两个从库中执行这个sql语句,然后重启mycat再次尝试即可。再插入时遇到这类问题,也是同样的步骤给两个master执行这个sql即可。
GRANT ALL PRIVILEGES ON db01.* TO 'it'@'%'; FLUSH PRIVILEGES;
在mycat中插入数据,检查四个库是否同步,正常情况下都会同步。
INSERT INTO employees (id, name, position, salary)
VALUES (4, 'Michael Brown', 'UX Designer', 70000.00);
在slave1和slave2分别修改刚插入的这个数据名,正常情况下修改从库,是不会同步到其他库的,可以以此来验证。
在slave1执行sql
use db01;
UPDATE employees
SET name = '张三'
WHERE id = 4;
在slave2执行sql
use db01;
UPDATE employees
SET name = '李四'
WHERE id = 4;
修改好后,在slave1查看这个时张三,在slave2中查看是李四,但是在两个master主库中查看是原名Michael Brown ,然后在mycat中多次执行查看数据,这三个名字都会看到才是正常的,多次读取的内容来自于slave1的,slave2的,master2的,但是不会是master1这个主库的。
接下来测试见master1的mysql服务关闭模拟故障,看是能切换到master2,能否进行插入和查询操作。
在master1执行关闭mysql服务
systemctl stop mysqld
在mycat主机里进入mycat查看,并插入一条数据验证
INSERT INTO employees (id, name, position, salary)
VALUES (5, '王五', 'Marketing Specialist', 72000.00);
select * from employees;
正确情况下是sql语句会执行成功,并且可以同步到slave2的库中,由于master1关闭了,slave1是不会同步数据的。