Mysql分库分表和主从复制

MyCat 实现分库分表

MySQL 的分库分表解决方案通常依赖于中间件来实现水平扩展。常见的中间件有以下几种

MyCat
ShardingSphere
TDDL (Taobao Distributed Data Layer)
Cobar
Vitess

MyCat 是一款开源的数据库中间件，支持 MySQL 数据库的分库分表功能。

使用 MyCat 实现分库分表的过程包括多个步骤，涉及配置 MyCat 和数据库的分片策略，路由规则等。下面通过一个实际案例来详细解释 MyCat 的实现原理、方式和步骤。

案例背景

假设你有一个电商系统，包含一个 orders 表，记录用户的订单信息。随着数据量的增长，单一数据库难以满足性能需求，因此需要进行分库分表。

实现原理

MyCat 是一种数据库中间件，支持分库分表。它的原理是通过代理 MySQL 连接，将 SQL 请求转发到实际的数据库实例上。MyCat 会根据预定义的路由规则（如分片策略）来决定将查询请求路由到哪个数据库或表。

MyCat 的 分库分表原理如下：

分库：将数据按照某种规则分配到多个数据库实例中。例如，按用户的 ID 来分库。

分表：将一个大表拆分成多个小表，避免单表的数据过大导致查询性能下降。例如，可以按时间范围或数据量来分表。

实现方式

MyCat 的实现方式主要通过配置分片规则来实现分库分表。配置内容包括：

数据源配置：定义数据库实例。
分片规则配置：指定哪些字段用于分片，如何分片（按范围、按哈希等）。
路由规则配置：根据 SQL 查询的条件来路由请求到不同的数据库或表。

实现步骤

安装并启动 MyCat
1. 下载 MyCat 安装包并解压。
2. 配置 MyCat 启动脚本，启动 MyCat。
```
cd MyCat
./bin/start.sh
```
配置数据源在 MyCat 的配置文件 conf/context.xml 中，定义数据源。假设我们有两个 MySQL 实例 db1 和 db2，分别存储不同的 orders 表。

<Context>
  <DataSource name="ds1" url="jdbc:mysql://localhost:3306/db1" user="root" password="password"/>
  <DataSource name="ds2" url="jdbc:mysql://localhost:3306/db2" user="root" password="password"/>
</Context>

配置分片规则在 conf/schema.xml 中配置分片策略。假设我们将 orders 表按照订单 ID 来进行分库分表，并且将订单 ID 对 2 取模来决定存储在哪个数据库和表中。

<schema name="mydb">
  <table name="orders">
    <shard key="order_id">
      <algorithm>hash</algorithm>  <!-- 按哈希值分表 -->
      <shard-count>2</shard-count>  <!-- 将数据分成 2 张表 -->
      <database>ds1</database>
      <database>ds2</database>
    </shard>
  </table>
</schema>

shard key 指定分片的字段，这里是 order_id。
lgorithm 指定分片算法，这里使用哈希算法。
shard-count 指定表的数量，这里是 2。
database 列出参与分片的数据库实例，ds1 和 ds2。

配置路由规则在 MyCat 中，路由规则定义了如何根据 SQL 的查询条件选择具体的数据库和表。例如，当查询 orders 表时，MyCat 会根据 order_id 的值来决定查询哪个数据库和表。

在 conf/mapper.xml 中配置路由规则：

<mapper>
  <select id="selectOrder" target="orders">
    SELECT * FROM orders WHERE order_id = #{order_id}
  </select>
</mapper>

配置分片后的表为了确保 MyCat 可以将数据存储到多个表中，你需要在 db1 和 db2 中创建对应的 orders 表。

-- 在 db1 中创建表
CREATE TABLE orders_0 (
  order_id INT PRIMARY KEY,
  order_date DATE,
  total DECIMAL(10, 2)
);

-- 在 db2 中创建表
CREATE TABLE orders_1 (
  order_id INT PRIMARY KEY,
  order_date DATE,
  total DECIMAL(10, 2)
);

