avatar
文章
63
标签
44
分类
10
首页
时间轴
标签
分类
娱乐
友情链接
留言板
关于
WF's Blog
首页
时间轴
标签
分类
娱乐
友情链接
留言板
关于
11.分布式架构-MyCAT
|数据库
字数总计:4.3k|阅读时长: 23 分钟
|阅读量:|评论数:

第11节 分布式架构-MyCAT

第1章 MyCAT基础架构图

第2章 MyCAT基础架构准备

2.1 环境准备

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

2.2 删除历史环境

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

2.3 创建相关目录初始化数据

1
2
3
4
5
mkdir /data/33{07..10}/data -p
mysqld --initialize-insecure  --user=mysql --datadir=/data/3307/data --basedir=/usr/local/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3308/data --basedir=/usr/local/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3309/data --basedir=/usr/local/mysql
mysqld --initialize-insecure  --user=mysql --datadir=/data/3310/data --basedir=/usr/local/mysql

2.4 准备配置文件和启动脚本

db01节点

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
========db01==============
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=7
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

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

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

cat >/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3308.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3309.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF
cat >/etc/systemd/system/mysqld3310.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target

[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF

db02节点

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
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
========db02===============
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3307/data
socket=/data/3307/mysql.sock
port=3307
log-error=/data/3307/mysql.log
log_bin=/data/3307/mysql-bin
binlog_format=row
skip-name-resolve
server-id=17
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3308/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3308/data
port=3308
socket=/data/3308/mysql.sock
log-error=/data/3308/mysql.log
log_bin=/data/3308/mysql-bin
binlog_format=row
skip-name-resolve
server-id=18
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF
cat >/data/3309/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3309/data
socket=/data/3309/mysql.sock
port=3309
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=19
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF


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

cat >/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3308.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3308/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3309.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3309/my.cnf
LimitNOFILE = 5000
EOF

cat >/etc/systemd/system/mysqld3310.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/usr/local/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF

2.5 改权限，启动多实例

1
2
3
4
5
6
7
8
9
10
11
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 节点主从规划

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

    10.0.1.52:3308  <----->    10.0.1.51:3308
    10.0.1.52:3310  ----->     10.0.1.52:3308
    10.0.1.51:3310  ----->     10.0.1.51:3308

2.7 分片规划

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

2.8 开始配置

shard1节点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
### shard1
10.0.1.51:3307 <-----> 10.0.1.52:3307
#### db02
mysql  -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'10.0.1.%' identified by '123';"
mysql  -S /data/3307/mysql.sock -e "grant all  on *.* to root@'10.0.1.%' identified by '123'  with grant option;"
#### db01
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.1.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.1.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.1.51:3309 ------> 10.0.1.51:3307
#### db01
mysql  -S /data/3309/mysql.sock  -e "CHANGE MASTER TO MASTER_HOST='10.0.1.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.1.52:3309 ------> 10.0.1.52:3307
#### db02
mysql  -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.1.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节点

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
### shard2
10.0.1.52:3308 <-----> 10.0.1.51:3308
#### db01
mysql  -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'10.0.1.%' identified by '123';"
mysql  -S /data/3308/mysql.sock -e "grant all  on *.* to root@'10.0.1.%' identified by '123'  with grant option;"
#### db02
mysql  -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.1.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.1.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.1.52:3310 -----> 10.0.1.52:3308
#### db02
mysql  -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.1.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.1.51:3310 -----> 10.0.1.51:3308
#### db01
mysql  -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.1.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 检测主从状态

1
2
3
4
5
6
7
8
9
10
11
12
13
2.9 检测主从状态
mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep "Running:"
mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep "Running:"
mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep "Running:"
mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep "Running:"

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
注：如果中间出现错误，在每个节点进行执行以下命令
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;"
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

2.10 MySQL分布式架构介绍

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

2.11 企业代表产品

1
2
3
4
5
6
7
8
9
360 Atlas-Sharding
Alibaba  cobar 
Mycat
TDDL
Heisenberg
Oceanus
Vitess
OneProxy 
DRDS

第3章 MyCAT安装

3.1 预先安装Java运行环境

1
yum -y install java

3.2 下载

1
2
Mycat-server-xxxxx.linux.tar.gz
http://dl.mycat.io/

3.3 解压文件

1
tar xf Mycat-server-1.6.7.3-release-20190927161129-linux.tar.gz

3.4 软件目录结构

1
2
[root@db01 /usr/local/mycat]# ls
bin  catlet  conf  lib  logs  version.txt

3.5 启动和连接

1
2
3
4
5
6
7
8
9
10
##配置环境变量
vim /etc/profile
export PATH=/usr/local/mycat/bin:$PATH
source /etc/profile

##启动
mycat start

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

3.6 配置文件介绍

1
2
3
4
5
6
7
8
9
10
##logs目录:
wrapper.log       ---->mycat启动日志
mycat.log         ---->mycat详细工作日志
##conf目录:
schema.xml      
##主配置文件（读写分离、高可用、分布式策略定制、节点控制）
server.xml
##mycat软件本身相关的配置
rule.xml 
##分片规则配置文件,记录分片规则列表、使用方法等

第4章 应用前环境准备

4.1 用户创建及数据库导入

1
2
3
4
5
6
7
8
db01:
mysql -S /data/3307/mysql.sock 
grant all on *.* to root@'10.0.1.%' identified by '123';
source /root/world.sql

mysql -S /data/3308/mysql.sock 
grant all on *.* to root@'10.0.1.%' identified by '123';
source /root/world.sql

4.2 配置文件处理

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
[root@db01 ~]# cd /usr/local/mycat/conf
[root@db01 ~]# mv schema.xml schema.xml.bak
[root@db01 /usr/local/mycat/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="dn1"> 
</schema>  
    <dataNode name="dn1" dataHost="localhost1" database= "world" />  
    <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="10.0.1.51:3307" user="root" password="123"> 
            <readHost host="db2" url="10.0.1.51:3309" user="root" password="123" /> 
    </writeHost> 
    </dataHost>  
</mycat:schema>

第5章 配置文件简单介绍

5.1 逻辑库：schema

1
2
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> 
</schema>

5.2 数据节点:datanode

1
<dataNode name="dn1" dataHost="localhost1" database= "world" />

5.3 数据主机：datahost(w和r)

1
2
3
4
5
6
<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="10.0.1.51:3307" user="root" password="123"> 
            <readHost host="db2" url="10.0.1.51:3309" user="root" password="123" /> 
    </writeHost> 
    </dataHost>

第6章 读写分离测试

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
##重启mycat
[root@db01 /usr/local/mycat/conf]# mycat restart
Stopping Mycat-server...
Stopped Mycat-server.
Starting Mycat-server...
##读写分离测试
mysql -uroot -p123456 -h 127.0.0.1 -P8066
mysql> show databases;
mysql> use TESTDB
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           9 |
+-------------+
1 row in set (0.01 sec)

mysql> begin;select @@server_id;commit;
Query OK, 0 rows affected (0.00 sec)

+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

第7章 配置读写分离及高可用

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
[root@db01 /usr/local/mycat/conf]# mv schema.xml schema.xml.rw
[root@db01 /usr/local/mycat/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="wufei1" database= "world" />  
    <dataHost name="wufei1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1"> 
        <heartbeat>select user()</heartbeat>  
    <writeHost host="db1" url="10.0.1.51:3307" user="root" password="123">
            <readHost host="db2" url="10.0.1.51:3309" user="root" password="123" /> 
    </writeHost> 
    <writeHost host="db3" url="10.0.1.52:3307" user="root" password="123"> 
            <readHost host="db4" url="10.0.1.52:3309" user="root" password="123" />
    </writeHost>        
    </dataHost>  
</mycat:schema>

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

真正的 writehost：负责写操作的writehost  
standby  writeHost  ：和readhost一样，只提供读服务

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

##测试：
mysql -uroot -p123456 -h 127.0.0.1 -P 8066
show variables like 'server_id';
##读写分离测试
 mysql -uroot -p -h 127.0.0.1 -P8066
 use TESTDB
 select @@server_id;    -----> 9  17  19
 begin;select @@server_id;commit;   -----> 7
 ##对db01 3307节点进行关闭和启动,测试读写操作
systemctl stop mysqld3307
mysql -uroot -p123456 -h 127.0.0.1 -P8066
mysql> select @@server_id;  ----> 19
mysql>  begin;select @@server_id;commit;  ----> 17

systemctl start mysqld3307
mysql -uroot -p123456 -h 127.0.0.1 -P8066
mysql> select @@server_id;  ----> 7 9 19
mysql>  begin;select @@server_id;commit;  ----> 17

第8章 配置中的属性介绍

balance

1
2
3
4
5
6
## balance属性
负载均衡类型，目前的取值有3种： 
 balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost上。 
 balance="1"，全部的readHost与standby writeHost参与select语句的负载均衡，简单的说，
 当双主双从模式(M1->S1，M2->S2，并且M1与 M2互为主备)，正常情况下，M2,S1,S2都参与select语句的负载均衡。 
 balance="2"，所有读操作都随机的在writeHost、readhost上分发。

writeType

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

switchType

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

datahost其他配置

1
2
3
4
5
6
7
8
9
## 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>  监测心跳

第9章 垂直分表

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
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="wufei1" database= "taobao" />
    <dataNode name="sh2" dataHost="wufei2" database= "taobao" />
    <dataHost name="wufei1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="10.0.1.51:3307" user="root" password="123">
            <readHost host="db2" url="10.0.1.51:3309" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="10.0.1.52:3307" user="root" password="123">
            <readHost host="db4" url="10.0.1.52:3309" user="root" password="123" />
    </writeHost>
    </dataHost>
    <dataHost name="wufei2" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="10.0.1.51:3308" user="root" password="123">
            <readHost host="db2" url="10.0.1.51:3310" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="10.0.1.52:3308" user="root" password="123">
            <readHost host="db4" url="10.0.1.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测试
mycat restart
mysql -uroot -p123456 -h 127.0.0.1 -P8066
mysql> use TESTDB
mysql> insert into order_t values(1,'zs');
mysql> insert into order_t values(2,'ls');
mysql> insert into user values(1,'zs');
mysql> insert into user values(2,'ls');
mysql>  select * from user;
mysql>  select * from order_t;

第10章 MyCAT核心特性——分片（水平拆分）

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
分片：对一个"bigtable"，比如说t3表

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

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

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

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

第11章 范围分片

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

范围分片实现

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
64
65
66
##(1)准备配置文件 
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="wufei1" database= "taobao" />
    <dataNode name="sh2" dataHost="wufei2" database= "taobao" />
    <dataHost name="wufei1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="10.0.1.51:3307" user="root" password="123">
            <readHost host="db2" url="10.0.1.51:3309" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="10.0.1.52:3307" user="root" password="123">
            <readHost host="db4" url="10.0.1.52:3309" user="root" password="123" />
    </writeHost>
    </dataHost>
    <dataHost name="wufei2" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="10.0.1.51:3308" user="root" password="123">
            <readHost host="db2" url="10.0.1.51:3310" user="root" password="123" />
    </writeHost>
    <writeHost host="db3" url="10.0.1.52:3308" user="root" password="123">
            <readHost host="db4" url="10.0.1.52:3310" user="root" password="123" />
    </writeHost>
    </dataHost>
</mycat:schema>

##(2)查看分片策略的使用方法:
vim rule.xml

<tableRule name="auto-sharding-long">
                <rule>
                        <columns>id</columns>
                        <algorithm>rang-long</algorithm>
                </rule>    
##(3) 函数使用方法			
<function name="rang-long"
    class="io.mycat.route.function.AutoPartitionByLong">
    <property name="mapFile">autopartition-long.txt</property>
</function>

##(4) 函数传参   
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
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');

第12章 取模分片（mod-long

文章作者: Wu Fei
文章链接: http://linuxwf.com/2020/04/15/11-%E5%88%86%E5%B8%83%E5%BC%8F%E6%9E%B6%E6%9E%84-MyCAT/
版权声明: 本博客所有文章除特别声明外，均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 WF's Blog！
mysqlmycat
打赏
  • 微信
    微信
  • 支付宝
    支付宝
相关推荐
2020-04-15
1.数据库介绍和MySQL安装
2020-04-15
10.读写分离架构-Atlas
2020-04-15
2.MySQL体系结构及管理
2020-04-15
3.SQL基础
2020-04-15
4.索引及执行计划
2020-04-15
5.存储引擎
评论
繁