从 MySQL 迁徙到 TiDB：使用 SQL-Replay 工具举行真实线上流量回放测试 SOP ...

导读

在 MySQL 迁徙至 TiDB 的过程中，兼容性和性能验证至关紧张。SQL-Replay 是一款实用工具，用于评估数据库的兼容性和性能，支持日记剖析、查询回放、性能丈量和陈诉天生等功能。
本文介绍了 SQL-Replay 工具的安装和使用步骤，以及如何运用 SQL-Replay 工具，通过回放 MySQL 慢查询日记或抓包流量文件，验证 TiDB 集群的兼容性和性能，提高数据库迁徙的成功率和效率。
SQL-Replay 工具介绍

1.1 工具简介
流量回放工具：GitHub - Bowen-Tang/sql-replay: mysql slow query replay
抓包工具：https://github.com/Bowen-Tang/parse-tshark
SQL-Replay 工具是一个设计用于回放 MySQL 慢查询文件和 parse-tshark 抓包文件的 GitHub 开源项目，目的是帮助评估数据库兼容性和性能。这个工具支持多种操纵模式，包括剖析慢查询日记、回放这些日记、将回放结果加载到数据库中以及天生陈诉。
1.2 原理概述
以下是 SQL-Replay 工具的核心功能原理：
1. 日记捕获与剖析：
a. MySQL 的慢日记文件以及通过 parse-tshark 抓包天生的文件，这些日记中包括 SQL 实行时间、SQL 文本。
b. 该工具可以剖析这些日记以提取相干信息，如 SQL 语句、实行时间，以及可能的其他元数据，如实行上下文或实行查询的用户。
2. 查询回放：
a. 在受控环境中回放提取的 SQL 查询至另一套 TiDB 数据库。
3. 性能丈量与分析：
a. 在回放期间，通过实行 SQL 查询获取在 TiDB 数据库中的实行时间。
b. 它将这些指标与基准或之前的运行结果举行比较，以识别性能降落或改进。这有助于确定可能引起问题的特定查询或数据库设置。
4. 陈诉天生：
a. 末了，SQL-Replay 天生陈诉，提供查询兼容性和性能的见解。这些陈诉帮助数据库管理员和开发职员明白不同查询对体系的影响，并做出有关优化的明智决策。
通过使用像 SQL-Replay 这样的工具，团队可以确保他们的数据库为性能优化并能有用处理实际负载。这在涉及数据库迁徙、升级或重大架构更改的场景中有有作用，对查询性能的影响至关紧张。

1.3 实用场景
根据 SQL-Replay 工具的特性和功能，以下是一些实用的场景：
1. SQL 兼容性评估：
使用该工具可以将在 MySQL 数据库上实行过的 SQL 回放至卑鄙 TiDB 数据库，根据实行结果判断卑鄙数据库对 SQL 的兼容度。
2. 数据库性能评估：
在数据库升级或迁徙前后，使用 SQL-Replay 往返放实际的生产查询，帮助评估数据库的性能改进或退化。这对于在实行重大更改之前明白潜在的性能问题非常有用。
3. 容灾和高可用性测试：
在计划容灾策略和高可用性办理方案时，SQL-Replay 可用于模拟实际查询负载，以测试体系在主机故障或网络停止时的表现。
4. 开发和测试：
开发职员和测试职员可以使用 SQL-Replay 在开发和测试环境中回放生产环境的查询，确保新开发的功能和优化在面对实际数据和负载时的稳定性和性能。
通过使用 SQL-Replay，团队可以更有用地明白更换数据库带来的差异，确保任何更改都符合预期的性能标准，并减少在生产环境中出现问题的风险。
1.4 支持的 MySQL 数据库
已知支持对以下 MySQL 数据库的慢查询回放至 TiDB。

• MySQL 5.6、5.7、8.0
  
• 华为云 MySQL RDS
  
• Aurora MySQL 5.7、8.0
  

