mycat分布式与切片使用（全）

mycat分布式架构

Mycat中间件的使用

场景介绍:随着业务的发展，公司数据达到了TB的时候，注意此处为TB。然后要分库分表，架构如何规划？

实现环境：
MYSQLdb01:10.0.0.51   db10.0.0.52   内存都给它3G

2、MyCAT基础架构准备（所有节点都做）

2.1 环境准备：

两台虚拟机 db01 db02
每台创建四个mysql实例：3307 3308 3309 3310

2.2 删除历史环境：

pkill mysqld
rm -rf /data/330* 
mv /etc/my.cnf /etc/my.cnf.bak

2.3 创建相关目录初始化数据
mkdir /data/33{07..10}/data -p
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/usr/local/mysql57
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/usr/local/mysql57
mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/usr/local/mysql57
mysqld --initialize-insecure  --user=mysql --datadir=/data/3310/data --basedir=/usr/local/mysql57


2.4 准备配置文件和启动脚本
========db01==============
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql57
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=/usr/local/mysql57
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=/usr/local/mysql57
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=/usr/local/mysql57
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=/usr/local/mysql57/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=/usr/local/mysql57/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=/usr/local/mysql57/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=/usr/local/mysql57/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF



========db02执行===============
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql57
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=/usr/local/mysql57
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=/usr/local/mysql57
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=/usr/local/mysql57
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=/usr/local/mysql57/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=/usr/local/mysql57/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=/usr/local/mysql57/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=/usr/local/mysql57/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF


