Mycat分布式架构部署

MySQL分布式架构介绍

1
2
3
1. schema拆分及业务分库
2. 垂直拆分-分库分表
3. 水平拆分-分片

环境准备

192.168.177.200【db1】 192.168.177.201【db2】
3307 3308 3309 3310 Mycat-server 3307 3308 3309 3310

创建实例

删除历史环境

1
2
3
4
5
pkill mysql
rm -rf /web/data/mysql/*
rm -rf /web/logs/mysql/*
mv /etc/my.cnf /etc/my.cnf.bak

创建相关目录并初始化

1
2
3
4
5
6
7
8
9
10
11
12
13
数据目录
mkdir -p /web/data/mysql/33{07..10}
日志目录
mkdir -p /web/logs/mysql/33{07..10}/binlog
touch /web/logs/mysql/33{07..10}/mysql.log
配置文件目录
mkdir -p  /etc/mysql/33{07..10}
初始化
cd /web/soft/mysql
./bin/mysqld --initialize-insecure --user=mysql --basedir=/web/soft/mysql --datadir=/web/data/mysql/3307/data
./bin/mysqld --initialize-insecure --user=mysql --basedir=/web/soft/mysql --datadir=/web/data/mysql/3308/data
./bin/mysqld --initialize-insecure --user=mysql --basedir=/web/soft/mysql --datadir=/web/data/mysql/3309/data
./bin/mysqld --initialize-insecure --user=mysql --basedir=/web/soft/mysql --datadir=/web/data/mysql/3310/data

配置

db1

配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
cat >/etc/mysql/3307/my.cnf<<EOF
[mysqld]
basedir=/web/soft/mysql/
datadir=/web/data/mysql/3307/data
socket=/web/data/mysql/3307/mysql.sock
port=3307
log-error=/web/logs/mysql/3307/mysql.log
log-bin=/web/logs/mysql/3307/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=7
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/etc/mysql/3308/my.cnf<<EOF
[mysqld]
basedir=/web/soft/mysql/
datadir=/web/data/mysql/3308/data
socket=/web/data/mysql/3308/mysql.sock
port=3308
log-error=/web/logs/mysql/3308/mysql.log
log-bin=/web/logs/mysql/3308/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=8
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/etc/mysql/3309/my.cnf<<EOF
[mysqld]
basedir=/web/soft/mysql/
datadir=/web/data/mysql/3309/data
socket=/web/data/mysql/3309/mysql.sock
port=3309
log-error=/web/logs/mysql/3309/mysql.log
log-bin=/web/logs/mysql/3309/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=9
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/etc/mysql/3310/my.cnf<<EOF
[mysqld]
basedir=/web/soft/mysql/
datadir=/web/data/mysql/3310/data
socket=/web/data/mysql/3310/mysql.sock
port=3310
log-error=/web/logs/mysql/3310/mysql.log
log-bin=/web/logs/mysql/3310/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=10
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

启动文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
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=/web/soft/mysql/bin/mysqld --defaults-file=/etc/mysql/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=/web/soft/mysql/bin/mysqld --defaults-file=/etc/mysql/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=/web/soft/mysql/bin/mysqld --defaults-file=/etc/mysql/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=/web/soft/mysql/bin/mysqld --defaults-file=/etc/mysql/3310/my.cnf
LimitNOFILE = 5000
EOF

db2

配置文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
cat >/etc/mysql/3307/my.cnf<<EOF
[mysqld]
basedir=/web/soft/mysql/
datadir=/web/data/mysql/3307/data
socket=/web/data/mysql/3307/mysql.sock
port=3307
log-error=/web/logs/mysql/3307/mysql.log
log-bin=/web/logs/mysql/3307/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=17
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/etc/mysql/3308/my.cnf<<EOF
[mysqld]
basedir=/web/soft/mysql/
datadir=/web/data/mysql/3308/data
socket=/web/data/mysql/3308/mysql.sock
port=3308
log-error=/web/logs/mysql/3308/mysql.log
log-bin=/web/logs/mysql/3308/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=18
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/etc/mysql/3309/my.cnf<<EOF
[mysqld]
basedir=/web/soft/mysql/
datadir=/web/data/mysql/3309/data
socket=/web/data/mysql/3309/mysql.sock
port=3309
log-error=/web/logs/mysql/3309/mysql.log
log-bin=/web/logs/mysql/3309/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=19
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/etc/mysql/3310/my.cnf<<EOF
[mysqld]
basedir=/web/soft/mysql/
datadir=/web/data/mysql/3310/data
socket=/web/data/mysql/3310/mysql.sock
port=3310
log-error=/web/logs/mysql/3310/mysql.log
log-bin=/web/logs/mysql/3310/binlog/mysql-bin
binlog_format=row
skip-name-resolve
server-id=20
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

启动文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
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=/web/soft/mysql/bin/mysqld --defaults-file=/etc/mysql/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=/web/soft/mysql/bin/mysqld --defaults-file=/etc/mysql/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=/web/soft/mysql/bin/mysqld --defaults-file=/etc/mysql/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=/web/soft/mysql/bin/mysqld --defaults-file=/etc/mysql/3310/my.cnf
LimitNOFILE = 5000
EOF

修改权限，启动多实例

1
2
3
4
5
6
7
8
9
10
11
12
chown -R mysql.mysql /web/data/mysql/*
chown -R mysql.mysql /web/logs/mysql/*
systemctl daemon-reload
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310

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

节点规划

1
2
3
4
5
6
7
8
箭头指向谁是主库
192.168.177.200:3307    <----->    192.168.177.201:3307
192.168.177.200:3309    ------>    192.168.177.200:3307
192.168.177.201:3309    ------>    192.168.177.201:3307

192.168.177.201:3308    <----->    192.168.177.200:3308
192.168.177.201:3310    ------>    192.168.177.201:3308
192.168.177.200:3310    ------>    192.168.177.200:3308

分片规划

1
2
3
4
5
6
7
8
9
10
shard1：
    Master：192.168.177.200:3307
    slave1：192.168.177.200:3309
    Standby Master：192.168.177.201:3307
    slave2：192.168.177.201:3309
shard2：
    Master：192.168.177.201:3308
    slave1：192.168.177.201:3310
    Standby Master：192.168.177.200:3308
    slave2：192.168.177.200:3310

配置

shard1

192.168.177.200:3307 <—–> 192.168.177.201:3307

db2

1
2
3
4
5
6
7
mysql -S /web/data/mysql/3307/mysql.sock -e "  GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.177.%' IDENTIFIED BY '123456';"
mysql -S /web/data/mysql/3307/mysql.sock -e "grant all  on *.* to root@'192.168.177.%' identified by '123456'  with grant option;"
mysql -S /web/data/mysql/3307/mysql.sock -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.177.%' IDENTIFIED BY '123456' WITH GRANT OPTION;"

mysql -S /web/data/mysql/3307/mysql.sock -e "  FLUSH PRIVILEGES;"
查看
mysql -S /web/data/mysql/3307/mysql.sock -e "SELECT user, host FROM mysql.user;"

db1

1
2
3
mysql  -S /web/data/mysql/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.177.201',MASTER_PORT=3307,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;"
mysql  -S /web/data/mysql/3307/mysql.sock -e "start slave;"
mysql  -S /web/data/mysql/3307/mysql.sock -e "show slave status\G"

db2

1
2
3
mysql  -S /web/data/mysql/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.177.200',MASTER_PORT=3307,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;"
mysql  -S /web/data/mysql/3307/mysql.sock -e "start slave;"
mysql  -S /web/data/mysql/3307/mysql.sock -e "show slave status\G"

192.168.177.200:3309 ——> 192.168.177.200:3307

db1

1
2
3
mysql  -S /web/data/mysql/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.177.200',MASTER_PORT=3307,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;"
mysql  -S /web/data/mysql/3309/mysql.sock  -e "start slave;"
mysql  -S /web/data/mysql/3309/mysql.sock  -e "show slave status\G"

192.168.177.201:3309 ——> 192.168.177.201:3307

db2

1
2
3
mysql  -S /web/data/mysql/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.177.201',MASTER_PORT=3307,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;"
mysql  -S /web/data/mysql/3309/mysql.sock -e "start slave;"
mysql  -S /web/data/mysql/3309/mysql.sock -e "show slave status\G"

shard2

192.168.177.201:3308 <—–> 192.168.177.200:3308

db1

1
2
3
4
5
6
7
mysql -S /web/data/mysql/3308/mysql.sock -e "  GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.177.%' IDENTIFIED BY '123456';"
mysql -S /web/data/mysql/3308/mysql.sock -e "grant all  on *.* to 'root'@'192.168.177.%' identified by '123456'  with grant option;"
mysql -S /web/data/mysql/3308/mysql.sock -e "GRANT ALL PRIVILEGES ON *.* TO 'root'@'192.168.177.%' IDENTIFIED BY '123456' WITH GRANT OPTION;"

mysql -S /web/data/mysql/3308/mysql.sock -e " FLUSH PRIVILEGES;"
查看
mysql -S /web/data/mysql/3308/mysql.sock -e "SELECT user, host FROM mysql.user;"

db2

1
2
3
mysql  -S /web/data/mysql/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.177.200',MASTER_PORT=3308,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;"
mysql  -S /web/data/mysql/3308/mysql.sock -e "start slave;"
mysql  -S /web/data/mysql/3308/mysql.sock -e "show slave status\G"

db1

1
2
3
mysql  -S /web/data/mysql/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.177.201',MASTER_PORT=3308,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;"
mysql  -S /web/data/mysql/3308/mysql.sock -e "start slave;"
mysql  -S /web/data/mysql/3308/mysql.sock -e "show slave status\G"

192.168.177.201:3310 —–> 192.168.177.201:3308

db2

1
2
3
mysql  -S /web/data/mysql/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.177.201',MASTER_PORT=3308,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;"
mysql  -S /web/data/mysql/3310/mysql.sock -e "start slave;"
mysql  -S /web/data/mysql/3310/mysql.sock -e "show slave status\G"

192.168.177.200:3310 —–> 192.168.177.200:3308

db1

1
2
3
mysql  -S /web/data/mysql/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.177.200',MASTER_PORT=3308,MASTER_USER='repl',MASTER_PASSWORD='123456',MASTER_AUTO_POSITION=1;"
mysql  -S /web/data/mysql/3310/mysql.sock -e "start slave;"
mysql  -S /web/data/mysql/3310/mysql.sock -e "show slave status\G"

检测主从状态

1
2
3
4
5
6
7
8
9
10
mysql -S /web/data/mysql/3307/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /web/data/mysql/3308/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /web/data/mysql/3309/mysql.sock -e "show slave status\G"|grep Yes
mysql -S /web/data/mysql/3310/mysql.sock -e "show slave status\G"|grep Yes

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

Mycat安装

安装运行环境

192.168.177.200【bd1】
Mycat-server 
1
2
yum install -y java
https://www.oracle.com/webapps/redirect/signon?nexturl=https://download.oracle.com/otn/java/jdk/8u431-b10/0d8f12bc927a4e2c9f8568ca567db4ee/jdk-8u431-linux-x64.tar.gz

下载Mycat软件包 解压

1
2
3
http://dl.mycat.io/
https://taoziax.lanzoub.com/iVKCo2d49xfc               密码:9uh9
tar -xf Mycat-server-1.6.7.5-release-20200422133810-linux.tar.gz

目录结构

1
2
ll
bin  catlet  conf  lib  logs  version.txt

配置变量 启动和连接

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
配置环境变量
vim /etc/profile
export PATH=/web/soft/mycat/bin:$PATH
source /etc/profile

创建日志目录
mkdir -p /web/soft/mycat/logs/

启动
mycat start   {stop restart status pause(暂停)}
服务端口
netstat -lnp |grep 8066        
tcp6       0      0 :::8066                 :::*                    LISTEN      6878/java   
管理端口
netstat -lnp |grep 9066
tcp6       0      0 :::9066                 :::*                    LISTEN      6878/java

连接mycat：
mysql -uroot -p123456 -h 127.0.0.1 -P8066

配置文件介绍

1
2
3
4
5
6
7
8
logs目录:
wrapper.log           mycat启动日志
mycat.log             mycat详细工作日志

conf目录:
schema.xml             主配置文件(读写分离、高可用、分布式策略定制、节点控制)
server.xml             mycat软件本身相关的配置(逻辑库、用户名、密码)
rule.xml               分片规则配置文件,记录分片规则列表、使用方法等

应用环境准备

创建用户及数据库导入

1
2
3
4
5
db1:
mysql -S /web/data/mysql/3307/mysql.sock 
source /root/amt.sql;
mysql -S /web/data/mysql/3308/mysql.sock 
source /root/amt.sql;

读写分离配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
cd /web/soft/mycat/conf/
mv schema.xml schema.xml.bak
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="dn1"> 
</schema>  
    <dataNode name="dn1" dataHost="localhost1" database= "amt" />  
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1"> 
        <heartbeat>select user()</heartbeat>  
    <writeHost host="db1" url="192.168.177.200:3307" user="root" password="123456"> 
            <readHost host="db2" url="192.168.177.200:3309" user="root" password="123456" /> 
    </writeHost> 
    </dataHost>  
</mycat:schema>

验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
重启mycat
mycat restart

读写分离测试
mysql -uroot -p123456 -h 127.0.0.1 -P8066
use TESTDB
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+

mysql> begin;select @@server_id;;commit;
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+

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

总结

1
2
3
配置重启后连接数据库 use TESTDB 卡住
1、配置文件后端数据库有误
2、后端数据节点有误

读写分离+高可用配置

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mv schema.xml schema.xml.rw
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="dn1"> 
</schema>  
    <dataNode name="dn1" dataHost="test1" database= "amt" />  
    <dataHost name="test1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1"> 
        <heartbeat>select user()</heartbeat>
    <!-- 主节点 -->
    <writeHost host="db1" url="192.168.177.200:3307" user="root" password="123456"> 
            <readHost host="db2" url="192.168.177.200:3309" user="root" password="123456" /> 
    </writeHost>
    <!-- 备节点 -->
    <writeHost host="db3" url="192.168.177.201:3307" user="root" password="123456"> 
            <readHost host="db4" url="192.168.177.201:3309" user="root" password="123456" /> 
    </writeHost>        
    </dataHost>  
</mycat:schema>

验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
主节点 writehost：负责写操作的writehost  
备节点  writeHost ：和readhost一样，只提供读服务

当写主节点宕机后，主节点后面跟的readhost也不提供服务，这时候备节点的writehost就提供写服务，后面跟的readhost提供读服务

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

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          17 |
+-------------+

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|          19 |
+-------------+

mysql> begin;select @@server_id;;commit;
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+

对db1 3307节点进行关闭和启动,测试读写操作
systemctl stop mysqld3307

配置文件介绍

1
2
3
4
5
6
7
8
9
10
11
逻辑库：schema
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">      </schema>  
数据节点：datanode
<dataNode name="dn1" dataHost="localhost1" database= "amt" />  
数据主机：datahost(读和写)
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql" dbDriver="native" switchType="1"> 
    <heartbeat>select user(1)</heartbeat>  
    <writeHost host="db1" url="192.168.177.200:3307" user="root" password="123456"> 
            <readHost host="db2" url="192.168.177.200:3309" user="root" password="123456" /> 
    </writeHost> 
</dataHost>

balance属性

1
2
3
4
5
负载均衡类型，目前的取值有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属性

1
2
3
4
负载均衡类型，目前的取值有2种： 
1、writeType="0", 所有写操作发送到配置的第一个writeHost，
第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为主，切换记录在配置文件中:dnindex.properties . 
2、writeType=“1”，所有写操作都随机的发送到配置的writeHost，不推荐使用

switchType属性

1
2
3
-1 表示不自动切换 
1 默认值，自动切换 
2 基于MySQL主从同步的状态决定是否切换 ，心跳语句为 show slave status

datahost其他配置

1
2
3
4
5
6
7
<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
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
cp 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="pwd" dataNode="sh2"/>
</schema>
    <dataNode name="sh1" dataHost="test1" database= "test" />
    <dataNode name="sh2" dataHost="test2" database= "test" />
    <dataHost name="test1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.177.200:3307" user="root" password="123456">
            <readHost host="db2" url="192.168.177.200:3309" user="root" password="123456" />
    </writeHost>
    <writeHost host="db3" url="192.168.177.201:3307" user="root" password="123456">
            <readHost host="db4" url="192.168.177.201:3309" user="root" password="123456" />
    </writeHost>
    </dataHost>
    <dataHost name="test2" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.177.200:3308" user="root" password="123456">
            <readHost host="db2" url="192.168.177.200:3310" user="root" password="123456" />
    </writeHost>
    <writeHost host="db3" url="192.168.177.201:3308" user="root" password="123456">
            <readHost host="db4" url="192.168.177.201:3310" user="root" password="123456" />
    </writeHost>
    </dataHost>
</mycat:schema>

验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
db1创建测试库和表:
mysql -S /web/data/mysql/3307/mysql.sock -e "create database test charset utf8;"
mysql -S /web/data/mysql/3307/mysql.sock -e "use test;create table user(id int,name varchar(20));"
mysql -S /web/data/mysql/3308/mysql.sock -e "create database test charset utf8;"
mysql -S /web/data/mysql/3308/mysql.sock -e "use test;create table pwd(id int,name varchar(20));"

重启
mycat restart 

mysql -uroot -p -h 127.0.0.1 -P8066

mysql> use TESTDB
Database changed
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| pwd            |
| user           |
+----------------+

核心特性——分片(水平拆分)

介绍

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
分片：对一个"bigtable"，比如说t3表
(1)行数非常多，800w
(2)访问非常频繁

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

分片策略：
范围 range  800w  1-400w  400w01-800w
取模 mod    取余数
枚举 
哈希 hash 
时间 流水

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

rule.xml介绍

1
2
3
4
5
6
7
8
9
tableRule标签：
name：属性指定唯一的名字，用于标识不同的表规则。内嵌的 rule 标签则指定对物理表中的哪一列进行拆分和使用什么路由算法。
columns：内指定要拆分的列名字。
algorithm：使用 function 标签中的 name 属性。连接表规则和具体路由算法。多个表规则可以连接到同一个路由算法上。table标签内使用。让逻辑表使用这个规则进行分片。

function标签
name：指定算法的名字。
class ：制定路由算法具体的类名字。
property： 为具体算法需要用到的一些属性。

范围分片

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
场景：t3表
(1)行数非常多，2000w（1-1000w:sh1   1000w01-2000w:sh2）
(2)访问非常频繁，用户访问较离散

cp 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="test1" database= "test" />
    <dataNode name="sh2" dataHost="test2" database= "test" />
    <dataHost name="test1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.177.200:3307" user="root" password="123456">
            <readHost host="db2" url="192.168.177.200:3309" user="root" password="123456" />
    </writeHost>
    <writeHost host="db3" url="192.168.177.201:3307" user="root" password="123456">
            <readHost host="db4" url="192.168.177.201:3309" user="root" password="123456" />
    </writeHost>
    </dataHost>
    <dataHost name="test2" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.177.200:3308" user="root" password="123456">
            <readHost host="db2" url="192.168.177.200:3310" user="root" password="123456" />
    </writeHost>
    <writeHost host="db3" url="192.168.177.201:3308" user="root" password="123456">
            <readHost host="db4" url="192.168.177.201:3310" user="root" password="123456" />
    </writeHost>
    </dataHost>
</mycat:schema>
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
查看
vim rule.xml
<tableRule name="auto-sharding-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>rang-long</algorithm>
                </rule>             
<function name="rang-long"
    class="io.mycat.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
</function>
    
vim autopartition-long.txt
0-10=0
11-20=1

验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
创建表：
mysql -S /web/data/mysql/3307/mysql.sock -e "use test;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /web/data/mysql/3308/mysql.sock  -e "use test;create table t3 (id int not null primary key auto_increment,name varchar(20) not null);"

测试：
重启mycat
mycat restart
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> select * from  t3;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
|  4 | d    |
| 11 | aa   |
| 12 | bb   |
| 13 | cc   |
| 14 | dd   |
+----+------+
8 rows in set (0.06 sec)

查询
mysql -S /web/data/mysql/3307/mysql.sock -e "use test;select * from t3;" 
mysql -S /web/data/mysql/3308/mysql.sock -e "use test;select * from t3;"

取模分片(mod-long)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
场景：t4表
取余分片方式：分片键（一个列）与节点数量进行取余，得到余数，将数据写入对应节点
vim schema.xml
<!-- 修改 -->
<table name="t4" dataNode="sh1,sh2" rule="mod-long" />

vim rule.xml
<tableRule name="mod-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>mod-long</algorithm>
                </rule>
        </tableRule>
<!-- 修改 -->
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
        <!-- 数据节点的数量 -->
        <property name="count">2</property>
</function>

验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
创建表：
mysql -S /web/data/mysql/3307/mysql.sock -e "use test;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /web/data/mysql/3308/mysql.sock -e "use test;create table t4 (id int not null primary key auto_increment,name varchar(20) not null);"

重启mycat 
mycat restart 

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

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');

查询
mysql -S /web/data/mysql/3307/mysql.sock -e "use test;select * from t4;" 
mysql -S /web/data/mysql/3308/mysql.sock -e "use test;select * from t4;"

枚举分片

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
场景：t5表
id  name telnum
1   成都   111
2   上海   222
3   成都   333
4   上海   444
5   成都   555

vim schema.xml
<!-- 修改 -->
<table name="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" />

vim rule.xml
<!-- 修改为使用的分片列名 -->
<tableRule name="sharding-by-intfile">
        <rule>
                <columns>name</columns>
                <algorithm>hash-int</algorithm>
        </rule>
</tableRule>

<function name="hash-int" class="io.mycat.route.function.PartitionByFileMap">
        <property name="mapFile">partition-hash-int.txt</property>
        <!-- 支持中文 默认值为0，0表示Integer，非零表示String。  -->
        <property name="type">1</property>
        <!-- 小于0表示不设置默认节点，枚举分片时，如果碰到不识别的枚举值，就让它路由到默认节点。  -->
        <property name="defaultNode">1</property>
</function> 

partition-hash-int.txt 配置： 
成都=0 
上海=1

设置默认节点
DEFAULT_NODE=1 
columns 标识将要分片的表字段，algorithm 分片函数， 其中分片函数配置中，mapFile标识配置文件名称

验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
创建表：
mysql -S /web/data/mysql/3307/mysql.sock -e "use test;create table t5 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /web/data/mysql/3308/mysql.sock -e "use test;create table t5 (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 t5(id,name) values(1,'成都');
insert into t5(id,name) values(2,'上海');
insert into t5(id,name) values(3,'成都');
insert into t5(id,name) values(4,'上海');
insert into t5(id,name) values(5,'成都');
insert into t5(id,name) values(6,'深圳');

查询
mysql -S /web/data/mysql/3307/mysql.sock -e "use test;select * from t5;" 
mysql -S /web/data/mysql/3308/mysql.sock -e "use test;select * from t5;"

Mycat全局表

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
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将把数据分发到全局表对应的所有分片执行，在进行数据读取时候将会随机获取一个节点读取数据。 

vim schema.xml
<!-- 修改 -->
<table name="t_area" primaryKey="id"  type="global" dataNode="sh1,sh2" />

验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
创建表
mysql -S /web/data/mysql/3307/mysql.sock -e "use test;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /web/data/mysql/3308/mysql.sock -e "use test;create table t_area (id int not null primary key auto_increment,name varchar(20) not null);"

重启mycat 
mycat restart 

mysql -uroot -p123456 -h192.168.177.200 -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');

查询
mysql -S /web/data/mysql/3307/mysql.sock -e "use test;select * from t_area;"
mysql -S /web/data/mysql/3308/mysql.sock -e "use test;select * from t_area;"

E-R分片

1
2
3
4
5
6
7
8
9
10
11
12
13
14
A 
join 
B  
为了防止跨分片join，可以使用E-R模式
A   join   B
on  a.xx=b.yy
join C
on A.id=C.id

vim schema.xml
<!-- 修改 -->
<table name="user" dataNode="sh1,sh2" rule="mod-long"> 
       <childTable name="age" joinKey="age_id" parentKey="id" /> 
</table>

验证

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
创建表
mysql -S /web/data/mysql/3307/mysql.sock -e "use test;CREATE TABLE user (id BIGINT PRIMARY KEY,name VARCHAR(50),age_id BIGINT);"
mysql -S /web/data/mysql/3307/mysql.sock -e "use test;CREATE TABLE age (age_id BIGINT PRIMARY KEY,age_value INT);"
mysql -S /web/data/mysql/3308/mysql.sock -e "use test;CREATE TABLE user (id BIGINT PRIMARY KEY,name VARCHAR(50),age_id BIGINT);"
mysql -S /web/data/mysql/3308/mysql.sock -e "use test;CREATE TABLE age (age_id BIGINT PRIMARY KEY,age_value INT);"

重启mycat 
mycat restart 

插入数据
mysql -uroot -p123456 -h192.168.177.200 -P8066
use TESTDB
INSERT INTO user (id, name, age_id) VALUES (2, 'A', 20);
INSERT INTO user (id, name, age_id) VALUES (4, 'B', 40);
INSERT INTO user (id, name, age_id) VALUES (6, 'C', 60);
INSERT INTO age (age_id, age_value) VALUES (20, 20);
INSERT INTO age (age_id, age_value) VALUES (40, 40);
INSERT INTO age (age_id, age_value) VALUES (60, 60);

验证
SELECT user.age_id, user.id, user.name, age.age_value FROM user LEFT JOIN age ON user.age_id = age.age_id;
+--------+----+------+-----------+
| age_id | id | name | age_value |
+--------+----+------+-----------+
|     20 |  2 | B    |        30 |
|     10 |  1 | A    |        25 |
|     30 |  3 | C    |        35 |
+--------+----+------+-----------+
MySQL
#Linux #MySQL #Mycat
Mycat分布式架构部署
https://yftxhy.site/2024/10/25/Mycat分布式架构部署/
作者
Taozi
发布于
2024年10月25日
许可协议