mysql之mycat分布式

最新推荐文章于 2023-06-16 15:56:20 发布

weixin_47647077

最新推荐文章于 2023-06-16 15:56:20 发布

阅读量264

点赞数

分类专栏： sk 文章标签： mysql mycat

本文链接：https://blog.csdn.net/weixin_47647077/article/details/118764798

版权

sk 专栏收录该内容

21 篇文章 0 订阅

订阅专栏

1、mycat基础搭建图

2、环境准备

2.1、MySQL部署

在服务器03和04操作
1、官网下载mysql-5.7的二进制包 mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
2、tar -zxf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
3、cd /usr/local/;mv mysql-5.7.32-linux-glibc2.12-x86_64 mysql
4、mkdir /data/33{07,17,08,18}/data -p ;chown -R mysql.mysql /data/33*
5、echo 'export PATH=/usr/local/mysql/bin:$PATH' /etc/profile;source /etc/profile
6、mv /etc/my.cnf /etc/my.cnf_bak
7、mysqld --initialize-insecure --user=mysql --datadir=/data/3307/data --basedir=/usr/local/mysql
   mysqld --initialize-insecure --user=mysql --datadir=/data/3317/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/3318/data --basedir=/usr/local/mysql

8、配置文件

==============================192.168.0.3====================================

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/3317/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3317/data
socket=/data/3317/mysql.sock
port=3317
log-error=/data/3317/mysql.log
log_bin=/data/3317/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/3318/my.cnf<<EOF
[mysqld]
basedir=/usr/local/mysql
datadir=/data/3318/data
socket=/data/3318/mysql.sock
port=3318
log-error=/data/3318/mysql.log
log_bin=/data/3318/mysql-bin
binlog_format=row
skip-name-resolve
server-id=18
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat >/usr/lib/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 >/usr/lib/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 >/usr/lib/systemd/system/mysqld3317.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/3317/my.cnf
LimitNOFILE = 5000
EOF
cat >/usr/lib/systemd/system/mysqld3318.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/3318/my.cnf
LimitNOFILE = 5000
EOF


systemctl daemon-reload
=================================192.168.0.4====================================
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=47
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=48
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

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


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

cat >/usr/lib/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 >/usr/lib/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 >/usr/lib/systemd/system/mysqld3317.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/3317/my.cnf
LimitNOFILE = 5000
EOF

cat >/usr/lib/systemd/system/mysqld3318.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/3318/my.cnf
LimitNOFILE = 5000
EOF


systemctl daemon-reload

9、启动两个服务器mysql
    systemctl start mysqld3307
    systemctl start mysqld3308
    systemctl start mysqld3317
    systemctl start mysqld3318
10、查看server_id
    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/3317/mysql.sock -e "show variables like 'server_id'"
    mysql -S /data/3318/mysql.sock -e "show variables like 'server_id'"

2.2、MySQL搭建主从

1、根据图标搭建主从关系（箭头指向为从库）
第一组：
192.168.0.3:3307  <------------> 192.168.0.4:3307
192.168.0.3:3307  -------------> 192.168.0.3:3317
192.168.0.4:3307  -------------> 192.168.0.4:3317
第二组：
192.168.0.3:3308  <------------> 192.168.0.4:3308
192.168.0.3:3308  -------------> 192.168.0.3:3318
192.168.0.4:3308  -------------> 192.168.0.4:3318

第一组主从搭建：
1、192.168.0.3:3307  <------------> 192.168.0.4:3307

1）建立链接的复制用户（192.168.0.3上操作）
mysql  -S /data/3307/mysql.sock -e "grant replication slave on *.* to master_3@'192.168.0.%' identified by '123456';"
2）查看binlog文件和节点（192.168.0.3上操作）
mysql  -S /data/3307/mysql.sock -e "show master status \G"
结果：
File: mysql-bin.000001
Position: 448

3）从库绑定主库信息（192.168.0.4上操作）
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO 
MASTER_HOST='192.168.0.3',
MASTER_USER='master_3',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=448,
MASTER_CONNECT_RETRY=10;"
4）启动复制：（192.168.0.4上操作）
mysql  -S /data/3307/mysql.sock -e "start slave;"
5）查看状态：（192.168.0.4上操作）
mysql  -S /data/3307/mysql.sock -e "show slave status\G"
7） （192.168.0.4上操作）
mysql  -S /data/3307/mysql.sock -e "grant replication slave on *.* to master_4@'192.168.0.%' identified by '123456';"  
8） （192.168.0.4上操作）
mysql  -S /data/3307/mysql.sock -e "show master status \G"
9）（192.168.0.3上操作）
mysql  -S /data/3307/mysql.sock -e "CHANGE MASTER TO 
MASTER_HOST='192.168.0.4',
MASTER_USER='master_4',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=451,
MASTER_CONNECT_RETRY=10;"
10）（192.168.0.3上操作）
mysql  -S /data/3307/mysql.sock -e "start slave;"
11） （192.168.0.3上操作）
mysql  -S /data/3307/mysql.sock -e "show slave status\G"

