基于mycat中间件的数据库分片架构的搭建

最新推荐文章于 2022-11-27 17:17:00 发布

SS_CC_Go

最新推荐文章于 2022-11-27 17:17:00 发布

阅读量398

点赞数

分类专栏：数据库

本文链接：https://blog.csdn.net/SS_CC_Go/article/details/104659923

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、环境

server1：mycat中间件安装机器

server2：安装数据库

server3：安装数据库

二、操作

1、创建数据库实例

1.1、server2创建实例(server3相同的操作)

1.1.1、创建数据目录

[root@server2 mysql]# mkdir /data/3307 -p
[root@server2 mysql]# mkdir /data/3308 -p
[root@server2 mysql]# mkdir /data/3309 -p
[root@server2 mysql]# mkdir /data/3310 -p

1.1.2、创建mysql用户和用户组

[root@server2 mysql]# useradd mysql
[root@server2 mysql]# groupadd mysql
groupadd: group 'mysql' already exists
[root@server2 mysql]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)

1.1.3、配置mysql的环境变量

vim /etc/profile
    export PATH=$PATH:/mnt/mysql/bin    ##最后一行添加

source /etc/profile      ##使文件生效

1.1.4、初始化数据库

mysqld --initialize-insecure --user=mysql --datadir=/data/3307 --basedir=/mnt/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3308 --basedir=/mnt/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3309 --basedir=/mnt/mysql
mysqld --initialize-insecure --user=mysql --datadir=/data/3310 --basedir=/mnt/mysql

1.1.5、准备配置文件

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

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

cat>/data/3309/my.cnf<<EOF
basedir=/mnt/mysql
datadir=/data/3309
socket=/data/3309/mysql.sock
port=3309
user=mysql
log-error=/data/3309/mysql.log
log_bin=/data/3309/mysql-bin
binlog_format=row
skip-name-resolve
server-id=09
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
EOF

cat>/data/3310/my.cnf<<EOF
basedir=/mnt/mysql
datadir=/data/3310
socket=/data/3310/mysql.sock
port=3310
user=mysql
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

1.1.6、准备启动脚本

cat>/etc/systemd/system/mysqld3307.service<<EOF
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/using-systemd.html
After=network.target
After=syslog.target

