MyCat 分库分表实践

最新推荐文章于 2022-06-30 14:03:45 发布

XYZip2

最新推荐文章于 2022-06-30 14:03:45 发布

阅读量178

点赞数

分类专栏： # 数据库文章标签： mysql 数据库 mycat

本文链接：https://blog.csdn.net/CaBCo/article/details/120313988

版权

数据库专栏收录该内容

9 篇文章 0 订阅

订阅专栏

服务部署

MySQL & MyCat

MySQL安装部署（略），下面主要说下MyCat的安装部署：

github下载MyCat：「链接」

解压MyCat安装包 (由于服务器局限，我选择windows本地安装)

配置MyCat：cd mycat/conf

server.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:server SYSTEM "server.dtd">
<mycat:server xmlns:mycat="http://io.mycat/">
	<system>
		<property name="useSqlStat">0</property>  
		<property name="useGlobleTableCheck">0</property> 
		<property name="sequnceHandlerType">2</property>
		<property name="processorBufferPoolType">0</property>
		<property name="useOffHeapForMerge">1</property>
		<property name="memoryPageSize">1m</property>
		<property name="spillsFileBufferSize">1k</property>
		<property name="useStreamOutput">0</property>
		<property name="systemReserveMemorySize">384m</property>
	</system>
	
 <!--MyCat 客户端登录用户名及密码：admin/admin123 -->
	<user name="admin">
		<property name="password">admin123</property>
   <!--这里的schemas要与schema.xml文件中定义的逻辑库名称保持一致 -->
		<property name="schemas">messagedb</property>
	</user>
</mycat:server>

schema.xml

<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
  <!--逻辑库定义 -->
	<schema name="messagedb" checkSQLschema="false" sqlMaxLimit="100">
      <!--message， source 为数据库逻辑表；分片规则取rule.xml中的声明，这里按月分片 -->
		  <table name="message" dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10,dn11,dn12" 
                  rule="sharding-by-month" />
		  <!--type="global"表示是一张全局表，如一些基础数据表，每个分片上维护一份，便于join查询 -->
      <table name="source" primaryKey="id" type="global" dataNode="dn1,dn2,dn3,dn4,dn5,dn6,dn7,dn8,dn9,dn10,dn11,dn12" />
	</schema>
	
 <!--dataNode定义分片，dataHost指定数据库实例， database指定数据库实例下具体的数据库 -->
	<dataNode name="dn1" dataHost="mysql-01" database="message202101" />
	<dataNode name="dn2" dataHost="mysql-01" database="message202102" />
	<dataNode name="dn3" dataHost="mysql-01" database="message202103" />
	<dataNode name="dn4" dataHost="mysql-01" database="message202104" />
	<dataNode name="dn5" dataHost="mysql-02" database="message202105" />
	<dataNode name="dn6" dataHost="mysql-02" database="message202106" />
	<dataNode name="dn7" dataHost="mysql-02" database="message202107" />
	<dataNode name="dn8" dataHost="mysql-02" database="message202108" />
	<dataNode name="dn9" dataHost="mysql-03" database="message202109" />
	<dataNode name="dn10" dataHost="mysql-03" database="message202110" />
	<dataNode name="dn11" dataHost="mysql-03" database="message202111" />
	<dataNode name="dn12" dataHost="mysql-03" database="message202112" />
    
	<dataHost name="mysql-01" maxCon="1000" minCon="10" balance="0"
					  writeType="0" dbType="mysql" dbDriver="native" switchType="-1">
    <!--心跳检测方法 -->
		<heartbeat>select user()</heartbeat>
    <!--writeHost 标签内可以定义readHost标签，实现读写分离 -->
		<writeHost host="hostM1" url="192.168.2.8:3306" user="root" password="root"></writeHost>
	</dataHost>
	<dataHost name="mysql-02" maxCon="1000" minCon="10" balance="0"
					  writeType="0" dbType="mysql" dbDriver="native" switchType="-1">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM2" url="192.168.2.9:3306" user="root" password="root"></writeHost>
	</dataHost>
	<dataHost name="mysql-03" maxCon="1000" minCon="10" balance="0"
					  writeType="0" dbType="mysql" dbDriver="native" switchType="-1">
		<heartbeat>select user()</heartbeat>
		<writeHost host="hostM3" url="192.168.2.143:3306" user="root" password="root"></writeHost>
	</dataHost>
</mycat:schema>

rule.xml

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
  
	 <tableRule name="sharding-by-month">
		  <rule>
			  <columns>create_time</columns>
			  <algorithm>partbymonth</algorithm>
		  </rule>
	  </tableRule>

    <function name="partbymonth" class="io.mycat.route.function.PartitionByMonth">
    <!--这里的日期格式定义具体参考完整版rule.xml文件中示例 -->
		<property name="dateFormat">yyyy-MM-dd</property>
		<property name="sBeginDate">2021-01-01</property>

	</function>
