1.使用dumpling 备份tidb 为sql 文件
[tidb@mysql1 backup]$ dumpling -u root -P 4000 -h 192.168.1.11 -p 'rootroot' --filetype sql -t 8 -o /tidb/backup -r 200000 -F 256MiB
Release version: v4.0.7
Git commit hash: 48b61dc62c7e27c99b81d939e4614f07e3f3a9d2
Git branch: heads/refs/tags/v4.0.7
Build timestamp: 2020-09-29 06:52:06Z
Go version: go version go1.13 linux/amd64
[2024/08/18 10:56:06.409 +08:00] [INFO] [config.go:158] ["detect server type"] [type=TiDB]
[2024/08/18 10:56:06.409 +08:00] [INFO] [config.go:176] ["detect server version"] [version=5.1.1]
[2024/08/18 10:56:06.430 +08:00] [INFO] [client.go:148] ["[pd] create pd client with endpoints"] [pd-address="[192.168.1.13:2379,192.168.1.11:2379,192.168.1.12:2379]"]
[2024/08/18 10:56:06.435 +08:00] [INFO] [base_client.go:237] ["[pd] update member urls"] [old-urls="[http://192.168.1.13:2379,http://192.168.1.11:2379,http://192.168.1.12:2379]"] [new-urls="[http://192.168.1.11:2379,http://192.168.1.12:2379,http://192.168.1.13:2379]"]
[2024/08/18 10:56:06.435 +08:00] [INFO] [base_client.go:253] ["[pd] switch leader"] [new-leader=http://192.168.1.13:2379] [old-leader=]
[2024/08/18 10:56:06.435 +08:00] [INFO] [base_client.go:103] ["[pd] init cluster id"] [cluster-id=7403658561768514175]
[2024/08/18 10:56:06.641 +08:00] [WARN] [block_allow_list.go:15] ["unsupported dump schema in TiDB now"] [schema=PERFORMANCE_SCHEMA]
[2024/08/18 10:56:06.641 +08:00] [WARN] [block_allow_list.go:15] ["unsupported dump schema in TiDB now"] [schema=mysql]
[2024/08/18 10:56:06.641 +08:00] [WARN] [block_allow_list.go:15] ["unsupported dump schema in TiDB now"] [schema=INFORMATION_SCHEMA]
[2024/08/18 10:56:06.641 +08:00] [WARN] [block_allow_list.go:15] ["unsupported dump schema in TiDB now"] [schema=METRICS_SCHEMA]
[2024/08/18 10:56:06.666 +08:00] [INFO] [ir_impl.go:207] ["get estimated rows count"] [estimateCount=4]
[2024/08/18 10:56:06.787 +08:00] [INFO] [ir_impl.go:207] ["get estimated rows count"] [estimateCount=10]
[2024/08/18 10:56:06.909 +08:00] [INFO] [main.go:216] ["dump data successfully, dumpling will exit now"]
2.检查导出结果
[tidb@mysql1 backup]$ ll
total 24
-rwxr-xr-x 1 tidb tidb 122 Aug 18 10:56 metadata
-rwxr-xr-x 1 tidb tidb 107 Aug 18 10:56 test.my_test1.0.sql
-rwxr-xr-x 1 tidb tidb 150 Aug 18 10:56 test.my_test1-schema.sql
-rwxr-xr-x 1 tidb tidb 241 Aug 18 10:56 test.my_test2.0.sql
-rwxr-xr-x 1 tidb tidb 150 Aug 18 10:56 test.my_test2-schema.sql
-rwxr-xr-x 1 tidb tidb 66 Aug 18 10:56 test-schema-create.sql
3.执行导入
[tidb@mysql1 backup]$ tiup tidb-lightning -config /tidb/tidb-lightning.toml
Starting component `tidb-lightning`: /home/tidb/.tiup/components/tidb-lightning/v5.1.1/tidb-lightning -config /tidb/tidb-lightning.toml
Verbose debug logs will be written to tidb-lightning.log
+---+-------------------------------------------------------------------------------------+-------------+--------+
| # | CHECK ITEM | TYPE | PASSED |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 1 | Cluster is available | critical | true |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 2 | Cluster has no other loads | performance | true |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 3 | Lightning has the correct storage permission | critical | true |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 4 | Cluster resources are rich for this import task | critical | true |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 5 | local disk resources are rich, source dir has 4.447KiB, local available is 15.72GiB | critical | true |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 6 | Source csv files size is proper | performance | true |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 7 | checkpoints are valid | critical | true |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 8 | table schemas are valid | critical | true |
+---+-------------------------------------------------------------------------------------+-------------+--------+
tidb lightning exit
4.数据检查
mysql> select * from test.my_test1;
+------+------+
| id | name |
+------+------+
| 1 | xsq1 |
| 2 | xsq2 |
| 3 | xsq3 |
| 4 | xsq4 |
| 1 | xsq1 |
| 2 | xsq2 |
| 3 | xsq3 |
| 4 | xsq4 |
+------+------+
8 rows in set (0.01 sec)
mysql> select * from test.my_test2;
+------+-------------+
| id | name |
+------+-------------+
| 1 | 薛双奇1 |
| 2 | 薛双奇2 |
| 3 | 薛双奇3 |
| 4 | 薛双奇4 |
| 5 | 薛双奇5 |
| 6 | 薛双奇6 |
| 7 | 薛双奇7 |
| 8 | 薛双奇8 |
| 9 | 薛双奇9 |
| 10 | 薛双奇10 |
| 1 | 薛双奇1 |
| 2 | 薛双奇2 |
| 3 | 薛双奇3 |
| 4 | 薛双奇4 |
| 5 | 薛双奇5 |
| 6 | 薛双奇6 |
| 7 | 薛双奇7 |
| 8 | 薛双奇8 |
| 9 | 薛双奇9 |
| 10 | 薛双奇10 |
+------+-------------+
20 rows in set (0.00 sec)