2、192.168.0.3:3307  -------------> 192.168.0.3:3317
192.168.0.3上操作：

mysql  -S /data/3317/mysql.sock -e "CHANGE MASTER TO 
MASTER_HOST='192.168.0.3',
MASTER_USER='master_3',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=448,
MASTER_CONNECT_RETRY=10;"

mysql  -S /data/3317/mysql.sock -e "start slave;"

mysql  -S /data/3317/mysql.sock -e "show slave status\G"

3、192.168.0.4:3307  -------------> 192.168.0.4:3317
192.168.0.4上操作：

mysql  -S /data/3317/mysql.sock -e "CHANGE MASTER TO 
MASTER_HOST='192.168.0.4',
MASTER_USER='master_4',
MASTER_PASSWORD='123456',
MASTER_PORT=3307,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=451,
MASTER_CONNECT_RETRY=10;"

mysql  -S /data/3317/mysql.sock -e "start slave;"

mysql  -S /data/3317/mysql.sock -e "show slave status\G"

第二组主从搭建：
1、192.168.0.3:3308  <------------> 192.168.0.4:3308

1）192.168.0.3
mysql  -S /data/3308/mysql.sock -e "grant replication slave on *.* to master_3@'192.168.0.%' identified by '123456';"

mysql  -S /data/3308/mysql.sock -e "show master status \G"

2）192.168.0.4
mysql  -S /data/3308/mysql.sock -e "CHANGE MASTER TO 
MASTER_HOST='192.168.0.3',
MASTER_USER='master_3',
MASTER_PASSWORD='123456',
MASTER_PORT=3308,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=451,
MASTER_CONNECT_RETRY=10;"

mysql  -S /data/3308/mysql.sock -e "start slave;"

mysql  -S /data/3308/mysql.sock -e "show slave status\G"

mysql  -S /data/3308/mysql.sock -e "grant replication slave on *.* to master_4@'192.168.0.%' identified by '123456';"  

mysql  -S /data/3307/mysql.sock -e "show master status \G"

3）（192.168.0.3上操作）
mysql  -S /data/3308/mysql.sock -e "CHANGE MASTER TO 
MASTER_HOST='192.168.0.4',
MASTER_USER='master_4',
MASTER_PASSWORD='123456',
MASTER_PORT=3308,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=451,
MASTER_CONNECT_RETRY=10;"

mysql  -S /data/3308/mysql.sock -e "start slave;"

mysql  -S /data/3308/mysql.sock -e "show slave status\G"

2、192.168.0.3:3308  -------------> 192.168.0.3:3318
192.168.0.3上操作：

mysql  -S /data/3318/mysql.sock -e "CHANGE MASTER TO 
MASTER_HOST='192.168.0.3',
MASTER_USER='master_3',
MASTER_PASSWORD='123456',
MASTER_PORT=3308,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=451,
MASTER_CONNECT_RETRY=10;"

mysql  -S /data/3318/mysql.sock -e "start slave;"

mysql  -S /data/3318/mysql.sock -e "show slave status\G"

3、192.168.0.4:3308  -------------> 192.168.0.4:3318
192.168.0.4上操作：

mysql  -S /data/3318/mysql.sock -e "CHANGE MASTER TO 
MASTER_HOST='192.168.0.4',
MASTER_USER='master_4',
MASTER_PASSWORD='123456',
MASTER_PORT=3308,
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=451,
MASTER_CONNECT_RETRY=10;"

mysql  -S /data/3318/mysql.sock -e "start slave;"

mysql  -S /data/3318/mysql.sock -e "show slave status\G"

mysql添加远程登录权限便于mycat管理（在192.168.0.3和.4上分别操作）：
mysql  -S /data/3307/mysql.sock -e "grant all  on *.* to root@'192.168.0.%' identified by '123456'  with grant option;"
mysql  -S /data/3317/mysql.sock -e "grant all  on *.* to root@'192.168.0.%' identified by '123456'  with grant option;"
mysql  -S /data/3308/mysql.sock -e "grant all  on *.* to root@'192.168.0.%' identified by '123456'  with grant option;"
mysql  -S /data/3318/mysql.sock -e "grant all  on *.* to root@'192.168.0.%' identified by '123456'  with grant option;"

3、mycat安装

3.1、mycat环境准备

1、yum install -y java

3.2、mycat下载

