首先,准备四台主机,搭建mycat分片服务器,通过某种特定条件,将存放在一个数据库(主机)中的数据,分散存放到多个数据库(主机)中,已达到分散单台设备负载的效果。其中192.168.4.21作为mycat服务器,192.168.4.22和192.168.4.23作为数据库服务器,192.168.4.24作为客户端。
192.168.4.21 mycat服务器
192.168.4.22 数据库服务器
192.168.4.23 数据库服务器
192.168.4.24 客户端


数据分片拓扑图

步骤:
公共配置:
1)关闭防火墙和selinux
2)将ip22、ip23搭建成为数据库服务器,下载mycat 软件到ip21主机上
搭建mysql数据库链接:https://blog.csdn.net/wgw_dream/article/details/83626627
3)ip22和ip23上面创建db1和db2库
1、创建库,在ip22数据库创建db1库,在ip23数据库创建db2库
1.1.
[root@c1 ~]# mysql -u root -p123456
mysql> create database db1; //c1上面创建db1库
Query OK, 1 row affected (0.00 sec)
[root@c2 ~]# mysql -u root -p123456
mysql> create database db2; //c2上面创建db2库
Query OK, 1 row affected (0.00 sec)
2、授权用户(ip22、ip23都要授权一个)
2.1.
mysql> grant all on *.* to admin@"%" identified by "123456";
Query OK, 0 rows affected, 1 warning (0.00 sec)
3、修改数据库的配置文件
注意:1代表不区分表名的大小写,0为区分大小写
3.1. ip22做
[root@c1 ~]# vim /etc/my.cnf
[mysqld]
lower_case_table_names=1 //表名忽略大小写
........................ //省略其它
[root@c1 ~]# systemctl restart mysqld //重启服务
3.2. ip23做
[root@c1 ~]# vim /etc/my.cnf
[mysqld]
lower_case_table_names=1 //表名忽略大小写
........................ //省略其它
[root@c1 ~]# systemctl restart mysqld //重启服务
4、在ip21上安装JDK
4.1.
[root@mycat ~]# rpm -qa | grep -i 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
copy-jdk-configs-2.2-3.el7.noarch
[root@mycat ~]# yum -y install java-1.8.0-openjdk
5、ip21安装mycat
5.1.
[root@mycat ~]# cd mysql/
[root@mycat mysql]# tar -xf Mycat-server-1.4-beta-20150604171601-linux.tar.gz //免安装,解压即可使用
[root@mycat mysql]# mv mycat/ /usr/local/
[root@mycat mysql]# ls /usr/local/mycat/
bin catlet conf lib logs version.txt
[root@mycat mysql]# cd /usr/local/mycat/
[root@mycat mycat]# ./bin/mycat --help \\查看帮助,如何使用
Usage: ./bin/mycat { console | start | stop | restart | status | dump }
目录结构说明:
bin mycat命令,如 启动 停止 等
catlet 扩展功能
conf 配置文件
lib mycat使用的jar
log mycat启动日志和运行日志
wrapper.log mycat服务启动日志
mycat.log 记录SQL脚本执行后的报错内容
重要配置文件说明:
server.xml 设置连mycat的账号信息
schema.xml 配置mycat的真实库表
rule.xml 定义mycat分片规则
配置标签说明
… … 定义连mycat用户信息
… … 指定数据节点
… … 指定数据库地址及用户信息
6、修改配置文件
6.1.查看server.xml配置文件
[root@mycat mycat]# cd conf/
[root@mycat conf]# vim server.xml
</system>
<user name="test"> //连接mycat服务时使用的用户名 test
<property name="password">test</property>
//使用test用户连接mycat用户时使用的密码
<property name="schemas">TESTDB</property>
//连接上mycat服务后,可以看到的库名多个时,使用逗号分隔 (是逻辑上的库名,服务器上没有这个库名,随便取,但要记住)
</user>
<user name="user">
<property name="password">user</property>
<property name="schemas">TESTDB</property>
<property name="readOnly">true</property>
//定义只读权限,使用定义的user用户连接mycat服务后只有读记录的权限,不写这一行则是可读可写
</user>
6.2. 修改server.xml配置文件
[root@mycat conf]# vim schema.xml
<table name="travelrecord" dataNode="dn1,dn2" rule="auto-sharding-long" />
//travelrecord(逻辑上的,名字不能随便写,一般不动)表分片到数据节点dn1和dn2,dn1和dn2随便取的名字
<table name="company" primaryKey="ID" type="global" dataNode="dn1,dn2" />
<table name="hotnews" primaryKey="ID" dataNode="dn1,dn2" rule="mod-long" />
<dataNode name="dn1" dataHost="c1" database="db1" />
//数据节点对应的服务器 name="dn1"名称要与上面的对应 dataHost="c1"写本机主机名,database="db1"存在的数据库名,定义分片使
用的库, 所在的物理主机,真正存储数据的db1库在物理主机mysql55上
<dataNode name="dn2" dataHost="c2" database="db2" />
//定义分片使用的库,所在的物理主机,真正存储数据的db1库在物理主机mysql55上
指定c1名称主机对应的ip地址
<dataHost name="c1" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="c1" url="192.168.4.55:3306" user="admin"
password="123456">
//访问数据库时,mycat服务连接数据库服务器时使用的用户名和密码
<!-- can have multi read hosts -->
</writeHost>
</dataHost>
指定c2名称主机对应的ip地址
<dataHost name="c2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<!-- can have multi write hosts -->
<writeHost host="c2" url="192.168.4.54:3306" user="admin"
password="123456">
//访问数据库时,mycat服务连接数据库服务器时使用的用户名和密码
<!-- can have multi read hosts -->
</writeHost>
</dataHost>
7、添加PATH路径
7.1.
[root@mycat conf]# export PATH=/usr/local/mycat/bin:$PATH //临时性,下次开机无效
[root@mycat conf]# echo "PATH=/usr/local/mycat/bin:$PATH" >> /etc/profile //追加重定向添加PATH路径
[root@mycat conf]# source /etc/profile //使修改操作立即生效
[root@mycat conf]# echo $PATH
/usr/local/mycat/bin:/usr/local/mycat/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
[root@mycat conf]# which mycat
/usr/local/mycat/bin/mycat
8、启动服务并查看端口
8.1.
[root@mycat conf]# mycat start
Starting Mycat-server...
[root@mycat conf]# ss -antup | grep :8066 //ss与netstat效果相同
tcp6 0 0 :::8066 :::* LISTEN 6421/java
[root@mycat conf]# ps -C java
PID TTY TIME CMD
6421 ? 00:00:04 java
9、客户端用之前授权的用户登陆
9.1客户端用admin用户登陆ip22数据库
[root@client ~]# mysql -h192.168.4.22 -uadmin -p123456
mysql> show processlist; //显示哪些线程正在运行
+----+-------+--------------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+--------------------+------+---------+------+----------+------------------+
| 3 | admin | 192.168.4.21:34580 | db2 | Sleep | 80 | | NULL |
| 4 | admin | 192.168.4.21:34570 | db2 | Sleep | 100 | | NULL |
| 5 | admin | 192.168.4.21:34572 | db2 | Sleep | 40 | | NULL |
| 6 | admin | 192.168.4.21:34562 | db2 | Sleep | 30 | | NULL |
| 7 | admin | 192.168.4.21:34564 | db2 | Sleep | 90 | | NULL |
| 8 | admin | 192.168.4.21:34566 | db2 | Sleep | 60 | | NULL |
| 9 | admin | 192.168.4.21:34574 | db2 | Sleep | 70 | | NULL |
| 10 | admin | 192.168.4.21:34576 | db2 | Sleep | 10 | | NULL |
| 11 | admin | 192.168.4.21:34578 | db2 | Sleep | 20 | | NULL |
| 12 | admin | 192.168.4.21:34568 | db2 | Sleep | 50 | | NULL |
| 14 | admin | 192.168.4..21:32796 | NULL | Query | 0 | starting | show processlist |
+----+-------+--------------------+------+---------+------+----------+------------------+
11 rows in set (0.00 sec)
9.2客户端用admin用户登陆ip23数据库
[root@client ~]# mysql -h192.168.4.23 -uadmin -p123456
mysql> show processlist; //显示哪些线程正在运行
+----+-------+--------------------+------+---------+------+----------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------+--------------------+------+---------+------+----------+------------------+
| 3 | root | localhost | NULL | Sleep | 2352 | | NULL |
| 4 | admin | 192.168.4.21:45148 | db1 | Sleep | 2 | | NULL |
| 5 | admin | 192.168.4.21:45150 | db1 | Sleep | 62 | | NULL |
| 6 | admin | 192.168.4.21:45160 | db1 | Sleep | 12 | | NULL |
| 7 | admin | 192.168.4.21:45162 | db1 | Sleep | 92 | | NULL |
| 8 | admin | 192.168.4.21:45152 | db1 | Sleep | 32 | | NULL |
| 9 | admin | 192.168.4.21:45154 | db1 | Sleep | 42 | | NULL |
| 10 | admin | 192.168.4.21:45156 | db1 | Sleep | 22 | | NULL |
| 11 | admin | 192.168.4.21:45158 | db1 | Sleep | 82 | | NULL |
| 12 | admin | 192.168.4.21:45164 | db1 | Sleep | 52 | | NULL |
| 13 | admin | 192.168.4.21:45166 | db1 | Sleep | 72 | | NULL |
| 14 | admin | 192.168.4..21:32796 | NULL | Query | 0 | starting | show processlist |
+----+-------+--------------------+------+---------+------+----------+------------------+
12 rows in set (0.00 sec)
[root@mycat conf]# ls /usr/local/mycat/logs/
mycat.log mycat.pid wrapper.log //wrapper.log为错误日志
[root@mycat conf]# ldconfig -v //更新加载的模块
10、客户端访问
[root@clent ~]# mysql -h192.168.4.21 -P8066 -utest -ptest
mysql> show databases; //查看所有库
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB; //进入名为“TESTDB”的库
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>
mysql> show tables; //查看当前库的所有表
+------------------+
| Tables in TESTDB |
+------------------+
| company |
| customer |
| customer_addr |
| employee |
| goods |
| hotnews |
| orders |
| order_items |
| travelrecord |
+------------------+
9 rows in set (0.00 sec)
11、客户端测试
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.03 sec)
mysql> insert into employee(id,name,sharding_id) values(1,"lucy",10010);
Query OK, 1 row affected (0.02 sec)
mysql> select * from employee;
+----+------+-------------+
| id | name | sharding_id |
+----+------+-------------+
| 1 | bob | 10000 |
| 1 | lucy | 10010 |
+----+------+-------------+
12、查看结果
12.1. c1上查看结果
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db1 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 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 |
+---------------+
| employee |
+---------------+
1 row in set (0.00 sec)
mysql> select * from employee; //查看结果
+----+------+-------------+
| id | name | sharding_id |
+----+------+-------------+
| 1 | bob | 10000 |
+----+------+-------------+
1 row in set (0.00 sec)
12.2. c2上查看结果
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| db2 |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows 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 |
+---------------+
| 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)