MyCat 读写分离「建议收藏」

MyCat 读写分离

大家好,又见面了,我是全栈君。

2013年阿里的Cobar在社区使用过程中发现存在一些比较严重的问题,及其使用限制,经过Mycat发起人第一次改良,第一代改良版——Mycat诞生。 Mycat开源以后,一些Cobar的用户参与了Mycat的开发,最终Mycat发展成为一个由众多软件公司的实力派架构师和资深开发人员维护的社区型开源软件。

环境说明Mycat依赖java7 及其以上

主机名 IP 安装的软件软件 说明
zqdd 192.168.0.171 MySQL: 5.1.71,MyCAT1.6 MySQL写服务,mycat服务
agent 192.168.0.181 MySQL: 5.1.71 MySQL读服务
agent2 192.168.0.85 MySQL: 5.1.71 MySQL读服务

1、安装mysql,并配置主从

192.168.0.171 配置

yum install mysql-server

[root@zqdd:/usr/local/mycat/conf]#cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
lower_case_table_names = 1
server-id=1
log-bin=mysql-bin

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


root@agent2:/var/lib/mysql#/etc/init.d/mysqld  start
Starting mysqld:                                           [  OK  ]

/usr/bin/mysqladmin -u root password 'redhat'

#主从
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.0.%' IDENTIFIED BY 'repl';
flush privileges;


#
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000010 |      106 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
#记住位置和文件名

192.168.0.181 配置

yum install mysql-server

root@agent:/var/lib/mysql#cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
lower_case_table_names = 1
server-id=2
log-bin=mysql-bin
relay-log=mysql-relay-bin
log-slave-updates=1
read-only=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

root@agent:/var/lib/mysql#/etc/init.d/mysqld start
Starting mysqld:                                           [  OK  ]
/usr/bin/mysqladmin -u root password 'redhat'

change master to master_host='192.168.0.171',master_user='repl',master_password='repl',master_log_file='mysql-bin.000010',master_log_pos=106;

192.168.0.85 配置

yum install mysql-server

