## 模块 1：为 SQL 工作负载重放设置 MySQL 和 TiDB + 设置上游 MySQL 和下游 TiDB 环境，并安装 sql-replay 和 tshark 工具 ### 步骤 1. 连接到远程 Linux 机器 + 点击这里 - [终端 1](ssh://monitor1-ssh#1) 打开一个新的终端，连接到为你的实践实验分配的虚拟机。 ### 步骤 2. 安装 `sql-replay` + 安装 `sql-replay` 工具。 ```bash mkdir ~/replay cd ~/replay wget https://github.com/Bowen-Tang/sql-replay/releases/download/v0.3.3/0.3.3.zip unzip 0.3.3.zip ``` ```output --2024-08-06 09:07:09-- https://github.com/Bowen-Tang/sql-replay/releases/download/0.3.3/0.3.3.zip Resolving github.com (github.com)... 140.82.116.3 Connecting to github.com (github.com)|140.82.116.3|:443... connected. HTTP request sent, awaiting response... 302 Found ... Resolving objects.githubusercontent.com (objects.githubusercontent.com)... 185.199.110.133, 185.199.109.133, 185.199.108.133, ... Connecting to objects.githubusercontent.com (objects.githubusercontent.com)|185.199.110.133|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 7015215 (6.7M) Saving to: ‘0.3.3.zip’ 100%======================================================================================================================================================================> 7,015,215 --.-K/s in 0.06s 2024-08-06 09:07:09 (119 MB/s) - ‘0.3.3.zip’ saved [7015215/7015215] $ unzip 0.3.3.zip Archive: 0.3.3.zip inflating: sql-replay ``` + 创建一个用于保存文件的目录。 ```bash mkdir ~/sql-replay cd ~/sql-replay ``` ### 步骤 3. 安装 `parse-tshark` + 连接到 MySQL server 节点。如果有提示，回答 `yes`。 ```bash ssh -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem rocky@${HOST_MYSQL1_PRIVATE_IP} ``` ```output Last login: Thu Aug 1 11:58:47 2024 from 10.90.4.90 [rocky@ip-10-90-1-235 ~]$ ``` + 安装 `wireshark` 和 `parse-tshark` 工具。 ```bash sudo yum install -y wireshark ``` ```output ... Complete! ``` + 将 `rocky` 添加到 `wireshark` 用户组，并授予 `dump` 路径访问权限。 ```bash sudo usermod -a -G wireshark rocky sudo chmod +x /usr/bin/dumpcap 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 unzip parse-tshark-v0.1.2.zip export PATH=/home/rocky/parse-tshark:$PATH ``` ```output [ec2-user@ip-10-90-4-205 replay]$ mkdir ~/parse-tshark [ec2-user@ip-10-90-4-205 replay]$ cd ~/parse-tshark [ec2-user@ip-10-90-4-205 parse-tshark]$ wget https://github.com/Bowen-Tang/parse-tshark/releases/download/0.1.2/parse-tshark-v0.1.2.zip --2024-08-06 09:11:36-- https://github.com/Bowen-Tang/parse-tshark/releases/download/0.1.2/parse-tshark-v0.1.2.zip Resolving github.com (github.com)... 140.82.116.3 Connecting to github.com (github.com)|140.82.116.3|:443... connected. HTTP request sent, awaiting response... 302 Found ... Resolving objects.githubusercontent.com (objects.githubusercontent.com)... 185.199.109.133, 185.199.110.133, 185.199.111.133, ... Connecting to objects.githubusercontent.com (objects.githubusercontent.com)|185.199.109.133|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 5162087 (4.9M) [application/octet-stream] Saving to: ‘parse-tshark-v0.1.2.zip’ 100%======================================================================================================================================================================> 5,162,087 10.1MB/s in 0.5s 2024-08-06 09:11:37 (10.1 MB/s) - ‘parse-tshark-v0.1.2.zip’ saved [5162087/5162087] $ unzip parse-tshark-v0.1.2.zip Archive: parse-tshark-v0.1.2.zip inflating: parse-tshark ``` ### 步骤 4. 为捕获 SQL 工作负载准备 MySQL + 以数据库用户 `root` 身份连接到 MySQL server。 ```bash mysql -h localhost -P 3306 -u root -pq1w2e3R4_ --connect-expired-password ``` ```output mysql> ``` + 创建数据库用户 `test`。 ```sql DROP USER IF EXISTS 'test'@'%'; CREATE USER 'test'@'%' IDENTIFIED BY 'q1w2e3R4_'; GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION; FLUSH PRIVILEGES; ``` + 在 MySQL 中设置慢日志参数。 ```sql SET GLOBAL slow_query_log = on; SET GLOBAL slow_query_log_file='/var/lib/mysql/local-slow.log'; SET GLOBAL long_query_time = 0; ``` ```sql EXIT ``` ```bash mysql -h localhost -P 3306 -u root -pq1w2e3R4_ --connect-expired-password ``` + 验证参数是否正确。 ```sql SHOW VARIABLES LIKE 'slow_query_log'; SHOW VARIABLES LIKE 'slow_query_log_file'; SHOW VARIABLES LIKE 'long_query_time'; ``` ```output mysql> SHOW VARIABLES LIKE 'slow_query_log'; +----------------+-------+ | Variable_name | Value | +----------------+-------+ | slow_query_log | ON | +----------------+-------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'slow_query_log_file'; +---------------------+----------------------------------------+ | Variable_name | Value | +---------------------+----------------------------------------+ | slow_query_log_file | /var/lib/mysql/local-slow.log | +---------------------+----------------------------------------+ 1 row in set (0.00 sec) mysql> SHOW VARIABLES LIKE 'long_query_time'; +-----------------+----------+ | Variable_name | Value | +-----------------+----------+ | long_query_time | 0.000000 | +-----------------+----------+ 1 row in set (0.00 sec) ``` + 创建`sbtest`数据库 ```sql DROP DATABASE IF EXISTS sbtest; CREATE DATABASE sbtest; ``` + 退出监控操作系统命令行。 ```sql EXIT ``` ```output Bye ``` + 为慢查询日志文件添加读取权限。 ```bash sudo chmod -R +r /var/lib/mysql/local-slow.log ``` + 返回 Linux 堡垒机服务器。 ```bash exit ``` ```output logout Connection to 10.90.1.130 closed. ``` ### 步骤 5. 为 SQL 工作负载重放准备 TiDB + 设置下游 TiDB 环境。 + 准备重放目标数据库，并创建 `replay_info` 表以记录 SQL 的执行信息。 ```bash mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -uroot ``` ```output mysql> ``` ```sql DROP DATABASE IF EXISTS sbtest; CREATE DATABASE sbtest; ``` ```output Query OK, 0 rows affected (0.11 sec) ``` + 您已安装 `sql-replay` 和 `parse-tshark` 工具，并完成了用于重放 SQL 工作负载的源数据库和目标数据库的设置。继续进行下一个模块。