MyCat基础架构图
image.png
箭头指向谁，谁是主库
11 的 3307 和 12的 3307 互为主从
11 的 3309 为 11的 3307的从库
12 的 3309 为 12的 3307的从库
3308和3307一样
MyCat搭建过程
环境准备
| 主机名 | IP | MySQL实例 |
|---|---|---|
| db01 | 10.0.0.11 | 3307 |
| db01 | 10.0.0.11 | 3309 |
| db01 | 10.0.0.11 | 3308 |
| db01 | 10.0.0.11 | 3310 |
| db02 | 10.0.0.12 | 3307 |
| db02 | 10.0.0.12 | 3309 |
| db02 | 10.0.0.12 | 3308 |
| db02 | 10.0.0.12 | 3310 |
初始化目录
mkdir /data/33{07..10}/data -p
mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/app/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308/data --basedir=/app/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309/data --basedir=/app/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3310/data --basedir=/app/mysql
db01
========db01==============
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/app/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=/app/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=/app/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=/app/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=/app/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=/app/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=/app/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=/app/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF
db02
========db02===============
cat >/data/3307/my.cnf<<EOF
[mysqld]
basedir=/app/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=/app/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=/app/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=/app/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=/app/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=/app/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=/app/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=/app/mysql/bin/mysqld --defaults-file=/data/3310/my.cnf
LimitNOFILE = 5000
EOF
启动数据库
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'"
分片规划
shard1：
Master：10.0.0.11:3307
slave1：10.0.0.11:3309
Standby Master：10.0.0.12:3307
slave2：10.0.0.12:3309
shard2：
Master：10.0.0.12:3308
slave1：10.0.0.12:3310
Standby Master：10.0.0.11:3308
slave2：10.0.0.11:3310
构建主从
shard1
10.0.0.11:3307 <-----> 10.0.0.12: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.12', 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.11', 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.11:3309 ------> 10.0.0.11:3307
db01
mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.11', 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.12', 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.12:3308 <-----> 10.0.0.11: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.11', 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.12', 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.12', 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.12:3310 -----> 10.0.0.12:3308
db02
mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.12', 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.11:3310 -----> 10.0.0.11:3308
db01
mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='10.0.0.11', 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"
检测主从状态
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
MyCat安装
yum -y install java
下载
wget http://dl.mycat.io/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
MyCat 分表
image.png
其中order表，业务量繁忙，修改量最大，需要进行水平拆分
数据库分布式架构
垂直拆分
- 指不不对表结构进行拆分
水平拆分
- 指对表结构进行拆分
拆分方法
范围拆分
取模
枚举
哈希
时间
MyCat具有以分片策略，研究MyCat就是研究分片策略
MyCat使用
- conf 目录
schema.xml 主配置文件 【重要】
rule.xml 拆分策略【重要】
server.xml mycat服务相关的端口和密码
log4j2.xml MyCat日志相关的
*.txt 分片策略使用规则
- logs目录
wrapper.log ---->mycat启动日志
mycat.log ---->mycat详细工作日志
数据准备
db01:
mysql -S /data/3307/mysql.sock
grant all on *.* to root@'10.0.0.%' identified by '123';
source /root/world.sql
mysql -S /data/3308/mysql.sock
grant all on *.* to root@'10.0.0.%' identified by '123';
source /root/world.sql
配置文件说明
[root@mysql mycat]# cat conf/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.0.11:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
--------------------------------------------------------------------------------------------------------------------
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema> # schema是库的意思，MyCat的逻辑库，可以认为TESTDB是MyCat的一个独立库
登录MyCat
[root@mysql bin]# mysql -uroot -p123456 -h 10.0.0.11 -P 8066
mysql> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB |
+----------+
1 row in set (0.00 sec)
mysql> use TESTDB
mysql> show tables;
+-----------------+
| Tables_in_world |
+-----------------+
| city |
| country |
| countrylanguage |
+-----------------+
为什么要有TESTDB逻辑库，因为我们是水平分表和垂直分表。给用户的感觉要是一张表，我们可以通过逻辑表把他们放在一起，show tables 可以看到表。逻辑库仅是一个逻辑概念
-----------------------------------------------------------------------------------------------------------------------------
<dataNode name="dn1" dataHost="localhost1" database= "world" />
dn1 也是逻辑概念，对应 dataHost localhost1 对应后端数据库的world
database=world 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.0.11:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3309" user="root" password="123" />
</writeHost>
</dataHost>
TESTDB访问的是后端写的数据库10.0.0.11:3307 读的数据库是10.0.0.11:3309 ，也是这个两个节点world数据库的表
MyCat 实现读写分离
配置文件
<?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.0.11:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
------------------------------------------------------------------------------------------------------------------------
<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.0.11:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3309" user="root" password="123" />
</writeHost>
</dataHost>
TESTDB访问的是后端写的数据库10.0.0.11:3307 读的数据库是10.0.0.11:3309 ，
也是这个两个节点world数据库的表,当写节点宕机了，读节点也就不能用了（因为数据不同步了，给用户不是最新的数据，但mycat仍然能读到从节点内容）。
解决办法：构建MHA，写节点用VIP
测试
mysql -uroot -p123456 -h 10.0.0.11 -P 8066
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
1 row in set (0.08 sec)
mysql> begin;
Query OK, 0 rows affected (0.01 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
1 row in set (0.01 sec)
MyCat高可用即读写分离
实现效果当
10.0.0.11:3307宕机了，10.0.0.12:3307进行接管
配置文件
[root@mysql mycat]# cat conf/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.0.11:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.12:3307" user="root" password="123">
<readHost host="db4" url="10.0.0.12:3309" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
--------------------------------------------------------------------------------------------------------------------------------------------------
<writeHost host="db1" url="10.0.0.11:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.12:3307" user="root" password="123">
<readHost host="db4" url="10.0.0.12:3309" user="root" password="123" />
</writeHost>
</dataHost>
如果写入,按照配置文件的顺序进行写入，第一个10.0.0.11:3307 为写入节点，第二个10.0.0.12:3307为备用写节点。当第一个宕机，第二个就会代替。
在第一个可以写时，10.0.0.11:3309,10.0.0.12:3307,10.0.0.12:3309都为读节点，一写三读
如果第一个宕机了，恢复之后，mycat依然使用第二个为写节点
测试
mysql -uroot -p123456 -h 10.0.0.11 -P 8066
mysql> select @@server_id;
+-------------+
+-------------+
| 17 |
+-------------+
1 row in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
1 row in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 19 |
+-------------+
1 row in set (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 9 |
+-------------+
1 row in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 7 |
+-------------+
1 row in set (0.00 sec)
--------------------------------------------------------------------------------------------------------------------------
[root@mysql mycat]# systemctl stop mysqld3307
[root@mysql mycat]# mysql -uroot -p123456 -h 10.0.0.11 -P 8066
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@server_id;
ERROR 1184 (HY000): java.net.ConnectException: 拒绝连接
mysql> select @@server_id;
ERROR 1184 (HY000): java.net.ConnectException: 拒绝连接
mysql> select @@server_id;
ERROR 1184 (HY000): java.net.ConnectException: 拒绝连接
mysql> select @@server_id;
ERROR 1184 (HY000): java.net.ConnectException: 拒绝连接
mysql> select @@server_id;
ERROR 1184 (HY000): java.net.ConnectException: 拒绝连接
mysql> select @@server_id;
ERROR 1184 (HY000): java.net.ConnectException: 拒绝连接
mysql> select @@server_id;
ERROR 1184 (HY000): java.net.ConnectException: 拒绝连接
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
| 17 |
+-------------+
1 row in set (0.02 sec)
MyCat 高级应用 分布式解决方案
垂直分表
配置文件
[root@mysql bin]# cat ../conf/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">
<table name="user" dataNode="dn1" />
<table name="order_t" dataNode="dn2" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database= "taobao" />
<dataNode name="dn2" dataHost="localhost2" database= "taobao" />
<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.0.11:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.12:3307" user="root" password="123">
<readHost host="db4" url="10.0.0.12:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="localhost2" 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.11:3308" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.12:3308" user="root" password="123">
<readHost host="db4" url="10.0.0.12:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
测试
mysql -S /data/3307/mysql.sock -e "create database taobao;use taobao; create table user(id int,name varchar(4));"
mysql -S /data/3308/mysql.sock -e "create database taobao;use taobao; create table order_t(id int,name varchar(4));"
mysql> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| order_t |
| user |
+------------------+
2 rows in set (0.01 sec)
mysql> insert user values(1,"aa");
Query OK, 1 row affected (0.02 sec)
mysql> insert order_t values(1,"bb");
Query OK, 1 row affected (0.00 sec)
mycat参数说明
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
------------------------------------------------------------------------------------------------------------------------------
# datahost配置属性
balance属性 ：控制从库的负载均衡（查）
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 .writeType="0" 所有的写操作都发送配置第一个writehost，第一个挂了，切到还生存的第二个writehost，重新启动已切换后的为主，切换记录记录在配置文件dnindex.poperties
2 .writeType="1" 所有写操作都随机发送到配置的writeHost中，但不推荐使用
# switchType属性
-1 表示不自动切换
1 默认自动切换
2 基于MySQL主从同步状态决定是否切换，心跳数据为 show slave status
# datahost 其他配置
maxCon="1000" 最大并发连接数
minCon="10" MyCat启动之后，会在后端节点上自动开启的连接线程。最好不要太多，此功能会消耗MySQL的内存。相当于MySQL的常驻内存线程，拿内存换CPU性能 20或30即可
tempReadHostAvaiable="1"
这个临时开启读，在这个一主一从时（1个writehost，1个readhost时）可以开启这个参数，如果2个writehost，2个readhost时<heartbeat>select user()<heartbeat>监测心跳
两个主两个从时，当一个主库宕了，这个主库的从库更新不到最新数据，即使读最后的结果也不一致
-----------------------------------------------------------------------------------------------------------------------------------
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
sqlMaxLimit 自动作分页，100行一个分页，可以自己调整
MyCat-分布式 范围分片（水平分片）
比如t1表
1 行数非常多，2000w（1-1000w:sh1 1000w1-2000w:sh2）
2 访问非常频繁，用户访问离散
范围分片模式-易扩展mycat 数据节点
vim schema.xml
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<?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">
<table name="t1" dataNode="dn1,dn2" rule="auto-sharding-long" />
<table name="order_t" dataNode="dn2" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database= "taobao" />
<dataNode name="dn2" dataHost="localhost2" database= "taobao" />
<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.0.11:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.12:3307" user="root" password="123">
<readHost host="db4" url="10.0.0.12:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="localhost2" 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.11:3308" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.12:3308" user="root" password="123">
<readHost host="db4" url="10.0.0.12:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
-------------------------------------------------------------------------------------------------------------------------------
vim rule.xml
<tableRule name="auto-sharding-long"> #定义规则
<rule>
<columns>id</columns> # 按照id列进行拆分
<algorithm>rang-long</algorithm> #拆分函数
</rule>
</tableRule>
<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.
0-5=0
5-10=1
0 代表第一个datanode 0到5行在第一个datanode 0<=x<=5
1 代表第二个datanode 6到10行在第二个datanode 5<x<=10
------------------------------------------------------------------------------------------------------------------------------
# 测试
mysql -S /data/mysql3307/mysql.sock -e "use taobao;create table t1(id int primary key);"
mysql -S /data/mysql3308/mysql.sock -e "use taobao;create table t1(id int primary key);"
mysql -uroot -p123456 -h 10.0.0.11 -P 8066
mysql> use TESTDB
mysql> insert into t1(id) values(1);
Query OK, 1 row affected (0.04 sec)
mysql> insert into t1(id) values(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(id) values(8);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1(id) values(7);
Query OK, 1 row affected (0.01 sec)
[root@mysql mycat]# mysql -S /data/3307/mysql.sock -e "use taobao;select * from t1;"
+----+
| id |
+----+
| 1 |
| 2 |
+----+
[root@mysql mycat]# mysql -S /data/3308/mysql.sock -e "use taobao;select * from t1;"
+----+
| id |
+----+
| 7 |
| 8 |
+----+
取模模式 - 不易扩展 mycat 数据节点，但数据分配平均
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">
<table name="t4" dataNode="dn1,dn2" rule="mod-long" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database= "taobao" />
<dataNode name="dn2" dataHost="localhost2" database= "taobao" />
<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.0.11:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.12:3307" user="root" password="123">
<readHost host="db4" url="10.0.0.12:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="localhost2" 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.11:3308" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.12:3308" user="root" password="123">
<readHost host="db4" url="10.0.0.12:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
--------------------------------------------------------------------------------------------------------------------
vim rule.xml
<tableRule name="mod-long">
<rule>
<columns>id</columns> id 为分片键
<algorithm>mod-long</algorithm> mod-long函数
</rule>
</tableRule>
<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>
## 测试
[root@mysql mycat]# mysql -S /data/3307/mysql.sock -e "use taobao;create table t4(id int primary key);"
[root@mysql mycat]# mysql -S /data/3308/mysql.sock -e "use taobao;create table t4(id int primary key);"
[root@mysql mycat]# mysql -uroot -p123456 -h 10.0.0.11 -P 8066
mysql> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into t4(id) value(1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into t4(id) value(2);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t4(id) value(3);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t4(id) value(4);
Query OK, 1 row affected (0.01 sec)
[root@mysql mycat]# mysql -S /data/3307/mysql.sock -e "use taobao; select * from t4;"
+----+
| id |
+----+
| 2 |
| 4 |
+----+
[root@mysql mycat]# mysql -S /data/3308/mysql.sock -e "use taobao; select * from t4;"
+----+
| id |
+----+
| 1 |
| 3 |
+----+
枚举分片
例： t6表
id name telnum
1 bj 1212
2 sh 2222
假设只有bi和sh两个地区
将来bj落在第一个分片上
sh 落在第二个分片上
[root@mysql mycat]# vim conf/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">
<table name="t6" dataNode="dn1,dn2" rule="sharding-by-intfile" />
</schema>
<dataNode name="dn1" dataHost="localhost1" database= "taobao" />
<dataNode name="dn2" dataHost="localhost2" database= "taobao" />
<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.0.11:3307" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3309" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.12:3307" user="root" password="123">
<readHost host="db4" url="10.0.0.12:3309" user="root" password="123" />
</writeHost>
</dataHost>
<dataHost name="localhost2" 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.11:3308" user="root" password="123">
<readHost host="db2" url="10.0.0.11:3310" user="root" password="123" />
</writeHost>
<writeHost host="db3" url="10.0.0.12:3308" user="root" password="123">
<readHost host="db4" url="10.0.0.12:3310" user="root" password="123" />
</writeHost>
</dataHost>
</mycat:schema>
vim rule.xml
<tableRule name="sharding-by-intfile">
<rule>
<columns>name</columns> 按照name列进行分片
<algorithm>hash-int</algorithm>
</rule>
</tableRule>
<function name="hash-int"
class="io.mycat.route.function.PartitionByFileMap">
<property name="mapFile">partition-hash-int.txt</property>
<property name="type">1</property> 使其支持字符串类型
</function>
[root@mysql mycat]# cat conf/partition-hash-int.txt
bj=0
sh=1
DEFAULT_NODE=1 默认在1
注：如果有34个省，则需要34个节点
# 测试
mysql -S /data/3307/mysql.sock -e "use taobao;create table t6(id int primary key,name varchar(3),telnum int);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table t6(id int primary key,name varchar(3),telnum int);"
[root@mysql mycat]# mysql -uroot -p123456 -h 10.0.0.11 -P 8066
mysql> use TESTDB
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into t6(id,name,telnum) values(1,bj,1);
Query OK, 1 row affected (0.02 sec)
mysql> insert into t6(id,name,telnum) values(2,sh,2);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t6(id,name,telnum) values(3,st,2);
Query OK, 1 row affected (0.01 sec)
[root@mysql mycat]# mysql -S /data/3307/mysql.sock -e "use taobao; select * from t6;"
+----+------+--------+
| id | name | telnum |
+----+------+--------+
| 1 | bj | 1 |
+----+------+--------+
[root@mysql mycat]# mysql -S /data/3308/mysql.sock -e "use taobao; select * from t6;"
+----+------+--------+
| id | name | telnum |
+----+------+--------+
| 2 | sh | 2 |
| 3 | st | 2 |
+----+------+--------+
网友评论