当前验证支持的慢日记格式实例如下：

1. # Time: 2024-01-19T16:29:48.141142Z
2. # User@Host: t1[t1] @  [10.2.103.21]  Id:   797
3. # Query_time: 0.000038  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
4. SET timestamp=1705681788;
5. SELECT c FROM sbtest1 WHERE id=250438;
8. # Time: 240119 16:29:48
9. # User@Host: t1[t1] @  [10.2.103.21]  Id:   797
10. # Query_time: 0.000038  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 1
11. SET timestamp=1705681788;
12. SELECT c FROM sbtest1 WHERE id=250438;
15. # Time: 231106  0:06:36
16. # User@Host: coplo2o[coplo2o] @  [10.0.2.34]  Id: 45827727
17. # Query_time: 1.066695  Lock_time: 0.000042 Rows_sent: 1  Rows_examined: 7039 Thread_id: 45827727 Schema: db Errno: 0 Killed: 0 Bytes_received: 0 Bytes_sent: 165 Read_first
18. : 0 Read_last: 0 Read_key: 1 Read_next: 7039 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 0 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 0 Sort_scan_count: 0 Created_tmp_
19. disk_tables: 0 Created_tmp_tables: 0 Start: 2023-11-06T00:06:35.589701 End: 2023-11-06T00:06:36.656396 Launch_time: 0.000000
20. # QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No  Filesort: No  Filesort_on_disk: No
21. use db;
22. SET timestamp=1699200395;
23. SELECT c FROM sbtest1 WHERE id=250438;

复制代码

SQL-Replay 使用 SOP

2.1 安装和设置
2.1.1 使用 SQL-Replay
直接下载，或从 GitHub 克隆源码举行当地编译。
根据仓库中提供的文档设置工具，确保所有依赖精确安装。
- 下载 SQL-Replay 可实行程序

1. mkdir replay && cd replay && wget https://github.com/Bowen-Tang/sql-replay/releases/download/0.3.2/0.3.2.zip
2. unzip 0.3.2.zip

复制代码

- 编译安装 SQL-Replay

1. #安装 golang (1.20 及以上)
2. #下载项目
3. git clone https://github.com/Bowen-Tang/sql-replay
5. #编译 sql-replay
6. cd sql-replay
7. go mod tidy
8. go build

复制代码

- 开启上游 MySQL slow log
设置 long_query_time 为 0，从将每一个 SQL 均记载在慢日记中。MySQL 修改完 long_query_time 参数后应用毗连重修后才会生效。
   需要注意的是慢日记 long_query_time 设置为 0 会导致大量的慢日记写入，在高并发场景下，可能会对性能有较大的影响（20%+）

1. --开启慢查询日志
2. set global slow_query_log=on;
3. --设置慢查询时间为 0
4. SET GLOBAL long_query_time = 0;
5. --获取<慢查询日志路径>
6. show variables like 'slow_query_log_file';

复制代码

2.1.2 使用 Parse-tshark
- 在需要抓取流量的 MySQL 上安装抓包程序 tshark

1. # Centos 7 自带的版本较低，但也能工作，建议编译安装 3.2.3 版本
2. yum install -y wireshark

复制代码

- 大概下载 parse-tshark 可实行程序

1. mkdir parse-tshark && cd parse-tshark && wget https://github.com/Bowen-Tang/parse-tshark/releases/download/0.1.2/parse-tshark-v0.1.2.zip
2. unzip parse-tshark-v0.1.2.zip

复制代码

- 大概编译安装 parse-tshark

1. # Install golang (1.20 and above)
2. # Download project
3. git clone https://github.com/Bowen-Tang/parse-tshark
5. # Compile parse-tshark
6. cd parse-tshark
7. go mod tidy
8. go build

复制代码

2.1.3 创建卑鄙 TiDB 回放信息表
- 在卑鄙 TiDB 创建回放信息表

