发布于 由admin

MySQL高阶讲座之：`MySQL`的`Logical Replication`：`Binlog`解析与`Row-Based`复制的性能。

各位观众老爷，晚上好！我是你们的老朋友，今天咱们聊聊MySQL逻辑复制里头的那些事儿，特别是Binlog的解析以及Row-Based复制的性能问题。保证让大家听得懂，学得会，还能拿出去吹牛逼！

一、开场白：什么是Logical Replication？

啥是逻辑复制？简单来说，就是把数据库的变更（增删改）以逻辑的形式记录下来，然后应用到其他的数据库上。这跟物理复制（比如基于磁盘镜像）不一样，它更灵活，可以跨版本、跨平台。MySQL的逻辑复制主要依赖的就是Binlog（Binary Log）。

二、Binlog：一切故事的起点

Binlog，顾名思义，就是二进制日志，记录了数据库里所有的数据变更操作。要想搞明白逻辑复制，就得先搞懂Binlog。

  1. Binlog的格式

    Binlog有三种格式：

    • Statement-Based Replication (SBR)：记录SQL语句。
    • Row-Based Replication (RBR)：记录每一行数据的变化。
    • Mixed-Based Replication (MBR)：混合模式，MySQL自行决定使用SBR还是RBR。

    可以通过show variables like 'binlog_format';查看当前的Binlog格式。

    mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

  2. Binlog的内容

    Binlog里面记录了啥？举个例子：

    # 创建表
CREATE TABLE `users` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 `name` varchar(255) DEFAULT NULL,
 `age` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

# 插入数据
INSERT INTO `users` (`name`, `age`) VALUES ('张三', 20);

# 更新数据
UPDATE `users` SET `age` = 21 WHERE `id` = 1;

# 删除数据
DELETE FROM `users` WHERE `id` = 1;

    这些操作都会被记录到Binlog里。 具体记录的内容取决于Binlog的格式。

  3. 如何查看Binlog？

    MySQL自带了一个工具：mysqlbinlog，可以用来查看Binlog的内容。

    mysqlbinlog mysql-bin.000001  # 查看第一个Binlog文件
mysqlbinlog --start-datetime="2023-10-26 00:00:00" --stop-datetime="2023-10-26 23:59:59" mysql-bin.000001 #查看指定时间段内的Binlog

    不过直接用mysqlbinlog看，内容比较晦涩，不太直观。可以使用一些图形化的工具，比如canal或者Maxwell，它们可以把Binlog解析成更易读的格式。

三、Binlog解析：从二进制到可理解

Binlog是二进制的，要想利用它做逻辑复制，就得先把它解析成能理解的数据结构。这个过程就是Binlog解析。

  1. Binlog解析的原理

    Binlog解析器会读取Binlog文件，按照Binlog的格式（SBR、RBR或MBR）解析出其中的事件。每个事件都包含了数据库变更的信息，比如表名、操作类型（INSERT、UPDATE、DELETE）以及变更的数据。

  2. 常用的Binlog解析工具

    • Canal：阿里巴巴开源的，专门用于MySQL Binlog解析。
    • Maxwell：Java编写的，可以将Binlog数据转换成JSON格式。
    • Debezium：一个分布式变更数据捕获平台，支持多种数据库。

    这些工具都提供了API，方便开发者集成到自己的应用中。

  3. 一个简单的Canal Demo

    这里简单演示一下Canal的使用（需要安装Canal Server）：

    (1) 配置Canal Client

    import com.alibaba.otter.canal.client.CanalConnector;
import com.alibaba.otter.canal.client.CanalConnectors;
import com.alibaba.otter.canal.common.utils.AddressUtils;
import com.alibaba.otter.canal.protocol.CanalEntry;
import com.alibaba.otter.canal.protocol.Message;
import java.net.InetSocketAddress;
import java.util.List;

public class SimpleCanalClient {

