【TIDB】dumpling 导出csv 格式的文件配合tidb-lightning进行备份恢复

1.备份数据 

[tidb@mysql1 backup]$ dumpling -u root -P 4000 -h 192.168.1.11 -p 'rootroot' --filetype csv -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 11:01:08.180 +08:00] [INFO] [config.go:158] ["detect server type"] [type=TiDB]
[2024/08/18 11:01:08.181 +08:00] [INFO] [config.go:176] ["detect server version"] [version=5.1.1]
[2024/08/18 11:01:08.200 +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 11:01:08.204 +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 11:01:08.204 +08:00] [INFO] [base_client.go:253] ["[pd] switch leader"] [new-leader=http://192.168.1.13:2379] [old-leader=]
[2024/08/18 11:01:08.204 +08:00] [INFO] [base_client.go:103] ["[pd] init cluster id"] [cluster-id=7403658561768514175]
[2024/08/18 11:01:08.277 +08:00] [WARN] [block_allow_list.go:15] ["unsupported dump schema in TiDB now"] [schema=METRICS_SCHEMA]
[2024/08/18 11:01:08.277 +08:00] [WARN] [block_allow_list.go:15] ["unsupported dump schema in TiDB now"] [schema=PERFORMANCE_SCHEMA]
[2024/08/18 11:01:08.277 +08:00] [WARN] [block_allow_list.go:15] ["unsupported dump schema in TiDB now"] [schema=mysql]
[2024/08/18 11:01:08.278 +08:00] [WARN] [block_allow_list.go:15] ["unsupported dump schema in TiDB now"] [schema=INFORMATION_SCHEMA]
[2024/08/18 11:01:08.290 +08:00] [INFO] [ir_impl.go:207] ["get estimated rows count"] [estimateCount=8]
[2024/08/18 11:01:08.354 +08:00] [INFO] [ir_impl.go:207] ["get estimated rows count"] [estimateCount=20]
[2024/08/18 11:01:08.411 +08:00] [INFO] [main.go:216] ["dump data successfully, dumpling will exit now"]
[tidb@mysql1 backup]$ ll

2.备份数据检查 

[tidb@mysql1 backup]$ ll
total 24
-rwxr-xr-x 1 tidb tidb 122 Aug 18 11:01 metadata
-rwxr-xr-x 1 tidb tidb  84 Aug 18 11:01 test.my_test1.0.csv
-rwxr-xr-x 1 tidb tidb 150 Aug 18 11:01 test.my_test1-schema.sql
-rwxr-xr-x 1 tidb tidb 316 Aug 18 11:01 test.my_test2.0.csv
-rwxr-xr-x 1 tidb tidb 150 Aug 18 11:01 test.my_test2-schema.sql
-rwxr-xr-x 1 tidb tidb  66 Aug 18 11:01 test-schema-create.sql
[tidb@mysql1 backup]$ more test.my_test1.0.csv
"id","name"
1,"xsq1"
2,"xsq2"
3,"xsq3"
4,"xsq4"
1,"xsq1"
2,"xsq2"
3,"xsq3"
4,"xsq4"

3.清理并恢复数据 

--清理数据 
mysql> drop table test.my_test1; 
Query OK, 0 rows affected (0.52 sec)

mysql> drop table test.my_test2;
Query OK, 0 rows affected (0.51 sec)

--执行导入  
[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.498KiB, local available is 16.03GiB | critical    | true   |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 6 | Source csv files size is proper                                                     | performance | true   |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 7 | checkpoints are valid                                                               | critical    | true   |
+---+-------------------------------------------------------------------------------------+-------------+--------+
| 8 | table schemas are valid                                                             | critical    | true   |
+---+--------------------------------

4.数据检查 

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)

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.00 sec)

5.总结 
tidb-lightning 可以导入sql文件，也可以导入使用dumpling 导出的csv格式的文件。