1. CREATE TABLE `test`.`replay_info` (
2. `sql_text` longtext DEFAULT NULL,
3. `sql_type` varchar(16) DEFAULT NULL,
4. `sql_digest` varchar(64) DEFAULT NULL,
5. `query_time` bigint(20) DEFAULT NULL,
6. `rows_sent` bigint(20) DEFAULT NULL,
7. `execution_time` bigint(20) DEFAULT NULL,
8. `rows_returned` bigint(20) DEFAULT NULL,
9. `error_info` text DEFAULT NULL,
10. `file_name` varchar(64) DEFAULT NULL
11. );

复制代码

2.2 天生真实业务流量文件
2.2.1 使用 SQL-Replay
慢日记文件方式：在上游 MySQL 数据库上发起真实业务测试，观测 slow log 中是否已有慢日记天生。

1. tail -n 10 <慢查询日志路径>

复制代码

2.2.2 使用 parse-tshark
抓包方式获取流量文件：在所有线上 MySQL 实例上，使用 tshark 举行 port 过滤，再二次过滤文件中的 mysql.query 和 相应时间。该方式天生的文件比较大，但对生产性能影响小（7%左右）。
- 抓包
该下令只是根据 3306 端口和 eth0 网卡抓包，以抓取 1 小时为例（每个文件大约 2000MB，最多天生 200 个）

1. cd ～/parse-tshark
2. sudo tshark -i eth0 -f "tcp port 3306" -a duration:3600 -b filesize:2000000 -b files:200 -w ts.pcap

复制代码

该下令会天生 ts*.pcap 文件。
- 获取抓包过程中的 user db 信息
由于 tshark 抓包时获取 user/db 信息过于复杂、且存在局限性，所以通过工具每隔 500ms 获取一次 MySQL 数据库的 processlist 视图信息，通过源端 IP+端口 与 processlist 视图中的 host 匹配，将信息输出到 host.ini文件中。

1. ./parse-tshark -mode getmysql -dbinfo 'username:password@tcp(localhost:3306)/information_schema' -output host.ini

复制代码

注意：该工具需要和 tshark 抓包同时运行，才能获取完整的 user/db 信息。
- 分析包
该下令针对抓包天生的 pcap 文件举行处理，处理成 SQL-replay 工具可读的文件（发起随后将这些文件传输到实行 SQL 回放的服务器处理）。

1. for i in `ls -rth ts*.pcap`
2. do
3. sudo tshark -r $i -Y "mysql.query or ( tcp.srcport==3306)" -d tcp.port==3306,mysql -o tcp.calculate_timestamps:true -T fields -e tcp.stream -e tcp.len -e tcp.time_delta -e ip.src -e tcp.srcport -e ip.dst -e tcp.dstport -e frame.time_epoch -e mysql.query -E separator='|' >> tshark.log
4. done

复制代码

多个文件将会被合并至 tshark.log 中。
2.3 剖析线上流量日记
2.3.1 使用 SQL-Replay
使用 parse 模式将慢查询日记转换成结构化的 JSON 格式，便于回放。这涉及指定包含慢查询的输入文件和 JSON 文件的输出路径。

1. ./sql-replay -mode parse -slow-in <慢查询日志路径> -slow-out <慢查询输出JSON文件路径>

复制代码

2.3.2使用 parse-tshark
天生 sql-replay 可回放的文件。

1. ./parse-tshark -mode parse2file -parsemode 1 -tsharkfile ./tshark.log -hostfile ./host.ini -replayfile ./tshark.out -defaultuser user_null -defaultdb db_null

复制代码

将输入的抓包文件 tshark.log、db 和 user 信息文件 host.ini 。根据需要回放的 SQL 填写参数 user_null、db_null。输出可实行 SQL 到 tshark.out 文件中，供回放使用。
2.4 回放 SQL
使用 replay 模式将 MySQL 数据库的 SQL 回放至卑鄙 TiDB。