这里，我们将 orders 表分成了 orders_0 和 orders_1，分别存储在 db1 和 db2 中。

配置 SQL 执行现在，你可以通过 MyCat 执行 SQL 查询了。假设你通过客户端（如 MySQL Workbench 或应用程序）连接到 MyCat，执行如下查询：

SELECT * FROM orders WHERE order_id = 1001;

MyCat 会根据 order_id 的值计算哈希值，决定将查询路由到 orders_0 或 orders_1 表，并返回相应的结果。

测试分库分表

插入数据：通过 MyCat 插入一些订单数据，确保数据正确地分布到不同的数据库和表中。

INSERT INTO orders (order_id, order_date, total) VALUES (1001, '2025-02-19', 99.99);
INSERT INTO orders (order_id, order_date, total) VALUES (1002, '2025-02-20', 59.99);

查询数据：通过 SQL 查询来验证数据是否被正确路由和查询。

SELECT * FROM orders WHERE order_id = 1001;

总结

使用 MyCat 实现分库分表的步骤可以概括为：

配置数据源：指定数据库实例。配置分片规则：定义如何根据字段值进行分片。配置路由规则：根据 SQL 查询条件进行路由。配置数据库和表：在数据库中创建分片后的表。执行查询和操作：通过 MyCat 执行查询，验证分片是否成功。通过 MyCat，可以方便地在应用层实现分库分表，并且能够支持复杂的分片策略，适应高并发、大数据量的业务场景。

主从复制

1. 主从复制的基本原理

主从复制是一种数据同步方式，主数据库（Master）负责处理写操作，从数据库（Slave）复制主数据库的数据，负责处理读操作。通过这种方式，可以实现读写分离，提高数据库的并发处理能力。

2. 主从复制与分库分表结合的目标

高可用性：主从复制可以保证数据库在主库出现故障时，从库可以接管，确保系统的高可用性。
读写分离：主库负责处理所有写操作（如数据插入、更新、删除），从库负责处理读操作（如查询）。这能显著提高系统的吞吐量，特别是对于读操作频繁的场景。
负载均衡：通过负载均衡策略，多个从库可以分担读操作的压力，提高系统的并发处理能力。
数据一致性：通过主从复制保证数据在多个库之间的一致性。

3. 分库分表 + 主从复制的架构设计

假设你的系统有多个分库（例如，db1, db2）和每个分库都配置了主从复制。这样就形成了分库分表 + 主从复制的架构，结构大致如下：

db1_master 和 db1_slave：db1_master 是主库，负责写操作；db1_slave 是从库，负责读操作。
db2_master 和 db2_slave：db2_master 是主库，负责写操作；db2_slave 是从库，负责读操作。

4. 实现步骤

步骤 1：配置 MySQL 的主从复制

配置主库（Master）：在 db1_master 和 db2_master 上启用二进制日志（binlog），以便从库能够同步数据。

[mysqld]
server-id = 1   # 每个主库必须唯一
log-bin = mysql-bin  # 启用二进制日志
binlog-do-db = your_database  # 需要复制的数据库

配置从库（Slave）：在 db1_slave 和 db2_slave 上配置从库，指向主库进行数据同步。

[mysqld]
 server-id = 2  # 每个从库必须唯一
 relay-log = mysql-relay-bin  # 启用中继日志
 log-bin = mysql-bin  # 启用二进制日志
 read-only = 1  # 设置为只读

启动复制进程：在从库上执行以下命令启动复制：

 CHANGE MASTER TO
 MASTER_HOST='db1_master',  # 指定主库地址
 MASTER_USER='replication_user',  # 指定复制账号
 MASTER_PASSWORD='password',  # 指定复制账号密码
 MASTER_LOG_FILE='mysql-bin.000001',  # 从哪个日志文件开始复制
 MASTER_LOG_POS=  106;  # 从哪个位置开始复制
 START SLAVE;  # 启动复制

验证复制状态：在从库执行以下命令验证复制是否正常：
```
SHOW SLAVE STATUS\G  # 查看复制状态
```