root@agent2:/var/lib/mysql# cat /etc/my.cnf 
[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
lower_case_table_names = 1
server-id=3
log-bin=mysql-bin
relay-log=mysql-relay-bin
log-slave-updates=1
read-only=1


[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

root@agent2:/var/lib/mysql#/etc/init.d/mysqld  start
Starting mysqld:                                           [  OK  ]

/usr/bin/mysqladmin -u root password 'redhat'

change master to master_host='192.168.0.171',master_user='repl',master_password='repl',master_log_file='mysql-bin.000010',master_log_pos=106;

2、启动主从复制

192.168.0.181 192.168.0.85 上操作

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


mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.0.171
                  Master_User: musingtec
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000012
          Read_Master_Log_Pos: 453
               Relay_Log_File: mysql-relay-bin.000011
                Relay_Log_Pos: 598
        Relay_Master_Log_File: mysql-bin.000012
             Slave_IO_Running: Yes   #
            Slave_SQL_Running: Yes   #
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 453
              Relay_Log_Space: 898
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_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: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
1 row in set (0.00 sec)

3、测试mysql主从是否正常

192.168.0.171    主库创建
[root@zqdd:/usr/local/mycat/conf]#mysql -uroot -predhat1 -e "create database testdb"
[root@zqdd:/usr/local/mycat/conf]#mysql -uroot -predhat1 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| musingtec          |
| mysql              |
| taokeeper          |
| test               |
| testdb             |
+--------------------+

从库查看
root@agent:/root#mysql -uroot -predhat1 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| musingtec          |
| musingtecb         |
| mysql              |
| test               |
| testdb             |
+--------------------+

从库查看
root@agent2:/root#mysql -uroot -predhat1 -e "show databases;"
+--------------------+
| Database           |
+--------------------+
| information_schema |
| db1                |
| musingtec          |
| mysql              |
| test               |
| testdb             |
+--------------------+

4、安装MyCat并配置

这里不做详细解释,请查看官方文档http://www.mycat.io/

官网下载http://dl.mycat.io/1.6-RELEASE/

wget http://dl.mycat.io/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz

tar xvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz -C /usr/local/


[root@zqdd:/root]#cd /usr/local/mycat/
[root@zqdd:/usr/local/mycat]#ls
bin  catlet  conf  lib  logs  version.txt

配置schema.xml

[root@zqdd:/usr/local/mycat/conf]#cat 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) -->
	<!--		<table name="travelrecord" dataNode="dn1" rule="auto-sharding-long" /> -->
	<!--	<table name="travelrecord" dataNode="dn1"/>
		    <table name="test1" dataNode="dn1"/>
-->
		<!-- global table is auto cloned to all defined data nodes ,so can join
			with any table whose sharding node is in the same data node -->
	<!--	<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2,dn3" />
		<table name="goods" primaryKey="ID" type="global" dataNode="dn1,dn2" /> -->
		<!-- random sharding using mod sharind rule -->
	<!--	<table name="hotnews" primaryKey="ID" autoIncrement="true" dataNode="dn1,dn2,dn3"
			   rule="mod-long" /> -->
		<!-- <table name="dual" primaryKey="ID" dataNode="dnx,dnoracle2" type="global"
			needAddLimit="false"/> <table name="worker" primaryKey="ID" dataNode="jdbc_dn1,jdbc_dn2,jdbc_dn3"
			rule="mod-long" /> -->
	<!--	<table name="employee" primaryKey="ID" dataNode="dn1,dn2"
			   rule="sharding-by-intfile" />
		<table name="customer" primaryKey="ID" dataNode="dn1,dn2"
			   rule="sharding-by-intfile">
			<childTable name="orders" primaryKey="ID" joinKey="customer_id"
						parentKey="id">
				<childTable name="order_items" joinKey="order_id"
							parentKey="id" />
			</childTable>
			<childTable name="customer_addr" primaryKey="ID" joinKey="customer_id"
						parentKey="id" />
		</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="musingtec" />
<!--	<dataNode name="dn2" dataHost="localhost1" database="musingtec" />
	<dataNode name="dn3" dataHost="localhost1" database="musingtec" />  -->
	<!--<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="192.168.0.171:3306" user="root"
				   password="redhat1">
			<!-- can have multi read hosts -->
			<readHost host="hostS2" url="192.168.0.181:3306" user="root" password="redhat1" />
			<readHost host="hostS3" url="192.168.0.85:3306" user="root" password="redhat1" />
		</writeHost>
            <!--  <writeHost host="hostS1" url="localhost:3316" user="root" 
				   password="123456" /> -->
		<!-- <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>

这里面,有两个参数需要注意,balance和 switchType。

其中,balance指的负载均衡类型,目前的取值有4种:

1. balance=”0″, 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上。

2. balance=”1″,全部的readHost与stand by writeHost参与select语句的负载均衡,简单的说,当双主双从模式(M1->S1,M2->S2,并且M1与 M2互为主备),正常情况下,M2,S1,S2都参与select语句的负载均衡。

3. balance=”2″,所有读操作都随机的在writeHost、readhost上分发。

4. balance=”3″,所有读请求随机的分发到wiriterHost对应的readhost执行,writerHost不负担读压力

switchType指的是切换的模式,目前的取值也有4种:

1. switchType=’-1′ 表示不自动切换

2. switchType=’1′ 默认值,表示自动切换

3. switchType=’2′ 基于MySQL主从同步的状态决定是否切换,心跳语句为 show slave status

4. switchType=’3’基于MySQL galary cluster的切换机制(适合集群)(1.4.1),心跳语句为 show status like ‘wsrep%’。

 

因此,该配置文件中的balance=”1″意味着作为stand by writeHost的hostS1和hostS2将参与select语句的负载均衡,这就实现了主从的读写分离,switchType=’-1’意味着当主挂掉的时候,不进行自动切换,即hostS1和hostS2并不会被提升为主,仍只提供读的功能。这就避免了将数据写进slave的可能性,毕竟,单纯的MySQL主从集群并不允许将数据读进slave中,除非配置的是双master。

配置server.xml

	
	<user name="root">
		<property name="password">redhat1</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">redhat1</property>
		<property name="schemas">TESTDB</property>
		<property name="readOnly">true</property>
	</user>

5、启动

添加/etc/profile
export MYCAT_HOME=/usr/local/mycat
export PATH=$PATH:$MYCAT_HOME/bin
source /etc/profile 生效

[root@zqdd:/usr/local/mycat/conf]#mycat  start
Starting Mycat-server...


查看端口
[root@zqdd:/usr/local/mycat/conf]# netstat -antlp |grep :9066   #管理端口
tcp        0      0 :::9066                     :::*                        LISTEN      5488/java           
[root@zqdd:/usr/local/mycat/conf]# netstat -antlp |grep :8066   #数据端口
tcp        0      0 :::8066                     :::*                        LISTEN      5488/java           
[root@zqdd:/usr/local/mycat/conf]#

6、测试8066 数据端口读写分离

[root@zqdd:/usr/local/mycat/conf]#mysql -uroot -h127.0.0.1 -predhat1 -P8066
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, 2013, Oracle and/or its affiliates. All rights reserved.

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.

mysql> 
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.01 sec)

mysql> use TESTDB;
Database changed
mysql> show tables;
Empty set (0.00 sec)

mysql> create table travelrecord1 (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table travelrecord2 (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into travelrecord1(id,user_id,traveldate,fee,days)  values(1,@@hostname,20170626,100,10);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into travelrecord1(id,user_id,traveldate,fee,days)  values(2,@@hostname,20170626,100,10);
Query OK, 1 row affected, 1 warning (0.01 sec)

mysql> insert into travelrecord1(id,user_id,traveldate,fee,days)  values(3,@@hostname,20170626,100,10);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into travelrecord2(id,user_id,traveldate,fee,days)  values(1,@@hostname,20170626,100,10);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into travelrecord2(id,user_id,traveldate,fee,days)  values(2,@@hostname,20170626,100,10);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> insert into travelrecord2(id,user_id,traveldate,fee,days)  values(3,@@hostname,20170626,100,10);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show tables;
+---------------------+
| Tables_in_musingtec |
+---------------------+
| travelrecord1       |
| travelrecord2       |
+---------------------+

mysql> select * from travelrecord1;
+----+---------+------------+------+------+
| id | user_id | traveldate | fee  | days |
+----+---------+------------+------+------+
|  1 | agent2  | 2017-06-26 |  100 |   10 |
|  2 | agent2  | 2017-06-26 |  100 |   10 |
|  3 | agent2  | 2017-06-26 |  100 |   10 |
+----+---------+------------+------+------+
3 rows in set (0.00 sec)

mysql> select * from travelrecord1;
+----+---------+------------+------+------+
| id | user_id | traveldate | fee  | days |
+----+---------+------------+------+------+
|  1 | agent   | 2017-06-26 |  100 |   10 |
|  2 | agent   | 2017-06-26 |  100 |   10 |
|  3 | agent   | 2017-06-26 |  100 |   10 |
+----+---------+------------+------+------+
3 rows in set (0.00 sec)

在这里,用了一个取巧的方法,即对user_id插入了当前实例的主机名,这样可直观的观察读写是否分离以及MyCAT的分片功能。能这样做的原因在于我当前的MySQL版本-5.1.71默认是基于statement的复制,如果是基于row的复制,则这个方法将不可取。

因为读服务器2个 所以select  查询每一次主机名不一样,可以看出读是在2台读主机上轮询的

看写服务器写进去的主机名

[root@zqdd:/usr/local/mycat/conf]#mysql -uroot -predhat1
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 60
Server version: 5.1.71-log Source distribution

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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.

mysql> use musingtec;
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> show tables;
+---------------------+
| Tables_in_musingtec |
+---------------------+
| travelrecord1       |
| travelrecord2       |
+---------------------+
2 rows in set (0.00 sec)

mysql> select * from travelrecord1;   #达到预期目的 读写已经分开!
+----+---------+------------+------+------+
| id | user_id | traveldate | fee  | days |
+----+---------+------------+------+------+
|  1 | zqdd    | 2017-06-26 |  100 |   10 |
|  2 | zqdd    | 2017-06-26 |  100 |   10 |
|  3 | zqdd    | 2017-06-26 |  100 |   10 |
+----+---------+------------+------+------+
3 rows in set (0.00 sec)

7、测试9066 管理端口

[root@zqdd:/usr/local/mycat/conf]#mysql -uroot -h127.0.0.1 -predhat1 -P9066
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (monitor)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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.

mysql> show @@datanode;
+------+----------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST              | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+----------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1  | localhost1/musingtec |     0 | mysql |      0 |   10 | 1000 |      89 |          0 |        0 |       0 |            -1 |
+------+----------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
1 row in set (0.00 sec)

mysql> show @@datasource; #查看后端数据源
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| DATANODE | NAME   | TYPE  | HOST          | PORT | W/R  | ACTIVE | IDLE | SIZE | EXECUTE | READ_LOAD | WRITE_LOAD |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
| dn1      | hostM1 | mysql | 192.168.0.171 | 3306 | W    |      0 |   10 | 1000 |      56 |         0 |         10 |
| dn1      | hostS2 | mysql | 192.168.0.181 | 3306 | R    |      0 |    5 | 1000 |      78 |        37 |          0 |
| dn1      | hostS3 | mysql | 192.168.0.85  | 3306 | R    |      0 |    5 | 1000 |      66 |        25 |          0 |
+----------+--------+-------+---------------+------+------+--------+------+------+---------+-----------+------------+
3 rows in set (0.01 sec)


mysql> 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.01 sec)


8、测试主MySQL宕掉

[root@zqdd:/root]#/etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]


[root@zqdd:/usr/local/mycat/conf]#mysql -uroot -h127.0.0.1 -predhat1 -P8066 -DTESTDB
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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.

mysql> select * from travelrecord1;
ERROR 1184 (HY000): Connection refused

主MySQL停掉了从的也无法访问。噢  不可以

修改datahost部分为

	<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.0.171:3306" user="root"
			password="redhat1">
		</writeHost>
			<!-- can have multi read hosts -->
		<!--	<readHost host="hostS2" url="192.168.0.181:3306" user="root" password="redhat1" /> -->
			<writeHost host="hostS2" url="192.168.0.181:3306" user="root" password="redhat1" />
			<writeHost host="hostS3" url="192.168.0.85:3306" user="root" password="redhat1" />
            <!--  <writeHost host="hostS1" url="localhost:3316" user="root" 
				   password="123456" /> -->
		<!-- <writeHost host="hostM2" url="localhost:3316" user="root" password="123456"/> -->
	</dataHost>

测试

[root@zqdd:/usr/local/mycat/conf]#mysql -uroot -h127.0.0.1 -predhat1 -P8066 -DTESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)

Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.

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.

mysql> select * from travelrecord1;
+----+---------+------------+------+------+
| id | user_id | traveldate | fee  | days |
+----+---------+------------+------+------+
|  1 | agent   | 2017-06-26 |  100 |   10 |
|  2 | agent   | 2017-06-26 |  100 |   10 |
|  3 | agent   | 2017-06-26 |  100 |   10 |
+----+---------+------------+------+------+
3 rows in set (0.00 sec)

mysql> select * from travelrecord1;
+----+---------+------------+------+------+
| id | user_id | traveldate | fee  | days |
+----+---------+------------+------+------+
|  1 | agent   | 2017-06-26 |  100 |   10 |
|  2 | agent   | 2017-06-26 |  100 |   10 |
|  3 | agent   | 2017-06-26 |  100 |   10 |
+----+---------+------------+------+------+
3 rows in set (0.00 sec)

mysql> select * from travelrecord1;
+----+---------+------------+------+------+
| id | user_id | traveldate | fee  | days |
+----+---------+------------+------+------+
|  1 | agent2  | 2017-06-26 |  100 |   10 |
|  2 | agent2  | 2017-06-26 |  100 |   10 |
|  3 | agent2  | 2017-06-26 |  100 |   10 |
+----+---------+------------+------+------+
3 rows in set (0.00 sec)

mysql> select * from travelrecord1;
+----+---------+------------+------+------+
| id | user_id | traveldate | fee  | days |
+----+---------+------------+------+------+
|  1 | agent   | 2017-06-26 |  100 |   10 |
|  2 | agent   | 2017-06-26 |  100 |   10 |
|  3 | agent   | 2017-06-26 |  100 |   10 |
+----+---------+------------+------+------+
3 rows in set (0.00 sec)

mysql> select * from travelrecord1;
+----+---------+------------+------+------+
| id | user_id | traveldate | fee  | days |
+----+---------+------------+------+------+
|  1 | agent2  | 2017-06-26 |  100 |   10 |
|  2 | agent2  | 2017-06-26 |  100 |   10 |
|  3 | agent2  | 2017-06-26 |  100 |   10 |
+----+---------+------------+------+------+
3 rows in set (0.01 sec)

mysql> select * from travelrecord1;
+----+---------+------------+------+------+
| id | user_id | traveldate | fee  | days |
+----+---------+------------+------+------+
|  1 | agent   | 2017-06-26 |  100 |   10 |
|  2 | agent   | 2017-06-26 |  100 |   10 |
|  3 | agent   | 2017-06-26 |  100 |   10 |
+----+---------+------------+------+------+
3 rows in set (0.01 sec)
#停掉主MySQL
[root@zqdd:/root]#/etc/init.d/mysqld stop
Stopping mysqld:                                           [  OK  ]
#################
继续查看可以自动
mysql> select * from travelrecord1;
+----+---------+------------+------+------+
| id | user_id | traveldate | fee  | days |
+----+---------+------------+------+------+
|  1 | agent2  | 2017-06-26 |  100 |   10 |
|  2 | agent2  | 2017-06-26 |  100 |   10 |
|  3 | agent2  | 2017-06-26 |  100 |   10 |
+----+---------+------------+------+------+
3 rows in set (0.01 sec)

mysql> select * from travelrecord1; 
+----+---------+------------+------+------+
| id | user_id | traveldate | fee  | days |
+----+---------+------------+------+------+
|  1 | agent2  | 2017-06-26 |  100 |   10 |
|  2 | agent2  | 2017-06-26 |  100 |   10 |
|  3 | agent2  | 2017-06-26 |  100 |   10 |
+----+---------+------------+------+------+
3 rows in set (0.01 sec)

mysql> insert into travelrecord1(id,user_id,traveldate,fee,days)  values(4,@@hostname,20170626,100,10);
ERROR 1184 (HY000): Connection refused

结论:验证停掉MySQL主,slave可以继续提供服务,但不能插入数据

未完待续。。。。。

转载于:https://my.oschina.net/kcw/blog/1031125

版权声明:本文内容由互联网用户自发贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 举报,一经查实,本站将立刻删除。

发布者:全栈程序员-用户IM,转载请注明出处:https://javaforall.cn/108396.html原文链接:https://javaforall.cn

【正版授权,激活自己账号】: Jetbrains全家桶Ide使用,1年售后保障,每天仅需1毛

【官方授权 正版激活】: 官方授权 正版激活 支持Jetbrains家族下所有IDE 使用个人JB账号...

(0)


相关推荐

  • JS实现图片循环滚动

    JS实现图片循环滚动之前在前端的时候有遇到这样一个问题,实现JS图片的循环滚动,然后鼠标移入的时候停止滚动,鼠标移开继续滚动,这里无非就是设置了一个定时器,鼠标移上时清除定时器达到滚动停止的目的,鼠标移开时重设定时器,代码如下:<!DOCTYPE><html> <head> <metacharset=”UTF-8″> <title>JS实…

  • gtest的介绍和使用

    gtest的介绍和使用一、什仫是gtestgtest是一个跨平台的(Liunx、MacOSX、Windows、Cygwin、WindowsCEandSymbian)C++单元测试框架,由google公司发布。gtest是为在不同平台上为编写C++测试而生成的。它提供了丰富的断言、致命和非致命判断、参数化、”死亡测试”等等。了解了什仫是gtest之后下面让我们来学习gt…

  • php autoconf 配置,automake,autoconf使用详解

    php autoconf 配置,automake,autoconf使用详解作为Linux下的程序开发人员,大家一定都遇到过Makefile,用make命令来编译自己写的程序确实是很方便.一般情况下,大家都是手工写一个简单Makefile,如果要想写出一个符合自由软件惯例的Makefile就不那么容易了.在本文中,将给大家介绍如何使用autoconf和automake两个工具来帮助我们自动地生成符合自由软件惯例的Makefile,这样就可以象常见的GNU程序一样,只要…

  • php中接口、抽象类以及接口和抽象类区别详解

    php中接口、抽象类以及接口和抽象类区别详解php中接口、抽象类以及接口和抽象类区别详解

  • 一、Bitmap的recycle问题

    一、Bitmap的recycle问题

    2021年12月13日
  • Spring Cloud版本冲突(java.lang.NoClassDefFoundError:org/springframework/boot/Bootstrapper)

    Spring Cloud版本冲突(java.lang.NoClassDefFoundError:org/springframework/boot/Bootstrapper)springboot整合springcloud和springcloudalibaba版本导致的问题问题描述问题描述修改springboot版本后,启动springboot项目项目报错:java.lang.NoClassDefFoundError:org/springframework/boot/Bootstrapperjava.lang.NoClassDefFoundError:org/springframework/boot/Bootstrapper atjava.lang.ClassL

发表回复

您的电子邮箱地址不会被公开。

关注全栈程序员社区公众号