1、Mycat-server-xxxxx.linux.tar.gz
2、http://dl.mycat.io/
3、tar -zxf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz  -C /usr/local/
4、ls /usr/local/mycat/
bin  catlet  conf  lib  logs  version.txt
5、echo 'export PATH=/usr/local/mycat/bin:$PATH' >> /etc/profile ; source /etc/profile

3.3、mycat启动及日志文件介绍

1、启动
mycat start
2、连接mycat：
mysql -uroot -p123456 -h 127.0.0.1 -P8066
3、日志文件
logs目录:
wrapper.log       ---->mycat启动日志
mycat.log         ---->mycat详细工作日志

3.4、mysql建需要管理的数据库

mysql -S /data/3307/mysql.sock  < world.sql
mysql -S /data/3308/mysql.sock  < world.sql

3.5、配置文件解析和读写分离配置

1、主要了解的配置文件
conf目录:
schema.xml    （主要链接数据库）  
主配置文件（读写分离、高可用、分布式策略定制、节点控制、）
server.xml      （主要链接用户）
mycat软件本身相关的配置
rule.xml 
分片规则配置文件,记录分片规则列表、使用方法等

2、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="host1" database= "world" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.0.3:3307" user="root" password="123456">
            <readHost host="db2" url="192.168.0.3:3317" user="root" password="123456" />
    </writeHost>
    </dataHost>
</mycat:schema>

3.5.1、schema.xml初步分析

1、数据库配置，与server.xml中的数据库对应
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
2、分片配置（数据节点）
    <dataNode name="dn1" dataHost="host1" database= "world" />
3、物理数据库配置（读写的分配）
    <dataHost name="host1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
    <writeHost host="db1" url="192.168.0.3:3307" user="root" password="123456">
            <readHost host="db2" url="192.168.0.3:3317" user="root" password="123456" />
    </writeHost>
    </dataHost>
</mycat:schema>
4、单独配置说明
sqlMaxLimit配置默认查询数量
database为真实数据库名
balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的writeHost 上。
balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1 ->S1 ， M2->S2，并且 M1 与 M2 互为主备)，正常情况下， M2,S1,S2 都参与 select 语句的负载均衡。
balance="2"，所有读操作都随机的在 writeHost、 readhost 上分发。
balance="3"， 所有读请求随机的分发到 wiriterHost 对应的 readhost 执行,writerHost 不负担读压力，注意 balance=3 只在 1.4 及其以后版本有， 1.3 没有。
writeType="0", 所有写操作发送到配置的第一个 writeHost，第一个挂了切到还生存的第二个writeHost，重新启动后已切换后的为准，切换记录在配置文件中:dnindex.properties .
writeType="1"，所有写操作都随机的发送到配置的 writeHost。
writeType="2"，没实现。
-1 表示不自动切换
1 默认值，自动切换
2 基于MySQL 主从同步的状态决定是否切换

3.5.2、读写分离配置验证

上面的配置是读写分离的操作
读写分离测试
 mysql -uroot -p -h 192.168.0.2 -P8066
 show variables like 'server_id';
 begin; show variables like 'server_id';
总结： 
以上案例实现了1主1从的读写分离功能，写操作落到主库，读操作落到从库.如果主库宕机，从库不能在继续提供服务了。

3.6、配置读写分离及高可用

<?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="host1" database= "world" />
    <dataHost name="host1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
        <writeHost host="mdb1" url="192.168.0.3:3307" user="root" password="123456">
                <readHost host="sdb1" url="192.168.0.3:3317" user="root" password="123456" />
        </writeHost>
        <writeHost host="mdb2" url="192.168.0.4:3307" user="root" password="123456">
                <readHost host="sdb2" url="192.168.0.4:3317" user="root" password="123456" />
        </writeHost>
    </dataHost>
</mycat:schema>


这个配置正常情况下mdb1（192.168.0.3:3307）负责sql语句的写
其他3个负责sql语句的读
如果主机或主机mysql宕掉了就会切换到mdb2变为写（即使mdb1正常也不会切换写mdb1变成读）

读写分离测试
 mysql -uroot -p -h 127.0.0.1 -P8066
 show variables like 'server_id';
 show variables like 'server_id';
 show variables like 'server_id';
 begin;
 show variables like 'server_id';
关闭192.168.0.3验证宕掉后的结果

3.7、垂直分表

