数据库中间件Mycat的安装使用
目录
1、介绍
2、环境架构准备
2.1 安装文件准备
2.2 创建相关目录并初始化数据
2.3 准备配置文件和启动脚本
2.4 启动多实例
2.5 搭建主从环境
2.6 检测主从状态
3、安装 MyCat 软件
4、配置文件介绍
4.1 schema.xml 配置文件
4.2 schema.xml 部分参数说明
5、垂直分片应用
5.1 配置文件准备
5.2 创建测试数据库
5.3 重启 mycat 并检查逻辑库
5.4 写入测试
6、水平分片应用
6.1 拆分概念
6.2 范围分片
6.3 取模分片
6.4 枚举分片
6.5 Mycat 全局表
6.6 E-R 分片
7、Mycat 日常管理
7.1 管理服务常用命令
7.2 修改逻辑库名
7.3 增加逻辑库
作者：AshesCat
1、介绍
MyCat是一个开源的分布式数据库中间件，是一个实现了MySQL协议的服务器
前端：用户可以把它看作是一个数据库代理，用MySQL客户端工具和命令行访问
后端：可以用MySQL原生协议与多个MySQL服务器通信，也可以用JDBC协议与大多数主流数据库服务器通信，其核心功能是分表分库，即将一个大表水平分割为N个小表，存储在后端MySQL服务器里或者其他数据库里
简单来说：数据库是对底层存储文件的抽象，而Mycat是对数据库的抽象
2、环境架构准备
两台虚拟机db04、db05
每台创建四个mysql实例：3307 3308 3309 3310
2.1 安装文件准备
cd /opt
wget https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
tar xf mysql-5.7.30-linux-glibc2.12-x86_64.tar.gz
ln -s /opt/mysql-5.7.30-linux-glibc2.12-x86_64 /data/app/mysql
useradd -M -s /sbin/nologin mysql
chown -R mysql.mysql /data/*
2.2 创建相关目录并初始化数据
mkdir /data/33{07..10}/data -p
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/data/app/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/data/app/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/data/app/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3310/data --basedir=/data/app/mysql
2.3 准备配置文件和启动脚本
2.3.1 db04
配置文件
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/data/app/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=7
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/data/app/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=8
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/data/app/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=9
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/data/app/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=10
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
启动脚本
cat >/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3308.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3309.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF
2.3.2 db05
配置文件
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/data/app/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=17
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/data/app/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=18
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/data/app/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=19
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3310/my.cnf<<EOF
[mysqld]
basedir=/data/app/mysql
datadir=/data/3310/data
socket=/data/3310/mysql.sock
port=3310
log-error=/data/3310/mysql.log
log_bin=/data/3310/mysql-bin
binlog_format=row
skip-name-resolve
server-id=20
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
启动脚本
cat >/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3308.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3309.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF
2.4 启动多实例
启动多实例
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310
测试服务状态
[root@db4 opt]# mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
ke 'server_id'"+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
[root@db4 opt]# mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 8 |
+---------------+-------+
[root@db4 opt]# mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
[root@db4 opt]# mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 10 |
+---------------+-------+
[root@db5 ~]# mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
[root@db5 ~]# mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 18 |
+---------------+-------+
[root@db5 ~]# mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
[root@db5 ~]# mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 20 |
+---------------+-------+
2.5 搭建主从环境
按照架构图进行搭建主从环境
2.5.1 stard1
192.168.10.54:3307 <---> 192.168.10.55:3307 双主搭建
# db05
mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'192.168.10.%' identified by '123';"
mysql -S /data/3307/mysql.sock -e "grant all on *.* to root@'192.168.10.%' identified by '123' with grant option;"
# db04
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.10.55', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3307/mysql.sock -e "start slave;"
mysql -S /data/3307/mysql.sock -e "show slave status\G"
# db05
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.10.54', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3307/mysql.sock -e "start slave;"
mysql -S /data/3307/mysql.sock -e "show slave status\G"
192.168.10.54:3309 ---> 192.168.10.54:3307 主从搭建
# db04
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.10.54', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3309/mysql.sock -e "start slave;"
mysql -S /data/3309/mysql.sock -e "show slave status\G"
192.168.10.55:3309 ---> 192.168.10.55:3307 主从搭建
# db05
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.10.55', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3309/mysql.sock -e "start slave;"
mysql -S /data/3309/mysql.sock -e "show slave status\G"
2.5.2 stard2
192.168.10.55:3308 <---> 192.168.10.54:3308 双主搭建
# db04
mysql -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'192.168.10.%' identified by '123';"
mysql -S /data/3308/mysql.sock -e "grant all on *.* to root@'192.168.10.%' identified by '123' with grant option;"
# db05
mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.10.54', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3308/mysql.sock -e "start slave;"
mysql -S /data/3308/mysql.sock -e "show slave status\G"
# db04
mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.10.55', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3308/mysql.sock -e "start slave;"
mysql -S /data/3308/mysql.sock -e "show slave status\G"
192.168.10.55:3310 ---> 192.168.10.55:3308 主从搭建
# db05
mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.10.55', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3310/mysql.sock -e "start slave;"
mysql -S /data/3310/mysql.sock -e "show slave status\G"
192.168.10.54:3310 ---> 192.168.10.54:3308 主从搭建
# db04
mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.10.54', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
mysql -S /data/3310/mysql.sock -e "start slave;"
mysql -S /data/3310/mysql.sock -e "show slave status\G"
2.6 检测主从状态
db04
[root@db4 ~]# mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db4 ~]# mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db4 ~]# mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db4 ~]# mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
db05
[root@db5 ~]# mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db5 ~]# mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db5 ~]# mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db5 ~]# mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3、安装 MyCat 软件
下载地址：http://dl.mycat.org.cn/ 安装 java 环境
[root@localhost opt]# yum install java -y
下载 mycat 软件
[root@localhost opt]# wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
解压安装，加入环境变量
[root@localhost opt]# tar xf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz
[root@localhost /]# vim /etc/profile
export PATH=/opt/mycat/bin:/usr/local/mysql/bin:$PATH
[root@localhost /]# source /etc/profile
启动 mycat
[root@localhost /]# mycat start
Starting Mycat-server...
[root@localhost /]#
连接 mycat
[root@localhost /]# mysql -uroot -p123456 -h127.0.0.1 -P8066
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.7.6-release-20210303094759 MyCat Server (OpenCloudDB)
Copyright (c) 2000, 2020, 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 databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql>
4、配置文件介绍
# conf目录
autopartition-long.txt # 分片配置信息
auto-sharding-long.txt # 分片配置信息
auto-sharding-rang-mod.txt # 分片配置信息
rule.xml # 分片策略的定义和使用方法
schema.xml # 主配置文件，节点、分片、高可用、读写分离
server.xml # Mycat服务配置文件
# log目录
mycat.log # 系统日志
wrapper.log # 启动日志
4.1 schema.xml 配置文件
[root@localhost ~]# vim /opt/mycat/conf/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="sh1">
<table name="city" dataNode="sh1"/> # 取节点sh1的city表
<table name="country" dataNode="sh2"/> # 取节点sh2的country表
# 分片定义(dn节点定义)
</schema>
<dataNode name="sh1" dataHost="dh1" database= "world" /> # sh1数据源是从world库，具体连接配置见dh1
<dataNode name="sh2" dataHost="dh2" database= "world" /> # sh2数据源是从world库，具体连接配置见dh2
# 数据主机定义(dh1定义)
<dataHost name="dh1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
# 两套主从，默认db1写，其它三台只读。
<writeHost host="db1" url="192.168.10.54:3307" user="root" password="123"> # 主库(Master)
<readHost host="db2" url="192.168.10.54:3309" user="root" password="123" /> # 从库
</writeHost>
<writeHost host="db3" url="192.168.10.55:3307" user="root" password="123"> # 备用主库(standby Master)
<readHost host="db4" url="192.168.10.55:3309" user="root" password="123" /> # 从库
</writeHost>
</dataHost>
# 数据主机定义(dh2定义)
<dataHost name="dh2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
# 两套主从，默认db1写，其它三台只读。
<writeHost host="db1" url="192.168.10.54:3308" user="root" password="123"> # 主库(Master)
<readHost host="db2" url="192.168.10.54:3310" user="root" password="123" /> # 从库
</writeHost>
<writeHost host="db3" url="192.168.10.55:3308" user="root" password="123"> # 备用主库(Standby Master)
<readHost host="db4" url="192.168.10.55:3310" user="root" password="123" /> # 从库
</writeHost>
</dataHost>
</mycat:schema>
4.2 schema.xml 部分参数说明
以下参数正常使用默认值即可。
<dataHost name="dh2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
balance # 读负载均衡参数
1：1w/3r,所有standby writehost、readhost参与select语句的负载均衡。
0：1rw,不开启读写分离机制，所有读写操作都发送到第一台writehost上。
2：1rw/3r,所有读操作都随机在wirtehost、readhost上分发。
writeType # 写负载均衡参数
0：1w/3r,默认，所有写操作只发送到配置的第一个writehost，第一个挂了后，切换到第二个还生存的writehsot，重新弄启动后以切换后的writehost为主，切换记录在配置文件：dnindex.properties中。
1：2w/2r,所有写操作随机发送到所有writehost，般用于双主半同步复制。并不推荐使用。
switchType # 是否自动切换
-1：不自动切换
1 ：默认，自动切换
2 ：基于MySQL主从同步的状态决定是否切换，心跳语句未show slave status
maxCon # 最大连接并发数
minCon # 连接池，mycat在启动之后，会在后端节点上自动开启的链接线程
tempReadHostAvailable = "1" # master宕机后，允许这台的slave继续处理select任务
这个一主一从时（一个readhost，一个writehost），可以开启这个参数。一般不用。
<heartbeat>select user()</heartbeat> # 监测心跳
5、垂直分片应用
5.1 配置文件准备
[root@localhost ~]# vim /opt/mycat/conf/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="sh1">
<table name="table7" dataNode="sh1"/>
<table name="table8" dataNode="sh2"/>
</schema>
<dataNode name="sh1" dataHost="dh1" database= "world" />
<dataNode name="sh2" dataHost="dh2" database= "world" />
<dataHost name="dh1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.10.54:3307" user="root" password="123">
<readHost host="db2" url="192.168.10.54:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.10.55:3307" user="root" password="123">
<readHost host="db4" url="192.168.10.55:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="dh2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.10.54:3308" user="root" password="123">
<readHost host="db2" url="192.168.10.54:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.10.55:3308" user="root" password="123">
<readHost host="db4" url="192.168.10.55:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
5.2 创建测试数据库
在 3307 节点创建 table7 和 table9，在 3308 节点创建 table7 和 table9。
mysql -S /data/3307/mysql.sock -e "create database world charset utf8;"
mysql -S /data/3308/mysql.sock -e "create database world charset utf8;"
mysql -S /data/3307/mysql.sock -e "use world;create table table7(id int,name varchar(20));create table table9(id int,name varchar(20));"
mysql -S /data/3308/mysql.sock -e "use world;create table table8(id int,name varchar(20));create table table7(id int,name varchar(20));"
5.3 重启 mycat 并检查逻辑库
mycat restart;
mysql> use TESTDB
Database changed
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| table7 |
| table8 |
| table9 |
+-----------------+
3 rows in set (0.01 sec)
5.4 写入测试
目前逻辑库 TESTDB 中存在 table7-9 三张表。table7 为 3307 节点表，table8 为 3308 节点表，table9 为 3308/3309 共有表。以此结论进行写入测试。
# 在逻辑库TESTDB中对3张表进行数据插入
mysql> insert into table7 values(1,'a');
mysql> insert into table8 values(2,'b');
mysql> insert into table9 values(3,'c');
# 在逻辑库查看三张表数据
mysql> select * from table7;
+------+------+
| id | name |
+------+------+
| 1 | a |
+------+------+
1 row in set (0.03 sec)
mysql> select * from table8;
+------+------+
| id | name |
+------+------+
| 2 | b |
+------+------+
1 row in set (0.00 sec)
mysql> select * from table9;
+------+------+
| id | name |
+------+------+
| 3 | c |
+------+------+
1 row in set (0.00 sec)
# 在3307节点查看数据
[root@db4 ~]# mysql -S /data/3307/mysql.sock -e "select * from world.table7;"
+------+------+
| id | name |
+------+------+
| 1 | a |
+------+------+
[root@db4 ~]# mysql -S /data/3307/mysql.sock -e "select * from world.table9;"
+------+------+
| id | name |
+------+------+
| 3 | c |
+------+------+
# 在3308节点查看数据
[root@db4 ~]# mysql -S /data/3308/mysql.sock -e "select * from world.table8;"
+------+------+
| id | name |
+------+------+
| 2 | b |
+------+------+
[root@db4 ~]# mysql -S /data/3309/mysql.sock -e "select * from world.table9;"
+------+------+
| id | name |
+------+------+
| 3 | c |
+------+------+
6、水平分片应用
6.1 拆分概念
6.1.1 分片策略
水平拆分包含了几乎经典业务中大部分的分片策略，mycat 已经开发了相应算法，非常方便调用。范围分片 取模 枚举 日期 HASH 等
6.1.2 分片键
作为分片条件的列，用来做查询条件的列，比较适合做分片键。
6.2 范围分片
对一张表 table3 进行分片
6.2.1 试用环境
1、表内行数较多(如 2000w 行数据，可按照范围分为 1-1000w，1000w01-2000w 分片) 2、数据访问非常频繁，但用户访问较离散。所有数据范围均匀访问。
6.2.2 配置文件准备
修改配置文件，定制分片策略。
# 1、schema.xml 添加：
vim schema.xml
<table name="table3" dataNode="sh1,sh2" rule="auto-sharding-long" />
# 2、定义和使用分片策略
vim rule.xml
<tableRule name="auto-sharding-long"> # 分片策略名称，schema.xml中调用
<rule>
<columns>id</columns> # 分片键为id列
<algorithm>rang-long</algorithm> # 范围方式(函数)
</rule>
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property> # 传参文件
</function>
# 3、定义范围
vim autopartition-long.txt
0-10=0 # id为0-10为0号分片
10-20=1 # id为10-20为0号分片
6.2.3 创建测试表
mysql -S /data/3307/mysql.sock -e "use world;create table table3 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use world;create table table3 (id int not null primary key auto_increment,name varchar(20) not null);"
6.2.4 重启 mycat 并检查
mycat restart
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| table3 |
| table7 |
| table8 |
| table9 |
+-----------------+
4 rows in set (0.07 sec)
6.2.5 写入测试
use TESTDB;
insert into table3(id,name) values(1,'a');
insert into table3(id,name) values(2,'b');
insert into table3(id,name) values(3,'c');
insert into table3(id,name) values(4,'d');
insert into table3(id,name) values(11,'aa');
insert into table3(id,name) values(12,'bb');
insert into table3(id,name) values(13,'cc');
insert into table3(id,name) values(14,'dd');
# 逻辑库查询结果
mysql> select * from table3;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 11 | aa |
| 12 | bb |
| 13 | cc |
| 14 | dd |
+----+------+
8 rows in set (0.03 sec)
# 3307节点查询结果
[root@db4 ~]# mysql -S /data/3307/mysql.sock -e "select * from world.table3;"
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
# 3308节点查询结果
[root@db4 ~]# mysql -S /data/3308/mysql.sock -e "select * from world.table3;"
+----+------+
| id | name |
+----+------+
| 11 | aa |
| 12 | bb |
| 13 | cc |
| 14 | dd |
+----+------+
6.3 取模分片
取余数分片方式：分片键（一个列）与节点数进行取余，得到余数，将数据写入对应节点。
6.3.1 配置文件准备
# 1、schema.xml 添加：
vim schema.xml
<table name="table4" dataNode="sh1,sh2" rule="mod-long" />
# 查看和定义分片使用
vim rule.xml
<property name="count">2</property> # 定义被除数（节点个数）
6.3.2 准备测试表
mysql -S /data/3307/mysql.sock -e "use world;create table table4 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use world;create table table4 (id int not null primary key auto_increment,name varchar(20) not null);"
6.3.3 重启 mycat
mycat restart
6.3.4 写入测试
use TESTDB;
insert into table4(id,name) values(1,'a');
insert into table4(id,name) values(2,'b');
insert into table4(id,name) values(3,'c');
insert into table4(id,name) values(4,'d');
# 在逻辑库TESTDB查询结果
mysql> select * from table4;
+----+------+
| id | name |
+----+------+
| 2 | b |
| 4 | d |
| 1 | a |
| 3 | c |
+----+------+
4 rows in set (0.03 sec)
# 在3307节点查询结果
[root@db4 ~]# mysql -S /data/3307/mysql.sock -e "select * from world.table4;"
+----+------+
| id | name |
+----+------+
| 2 | b |
| 4 | d |
+----+------+
# 在3308节点查询结果
[root@db4 ~]# mysql -S /data/3308/mysql.sock -e "select * from world.table4;"
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | c |
+----+------+
6.4 枚举分片
枚举分片要根据不同的表的情况，选择分片键 。如中国表中，是根据不同省进行存储数据，便可根据省名称进行分片。前提是有一个列中存储了省的名称。如 bj、sh、hlj 等。如下表，可根据 name 列进行分片。
| id | name | teleno |
|---|---|---|
| 1 | bj | 1111111 |
| 2 | sh | 2222222 |
| 3 | bj | 3333333 |
| 4 | hlj | 4444444 |
| 5 | sh | 5555555 |
6.4.1 配置文件准备
# 1、schema.xml 添加：
vim schema.xml
<table name="table4" dataNode="sh1,sh2" rule="sharding-by-intfile" />
# 2、查看和定义分片使用
vim rule.xml
<tableRule name="sharding-by-intfile">
<rule>
<columns>name</columns> # 修改分片键(name)
<algorithm>hash-int</algorithm>
</rule>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property> # rule增加此行，修改type为1。不然默认为0，partition-hash-int.txt文档只支持二进制。bj/sh不生效。
</function>
# 3、定义范围
vim partition-hash-int.txt
bj=0 # name列为bj，分到0分片
sh=1 # name列为sh，分到1分片
DEFAULT_NODE=1 # 其它非bj,sh，分到默认1分片
6.5 Mycat 全局表
如果你的业务中有写数据类似于数据字典，比如配置文件的配置、常用业务的配置或者数据量不大很少变动的表。这些表往往不是特别大，而且大部分的业务场景都会用到。那么这种表适合设置为 Mycat 全局表。无需对数据进行切分，在所有的分片上保存一份数据即可。Mycat 在 Join 操作中，业务表与全局表进行 Join 聚合会优先选择相同分片内的全局表 join，避免跨库 join。在进行数据插入操作是，mycat 将把数据分发到全局表对应的所有分片执行，在进行数据读取时候会随机获取一个节点的数据。
6.5.1 配置全局表
# 1、schema.xml 添加：
vim schema.xml
<table name="t_area" primaryKey="id" type="global" dataNode="sh1,sh2" />
6.6 E-R 分片
如果业务中有两张表经常做 join 操作，如可以设置为 E-R 分片。如
select * from a join b on a.id = b.aid where b.id = 1002;
此策略会按取模分片的方式将两张表的数据分别分片到两个节点内，且相关联的数据会分到同一个节点。如 a，b 表：
a:
| id | name |
|---|---|
| 1 | a |
| 2 | b |
b:
| id | addr | aid |
|---|---|---|
| 1001 | bj | 1 |
| 1002 | sh | 2 |
分片结果会 a 表的 1 行和 b 表的 1001 行在一个节点，a 表的 2 和 b 表的 1002 在一个节点。因为有 aid 相关联。
6.6.1 准备配置文件
schema.xml添加
vim schema.xml
<table name="a" dataNode="sh1,sh2" rule="mod-long_e-r" > # 因为rule名称与mod-log重复，复制重命名新增一个，对应rule也需要新增同样名称表定义和函数。
<childTable name="b" joinKey="aid" parentKey="id" /> # 配置非驱动表
</table>
rule.xml增加
vim rule.xml
</tableRule>
<tableRule name="mod-long_e-r"> # 自定义表定义名称
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</function>
<function name="mod-long_e-r" class="io.mycat.route.function.PartitionByMod"> # 自定义函数名称
<!-- how many data nodes -->
<property name="count">2</property>
</function>
6.6.2 准备测试表
mysql -S /data/3307/mysql.sock -e "use world;CREATE TABLE a ( id INT NOT NULL PRIMARY key auto_increment, NAME VARCHAR ( 20 ) NOT NULL );"
mysql -S /data/3307/mysql.sock -e "use world;CREATE TABLE b ( id INT NOT NULL PRIMARY key auto_increment, addr VARCHAR ( 20 ) NOT NULL, aid int);"
mysql -S /data/3308/mysql.sock -e "use world;CREATE TABLE a ( id INT NOT NULL PRIMARY key auto_increment, NAME VARCHAR ( 20 ) NOT NULL );"
mysql -S /data/3308/mysql.sock -e "use world;CREATE TABLE b ( id INT NOT NULL PRIMARY key auto_increment, addr VARCHAR ( 20 ) NOT NULL, aid int);"
6.6.3 重启 mycat
mycat restart
6.6.4 写入测试
# 在逻辑库TESTDB写入
insert into a(id,name) values(1,'a');
insert into a(id,name) values(2,'b');
insert into a(id,name) values(3,'c');
insert into a(id,name) values(4,'d');
insert into a(id,name) values(5,'e');
insert into b(id,addr,aid) values(1001,'bj',1);
insert into b(id,addr,aid) values(1002,'sh',2);
insert into b(id,addr,aid) values(1003,'gz',3);
insert into b(id,addr,aid) values(1004,'wh',4);
insert into b(id,addr,aid) values(1005,'tj',5);
# 在逻辑库查询结果
mysql> USE TESTDB;
mysql> select * from a;
+----+------+
| id | NAME |
+----+------+
| 2 | b |
| 4 | d |
| 1 | a |
| 3 | c |
| 5 | e |
+----+------+
5 rows in set (0.02 sec)
mysql> select * from b;
+------+------+------+
| id | addr | aid |
+------+------+------+
| 1002 | sh | 2 |
| 1004 | wh | 4 |
| 1001 | bj | 1 |
| 1003 | gz | 3 |
| 1005 | tj | 5 |
+------+------+------+
5 rows in set (0.00 sec)
# 在3307节点查询结果
[root@db4 ~]# mysql -S /data/3307/mysql.sock -e "select * from world.a;"
+----+------+
| id | NAME |
+----+------+
| 2 | b |
| 4 | d |
+----+------+
[root@db4 ~]# mysql -S /data/3307/mysql.sock -e "select * from world.b;"
+------+------+------+
| id | addr | aid |
+------+------+------+
| 1002 | sh | 2 |
| 1004 | wh | 4 |
+------+------+------+
# 在3308节点查询结果
[root@db4 ~]# mysql -S /data/3308/mysql.sock -e "select * from world.a;"
+----+------+
| id | NAME |
+----+------+
| 1 | a |
| 3 | c |
| 5 | e |
+----+------+
[root@db4 ~]# mysql -S /data/3308/mysql.sock -e "select * from world.b;"
+------+------+------+
| id | addr | aid |
+------+------+------+
| 1001 | bj | 1 |
| 1003 | gz | 3 |
| 1005 | tj | 5 |
+------+------+------+
7、Mycat 日常管理
7.1 管理服务常用命令
# 查看帮助
show @@help;
# 查看分片信息
show @@datanode;
# 查看数据源
show @@datasource;
# 查看服务信息
show @@server;
# 重载配置信息
reload @@config : schema.xml
reload @@config_all : 所有配置重新加载
7.2 修改逻辑库名
# 修改配置文件
vim schema.xml
<schema name="DB2" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1"> # 修改库名DB2
vim server.xml
<user name="root" defaultAccount="true"> # root用户
<property name="password">123456</property> # 默认密码
<property name="schemas">DB2</property> # 默认数据库(修改)
<property name="defaultSchema">DB2</property> # 默认数据库(修改)
<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema，不设置则为null,报错 -->
<!-- 表级 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> # user 用户
<property name="schemas">DB2</property> # 默认数据库(修改)
<property name="readOnly">true</property> # 只读权限
<property name="defaultSchema">DB2</property> # 默认数据库(修改)
</user>
# 重载配置信息
reload config_all;
7.3 增加逻辑库
# schema.xml增加
<schema name="DB3" checkSQLschema="false" sqlMaxLimit="100" dataNode="sh1">
</schema>
# server.xml里授权
<property name="schemas">DB2,DB3</property> # 在原库后增加","分隔
# 重载配置信息
reload config_all;
评论