MyCAT实现MySQL读写分离

一、概述

MyCAT做为一个读写分离代理服务软件，可以在一定规模的场景使用，用读写、分割库表等，已实现分离来减轻主服务的压力，进而实现更加高级别的并发。

二、内容实践

1、先部署两台mariadb-server，并配置主从复制

 yum install -y mariadb-server

配置两台的 /etc/my.cnf.d/mariadb-server.cnf 文件中server-id，并开启log-bin

systemctl enable --now mariadb

配置数据库远程账户

grant all privileges on *.* to 'repuser'@'192.168.22.%' identified by 'test';

配置从数据库的远程连接主服务器

CHANGE MASTER TO
  MASTER_HOST='192.168.22.28',
  MASTER_USER='repuser',
  MASTER_PASSWORD='test',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='mariadb-bin.000002',
  MASTER_LOG_POS=344;

可以正常连接

2、安装MyCAT服务器

下载MyCAT二进制包至/app中

yum install java -y
 wget http://dl.mycat.org.cn/1.6.7.6/20210730131311/Mycat-server-1.6.7.6-release-20210730131311-linux.tar.gz
tar -xf Mycat-server-1.6.7.6-release-20210730131311-linux.tar.gz -C /app/

添加环境变量

echo 'PATH=/app/mycat/bin:$PATH' > /etc/profile.d/mycat.sh

更改mycat默认默认登陆密码123456 vim /app/mycat/conf/server.xml

配置文件中还存在默认user用户，如果不需要可以将其注释 更改mycat默认的8066端口为3306，配置选项默认是被注释掉，需要取消注释后才能生效。

修改配置文件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'>
		<!-- auto sharding by id (long) -->
		<!--splitTableNames 启用<table name 属性使用逗号分割配置多个表,即多个表使用这个配置-->
<!--fetchStoreNodeByJdbc 启用ER表使用JDBC方式获取DataNode-->
<!--		<table name="customer" primaryKey="id" dataNode="dn1,dn2" rule="sharding-by-intfile" autoIncrement="true" fetchStoreNodeByJdbc="true">
			<childTable name="customer_addr" primaryKey="id" joinKey="customer_id" parentKey="id"> </childTable>
		</table>
-->
		<!-- <table name="oc_call" primaryKey="ID" dataNode="dn1$0-743" rule="latest-month-calldate"
			/> -->
	</schema>
	<!-- <dataNode name="dn1$0-743" dataHost="localhost1" database="db$0-743"
		/> -->
	<dataNode name="dn1" dataHost="localhost1" database="hellodb" />
	<!--
	<dataNode name="dn2" dataHost="localhost1" database="db2" />
	<dataNode name="dn3" dataHost="localhost1" database="db3" />-->
	<!--<dataNode name="dn4" dataHost="sequoiadb1" database="SAMPLE" />
	 <dataNode name="jdbc_dn1" dataHost="jdbchost" database="db1" />
	<dataNode	name="jdbc_dn2" dataHost="jdbchost" database="db2" />
	<dataNode name="jdbc_dn3" 	dataHost="jdbchost" database="db3" /> -->
	<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
			  writeType="0" dbType="mysql" dbDriver="native" switchType="1"  slaveThreshold="100">
		<heartbeat>select user()</heartbeat>
		<!-- can have multi write hosts -->
		<!--
		<writeHost host="hostM1" url="jdbc:mysql://localhost:3306" user="root"
				   password="root">
		</writeHost>
-->
		<writeHost host="host1" url="192.168.22.28:3306" user="root"
				   password="centos">
		<readHost host="host2" url="192.168.22.38:3306" user="root" password="centos" />
		</writeHost>
		<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>
	<!--
		<dataHost name="sequoiadb1" maxCon="1000" minCon="1" balance="0" dbType="sequoiadb" dbDriver="jdbc">
		<heartbeat> 		</heartbeat>
		 <writeHost host="hostM1" url="sequoiadb://1426587161.dbaas.sequoialab.net:11920/SAMPLE" user="jifeng" 	password="jifeng"></writeHost>
		 </dataHost>

	  <dataHost name="oracle1" maxCon="1000" minCon="1" balance="0" writeType="0" 	dbType="oracle" dbDriver="jdbc"> <heartbeat>select 1 from dual</heartbeat>
		<connectionInitSql>alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss'</connectionInitSql>
		<writeHost host="hostM1" url="jdbc:oracle:thin:@127.0.0.1:1521:nange" user="base" 	password="123456" > </writeHost> </dataHost>

		<dataHost name="jdbchost" maxCon="1000" 	minCon="1" balance="0" writeType="0" dbType="mongodb" dbDriver="jdbc">
		<heartbeat>select 	user()</heartbeat>
		<writeHost host="hostM" url="mongodb://192.168.0.99/test" user="admin" password="123456" ></writeHost> </dataHost>

		<dataHost name="sparksql" maxCon="1000" minCon="1" balance="0" dbType="spark" dbDriver="jdbc">
		<heartbeat> </heartbeat>
		 <writeHost host="hostM1" url="jdbc:hive2://feng01:10000" user="jifeng" 	password="jifeng"></writeHost> </dataHost> -->

	<!-- <dataHost name="jdbchost" maxCon="1000" minCon="10" balance="0" dbType="mysql"
		dbDriver="jdbc"> <heartbeat>select user()</heartbeat> <writeHost host="hostM1"
		url="jdbc:mysql://localhost:3306" user="root" password="123456"> </writeHost>
		</dataHost> -->
</mycat:schema>

启动mycat服务器。如果需要加到开机自动启动可以加到rc.local中

mycat start

查看mycat/logs/wrapper.log文件中启动提示，必须看到成功的提示表示服务器启动正常。

远程连接mycat服务器

[root@localhost ~]# mysql -uroot -h192.168.22.48 -pcentos
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.7.6-release-20210730131311 MyCat Server (OpenCloudDB)

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.003 sec)

MySQL [(none)]> use TESTDB;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MySQL [TESTDB]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.002 sec)

确认是否读写分离，此处的从节点的server_id

确认写入是否是在主节点上执行

执行前的表数据内容 查询当前系统时间 在从服务器上看通用日志（PS:需要开启general_log）

执行后的数据内容 可以看出已经成功调用本地服务器变量，并将25号学生的年龄修改为28

三、总结

通过以上实践，可以实现简单的读写分离的需求。当然mycat还支持众多的分库分表等特性，这些需要时间继续研究。