1. # 回放所有用户、所有 SQL
2. ./sql-replay -mode replay -db <TiDB 连接字符串> -speed <回放速度> -slow-out <慢查询输出JSON文件路径> -replay-out <回放输出路径>/<回放任务名称> -username all -sqltype all -dbname all

复制代码

回放结果会存放在<回放输出路径>下，以<回放任务名称>开头，例如，<回放任务名称>为 sb1_all，则输出文件以 sb1_all.* 命名。
提示：

• 通过设置 speed 为 n，提高 SQL 回放频率，可以提升 SQL 回放的速率。
  
• 当数据库中就一个 database，一个 user 时，使用 -username all -dbname all 往返放。
  
• 当数据库中有多个 database、多个 user 时，发起启动多个 SQL-Replay 历程并行回放（否则将出现大量 SQL 报错），每个历程对应不同的 -username 和 -dbname（注意 -db 中的用户名、数据库名也需保持同等）。
  

2.5 加载回放结果
使用 load 模式将回放结果加载到指定的数据库表中举行进一步分析。其中，<回放输出路径>可以为 SQL-Replay 或 parse-tshark 两种模式的回放 SQL 输出文件。

1. ./sql-replay -mode load -db <TiDB 连接字符串> -out-dir <回放输出路径> -replay-name <回放任务名称> -table replay_info

复制代码

通过查询 TiDB 数据中目的表的数据，确认是否已完成数据加载。

1. select count(1) from replay_info where file_name like 'sb1_all.%' limit 11

复制代码

2.6 天生陈诉
使用 report 模式从回放数据天生详细陈诉，帮助明白不同查询对数据库兼容性和性能陈诉。

1. ./sql-replay -mode report -db <TiDB 连接字符串> -replay-name <回放任务名称> -port <Web报告端口>

复制代码

通过访问 http://ip:8081 可以查看流量回放陈诉。
Replay Summary 中，记载了 SQL 总耗时对比、快的 SQL 条数、慢的 SQL 条数、错误的 SQL 条数。

Sql Error Info 中，根据 sql_digest 以及 error_info（前 10 位）排序。

常见问题

如果慢查询中 connection 没有 use db 该怎么办？
通过慢查询回放时，由于日记中没有记载 database 信息，所以在 replay 时，只能指定 -db all，大概不指定，否则不会举行回放（如果想要在慢查询回放时过滤库，可以通过指定 -username 以及 -db 中的用户名和数据库名的形式来完成对应库的回放）。
是否支持流量放大？
没有设计自动的流量放大功能，如果仅需要放大读流量，可以通过启动多个回放程序来加大读流量的方式实现流量放大。
SQL 回放的顺序和上游完全同等么？
SQL 回放顺序并不完全与真实实行顺序相等。
客户长链接和短毗连有什么影响么？
在短毗连的情况下，可能存在毗连数过多的问题
对于海量数据（20TB+）的场景，如何举行真实流量压测？
仿真流量测试在大数据量场景下，还是发起可以全量数据和流量举行测试。如果因为时间周期、成本、紧张程度、复杂程度等因素综合考虑无法全量数据压测，发起按照业务主维度（例如游戏服的玩家、订单体系的订单）的 1/4 大概 1/2 等数据举行压测，并且压测时需要将流量放大指定倍数到全流量级别，以便只管模拟线上的场景，别的就算这样做了，还是可能会存在和线上真实流量较大的偏差，比如别的维度（例如商家维度查询）的跨主维度查询时的数据量可能只有真实流量的 1/4 大概 1/2，从而导致测试数据有肯定的偏差。
云上 RDS MySQL 都支持么？
云上 RDS 的慢查询日记格式不尽雷同，不肯定支持（需要验证慢日记格式）；暂不支持 MariaDB，当前无法获取 connection_id，后续加上。
回放时会碰到 too many open files
当 connection_id 值过多（>4096）时，举行回放时会碰到 too many open files 错误，临时办理办法：回放前 ulimit -n 1000000。

免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。