数据分片存储，mycat服务器

关闭防火墙和selinux，配置yum源
配置21 、22 数据库（这里以21为例）
[root@host21 ~]# tar -xf mysql-5.7.17.tar
[root@host21 ~]# yum -y install mysql-community-*.rpm
[root@host21 ~]# systemctl restart mysqld
[root@host21 ~]# grep password /var/log/mysqld.log
2019-05-27T14:45:14.647358Z 1 [Note] A temporary password is generated for root@localhost: BVwKi/g&-93z #root初始密码

[root@host21 ~]# mysql -uroot -p"BVwKi/g&-93z"
mysql> set global validate_password_policy=0; #改密码策略
Query OK, 0 rows affected (0.00 sec)

mysql> set global validate_password_length=6;
Query OK, 0 rows affected (0.00 sec)

mysql> alter user root@localhost identified by “123456”;
Query OK, 0 rows affected (0.01 sec)
mysql> grant all on . to ooo@"%"identified by “123456”; #在21上面授权一个用户，用户名对应mycai服务器配置文件里要求一样
mysql> create database db1; #创建一个库，库名对应mycai服务器里配置文件里的名字一样
[root@host21 ~]# vim /etc/my.cnf #进数据库配置文件永久修改密码策略
[mysqld]
validate_password_policy=0
validate_password_length=6
配置mycai分片服务器

[root@host24 ~]# which java
/usr/bin/java
[root@host24 ~]# java -version
openjdk version “1.8.0_131”
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed mode)
[root@host24 ~]# rpm -qa | grep -i openjdk #在24主机上面安装JDK，安装自带的即可
java-1.8.0-openjdk-1.8.0.131-11.b12.el7.x86_64
java-1.8.0-openjdk-headless-1.8.0.131-11.b12.el7.x86_64

[root@host24 ~]# tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz #免安装，解压即可使用
[root@host24 ~]# mv mycat /usr/local/
[root@host24 ~]# ls /usr/local/mycat/ #查看解压目录里的目录
bin catlet conf lib logs version.txt
目录结构说明：
bin mycat命令，如 启动 停止 等
catlet 扩展功能
conf 配置文件
lib mycat使用的jar
log mycat启动日志和运行日志
wrapper.log mycat服务启动日志
mycat.log 记录SQL脚本执行后的报错内容
重要配置文件说明：
server.xml 设置连mycat的账号信息
schema.xml 配置mycat的真实库表
rule.xml 定义mycat分片规则
配置标签说明

<user>.. ..</user> 定义连mycat用户信息
<datanode>.. ..</datanode> 指定数据节点
<datahost>.. ..</datahost> 指定数据库地址及用户信息
1
2
3
查看server.xml配置文件

<user name="root"> #客户端连接mycat服务时使用的用户名 root
<property name="password">123456</property> #使用root用户连接mycat用户时使用的密码
<property name="schemas">TESTDB</property> #连接上mycat服务后，可以看到的库名多个时，使用逗号分隔 （是逻辑上的库名,服务器上没有这个库名，随便取，但要记住）

<!-- 表级 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用户连接mycat服务后只有读记录的权限,不写这一行则是可读可写

</user>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
修改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">
<!-- auto sharding by id (long) -->
<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" />

<!-- 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="employee" primaryKey="ID" dataNode="dn1,dn2,dn3"
rule="sharding-by-intfile" />
<table name="customer" primaryKey="ID" dataNode="dn1,dn2,dn3"
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="db1" />
<dataNode name="dn2" dataHost="localhost2" database="db2" />
<dataNode name="dn3" dataHost="localhost3" database="db3" />

