MySQL主从复制、读写分离（MayCat2)实现数据同步_读写分离怎么保证数据同步

1.MySQL主从复制原理。

MySQL主从复制是一个异步的复制过程，底层是基于Mysql数据库自带的 二进制日志 功能。就是一台或多台MySQL数据库（slave，即从库）从另一台MySQL数据库（master，即主库）进行日志的复制，然后再解析日志并应用到自身，最终实现 从库 的数据和 主库 的数据保持一致。MySQL主从复制是MySQL数据库自带功能，无需借助第三方工具。

主从介绍
MySQL主从又叫Replication、AB复制。简单讲就是A与B两台机器做主从后，在A上写数据，另外一台B
也会跟着写数据，实现数据实时同步。有这样几个关键点：
1）MySQL主从是基于binlog，主上需开启binlog才能进行主从；
2）主从过程大概有3个步骤；
3）主将更改操作记录到binlog里；
4）从将主的binlog事件（SQL语句） 同步本机上并记录在relaylog里；
5）从根据relaylog里面的SQL语句按顺序执行。
二进制日志
二进制日志（BINLOG）记录了所有的 DDL（数据定义语言）语句和 DML（数据操纵语言）语句，但是不包括数据查询语句。此日志对于灾难时的数据恢复起着极其重要的作用，MySQL的主从复制， 就是通过该binlog实现的。默认MySQL是未开启该日志的
主从作用
主从作用有：实时灾备，用于故障切换；读写分离，提供查询服务；备份，避免影响业务。
主从形式

基本原理

实验环境
基于centos7.9、mysql5.7

2.实现MySQL主从复制（一主两从）。

这里我借助docker来完成
①拉取mysql镜像并运行三个mysql容器
mysql1为主
mysql2和mysql3为从

docker pull mysql:5.7		#拉取mysql镜像
docker run --name mysql1 -p 3307:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --lower_case_table_names=1
docker run --name mysql2 -p 3308:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --lower_case_table_names=1
docker run --name mysql3 -p 3309:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7 --lower_case_table_names=1
1
2
3
4

②修改mysql配置文件
将docker上的文件拷贝出来，方便修改

mkdir /mysqlms
cd /mysqlms
docker cp mysql1:/etc/my.cnf mysql1.cnf
docker cp mysql2:/etc/my.cnf mysql2.cnf
docker cp mysql3:/etc/my.cnf mysql3.cnf
1
2
3
4
5

修改主的配置文件
mysql1
在[mysqld]下添加：

server-id=1	#服务器的标识
log-bin=master.bin	#打开日志文件功能	
1
2

修改两个从的配置文件
mysql2
在[mysqld]下添加

server-id=2
1

mysql3
在[mysqld]下添加

server-id=3
1

③配置文件修改后，复制到容器里面

docker cp mysql1.cnf mysql1:/etc/my.cnf
docker cp mysql2.cnf mysql2:/etc/my.cnf
docker cp mysql3.cnf mysql3:/etc/my.cnf
1
2
3

④启动mysql

[root@www mysqlms]# docker start mysql1 mysql2 mysql3
mysql1
mysql2
mysql3

1
2
3
4
5

用Navicat测试连接

⑤进入主机里面执行相关配置

[root@www mysqlms]# docker exec -it mysql1 bash
bash-4.2# mysql -uroot -p123456
1
2

#创建用户：
mysql> create user 'rep'@'%' identified by '123456';
Query OK, 0 rows affected (0.00 sec)
#给该用户授予权限：
mysql> grant replication slave on *.* to 'rep'@'%';
Query OK, 0 rows affected (0.00 sec)
#刷新权限：
mysql>  flush privileges;
Query OK, 0 rows affected (0.01 sec)

1
2
3
4
5
6
7
8
9
10

⑥ 进入从机里面执行相关配置（mysql2 和mysql3配置相同）
这里以mysql2为例
查看主的（mysql1）的状态

[root@www ~]# docker exec -it mysql2 bash
bash-4.2# mysql -uroot -p123456
mysql> change master to
-> master_host="192.168.15.136",master_port=3307,
-> master_user="rep",
-> master_password="123456",
-> master_log_file="master.000001",
-> master_log_pos=745;
Query OK, 0 rows affected, 2 warnings (0.07 sec)
1
2
3
4
5
6
7
8
9

