## 模块 2：将示例数据库从 MySQL 迁移到 TiDB
### 步骤 1. 连接到 TiUP 控制机
+ 点击此处 - [终端 2](ssh://monitor1-ssh#2) 连接到 TiUP 控制机。
### 步骤 2. 在 MySQL 中创建用于迁移的用户
+ 在 MySQL 上创建专用用户 `dm_user` 用于迁移目的。如果 SSH 命令提示，请回答 `yes`。
```bash
ssh -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem rocky@${HOST_MYSQL1_PRIVATE_IP}
```
```output
[rocky@ip-10-90-1-38 ~]$
```
```bash
X=q1w2e3R4_
mysql -h localhost -P 3306 -uroot -p$X --connect-expired-password << EOF
DROP USER IF EXISTS dm_user@'%';
CREATE USER dm_user@'%' IDENTIFIED BY 'q1w2e3R4_';
GRANT ALL PRIVILEGES ON *.* TO dm_user@'%';
EXIT
EOF
```
```output
mysql: [Warning] Using a password on the command line interface can be insecure.
```
+ 退出至 TiUP 控制机。
```bash
exit
```
```output
logout
Connection to 10.90.1.38 closed.
```
### 步骤 3. 部署并启动 TiDB 集群
+ 检查已配置的集群拓扑 YAML。
```bash
cat topology.yaml
```
```output
global:
user: "ec2-user"
ssh_port: 22
deploy_dir: "/tidb-deploy"
data_dir: "/tidb-data"
arch: "amd64"
server_configs:
pd:
replication.max-replicas: 3
pd_servers:
- host: 10.90.2.11
client_port: 2379
peer_port: 2380
advertise_client_addr: "http://34.221.120.15:2379"
tidb_servers:
- host: 10.90.1.173
port: 4000
status_port: 10080
deploy_dir: /tidb-deploy/tidb-4000
log_dir: /tidb-deploy/tidb-4000/log
tikv_servers:
- host: 10.90.1.233
port: 20160
status_port: 20180
- host: 10.90.1.79
port: 20160
status_port: 20180
- host: 10.90.1.242
port: 20160
status_port: 20180
monitoring_servers:
- host: 10.90.4.90
grafana_servers:
- host: 10.90.4.90
alertmanager_servers:
- host: 10.90.4.90
tiproxy_servers:
- host: 10.90.4.90
```
+ 修复系统配置以部署 TiDB 集群。
```bash
~/.tiup/bin/tiup mirror set tidb-community-server-tidb_version-linux-amd64
~/.tiup/bin/tiup cluster check topology.yaml \
--user ec2-user \
-i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \
--apply
```
```output
.
.
.
+ Try to apply changes to fix failed checks
+ Try to apply changes to fix failed checks
- Applying changes on 10.90.2.239 ... ⠴ Sysctl: host=10.90.2.239 vm.swappiness = 0
- Applying changes on 10.90.1.190 ... ⠴ Sysctl: host=10.90.1.190 vm.swappiness = 0
- Applying changes on 10.90.1.233 ... ⠴ Sysctl: host=10.90.1.233 vm.swappiness = 0
+ Try to apply changes to fix failed checks
+ Try to apply changes to fix failed checks
- Applying changes on 10.90.2.239 ... Done
- Applying changes on 10.90.1.190 ... Done
- Applying changes on 10.90.1.233 ... Done
- Applying changes on 10.90.1.136 ... Done
- Applying changes on 10.90.1.42 ... Done
- Applying changes on 10.90.4.231 ... Done
```
+ 部署 TiDB 集群。
```bash
~/.tiup/bin/tiup cluster deploy tidb-demo tidb_version ./topology.yaml \
--user ec2-user \
-i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \
--yes
```
```output
...
Cluster `tidb-demo` deployed successfully, you can start it with command: `tiup cluster start tidb-demo --init`
```
+ 启动已部署的 TiDB 集群。
```bash
~/.tiup/bin/tiup cluster start tidb-demo
```
```output
...
Started cluster `tidb-demo` successfully
```
+ 检查 TiDB 集群状态。请注意 `tidb-demo` TiDB 集群中有一个 TiDB server 和 三个 TiKV server，并且 `TiProxy` 于 `Alert Manager`, `Grafana` 和 `Prometheus`部署在同一节点上。
```bash
~/.tiup/bin/tiup cluster display tidb-demo
```
```output
Cluster type: tidb
Cluster name: tidb-demo
Cluster version: v6.5.9
Deploy user: ec2-user
SSH type: builtin
Dashboard URL: http://34.221.120.15:2379/dashboard
Grafana URL: http://10.90.4.90:3000
ID Role Host Ports OS/Arch Status Data Dir Deploy Dir
-- ---- ---- ----- ------- ------ -------- ----------
10.90.4.90:9093 alertmanager 10.90.4.90 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093
10.90.4.90:3000 grafana 10.90.4.90 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000
10.90.2.11:2379 pd 10.90.2.11 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379
10.90.4.90:9090 prometheus 10.90.4.90 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090
10.90.1.173:4000 tidb 10.90.1.173 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000
10.90.1.233:20160 tikv 10.90.1.233 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160
10.90.1.242:20160 tikv 10.90.1.242 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160
10.90.1.79:20160 tikv 10.90.1.79 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160
10.90.4.90:6000 tiproxy 10.90.4.90 6000/3080 linux/x86_64 Up - /tidb-deploy/tiproxy-6000
Total nodes: 9
```
### 步骤 3. 为迁移准备 TiDB
+ 在 TiDB 上创建一个专门用于迁移的用户 `dm_user` 。
```bash
mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -uroot << 'EOF'
DROP USER IF EXISTS dm_user@'%';
CREATE USER dm_user@'%' identified by 'q1w2e3R4_';
GRANT ALL PRIVILEGES ON *.* TO dm_user@'%';
EXIT
EOF
```
+ 在 TiDB 上创建目标数据库 `sbtest` 及其空表。请注意，列 `id` 设置为 `AUTO_RANDOM` 而不是 `AUTO_INCREMENT`。`AUTO_RANDOM` 是 TiDB 特有的功能，用于避免热点性能问题，要了解有关 `AUTO_RANDOM` 的更多信息，请报名参加自学的研讨会 [TiDB Workshop for MySQL Users](https://eng.edu.pingcap.com/catalog/info/id:231)。
```bash
mysql -h ${HOST_DB1_PRIVATE_IP} -P 4000 -uroot << 'EOF'
CREATE DATABASE sbtest;
CREATE TABLE `sbtest`.`sbtest1` (
`id` bigint NOT NULL AUTO_RANDOM,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `sbtest`.`sbtest2` (
`id` bigint NOT NULL AUTO_RANDOM,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `sbtest`.`sbtest3` (
`id` bigint NOT NULL AUTO_RANDOM,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `sbtest`.`sbtest4` (
`id` bigint NOT NULL AUTO_RANDOM,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `sbtest`.`sbtest5` (
`id` bigint NOT NULL AUTO_RANDOM,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
CREATE TABLE `sbtest`.`sbtest6` (
`id` bigint NOT NULL AUTO_RANDOM,
`k` int NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
PRIMARY KEY (`id`),
KEY `k_1` (`k`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
SET GLOBAL allow_auto_random_explicit_insert = true;
USE sbtest;
SHOW TABLES;
EXIT
EOF
```
```output
Tables_in_sbtest
sbtest1
sbtest2
sbtest3
sbtest4
sbtest5
sbtest6
```
### 步骤 4. 部署 TiDB Data Migration 集群
+ 安装 TiDB Data Migration 组件。
```bash
~/.tiup/bin/tiup install dm dmctl:vtidb_version dm-master:vtidb_version dm-worker:vtidb_version
```
```bash
~/.tiup/bin/tiup list --verbose | grep dm
```
```output
dm pingcap v1.15.1,v1.16.0 linux/amd64 Data Migration Platform manager
dm-master pingcap v8.0.0,vtidb_version linux/amd64 dm-master component of Data Migration Platform.
dm-worker pingcap v8.0.0,vtidb_version linux/amd64 dm-worker component of Data Migration Platform.
dmctl pingcap v6.5.1,v6.5.9,v7.5.0,vtidb_version linux/amd64 dmctl component of Data Migration Platform.
```
+ TiDB Data Migration 集群拓扑配置文件已为你预配置好。请查看配置，无需更改。
```bash
cat dm-topology.yaml
```
```output
global:
user: "ec2-user"
ssh_port: 22
deploy_dir: "/tidb-deploy"
data_dir: "/tidb-data"
arch: "amd64"
master_servers:
- host: 10.90.4.191
worker_servers:
- host: 10.90.3.49
- host: 10.90.3.71
- host: 10.90.3.135
monitoring_servers:
- host: 10.90.1.7
grafana_servers:
- host: 10.90.1.7
alertmanager_servers:
- host: 10.90.1.7
```
+ 部署并启动 TiDB Data Migration 集群 - `dm-test`.
```bash
~/.tiup/bin/tiup dm deploy dm-test vtidb_version ./dm-topology.yaml \
--user ec2-user \
-i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem \
--yes
```
```output
Starting component dm: /home/ec2-user/.tiup/components/dm/v1.16.0/tiup-dm deploy dm-test vtidb_version ./dm-topology.yaml --user ec2-user -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem --yes
+ Detect CPU Arch Name
- Detecting node 10.90.4.191 Arch info ... Done
- Detecting node 10.90.3.49 Arch info ... Done
- Detecting node 10.90.3.71 Arch info ... Done
- Detecting node 10.90.3.135 Arch info ... Done
- Detecting node 10.90.1.7 Arch info ... Done
...
Cluster `dm-test` deployed successfully, you can start it with command: `tiup dm start dm-test`
```
+ 启动 TiDB Data Migration 集群 - `dm-test`.
```bash
~/.tiup/bin/tiup dm start dm-test
```
```output
Starting component dm: /home/ec2-user/.tiup/components/dm/v1.16.0/tiup-dm start dm-test
Starting cluster dm-test...
+ [ Serial ] - SSHKeySet: privateKey=/home/ec2-user/.tiup/storage/dm/clusters/dm-test/ssh/id_rsa, publicKey=/home/ec2-user/.tiup/storage/dm/clusters/dm-test/ssh/id_rsa.pub
+ [Parallel] - UserSSH: user=ec2-user, host=10.90.1.7
+ [Parallel] - UserSSH: user=ec2-user, host=10.90.3.71
+ [Parallel] - UserSSH: user=ec2-user, host=10.90.1.7
+ [Parallel] - UserSSH: user=ec2-user, host=10.90.4.191
+ [Parallel] - UserSSH: user=ec2-user, host=10.90.1.7
+ [Parallel] - UserSSH: user=ec2-user, host=10.90.3.49
+ [Parallel] - UserSSH: user=ec2-user, host=10.90.3.135
+ [ Serial ] - StartCluster
Starting component dm-master
Starting instance 10.90.4.191:8261
Start instance 10.90.4.191:8261 success
Starting component dm-worker
Starting instance 10.90.3.135:8262
Starting instance 10.90.3.49:8262
Starting instance 10.90.3.71:8262
Start instance 10.90.3.135:8262 success
Start instance 10.90.3.71:8262 success
Start instance 10.90.3.49:8262 success
Starting component prometheus
Starting instance 10.90.1.7:9090
Start instance 10.90.1.7:9090 success
Starting component grafana
Starting instance 10.90.1.7:3000
Start instance 10.90.1.7:3000 success
Starting component alertmanager
Starting instance 10.90.1.7:9093
Start instance 10.90.1.7:9093 success
Started cluster `dm-test` successfully
```
+ 确认 TiDB Data Migration集群状态为 up 并正在运行。
```bash
tiup dm display dm-test
```
```output
Starting component dm: /home/ec2-user/.tiup/components/dm/v1.16.0/tiup-dm display dm-test
Cluster type: dm
Cluster name: dm-test
Cluster version: vtidb_version
Deploy user: ec2-user
SSH type: builtin
Grafana URL: http://10.90.3.188:3000
ID Role Host Ports OS/Arch Status Data Dir Deploy Dir
-- ---- ---- ----- ------- ------ -------- ----------
10.90.3.188:9093 alertmanager 10.90.3.188 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093
10.90.4.196:8261 dm-master 10.90.4.196 8261/8291 linux/x86_64 Healthy|L /tidb-data/dm-master-8261 /tidb-deploy/dm-master-8261
10.90.4.196:8262 dm-worker 10.90.4.196 8262 linux/x86_64 Free /tidb-data/dm-worker-8262 /tidb-deploy/dm-worker-8262
10.90.3.188:3000 grafana 10.90.3.188 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000
10.90.3.188:9090 prometheus 10.90.3.188 9090 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090
Total nodes: 5
```
### 步骤 5. 设置迁移任务源配置
+ 创建任务源配置。
```bash
cat << EOF > mysql-source-conf1.yaml
source-id: "mysql-replica-01"
from:
host: "${HOST_MYSQL1_PRIVATE_IP}"
port: 3306
user: "dm_user"
password: "q1w2e3R4_"
EOF
cat mysql-source-conf1.yaml
```
```output
source-id: "mysql-replica-01"
from:
host: "10.90.1.135"
port: 3306
user: "dm_user"
password: "q1w2e3R4_"
```
+ 将 MySQL 实例设置为迁移源，并检查该源是否可以被 TiDB Data Migration 集群识别。
```bash
~/.tiup/bin/tiup dmctl:vtidb_version \
--master-addr=${HOST_MONITOR1_PRIVATE_IP}:8261 operate-source create mysql-source-conf1.yaml
```
```output
Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/vtidb_version/dmctl/dmctl --master-addr=10.90.4.66:8261 operate-source create mysql-source-conf1.yaml
{
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"source": "mysql-replica-01",
"worker": "dm-10.90.3.187-8262"
}
]
}
```
```bash
~/.tiup/bin/tiup dmctl:vtidb_version \
--master-addr=${HOST_MONITOR1_PRIVATE_IP}:8261 get-config source mysql-replica-01
```
```output
Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/vtidb_version/dmctl/dmctl --master-addr=10.90.4.66:8261 get-config source mysql-replica-01
{
"result": true,
"msg": "",
"cfg": "enable: true\nenable-gtid: false\nauto-fix-gtid: false\nrelay-dir: relay-dir\nmeta-dir: \"\"\nflavor: mysql\ncharset: \"\"\nenable-relay: false\nrelay-binlog-name: \"\"\nrelay-binlog-gtid: \"\"\nsource-id: mysql-replica-01\nfrom:\n host: 10.90.1.135\n port: 3306\n user: dm_user\n password: '******'\n max-allowed-packet: null\n session: {}\n security: null\npurge:\n interval: 3600\n expires: 0\n remain-space: 15\nchecker:\n check-enable: true\n backoff-rollback: 5m0s\n backoff-max: 5m0s\n check-interval: 5s\n backoff-min: 1s\n backoff-jitter: true\n backoff-factor: 2\nserver-id: 429530419\ntracer: {}\ncase-sensitive: false\nfilters: []\n"
}
```
```bash
~/.tiup/bin/tiup dmctl:vtidb_version \
--master-addr=${HOST_MONITOR1_PRIVATE_IP}:8261 operate-source show
```
```output
Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/vtidb_version/dmctl/dmctl --master-addr=10.90.4.66:8261 operate-source show
{
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"source": "mysql-replica-01",
"worker": "dm-10.90.3.187-8262"
}
]
}
```
### 步骤 6. 创建迁移任务配置并启动迁移任务
+ 迁移任务将把 `sbtest` 数据库从 MySQL server 迁移到 TiDB 集群。
+ 生成迁移任务配置。
```bash
cat << EOF > dm-task.yaml
name: "dm-task"
task-mode: all
ignore-checking-items: ["auto_increment_ID"]
target-database:
host: "${HOST_DB1_PRIVATE_IP}"
port: 4000
user: "dm_user"
password: "q1w2e3R4_"
mysql-instances:
- source-id: "mysql-replica-01"
route-rules: ["instance-1-user-schema-rule"]
block-allow-list: "rule_1"
mydumper-config-name: "global"
loader-config-name: "global"
routes:
instance-1-user-schema-rule:
schema-pattern: "sbtest"
target-schema: "sbtest"
block-allow-list:
rule_1:
do-dbs: ["sbtest"]
mydumpers:
global:
threads: 32
chunk-filesize: 64
loaders:
global:
pool-size: 8
import-mode: "physical"
pd-addr: "${HOST_PD1_PRIVATE_IP}:2379"
EOF
cat dm-task.yaml
```
```output
name: "dm-task"
task-mode: all
ignore-checking-items: ["auto_increment_ID"]
...
loaders:
global:
pool-size: 32
import-mode: "physical"
```
+ 预检查迁移任务。消息应为 `pre-check is passed`.
```bash
~/.tiup/bin/tiup dmctl:vtidb_version \
--master-addr=${HOST_MONITOR1_PRIVATE_IP}:8261 check-task ./dm-task.yaml
```
```output
Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/vtidb_version/dmctl/dmctl --master-addr=10.90.4.235:8261 check-task ./dm-task.yaml
{
"result": true,
"msg": "pre-check is passed. "
}
```
+ 启动迁移任务并检查迁移进度。
+ 迁移任务包含 3 个子任务单元： `Dump`, `Load` 和 `Sync`.
```bash
~/.tiup/bin/tiup dmctl:vtidb_version \
--master-addr=${HOST_MONITOR1_PRIVATE_IP}:8261 start-task ./dm-task.yaml
```
```output
Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/vtidb_version/dmctl/dmctl --master-addr=10.90.4.66:8261 start-task ./dm-task.yaml
{
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"source": "mysql-replica-01",
"worker": "dm-10.90.3.187-8262"
}
],
"checkResult": "pre-check is passed. "
}
```
### 步骤 7. 检查迁移进度
+ 多次使用 `query-status` 操作检查迁移进度。请注意 `subTaskStatus` 中的 `stage`、`unit` 和 `progress` 属性。.
+ 使用 `watch` 命令来监控整个任务的进度。根据实验室环境配置（由于它不是 PoC，我们使用低端服务器进行动手实验），可能需要大约 8 分钟（Dump：4 分钟，Load：4 分钟）。该喝杯咖啡了。
+ 单元 `Dump`，阶段 `Running`：
```bash
watch -n 1 -d ./.tiup/bin/tiup dmctl:vtidb_version --master-addr=${HOST_MONITOR1_PRIVATE_IP}:8261 query-status ./dm-task.yaml
```
```output
Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/vtidb_version/dmctl/dmctl --master-addr=10.90.4.235:8261 query-status ./dm-task.yaml
{
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"sourceStatus": {
"source": "mysql-replica-01",
"worker": "dm-10.90.1.147-8262",
"result": null,
"relayStatus": null
},
"subTaskStatus": [
{
"name": "dm-task",
"stage": "Running",
"unit": "Dump",
"result": null,
"unresolvedDDLLockID": "",
"dump": {
"totalTables": "6",
"completedTables": 3,
"finishedBytes": 7949200595,
"finishedRows": 39359277,
"estimateTotalRows": 59224808,
"bps": "57472325",
"progress": "64.63 %"
},
"validation": null
}
]
}
]
}
```
+ 单元 `Load`，阶段 `Running`：
```output
Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/vtidb_version/dmctl/dmctl --master-addr=10.90.4.196:8261 query-status ./dm-task.yaml
{
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"sourceStatus": {
"source": "mysql-replica-01",
"worker": "dm-10.90.4.196-8262",
"result": null,
"relayStatus": null
},
"subTaskStatus": [
{
"name": "dm-task",
"stage": "Running",
"unit": "Load",
"result": null,
"unresolvedDDLLockID": "",
"load": {
"finishedBytes": "5307344924",
"totalBytes": "8161057816",
"progress": "65.03 %",
"metaBinlog": "(binlog.000008, 157240317)",
"metaBinlogGTID": "",
"bps": "62882221"
},
"validation": null
}
]
}
]
}
```
+ 单元 `Sync`，阶段 `Running`。注意目标 TiDB 和源 MySQL 已同步。
```output
Starting component dmctl: /home/ec2-user/.tiup/components/dmctl/vtidb_version/dmctl/dmctl --master-addr=10.90.4.196:8261 query-status ./dm-task.yaml
{
"result": true,
"msg": "",
"sources": [
{
"result": true,
"msg": "",
"sourceStatus": {
"source": "mysql-replica-01",
"worker": "dm-10.90.4.196-8262",
"result": null,
"relayStatus": null
},
"subTaskStatus": [
{
"name": "dm-task",
"stage": "Running",
"unit": "Sync",
"result": null,
"unresolvedDDLLockID": "",
"sync": {
"totalEvents": "19767046",
"totalTps": "21118",
"recentTps": "0",
"masterBinlog": "(binlog.000011, 706201359)",
"masterBinlogGtid": "",
"syncerBinlog": "(binlog.000011, 706201359)",
"syncerBinlogGtid": "00000000-0000-0000-0000-000000000000:0",
"blockingDDLs": [
],
"unresolvedGroups": [
],
"synced": true,
"binlogType": "remote",
"secondsBehindMaster": "0",
"blockDDLOwner": "",
"conflictMsg": "",
"totalRows": "19767046",
"totalRps": "21118",
"recentRps": "0"
},
"validation": null
}
]
}
]
}
```
+ 当 `Sync` 单元达到 `"synced": true` 时，按 Ctrl+C 停止 `watch` 命令。
### 步骤 8. 验证 TiDB 存储层规模估算
+ 有 3 种方法可以获取 TiDB 中 `sbtest` 数据库的数据量。
+ 方法一：通过执行SQL获取数据大小，结果只包含`sbtest`数据库的数据量。.
+ 连接到 MySQL，并运行数据大小查询。
```bash
mysql -h ${HOST_MONITOR1_PRIVATE_IP} -P 6000 -uroot
```
```output
mysql>
```
+ 分析`sbtest`数据库的所有表。
```sql
ANALYZE TABLE sbtest.sbtest1, sbtest.sbtest2, sbtest.sbtest3, sbtest.sbtest4, sbtest.sbtest5, sbtest.sbtest6;
```
```output
Query OK, 0 rows affected, 6 warnings (22.93 sec)
```
+ 检索系统表获取数据大小。
```sql
SELECT db_name, SUM(approximate_size)/1024 size_GB
FROM information_schema.TIKV_REGION_STATUS
WHERE db_name='sbtest'
GROUP BY db_name;
```
```output
+---------+---------+
| db_name | size_GB |
+---------+---------+
| sbtest | 17.3232 |
+---------+---------+
1 row in set (0.01 sec)
```
+ 方法二：使用操作系统命令检查数据文件存储情况，数据量结果不仅包含`sbtest`数据库，还包含其他元数据。
+ 退出到操作系统提示符。
```sql
EXIT
```
```output
logout
Connection to 10.90.1.132 closed.
```
+ 获取 TiKV 节点的数据目录。
```bash
~/.tiup/bin/tiup cluster display tidb-demo
```
```output
Cluster type: tidb
Cluster name: tidb-demo
Cluster version: vtidb_version
Deploy user: ec2-user
SSH type: builtin
Dashboard URL: http://34.217.88.220:2379/dashboard
Grafana URL: http://10.90.4.32:3000
ID Role Host Ports OS/Arch Status Data Dir Deploy Dir
-- ---- ---- ----- ------- ------ -------- ----------
10.90.4.32:9093 alertmanager 10.90.4.32 9093/9094 linux/x86_64 Up /tidb-data/alertmanager-9093 /tidb-deploy/alertmanager-9093
10.90.4.32:3000 grafana 10.90.4.32 3000 linux/x86_64 Up - /tidb-deploy/grafana-3000
10.90.2.124:2379 pd 10.90.2.124 2379/2380 linux/x86_64 Up|L /tidb-data/pd-2379 /tidb-deploy/pd-2379
10.90.4.32:9090 prometheus 10.90.4.32 9090/12020 linux/x86_64 Up /tidb-data/prometheus-9090 /tidb-deploy/prometheus-9090
10.90.1.176:4000 tidb 10.90.1.176 4000/10080 linux/x86_64 Up - /tidb-deploy/tidb-4000
10.90.3.108:20160 tikv 10.90.3.108 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160
10.90.3.32:20160 tikv 10.90.3.32 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160
10.90.3.93:20160 tikv 10.90.3.93 20160/20180 linux/x86_64 Up /tidb-data/tikv-20160 /tidb-deploy/tikv-20160
Total nodes: 8
```
+ 连接其中一个 TiKV 节点并计算数据存储大小。
```bash
ssh -i /home/ec2-user/.ssh/pe-class-key-${LAB:REGION_NAME}.pem ec2-user@${HOST_KV1_PRIVATE_IP}
```
```output
Last login: Tue Jul 23 05:45:15 2024
8 package(s) needed for security, out of 27 available
Run "sudo yum update" to apply all updates.
```
```bash
sudo du -h /tidb-data/tikv-20160/db --max-depth=0
```
```output
6.0G /tidb-data/tikv-20160/db
```
+ 方法三：查看 Grafana 获取数据量，数据量结果中不仅包含 `sbtest` 数据库，还包含其他元数据。
+ 使用用户名 `admin` 和密码 `admin` 登录 Grafana 仪表板（如果没有出现 Grafana 链接，请点击左侧蓝色的 `REFRESH` 按钮），找到 `tidb-demo-TiKV-Summary - Server - Store size`。
+ 你将得到 tikv 集群的数据大小为 6.6GB * 3 = 19.8GB。
### 模块 2 结论
在步骤 8 中，所有三种方法的结果都接近演示 1 中的估计值。