200字
实战案例：利用 Mycat 实现 MySQL 的读写分离
2026-03-03
2026-03-03
mysql

实战案例：利用 Mycat 实现 MySQL 的读写分离

所有主机的系统环境

cat /etc/centos-release
CentOS Linux release 8.0.1905 (Core)

服务器共三台

mycat-server 10.0.0.8 #内存建议2G以上
mysql-master 10.0.0.18 MySQL 8.0 或者Mariadb 10.3.17
mysql-slave 10.0.0.28 MySQL 8.0 或者Mariadb 10.3.17

关闭SELinux和防火墙

systemctl stop firewalld
setenforce 0
时间同步

1、创建 MySQL 主从数据库

[root@centos8 ~]#yum -y install mysql-server
#或者
[root@centos8 ~]#yum -y install mariadb-server

1) 修改master和slave上的配置文件

#master上的my.cnf
[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id = 1
log-bin

#slave上的my.cnf
[mysqld]
server-id = 2

[root@centos8 ~]#systemctl start mariadb

2) Master上创建复制用户

[root@centos8 ~]#mysql -uroot -p
MariaDB [(none)]>GRANT REPLICATION SLAVE ON *.* TO 'repluser'@'10.0.0.%' IDENTIFIED BY 'replpass';
mysql> FLUSH PRIVILEGES;
mysql> show master status;
+------------------+----------+--------------+------------------+---------------
----+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
Executed_Gtid_Set |
+------------------+----------+--------------+------------------+---------------
----+
|mariadb-bin.000001| 403 | | |
|
+------------------+----------+--------------+------------------+---------------
----+
1 row in set (0.00 sec)
  1. Slave上执行
[root@centos8 ~]#mysql -uroot -p
mysql> CHANGE MASTER TO
-> MASTER_HOST='10.0.0.18',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='replpass',
-> MASTER_LOG_FILE='mariadb-bin.000001',
-> MASTER_LOG_POS=403;

mysql> start slave;
Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.18
Master_User: repluser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mariadb-bin.000001
Read_Master_Log_Pos: 439
Relay_Log_File: mariadb-relay-bin.000002
Relay_Log_Pos: 689
Relay_Master_Log_File: mariadb-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
...省略..,

2、在MySQL代理服务器10.0.0.8安装mycat并启动

root@centos8 ~]#yum -y install java
#确认安装成功
[root@centos8 ~]#java -version
openjdk version "1.8.0_201"
OpenJDK Runtime Environment (build 1.8.0_201-b09)
OpenJDK 64-Bit Server VM (build 25.201-b09, mixed mode)

#下载并安装
[root@centos8 ~]#wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
#wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz

[root@centos8 ~]#mkdir /apps
[root@centos8 ~]#tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/
#tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps

#配置环境变量
[root@centos8 ~]#echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
[root@centos8 ~]#source /etc/profile.d/mycat.sh

#查看端口
[root@centos8 ~]#ss -ntl
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 128 [::]:22 [::]:*
#启动mycat
[root@mycat ~]#file /apps/mycat/bin/mycat
/apps/mycat/bin/mycat: POSIX shell script, ASCII text executable
[root@mycat ~]#mycat
Usage: /apps/mycat/bin/mycat { console | start | stop | restart | status | dump}

#注意: 此步启动较慢,需要等一会儿,另外如果内存太小,会导致无法启动
[root@centos8 ~]#mycat start
Starting Mycat-server...

#可以看到打开多个端口,其中8066端口用于连接MyCAT
[root@centos8 ~]#ss -ntlp
State Recv-Q Send-Q Local Address:Port Peer Address:Port
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
users:(("sshd",pid=791,fd=5))
LISTEN 0 1 127.0.0.1:32000 0.0.0.0:*
users:(("java",pid=4640,fd=4))
LISTEN 0 128 [::]:22 [::]:*
users:(("sshd",pid=791,fd=7))
LISTEN 0 50 *:1984 *:*
users:(("java",pid=4640,fd=57))
LISTEN 0 100 *:8066 *:*
users:(("java",pid=4640,fd=87))
LISTEN 0 50 *:43465 *:*
users:(("java",pid=4640,fd=58))
LISTEN 0 100 *:9066 *:*
users:(("java",pid=4640,fd=83))
LISTEN 0 50 *:45259 *:*
users:(("java",pid=4640,fd=56))