启动slave并查看状态

成功标志：

Slave_IO_Running: Yes
Slave_SQL_Running: Yes
1
2

⑦测试
在mysql1 里面创建数据库，看mysq2、mysql3 有没有复制过去
mysql1上

mysq2、mysql3 上

主从复制成功！！！

3.基于MySQL一主两从配置，完成MySQL读写分离配置。（MyCat2）

前提，先搭建好MySQL的主从配置
首先确保有jdk环境

[root@s2 ~]# java -version
java version "1.8.0_361"
Java(TM) SE Runtime Environment (build 1.8.0_361-b09)
Java HotSpot(TM) 64-Bit Server VM (build 25.361-b09, mixed mode)

1
2
3
4
5

配置mycat环境

#安装程序包：
wget -c http://dl.mycat.org.cn/2.0/install-template/mycat2-install-template-1.21.zip
#jar包
wget -c http://dl.mycat.org.cn/2.0/1.21-release/mycat2-1.21-release-jar-with-dependencies.jar
1
2
3
4

解压到data目录下

[root@s2 ~]# mkdir /data
[root@s2 ~]# unzip mycat2-install-template-1.21.zip -d /data
1
2

修改权限
把bin目录的文件加执行权限：

cd /data/mycat/bin
chmod +x *
1
2

把所需的jar复制到mycat/lib目录

cp ~/mycat2-1.21-release-jar-with-dependencies.jar /data/mycat/lib/
1

MyCat的目录结构

 ll /data/mycat/
total 8
drwxr-xr-x 2 root root 4096 Mar 5 2021 bin
drwxr-xr-x 9 root root 275 Mar 5 2021 conf
drwxr-xr-x 2 root root 4096 Mar 20 14:11 lib
drwxr-xr-x 2 root root 6 Mar 5 2021 logs
1
2
3
4
5
6

bin 执行命令的目录
conf 配置文件
lib 依赖包
logs 日志包
启动一个3306的MySQL
因为mycat代理连接启动时需要有一个默认的数据源，所以我们在启动的时候先为其准备一个数据源，
接下来我们使用docker启动

# 配置docker加速
sudo mkdir -p /etc/docker
# tee /etc/docker/daemon.json <<-'EOF'
{
"registry-mirrors": ["https://rvq9mjyt.mirror.aliyuncs.com"]
}
EOF
# systemctl daemon-reload
# systemctl restart docker
# docker run --name mysql -p 3306:3306 -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7
--lower_case_table_names=1

1
2
3
4
5
6
7
8
9
10
11
12

配置物理库地址
在启动之前我们要配置物理库的地址，要不然MyCAT启动就会报错。

[root@localhost ~]# vim /data/mycat/conf/datasources/prototypeDs.datasource.json 

{
        "dbType":"mysql",
        "idleTimeout":60000,
        "initSqls":[],
        "initSqlsGetConnection":true,
        "instanceType":"READ_WRITE",
        "maxCon":1000,
        "maxConnectTimeout":3000,
        "maxRetryCount":5,
        "minCon":1,
        "name":"prototypeDs",
        "password":"123456",
        "type":"JDBC",
        "url":"jdbc:mysql://localhost:3306?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
        "user":"root",
        "weight":0
}

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

配置说明：
1）dbType：数据源类型
2）name：数据源名字
3）password：后端MySQL的密码
4）url：后端MySQL的JDBC连接地址
5）user：后端MySQL的用户名
6）weight：配置数据源负载均衡的使用权重

启动MyCat

cd /data/mycat/bin
./mycat start 
1
2

额外介绍：

./mycat console 前台运行
./mycat install 添加到系统自动启动
./mycat remove 取消随系统自动启动
./mycat restart 重启
./mycat pause 暂停
./mycat status 查看启动状态

查看logs/wrapper.log文档
如果出现以下的信息就说明启动成功了。

cat /data/mycat/logs/wrapper.log
1

** 连接MyCAT**
用Navicat连接
也可在虚拟机上看是否连接成功