需要确保 Slave_IO_Running 和 Slave_SQL_Running 都为 Yes，表示复制正常。

步骤 2：配置 MyCat 路由规则（分库分表 + 主从复制）

配置数据源：在 context.xml 中配置主库和从库的数据源。

<Context>
  <!-- db1 主从复制 -->
  <DataSource name="ds1_master" url="jdbc:mysql://localhost:3306/db1_master" user="root" password="password"/>
  <DataSource name="ds1_slave" url="jdbc:mysql://localhost:3306/db1_slave" user="root" password="password"/>
  <!-- db2 主从复制 -->
  <DataSource name="ds2_master" url="jdbc:mysql://localhost:3306/db2_master" user="root" password="password"/>
  <DataSource name="ds2_slave" url="jdbc:mysql://localhost:3306/db2_slave" user="root" password="password"/>
</Context>

配置分片规则和路由规则：在 schema.xml 中配置分片规则时，可以指定读写分离策略。对于写操作（如 INSERT、UPDATE、DELETE），路由到主库；对于读操作（如 SELECT），路由到从库。

<schema name="mydb">
<table name="orders">
    <shard key="order_id">
    <algorithm>hash</algorithm>
    <shard-count>2</shard-count>
    <!-- 读写分离 -->
    <database>ds1_master</database>  <!-- 写操作走主库 -->
    <database>ds1_slave</database>   <!-- 读操作走从库 -->
    </shard>
</table>
</schema>

这里的 ds1_master 和 ds1_slave 对应主从库。
当执行写操作时，MyCat 会将请求路由到主库 ds1_master。
当执行读操作时，MyCat 会将请求路由到从库 ds1_slave。

配置负载均衡： MyCat 可以配置多个从库来实现读操作的负载均衡。你可以配置多个从库，MyCat 会根据负载均衡策略将读操作分发到不同的从库。

<database>ds1_slave_1</database>
<database>ds1_slave_2</database>

这样，多个从库可以分担读请求的压力，提升读操作的并发能力。

步骤 3：验证读写分离效果

执行写操作：向 orders 表插入数据，MyCat 会将写操作路由到主库 ds1_master。

INSERT INTO orders (order_id, order_date, total) VALUES (1001, '2025-02-19', 99.99);

执行读操作：查询订单数据，MyCat 会将读操作路由到从库 ds1_slave（或负载均衡的从库）。

SELECT * FROM orders WHERE order_id = 1001;

步骤 4：监控与扩展

监控数据库性能： 使用 MyCat 和 MySQL 自带的监控工具，监控数据库的性能，确保主从复制、读写分离和负载均衡的正常运行。
扩展从库： 随着读操作量的增加，可以增加更多的从库，MyCat 会根据负载均衡策略自动分发读请求。
故障恢复： 如果某个主库出现故障，可以通过切换主库和从库来保证系统的高可用性，确保读写操作的正常进行。

5. 总结

通过将 分库分表 和 主从复制 结合，MyCat 实现了高并发的读写分离架构：

主库负责写操作，确保数据的完整性。从库负责读操作，分担查询压力，提升系统的并发能力。多个从库可以进一步扩展，提高读操作的处理能力。主从复制保证数据一致性，并且提供故障转移机制，确保系统的高可用性。这种架构对于大数据量和高并发的场景非常适用，可以有效提升数据库的性能和可靠性。

Mysql分库分表和主从复制#

MyCat 实现分库分表#

案例背景#

实现原理#

实现方式#

实现步骤#

测试分库分表#

总结#

主从复制#

1. 主从复制的基本原理#

2. 主从复制与分库分表结合的目标#

3. 分库分表 + 主从复制的架构设计#

4. 实现步骤#

步骤 1：配置 MySQL 的主从复制#

步骤 2：配置 MyCat 路由规则（分库分表 + 主从复制）#

步骤 3：验证读写分离效果#

步骤 4：监控与扩展#

5. 总结#