使用MyCat进行分表分库的详细步骤

1. 环境准备

1.1 安装MySQL

准备3个MySQL实例（可以是单机多实例或不同服务器）：

• 192.168.1.100:3306 (db1)
• 192.168.1.101:3306 (db2)
• 192.168.1.102:3306 (db3)

1.2 安装MyCat

# 下载MyCat 1.6
wget http://dl.mycat.org.cn/1.6-RELEASE/Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
tar -zxvf Mycat-server-1.6-RELEASE-*.tar.gz
cd mycat

2. 创建物理库和表

在每个MySQL实例执行：

— 创建数据库
CREATE DATABASE IF NOT EXISTS db1 DEFAULT CHARSET utf8mb4;
CREATE DATABASE IF NOT EXISTS db2 DEFAULT CHARSET utf8mb4;
CREATE DATABASE IF NOT EXISTS db3 DEFAULT CHARSET utf8mb4;

— 创建分表（以订单表为例）
USE db1;
CREATE TABLE `t_order_0` (
`id` BIGINT AUTO_INCREMENT PRIMARY KEY,
`user_id` BIGINT NOT NULL,
`amount` DECIMAL(10,2) NOT NULL,
`create_time` DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE `t_order_1` LIKE `t_order_0`;

USE db2;
CREATE TABLE `t_order_2` LIKE `t_order_0`;
CREATE TABLE `t_order_3` LIKE `t_order_0`;

USE db3;
CREATE TABLE `t_order_4` LIKE `t_order_0`;
CREATE TABLE `t_order_5` LIKE `t_order_0`;

3. MyCat配置

3.1 配置server.xml

<mycat:server xmlns:mycat="http://io.mycat/">
<user name="root" defaultAccount="true">
<property name="password">123456</property>
<property name="schemas">order_db</property>
</user>
</mycat:server>

3.2 配置schema.xml

<mycat:schema xmlns:mycat="http://io.mycat/">
<!– 逻辑库 –>
<schema name="order_db" checkSQLschema="false" sqlMaxLimit="100">
<!– 分表配置 –>
<table name="t_order" dataNode="dn1,dn2,dn3" rule="mod-long" />
</schema>

<!– 数据节点 –>
<dataNode name="dn1" dataHost="host1" database="db1" />
<dataNode name="dn2" dataHost="host2" database="db2" />
<dataNode name="dn3" dataHost="host3" database="db3" />

<!– 物理主机配置 –>
<dataHost name="host1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="192.168.1.100:3306" user="root" password="mysql123" />
</dataHost>

<dataHost name="host2" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM2" url="192.168.1.101:3306" user="root" password="mysql123" />
</dataHost>

<dataHost name="host3" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM3" url="192.168.1.102:3306" user="root" password="mysql123" />
</dataHost>
</mycat:schema>

3.3 配置rule.xml

<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="mod-long">
<rule>
<columns>user_id</columns>
<algorithm>mod-long</algorithm>
</rule>
</tableRule>

<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!– 分片数量 –>
<property name="count">3</property>
</function>
</mycat:rule>

4. 启动MyCat

./bin/mycat start
tail -f logs/mycat.log # 查看启动日志

5. Java代码示例

5.1 Maven依赖

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.28</version>
</dependency>

5.2 连接配置

public class MyCatDataSource {
private static final String URL = "jdbc:mysql://localhost:8066/order_db?useSSL=false&serverTimezone=UTC";
private static final String USER = "root";
private static final String PASSWORD = "123456";

public static Connection getConnection() throws SQLException {
return DriverManager.getConnection(URL, USER, PASSWORD);
}
}

5.3 插入数据

public class OrderDAO {
public void insertOrder(Long userId, BigDecimal amount) {
String sql = "INSERT INTO t_order (user_id, amount) VALUES (?, ?)";

try (Connection conn = MyCatDataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {

pstmt.setLong(1, userId);
pstmt.setBigDecimal(2, amount);
pstmt.executeUpdate();

System.out.println("Order inserted for user: " + userId);
} catch (SQLException e) {
e.printStackTrace();
}
}
}

5.4 查询数据

public class OrderQuery {
public void findByUserId(Long userId) {
String sql = "SELECT * FROM t_order WHERE user_id = ?";

try (Connection conn = MyCatDataSource.getConnection();
PreparedStatement pstmt = conn.prepareStatement(sql)) {

pstmt.setLong(1, userId);
ResultSet rs = pstmt.executeQuery();

while (rs.next()) {
System.out.printf("ID: %d, User: %d, Amount: %.2f%n",
rs.getLong("id"),
rs.getLong("user_id"),
rs.getBigDecimal("amount"));
}
} catch (SQLException e) {
e.printStackTrace();
}
}
}

6. 测试验证

6.1 插入测试数据

public class Main {
public static void main(String[] args) {
OrderDAO dao = new OrderDAO();

// 插入10条数据
for (long i = 1; i <= 10; i++) {
dao.insertOrder(i, new BigDecimal("100.00"));
}
}
}

6.2 验证分片

连接MyCat端口查看数据分布：

mysql -uroot -p123456 -h127.0.0.1 -P8066 -Dorder_db

执行查询：

— 查看总数据
SELECT COUNT(*) FROM t_order;

— 查看具体分片
EXPLAIN SELECT * FROM t_order WHERE user_id = 5;

7. 注意事项

8. 扩容方案

当需要扩展到6个分片时：

这个示例展示了MyCat的完整分库分表实现，实际生产环境需要根据业务特点调整分片策略和配置参数。