【MySQL】MySQL如何高效地归档数据
2022-09-17 09:58 abce 阅读(1960) 评论(0) 编辑 收藏 举报高效归档应该满足的需求：
1.归档应该是异步的
2.归档复制使用的存储引擎应该是针对大的数据集优化过的
3.常规集群应该只是正常删除数据
4.归档系统应该从复制流中删除删除语句，只保留插入和更新
5.归档系统应该是健壮的并且能够处理故障和恢复复制
关键因素
我们最初的出发点有点像下图：
集群由一个源(S)和两个副本（R1和R2）组成，我们正在为归档(RA) 添加一个副本。只要将基于行的复制格式与full-row images一起使用，现有集群在接下来的所有讨论中都几乎无关紧要。
上述设置理论上足以归档数据，但为了做到这一点，我们不能允许我们要归档的表上的删除语句流过复制流。删除必须在所有普通服务器上使用sql_log_bin=0执行。虽然这看起来很简单，但它有许多缺点。必须在所有服务器上定期调用cron作业或SQL事件。这些作业必须删除所有生产服务器上的相同数据。此过程可能会在表之间引入一些差异。pt-table-checksum等验证工具可能会开始报告出错误。正如我们将看到的，还有其他选择。
Capturing the changes (CDC)
我们需要的一个重要组件来捕获对我们要归档的表的更改。MySQL二进制日志与基于行的格式和full row image一起使用时，非常适合此目的。我们需要一个工具，它可以像副本一样连接到数据库服务器，将二进制日志事件转换为可用的形式，并跟踪其在二进制日志中的位置。
对于这个项目，我们将使用Zendesk开发的工具Maxwell (https://maxwells-daemon.io/)。Maxwell像常规副本一样连接到源服务器，并以JSON格式输出基于行的事件。它跟踪源服务器上的表的复制位置。
删除操作
由于CDC组件将以JSON格式输出事件，因此我们只需过滤出我们感兴趣的表，然后忽略删除事件。可以使用任何具有良好JSON和MySQL支持的编程语言。在这篇文章中，我将使用Python。
用于归档的存储引擎
InnoDB非常适合OLTP工作负载，但对于归档数据来说远不是那么理想。MyRocks是一个更好的选择，因为它是写优化的，并且在数据压缩方面效率更高。
高效的数据归档的架构
转移表
对于我们的归档副本，我们有一些架构选项。
如下所示的第一个体系架构是将CDC连接到归档副本。这意味着如果我们正在归档表t，我们需要在归档副本上同时拥有生产t（从中删除数据）和归档副本tA（长期保留其数据）。
这种架构的主要优点是所有与归档过程相关的组件只与归档副本交互。当然，不利的一面是归档副本上存在重复数据，因为它必须同时托管t和tA。有人可能会争辩说，表t可能正在使用blackhole存储引擎，但我们不要潜入这样一个兔子洞(a rabbit hole)。
忽略表
另一个架构选项是使用来自源的两个不同的复制流。第一个流是常规复制连接，但副本具有复制选项replicate-ignore-table=t。表t的复制事件由Maxwell控制的第二个复制连接处理。删除事件被移除，插入和更新应用于归档副本。
虽然后面这个架构在归档副本上只存储了一个t副本，但它需要来自源的两个完整复制流。
示例
我目前的目标是提供一个尽可能简单的示例，同时还能工作。我将在Sysbench tpc-c脚本中使用移动表方法。
此脚本有一个选项enable_purge，用于删除已处理的旧订单。我们的目标是创建包含所有行的表tpccArchive.orders1，包括已删除的行，而表tpcc.orders1是常规订单表。它们具有相同的结构，但归档表使用的是MyRocks。
让我们首先准备归档表：
mysql> create database tpccArchive; Query OK, 1 row affected (0,01 sec) mysql> use tpccArchive; Database changed mysql> create table orders1 like tpcc.orders1; Query OK, 0 rows affected (0,05 sec) mysql> alter table orders1 engine=rocksdb; Query OK, 0 rows affected (0,07 sec) Records: 0 Duplicates: 0 Warnings: 0
捕获对表的变更
现在，我们可以安装 Maxwell。Maxwell是基于Java的应用程序，因此需要兼容的JRE。 它还将作为副本连接到MySQL，因此它需要一个具有所需授权的帐户。它还需要自己的maxwell schema来保持复制状态和位置。
root@LabPS8_1:~# apt-get install openjdk-17-jre-headless root@LabPS8_1:~# mysql -e "create user maxwell@'localhost' identified by 'maxwell';" root@LabPS8_1:~# mysql -e 'create database maxwell;' root@LabPS8_1:~# mysql -e 'grant ALL PRIVILEGES ON maxwell.* TO maxwell@localhost;' root@LabPS8_1:~# mysql -e 'grant SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO maxwell@localhost;' root@LabPS8_1:~# curl -sLo - https://github.com/zendesk/maxwell/releases/download/v1.37.6/maxwell-1.37.6.tar.gz| tar zxvf - root@LabPS8_1:~# cd maxwell-1.37.6/ root@LabPS8_1:~/maxwell-1.37.6# ./bin/maxwell -help Help for Maxwell: Option Description ------ ----------- --config <String> location of config.properties file --env_config <String> json object encoded config in an environment variable --producer <String> producer type: stdout|file|kafka|kinesis|nats|pubsub|sns|sqs|rabbitmq|redis|custom --client_id <String> unique identifier for this maxwell instance, use when running multiple maxwells --host <String> main mysql host (contains `maxwell` database) --port <Integer> port for host --user <String> username for host --password <String> password for host --help [ all, mysql, operation, custom_producer, file_producer, kafka, kinesis, sqs, sns, nats, pubsub, output, filtering, rabbitmq, redis, metrics, http ] In our example, we’ll use the <i>stdout</i> producer to keep things as simple as possible.
过滤脚本
为了向tpccArchive.orders1表添加和更新行，我们需要一个逻辑来识别表tpcc.orders1的事件并忽略删除语句。
同样，为简单起见，我选择使用Python脚本。我不会在这里展示整个脚本，请随时从我的GitHub(https://github.com/y-trudeau/blog_data/blob/master/DataArchiving/ArchiveTpccOrders1.py)存储库下载它。
它本质上是一个写入stdin的在线循环。该行作为JSON字符串加载，然后根据找到的值做出一些决定。这是其核心的一小部分代码：
... for line in sys.stdin: j = json.loads(line) if j['database'] == dbName and j['table'] == tableName: debug_print(line) if j['type'] == 'insert': # Let's build an insert ignore statement sql += 'insert ignore into ' + destDbName + '.' + tableName ...
当事件类型为"插入"时，上述代码创建了"插入忽略"语句。该脚本使用用户archiver和密码tpcc连接到数据库，然后将事件应用于表tpccArchive.orders1。
root@LabPS8_1:~# mysql -e "create user archiver@'localhost' identified by 'tpcc';" root@LabPS8_1:~# mysql -e 'grant ALL PRIVILEGES ON tpccArchive.* TO archiver@localhost;'
为了简化重现，这里是tpcc代码：
yves@ThinkPad-P51:~/src/sysbench-tpcc$ ./tpcc.lua --mysql-host=10.0.4.158 --mysql-user=tpcc --mysql-password=tpcc --mysql-db=tpcc \
--threads=1 --tables=1 --scale=1 --db-driver=mysql --enable_purge=yes --time=7200 --report-interval=10 prepare
yves@ThinkPad-P51:~/src/sysbench-tpcc$ ./tpcc.lua --mysql-host=10.0.4.158 --mysql-user=tpcc --mysql-password=tpcc --mysql-db=tpcc \
--threads=1 --tables=1 --scale=1 --db-driver=mysql --enable_purge=yes --time=7200 --report-interval=10 run
数据库的地址是10.0.4.158。选项enable_purge，导致老的orders1表被删除。
对于归档这一侧，运行在虚拟机上：
root@LabPS8_1:~/maxwell-1.37.6# bin/maxwell --user='maxwell' --password='maxwell' --host='127.0.0.1' \ --producer=stdout 2> /tmp/maxerr | python3 ArchiveTpccOrders1.py
运行两个小时之后：
mysql> select TABLE_SCHEMA, TABLE_ROWS, DATA_LENGTH, INDEX_LENGTH, ENGINE from information_schema.tables where table_name='orders1'; +--------------+------------+-------------+--------------+---------+ | TABLE_SCHEMA | TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH | ENGINE | +--------------+------------+-------------+--------------+---------+ | tpcc | 48724 | 4210688 | 2310144 | InnoDB | | tpccArchive | 1858878 | 38107132 | 14870912 | ROCKSDB | +--------------+------------+-------------+--------------+---------+ 2 rows in set (0,00 sec)
更真实的架构
上面的例子就是一个例子，比任何生产系统都简单。这里有几个需求：
·Maxwell必须能够从正确的复制位置重新启动并继续
·Python脚本必须能够从正确的复制位置重新启动并继续
·如果连接断开，Python脚本必须能够重新连接到MySQL并重试事务。
Maxwell已经处理了第一点，它使用数据库来存储它的当前位置。下面的逻辑步骤是在Maxwell和 Python脚本之间添加一个比简单的流程管道更强大的排队系统。Maxwell支持许多队列系统，如kafka、kinesis、rabbitmq、redis等。对于我们的应用程序，我倾向于使用kafka和单个分区的解决方案。kafka不管理消息的偏移量，由应用程序决定。这意味着Python脚本可以更新表的一行，作为它应用的每个事务的一部分，以跟踪其在kafka流中的位置。如果归档表使用RocksDB，则队列位置跟踪表也应该使用RocksDB，这样数据库事务就不会跨存储引擎。
结论
在这篇文章中，我提供了一个使用MySQL复制二进制日志来归档数据的解决方案。
归档快速增长的表是一种常见的需求，希望这样的解决方案可以提供帮助。如果在副本上有一个MySQL插件能够直接过滤复制事件，那就太好了。这将不再需要像Maxwell和我的python脚本这样的外部解决方案。然而，一般来说，这种归档解决方案只是汇总表的一种特殊情况。在以后的帖子中，我希望提供一个更完整的解决方案，同时保持一个总结。