#查看日志，确定成功，可能需要等一会儿才能看到成功的提示
[root@centos8 ~]#tail /apps/mycat/logs/wrapper.log
ERROR | wrapper | 2020/02/28 15:21:48 | Startup failed: Timed out waiting for
a signal from the JVM.
ERROR | wrapper | 2020/02/28 15:21:48 | JVM did not exit on request,
terminated
INFO | wrapper | 2020/02/28 15:21:48 | JVM exited on its own while waiting to
kill the application.
STATUS | wrapper | 2020/02/28 15:21:48 | JVM exited in response to signal
SIGKILL (9).
STATUS | wrapper | 2020/02/28 15:21:52 | Launching a JVM...
INFO | jvm 2 | 2020/02/28 15:21:52 | OpenJDK 64-Bit Server VM warning:
ignoring option MaxPermSize=64M; support was removed in 8.0
INFO | jvm 2 | 2020/02/28 15:22:13 | Wrapper (Version 3.2.3)
http://wrapper.tanukisoftware.org
INFO | jvm 2 | 2020/02/28 15:22:13 | Copyright 1999-2006 Tanuki Software,
Inc. All Rights Reserved.
INFO | jvm 2 | 2020/02/28 15:22:13 |
INFO | jvm 2 | 2020/02/28 15:22:31 | MyCAT Server startup successfully. see
logs in logs/mycat.log

 #用默认密码123456来连接mycat
[root@centos8 ~]#mysql -uroot -p123456 -h 10.0.0.8 -P8066
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server
(OpenCloundDB)
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)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.01 sec)

MySQL [TESTDB]> show tables;
+------------------+
| Tables in TESTDB |
+------------------+
| address |
| travelrecord |
+------------------+
2 rows in set (0.01 sec)

MySQL [TESTDB]> select * from travelrecord ;
ERROR 1105 (HY000): backend connect: java.lang.IllegalArgumentException: Invalid
DataSource:0
MySQL [TESTDB]>

4、在mycat 服务器上修改server.xml文件配置Mycat的连接信息

[root@centos8 ~]#vim /apps/mycat/conf/server.xml
...省略...
#修改下面行的8066改为3306复制到到独立非注释行
<property name="serverPort">3306</property>
<property name="handlelDistributedTransactions">0</property> #将上面行放在此行前面

#或者删除注释,并修改下面行的8066改为3306
<property name="serverPort">3306</property>
<property name="managerPort">9066</property>
<property name="idleTimeout">300000</property>
<property name="authTimeout">15000</property>
<property name="bindIp">0.0.0.0</property>
<property name="dataNodeIdleCheckPeriod">300000</property> #5 * 60 * 1000L; //连接空闲检查 删除#后面此部分
<property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> #--> 删除#后面此部分
....
<user name="root"> #连接Mycat的用户名
<property name="password">ayaka</property> #连接Mycat的密码
<property name="schemas">TESTDB</property> #数据库名要和schema.xml相对应</user>
</mycat:server>

这里使用的是root，密码为ayaka,逻辑数据库为TESTDB，这些信息都可以自己随意定义,读写权限都有，没有针对表做任何特殊的权限。重点关注上面这段配置，其他默认即可。

5、修改schema.xml实现读写分离策略

[root@centos8 ~]#vim /apps/mycat/conf/schema.xml

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="***false***" sqlMaxLimit="100"
dataNode="***dn1***"></schema>
<dataNode name="dn1" dataHost="localhost1" database="***mycat***" /> #其中mycat
表示后端服务器实际的数据库名称
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="***1***"
writeType="0" dbType="mysql" dbDriver="native" switchType="1"
slaveThreshold="100">
<heartbeat>select user()</heartbeat>
***<writeHost host="host1" url="10.0.0.18:3306" user="root"
password="123456">***
***<readHost host="host2" url="10.0.0.28:3306" user="root" password="123456"
/>***
</writeHost>
</dataHost>
</mycat:schema>