[install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/mnt/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/using-systemd.html
After=network.target
After=syslog.target

[install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/mnt/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/using-systemd.html
After=network.target
After=syslog.target

[install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/mnt/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/using-systemd.html
After=network.target
After=syslog.target

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

1.1.7、修改权限，启动多实例子

chown -R mysql.mysql /data/*
systemctl start mysqld3307
systemctl start mysqld3308
systemctl start mysqld3309
systemctl start mysqld3310

2、分片并搭建主从复制

Shard1：
    server2:3307<---->server3:3307			双主
    server2:3307---->server2:3309			
    server3:3307---->server3:3309
Shard2：
    server2:3308<---->server3:3308			双主	
    server2:3308---->server2:3310
    server3:3308---->server3:3310

2.1、主库创建复制用户和超级用户

server2:
    mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'172.25.27.%' identified by 'repl';"
    mysql -S /data/3307/mysql.sock -e "grant all privileges on *.* to root@'172.25.27.%' identified by 'root' with grant option;"
    mysql -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'172.25.27.%' identified by 'repl';"
    mysql -S /data/3308/mysql.sock -e "grant all privileges on *.* to root@'172.25.27.%' identified by 'root' with grant option;"

server3:
    mysql -S /data/3307/mysql.sock -e "grant replication slave on *.* to repl@'172.25.27.%' identified by 'repl';"
    mysql -S /data/3307/mysql.sock -e "grant all privileges on *.* to root@'172.25.27.%' identified by 'root' with grant option;"
    mysql -S /data/3308/mysql.sock -e "grant replication slave on *.* to repl@'172.25.27.%' identified by 'repl';"
    mysql -S /data/3308/mysql.sock -e "grant all privileges on *.* to root@'172.25.27.%' identified by 'root' with grant option;"

2.2、主从搭建

server2:
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.25.27.3',
MASTER_PORT=3307,MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='repl';start 
slave;show slave status\G"

mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.25.27.3',
MASTER_PORT=3308,MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='repl';start 
slave;show slave status\G"

mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.25.27.2',
MASTER_PORT=3307,MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='repl';start 
slave;show slave status\G"

mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.25.27.2',
MASTER_PORT=3308,MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='repl';start 
slave;show slave status\G"

server3:
mysql -S /data/3307/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.25.27.2',
MASTER_PORT=3307,MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='repl';start 
slave;show slave status\G"

mysql -S /data/3308/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.25.27.2',
MASTER_PORT=3308,MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='repl';start 
slave;show slave status\G"    

mysql -S /data/3309/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.25.27.3',
MASTER_PORT=3307,MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='repl';start 
slave;show slave status\G"

mysql -S /data/3310/mysql.sock -e "CHANGE MASTER TO MASTER_HOST='172.25.27.3',
MASTER_PORT=3308,MASTER_AUTO_POSITION=1,MASTER_USER='repl',MASTER_PASSWORD='repl';start 
slave;show slave status\G"

2.3、主从复制测试

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

3、mycat配置

3.1、配置读写分离和高可用

3.1.1、安装java环境

3.1.2、mycat环境变量的配置

server1：
  vim /etc/profile
    export PATH=$PATH:/mnt/mycat/bin
  source /etc/profile

3.1.3、mycat读写分离和高可用配置

vim /mnt/mycat/schema.xml    ##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="sh1">
                <table name="user" dataNode="sh1"/>        ##不同分片集群上的表名
                <table name="order_t" dataNode="sh2"/>     
        </schema>
        <dataNode name="sh1" dataHost="first" database="taobao" />
        <dataNode name="sh2" dataHost="second" database="taobao" />
        ##第一个分片上的读写分离和高可用
        <dataHost name="first" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
                        <heartbeat>select user()</heartbeat>    ##心跳检测
                <writeHost host="db1" url="172.25.27.2:3307" user="root" password="root">
                        <readHost host="db2" url="172.25.27.2:3309" user="root" password="root" />
                </writeHost>
                <writeHost host="db3" url="172.25.27.3:3307" user="root" password="root">
                        <readHost host="db4" url="172.25.27.3:3309" user="root" password="root" />
                </writeHost>
        </dataHost>
        ##第二个分片上的读写分离和高可用
        <dataHost name="second" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1">
                        <heartbeat>select user()</heartbeat>
                <writeHost host="db1" url="172.25.27.2:3308" user="root" password="root">
                        <readHost host="db2" url="172.25.27.2:3310" user="root" password="root" />
                </writeHost>
                <writeHost host="db3" url="172.25.27.3:3308" user="root" password="root" >
                        <readHost host="db4" url="172.25.27.3:3310" user="root" password="root" />
                </writeHost>
        </dataHost>
</mycat:schema>

3.1.4、启动mycat并连接

mycat start
server.xml           ##mycat服务本身配置，包括用户，密码，端口等

mysql -uuser -puser -h172.25.27.1 -P
MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
MySQL [TESTDB]> show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| order_t          |
| user             |
+------------------+
分片集群创建库和表之前这两个表是没有用的，必须在sh1和sha2上分别创建user表和order_t表

3.1.5、在sh1和sh2上创建表，在mycat上逻辑上两个表在一个库中

前提是两个分片上都必须有taobao库，当然也可以是其他的库，这要根据schema.xml中的配置来
sh1上创建的user表
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
mysql> show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| user             |
+------------------+
mysql> desc user;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

sh2上ibian创建的order_t表
mysql> show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| order_t          |
+------------------+
mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           8 |
+-------------+
mysql> desc order_t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| goods | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+

逻辑上两个表在一块，但实际上两个表是在两个集群中

MySQL [(none)]> show databases;
+----------+
| DATABASE |
+----------+
| TESTDB   |
+----------+
1 row in set (0.00 sec)

MySQL [(none)]> use TESTDB
Database changed
MySQL [TESTDB]> show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| order_t          |
| user             |
+------------------+
2 rows in set (0.00 sec)

MySQL [TESTDB]> desc user
    -> ;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

MySQL [TESTDB]> desc order_t;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| goods | varchar(20) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

3.1.6、在mycat上向sh1和sh2插入数据

MySQL [TESTDB]> show tables;
+------------------+
| Tables_in_taobao |
+------------------+
| order_t          |
| user             |
+------------------+
2 rows in set (0.00 sec)

MySQL [TESTDB]> insert into user values(1,'zhangsan');
Query OK, 1 row affected (0.23 sec)

MySQL [TESTDB]> insert into user values(2,'lisi');
Query OK, 1 row affected (0.11 sec)

MySQL [TESTDB]> insert into user values(3,'wangwu');
Query OK, 1 row affected (0.05 sec)

MySQL [TESTDB]> insert into order_t values(1,'shouji');
Query OK, 1 row affected (0.38 sec)

MySQL [TESTDB]> insert into order_t values(2,'diannao');
Query OK, 1 row affected (0.07 sec)

MySQL [TESTDB]> insert into order_t values(3,'pingban');
Query OK, 1 row affected (0.06 sec)

MySQL [TESTDB]> select * from user;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | wangwu   |
+------+----------+
3 rows in set (0.05 sec)

MySQL [TESTDB]> select * from order_t;
+------+---------+
| id   | goods   |
+------+---------+
|    1 | shouji  |
|    2 | diannao |
|    3 | pingban |
+------+---------+
3 rows in set (0.00 sec)

3.2、分片策略

3.2.1、范围

vim /mnt/mycat/schema.xml
<table name='t1' dataNode="sh1,sh2" rule="auto-sharding-long"/>

cat /mnt/mycat/rule.xml        ##查找应该去添加什么规则
        <tableRule name="auto-sharding-long">
                <rule>
                        <columns>id</columns>
                        <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=1
6-10=0
~

测试

MySQL [TESTDB]> insert into t1 (id,name) values (1,"sddd");
Query OK, 1 row affected (0.06 sec)

MySQL [TESTDB]> insert into t1 (id,name) values (2,"sddd");
Query OK, 1 row affected (0.08 sec)

MySQL [TESTDB]> insert into t1 (id,name) values (3,"sddd");
Query OK, 1 row affected (0.05 sec)

MySQL [TESTDB]> insert into t1 (id,name) values (4,"sddd");
Query OK, 1 row affected (0.09 sec)

MySQL [TESTDB]> insert into t1 (id,name) values (5,"sddd");
Query OK, 1 row affected (0.10 sec)
 
MySQL [TESTDB]> insert into t1 (id,name) values (6,"swas");
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    1
Current database: TESTDB

Query OK, 1 row affected (0.15 sec)

MySQL [TESTDB]> insert into t1 (id,name) values (7,"swas");
Query OK, 1 row affected (0.09 sec)

MySQL [TESTDB]> insert into t1 (id,name) values (8,"swas");
Query OK, 1 row affected (0.09 sec)

MySQL [TESTDB]> insert into t1 (id,name) values (9,"swas");
Query OK, 1 row affected (0.14 sec)

MySQL [TESTDB]> insert into t1 (id,name) values (10,"swas");
Query OK, 1 row affected (0.07 sec)

mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    6 | swas |
|    7 | swas |
|    8 | swas |
|    9 | swas |
|   10 | swas |
+------+------+
5 rows in set (0.00 sec)

mysql> select @@server_id;
+-------------+
| @@server_id |
+-------------+
|           7 |
+-------------+
1 row in set (0.00 sec)


mysql> select * from t1;
+------+------+
| id   | name |
+------+------+
|    1 | sddd |
|    2 | sddd |
|    3 | sddd |
|    4 | sddd |
|    5 | sddd |
+------+------+
5 rows in set (0.00 sec)
mysql>  select @@server_id
    -> ;
+-------------+
| @@server_id |
+-------------+
|          8  |
+-------------+
1 row in set (0.00 sec)

3.2.2、取模

vim /mnt/mycat/schema.xml
    <table name='t2' dataNode="sh1,sh2" rule="mod-long"/>
cat /mnt/mycat/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>
        </function>

3.2.3、玫举

vim /mnt/mycat/schema.xml
    <table name='t3' dataNode="sh1,sh2" rule="sharding-by-intfile"/>
cat /mnt/mycat/rule.xml
        <tableRule name="sharding-by-intfile">
                <rule>
                        <columns>sharding_id</columns>
                        <algorithm>hash-int</algorithm>
                </rule>
        </tableRule>
        <function name="hash-int"
                class="io.mycat.route.function.PartitionByFileMap">
                <property name="mapFile">partition-hash-int.txt</property>
        </function>
vim /mnt/mycat/partition-hash-int.txt
    bj=0
    tj=1
    DEFAULT_NODE=0