# 2.5 修改权限，启动多实例
chown -R mysql.mysql /data/*
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310

mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"


# 2.6 节点主从规划
#箭头指向谁是主库（介绍不执行）
    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
#2.7 分片规划（介绍不执行）
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
    
    
    
# 2.8 开始配置---->直接复制粘贴配置即可（注意不要装错了机器）
shard1
10.0.0.51:3307 <-----> 10.0.0.52:3307
## db02操作
mysql  -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
mysql  -S /data/3307/mysql.sock -e "grant all  on *.* to root@'10.0.0.%' identified by '123'  with grant option;"

## db01
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', 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"

## db02
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', 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"
10.0.0.51:3309 ------> 10.0.0.51:3307

## db01
mysql  -S /data/3309/mysql.sock  -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', 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"
10.0.0.52:3309 ------> 10.0.0.52:3307

## db02
mysql  -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', 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"
shard2
10.0.0.52:3308 <-----> 10.0.0.51:3308

## db01
mysql  -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'10.0.0.%' identified by '123';"
mysql  -S /data/3308/mysql.sock -e "grant all  on *.* to root@'10.0.0.%' identified by '123'  with grant option;"

## db02
mysql  -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', 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"

## db01
mysql  -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', 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"
10.0.0.52:3310 -----> 10.0.0.52:3308
db02
mysql  -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.52', 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"
10.0.0.51:3310 -----> 10.0.0.51:3308
db01
mysql  -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.51', 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.9 检测主从状态
mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes

#8个yes表示修改成功
[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes
Yes             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@db01 ~]# mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
[root@db01 ~]# mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

注：如果中间出现错误，在每个节点进行执行以下命令
mysql -S /data/3307/mysql.sock -e "stop slave; reset slave all;"
mysql -S /data/3308/mysql.sock -e "stop slave; reset slave all;"
mysql -S /data/3309/mysql.sock -e "stop slave; reset slave all;"
mysql -S /data/3310/mysql.sock -e "stop slave; reset slave all;"

[root@db01 ~]# mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 7     |
+---------------+-------+
[root@db01 ~]# mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 8     |
+---------------+-------+
[root@db01 ~]# mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 9     |
+---------------+-------+
[root@db01 ~]# mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id     | 10    |
+---------------+-------+

配置环境

mycat官网网站：www.mycat.io (软件包在官网下载即可---->地址：http://dl.mycat.io/)

#db01操作
[root@db01 ~]# yum install java -y
[root@db01 ~]# cd /usr/local
[root@db01 local]# rz Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@db01 local]# tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz 
[root@db01 local]# ls
bin      lib      Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz  sbin
etc      lib64    mysql57                                                   share
games    libexec  mysql-5.7.26-linux-glibc2.12-x86_64                       src
include  mycat    mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz

3.5 启动和连接
配置环境变量
[root@db01 ~]#vim /etc/profile
......
export PATH=/usr/local/mycat/bin:$PATH
[root@db01 ~]# source /etc/profile
[root@db01 ~]# mycat start
Starting Mycat-server...
[root@db01 ~]# mysql -uroot -p123456 -h 127.0.0.1 -P8066  #连接mycat

4.配置文件介绍

logs目录:

wrapper.log ---->mycat启动日志
mycat.log ---->mycat详细工作日志

conf目录:
schema.xml
主配置文件（读写分离、高可用、分布式策略定制、节点控制）

server.xml
mycat软件本身相关的配置

rule.xml
分片规则配置文件,记录分片规则列表、使用方法等

5.应用前环境准备
5.1 用户创建及数据库导入
db01:
mysql -S /data/3307/mysql.sock 
mysql> grant all on *.* to root@'10.0.0.%' identified by '123';
mysql> source /root/world.sql

mysql -S /data/3308/mysql.sock 
mysql> grant all on *.* to root@'10.0.0.%' identified by '123';
mysql> source /root/world.sql
[root@db01 ~]#

5.2 配置文件处理
[root@db01 ~]# cd /usr/local/mycat/conf
[root@db01 ~]# mv schema.xml schema.xml.bak
[root@db01 ~]# vim 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.0.0.51:3307" user="root" password="123"> 
                        <readHost host="db2" url="10.0.0.51:3309" user="root" password="123" /> 
        </writeHost> 
        </dataHost>  
</mycat:schema>

6.读写分离结构配置

6. 读写分离结构配置
vim 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.0.0.51:3307" user="root" password="123"> 
                        <readHost host="db2" url="10.0.0.51:3309" user="root" password="123" /> 
        </writeHost> 
        </dataHost>  
</mycat:schema>

[root@db01 conf]# mycat restart
读写分离测试

mysql -uroot -p -h 127.0.0.1 -P8066
select @@server_id;

begin;
select @@server_id;
commit;

总结： 
以上案例实现了1主1从的读写分离功能，写操作落到主库，读操作落到从库.如果主库宕机，从库不能在继续提供服务了。

8. 配置读写分离及高可用

8. 配置读写分离及高可用
[root@db01 conf]# mv schema.xml schema.xml.rw
[root@db01 conf]# vim 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.0.0.51:3307" user="root" password="123"> 
            <readHost host="db2" url="10.0.0.51:3309" user="root" password="123" /> 
    </writeHost> 
    <writeHost host="db3" url="10.0.0.52:3307" user="root" password="123"> 
            <readHost host="db4" url="10.0.0.52:3309" user="root" password="123" /> 
    </writeHost>        
    </dataHost>  
</mycat:schema

真正的 writehost：负责写操作的writehost  
standby  writeHost  ：和readhost一样，只提供读服务
当写节点宕机后，后面跟的readhost也不提供服务，这时候standby的writehost就提供写服务，
后面跟的readhost提供读服

[root@db01 conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...

测试：
mycat restart
mysql -uroot -p123456 -h 127.0.0.1 -P 8066



读写分离测试
mysql -uroot -p -h 127.0.0.1 -P8066
select @@server_id;


begin;
select @@server_id;
commit;

9. 配置中的属性介绍:

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>  监测心跳

10. 垂直分表

10. 垂直分表
mv  schema.xml  schema.xml.ha 
vim 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.0.0.51:3307" user="root" password="123">
            <readHost host="db2" url="10.0.0.51:3309" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="10.0.0.52:3307" user="root" password="123">
            <readHost host="db4" url="10.0.0.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.0.0.51:3308" user="root" password="123">
            <readHost host="db2" url="10.0.0.51:3310" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="10.0.0.52:3308" user="root" password="123">
            <readHost host="db4" url="10.0.0.52:3310" user="root" password="123" />
    </writeHost>
    </dataHost>
</mycat:schema>

创建测试库和表:
mysql -S /data/3307/mysql.sock -e "create database taobao charset utf8;"
mysql -S /data/3308/mysql.sock -e "create database taobao charset utf8;"
mysql -S /data/3307/mysql.sock -e "use taobao;create table user(id int,name varchar(20))";
mysql -S /data/3308/mysql.sock -e "use taobao;create table order_t(id int,name varchar(20))"

重启测试 :
mycat restart
mysql -uroot -p -h 127.0.0.1 -P8066

use  TESTDB ;
mysql> insert into order_t(id,name) values(1,'c');
mysql> insert into order_t(id,name) values(2,'d');
mysql> insert into user(id,name) values(1,'a');
mysql> insert into user(id,name) values(2,'b');


分别登陆后端主节点查看数据
mysql -S /data/3307/mysql.sock -e "select * from taobao.user"
mysql -S /data/3308/mysql.sock -e "select * from taobao.order_t"

11、MyCAT核心特性——分片（水平拆分）

分片：对一个"bigtable"，比如说t3表

(1)行数非常多，800w
(2)访问非常频繁

分片的目的：
（1）将大数据量进行分布存储
（2）提供均衡的访问路由

分片策略：
范围 range
取模 mod
枚举
哈希 hash
时间流水

优化关联查询
全局表
ER分片

12 .范围分片

比如说t3表
(1)行数非常多，2000w（1-1000w:sh1 1000w-2000w:sh2）
(2)访问非常频繁，用户访问较离散

mv schema.xml schema.xml.1  
vim 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.0.0.51:3307" user="root" password="123">
            <readHost host="db2" url="10.0.0.51:3309" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="10.0.0.52:3307" user="root" password="123">
            <readHost host="db4" url="10.0.0.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.0.0.51:3308" user="root" password="123">
            <readHost host="db2" url="10.0.0.51:3310" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="10.0.0.52:3308" user="root" password="123">
            <readHost host="db4" url="10.0.0.52:3310" user="root" password="123" />
    </writeHost>
    </dataHost>
</mycat:schema>
[root@db01 conf]# mycat restart

vim autopartition-long.txt
......
0-10=0
10-20=1

创建测试表：
mysql -S /data/3307/mysql.sock -e "use taobao;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -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
mycat restart
[root@db01 conf]# mysql -uroot -p123456 -h 127.0.0.1 -P 8066
insert into t3(id,name) values(1,'a');
insert into t3(id,name) values(2,'b');
insert into t3(id,name) values(3,'c');
insert into t3(id,name) values(4,'d');
insert into t3(id,name) values(11,'aa');
insert into t3(id,name) values(12,'bb');
insert into t3(id,name) values(13,'cc');
insert into t3(id,name) values(14,'dd');


# 分别登陆后端主节点查看数据
mysql -S /data/3307/mysql.sock -e "select * from taobao.t3"
mysql -S /data/3308/mysql.sock -e "select * from taobao.t3"

分片策略

13、取模分片（mod-long）

取模：数学算法，任何数字和他取模，得到的都是0~N-1 1/3=余1 2/3=余2 3/3=1

10 / 2 = yu0 -------->0号分片

业务场景：

1—1000w

1000-2000w

取余分片方式：分片键（一个列）与节点数量进行取余，得到余数，将数据写入对应节点
[root@db01 conf]# vim schema.xml
......#修改此行内容
<table name="t4" dataNode="sh1,sh2" rule="mod-long" />
......
[root@db01 conf]# vim rule.xml
        </function>
        <function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>   #将此处的3改为2
        </function>



#创建测试表：
[root@db01 conf]# mysql -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 conf]# mysql -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 conf]# source /etc/profile
[root@db01 conf]# mycat restart/start
测试： 
[root@db01 conf]# mysql -uroot -p123456 -h10.0.0.51 -P8066
mysql
use TESTDB
insert into t4(id,name) values(1,'a');
insert into t4(id,name) values(2,'b');
insert into t4(id,name) values(3,'c');
insert into t4(id,name) values(4,'d');

分别登录后端节点查询数据
[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "select * from taobao.t4;"
+----+------+
| id | name |
+----+------+
|  2 | b    |
|  4 | d    |
+----+------+
[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "select * from taobao.t4;"
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  3 | c    |
+----+------+
[root@db01 conf]# 


测试： 
mysql -uroot -p123456 -h10.0.0.51 -P8066

14. 枚举分片

t5 表
id name telnum
1 bj 1212
2 sh 22222
3 bj 3333
4 sh 44444
5 bj 5555

[root@db01 conf]# vim schema.xml
......
				<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" />
......
[root@db01 conf]# vim rule.xml 
<tableRule name="sharding-by-intfile"> 
<rule> <columns>name</columns>   ##修改文件中的名称为name
<algorithm>hash-int</algorithm> 
</rule> 
</tableRule> 

<function name="hash-int" class="org.opencloudb.route.function.PartitionByFileMap"> 
<property name="mapFile">partition-hash-int.txt</property> 
  <property name="type">1</property>     #添加下列两行
                <property name="defaultNode">0</property>
</function>

[root@db01 conf]# vim partition-hash-int.txt      配置： 
bj=0  #北京
sh=1  #上海
DEFAULT_NODE=1


columns 标识将要分片的表字段，algorithm 分片函数， 其中分片函数配置中，mapFile标识配置文件名称

准备测试环境
mysql -S /data/3307/mysql.sock -e "use taobao;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -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 conf]# mycat restart 
[root@db01 conf]# mysql -uroot -p123456 -h10.0.0.51 -P8066

use TESTDB
insert into t5(id,name) values(1,'bj');
insert into t5(id,name) values(2,'sh');
insert into t5(id,name) values(3,'bj');
insert into t5(id,name) values(4,'sh');
insert into t5(id,name) values(5,'tj');
select * from t5;

分别登录后端节点查询数据
mysql -S /data/3307/mysql.sock -e "select * from taobao.t5;"
mysql -S /data/3308/mysql.sock -e "select * from taobao.t5;"

15.mycat全局表（一张表经常被别人join）

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 conf]# vim schema.xml 
......
<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" />
......
后端数据准备
mysql -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);"

mysql -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
mycat restart

测试： 
mysql -uroot -p123456 -h10.0.0.51 -P8066

use TESTDB
insert into t_area(id,name) values(1,'a');
insert into t_area(id,name) values(2,'b');
insert into t_area(id,name) values(3,'c');
insert into t_area(id,name) values(4,'d');

分别登录后端节点查询数据
[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "select * from taobao.t_area;"
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+
[root@db01 conf]# mysql -S /data/3308/mysql.sock -e "select * from taobao.t_area;"
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
+----+------+

16. E-R分片

A
join
B

为了防止跨分片join，可以使用E-R模式
A join B
on a.xx=b.yy
join C
on A.id=C.id

a  
id   name  aid             
1    a     11   
2    b     22 
3    c     33  
4    d     44 
5    e     55 

b  
id   addr  tel
11    bj    110
22    sh    120
33    tj    119
44    cq    112
55    sz    114
a 
2    b     22 
4    d     44  
b
22    sh    120
44    cq    112
a 
1    a     11  
3    c     33  
5    e     55 
b
11    bj    110
33    tj    119
55    sz    114
配置:

配置:

1.[root@db01 conf]# vim schema.xml
......
<table name="a" dataNode="sh1,sh2" rule="mod-long1"> 
       <childTable name="b" joinKey="id" parentKey="aid" /> 
	   
</table>

</schema>
......
2、
1. vim schema.xml
<table name="a" dataNode="sh1,sh2" rule="mod-long1"> 
       <childTable name="b" joinKey="id" parentKey="aid" /> 
</table> 

2. vim rule.xml
# 下列为新增，在类似后面新加！！！
......
        <tableRule name="mod-long1">
                <rule>
                        <columns>id</columns>
                        <algorithm>mod-long1</algorithm>
                </rule>
        </tableRule>
......
        <function name="mod-long1" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>
        </function>
.......

3. 准备数据

mysql -S /data/3307/mysql.sock  -e "use taobao;create table a (id int not null primary key auto_increment,name varchar(20) not null,aid int not null);"
mysql -S /data/3308/mysql.sock  -e "use taobao;create table a (id int not null primary key auto_increment,name varchar(20) not null,aid int not null);"
mysql -S /data/3307/mysql.sock  -e "use taobao;create table b (id int not null ,addr varchar(20) not null,tel int not null);"
mysql -S /data/3308/mysql.sock  -e "use taobao;create table b (id int not null ,addr varchar(20) not null,tel int not null);"

4. 重启mycat 测试： 
mycat restart 
mysql -uroot -p123456 -h10.0.0.51 -P8066
use TESTDB
insert into a(id,name,aid) values(1,'a',11);
insert into a(id,name,aid) values(2,'b',22);
insert into a(id,name,aid) values(3,'c',33);
insert into a(id,name,aid) values(4,'d',44);
insert into a(id,name,aid) values(5,'e',55);


insert into b(id,addr,tel) values(11,'bj',110);
insert into b(id,addr,tel) values(22,'sh',220);
insert into b(id,addr,tel) values(33,'tj',330);
insert into b(id,addr,tel) values(44,'cq',440);
insert into b(id,addr,tel) values(55,'sz',550);

mysql> select * from a;
+----+------+-----+
| id | name | aid |
+----+------+-----+
|  2 | b    |  22 |
|  4 | d    |  44 |
|  1 | a    |  11 |
|  3 | c    |  33 |
|  5 | e    |  55 |
+----+------+-----+
5 rows in set (0.07 sec)

mysql> select * from b;
+----+------+-----+
| id | addr | tel |
+----+------+-----+
| 22 | sh   | 220 |
| 44 | cq   | 440 |
| 11 | bj   | 110 |
| 33 | tj   | 330 |
| 55 | sz   | 550 |
+----+------+-----+
5 rows in set (0.00 sec)


5. 分别登录后端节点查询数据
mysql -S /data/3307/mysql.sock -e "select * from taobao.a;"
mysql -S /data/3307/mysql.sock -e "select * from taobao.b;"

[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "select * from taobao.a;"
+----+------+-----+
| id | name | aid |
+----+------+-----+
|  2 | b    |  22 |
|  4 | d    |  44 |
+----+------+-----+
[root@db01 conf]# mysql -S /data/3307/mysql.sock -e "select * from taobao.b;"
+----+------+-----+
| id | addr | tel |
+----+------+-----+
| 22 | sh   | 220 |
| 44 | cq   | 440 |
+----+------+-----+

练习（自己完成）：

月份分片（9T）每个月分片

HASH分片（CRC32）---->架构图

Mysql连接数被限制为214问题

问题

项目中，由于连接数过多，提示“Too many connections”，需要增加连接数。我在 /etc/my.cnf中修改了

max_connections = 2000
 
但是， 实际连接数一直被限制在 214
mysql> show variables like "max_connections";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 214   |
+-----------------+-------+
1 row in set (0.01 sec)

mysql>

MySQL max_connections 总是 214 。不能设大了？

环境

CentOS 7.1
MySQL 5.6.25

思考

如果我设置连接小于214时，比如 200，那么实际连接数就是 200，也就是说，我的配置文件是没有问题的。

查 MySQL 官方文档，里面说了。

The maximum number of connections MySQL can support depends on the quality of the thread library on a given platform, the amount of RAM available, how much RAM is used for each connection, the workload from each connection, and the desired response time. Linux or Solaris should be able to support at 500 to 1000 simultaneous connections routinely and as many as 10,000 connections if you have many gigabytes of RAM available and the workload from each is low or the response time target undemanding. Windows is limited to (open tables × 2 + open connections) < 2048 due to the Posix compatibility layer used on that platform.
 
Increasing open-files-limit may be necessary. Also see Section 2.5, “Installing MySQL on Linux”, for how to raise the operating system limit on how many handles can be used by MySQL.

大概意思是 MySQL 能够支持的最大连接数量受限于操作系统,必要时可以增大 open-files-limit。换言之，连接数与文件打开数有关。

解决

posted @ 2020-01-07 20:04 老王教你学Linux 阅读(401) 评论(0) 编辑收藏举报

刷新页面返回顶部

老王教你学Linux

吃水不忘挖井人，走路不忘领路者！！！文档配视频效果更佳哦：https://edu.51cto.com/lecturer/14390454.html

mycat分布式与切片使用（全）

mycat分布式架构

Mycat中间件的使用

2、MyCAT基础架构准备（所有节点都做）

2.1 环境准备：

配置环境

4.配置文件介绍

6.读写分离结构配置

8. 配置读写分离及高可用

9. 配置中的属性介绍:

10. 垂直分表

11、MyCAT核心特性——分片（水平拆分）

12 .范围分片

分片策略

13、取模分片（mod-long）

14. 枚举分片

15.mycat全局表（一张表经常被别人join）

16. E-R分片

Mysql连接数被限制为214问题

问题

环境

思考

公告

老王教你学Linux

吃水不忘挖井人，走路不忘领路者！！！ 文档配视频效果更佳哦：https://edu.51cto.com/lecturer/14390454.html

mycat分布式与切片使用（全）

mycat分布式架构

Mycat中间件的使用

2、MyCAT基础架构准备（所有节点都做）

2.1 环境准备：

配置环境

4.配置文件介绍

6.读写分离结构配置

8. 配置读写分离及高可用

9. 配置中的属性介绍:

10. 垂直分表

11、MyCAT核心特性——分片（水平拆分）

12 .范围分片

分片策略

13、取模分片（mod-long）

14. 枚举分片

15.mycat全局表（一张表经常被别人join）

16. E-R分片

Mysql连接数被限制为214问题

问题

环境

思考

公告

吃水不忘挖井人，走路不忘领路者！！！文档配视频效果更佳哦：https://edu.51cto.com/lecturer/14390454.html