MySQL数据库基于MyCat 分库分表哪些事
MySQL安装部署（略），下面主要说下MyCat的安装部署：
从github下载并解压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 并授权
然后在每个数据库里执行以下脚本：
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 |
+------------------+------------------+由于我们公司的数据量级太小，数据库又是直接买的云服务器高可用版，没有机会进行分库分表实战，特此实践一把。
MySQL数据库基于MyCat 分库分表哪些事
https://cn.10691.cn//archives/10061