</mycat:rule>

建库建表

分别在192.168.2.8 / 192.168.2.9 / 192.168.2.143 三个服务节点上部署MySQL服务，

启动后创建用户root / root 并授权

192.168.2.8

192.168.2.9

192.168.2.143

然后在每个数据库里执行以下脚本：

create table source (
	id int(11) not null auto_increment primary key comment 'pk',
	name varchar(10) default '' comment 'source name'
);

create table message (
	id int(11) not null auto_increment primary key comment 'pk',
	content varchar(255) default '' comment 'message content',
	create_time date default null,
	source_id int(11) not null,
	foreign key(source_id) references source(id)
);

insert into `source`(`id`,`name`) values(1,'weibo');
insert into `source`(`id`,`name`) values(2,'weixin');
insert into `source`(`id`,`name`) values(3,'qq');
insert into `source`(`id`,`name`) values(4,'email');
insert into `source`(`id`,`name`) values(5,'sms');

启动MyCat

以管理员身份运行cmd:

mycat\bin>mycat.bat start
wrapper  | The Mycat-server service is already running with status: RUNNING

连接检查

先说说MyCat的两个端口：9066端口是管理端口，提供查看当前系统节点的情况，报告心跳状态等相关系统监控的功能，8066是数据端口，相当于数据库的访问端口

[root@master local]# mysql -uadmin -padmin123 -P9066 -h192.168.2.136
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (monitor)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show @@datanode;
+------+------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| NAME | DATHOST                | INDEX | TYPE  | ACTIVE | IDLE | SIZE | EXECUTE | TOTAL_TIME | MAX_TIME | MAX_SQL | RECOVERY_TIME |
+------+------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
| dn1  | mysql-01/message202101 |     0 | mysql |      0 |    6 | 1000 |     974 |          0 |        0 |       0 |            -1 |
| dn10 | mysql-03/message202110 |     0 | mysql |      0 |    2 | 1000 |       8 |          0 |        0 |       0 |            -1 |
| dn11 | mysql-03/message202111 |     0 | mysql |      0 |    0 | 1000 |      11 |          0 |        0 |       0 |            -1 |
| dn12 | mysql-03/message202112 |     0 | mysql |      0 |    8 | 1000 |     975 |          0 |        0 |       0 |            -1 |
| dn2  | mysql-01/message202102 |     0 | mysql |      0 |    2 | 1000 |      13 |          0 |        0 |       0 |            -1 |
| dn3  | mysql-01/message202103 |     0 | mysql |      0 |    3 | 1000 |       8 |          0 |        0 |       0 |            -1 |
| dn4  | mysql-01/message202104 |     0 | mysql |      0 |    2 | 1000 |       8 |          0 |        0 |       0 |            -1 |
| dn5  | mysql-02/message202105 |     0 | mysql |      0 |    4 | 1000 |     976 |          0 |        0 |       0 |            -1 |
| dn6  | mysql-02/message202106 |     0 | mysql |      0 |    2 | 1000 |       8 |          0 |        0 |       0 |            -1 |
| dn7  | mysql-02/message202107 |     0 | mysql |      0 |    7 | 1000 |      11 |          0 |        0 |       0 |            -1 |
| dn8  | mysql-02/message202108 |     0 | mysql |      0 |    0 | 1000 |       8 |          0 |        0 |       0 |            -1 |
| dn9  | mysql-03/message202109 |     0 | mysql |      0 |    2 | 1000 |       8 |          0 |        0 |       0 |            -1 |
+------+------------------------+-------+-------+--------+------+------+---------+------------+----------+---------+---------------+
12 rows in set (0.02 sec)

mysql> show @@heartbeat;
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| NAME   | TYPE  | HOST          | PORT | RS_CODE | RETRY | STATUS | TIMEOUT | EXECUTE_TIME | LAST_ACTIVE_TIME    | STOP  |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
| hostM2 | mysql | 192.168.2.9   | 3306 |       1 |     0 | idle   |       0 | 1,1,1        | 2021-09-15 17:34:04 | false |
| hostM1 | mysql | 192.168.2.8   | 3306 |       1 |     0 | idle   |       0 | 1,1,1        | 2021-09-15 17:34:04 | false |
| hostM3 | mysql | 192.168.2.143 | 3306 |       1 |     0 | idle   |       0 | 1,2,1        | 2021-09-15 17:34:04 | false |
+--------+-------+---------------+------+---------+-------+--------+---------+--------------+---------------------+-------+
3 rows in set (0.01 sec)

