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格式的文件。