<?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="host1" database= "taobao" />
    <dataNode name="dn2" dataHost="host2" database= "taobao" />

    <dataHost name="host1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
        <writeHost host="mdb1" url="192.168.0.3:3307" user="root" password="123456">
                <readHost host="sdb1" url="192.168.0.3:3317" user="root" password="123456" />
        </writeHost>
        <writeHost host="mdb2" url="192.168.0.4:3307" user="root" password="123456">
                <readHost host="sdb2" url="192.168.0.4:3317" user="root" password="123456" />
        </writeHost>
    </dataHost>

    <dataHost name="host2" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
        <writeHost host="mdb1" url="192.168.0.3:3308" user="root" password="123456">
                <readHost host="sdb1" url="192.168.0.3:3318" user="root" password="123456" />
        </writeHost>
        <writeHost host="mdb2" url="192.168.0.4:3308" user="root" password="123456">
                <readHost host="sdb2" url="192.168.0.4:3318" user="root" password="123456" />
        </writeHost>
    </dataHost>

</mycat:schema>

3.7.1、垂直表分析

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))"

3.8、水平拆分表

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="dn1">
        <table name="user" dataNode="dn1"/>
        <table name="order_t" dataNode="dn2"/>
        <table name="test1" dataNode="dn1,dn2" rule="auto-sharding-long" />
</schema>

    <dataNode name="dn1" dataHost="host1" database= "taobao" />
    <dataNode name="dn2" dataHost="host2" database= "taobao" />

    <dataHost name="host1" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
        <writeHost host="mdb1" url="192.168.0.3:3307" user="root" password="123456">
                <readHost host="sdb1" url="192.168.0.3:3317" user="root" password="123456" />
        </writeHost>
        <writeHost host="mdb2" url="192.168.0.4:3307" user="root" password="123456">
                <readHost host="sdb2" url="192.168.0.4:3317" user="root" password="123456" />
        </writeHost>
    </dataHost>

    <dataHost name="host2" maxCon="1000" minCon="10" balance="1"  writeType="0" dbType="mysql"  dbDriver="native" switchType="1">
        <heartbeat>select user()</heartbeat>
        <writeHost host="mdb1" url="192.168.0.3:3308" user="root" password="123456">
                <readHost host="sdb1" url="192.168.0.3:3318" user="root" password="123456" />
        </writeHost>
        <writeHost host="mdb2" url="192.168.0.4:3308" user="root" password="123456">
                <readHost host="sdb2" url="192.168.0.4:3318" user="root" password="123456" />
        </writeHost>
    </dataHost>

</mycat:schema>



2、cat rule.xml

                </rule>
        </tableRule>
        <tableRule name="auto-sharding-long">
                <rule>

        <function name="rang-long"
                          class="io.mycat.route.function.AutoPartitionByLong">
                <property name="mapFile">autopartition-long.txt</property>
        </function>


3、cat autopartition-long.txt
0-10=0
10-20=1


4、分析：
<table name="test1" dataNode="dn1,dn2" rule="auto-sharding-long" />  （在schema.xml中加）
把test1表分别建到host1和host2两个服务器的数据库中调用rule.xml规则
这里配置的规则：
根据id数（1-10）数据写到host1和（11-20）数据写到host2

3.8.1、测试

创建测试表：
mysql -S /data/3307/mysql.sock -e "use taobao;create table test1 (id int not null primary key auto_increment,name varchar(20) not null);"

mysql -S /data/3308/mysql.sock  -e "use taobao;create table test1 (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 test1(id,name) values(1,'a');
insert into test1(id,name) values(2,'b');
insert into test1(id,name) values(3,'c');
insert into test1(id,name) values(11,'aa');
insert into test1(id,name) values(12,'bb');

3.9、取模分片（mod-long）：

vim schema.xml (加一行)

     <table name="test2" dataNode="dn1,dn2" rule="mod-long" />

cat rule.xml (修改count后面数值)

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
                <!-- how many data nodes -->
                <property name="count">2</property>
</function>

3.9.1、测试

准备测试环境
     
创建测试表：
mysql -S /data/3307/mysql.sock -e "use taobao;create table test2 (id int not null primary key auto_increment,name varchar(20) not null);"
mysql -S /data/3308/mysql.sock -e "use taobao;create table test2 (id int not null primary key auto_increment,name varchar(20) not null);"

重启mycat 
mycat restart 

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

use TESTDB
insert into test2(id,name) values(1,'a');
insert into test2(id,name) values(2,'b');
insert into test2(id,name) values(3,'c');
insert into test2(id,name) values(4,'d');

分别登录后端节点查询数据
mysql -S /data/3307/mysql.sock 
use taobao
select * from test2;

mysql -S /data/3308/mysql.sock 
use taobao
select * from test2;

3.10、模块

rule的规则还有很多可以自己慢慢研究

weixin_47647077

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
mysql之mycat分布式

1、mycat基础搭建图2、环境准备1、官网下载mysql-5.7的二进制包 mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz2、tar -zxf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
复制链接

扫一扫