如果看到各个节点都已经出现，并且心跳状态RS_CODE=1，则表示后端数据库连接正常。

再连接到数据端口：可以看到只有一个逻辑库messagedb,

MyCat会根据schema.xml 定义的关系库及rule.xml中定义的分片规则进行分库分表

[root@master local]# mysql -uadmin -padmin123 -P8066 -h192.168.2.136
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 5.6.29-mycat-1.6-RELEASE-20161028204710 MyCat Server (OpenCloundDB)
Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+-----------+
| DATABASE  |
+-----------+
| messagedb |
+-----------+
1 row in set (0.00 sec)

分库分表验证测试

下面通过JDBC的方式批量插入数据，测试是否能按月份自动分库分表插入

import java.sql.*;
import java.util.Calendar;
import java.util.Random;

public class TestMyCat {

    private static final String driver = "com.mysql.jdbc.Driver";
    private static final String url = "jdbc:mysql://192.168.2.136:8066/messagedb?useServerPrepStmts=false&rewriteBatchedStatements=true";
    private static final String username = "admin";
    private static final String password = "admin123";

    public static void main(String[] atgs) throws SQLException {

        Calendar calendar = Calendar.getInstance();
        Random random = new Random();
        calendar.set(2021, Calendar.JANUARY, 1, 0, 0, 0);
        long beginTime = calendar.getTimeInMillis();

        Connection connection = null;
        PreparedStatement ps = null;
        try {
            Class.forName(driver);
            connection = DriverManager.getConnection(url, username, password);
            connection.setAutoCommit(false);
            String sql = "insert into message(`content`, `create_time`, `source_id`) values(?,?,?)";
            ps = connection.prepareStatement(sql);
            long start = System.currentTimeMillis();
            for (int i = 0; i < 10000; i++) {
                ps.setString(1, System.currentTimeMillis() + "");
                long randomDay = random.nextInt(365);
                long randomTime = beginTime + randomDay * 86400 * 1000;
                Date date = new Date(randomTime);
                int source_id = random.nextInt(5) + 1;
                ps.setDate(2, date);
                ps.setInt(3, source_id);
                ps.addBatch();
                if (i != 0 && i % 1000 == 0) {
                    System.out.println("execute batch : " + i);
                    ps.executeBatch();
                }
            }
            ps.executeBatch();
            connection.commit();
            System.out.println(System.currentTimeMillis() - start);
        } catch (SQLException | ClassNotFoundException e) {
            e.printStackTrace();
        } finally {
            if (ps != null)
                ps.close();
            if (connection != null)
                connection.close();
        }
    }
}

mysql -uroot -proot -P3306 -h192.168.2.8 -e "select min(create_time), max(create_time) from message202101.message;"
Warning: Using a password on the command line interface can be insecure.
+------------------+------------------+
| min(create_time) | max(create_time) |
+------------------+------------------+
| 2021-01-01       | 2021-01-31       |
+------------------+------------------+
[root@master local]# mysql -uroot -proot -P3306 -h192.168.2.8 -e "select min(create_time), max(create_time) from message202102.message;"
Warning: Using a password on the command line interface can be insecure.
+------------------+------------------+
| min(create_time) | max(create_time) |
+------------------+------------------+
| 2021-02-01       | 2021-02-28       |
+------------------+------------------+
[root@master local]# mysql -uroot -proot -P3306 -h192.168.2.8 -e "select min(create_time), max(create_time) from message202103.message;"
Warning: Using a password on the command line interface can be insecure.
+------------------+------------------+
| min(create_time) | max(create_time) |
+------------------+------------------+
| 2021-03-01       | 2021-03-31       |
+------------------+------------------+
[root@master local]# mysql -uroot -proot -P3306 -h192.168.2.8 -e "select min(create_time), max(create_time) from message202104.message;"
Warning: Using a password on the command line interface can be insecure.
+------------------+------------------+
| min(create_time) | max(create_time) |
+------------------+------------------+
| 2021-04-01       | 2021-04-30       |
+------------------+------------------+

由于我们公司的数据量级太小，数据库又是直接买的云服务器高可用版，没有机会进行分库分表实战。

以上参照大佬的文章：https://mp.weixin.qq.com/s/9eEX2OWaOTNCjemOb7R8jQ
亲自实践了一把，感谢大牛的分享！

后续有时间了再探究下读写分离。

XYZip2

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MyCat 分库分表实践

服务部署MySQL & MyCatMySQL安装部署（略），下面主要说下MyCat的安装部署：github下载MyCat：「链接」解压MyCat安装包 (由于服务器局限，我选择windows本地安装)配置MyCat：cd mycat/confserver.xml<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE mycat:server SYSTEM "server.dtd"><myc..
复制链接

扫一扫

专栏目录