   public static void main(String[] args) {
       // 创建连接
       CanalConnector connector = CanalConnectors.newSingleConnector(new InetSocketAddress(AddressUtils.getHostAddress(),
           11111), "example", "", "");

       try {
           connector.connect();
           connector.subscribe(".*\..*"); // 订阅所有数据库的所有表

           while (true) {
               Message message = connector.getWithoutAck(100); // 获取指定数量的数据
               long batchId = message.getId();
               int size = message.getEntries().size();
               if (batchId == -1 || size == 0) {
                   try {
                       Thread.sleep(1000);
                   } catch (InterruptedException e) {
                       // ignore
                   }
               } else {
                   printEntry(message.getEntries());
               }

               connector.ack(batchId); // 提交确认
               // connector.rollback(batchId); // 处理失败, 回滚数据
           }
       } finally {
           connector.disconnect();
       }
   }

   private static void printEntry(List<CanalEntry.Entry> entrys) {
       for (CanalEntry.Entry entry : entrys) {
           if (entry.getEntryType() == CanalEntry.EntryType.TRANSACTIONBEGIN || entry.getEntryType() == CanalEntry.EntryType.TRANSACTIONEND) {
               continue;
           }

           CanalEntry.RowChange rowChange;
           try {
               rowChange = CanalEntry.RowChange.parseFrom(entry.getStoreValue());
           } catch (Exception e) {
               throw new RuntimeException("ERROR ## parser of eromanga-event has an error , data:" + entry.toString(),
                   e);
           }

           CanalEntry.EventType eventType = rowChange.getEventType();
           System.out.println(String.format("================> binlog[%s:%s] , name[%s,%s] , eventType : %s",
               entry.getHeader().getLogfileName(), entry.getHeader().getLogfileOffset(),
               entry.getHeader().getSchemaName(), entry.getHeader().getTableName(),
               eventType));

           for (CanalEntry.RowData rowData : rowChange.getRowDatasList()) {
               if (eventType == CanalEntry.EventType.DELETE) {
                   printColumn(rowData.getBeforeColumnsList());
               } else if (eventType == CanalEntry.EventType.INSERT) {
                   printColumn(rowData.getAfterColumnsList());
               } else {
                   System.out.println("-------> before");
                   printColumn(rowData.getBeforeColumnsList());
                   System.out.println("-------> after");
                   printColumn(rowData.getAfterColumnsList());
               }
           }
       }
   }

   private static void printColumn(List<CanalEntry.Column> columns) {
       for (CanalEntry.Column column : columns) {
           System.out.println(column.getName() + " : " + column.getValue() + "    update=" + column.getUpdated());
       }
   }
}

    (2) 运行上面的代码，就可以实时监听数据库的变更，并打印出来。

四、Row-Based Replication (RBR)：性能的爱与恨