<dataHost name="localhost1" 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.4.21:3306" user="ooo"
password="123456">
</writeHost>
</dataHost>
<dataHost name="localhost2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="192.168.4.22:3306" user="ooo"
password="123456">
</writeHost>
</dataHost>
<dataHost name="localhost3" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM3" url="192.168.4.23:3306" user="ooo" #指定hostM3名称主机对应的ip地址
password="123456"> #访问数据库时，mycat服务连接数据库服务器时使用的用户名和密码
</writeHost>
</dataHost>
</mycat:schema>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
添加PATH路径
[root@host24 ~]# export PATH=/usr/local/mycat/bin:KaTeX parse error: Expected 'EOF', got '#' at position 21: …[root@host24 ~]#̲ echo "PATH=/us…PATH" >> /etc/profile
[root@host24 ~]# source /etc/profile
[root@host24 ~]# echo $PATH
/usr/local/mycat/bin:/usr/local/mycat/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@host24 ~]# which mycat
/usr/local/mycat/bin/mycat
启动服务并查看端口
[root@host24 ~]# mycat start #起服务
Starting Mycat-server…
[root@host24 ~]# netstat -antup | grep :8066 #查看端口
tcp6 0 0 :::8066 ::😗 LISTEN 18613/java
[root@host24 ~]# ps -C java #查看进程
PID TTY TIME CMD
18613 ? 00:00:01 java
用ooo用户登入
[root@host20 ~]# mysql -h192.168.4.21 -uooo -p123456
mysql> show processlist;
±—±-----±-------------------±-----±--------±-----±---------±-----------------+
| Id | User | Host | db | Command | Time | State | Info |
±—±-----±-------------------±-----±--------±-----±---------±-----------------+
| 17 | ooo | 192.168.4.24:50180 | db1 | Sleep | 46 | | NULL |
| 18 | ooo | 192.168.4.24:50182 | db1 | Sleep | 26 | | NULL |
| 19 | ooo | 192.168.4.24:50184 | db1 | Sleep | 56 | | NULL |
| 20 | ooo | 192.168.4.24:50186 | db1 | Sleep | 36 | | NULL |
| 21 | ooo | 192.168.4.24:50188 | db1 | Sleep | 6 | | NULL |
| 22 | ooo | 192.168.4.24:50190 | db1 | Sleep | 76 | | NULL |
| 23 | ooo | 192.168.4.24:50192 | db1 | Sleep | 86 | | NULL |
| 24 | ooo | 192.168.4.24:50194 | db1 | Sleep | 16 | | NULL |
| 25 | ooo | 192.168.4.24:50196 | db1 | Sleep | 66 | | NULL |
| 26 | ooo | 192.168.4.24:50198 | db1 | Sleep | 96 | | NULL |
| 28 | ooo | 192.168.4.20:53532 | NULL | Query | 0 | starting | show processlist |
±—±-----±-------------------±-----±--------±-----±---------±-----------------+
11 rows in set (0.00 sec)

[root@host24 ~]# ls /usr/local/mycat/logs/
2019-05 mycat.log mycat.pid wrapper.log #wrapper.log为错误日志
[root@host24 ~]# ldconfig -v #更新加载的模块
客户端访问

命令： mysql -hmycat主机的IP -P端口号 -u用户 -p密码 #端口是大写的P，密码是小写的p

[root@host20 ~]# mysql -h192.168.4.24 -P8066 -uroot -p123456
mysql> show databases;
±---------+
| DATABASE |
±---------+
| TESTDB |
±---------+
1 row in set (0.00 sec)
mysql> USE TESTDB;
mysql> show tables;
±-----------------+
| Tables in TESTDB |
±-----------------+
| company |
| customer |
| customer_addr |
| employee |
| goods |
| hotnews |
| orders |
| order_items |
| travelrecord |
±-----------------+
9 rows in set (0.00 sec)
客户端测试：
mysql> create table employee(id int not null primary key,name varchar(100),sharding_id int not null);
Query OK, 0 rows affected (0.10 sec)
mysql> insert into employee(id,name,sharding_id) values(1,“bob”,10000);
Query OK, 1 row affected (0.18 sec)

mysql> insert into employee(id,name,sharding_id) values(1,“lucy”,10010);
Query OK, 1 row affected (0.07 sec)

在其他主机上面查看结果
mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| db2 |
| mysql |
| performance_schema |
| sys |
±-------------------+
5 rows in set (0.01 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 |
±--------------+
| employee |
±--------------+
1 row in set (0.00 sec)

mysql> select * from employee; #查看结果
±—±-----±------------+
| id | name | sharding_id |
±—±-----±------------+
| 1 | lucy | 10010 |
±—±-----±------------+
1 row in set (0.00 sec)
---------------------