一、架构设计
1、主从规划
数据库 | mysql-1 | mysql-2 |
---|---|---|
实例 | 3307 3308 3309 3310 | 3307 3308 3309 3310 |
互为主从 | mysql-1:3307←→mysql-2:3307 | mysql-1:3308←→mysql-2:3308 |
单向主从 | mysql-1:3309—→mysql-1:3307 | mysql-2:3309—→mysql-2:3307 |
单向主从 | mysql-1:3310—→mysql-1:3308 | mysql-2:3310—→mysql-2:3308 |
2、分片规划
shard1 | shard2 | |
---|---|---|
Master | mysql-1:3307 | mysql-2:3308 |
slave1 | mysql-1:3309 | mysql-2:3310 |
Standby Master | mysql-2:3307 | mysql-1:3308 |
slave2 | mysql-2:3309 | mysql-1:3310 |
二、环境搭建
1、准备实例
1、安装MySQL
# mkdir -p /data/33{07..10}/data
# 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、准备mysql-1配置文件
# 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
3、准备mysql-2配置文件
# 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
4、修改权限,启动多实例
# chown -R mysql.mysql /data/*
# systemctl start mysqld3307
# systemctl start mysqld3308
# systemctl start mysqld3309
# systemctl start mysqld3310
# netstat -anpt | grep mysql
tcp6 0 0 :::3307 :::* LISTEN 17583/mysqld
tcp6 0 0 :::3308 :::* LISTEN 17590/mysqld
tcp6 0 0 :::3309 :::* LISTEN 17597/mysqld
tcp6 0 0 :::3310 :::* LISTEN 17685/mysqld
# mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 7 |
+---------------+-------+
# mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 8 |
+---------------+-------+
# mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 9 |
+---------------+-------+
# mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 10 |
+---------------+-------+
# mysql -S /data/3307/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 17 |
+---------------+-------+
# mysql -S /data/3308/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 18 |
+---------------+-------+
# mysql -S /data/3309/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 19 |
+---------------+-------+
# mysql -S /data/3310/mysql.sock -e "show variables like 'server_id'"
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| server_id | 20 |
+---------------+-------+
2、配置主从
1、配置互为主从
1、mysql-1:3307←→mysql-2:3307
# mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'192.168.1.%' identified by '123';"
# mysql -S /data/3307/mysql.sock -e "grant all on *.* to root@'192.168.1.%' identified by '123' with grant option;"
# mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.1.20', 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"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.20
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1045
Relay_Log_File: mysql-1-relay-bin.000002
Relay_Log_Pos: 1258
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.1.10', 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"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1045
Relay_Log_File: mysql-2-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2、mysql-1:3308←→mysql-2:3308
# mysql -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'192.168.1.%' identified by '123';"
# mysql -S /data/3308/mysql.sock -e "grant all on *.* to root@'192.168.1.%' identified by '123' with grant option;"
# mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.1.10', 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"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: repl
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 755
Relay_Log_File: mysql-2-relay-bin.000002
Relay_Log_Pos: 968
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.1.20', 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"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.20
Master_User: repl
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 755
Relay_Log_File: mysql-1-relay-bin.000002
Relay_Log_Pos: 414
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2、配置单向主从
1、mysql-1:3309—→mysql-1:3307
# mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.1.10', 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"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1045
Relay_Log_File: mysql-1-relay-bin.000002
Relay_Log_Pos: 1258
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
2、mysql-2:3309—→mysql-2:3307
# mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.1.20', 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"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.20
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 1045
Relay_Log_File: mysql-2-relay-bin.000002
Relay_Log_Pos: 1258
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3、mysql-2:3310—→mysql-2:3308
# mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.1.20', 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"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.20
Master_User: repl
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 755
Relay_Log_File: mysql-2-relay-bin.000002
Relay_Log_Pos: 968
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
4、mysql-1:3310—→mysql-1:3308
# mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='192.168.1.10', 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"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.10
Master_User: repl
Master_Port: 3308
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 755
Relay_Log_File: mysql-1-relay-bin.000002
Relay_Log_Pos: 968
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
3、检测主从状态
# mysql -S /data/3307/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# 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 "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# mysql -S /data/3308/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# mysql -S /data/3309/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
# mysql -S /data/3310/mysql.sock -e "show slave status\G"|grep Yes
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
三、MyCAT安装
# yum -y install java
# wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
# tar zxf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /usr/local/
# vim /etc/profile
export PATH=/usr/local/mycat/bin:$PATH
# source /etc/profile
# mycat start
Starting Mycat-server...
# mysql -uroot -p123456 -h 127.0.0.1 -P8066
mysql>
四、Mycat基础应用
1、实现1主1从读写分离
# cd /usr/local/mycat/conf
# mv schema.xml schema.xml.bak
# vim schema.xml
<!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="mysql-1" database= "world" />
#后端主机定义
<dataHost name="mysql-1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.1.10:3307" user="root" password="123">
<readHost host="db2" url="192.168.1.10:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
# mycat start
Starting Mycat-server...
Mycat-server is already running.
# mysql -uroot -p123456 -h 192.168.1.10 -P 8066
Your MySQL connection id is 2
Server version: 5.6.29-mycat-1.6.7.4-release-20200105164103 MyCat Server (OpenCloudDB)
mysql> select @@server_id; # 测试读
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
1 row in set (0.05 sec)
mysql> begin;select @@server_id;commit; # 测试写
Query OK, 0 rows affected (0.01 sec)
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
属性 | 说明 |
---|---|
schema name | 定义mycat实例中的逻辑库,mycat可以有多个逻辑库,每个逻辑库都有自己的相关配置。可以使用schema标签来划分这些不同的逻辑库 |
dataNode | 该标签用于绑定逻辑库到某个具体的database上 |
dataHost | 定义了具体数据库实例,读写分离配置和心跳语句 |
2、实现高可用+读写分离
# cd /usr/local/mycat/conf/
# vim schema.xml
<!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="mysql-1" database= "world" />
<dataHost name="mysql-1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
#真正的写节点,负责写操作
<writeHost host="db1" url="192.168.1.10:3307" user="root" password="123">
<readHost host="db2" url="192.168.1.10:3309" user="root" password="123" />
</writeHost>
#准备写节点,负责读,当192.168.1.10:3307宕掉,会切换成为真正的写节点
<writeHost host="db3" url="192.168.1.20:3307" user="root" password="123">
<readHost host="db4" url="192.168.1.20:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
取值 | balance(负载均衡类型) | writeType(负载均衡类型) | switchType |
---|---|---|---|
-1 | / | / | 不自动切换 |
0 | 不开启读写分离机制,所有读操作都发送到当前可用的writeHost上 | / | 默认值,自动切换 |
1 | 全部的readHost与standby writeHost参与select语句的负载均衡 | 所有写操作发送到配置的第一个writeHost | 基于MySQL主从同步的状态决定是否切换 ,心跳语句为 show slave status |
2 | 所有读操作都随机的在writeHost、readhost上分发 | 所有写操作都随机的发送到配置的writeHost | / |
属性 | 说明 |
---|---|
maxCon | 最大的并发连接数 |
minCon | mycat在启动之后,会在后端节点上自动开启的连接线程 |
tempReadHostAvailable | 这个一主一从时(1个writehost,1个readhost时),可以开启这个参数,如果2个writehost,2个readhost时 |
<heartbeat>select user()</heartbeat> | 监测心跳 |
五、Mycat高级应用-分布式解决方案
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="user" dataNode="sh1"/>
<table name="order_t" dataNode="sh2"/>
</schema>
<dataNode name="sh1" dataHost="mysql-1" database= "taobao" />
<dataNode name="sh2" dataHost="mysql-2" database= "taobao" />
<dataHost name="mysql-1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.1.10:3307" user="root" password="123">
<readHost host="db2" url="192.168.1.10:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.1.20:3307" user="root" password="123">
<readHost host="db4" url="192.168.1.20:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="mysql-2" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
<heartbeat>select user()</heartbeat>
<writeHost host="db1" url="192.168.1.10:3308" user="root" password="123">
<readHost host="db2" url="192.168.1.10:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="192.168.1.20:3308" user="root" password="123">
<readHost host="db4" url="192.168.1.20:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
2、水平拆分
目的:将大数据量进行分布存储,提供均衡的访问路由
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" />
# vim rule.xml
<tableRule name="auto-sharding-long">
<rule>
<columns>id</columns> # 以id作为数值范围
<algorithm>rang-long</algorithm>
</rule>
<function name="rang-long"
class="io.mycat.route.function.AutoPartitionByLong">
<property name="mapFile">autopartition-long.txt</property>
</function>
# vim autopartition-long.txt
# range start-end ,data node index
# K=1000,M=10000. K=千,M=万
0-500M=0
500M-1000M=1
::: warning
分片规则配置文件autopartition-long.txt中配置的节点要跟dataNode数量对应上,不然可能会报错
:::
2、取模分片
目的:切分规则根据配置中输入的数值n。此种分片规则将数据分成n份,从而将数据均匀的分布于各个节点上
取余分片方式:分片键(一个列)与节点数量进行取余,得到余数,将数据写入对应节点
# 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="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">
<!-- how many data nodes -->
<property name="count">2</property> # 2即为节点数
</function>
3、枚举分片
目的:将符合条件的记录分配到对应节点上,不满足条件的分配到默认节点
# 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="t5" dataNode="sh1,sh2" rule="sharding-by-intfile" />
# vim rule.xml
<tableRule name="sharding-by-intfile">
<rule>
<columns>sharding_id</columns>
<algorithm>hash-int</algorithm>
</rule>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property> # 支持字符串(中文)
<property name="defaultNode">0</property>
</function>
# vim partition-hash-int.txt
10000=0
10010=1
DEFAULT_NODE=1 # 情况之外
参数 | 说明 |
---|---|
columns | 标识将要分片的表字段 |
algorithm | 分片函数 |
mapFile | 标识配置文件名称 |
3、全局表
指在所有节点都保存一份一样的数据
# vim schema.xml
<table name="t6" primaryKey="id" type="global" dataNode="sh1,sh2" />
评论区