目 录CONTENT

文章目录

搭建MyCAT

简中仙
2020-10-26 / 0 评论 / 0 点赞 / 53 阅读 / 0 字 / 正在检测是否收录...
温馨提示:
本文最后更新于2024-03-06,若内容或图片失效,请留言反馈。 本文如有错误或者侵权的地方,欢迎您批评指正!

一、架构设计

1、主从规划

数据库mysql-1mysql-2
实例3307 3308 3309 33103307 3308 3309 3310
互为主从mysql-1:3307←→mysql-2:3307mysql-1:3308←→mysql-2:3308
单向主从mysql-1:3309—→mysql-1:3307mysql-2:3309—→mysql-2:3307
单向主从mysql-1:3310—→mysql-1:3308mysql-2:3310—→mysql-2:3308

2、分片规划

shard1shard2
Mastermysql-1:3307mysql-2:3308
slave1mysql-1:3309mysql-2:3310
Standby Mastermysql-2:3307mysql-1:3308
slave2mysql-2:3309mysql-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最大的并发连接数
minConmycat在启动之后,会在后端节点上自动开启的连接线程
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" /> 
0

评论区