RBR，也就是基于行的复制，记录的是每一行数据的变化。相对SBR，它有优点也有缺点。

  1. RBR的优点

    • 更可靠：避免了SBR中SQL语句执行结果不一致的问题（比如使用了NOW()函数）。
    • 适用性更广：对于复杂的存储过程、触发器等，RBR也能很好地支持。
    • 减少锁竞争：在某些场景下，RBR可以减少锁的竞争，提高并发性能。

  2. RBR的缺点

    • Binlog体积更大：记录每一行数据的变化，Binlog体积会比SBR大很多。
    • 网络传输压力更大：Binlog体积大，导致网络传输的压力也更大。
    • 解析成本更高：解析RBR的Binlog，需要更多的CPU和内存资源。

  3. RBR的性能优化

    • 减少不必要的Binlog记录：可以通过binlog_ignore_db参数，忽略不需要复制的数据库。
    • 优化网络传输：使用压缩算法，减少网络传输的数据量。
    • 选择合适的Binlog解析工具：不同的解析工具，性能差异很大，需要根据实际情况选择。
    • 调整MySQL参数：例如binlog_cache_size，max_binlog_size等。

    下面是一些关键参数的说明：

    参数名 描述 默认值 建议
    binlog_cache_size 用于在内存中缓存Binlog的事务大小，如果事务较大，需要增加这个值。 32K 根据实际情况调整，过大会占用过多内存，过小会频繁写入磁盘。
    max_binlog_size 单个Binlog文件的最大大小，超过这个值会自动切换到下一个Binlog文件。 1G 根据磁盘空间和备份策略调整，过大会导致恢复时间变长，过小会导致Binlog文件过多。
    sync_binlog 控制Binlog写入磁盘的频率，sync_binlog=1表示每次事务都写入磁盘，sync_binlog=0表示由操作系统决定。 0 建议设置为1，保证数据安全，但会降低性能。如果对数据安全性要求不高，可以设置为0，但有丢失数据的风险。
    binlog_row_image 控制RBR模式下，Binlog记录哪些行数据，FULL表示记录所有列，MINIMAL表示只记录修改的列。 FULL 如果只需要记录修改的列，可以设置为MINIMAL，减少Binlog体积。
    binlog_transaction_dependency_tracking 用于控制事务依赖关系追踪，可以优化并行复制的性能。 COMMIT_ORDER 根据实际情况调整，可以提高并行复制的性能。

  4. RBR应用场景案例分析

    假设我们有一个电商平台的订单系统，需要将订单数据同步到数据仓库进行分析。订单表orders结构如下：

    CREATE TABLE `orders` (
`order_id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '订单ID',
`user_id` bigint(20) NOT NULL COMMENT '用户ID',
`product_id` bigint(20) NOT NULL COMMENT '商品ID',
`order_time` datetime NOT NULL COMMENT '下单时间',
`order_amount` decimal(10,2) NOT NULL COMMENT '订单金额',
`order_status` tinyint(4) NOT NULL COMMENT '订单状态',
PRIMARY KEY (`order_id`),
KEY `idx_user_id` (`user_id`),
KEY `idx_product_id` (`product_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单表';

    由于订单数据量非常大，我们需要使用RBR模式进行数据同步，保证数据的一致性和可靠性。

    (1) 配置MySQL主库开启Binlog，并设置为RBR模式。

    [mysqld]
log-bin=mysql-bin
binlog_format=ROW
server-id=1
sync_binlog=1
innodb_flush_log_at_trx_commit=1

    (2) 使用Canal或者Debezium等工具监听Binlog，并将数据同步到数据仓库。

    (3) 在数据仓库中，我们可以对订单数据进行各种分析，例如用户购买行为分析、商品销售情况分析等。

    在这个场景下，使用RBR模式可以保证订单数据的完整性和准确性，避免因SQL语句执行结果不一致导致的数据错误。

五、SBR、RBR、MBR，该选哪个？

这三种模式各有优缺点，该选哪个，取决于你的具体场景。

特性 SBR RBR MBR
Binlog大小 小 大 介于SBR和RBR之间
可靠性 较低，可能存在数据不一致 较高，保证数据一致性 较高，但仍可能存在数据不一致
适用性 简单SQL语句，不包含不确定函数的SQL语句 复杂存储过程、触发器等，所有场景适用 大部分场景适用，MySQL自行决定使用SBR或RBR
性能 较高 较低，解析和传输成本较高 介于SBR和RBR之间
  • 如果你的数据库操作很简单，对数据一致性要求不高，可以选择SBR。
  • 如果你的数据库操作很复杂，对数据一致性要求很高，可以选择RBR。
  • 如果你不知道该选哪个，或者想兼顾性能和可靠性，可以选择MBR。

六、总结：复制的艺术

MySQL的逻辑复制是一个非常强大的功能，可以用于数据备份、数据同步、读写分离等多种场景。但是，要想用好它，需要深入理解Binlog的原理，掌握Binlog解析的方法，以及了解RBR的性能特点。

记住，没有银弹，只有根据实际情况选择最合适的方案。

七、彩蛋：一些实用的Tips

  1. 定期清理Binlog：Binlog会占用大量的磁盘空间，需要定期清理。可以通过PURGE BINARY LOGS BEFORE 'yyyy-mm-dd hh:mm:ss';命令清理指定时间之前的Binlog。

  2. 监控复制延迟：可以使用SHOW SLAVE STATUSG命令查看复制的状态，包括复制延迟。

  3. 合理配置GTID：GTID（Global Transaction Identifier）可以保证事务的唯一性，避免重复执行。建议开启GTID模式。

  4. 使用Semi-Synchronous Replication：半同步复制可以提高数据的安全性，但会降低性能。

好了，今天的讲座就到这里。希望大家有所收获，下次再见！ 记得点赞、收藏、转发哦！

发表回复

您的邮箱地址不会被公开。 必填项已用 * 标注