Mycat基本搭建
1.Java环境检查与安装(略)
【检查】
[root@mysqldb tmp]# java -verson
-bash: java: command not found
【直接解压安装】
[root@mysqldb tmp]#tar xvf jdk-8u101-linux-x64.tar.gz -C /usr/local
[root@mysqldb tmp]# /usr/local/jdk1.8.0_101/bin/java -version
java version "1.8.0_101"
Java(TM) SE Runtime Environment (build 1.8.0_101-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.101-b13, mixed mode)
【加入/etc/profile环境变量,也可以配置单独用户下】
[root@mysqldb tmp]#echo "export JAVA_HOME=/usr/local/jdk1.8.0_101">>/etc/profile
[root@mysqldb tmp]#echo "export PATH=\$JAVA_HOME/bin:$PATH">>/etc/profile
[root@mysqldb tmp]#source /etc/profile
[root@mysqldb tmp]#java -version
java version "1.8.0_101"
Java(TM) SE Runtime Environment (build 1.8.0_101-b13)
Java HotSpot(TM) 64-Bit Server VM (build 25.101-b13, mixed mode)
[root@mysqldb tmp]#
2. 编译安装MySQL 5.6(略)
#安装的MySQL实例
192.168.2.130 3306
3. 安装MYCAT
【MYCAT下载】
Mycat 1.5地址： https://github.com/MyCATApache/Mycat-download/tree/master/1.5-RELEASE
Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz
【安装MYCAT】
# 解压
[root@localhost u01]# tar -zxvf Mycat-server-1.5.1-RELEASE-20161130213509-linux.tar.gz
# copy
[root@localhost u01]# mv mycat/ /usr/local/
# 加入系统路径 /etc/profile
export MYCAT_HOME=/usr/local/mycat
export PATH=$JAVA_HOME/bin:$MYCAT_HOME/bin:/usr/local/sbin:/usr/local/bin:/sbin:/bin:/usr/sbin:/usr/bin:/root/bin
# 验证
[root@localhost u01]# mycat --version
Usage: /usr/local/mycat/bin/mycat { console | start | stop | restart | status | dump }
4.MYCAT配置分片
4.1 MySQL中新建三个数据库 db1 db2 db3
mysql> CREATE DATABASE db1;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE DATABASE db2;
Query OK, 1 row affected (0.01 sec)
mysql> CREATE DATABASE db3;
Query OK, 1 row affected (0.02 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| db2 |
| db3 |
| jfedu |
| mysql |
| performance_schema |
| replTestDB |
| sakila |
| test |
| testdb |
| xtrabackup |
| zabbix |
+--------------------+
13 rows in set (0.00 sec)
4.2 MyCat配置文件介绍
conf/rule.xml 定义分片规则
conf/schema.xml 定义逻辑库、表以及分片节点等内容
conf/server.xml 定义用户授权及服务器参数相关配置
4.3配置schema
[root@localhost local]# cd /usr/local/mycat/conf
[root@localhost conf]# vim schema.xml
<?xml version="1.0"?> #第一行必须是XML文件的第一个元素且前面不能空格。 否则报错"[xX][mM][lL]" 的处理指令目标
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://org.opencloudb/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
#TESTDB 是MyCat默认的一个测试逻辑数据库，需要在此节点下定义逻辑表，但在这里只是指定表的名称，并不对表进行详细的定义。
#下面这条语句就是指逻辑表tb_user_info将在dn1,dn2,dn3上创建 使用的分片规则是 auto-sharding-long
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />
</schema>
<dataNode name="dn1" dataHost="192.168.2.130" database="db1" />
<dataNode name="dn2" dataHost="192.168.2.130" database="db2" />
<dataNode name="dn3" dataHost="192.168.2.130" database="db3" />
<dataHost name="192.168.2.130" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.2.130:3306" user="root"
password="root123">
</writeHost>
</dataHost>
</mycat:schema>
此时在schema.xml中配置好的表名，实际上只是一个逻辑的表，这个表在物理数据库中并不存在，需要在MyCat通过Create Table 来创建这个表，执行Create语句以后，MyCat会在真实MySql配置的数据库中创建表。
4.4 auto-sharding-long分片规则的实现原理
[root@localhost local]# cd /usr/local/mycat/conf
[root@localhost local]# cat rule.xml
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
<function name="rang-long" class="org.opencloudb.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
可见这个TableRule是通过id 来进行分片的，分片的算法是rang-long，算法中使用了autopartition-long.txt
[root@localhost local]# cat autopartition-long.txt
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2
K表示1000条记录，M表示10000条记录,上面的三个配置就是0500万的记录会存在数据库db1的表中，500万1000万会存在db2的表中，1000万~1500万会存在db3的表中。
4.5 启动mycat
#启动 Mycat
[root@localhost conf]# mycat start
Starting Mycat-server...
#查看mycat状态
[root@localhost conf]# mycat status
Mycat-server is running (60202).
#查看MyCat进程
[root@localhost conf]# ps -ef |grep mycat
root 60202 1 0 18:14 ? 00:00:00 /usr/local/mycat/bin/./wrapper-linux-x86-64 /usr/local/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/usr/local/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycat
root 61948 91636 0 18:23 pts/4 00:00:00 grep --color=auto mycat
# 查看端口监听情况
ss -tanl
#通过mycat访问数据库
mysql@localhost ~]$ mysql -h 192.168.2.130 -P8066 -u test -ptest
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.5.8-mycat-1.5.1-RELEASE-20161130213509 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>
5.mycat基本操作
#mycat查询逻辑数据库
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
#逻辑表 此时表物理上还未创建
mysql> use TESTDB;
Database changed
mysql> show tables;
Current database: TESTDB
+------------------+
| Tables in TESTDB |
+------------------+
| travelrecord |
+------------------+
1 row in set (0.03 sec)
#创建表
mysql> create table travelrecord (id bigint not null primary key,user_id varchar(100),traveldate DATE, fee decimal,days int);
Query OK, 0 rows affected (0.25 sec)
mysql> show create table travelrecord;
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| travelrecord | CREATE TABLE `travelrecord` (
`id` bigint(20) NOT NULL,
`user_id` varchar(100) DEFAULT NULL,
`traveldate` date DEFAULT NULL,
`fee` decimal(10,0) DEFAULT NULL,
`days` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+--------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
#查看物理表
mysql> use db1;
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_db1 |
+---------------+
| travelrecord |
+---------------+
1 row in set (0.00 sec)
mysql> use db2;
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_db2 |
+---------------+
| travelrecord |
+---------------+
1 row in set (0.00 sec)
mysql> use db3
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_db3 |
+---------------+
| travelrecord |
+---------------+
1 row in set (0.00 sec)
#可以在MySql中的三个数据库中看到，表确实已经创建了。
#验证路由
mysql> explain select * from travelrecord;
+-----------+--------------------------------------+
| DATA_NODE | SQL |
+-----------+--------------------------------------+
| dn1 | SELECT * FROM travelrecord LIMIT 100 |
| dn2 | SELECT * FROM travelrecord LIMIT 100 |
| dn3 | SELECT * FROM travelrecord LIMIT 100 |
+-----------+--------------------------------------+
3 rows in set (0.13 sec)
#下面我们分别向表中插入三条数据分别ID是 1000，5100000，11000000，看是否正常分配到三个表中：
#能过在MyCat中执行explain SQL语句，可以查看插入的记录将会被分配到哪个表中：
insert into travelrecord(id,user_id,traveldate,fee,days) values(1000,'chinesern','2017-10-11',100,10);
insert into travelrecord(id,user_id,traveldate,fee,days) values(5100000,'chinesern','2017-10-11',100,10);
insert into travelrecord(id,user_id,traveldate,fee,days) values(11000000,'chinesern','2017-10-11',100,10);
#验证路由
mysql> explain insert into travelrecord(id,user_id,traveldate,fee,days) values(1000,'chinesern','2017-10-11',100,10);
+-----------+-------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-------------------------------------------------------------------------------------------------------+
| dn1 | insert into travelrecord(id,user_id,traveldate,fee,days) values(1000,'chinesern','2017-10-11',100,10) |
+-----------+-------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain insert into travelrecord(id,user_id,traveldate,fee,days) values(5100000,'chinesern','2017-10-11',100,10);
+-----------+----------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+----------------------------------------------------------------------------------------------------------+
| dn2 | insert into travelrecord(id,user_id,traveldate,fee,days) values(5100000,'chinesern','2017-10-11',100,10) |
+-----------+----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> explain insert into travelrecord(id,user_id,traveldate,fee,days) values(11000000,'chinesern','2017-10-11',100,10);
+-----------+-----------------------------------------------------------------------------------------------------------+
| DATA_NODE | SQL |
+-----------+-----------------------------------------------------------------------------------------------------------+
| dn3 | insert into travelrecord(id,user_id,traveldate,fee,days) values(11000000,'chinesern','2017-10-11',100,10) |
+-----------+-----------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
#物理表中验证
mysql> use db1;
Database changed
mysql> select * from travelrecord;
+------+-----------+------------+------+------+
| id | user_id | traveldate | fee | days |
+------+-----------+------------+------+------+
| 1000 | chinesern | 2017-10-11 | 100 | 10 |
+------+-----------+------------+------+------+
1 row in set (0.01 sec)
mysql> use db2;
Database changed
mysql> select * from travelrecord;
+---------+-----------+------------+------+------+
| id | user_id | traveldate | fee | days |
+---------+-----------+------------+------+------+
| 5100000 | chinesern | 2017-10-11 | 100 | 10 |
+---------+-----------+------------+------+------+
1 row in set (0.00 sec)
mysql> use db3;
Database changed
mysql> select * from travelrecord;
+----------+-----------+------------+------+------+
| id | user_id | traveldate | fee | days |
+----------+-----------+------------+------+------+
| 11000000 | chinesern | 2017-10-11 | 100 | 10 |
+----------+-----------+------------+------+------+
1 row in set (0.00 sec)
#数据已经按照规则分布到不同的分片了！
