大家好，又见面了，我是全栈君。
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账号...