#以上***部分表示原配置文件中需要修改的内容
#注意大小写

最终文件内容
[root@mycat ~]#cat /apps/mycat/conf/schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
	<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"
dataNode="dn1">
	</schema>
	<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
			writeType="0" dbType="mysql" dbDriver="native" switchType="1"
  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<writeHost host="host1" url="10.0.0.18:3306" user="root"
					password="123456">
		<readHost host="host2" url="10.0.0.28:3306" user="root"
password="123456" />
		</writeHost>
	</dataHost>
</mycat:schema>
#重新启动mycat
[root@centos8 ~]#mycat restart

上面配置中，balance改为1，表示读写分离。以上配置达到的效果就是10.0.0.18为主库，10.0.0.28为从库
注意：要保证能使用root/123456权限成功登录10.0.0.18和10.0.0.28机器上面的mysql数据库。同时，也一定要授权mycat机器能使用root/123456权限成功登录这两台机器的mysql数据库！！这很重要，否则会导致登录mycat后，对库和表操作失败！

6、在后端主服务器创建用户并对mycat授权

[root@centos8 ~]#mysql -uroot -p
mysql>create database mycat;
mysql>GRANT ALL ON *.* TO 'root'@'10.0.0.%' IDENTIFIED BY '123456' ;
mysql> flush privileges;

7、在Mycat服务器上连接并测试

[root@centos8 ~]#mysql -uroot -payaka -h127.0.0.1 TESTDB
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB | //只能看一个虚拟数据库
+----------+
mysql> use TESTDB;
mysql> create table t1(id int);
MySQL> select @@server_id;
MySQL> select @@hostname;

8、通过通用日志确认实现读写分离

在mysql中查看通用日志

show variables like 'general_log'; #查看日志是否开启
set global general_log=on; #开启日志功能
show variables like 'general_log_file'; #查看日志文件保存位置
set global general_log_file='tmp/general.log'; #设置日志文件保存位置

在主和从服务器分别启用通用日志，查看读写分离

[root@centos8 ~]#vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
general_log=ON

[root@centos8 ~]#systemctl restart mariadb
[root@centos8 ~]#tail -f /var/lib/mysql/centos8.log

9、停止从节点，MyCAT自动调度读请求至主节点

[root@slave ~]#systemctl stop mariadb
[root@client ~]#mysql -uroot -payaka -h10.0.0.8 -P8066
MySQL [(none)]> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
MySQL [(none)]>

#停止主节点，MyCAT不会自动调度写请求至从节点
MySQL [TESTDB]> insert teachers values(5,'wang',30,'M');
ERROR 1184 (HY000): java.net.ConnectException: Connection refused

10、MyCAT对后端服务器的健康性检查方法select user()

#开启通用日志
[root@master ~]#mysql
mysql> set global general_log=1;

[root@slave ~]#mysql
mysql> set global general_log=1;

#查看通用日志
[root@master ~]#tail -f /var/lib/mysql/master.log
/usr/libexec/mysqld, Version: 8.0.17 (Source distribution). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2021-02-22T08:52:57.086198Z 17 Query select user()
2021-02-22T08:53:07.086340Z 24 Query select user()
2021-02-22T08:53:17.086095Z 16 Query select user()
2021-02-22T08:53:27.086629Z 18 Query select user()

[root@slave ~]#tail -f /var/lib/mysql/slave.log
/usr/libexec/mysqld, Version: 8.0.17 (Source distribution). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
2021-02-22T08:46:01.437376Z 10 Query select user()
2021-02-22T08:46:11.438172Z 11 Query select user()
2021-02-22T08:46:21.437458Z 12 Query select user()
2021-02-22T08:46:31.437742Z 13 Query select user(
Mycat
ProxySQL

评论

© Ayaka's Blog. All Rights Reserved. / RSS / Sitemap
Powered by Halo & Theme-Fuwari
冀ICP备2026000147号-1 | 冀公网安备13063502000135号