Mycat的读写分离

---

MySQL主从复制原理

Mysql的主从复制中主要有三个线程：master（binlog dump thread）、slave（I/O thread 、SQL thread），Master一条线程和Slave中的两条线程。

master（binlog dump thread）主要负责Master库中有数据更新的时候，会按照binlog格式，将更新的事件类型写入到主库的binlog文件中。

并且，Master会创建log dump线程通知Slave主库中存在数据更新，这就是为什么主库的binlog日志一定要开启的原因。

I/O thread线程在Slave中创建，该线程用于请求Master，Master会返回binlog的名称以及当前数据更新的位置、binlog文件位置的副本。

然后，将binlog保存在 「relay log（中继日志）」 中，中继日志也是记录数据更新的信息。

SQL线程也是在Slave中创建的，当Slave检测到中继日志有更新，就会将更新的内容同步到Slave数据库中，这样就保证了主从的数据的同步。

MySQL主从环境搭建

设置server-id值并开启binlog参数开启gtid
[mysqld]
server_id=10
log_bin=master
gtid_mode=on
enforce_gtid_consistency=true

server_id 主从库不能重复

使用docker搭建

CONTAINER ID        IMAGE               COMMAND                  CREATED             STATUS              PORTS                               NAMES
1224ecb4130b        mysql:5.7           "docker-entrypoint..."   25 minutes ago      Up 25 minutes       33060/tcp, 0.0.0.0:3309->3306/tcp   mysql3
e8ac80097bd2        mysql:5.7           "docker-entrypoint..."   25 minutes ago      Up 25 minutes       33060/tcp, 0.0.0.0:3308->3306/tcp   mysql2
e9c72c63c9c2        mysql:5.7           "docker-entrypoint..."   25 minutes ago      Up 25 minutes       33060/tcp, 0.0.0.0:3307->3306/tcp   mysql1

docker 里面不好编辑 cp 到服务器操作

docker cp mysql1:/etc/mysql/conf.d/docker.cnf /root/mysqlcnf/

主库设置

在 /etc/mysql/conf.d/mysql1.cnf 添加

server_id=10
log_bin=master
gtid_mode=on
enforce_gtid_consistency=true

从库1配置
在/etc/mysql/conf.d/mysql2.cnf 添加

server_id=11
log_bin=slave
gtid_mode=on
enforce_gtid_consistency=true

从库2配置
在/etc/mysql/conf.d/mysql3.cnf 添加

server_id=12
log_bin=slave
gtid_mode=on
enforce_gtid_consistency=true

然后上传到各个docker中

[root@mysql mysqlcnf]# docker cp /root/mysqlcnf/mysql1.cnf  mysql1:/etc/mysql/conf.d/docker.cnf
[root@mysql mysqlcnf]# docker cp /root/mysqlcnf/mysql2.cnf  mysql2:/etc/mysql/conf.d/docker.cnf
[root@mysql mysqlcnf]# docker cp /root/mysqlcnf/mysql3.cnf  mysql3:/etc/mysql/conf.d/docker.cnf
# 重启
[root@mysql mysqlcnf]# docker restart mysql1 mysql2 mysql3
mysql1
mysql2
mysql3

主库配置

建立同步账号
mysql> grant replication slave on *.* to 'rep'@'192.168.1.%' identified by
'123456';

从库配置

change master to
master_host='192.168.1.50',
master_user='repl',
master_password='123456',
master_auto_position=1;

验证

主库创建一个数据库

从库

主从配置成功

MySQL读写分离

一主两从
环境搭建
需要这三个文件
要有Java环境 mycat

-rw-r--r--  1 root root 127431820 Mar 31 00:31 jdk-8u261-linux-x64.rpm
-rw-r--r--  1 root root 151819628 May  9  2022 mycat2-1.21-release-jar-with-dependencies.jar
-rw-r--r--  1 root root   1246974 May  9  2022 mycat2-install-template-1.21.zip

安装jdk
1、解压 mycat2-install-template-1.21.zip
2、将mycat2-1.21-release-jar-with-dependencies.jar 放在mycat 的lib中
3、给mycat下的bin目录赋可执行权限+x
4、在bin目录下启动mycat

1、unzip mycat2-install-template-1.21.zip -d /data/
2、mv mycat2-1.21-release-jar-with-dependencies.jar /data/mycat/lib/
3、chmod +x /data/mycat/bin/*
4、
[root@mysql bin]# ./mycat start
Starting mycat2...
mycat2 is already running.
[root@mysql bin]# ./mycat status
mycat2 is running (18162).

5、使用连接工具连接端口8066

6、主从配置 之前已经配好
7、添加主的数据源（可读可写）

{
	"dbType":"mysql",
	"idleTimeout":60000,
	"initSqls":[],
	"initSqlsGetConnection":true,
	"instanceType":"READ_WRITE",
	"logAbandoned":true,
	"maxCon":1000,
	"maxConnectTimeout":3000,
	"maxRetryCount":5,
	"minCon":1,
	"name":"m1",
	"password":"123456",
	"queryTimeout":0,
	"removeAbandoned":false,
	"removeAbandonedTimeoutSecond":180,
	"type":"JDBC",
	"url":"jdbc:mysql://127.0.0.1:3306/db1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
	"user":"root",
	"weight":0
}

8、添加两个从的数据源

{
	"dbType":"mysql",
	"idleTimeout":60000,
	"initSqls":[],
	"initSqlsGetConnection":true,
	"instanceType":"READ",
	"logAbandoned":true,
	"maxCon":1000,
	"maxConnectTimeout":3000,
	"maxRetryCount":5,
	"minCon":1,
	"name":"m1s1",
	"password":"123456",
	"queryTimeout":0,
	"removeAbandoned":false,
	"removeAbandonedTimeoutSecond":180,
	"type":"JDBC",
	"url":"jdbc:mysql://127.0.0.1:3307/db1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
	"user":"root",
	"weight":0
}

{
	"dbType":"mysql",
	"idleTimeout":60000,
	"initSqls":[],
	"initSqlsGetConnection":true,
	"instanceType":"READ",
	"logAbandoned":true,
	"maxCon":1000,
	"maxConnectTimeout":3000,
	"maxRetryCount":5,
	"minCon":1,
	"name":"m1s2",
	"password":"123456",
	"queryTimeout":0,
	"removeAbandoned":false,
	"removeAbandonedTimeoutSecond":180,
	"type":"JDBC",
	"url":"jdbc:mysql://127.0.0.1:3308/db1?serverTimezone=UTC&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true",
	"user":"root",
	"weight":0
}

9、查看

10、创建集群

{
	"clusterType":"MASTER_SLAVE",
	"heartbeat":{
		"heartbeatTimeout":1000,
		"maxRetryCount":0,
		"minSwitchTimeInterval":300,
		"showLog":true,
		"slaveThreshold":0.0
	},
	"masters":[
		"m1"
	],
	"maxCon":2000,
	"name":"prototype",
	"readBalanceType":"BALANCE_ALL",
	"replicas":[
		"m1s1",
		"m1s2"
	],
	"switchType":"SWITCH"
}

11、创建逻辑库

CREATE DATABASE db1 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

12、修改逻辑库的数据源

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

13、测试读写分离是否成功

use db1
CREATE TABLE SYS_USER( ID BIGINT PRIMARY KEY, USERNAME VARCHAR(200) NOT NULL,
ADDRESS VARCHAR(500));
INSERT INTO SYS_USER(ID,USERNAME,ADDRESS) VALUES(1,"XIAOMING","WUHAN");