环境准备
| IP地址 | 主机名 | 角色 |
| 192.168.10.200 | mysql200 | Master服务器 |
| 192.168.10.201 | mysql201 | Slave服务器 |
| 192.168.10.202 | mysql202 | 读写分离服务器 |
步骤一：搭建一主一从结构
Master主机 增加以下配置参数
[root@mysql200 ~]# tail -n 2 /etc/my.cnf.d/mysql-server.cnf
log-bin=/mylog/mysql200
server-id=200
[root@mysql200 ~]# mysql
mysql> create user repluser@'%' identified by '123456';
mysql> grant all privileges on *.* to repluser@'%';
mysql> flush privileges;
mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysql200.000002 | 832 | | | |
+-----------------+----------+--------------+------------------+-------------------+
Slave主机 增加以下配置参数
[root@mysql201 ~]# tail -n 1 /etc/my.cnf.d/mysql-server.cnf
server-id=201
[root@mysql201 ~]# mysql
mysql> change master to master_host='192.168.10.200',master_password='123456',master_user='repluser',master_log_file='mysql200.000002',master_log_pos=832;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.10.200
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql200.000002
Read_Master_Log_Pos: 832
Relay_Log_File: mysql201-relay-bin.000003
Relay_Log_Pos: 325
Relay_Master_Log_File: mysql200.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 832
Relay_Log_Space: 718
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 200
Master_UUID: cd412317-3719-11f0-b5ae-000c293e8f5a
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Replica has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
步骤二：配置mycat服务器
1)从真机拷贝软件到mycat202主机
[root@mysql202 ~]# ls mycat2-*
mycat2-1.21-release-jar-with-dependencies.jar mycat2-install-template-1.21.zip2)安装mycat软件
2)安装mycat软件
//安装jdk
[root@mysql202 ~]# yum -y install java-1.8.0-openjdk.x86_64
//安装mycat
[root@mysql202 ~]# unzip mycat2-install-template-1.21.zip
[root@mysql202 ~]# mv mycat /usr/local/
//安装依赖
[root@mysql202 ~]# cp mycat2-1.21-release-jar-with-dependencies.jar /usr/local/mycat/lib/
//修改权限
[root@mysql202 ~]# chmod 777 /usr/local/mycat/
3)定义客户端连接mycat服务使用用户及密码：
[root@mysql202 ~]# cat /usr/local/mycat/conf/users/root.user.json
{
"dialect":"mysql",
"ip":null,
"password":"654321",
"transactionType":"proxy",
"username":"mycat"
}
4）定义连接的数据库服务器
[root@mysql202 ~]# cat /usr/local/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/mysql?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8",
"user":"plj",
"weight":0
}
5)在mycat202主机运行数据库服务
[root@mysql202 ~]# mysql
mysql> create user plj@'%' identified by '123456';
mysql> grant all on *.* to plj@'%';
mysql> flush privileges;
6)启动mycat服务
[root@mysql202 ~]# chmod +x /usr/local/mycat/bin/*
[root@mysql202 ~]# /usr/local/mycat/bin/mycat start
Starting mycat2...
[root@mysql202 ~]# netstat -utnlp | grep 8066
tcp6 0 0 :::8066 :::* LISTEN 86768/java 7) 连接mycat服务
[root@mysql202 ~]# mysql -umycat -p654321 -P8066 -h127.0.0.1
mysql> show databases;
+--------------------+
| `Database` |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
+--------------------+
步骤三：配置读写分离
1）添加数据源：连接mycat服务后做如下操作
[root@mysql202 ~]# mysql -umycat -p654321 -P8066 -h127.0.0.1
mysql> /*+ mycat:createdatasource {
-> "name":"whost200","url":"jdbc:mysql://192.168.10.200:3306","user":"plja","password":"123456"} */;
mysql> /*+ mycat:createdatasource {
-> "name":"rhost201","url":"jdbc:mysql://192.168.10.201:3306","user":"plja","password":"123456"} */;
mysql> /*+ mycat:showdatasources {} */ \G
*************************** 1. row ***************************
NAME: whost200
USERNAME: plja
PASSWORD: 123456
MAX_CON: 1000
MIN_CON: 1
EXIST_CON: 0
USE_CON: 0
MAX_RETRY_COUNT: 5
MAX_CONNECT_TIMEOUT: 30000
DB_TYPE: mysql
URL: jdbc:mysql://192.168.10.200:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true
WEIGHT: 0
INIT_SQL:
INIT_SQL_GET_CONNECTION: true
INSTANCE_TYPE: READ_WRITE
IDLE_TIMEOUT: 60000
DRIVER: {
CreateTime:"2025-05-22 11:48:24",
ActiveCount:0,
PoolingCount:0,
CreateCount:0,
DestroyCount:0,
CloseCount:0,
ConnectCount:0,
Connections:[
]
}
TYPE: JDBC
IS_MYSQL: true
*************************** 2. row ***************************
NAME: rhost201
USERNAME: plja
PASSWORD: 123456
MAX_CON: 1000
MIN_CON: 1
EXIST_CON: 0
USE_CON: 0
MAX_RETRY_COUNT: 5
MAX_CONNECT_TIMEOUT: 30000
DB_TYPE: mysql
URL: jdbc:mysql://192.168.10.201:3306?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8&autoReconnect=true
WEIGHT: 0
INIT_SQL:
INIT_SQL_GET_CONNECTION: true
INSTANCE_TYPE: READ_WRITE
IDLE_TIMEOUT: 60000
DRIVER: {
CreateTime:"2025-05-22 11:48:24",
ActiveCount:0,
PoolingCount:0,
CreateCount:0,
DestroyCount:0,
CloseCount:0,
ConnectCount:0,
Connections:[
]
}
TYPE: JDBC
IS_MYSQL: true
*************************** 3. row ***************************
NAME: prototypeDs
USERNAME: plj
PASSWORD: 123456
MAX_CON: 1000
MIN_CON: 1
EXIST_CON: 0
USE_CON: 0
MAX_RETRY_COUNT: 5
MAX_CONNECT_TIMEOUT: 3000
DB_TYPE: mysql
URL: jdbc:mysql://localhost:3306/mysql?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=true
WEIGHT: 0
INIT_SQL:
INIT_SQL_GET_CONNECTION: true
INSTANCE_TYPE: READ_WRITE
IDLE_TIMEOUT: 60000
DRIVER: {
CreateTime:"2025-05-22 11:48:24",
ActiveCount:0,
PoolingCount:0,
CreateCount:0,
DestroyCount:0,
CloseCount:0,
ConnectCount:0,
Connections:[
]
}
TYPE: JDBC
IS_MYSQL: true
2）配置数据库服务器添加plja用户
//在master服务器添加
[root@mysql200 ~]# mysql
mysql> create user plja@"%" identified by "123456";
mysql> grant all on *.* to plja@"%";
mysql>exit
//在slave服务器查看是否同步成功
[root@mysql201 ~]# mysql -e 'select user , host from mysql.user where user="plja"'
+------+------+
| user | host |
+------+------+
| plja | % |
+------+------+
3）创建集群
mysql> /*!mycat:createcluster{
"name":"rwcluster",
"masters":["whost200"],
"replicas":["rhost201"]
}*/ ;
mysql> /*+ mycat:showClusters{}*/\G;
*************************** 1. row ***************************
NAME: rwcluster
SWITCH_TYPE: SWITCH
MAX_REQUEST_COUNT: 2000
TYPE: BALANCE_ALL
WRITE_DS: whost200
READ_DS: whost200,rhost201
WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
AVAILABLE: true
*************************** 2. row ***************************
NAME: prototype
SWITCH_TYPE: SWITCH
MAX_REQUEST_COUNT: 200
TYPE: BALANCE_ALL
WRITE_DS: prototypeDs
READ_DS: prototypeDs
WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1
READ_L: io.mycat.plug.loadBalance.BalanceRandom$1
AVAILABLE: true
2 rows in set (0.01 sec)
ERROR:
No query specified
//创建的集群以文件的形式保存在目录下
[root@mysql202 ~]# ls /usr/local/mycat/conf/clusters/
prototype.cluster.json rwcluster.cluster.json
4）指定主机角色
//修改master角色主机仅负责写访问
[root@mysql202 ~]# grep WRITE /usr/local/mycat/conf/datasources/whost200.datasource.json
"instanceType":"WRITE", #修改该配置参数
//修改slave角色主机仅负责读访问
[root@mysql202 ~]# grep READ /usr/local/mycat/conf/datasources/rhost201.datasource.json
"instanceType":"READ",
5）修改读策略
[root@mysql202 ~]# grep READ /usr/local/mycat/conf/clusters/rwcluster.cluster.json
"readBalanceType":"BALANCE_ALL_READ", #把读访问平均分配给read角色的主机
//重启mycat服务
[root@mysql202 ~]# /usr/local/mycat/bin/mycat restart
步骤四：测试配置
思路如下：
- 连接mycat服务建库
- 指定存储数据使用的集群
- 连接mycat服务建表
- 客户端连接mycat服务执行select 或 insert
具体操作如下：
//连接mycat服务
[root@mysql202 ~]# mysql -umycat -p654321 -P8066 -h127.0.0.1
//创建存储数据的库
mysql> create database testdb1;
mysql> exit
//指定库存储数据使用的集群
[root@mysql202 ~]# cat /usr/local/mycat/conf/schemas/testdb1.schema.json
{
"customTables":{},
"globalTables":{},
"normalProcedures":{},
"normalTables":{},
"schemaName":"testdb1",
"shardingTables":{},
"views":{},
"targetName":"rwcluster" #添加此行
}
//重启mycat服务
[root@mysql202 ~]# /usr/local/mycat/bin/mycat restart
//客户端连接mycat服务
[root@mysql202 ~]# mysql -umycat -p654321 -P8066 -h127.0.0.1
//建表
mysql> create table testdb1.user (name varchar(10) , password varchar(10));
//插入记录
mysql> insert into testdb1.user values("yaya","123456");
//查看表记录
mysql> select * from testdb.user;
+------+----------+
| name | password |
+------+----------+
| yaya | 123456 |
+------+----------+
mysql> exit
测试读写分离
//在slave服务器本机插入记录，使其与master服务器的数据不一样
[root@mysql201 ~]# mysql
mysql> insert into testdb1.user values("yaya1111","123456");
mysql> select * from testdb1.user;
+----------+----------+
| name | password |
+----------+----------+
| yaya | 123456 |
| yaya1111 | 123456 |
+----------+----------+
//主服务器数据不变
mysql> select * from testdb1.user;
+------+----------+
| name | password |
+------+----------+
| yaya | 123456 |
+------+----------+
//客户端连接mycat服务,读/写数据
mysql> select * from testdb1.user; 显示的是slave服务器user表的数据
+----------+----------+
| name | password |
+----------+----------+
| yaya | 123456 |
| yaya1111 | 123456 |
+----------+----------+
//插入表记录
mysql> insert into testdb1.user values("yaya2222","123456");
//在主服务器查看数据
mysql> select * from testdb1.user;
+----------+----------+
| name | password |
+----------+----------+
| yaya | 123456 |
| yaya2222 | 123456 |
//在mycat服务器查看数据
mysql> select * from testdb1.user;
+----------+----------+
| name | password |
+----------+----------+
| yaya | 123456 |
| yaya1111 | 123456 |
| yaya2222 | 123456 |
+----------+----------+