# yum install -y mariadb
# mysql -uroot -p123456 -P8066
[root@node4 bin]# mysql -uroot -p123456 -P8066 -h127.0.0.1
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 71
Server version: 5.7.33-mycat-2.0 MySQL Community Server (GPL)
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MySQL [(none)]> \q
Bye
1
2
3
4
5
6
7
8
9
10

MyCAT2的主从配置
在上面的操作中，我们已经搭建好MySQL的主从配置，登录MyCAT 2在MyCAT2里面操作，也就是连接8066这个端口。
目标：将mysql1（主）设置为可读写的数据源，将mysql2（从）和mysql3（从）设置为只读的数据源。

以下操作均在Navicat上执行，但实际修改和创建的是/data/mycat/下的文件
创建数据源
选择mycat数据库，新建查询

# 添加读写的数据源
/*+ mycat:createDataSource{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ_WRITE",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"mysql1",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://127.0.0.1:3307/db1?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
"user":"root",
"weight":0
} */;

# 添加读的数据源

/*+ mycat:createDataSource{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"mysql2",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://127.0.0.1:3308/db1?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
"user":"root",
"weight":0
} */;

/*+ mycat:createDataSource{
"dbType":"mysql",
"idleTimeout":60000,
"initSqls":[],
"initSqlsGetConnection":true,
"instanceType":"READ",
"maxCon":1000,
"maxConnectTimeout":3000,
"maxRetryCount":5,
"minCon":1,
"name":"mysql3",
"password":"123456",
"type":"JDBC",
"url":"jdbc:mysql://127.0.0.1:3309/db1?useUnicode=true&serverTimezone=UTC&characterEncoding=UTF-8",
"user":"root",
"weight":0
} */;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57

查询数据源

/*+ mycat:showDataSources{} */
1

创建集群

/*! mycat:createCluster{
"clusterType":"MASTER_SLAVE",
"heartbeat":{
"heartbeatTimeout":1000,
"maxRetry":3,
"minSwitchTimeInterval":300,
"slaveThreshold":0
},
"masters":[
"mysql1"
],
"maxCon":2000,
"name":"prototype",
"readBalanceType":"BALANCE_ALL",
"replicas":[
"mysql1","mysql2"
],
"switchType":"SWITCH"
} */;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

查询集群

/*+ mycat:showClusters{} */
1

创建逻辑库

CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
1

此时/data/mycat/conf/schemas/db1.schema.json文件被创建
修改逻辑库的数据源
修改/data/mycat/conf/schemas/db1.schema.json
在里面添加 “targetName”:“prototype”,

[root@localhost conf]# vim /data/mycat/conf/schemas/db1.schema.json 

{
        "customTables":{},
        "globalTables":{},
        "normalProcedures":{},
        "normalTables":{},
        "schemaName":"db1",
        "shardingTables":{},
        "targetName":"prototype",
        "views":{}
}

1
2
3
4
5
6
7
8
9
10
11
12
13

测试读写分离是否成功(在MyCAT里面测试)
重启MyCAT（因为修改了数据源文件）：

[root@localhost conf]# cd /data/mycat/bin/
[root@localhost bin]# ./mycat restart
Stopping mycat2...
Stopped mycat2.
Starting mycat2...
[root@localhost bin]# 
1
2
3
4
5
6

在MyCAT里面创建一个sys_user表：

use db1;
CREATE TABLE SYS_USER( ID BIGINT PRIMARY KEY, USERNAME VARCHAR(200) NOT NULL,
ADDRESS VARCHAR(500));

1
2
3
4

通过注释生成物理库和物理表：
如果物理表不存在，在 MyCAT2 能正常启动的情况下，根据当前配置自动创建分片表，全局表和物理
表：

/*+ mycat:repairPhysicalTable{} */;
1

查看后端物理库：发现物理库和物理表都生成了。

在MyCAT里面向sys_user表添加一条数据：

use db1;
INSERT INTO SYS_USER(ID,USERNAME,ADDRESS) VALUES(1,"XIAOMING","WUHAN");
1
2

修改MySQL里面数据的让数据不一样：(仅用于测试验证)
mysql1上将ADDRESS字段修改成mysql1
mysql2上将ADDRESS字段修改成mysql2
mysql3上将ADDRESS字段修改成mysql3

在MyCAT里面查询数据，会发现每次查询的结果不一样：


MyCAT2主从搭建完成。