mycat实验之主从配置读写分离和分表
创新互联是一家以网络技术公司，为中小企业提供网站维护、成都网站制作、成都网站设计、网站备案、服务器租用、空间域名、软件开发、微信小程序等企业互联网相关业务，是一家有着丰富的互联网运营推广经验的科技公司，有着多年的网站建站经验，致力于帮助中小企业在互联网让打出自已的品牌和口碑，让企业在互联网上打开一个面向全国乃至全球的业务窗口：建站电话联系：18982081108
1.实验环境：
vmware虚机3个 （虚机太少了，电脑有点吃力，3个虚机只能达到基本的测试）
系统centos7 （实验是关闭防火墙和selinux做的）
MySQL版本5.7
mycat版本1.6
虚机名字和ip：
mysql1 192.168.211.138
mysql2 192.168.211.139
mysql3 192.168.211.142
mycat安装在mysql1(192.168.211.138)
这台主机须能够解析mysql2,mysql3的地址。
可通过/etc/hosts 手动添加解析
2.实验目的
实现mycat读写分离和分表的功能。
3.实验思路
在mycat创建一个逻辑库：hello。
创建四个表格：
t1 这个表格用来分片，用枚举的规则分片。
t2 这个表格做全局表。
t3 这个表格做普通表，定义它放到其中一个datanode,放到mysql1
t4 这个表格做普通表，定义它放到其中一个datanode,放到mysql3
虚机的分配：
mysql1 和mysql2做主从配置，其中mysql1为主，mysql2为从,其中mysql1用来写，mysql2用来读。
mysql3 就是个单独的datanode
实际我们只有两个datanode，mysql1,mysql3。
4.实验步骤
分别在虚机安装mysql5.7，步骤略。
设置mysql用户，步骤略。请注意，必须设置一个用户允许内网地址连接或者干脆是允许任何地方连接。比如设置'root'@'%'。
安装mycat。步骤略。
配置mysql1和mysql2主从，步骤略。
以上都是些常用配置，懒得特意记录了。
以上的环境都配置好了，就可以开始我们实验核心步骤mycat的配置了
现在开始整个过程的配置。
1.在mysql1（192.168.211.138）里创建一个db1.
mysql> create database db1;
Query OK, 1 row affected (0.01 sec)
检查mysql2（192.168.211.139）里是不是同步了？
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| logs |
| mysql |
| performance_schema |
| sys |
| wordpress |
+--------------------+
7 rows in set (0.00 sec)
同步了。
2.在mysql3（192.168.211.142）里创建db2。
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db2 |
| logs |
| mysql |
| performance_schema |
| sys |
| wordpress |
+--------------------+
7 rows in set (0.01 sec)
3.在mysql1里配置mycat，我的mycat安装在mysql1。
我的配置文件路径：
[root@mysql1 conf]# pwd
/usr/local/mycat/conf
主要配置文件有：
[root@mysql1 conf]# ls
autopartition-long.txt log4j2.xml schema.xml.bk server.xml.bk
auto-sharding-long.txt migrateTables.properties schema.xml.bk2 sharding-by-enum.txt
auto-sharding-rang-mod.txt myid.properties sequence_conf.properties wrapper.conf
cacheservice.properties partition-hash-int.txt sequence_db_conf.properties zkconf
dnindex.properties partition-range-mod.txt sequence_distributed_conf.properties zkdownload
ehcache.xml rule.xml sequence_time_conf.properties
index_to_charset.properties schema.xml server.xml
[root@mysql1 conf]#
需要用到的是:schema.xml ##这个文件配置分表读写分离策略
rule.xml ##这是分表规则的定义
server.xml ##登录mycat的账户密码和防火墙的设置
4.首先配置：schema.xml
备份下原配置文件：
[root@mysql1 conf]# cp schema.xml schema.xml.bk3
清空配置文件：
[root@mysql1 conf]# echo " " >schema.xml
重新配置schema.xml，如下是我的配置文件整个内容:
select user()
select user()
这些字段的解释，可以查看我前面分片规则里面的解析。
说明下datahost balance字段的意义
balance属性
负载均衡类型，目前的取值有3种：
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不负担读压
writeType属性
负载均衡类型，目前的取值有3种：
1. writeType="0", 所有写操作发送到配置的第一个writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，
切换记录在配置文件中:dnindex.properties .
2. writeType="1"，所有写操作都随机的发送到配置的writeHost，1.5以后废弃不推荐。
需要说明的是：
select user()
读写分离这里有两种写法：
除了上面的还可以写下面这样：
select user()
区别是上面的那种写法，如果主服务器挂了，从服务器不能读了。
下面的这种写法，主服务器挂了，从服务器依然可以正常读取。
这里说一下，后面会做测试。
5.看看rule.xml里的配置
看分表t1的设置：
分表规则是 sharding-by-intfile
看看这个的规则设置
city
hash-int
为什么columns是city呢？
这个city是我设定的，计划创建表格t1(id,name,bu,city)有这四个列。我准备用city来做分片的列。
继续往下看
partition-hash-int.txt
1
0
这是用的hash-int函数的设置了。其中mpfile是指要读取的配置文件。这是什么意思呢？
看看partition-hash-int.txt的内容
[root@mysql1 conf]# cat partition-hash-int.txt
#10000=0
#10010=1
bj=0
gz=0
sz=1
[root@mysql1 conf]#
我计划的表格t1 city列有三个值，bj gz sz ，这个设置的意思是：bj gz 的数据存储到datanode1也就是mysql1，sz的数据存储
到datanode2也就是mysql3。
1
0
这两条参数也必须要有，type默认的值是0，而0的格式是interger，注意我们分片的列是city是字符。
6.配置server.xml，参考下官方教程，很简单。
7.到这里可以来启动mycat，登录进去创建表格，插入数据测试，是不是能够达到我们的预期目标？
[root@mysql1 conf]# mysql -uroot -p123456 -P8066 -h 127.0.0.1
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 2
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2017, 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 |
+----------+
| hello |
+----------+
1 row in set (0.02 sec)
mysql>
我们设置的逻辑库hello
mysql> use hello;
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 hello |
+-----------------+
| t1 |
| t2 |
| t3 |
| t4 |
+-----------------+
4 rows in set (0.01 sec)
mysql>
mysql> desc t1;
ERROR 1146 (42S02): Table 'db1.t1' doesn't exist
mysql>
注意这里有个问题，这些t1-t4的表格，看似是有，其实是假的。感觉像是BUG。表格需要我们手动创建。
创建表格：
mysql> create table t1 (id int not null,name varchar(15) not null,bu varchar(10) not null,city varchar(5) not null);
Query OK, 0 rows affected (0.36 sec)
mysql> create table t2 (id int not null,name varchar(15) not null);
Query OK, 0 rows affected (0.10 sec)
mysql> create table t3 (id int not null,gongzi int not null);
Query OK, 0 rows affected (0.07 sec)
mysql> create table t4 (id int not null,shui int not null);
Query OK, 0 rows affected (0.07 sec)
看看创建的表格都放在哪?
mysql> explain create table t1 (id int not null,name varchar(15) not null,bu varchar(10) not null,city varchar(5) not null);
+-----------+--------------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------------------------------------------------------+
| dn1 | create table t1 (id int not null,name varchar(15) not null,bu varchar(10) not null,city varchar(5) not null) |
| dn2 | create table t1 (id int not null,name varchar(15) not null,bu varchar(10) not null,city varchar(5) not null) |
+-----------+--------------------------------------------------------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
这是分片表，dn1,dn2都有
mysql> explain create table t2 (id int not null,name varchar(15) not null);
+-----------+-------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------------------+
| dn1 | create table t2 (id int not null,name varchar(15) not null) |
| dn2 | create table t2 (id int not null,name varchar(15) not null) |
+-----------+-------------------------------------------------------------+
2 rows in set (0.00 sec)
mysql>
这是全局表，dn1,dn2都有，全局表的意思是，每个dn节点都有的表而且数据保持一致。
mysql> explain create table t3 (id int not null,gongzi int not null);
+-----------+-------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------------+
| dn1 | create table t3 (id int not null,gongzi int not null) |
+-----------+-------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
普通表，预设就是放在dn1，正常
mysql> explain create table t4 (id int not null,shui int not null);
+-----------+------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+------------------------------------------------------+
| dn2 | create table t4 (id int not null,shui int not null) |
+-----------+------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
普通表，预设就是放在dn2，正常
看看从库的情况：
mysql> show tables;
+---------------+
| Tables_in_db1 |
+---------------+
| t1 |
| t2 |
| t3 |
+---------------+
3 rows in set (0.00 sec)
mysql>
除了t4没有，其他都有，正常。
插入数据：
插入t1
mysql> insert into t1(id,name,bu,city)values(1,'am1','sy','bj');
Query OK, 1 row affected (0.38 sec)
mysql> insert into t1(id,name,bu,city)values(2,'am2','cs','gz');
Query OK, 1 row affected (0.03 sec)
mysql> insert into t1(id,name,bu,city)values(3,'am3','net','sz');
Query OK, 1 row affected (0.08 sec)
mysql>
插入t2
mysql> insert into t2(id,name)value(4,'am4'),(5,'am5');
Query OK, 2 rows affected (0.11 sec)
Records: 2 Duplicates: 0 Warnings: 0
插入t3
mysql> insert into t3(id,gongzi)values(6,1000),(7,1200);
Query OK, 2 rows affected (0.02 sec)
Records: 2 Duplicates: 0 Warnings: 0
插入t4
mysql> insert into t4(id,shui)values(8,10),(9,8);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
基础架构和环境以及达成，现在来测试：
首先，t1根据预设的目的，bj和gz的数据存放在dn1(mysql1),sz的数据存放在dn2(mysql3)
mysql> select * from t1 where city='bj' or city='gz';
+----+------+----+------+
| id | name | bu | city |
+----+------+----+------+
| 1 | am1 | sy | bj |
| 2 | am2 | cs | gz |
+----+------+----+------+
2 rows in set (0.10 sec)
mysql> explain select * from t1 where city='bj' or city='gz';
+-----------+--------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------------------------------+
| dn1 | SELECT * FROM t1 WHERE city = 'bj' OR city = 'gz' LIMIT 100 |
+-----------+--------------------------------------------------------------+
1 row in set (0.00 sec)
mysql>
mysql> explain select * from t1 where city='sz';
+-----------+----------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------+
| dn2 | SELECT * FROM t1 WHERE city = 'sz' LIMIT 100 |
+-----------+----------------------------------------------+
1 row in set (0.02 sec)
mysql>
分表正常
测试读写分离
开启debug日志
##默认level是info 改成debug后，重启mycat服务。
检索数据：
检索t3的数据，t3只有mysql1有。
mysql> select * from t3;
+----+--------+
| id | gongzi |
+----+--------+
| 6 | 1000 |
| 7 | 1200 |
+----+--------+
2 rows in set (0.02 sec)
以下是mycat.log日志，可以看到是从mysql2(192.168.211.139)读取的数据
2017-12-15 01:47:23.280 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:341))
- release connection MySQLConnection [id=22, lastTime=1513320443251, user=root, schema=db1, old shema=db1, borrowed=true,
fromSlaveDB=true, threadId=33, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM t3
LIMIT 100}, respHandler=SingleNodeHandler [node=dn1{SELECT *
FROM t3
LIMIT 100}, packetId=6], host=192.168.211.139, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]
2017-12-15 01:54:37.777 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.server.NonBlockingSession.releaseConnection(NonBlockingSession.java:341))
- release connection MySQLConnection [id=26, lastTime=1513320877751, user=root, schema=db1, old shema=db1, borrowed=true, fromSlaveDB=true,
threadId=31, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM t3
LIMIT 100}, respHandler=SingleNodeHandler [node=dn1{SELECT *
FROM t3
LIMIT 100}, packetId=6], host=192.168.211.139, port=3306, statusSync=null, writeQueue=0, modifiedSQLExecuted=false]mysql> select * from t1;
+----+------+-----+------+
| id | name | bu | city |
+----+------+-----+------+
| 3 | am3 | net | sz |
| 1 | am1 | sy | bj |
| 2 | am2 | cs | gz |
+----+------+-----+------+
3 rows in set (0.04 sec)
日志
2017-12-15 02:02:35.818 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(MySQLConnection.java:448))
- con need syn ,total syn cmd 1 commands SET names utf8;schema change:false con:MySQLConnection [id=29, lastTime=1513321355817, user=root,
schema=db1, old shema=db1, borrowed=true, fromSlaveDB=true, threadId=35, charset=utf8, txIsolation=3, autocommit=true, attachment=dn1{SELECT *
FROM t1
LIMIT 100}, respHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@11eaaf37, host=192.168.211.139, port=3306, statusSync=null, writeQueue=0,
modifiedSQLExecuted=false]
2017-12-15 02:02:35.819 DEBUG [$_NIOREACTOR-0-RW] (io.mycat.backend.mysql.nio.MySQLConnection.synAndDoExecute(MySQLConnection.java:448))
- con need syn ,total syn cmd 1 commands SET names utf8;schema change:false con:MySQLConnection [id=20, lastTime=1513321355819, user=root,
schema=db2, old shema=db2, borrowed=true, fromSlaveDB=false, threadId=65, charset=utf8, txIsolation=3, autocommit=true, attachment=dn2{SELECT *
FROM t1
LIMIT 100}, respHandler=io.mycat.backend.mysql.nio.handler.MultiNodeQueryHandler@11eaaf37, host=192.168.211.142, port=3306, statusSync=null, writeQueue=0,
modifiedSQLExecuted=false]多执行几次检索存在dn1的数据，查看日志，可以确认，数据都是从mysql2(192.168.211.139)读取。实现了读写分离。
8.读写两种不同写法的测试
前面有提到读写分离有两种写法，
select user()
读写分离这里有两种写法：
除了上面的还可以写下面这样：
select user()
区别是上面的那种写法，如果主服务器挂了，从服务器不能读了。
下面的这种写法，主服务器挂了，从服务器依然可以正常读取。
现在服务器的写法是第一种，主服务器挂了，从服务器不能读了。执行看看
[root@mysql1 ~]# systemctl stop mysqld.service
[root@mysql1 ~]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Fri 2017-12-15 02:16:43 EST; 35s ago
Docs: man:mysqld(8)
[root@mysql1 ~]# mysql -uroot -p123456 -P8066 -h227.0.0.1
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 2
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2017, 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 t1;
ERROR 3000 (HY000): No MyCAT Database selected
mysql> select * from t1;
ERROR 3000 (HY000): No MyCAT Database selected
确实无法读取
测试第二种
修改schema.xml配置文件
重新启动mysql和mycat 保证环境正常
正常使用环境，我们现在关闭mysql1就是主节点
[root@mysql1 conf]# systemctl stop mysqld.service
[root@mysql1 conf]# systemctl status mysqld.service
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: inactive (dead) since Fri 2017-12-15 02:23:55 EST; 23s ago
Docs: man:mysqld(8)
检索数据看看
[root@mysql1 ~]# mysql -uroot -p123456 -P8066 -h227.0.0.1
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, 2017, 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 hello;
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> select * from t1;
+----+------+-----+------+
| id | name | bu | city |
+----+------+-----+------+
| 3 | am3 | net | sz |
| 1 | am1 | sy | bj |
| 2 | am2 | cs | gz |
+----+------+-----+------+
3 rows in set (0.46 sec)
果然如此，孰优孰劣无须说明了吧。
后记：通过环境的搭建和简单的测试，达到了设计的预期目标。
网页题目：mycat基础实验之主从配置读写分离和分表
当前地址：
http://chengdu.cdxwcx.cn/article/jgjego.html
