MySQL主从复制（读写分离）
- 1、读写分离简介
- 2、读写分离优点
- 3、读写分离性能提高原因
- 4、Mycat实现MySQL主从复制读写分离
- 1）Mycat工作原理（中间件）
- 2）MyCAT的安装及部署
- ①部署jdk环境
- ②安装Mycat
- ③配置文件
- A. schema.xml配置文件
- a. schema标签
- b. table标签
- c. childTable标签（定义逻辑子表）
- d. dataNode标签
- e. dataHost标签
- f. heartbeat标签
- g. writeHost & readHost标签
- B. server.xml配置文件
- user标签
- 修改默认端口（服务端口 & 管理端口）
- 5、配置示例
- 1） 环境准备
- 2） 修改schema.xml配置文件
- ① 修改配置文件（第一处）
- ② 修改配置文件（第二处）
- ③ 修改配置文件（第三处）
- ④ 修改配置文件（第四处）
- 3） 创建管理用户
- 4）启动Mycat
- 5）客户端连接MySQL主库服务器测试
- ①使用“写”用户登录 & 测试
- ②使用“读”用户登录 & 测试
- ③使用“管理”端口登录 & 测试
1、读写分离简介
读写分离可以简单理解为就是让主库处理事务性查询（增删改），从库处理select查询（分担主库压力）。通过数据库主从复制将事务性查询导致的数据变更同步到从库，当然主库也可以进行select查询。最大的作用就是缓解服务器的压力。
2、读写分离优点
- 增加冗余；
- 缓解服务器的压力；增加机器的处理能力；
3、读写分离性能提高原因
- 物理服务器增加；负荷增加；
- 主库和从库只负责自己的写和读操作，极大的缓解了X锁和S锁；
- 从库可以配置为myisam引擎，提高读的性能及节省系统开销；
- 从库同步主库的数据和主库直接写还是有区别的，从库是通过主库发来的binlog来同步的，但是呢，区别在于主库向从库发送binlog是异步的，所以同步数据也是异步的。
- 读写分离适用于读的操作应用较多的场景，如果只有一台服务器，当select很多时，update和delete会被select的访问堵塞，这时就需要等待select结束，并发性能不高；当读和写比例相近时，可以设置成互为主从。
- 分摊读写，这个就是拿机器数量去换性能。
- 增加冗余，提高容错，当主故障时，可以迅速地把从切换为主来使用。
4、Mycat实现MySQL主从复制读写分离
1）Mycat工作原理（中间件）
2）MyCAT的安装及部署
①部署jdk环境
MyCAT使用Java开发，需要有JAVA运行环境，mycat依赖jdk1.7的环境[root@localhost ~]# yum localinstall jdk-8u144-linux-x64.rpm -y②安装Mycat
[root@localhost ~]# tar xf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/
[root@localhost ~]# ll -d /usr/local/mycat/
drwxr-xr-x 7 root root 85 May 11 23:04 /usr/local/mycat/
[root@localhost ~]# echo "export PATH=$PATH:/usr/local/mycat/bin" > /etc/profile.d/mycat.sh
[root@localhost ~]# source /etc/profile.d/mycat.sh
[root@localhost ~]# ll /usr/local/mycat/
total 12
drwxr-xr-x 2 root root 190 May 11 23:04 bin
drwxrwxrwx 2 root root 6 Mar 1 2016 catlet
drwxrwxrwx 4 root root 4096 May 13 23:34 conf
drwxr-xr-x 2 root root 4096 May 11 23:04 lib
drwxrwxrwx 3 root root 57 May 14 00:01 logs
-rwxrwxrwx 1 root root 217 Oct 28 2016 version.txt③配置文件
[root@localhost ~]# cd /usr/local/mycat/conf/
[root@localhost conf]# ll
total 104
-rwxrwxrwx 1 root root 88 Oct 28 2016 autopartition-long.txt
-rwxrwxrwx 1 root root 48 Oct 28 2016 auto-sharding-long.txt
-rwxrwxrwx 1 root root 62 Oct 28 2016 auto-sharding-rang-mod.txt
-rwxrwxrwx 1 root root 334 Oct 28 2016 cacheservice.properties
-rw-r--r-- 1 root root 51 May 13 23:34 dnindex.properties
-rwxrwxrwx 1 root root 439 Oct 28 2016 ehcache.xml
-rwxrwxrwx 1 root root 2132 Oct 28 2016 index_to_charset.properties
-rwxrwxrwx 1 root root 1246 Oct 10 2016 log4j2.xml
-rwxrwxrwx 1 root root 178 Oct 28 2016 migrateTables.properties
-rwxrwxrwx 1 root root 246 Oct 28 2016 myid.properties
-rwxrwxrwx 1 root root 15 Oct 28 2016 partition-hash-int.txt
-rwxrwxrwx 1 root root 102 Oct 28 2016 partition-range-mod.txt
-rwxrwxrwx 1 root root 4794 Oct 28 2016 rule.xml
-rwxrwxrwx 1 root root 848 May 13 23:30 schema.xml
-rwxrwxrwx 1 root root 413 Oct 28 2016 sequence_conf.properties
-rwxrwxrwx 1 root root 75 Oct 28 2016 sequence_db_conf.properties
-rwxrwxrwx 1 root root 27 Oct 28 2016 sequence_distributed_conf.properties
-rwxrwxrwx 1 root root 51 Oct 28 2016 sequence_time_conf.properties
-rwxrwxrwx 1 root root 3827 May 12 00:34 server.xml
-rwxrwxrwx 1 root root 16 Oct 28 2016 sharding-by-enum.txt
-rwxrwxrwx 1 root root 4182 Oct 28 2016 wrapper.conf
drwxrwxrwx 2 root root 4096 May 11 23:04 zkconf
drwxrwxrwx 2 root root 36 May 11 23:04 zkdownloadA. schema.xml配置文件
a. schema标签
默认: <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"></schema>
属性:
name: 指定MyCat实例中的逻辑库名字（可用于区分不同逻辑库）
checkSQLschema: 是否去掉表前面的数据库的名称"TESTDB"，默认为false（保留名称）
sqlMaxLimit: 为每条查询语句末尾自动添加limit "定义值"（相当于"selelct * from test_table limit 100;"）
dataNode: 指定这个逻辑表所属的"dataNode"（数据节点），需要和下方table标签的"dataNode"和下方dataNode标签的"name"一致b. table标签
属性:
name: 指定逻辑表的表名（可用于区分不同逻辑表）
dataNode: 指定这个逻辑表所属的"dataNode"（数据节点），需要和上方schema标签的"dataNode"和下方dataNode标签的"name"一致
rule: 指定逻辑表要使用的规则名字，规则在"rule.xml"配置文件中定义
ruleRequired: 指定逻辑表是否绑定分片规则，可选择true & false
primaryKey: 逻辑表所对应真实表的主键
type: 定义逻辑表的类型，存在“全局表”和”普通表”两种类型，"global"表示全局表，不定义表示为普通表
autoIncrement: 定义主键是否自增长，可选择true & falsec. childTable标签（定义逻辑子表）
（略）
【属性与table类似】d. dataNode标签
默认: <dataNode name="dn1" dataHost="localhost1" database="db1" />
属性:
name: 定义数据节点的名字【必须唯一】
dataHost: 定义该节点所属数据库实例（IP地址），需要和下方dataHost标签的"name"一致
database: 定义该节点所属数据库的具体库e. dataHost标签
默认:
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
</dataHost>
属性:
name: 定义该节点所属数据库实例（IP地址）【必须唯一】
maxCon: 最大并发连接数
minCon: 最小并发连接数
balance:
1、balance=0 不开启读写分离机制，所有读操作都发送到当前可用的writehostle .
2、balance=1 全部的readhost与stand by writeHost 参与select语句的负载均衡。简单的说，双主双从模式(M1->S1,M2->S2，并且M1和M2互为主备)，正常情况下，M1，S1，S2都参与select语句的复杂均衡。
3、balance=2 所有读操作都随机的在readhost和writehost上分发
writeType:
负载均衡类型，目前的取值有3种：
1、writeType="0", 所有写操作发送到配置的第一个writeHost。
2、writeType="1"，所有写操作都随机的发送到配置的writeHost（写的主机不止一台）。
3、writeType="2"，不执行写操作。
dbType: 指定后端连接的数据库类型，可选值"mongodb" & "oracle" & "spark"等
dbDriver: 指定连接后端数据库使用的Driver（数据库驱动），可选值"native" & "JDBC"
switchType:
1、switchType=-1 表示不自动切换
2、switchType=1 默认值，自动切换
3、switchType=2 基于MySQL 主从同步的状态决定是否切换
4、switchType=3 基于MySQL galary cluster（MySQL的同步多主集群软件）的切换机制【适合集群】
tempReadHostAvailable: 暂时不太明白用法，可选值"0" & "1"f. heartbeat标签
默认: <heartbeat>select user()</heartbeat>
作用: 监测心跳g. writeHost & readHost标签
默认:
<writeHost host="hostM1" url="localhost:3306" user="root" password="123456">
<readHost host="hostS2" url="192.168.1.200:3306" user="root" password="xxx" />
</writeHost>
属性:
host: 定义实例名（标识不同实例）
url: 指定所连接实例IP
user: 指定所连接实例用户
password: 指定所连接实例用户的密码
weight: 指定权重（在readHost中指定）
usingDecrypt: 是否开启密码加密，可选值"0" & "1"（默认"0"，不开启）B. server.xml配置文件
user标签
<user name="root"> //映射另一个配置文件指定的写用户
<user name="user"> //映射另一个配置文件指定的读用户修改默认端口（服务端口 & 管理端口）
<property name="serverPort">8066</property> <property name="managerPort">9066</property>
//注：默认有注释，需要将注释去掉【直接修改即可】5、配置示例
1） 环境准备
三台主机：一应用服务器、一主、两从
应用服务器（Application）[ip为192.168.25.135]
主库（MySQL master）[ip为192.168.25.131]
从库（MySQL slave1）[ip为192.168.25.133]
从库（MySQL slave2）[ip为192.168.25.134]
2） 修改schema.xml配置文件
① 修改配置文件（第一处）
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
//这里的TESTDB就是我们所宣称的数据库名称，必须和server.xml中的用户指定的数据库名称一致。添加一个dataNode="dn1"，是指定了我们这个库只有在dn1上，没有分库。② 修改配置文件（第二处）
<dataNode name="dn1" dataHost="localhost1" database="db1" />
//这里只需要改database的名字，就是你真是的数据库上的数据库名，可根据自己的数据库名称修改。③ 修改配置文件（第三处）
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">④ 修改配置文件（第四处）
<writeHost host="hostM1" url="192.168.95.120:3306" user="mycat_w" password="123456">
<!– can have multi read hosts –>
<readHost host="hostS2" url="192.168.95.140:3306" user="mycat_r" password="123456" />
<readHost host="hostS3" url="192.168.95.140:3307" user="mycat_r" password="123456" />
<!--<writeHost host="hostS1" url="localhost:3316" user="root"
password="123456" />-->配置好文件内容如下
<?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="test" />
<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="192.168.25.131:3306" user="mycat_w" password="123456">
<!-- can have multi read hosts -->
<readHost host="hostS1" url="192.168.25.133:3306" user="mycat_r" password="123456" />
<readHost host="hostS2" url="192.168.25.134:3306" user="mycat_r" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>3） 创建管理用户
主库上创建（主从同步）
db01 [(none)] > grant create,insert,delete,update,select on test.* to "mycat_w"@"192.168.95.%" identified by "123456";
db01 [(none)] > flush privileges;
db01 [(none)] > grant select on test.* to "mycat_r"@"192.168.95.%" identified by "123456";
db01 [(none)] > flush privileges;4）启动Mycat
[root@localhost ~]# mycat start
Starting Mycat-server...
[root@localhost ~]# mycat status
Mycat-server is running (10960).
[root@localhost ~]# netstat -lnupt | grep 066
tcp6 0 0 :::8066 :::* LISTEN 1735/java
tcp6 0 0 :::9066 :::* LISTEN 1735/java5）客户端连接MySQL主库服务器测试
[root@localhost ~]# cat /usr/local/mycat/conf/server.xml //根据server.xml所映射的账号和对应的密码进行登录测试
<user name="root"> <!-- 映射另一个配置文件指定的写用户 -->
<property name="password">123456</property>
<property name="schemas">TESTDB</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>①使用“写”用户登录 & 测试
[root@localhost ~]# mysql -uroot -p123456 -h192.168.25.135 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '192.168.25.135' (113)
[root@localhost ~]# systemctl stop firewalld
[root@localhost ~]# mysql -uroot -p123456 -h192.168.25.135 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL 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, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
db01 [(none)] >db01 [(none)] > show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
db01 [(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
db01 [TESTDB] > create table test(id int,name varchar(10),address varchar(20));
Query OK, 0 rows affected (0.01 sec)
db01 [TESTDB] > show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
db01 [TESTDB] > insert into test values(1,"xiaotian","shenzhen");
Query OK, 1 row affected (0.00 sec)
db01 [TESTDB] > select * from test;
+------+----------+----------+
| id | name | address |
+------+----------+----------+
| 1 | xiaotian | shenzhen |
+------+----------+----------+
1 row in set (0.02 sec)
#可以看到主库也是可以正常进行查看的②使用“读”用户登录 & 测试
[root@localhost ~]# mysql -uuser -puser -h192.168.25.135 -P8066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
db02 [(none)] >db02 [(none)] > show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
db02 [(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
db02 [TESTDB] > show tables;
+----------------+
| Tables_in_test |
+----------------+
| test |
+----------------+
1 row in set (0.00 sec)
db02 [TESTDB] > select * from test;
+------+----------+----------+
| id | name | address |
+------+----------+----------+
| 1 | xiaotian | shenzhen |
+------+----------+----------+
1 row in set (0.00 sec)
db02 [TESTDB] > insert into test values(1,"xiaoming","guangzhou");
ERROR 1495 (HY000): User readonly
#可以看出从库只能进行查看（没有写权限）③使用“管理”端口登录 & 测试
[root@localhost ~]# mysql -uroot -p123456 -h192.168.25.135 -P9066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (monitor)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
db03 [(none)] > \q
Bye
[root@localhost ~]# mysql -uuser -puser -h192.168.25.135 -P9066
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (monitor)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
db03 [(none)] >
//可以看到管理端口root用户和user用户都可以登录#可以执行如下管理命令
db03 [(none)] > show @@help;
+------------------------------------------+--------------------------------------------+
| STATEMENT | DESCRIPTION |
+------------------------------------------+--------------------------------------------+
| show @@time.current | Report current timestamp |
| show @@time.startup | Report startup timestamp |
| show @@version | Report Mycat Server version |
| show @@server | Report server status |
| show @@threadpool | Report threadPool status |
| show @@database | Report databases |
| show @@datanode | Report dataNodes |
| show @@datanode where schema = ? | Report dataNodes |
| show @@datasource | Report dataSources |
| show @@datasource where dataNode = ? | Report dataSources |
| show @@datasource.synstatus | Report datasource data synchronous |
| show @@datasource.syndetail where name=? | Report datasource data synchronous detail |
| show @@datasource.cluster | Report datasource galary cluster variables |
| show @@processor | Report processor status |
| show @@command | Report commands status |
| show @@connection | Report connection status |
| show @@cache | Report system cache usage |
| show @@backend | Report backend connection status |
| show @@session | Report front session details |
| show @@connection.sql | Report connection sql |
| show @@sql.execute | Report execute status |
| show @@sql.detail where id = ? | Report execute detail status |
| show @@sql | Report SQL list |
| show @@sql.high | Report Hight Frequency SQL |
| show @@sql.slow | Report slow SQL |
| show @@sql.resultset | Report BIG RESULTSET SQL |
| show @@sql.sum | Report User RW Stat |
| show @@sql.sum.user | Report User RW Stat |
| show @@sql.sum.table | Report Table RW Stat |
| show @@parser | Report parser status |
| show @@router | Report router status |
| show @@heartbeat | Report heartbeat status |
| show @@heartbeat.detail where name=? | Report heartbeat current detail |
| show @@slow where schema = ? | Report schema slow sql |
| show @@slow where datanode = ? | Report datanode slow sql |
| show @@sysparam | Report system param |
| show @@syslog limit=? | Report system mycat.log |
| show @@white | show mycat white host |
| show @@white.set=?,? | set mycat white host,[ip,user] |
| show @@directmemory=1 or 2 | show mycat direct memory usage |
| switch @@datasource name:index | Switch dataSource |
| kill @@connection id1,id2,... | Kill the specified connections |
| stop @@heartbeat name:time | Pause dataNode heartbeat |
| reload @@config | Reload basic config from file |
| reload @@config_all | Reload all config from file |
| reload @@route | Reload route config from file |
| reload @@user | Reload user config from file |
| reload @@sqlslow= | Set Slow SQL Time(ms) |
| reload @@user_stat | Reset show @@sql @@sql.sum @@sql.slow |
| rollback @@config | Rollback all config from memory |
| rollback @@route | Rollback route config from memory |
| rollback @@user | Rollback user config from memory |
| reload @@sqlstat=open | Open real-time sql stat analyzer |
| reload @@sqlstat=close | Close real-time sql stat analyzer |
| offline | Change MyCat status to OFF |
| online | Change MyCat status to ON |
| clear @@slow where schema = ? | Clear slow sql by schema |
| clear @@slow where datanode = ? | Clear slow sql by datanode |
+------------------------------------------+--------------------------------------------+
58 rows in set (0.00 sec)
1）查看所有的命令，如下：
db03 [(none)] > show @@help;
2）显示mycat数据库的列表，对应的在scehma.xml配置的逻辑库
db03 [(none)] > show @@databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
3）显示mycat数据节点的列表，对应的是scehma.xml配置文件的dataNode节点
db03 [(none)] > show @@datanode;
+------+-----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST | INDEX | TYPE | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+-----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1 | localhost1/test | 0 | mysql | 0 | 10 | 1000 | 164 | 0 | 0 | 0 | -1 |
+------+-----------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
1 row in set (0.01 sec)
#注：其中，NAME表示datanode的名称；dataHost 对应的是dataHost属性的值，数据主机的名称，ACTIVE表示活跃的连接数，IDIE表示闲置的连接数，SIZE对应的是总连接的数量。
db03 [(none)] > show @@heartbeat;
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME | TYPE | HOST | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME | STOP |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM1 | mysql | 192.168.25.131 | 3306 | 1 | 0 | idle | 0 | 0,0,0 | 2022-05-15 17:59:05 | false |
| hostS1 | mysql | 192.168.25.133 | 3306 | 1 | 0 | idle | 0 | 0,0,0 | 2022-05-15 17:59:05 | false |
| hostS2 | mysql | 192.168.25.134 | 3306 | 1 | 0 | idle | 0 | 0,0,0 | 2022-05-15 17:59:05 | false |
+--------+-------+----------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
3 rows in set (0.01 sec)
#注：RS_CODE状态为1（正常状态）
4、获取当前mycat的版本
db03 [(none)] > show @@version;
+-----------------------------------------+
| VERSION |
+-----------------------------------------+
| 5.6.29-mycat-1.6-RELEASE-20161028204710 |
+-----------------------------------------+
1 row in set (0.00 sec)
5、显示mycat前端连接状态
db03 [(none)] > show @@connection;
+------------+------+----------------+------+------------+------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| PROCESSOR | ID | HOST | PORT | LOCAL_PORT | USER | SCHEMA | CHARSET | NET_IN | NET_OUT | ALIVE_TIME(S) | RECV_BUFFER | SEND_QUEUE | txlevel | autocommit |
+------------+------+----------------+------+------------+------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
| Processor0 | 4 | 192.168.25.133 | 8066 | 46684 | user | TESTDB | utf8:33 | 289 | 894 | 771 | 4096 | 0 | 3 | true |
| Processor1 | 7 | 192.168.25.134 | 9066 | 46484 | user | NULL | utf8:33 | 237 | 4553 | 250 | 4096 | 0 | | |
| Processor3 | 5 | 192.168.25.131 | 8066 | 33652 | root | TESTDB | utf8:33 | 306 | 728 | 491 | 4096 | 0 | 3 | true |
+------------+------+----------------+------+------------+------+--------+---------+--------+---------+---------------+-------------+------------+---------+------------+
3 rows in set (0.00 sec)
6、显示mycat后端连接状态
db03 [(none)] > show @@backend;
+------------+------+---------+----------------+------+--------+--------+---------+------+--------+----------+------------+--------+---------+---------+------------+
| processor | id | mysqlId | host | port | l_port | net_in | net_out | life | closed | borrowed | SEND_QUEUE | schema | charset | txlevel | autocommit |
+------------+------+---------+----------------+------+--------+--------+---------+------+--------+----------+------------+--------+---------+---------+------------+
| Processor0 | 16 | 10 | 192.168.25.134 | 3306 | 46190 | 2275 | 536 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor0 | 19 | 13 | 192.168.25.133 | 3306 | 52476 | 1632 | 379 | 1214 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor0 | 4 | 16 | 192.168.25.131 | 3306 | 35258 | 1405 | 358 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor0 | 23 | 15 | 192.168.25.133 | 3306 | 52484 | 1077 | 286 | 914 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor0 | 8 | 21 | 192.168.25.131 | 3306 | 35272 | 1416 | 425 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor0 | 12 | 8 | 192.168.25.133 | 3306 | 52460 | 2225 | 538 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor1 | 1 | 18 | 192.168.25.131 | 3306 | 35266 | 1334 | 360 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor1 | 17 | 11 | 192.168.25.134 | 3306 | 46194 | 2306 | 534 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor1 | 20 | 14 | 192.168.25.133 | 3306 | 52478 | 1592 | 381 | 1214 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor1 | 5 | 14 | 192.168.25.131 | 3306 | 35260 | 1416 | 379 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor1 | 24 | 15 | 192.168.25.134 | 3306 | 46206 | 1077 | 286 | 914 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor1 | 9 | 22 | 192.168.25.131 | 3306 | 35276 | 1312 | 379 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor1 | 25 | 16 | 192.168.25.133 | 3306 | 52490 | 749 | 214 | 614 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor1 | 13 | 10 | 192.168.25.133 | 3306 | 52466 | 2143 | 520 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor2 | 2 | 20 | 192.168.25.131 | 3306 | 35270 | 1405 | 358 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor2 | 18 | 11 | 192.168.25.133 | 3306 | 52472 | 2225 | 538 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor2 | 21 | 14 | 192.168.25.134 | 3306 | 46200 | 1527 | 374 | 1214 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor2 | 6 | 19 | 192.168.25.131 | 3306 | 35268 | 1947 | 373 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor2 | 10 | 15 | 192.168.25.131 | 3306 | 35262 | 1300 | 397 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor2 | 26 | 16 | 192.168.25.134 | 3306 | 46208 | 667 | 196 | 614 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor2 | 27 | 17 | 192.168.25.134 | 3306 | 46212 | 93 | 70 | 14 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor2 | 14 | 9 | 192.168.25.134 | 3306 | 46188 | 2266 | 536 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor3 | 3 | 23 | 192.168.25.131 | 3306 | 35274 | 1334 | 370 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor3 | 22 | 13 | 192.168.25.134 | 3306 | 46202 | 1487 | 376 | 1214 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor3 | 7 | 17 | 192.168.25.131 | 3306 | 35264 | 1296 | 404 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor3 | 11 | 8 | 192.168.25.134 | 3306 | 46184 | 2225 | 538 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
| Processor3 | 15 | 9 | 192.168.25.133 | 3306 | 52462 | 2183 | 518 | 1514 | false | false | 0 | test | utf8:33 | 3 | true |
+------------+------+---------+----------------+------+--------+--------+---------+------+--------+----------+------------+--------+---------+---------+------------+
27 rows in set (0.00 sec)
7、显示数据源
db03 [(none)] > show @@datasource;
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME | TYPE | HOST | PORT | W/R | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
| dn1 | hostM1 | mysql | 192.168.25.131 | 3306 | W | 0 | 10 | 1000 | 174 | 0 | 11 |
| dn1 | hostS1 | mysql | 192.168.25.133 | 3306 | R | 0 | 8 | 1000 | 164 | 4 | 0 |
| dn1 | hostS2 | mysql | 192.168.25.134 | 3306 | R | 0 | 9 | 1000 | 166 | 5 | 0 |
+----------+--------+-------+----------------+------+------+--------+------+------+---------+-----------+------------+
3 rows in set (0.00 sec)
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
