云--主从数据库读写分离（mycat）

一、安装与配置主从数据库
https://www.cnblogs.com/used-conduit-onion/p/18812575

操作机：mysql1

二、准备
1.安装java开发者环境
yum -y install java java-devel

2.下载
官网：https://www.mycat.org.cn/
https://github.com/MyCATApache/Mycat-Server/releases

三、安装与配置

3.解压与环境配置
tar -zxf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local
echo 'export MYCAT_HOME=/usr/local/mycat' >> /etc/profile
echo 'export PATH=$PATH:$MYCAT_HOME/bin:$MYCAT_HOME/sbin' >> /etc/profile

4.配置
4.1
mv $MYCAT_HOME/conf/schema.xml $MYCAT_HOME/conf/schema.xml.bak
touch $MYCAT_HOME/conf/schema.xml

<?xml version="1.0"?>
		<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
		<mycat:schema xmlns:mycat="http://io.mycat/">
		<schema name="逻辑数据库" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1"></schema> 
		<dataNode name="dn1" dataHost="localhost1" database="已存在的数据库名称" />  
		<dataHost name="localhost1" maxCon="1000" minCon="10" balance="3" dbType="mysql" dbDriver="native" writeType="0" switchType="1"  slaveThreshold="100">  
			<heartbeat>select user()</heartbeat>
			<writeHost host="hostM1" url="主数据库IP地址、域名或者主机名:3306" user="root" password="数据库密码"> 
				<readHost host="hostS1" url="从数据库IP地址、域名或者主机名:3306" user="root" password="数据库密码" />
			</writeHost>
		</dataHost>
		</mycat:schema>

4.2
vi $MYCAT_HOME/conf/server.xml

</mycat:server>
         <user name="root">
                <property name="password">数据库密码</property>
                <property name="schemas">逻辑数据库（必须与上面相同）</property>

                <!-- 表级 DML 权限设置 -->
                <!--
                <privileges check="false">
                        <schema name="TESTDB" dml="0110" >
                                <table name="tb01" dml="0000"></table>
                                <table name="tb02" dml="1111"></table>
                        </schema>
                </privileges>
                 -->
        </user>

删除的内容

        <user name="user">
                <property name="password">user</property>
                <property name="schemas">TESTDB</property>
                <property name="readOnly">true</property>
        </user>

5.启动
mycat start

6.查看
6.1 端口
yum -y install net-tools
netstat -ntpl

6.2 读写记录
mysql -h127.0.0.1 -P9066 -uroot -p000000 -e"
show @@datasource;
"

+----------+--------+-------+--------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST   | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+--------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | mysql1 | 3306 | W    |      0 |    9 | 1000 |     239 |         1 |         10 |
| dn1      | hostS1 | mysql | mysql2 | 3306 | R    |      0 |    9 | 1000 |     208 |         4 |          0 |
+----------+--------+-------+--------+------+------+--------+------+------+---------+-----------+------------+

mysql -h127.0.0.1 -P9066 -uroot -p000000 -e"
show @@heartbeat;
"

+--------+-------+--------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME   | TYPE  | HOST   | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+-------+--------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | mysql1 | 3306 |       1 |     0 | idle   |       0 | 0,0,0        | 2025-04-22 09:17:09 | false |
| hostS1 | mysql | mysql2 | 3306 |       1 |     0 | idle   |       0 | 1,1,1        | 2025-04-22 09:17:09 | false |
+--------+-------+--------+------+---------+-------+--------+---------+--------------+---------------------+-------+

7.操作
mysql -h127.0.0.1 -P8066 -uroot -p000000;

[root@mysql1 conf]# mysql -h127.0.0.1 -P8066 -uroot -p000000
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2017, 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 |
+----------+
| mycat    |
+----------+
1 row in set (0.00 sec)

MySQL [(none)]> use mycat;
Database changed
MySQL [mycat]> show tables;
Empty set (0.00 sec)

MySQL [mycat]> insert into mycat (name,age,gender,address,occupation)values("liu1",25,"gentleman","beijing","sofware engineer");
Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL [mycat]> select * from mycat;
+----+------+------+-----------+---------+-----------------+
| id | name | age  | gender    | address | occupation      |
+----+------+------+-----------+---------+-----------------+
|  1 | liu1 |   25 | gentleman | beijing | sofware enginee |
+----+------+------+-----------+---------+-----------------+
1 row in set (0.00 sec)

MySQL [mycat]>