MySQL 分布式软件MyCAT介绍
目录
MySQL 分布式软件MyCAT介绍
一、MySQL 分布式软件MyCAT介绍
1.1.1 MySQL 分布式软件MyCAT介绍
1.1.2 MyCat 架构环境部署的前提准备
1.1.3 MySQL分布式架构部署（MyCat）
1.1.4 MyCat 读写分离+高可用
1.1.5 MyCat 分库分表
一、MySQL 分布式软件MyCAT介绍
1.1.1 MySQL 分布式软件MyCAT介绍
- MySQL 分布式软件MyCAT介绍
1、一个彻底开源的，面向企业应用开发的大数据库集群
2、支持事务、ACID、可以替代MySQL的加强版数据库
3、一个可以视为MySQL集群的企业级数据库，用来替代昂贵的Oracle集群
4、一个融合内存缓存技术、NoSQL技术、HDFS大数据的新型SQL Server
5、结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
6、一个新颖的数据库中间件产品
- 为什么要使用MyCat
如今随着互联网的发展，数据的量级也是撑指数的增长，从GB到TB到PB。对数据的各种操作也是愈加的困难，传统的关系性数据库已经无法满足快速查询与插入数据的需求。这个时候NoSQL的出现暂时解决了这一危机。它通过降低数据的安全性，减少对事务的支持，减少对复杂查询的支持，来获取性能上的提升。但是，在有些场合NoSQL一些折衷是无法满足使用场景的，就比如有些使用场景是绝对要有事务与安全指标的。这个时候NoSQL肯定是无法满足的，所以还是需要使用关系性数据库。如何使用关系型数据库解决海量存储的问题呢？此时就需要做数据库集群，为了提高查询性能将一个数据库的数据分散到不同的数据库中存储，为应对此问题就出现了——MyCat
- MyCat 的核心应用（分库分表）
数据库分片指：通过某种特定的条件，将我们存放在一个数据库中的数据分散存放在不同的多个数据库（主机）中，这样来达到分散单台设备的负载，根据切片规则，可分为以下两种切片模式
MyCAT通过定义表的分片规则来实现分片，每个表格可以捆绑一个分片规则，每个分片规则指定一个分片字段并绑定一个函数，来实现动态分片算法
1.Schema：逻辑库，与MySQL中的Database（数据库）对应，一个逻辑库中定义了所包括的Table。
2.Table：逻辑表，即物理数据库中存储的某一张表，与传统数据库不同，这里的表格需要声明其所存储的逻辑数据节点DataNode。在此可以指定表的分片规则。
3.DataNode：MyCAT的逻辑数据节点，是存放table的具体物理节点，也称之为分片节点，通过DataSource来关联到后端某个具体数据库上
4.DataSource：定义某个物理库的访问地址，用于捆绑到Datanode上
5、分片规则：前面讲了数据切分，一个大表被分成若干个分片表，就需要一定的规则，这样按照某种业务规则把数据分到某个分片的规则就是分片规则，数据切分选择合适的分片规则非常重要，将极大的避免后续数据处理的难
1.1.2 MyCat 架构环境部署的前提准备
- MyCat 架构环境部署需求
系统：
[root@db01 ~]# cat /etc/redhat-release
CentOS Linux release 7.7.1908 (Core)
MySQL版本：5.7.26
两台虚拟主机：
10.4.7.51
10.4.7.52
两台虚拟主机分别安装 3307 8 9 10 四套多实例
- 部署多实例
[root@db01 ~]# rm -rf /data/330*
[root@db01 ~]# mkdir -p /data/330{7,8,9,10}
[root@db01 ~]# ll /data/330{7,8,9,10}
/data/3310:
total 0
/data/3307:
total 0
/data/3308:
total 0
/data/3309:
total 0
10.4.7.51(db01)
配置文件
[root@db01 /data/3307]# cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/application/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
[root@db01 /data/3308]# cat >/data/3308/my.cnf<<EOF
> [mysqld]
> basedir=/application/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
[root@db01 /data/3309]# cat >/data/3309/my.cnf<<EOF
> [mysqld]
> basedir=/application/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
[root@db01 /data/3310]# cat >/data/3310/my.cnf<<EOF
> [mysqld]
> basedir=/application/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
10.4.7.52(db02)
配置文件
[root@db02 /data/3307]# cat >/data/3307/my.cnf<<EOF
> [mysqld]
> basedir=/application/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
[root@db02 /data/3308]# cat >/data/3308/my.cnf<<EOF
> [mysqld]
> basedir=/application/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
[root@db02 /data/3309]# cat >/data/3309/my.cnf<<EOF
> [mysqld]
> basedir=/application/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
[root@db02 /data/3310]# cat >/data/3310/my.cnf<<EOF
> [mysqld]
> basedir=/application/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
初始化多实例数据库：
db01：
[root@db01 /data/3310]# /application/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3307/data
[root@db01 /data/3310]# /application/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3308/data
[root@db01 /data/3310]# /application/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3309/data
[root@db01 /data/3310]# /application/mysql/bin/mysqld --initialize-insecure --user=mysql --basedir=/application/mysql --datadir=/data/3310/data
db02：与db01相同
- 配置启动文件
[root@db01 /data/3310]# 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=/application/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
> LimitNOFILE = 5000
> EOF
[root@db01 /data/3310]# 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=/application/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
> LimitNOFILE = 5000
> EOF
[root@db01 /data/3310]# 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=/application/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
> LimitNOFILE = 5000
> EOF
[root@db01 /data/3310]# 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=/application/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
> LimitNOFILE = 5000
> EOF
#注意：db01和db02的启动配置文件相同，复制粘贴即可用。
- 赋权启动多实例
db01:
[root@db01 /data/3310]# chown -R mysql.mysql /data/*
[root@db01 /data/3310]# ll /data/
total 0
drwxr-xr-x 3 mysql mysql 32 Jul 3 14:51 3307
drwxr-xr-x 3 mysql mysql 32 Jul 3 14:52 3308
drwxr-xr-x 3 mysql mysql 32 Jul 3 14:56 3309
drwxr-xr-x 3 mysql mysql 32 Jul 3 14:59 3310
[root@db01 /data/3310]# systemctl start mysqld3307
[root@db01 /data/3310]# systemctl start mysqld3308
[root@db01 /data/3310]# systemctl start mysqld3309
[root@db01 /data/3310]# systemctl start mysqld3310
[root@db01 /data/3310]# ss -luntp|grep 33
tcp LISTEN 0 80 [::]:3306 [::]:* users:(("mysqld",pid=1370,fd=34))
tcp LISTEN 0 80 [::]:3307 [::]:* users:(("mysqld",pid=6885,fd=21))
tcp LISTEN 0 80 [::]:3308 [::]:* users:(("mysqld",pid=6917,fd=19))
tcp LISTEN 0 80 [::]:3309 [::]:* users:(("mysqld",pid=6953,fd=19))
tcp LISTEN 0 80 [::]:3310 [::]:* users:(("mysqld",pid=6985,fd=20))
db02:
[root@db02 /data/3310]# chown -R mysql.mysql /data/*
[root@db02 /data/3310]# systemctl start mysqld3307
[root@db02 /data/3310]# systemctl start mysqld3308
[root@db02 /data/3310]# systemctl start mysqld3309
[root@db02 /data/3310]# systemctl start mysqld3310
[root@db02 /data/3310]# ss -luntp|grep 33
tcp LISTEN 0 80 [::]:3306 [::]:* users:(("mysqld",pid=1654,fd=22))
tcp LISTEN 0 80 [::]:3307 [::]:* users:(("mysqld",pid=7021,fd=22))
tcp LISTEN 0 80 [::]:3308 [::]:* users:(("mysqld",pid=7053,fd=22))
tcp LISTEN 0 80 [::]:3309 [::]:* users:(("mysqld",pid=7089,fd=21))
tcp LISTEN 0 80 [::]:3310 [::]:* users:(("mysqld",pid=7123,fd=22))
检查mysql实例是否正常
db01：
[root@db01 /data/3310]# mysql -S /data/3307/mysql.sock -e "show global variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
[root@db01 /data/3310]# mysql -S /data/3308/mysql.sock -e "show global variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 8 |
+---------------+-------+
[root@db01 /data/3310]# mysql -S /data/3309/mysql.sock -e "show global variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
[root@db01 /data/3310]# mysql -S /data/3310/mysql.sock -e "show global variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 10 |
+---------------+-------+
db02：
[root@db02 /data/3310]# mysql -S /data/3307/mysql.sock -e "show global variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
[root@db02 /data/3310]# mysql -S /data/3308/mysql.sock -e "show global variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 18 |
+---------------+-------+
[root@db02 /data/3310]# mysql -S /data/3309/mysql.sock -e "show global variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
[root@db02 /data/3310]# mysql -S /data/3310/mysql.sock -e "show global variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 20 |
+---------------+-------+
- 节点主从规划
箭头指向谁是主库
10.0.0.51:3307 <-----> 10.0.0.52:3307
10.0.0.51:3309 ------> 10.0.0.51:3307
10.0.0.52:3309 ------> 10.0.0.52:3307
10.0.0.52:3308 <-----> 10.0.0.51:3308
10.0.0.52:3310 -----> 10.0.0.52:3308
10.0.0.51:3310 -----> 10.0.0.51:3308
- 分片规划
shard1：
Master：10.0.0.51:3307
slave1：10.0.0.51:3309
Standby Master：10.0.0.52:3307
slave2：10.0.0.52:3309
shard2：
Master：10.0.0.52:3308
slave1：10.0.0.52:3310
Standby Master：10.0.0.51:3308
slave2：10.0.0.51:3310
- 设置主从
shard1
10.0.0.51:3307 <-----> 10.0.0.52:3307 （互为主从）
db02:主从授权
[root@db02 /data/3307]# mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.4.7.%' identified by '123';"
[root@db02 /data/3307]# mysql -S /data/3307/mysql.sock -e "grant all on *.* to root@'10.4.7.%' identified by '123' with grant option;"
db01:设置主从
[root@db01 /data/3307]# mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.4.7.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
[root@db01 /data/3307]# mysql -S /data/3307/mysql.sock -e "start slave;"
[root@db01 /data/3307]# mysql -S /data/3307/mysql.sock -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.7.52
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 749
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 962
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
能看到同步的用户证明主从ok
[root@db01 /data/3307]# mysql -S /data/3307/mysql.sock -e "use mysql;select user,host from user;"
+---------------+-----------+
| user | host |
+---------------+-----------+
| repl | 10.4.7.% |
| root | 10.4.7.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
db02:
[root@db02 /data/3307]# mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.4.7.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
[root@db02 /data/3307]# mysql -S /data/3307/mysql.sock -e "start slave;"
[root@db02 /data/3307]# mysql -S /data/3307/mysql.sock -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.7.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 749
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
db01 3307 db02 3307 互为主从成功
db01：
10.0.0.51:3309（从） ------> 10.0.0.51:3307（主）
[root@db01 /data/3307]# mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.4.7.51', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
[root@db01 /data/3307]# mysql -S /data/3309/mysql.sock -e "start slave;"
[root@db01 /data/3307]# mysql -S /data/3309/mysql.sock -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.7.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 749
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 962
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db01 /data/3307]# mysql -S /data/3309/mysql.sock -e "use mysql;select user,host from user;"
+---------------+-----------+
| user | host |
+---------------+-----------+
| repl | 10.4.7.% |
| root | 10.4.7.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
db02：
10.0.0.52:3309（从） ------> 10.0.0.52:3307（主）
[root@db02 /data/3307]# mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.4.7.52', MASTER_PORT=3307, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
[root@db02 /data/3307]# mysql -S /data/3309/mysql.sock -e "start slave;"
[root@db02 /data/3307]# mysql -S /data/3309/mysql.sock -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.7.52
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 749
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 962
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db02 /data/3307]# mysql -S /data/3309/mysql.sock -e "use mysql;select user,host from user;"
+---------------+-----------+
| user | host |
+---------------+-----------+
| repl | 10.4.7.% |
| root | 10.4.7.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
shard2
10.0.0.52:3308 <-----> 10.0.0.51:3308 （互为主从）
db01：主从授权
[root@db01 /data/3307]# mysql -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'10.4.7.%' identified by '123';"
[root@db01 /data/3307]# mysql -S /data/3308/mysql.sock -e "grant all on *.* to root@'10.4.7.%' identified by '123' with grant option;"
db02:
[root@db02 /data/3307]# mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.4.7.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
[root@db02 /data/3307]# mysql -S /data/3308/mysql.sock -e "start slave;"
[root@db02 /data/3307]# mysql -S /data/3308/mysql.sock -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.7.51
Master_User: repl
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 749
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 962
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db02 /data/3307]# mysql -S /data/3308/mysql.sock -e "use mysql;select user,host from user;"
+---------------+-----------+
| user | host |
+---------------+-----------+
| repl | 10.4.7.% |
| root | 10.4.7.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
db01:
[root@db01 /data/3307]# mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.4.7.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
[root@db01 /data/3307]# mysql -S /data/3308/mysql.sock -e "start slave;"
[root@db01 /data/3307]# mysql -S /data/3308/mysql.sock -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.7.52
Master_User: repl
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 749
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
db01:
10.0.0.51:3310 （从）-----> 10.0.0.51:3308（主）
[root@db01 /data/3307]# mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.4.7.51', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
[root@db01 /data/3307]# mysql -S /data/3310/mysql.sock -e "start slave;"
[root@db01 /data/3307]# mysql -S /data/3310/mysql.sock -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.7.51
Master_User: repl
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 749
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 962
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db01 /data/3307]# mysql -S /data/3310/mysql.sock -e "use mysql;select user,host from user;"
+---------------+-----------+
| user | host |
+---------------+-----------+
| repl | 10.4.7.% |
| root | 10.4.7.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
db02：
10.0.0.52:3310 （从）-----> 10.0.0.52:3308（主）
[root@db02 /data/3307]# mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.4.7.52', MASTER_PORT=3308, MASTER_AUTO_POSITION=1, MASTER_USER='repl', MASTER_PASSWORD='123';"
[root@db02 /data/3307]# mysql -S /data/3310/mysql.sock -e "start slave;"
[root@db02 /data/3307]# mysql -S /data/3310/mysql.sock -e "show slave status\G"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.4.7.52
Master_User: repl
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 749
Relay_Log_File: db02-relay-bin.000002
Relay_Log_Pos: 962
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db02 /data/3307]# mysql -S /data/3310/mysql.sock -e "use mysql;select user,host from user;"
+---------------+-----------+
| user | host |
+---------------+-----------+
| repl | 10.4.7.% |
| root | 10.4.7.% |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+---------------+-----------+
- 检查主从状态：
db01:
[root@db01 /data/3307]# mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db01 /data/3307]# mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db01 /data/3307]# mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db01 /data/3307]# mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
db02:
[root@db02 /data/3307]# mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes
Yes Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db02 /data/3307]# mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db02 /data/3307]# mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
[root@db02 /data/3307]# mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
1.1.3 MySQL分布式架构部署（MyCat）
- MyCat 核心功能（分库分表）
1、schema拆分及业务分库
2、垂直拆分-分库分表
3、水平拆分-分片
其它企业代表产品
360 Atlas-Sharding
Alibaba cobar
Mycat--->DBLE（MyCat的二次开发）
TDDL
Heisenberg
Oceanus
Vitess
OneProxy
DRDS
- MyCat 建构部署
首先安装Java运行环境
[root@db01 ~]# yum install -y java
下载安装MyCat
官网下载地址：http://www.mycat.org.cn/
[root@db01 ~]# cd /server/tools/
[root@db01 /server/tools]# tar zxf Mycat-server-1.6.7.1-release-20190627191042-linux.tar.gz -C /application/
[root@db01 /server/tools]# ll /application/mycat
total 12
drwxr-xr-x 2 root root 190 Jul 3 19:49 bin
drwxrwxrwx 2 root root 6 Jun 24 2019 catlet
drwxrwxrwx 4 root root 4096 Jul 3 19:49 conf
drwxr-xr-x 2 root root 4096 Jul 3 19:49 lib
drwxrwxrwx 2 root root 6 Jun 26 2019 logs
-rwxrwxrwx 1 root root 227 Jun 27 2019 version.txt
配置文件介绍：
logs目录:
wrapper.log ---->mycat启动日志
mycat.log ---->mycat详细工作日志
conf目录:
schema.xml
主配置文件（读写分离、高可用、分布式策略定制、节点控制）
server.xml
mycat软件本身相关的配置
rule.xml
分片规则配置文件,记录分片规则列表、使用方法等
- 配置环境变量
[root@db01 /server/tools]# vim /etc/profile
[root@db01 /server/tools]# tail -1 /etc/profile
export PATH=/application/mycat/bin:$PATH
[root@db01 /server/tools]# source /etc/profile
- 启动MyCat
[root@db01 /server/tools]# mycat start
Starting Mycat-server...
[root@db01 /server/tools]# ps -ef |grep mycat
root 8342 1 0 19:55 ? 00:00:00 /application/mycat/bin/./wrapper-linux-x86-64 /application/mycat/conf/wrapper.conf wrapper.syslog.ident=mycat wrapper.pidfile=/application/mycat/logs/mycat.pid wrapper.daemonize=TRUE wrapper.lockfile=/var/lock/subsys/mycat
root 8344 8342 4 19:55 ? 00:00:03 java -DMYCAT_HOME=. -server -XX:MaxPermSize=64M -XX:+AggressiveOpts -XX:MaxDirectMemorySize=2G -Dcom.sun.management.jmxremote -Dcom.sun.management.jmxremote.port=1984 -Dcom.sun.management.jmxremote.authenticate=false -Dcom.sun.management.jmxremote.ssl=false -Xmx4G -Xms1G -Djava.library.path=lib -classpath lib/wrapper.jar:conf:lib/asm-4.0.jar:lib/commons-collections-3.2.1.jar:lib/commons-lang-2.6.jar:lib/curator-client-2.11.0.jar:lib/curator-framework-2.11.0.jar:lib/curator-recipes-2.11.0.jar:lib/disruptor-3.3.4.jar:lib/dom4j-1.6.1.jar:lib/druid-1.0.26.jar:lib/ehcache-core-2.6.11.jar:lib/fastjson-1.2.12.jar:lib/guava-19.0.jar:lib/hamcrest-core-1.3.jar:lib/hamcrest-library-1.3.jar:lib/jline-0.9.94.jar:lib/joda-time-2.9.3.jar:lib/jsr305-2.0.3.jar:lib/kryo-2.10.jar:lib/leveldb-0.7.jar:lib/leveldb-api-0.7.jar:lib/libwrapper-linux-ppc-64.so:lib/libwrapper-linux-x86-32.so:lib/libwrapper-linux-x86-64.so:lib/log4j-1.2-api-2.5.jar:lib/log4j-1.2.17.jar:lib/log4j-api-2.5.jar:lib/log4j-core-2.5.jar:lib/log4j-slf4j-impl-2.5.jar:lib/mapdb-1.0.7.jar:lib/minlog-1.2.jar:lib/mongo-java-driver-2.11.4.jar:lib/Mycat-server-1.6.7.1-release.jar:lib/mysql-binlog-connector-java-0.16.1.jar:lib/mysql-connector-java-5.1.35.jar:lib/netty-3.7.0.Final.jar:lib/netty-buffer-4.1.9.Final.jar:lib/netty-common-4.1.9.Final.jar:lib/objenesis-1.2.jar:lib/reflectasm-1.03.jar:lib/sequoiadb-driver-1.12.jar:lib/slf4j-api-1.6.1.jar:lib/univocity-parsers-2.2.1.jar:lib/velocity-1.7.jar:lib/wrapper.jar:lib/zookeeper-3.4.6.jar -Dwrapper.key=2PBFiQFz4f6rVHbM -Dwrapper.port=32000 -Dwrapper.jvm.port.min=31000 -Dwrapper.jvm.port.max=31999 -Dwrapper.pid=8342 -Dwrapper.version=3.2.3 -Dwrapper.native_library=wrapper -Dwrapper.service=TRUE -Dwrapper.cpu.timeout=10 -Dwrapper.jvmid=1 org.tanukisoftware.wrapper.WrapperSimpleApp io.mycat.Myca
[root@db01 /server/tools]# ss -luntp|grep 8066
tcp LISTEN 0 100 [::]:8066 [::]:* users:(("java",pid=8344,fd=79))
[root@db01 /server/tools]# ss -luntp|grep 9066
tcp LISTEN 0 100 [::]:9066 [::]:* users:(("java",pid=8344,fd=75))
[root@db01 /server/tools]# ss -luntp|grep java
tcp LISTEN 0 1 127.0.0.1:32000 *:* users:(("java",pid=8344,fd=4))
tcp LISTEN 0 50 [::]:1984 [::]:* users:(("java",pid=8344,fd=58))
tcp LISTEN 0 100 [::]:8066 [::]:* users:(("java",pid=8344,fd=79))
tcp LISTEN 0 50 [::]:26242 [::]:* users:(("java",pid=8344,fd=59))
tcp LISTEN 0 50 [::]:23107 [::]:* users:(("java",pid=8344,fd=57))
tcp LISTEN 0 100 [::]:9066 [::]:* users:(("java",pid=8344,fd=75))
后端连接代理端口：8066 管理端口：9066
[root@db01 ~]# mysql -uroot -p123456 -h 127.0.0.1 -P8066 -e "select version();"
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------------------------------------+
| VERSION() |
+---------------------------------------------+
| 5.6.29-mycat-1.6.7.1-release-20190627191042 |
+---------------------------------------------+
1.1.4 MyCat 读写分离+高可用
官方文档：http://www.mycat.org.cn/
- 用户创建及数据库导入
dbo1:
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3307/mysql.sock
db01 [(none)]>grant all on *.* to root@'10.4.7.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.05 sec)
db01 [(none)]>source /root/world.sql
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3308/mysql.sock
db01 [(none)]>grant all on *.* to root@'10.4.7.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.08 sec)
db01 [world]>source /root/world.sql
db02：
[root@db02 ~]# mysql -uroot -p123 -S /data/3307/mysql.sock
db02 [(none)]>grant all on *.* to root@'10.4.7.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.04 sec)
db02 [(none)]>source /root/world.sql
[root@db02 ~]# mysql -uroot -p123 -S /data/3309/mysql.sock
db02 [(none)]>grant all on *.* to root@'10.4.7.%' identified by '123';
Query OK, 0 rows affected, 1 warning (0.10 sec)
- 读写分支
[root@db01 ~]# cd /application/mycat/conf/
[root@db01 /application/mycat/conf]# cp schema.xml schema.xml.back
[root@db01 /application/mycat/conf]# vim schema.xml
[root@db01 /application/mycat/conf]# cat 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">
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "world" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.4.7.51:3307" user="root" password="123">
<readHost host="db2" url="10.4.7.51:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
重启MyCat
[root@db01 /application/mycat/conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
[root@db01 /application/mycat/conf]# ss -luntp|grep java
tcp LISTEN 0 1 127.0.0.1:32000 *:* users:(("java",pid=13272,fd=4))
tcp LISTEN 0 50 [::]:31901 [::]:* users:(("java",pid=13272,fd=59))
tcp LISTEN 0 50 [::]:1984 [::]:* users:(("java",pid=13272,fd=58))
tcp LISTEN 0 100 [::]:8066 [::]:* users:(("java",pid=13272,fd=79))
tcp LISTEN 0 50 [::]:18276 [::]:* users:(("java",pid=13272,fd=57))
tcp LISTEN 0 100 [::]:9066 [::]:* users:(("java",pid=13272,fd=75))
测试
[root@db01 /application/mycat/conf]# mysql -uroot -p123456 -h 127.0.0.1 -P 8066
#mycat用户默认是root 密码是123456
db01 [(none)]>show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.07 sec)
db01 [(none)]>use 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
db01 [TESTDB]>show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
3 rows in set (0.00 sec)
读测试
db01 [TESTDB]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
1 row in set (0.05 sec)
db01 [TESTDB]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
1 row in set (0.00 sec)
写测试
db01 [TESTDB]>begin;select @@server_id;
Query OK, 0 rows affected (0.00 sec)
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
1 row in set (0.03 sec)
db01 [TESTDB]>begin;select @@server_id;
Query OK, 0 rows affected (0.00 sec)
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
1 row in set (0.00 sec)
注意MyCat的读是被写包含的，如果写主机宕机，那么读主机就不提供正常服务了。
- 配置文件-读写分离+高可用
[root@db01 /application/mycat/conf]# vim schema.xml
[root@db01 /application/mycat/conf]# cat 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">
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "world" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.4.7.51:3307" user="root" password="123">
<readHost host="db2" url="10.4.7.51:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.4.7.52:3307" user="root" password="123">
<readHost host="db4" url="10.4.7.52:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
重启mycat
[root@db01 /application/mycat/conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
测试：
读测试
db01 [(none)]>commit;
Query OK, 0 rows affected (0.00 sec)
db01 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
1 row in set (0.24 sec)
db01 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 19 |
+-------------+
1 row in set (0.01 sec)
db01 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
1 row in set (0.03 sec)
db01 [(none)]>select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 17 |
+-------------+
1 row in set (0.06 sec)
写测试
db01 [(none)]>begin;select @@server_id;commit;
Query OK, 0 rows affected (0.00 sec)
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
1 row in set (0.07 sec)
- schema.xml 配置属性介绍
balance属性:
负载均衡类型，目前的取值有3种：
1、balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。
2、balance="1"，全部的readHost与standby writeHost参与select语句的负载均衡，简单的说，
当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。
3、balance="2"，所有读操作都随机的在writeHost、readhost上分发。
writeType属性：
负载均衡类型，目前的取值有2种：
1、writeType="0", 所有写操作发送到配置的第一个writeHost，
第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为主，切换记录在配置文件中:dnindex.properties .
2、writeType=“1”，所有写操作都随机的发送到配置的writeHost，但不推荐使用
switchType属性：
-1 表示不自动切换
1 默认值，自动切换
2 基于MySQL主从同步的状态决定是否切换 ，心跳语句为 show slave status
datahost其他配置：
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
maxCon="1000"：最大的并发连接数
minCon="10" ：mycat在启动之后，会在后端节点上自动开启的连接线程
tempReadHostAvailable="1"
这个一主一从时（1个writehost，1个readhost时），可以开启这个参数，如果2个writehost，2个readhost时
<heartbeat>select user()</heartbeat> 监测心跳
1.1.5 MyCat 分库分表
- 垂直分表
[root@db01 /application/mycat/conf]# vim schema.xml
[root@db01 /application/mycat/conf]# cat 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="user" dataNode="sh1"/>
<table name="order_t" dataNode="sh2"/>
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "taobao" />
<dataNode name="sh2" dataHost="oldguo2" database= "taobao" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.4.7.51:3307" user="root" password="123">
<readHost host="db2" url="10.4.7.51:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.4.7.52:3307" user="root" password="123">
<readHost host="db4" url="10.4.7.52:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.4.7.51:3308" user="root" password="123">
<readHost host="db2" url="10.4.7.51:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.4.7.52:3308" user="root" password="123">
<readHost host="db4" url="10.4.7.52:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
创建测试库和表:
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3307/mysql.sock -e "create database taobao charset utf8;"
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3308/mysql.sock -e "create database taobao charset utf8;"
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3307/mysql.sock -e "use taobao;create table user(id int,name varchar(20))";
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3308/mysql.sock -e "use taobao;create table order_t(id int,name varchar(20))"
重启MyCat
[root@db01 /application/mycat/conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
查看
[root@db01 /application/mycat/conf]# mysql -uroot -p123456 -h10.4.7.51 -P8066
db01 [(none)]>show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
db01 [TESTDB]>show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| order_t |
| user |
+------------------+
2 rows in set (0.00 sec)
- MyCAT核心特性——分片（水平拆分）
分片：对一个"bigtable"，比如说t3表
(1)行数非常多，800w
(2)访问非常频繁
分片的目的：
（1）将大数据量进行分布存储
（2）提供均衡的访问路由
分片策略：
范围 range 800w 1-400w 400w01-800w
取模 mod 取余数
枚举
哈希 hash
时间 流水
优化关联查询
全局表
ER分片
- 范围分片
[root@db01 /application/mycat/conf]# cat 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="t3" dataNode="sh1,sh2" rule="auto-sharding-long" />
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "taobao" />
<dataNode name="sh2" dataHost="oldguo2" database= "taobao" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.4.7.51:3307" user="root" password="123">
<readHost host="db2" url="10.4.7.51:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.4.7.52:3307" user="root" password="123">
<readHost host="db4" url="10.4.7.52:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.4.7.51:3308" user="root" password="123">
<readHost host="db2" url="10.4.7.51:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.4.7.52:3308" user="root" password="123">
<readHost host="db4" url="10.4.7.52:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
分片配置文件（找到范围分片的位置和配置文件地址）
<tableRule name="auto-sharding-long"> #范围分片
<rule>
<columns>id</columns>
<algorithm>rang-long</algorithm>
</rule>
</tableRule>
配置地址
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property> #范围分片的txt文件
</function>
编辑范围分片的txt文件
[root@db01 /application/mycat/conf]# cat autopartition-long.txt
# range start-end ,data node index
# K=1000,M=10000.
0-500M=0
500M-1000M=1
1000M-1500M=2
修改如下（模拟测试）：
[root@db01 /application/mycat/conf]# cat autopartition-long.txt
# range start-end ,data node index
# K=1000,M=10000.
0-5=0
6-10=1
创建测试表：
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3307/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3308/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"
重启mycat
[root@db01 /application/mycat/conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
连接mycat插入数据测试
[root@db01 /application/mycat/conf]# mysql -uroot -p123456 -h10.4.7.51 -P8066
db01 [(none)]>use TESTDB
db01 [TESTDB]>show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| t3 |
| user |
+------------------+
2 rows in set (0.00 sec)
db01 [TESTDB]>insert into t3(id,name) values(1,'a');
Query OK, 1 row affected (0.21 sec)
db01 [TESTDB]>insert into t3(id,name) values(2,'b');
Query OK, 1 row affected (0.15 sec)
db01 [TESTDB]>insert into t3(id,name) values(3,'c');
Query OK, 1 row affected (0.19 sec)
db01 [TESTDB]>insert into t3(id,name) values(4,'d');
Query OK, 1 row affected (0.02 sec)
db01 [TESTDB]>insert into t3(id,name) values(5,'e');
Query OK, 1 row affected (0.05 sec)
db01 [TESTDB]>insert into t3(id,name) values(6,'f');
Query OK, 1 row affected (0.05 sec)
db01 [TESTDB]>insert into t3(id,name) values(7,'x');
Query OK, 1 row affected (0.05 sec)
db01 [TESTDB]>insert into t3(id,name) values(8,'y');
Query OK, 1 row affected (0.06 sec)
db01 [TESTDB]>select * from t3;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
| 6 | f |
| 7 | x |
| 8 | y |
+----+------+
8 rows in set (0.11 sec)
单独登录数据库查看差异
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3307/mysql.sock -e 'use taobao;select * from t3;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
| 5 | e |
+----+------+
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3308/mysql.sock -e 'use taobao;select * from t3;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
| 6 | f |
| 7 | x |
| 8 | y |
+----+------+
#在mycat的逻辑数据库中t3表的数据时在一起的，但是在后端数据的3307,3308中t3表的数据是按照我们的范围分片策略分开的，证明范围分片策略成功。
- 取模分片
取余分片方式：分片键（一个列）与节点数量进行取余，得到余数，将数据写入对应节点
[root@db01 /application/mycat/conf]# cp schema.xml schema.xml.rang
[root@db01 /application/mycat/conf]# cat 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="t4" dataNode="sh1,sh2" rule="mod-long" /> #只需要修改这里其它不用修改
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "taobao" />
<dataNode name="sh2" dataHost="oldguo2" database= "taobao" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.4.7.51:3307" user="root" password="123">
<readHost host="db2" url="10.4.7.51:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.4.7.52:3307" user="root" password="123">
<readHost host="db4" url="10.4.7.52:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.4.7.51:3308" user="root" password="123">
<readHost host="db2" url="10.4.7.51:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.4.7.52:3308" user="root" password="123">
<readHost host="db4" url="10.4.7.52:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
找到取模分片的位置
<tableRule name="mod-long">
<rule>
<columns>id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes --> #分片节点有多少个
<property name="count">3</property> #将3改为2，因为分片节点只有两个
</function>
修改如下：
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<!-- <property name="count">3</property> --> #把这行注释掉
<property name="count">2</property> #添加新行
</function>
创建测试表：
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3307/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3308/mysql.sock -e "use taobao;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"
重启mycat
[root@db01 /application/mycat/conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
测试
[root@db01 /application/mycat/conf]# mysql -uroot -p123456 -h10.4.7.51 -P8066
db01 [(none)]>use TESTDB
db01 [TESTDB]>insert into t4(id,name) values(1,'a');
Query OK, 1 row affected (0.23 sec)
db01 [TESTDB]>insert into t4(id,name) values(2,'b');
Query OK, 1 row affected (0.12 sec)
db01 [TESTDB]>insert into t4(id,name) values(3,'c');
Query OK, 1 row affected (0.03 sec)
db01 [TESTDB]>insert into t4(id,name) values(4,'d');
Query OK, 1 row affected (0.03 sec)
在后端数据库查看差异
db01 [TESTDB]>select * from t4;
+----+------+
| id | name |
+----+------+
| 2 | b |
| 4 | d |
| 1 | a |
| 3 | c |
+----+------+
4 rows in set (0.09 sec)
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3307/mysql.sock -e "use taobao;select * from t4;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
| 2 | b |
| 4 | d |
+----+------+
#id号除以节点数余数为0数据写入到3307
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3308/mysql.sock -e "use taobao;select * from t4;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
| 1 | a |
| 3 | c |
+----+------+
#id号除以节点数余数为1数据写入到3308
- 枚举分片(sharding-by-intfile) 生产中用的挺多的
[root@db01 /application/mycat/conf]# cat 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="t3" dataNode="sh1,sh2" rule="auto-sharding-long" />
<table name="t4" dataNode="sh1,sh2" rule="mod-long" />
<table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" /> #添加此行内容。其它不用修改
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "taobao" />
<dataNode name="sh2" dataHost="oldguo2" database= "taobao" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.4.7.51:3307" user="root" password="123">
<readHost host="db2" url="10.4.7.51:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.4.7.52:3307" user="root" password="123">
<readHost host="db4" url="10.4.7.52:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.4.7.51:3308" user="root" password="123">
<readHost host="db2" url="10.4.7.51:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.4.7.52:3308" user="root" password="123">
<readHost host="db4" url="10.4.7.52:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
配置枚举分片规则
<tableRule name="sharding-by-intfile">
<rule>
<!-- <columns>sharding_id</columns> --> #注释点一列
<columns>name</columns> #添加一个name列
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property> #默认只识别数据，不识别字符串，要想识别字符串要加上这么一行
</function>
定义枚举分片策略
[root@db01 /application/mycat/conf]# cat partition-hash-int.txt
bj=0 #bj的号吗存到0号分片
sh=1 #sh的号吗存到1号分片
DEFAULT_NODE=1 #既不是bj的号吗又不是sh的号吗存到1号分片
准备测试环境
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3308/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
重启mycat
[root@db01 /application/mycat/conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
写入数据测试
[root@db01 /application/mycat/conf]# mysql -uroot -p123456 -h10.4.7.51 -P8066
db01 [(none)]>use TESTDB
db01 [TESTDB]>insert into t5(id,name) values(1,'bj');
Query OK, 1 row affected (0.20 sec)
db01 [TESTDB]>insert into t5(id,name) values(2,'sh');
Query OK, 1 row affected (0.11 sec)
db01 [TESTDB]>insert into t5(id,name) values(3,'bj');
Query OK, 1 row affected (0.09 sec)
db01 [TESTDB]>insert into t5(id,name) values(4,'sh');
Query OK, 1 row affected (0.20 sec)
db01 [TESTDB]>insert into t5(id,name) values(5,'tj');
Query OK, 1 row affected (0.10 sec)
db01 [TESTDB]>select * from t5;
+----+------+
| id | name |
+----+------+
| 1 | bj |
| 3 | bj |
| 2 | sh |
| 4 | sh |
| 5 | tj |
+----+------+
5 rows in set (0.09 sec)
对比后端数据库查看差异
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3307/mysql.sock -e "use taobao;select * from t5;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
| 1 | bj |
| 3 | bj |
+----+------+
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3308/mysql.sock -e "use taobao;select * from t5;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
| 2 | sh |
| 4 | sh |
| 5 | tj |
+----+------+
#枚举分片策略成功
- Mycat的全局表
a b c d
join
t
select t1.name ,t.x from t1
join t
select t2.name ,t.x from t2
join t
select t3.name ,t.x from t3
join t
使用场景：
如果你的业务中有些数据类似于数据字典，比如配置文件的配置，
常用业务的配置或者数据量不大很少变动的表，这些表往往不是特别大，
而且大部分的业务场景都会用到，那么这种表适合于Mycat全局表，无须对数据进行切分，
要在所有的分片上保存一份数据即可，Mycat 在Join操作中，业务表与全局表进行Join聚合会优先选择相同分片内的全局表join，
避免跨库Join，在进行数据插入操作时，mycat将把数据分发到全局表对应的所有分片执行，在进行数据读取时候将会随机获取一个节点读取数据。
[root@db01 /application/mycat/conf]# cat 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="t3" dataNode="sh1,sh2" rule="auto-sharding-long" />
<table name="t4" dataNode="sh1,sh2" rule="mod-long" />
<table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" />
<table name="t_area" primaryKey="id" type="global" dataNode="sh1,sh2" /> #添加这么一行，其它不要修改 （分片策略可删可不删）
</schema>
<dataNode name="sh1" dataHost="oldguo1" database= "taobao" />
<dataNode name="sh2" dataHost="oldguo2" database= "taobao" />
<dataHost name="oldguo1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.4.7.51:3307" user="root" password="123">
<readHost host="db2" url="10.4.7.51:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.4.7.52:3307" user="root" password="123">
<readHost host="db4" url="10.4.7.52:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="oldguo2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="10.4.7.51:3308" user="root" password="123">
<readHost host="db2" url="10.4.7.51:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.4.7.52:3308" user="root" password="123">
<readHost host="db4" url="10.4.7.52:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
后端数据准备：
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3307/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3308/mysql.sock -e "use taobao;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"
重启mycat
[root@db01 /application/mycat/conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
测试：
[root@db01 /application/mycat/conf]# mysql -uroot -p123456 -h10.4.7.51 -P8066
db01 [(none)]>use TESTDB
db01 [TESTDB]>insert into t_area(id,name) values(1,'a');
Query OK, 1 row affected (0.19 sec)
db01 [TESTDB]>insert into t_area(id,name) values(2,'b');
Query OK, 1 row affected (0.11 sec)
db01 [TESTDB]>insert into t_area(id,name) values(3,'c');
Query OK, 1 row affected (0.22 sec)
db01 [TESTDB]>insert into t_area(id,name) values(4,'d');
Query OK, 1 row affected (0.13 sec)
db01 [TESTDB]>select * from t_area;
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
4 rows in set (0.04 sec)
后端查看
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3307/mysql.sock -e "select * from taobao.t_area;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
[root@db01 /application/mycat/conf]# mysql -uroot -p123 -S /data/3308/mysql.sock -e "select * from taobao.t_area;"
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+
| id | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
| 4 | d |
+----+------+
