【TIDB】dumpling 配合tidb-lighting 以SQL文件形式进行备份恢复

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)