Mysql的读写分离

环境准备

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 eventMaster_Host: 192.168.10.200Master_User: repluserMaster_Port: 3306Connect_Retry: 60Master_Log_File: mysql200.000002Read_Master_Log_Pos: 832Relay_Log_File: mysql201-relay-bin.000003Relay_Log_Pos: 325Relay_Master_Log_File: mysql200.000002Slave_IO_Running: YesSlave_SQL_Running: YesReplicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0Last_Error: Skip_Counter: 0Exec_Master_Log_Pos: 832Relay_Log_Space: 718Until_Condition: NoneUntil_Log_File: Until_Log_Pos: 0Master_SSL_Allowed: NoMaster_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: NoLast_IO_Errno: 0Last_IO_Error: Last_SQL_Errno: 0Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 200Master_UUID: cd412317-3719-11f0-b5ae-000c293e8f5aMaster_Info_File: mysql.slave_master_infoSQL_Delay: 0SQL_Remaining_Delay: NULLSlave_SQL_Running_State: Replica has read all relay log; waiting for more updatesMaster_Retry_Count: 86400Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: Master_public_key_path: Get_master_public_key: 0Network_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.zip

2)安装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: whost200USERNAME: pljaPASSWORD: 123456MAX_CON: 1000MIN_CON: 1EXIST_CON: 0USE_CON: 0MAX_RETRY_COUNT: 5MAX_CONNECT_TIMEOUT: 30000DB_TYPE: mysqlURL: jdbc:mysql://192.168.10.200:3306?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=trueWEIGHT: 0INIT_SQL: 
INIT_SQL_GET_CONNECTION: trueINSTANCE_TYPE: READ_WRITEIDLE_TIMEOUT: 60000DRIVER: {CreateTime:"2025-05-22 11:48:24",ActiveCount:0,PoolingCount:0,CreateCount:0,DestroyCount:0,CloseCount:0,ConnectCount:0,Connections:[]
}TYPE: JDBCIS_MYSQL: true
*************************** 2. row ***************************NAME: rhost201USERNAME: pljaPASSWORD: 123456MAX_CON: 1000MIN_CON: 1EXIST_CON: 0USE_CON: 0MAX_RETRY_COUNT: 5MAX_CONNECT_TIMEOUT: 30000DB_TYPE: mysqlURL: jdbc:mysql://192.168.10.201:3306?useUnicode=true&serverTimezone=Asia/Shanghai&characterEncoding=UTF-8&autoReconnect=trueWEIGHT: 0INIT_SQL: 
INIT_SQL_GET_CONNECTION: trueINSTANCE_TYPE: READ_WRITEIDLE_TIMEOUT: 60000DRIVER: {CreateTime:"2025-05-22 11:48:24",ActiveCount:0,PoolingCount:0,CreateCount:0,DestroyCount:0,CloseCount:0,ConnectCount:0,Connections:[]
}TYPE: JDBCIS_MYSQL: true
*************************** 3. row ***************************NAME: prototypeDsUSERNAME: pljPASSWORD: 123456MAX_CON: 1000MIN_CON: 1EXIST_CON: 0USE_CON: 0MAX_RETRY_COUNT: 5MAX_CONNECT_TIMEOUT: 3000DB_TYPE: mysqlURL: jdbc:mysql://localhost:3306/mysql?serverTimezone=Asia/Shanghai&useUnicode=true&characterEncoding=UTF-8&autoReconnect=trueWEIGHT: 0INIT_SQL: 
INIT_SQL_GET_CONNECTION: trueINSTANCE_TYPE: READ_WRITEIDLE_TIMEOUT: 60000DRIVER: {CreateTime:"2025-05-22 11:48:24",ActiveCount:0,PoolingCount:0,CreateCount:0,DestroyCount:0,CloseCount:0,ConnectCount:0,Connections:[]
}TYPE: JDBCIS_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: rwclusterSWITCH_TYPE: SWITCH
MAX_REQUEST_COUNT: 2000TYPE: BALANCE_ALLWRITE_DS: whost200READ_DS: whost200,rhost201WRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1READ_L: io.mycat.plug.loadBalance.BalanceRandom$1AVAILABLE: true
*************************** 2. row ***************************NAME: prototypeSWITCH_TYPE: SWITCH
MAX_REQUEST_COUNT: 200TYPE: BALANCE_ALLWRITE_DS: prototypeDsREAD_DS: prototypeDsWRITE_L: io.mycat.plug.loadBalance.BalanceRandom$1READ_L: io.mycat.plug.loadBalance.BalanceRandom$1AVAILABLE: 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   |
+----------+----------+