Issues with TiDB Database Backup and Restore: Restoring Table Structure Without Table Data

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: TIDB数据库备份还原问题,表结构还原,无表数据。

| username: TiDBer_ydSkDlLw

[Test Environment] Testing environment
[TiDB Version] V8.1.0

The TiDB backup successfully ran the restore command,
tiup br restore db -s “local:///opt/back1013” --pd “192.168.1.243:2379” --db test --log-file backuptable1701.log
After testing, it can only operate on the original database, and it cannot restore to a newly created empty database.

Problem encountered: I deleted all table structures in the test database, turning it into an empty database. When running the restore command, the database structure was successfully restored, but the data in the tables was not restored. What could be the issue here?

| username: zhaokede | Original post link

Is there any log information?

| username: TiDBer_ydSkDlLw | Original post link

[2024/06/12 17:12:56.711 +08:00] [INFO] [collector.go:77] [“Database Backup success summary”] [total-ranges=1] [ranges-succeed=1] [ranges-failed=0] [backup-checksum=12.874806ms] [backup-fast-checksum=612.781µs] [backup-total-ranges=2] [backup-total-regions=2] [total-take=4.498950966s] [total-kv=3] [total-kv-size=138B] [average-speed=30.67B/s] [backup-data-size(after-compressed)=1.801kB] [Size=1801] [BackupTS=450411514701283329]
root@yiduo-VirtualBox:~# tiup br restore db -s “local:///opt/back1013” --pd “192.168.1.243:2379” --db test --log-file backuptable1701.log
Checking updates for component br… Timedout (after 2s)
Starting component br: /root/.tiup/components/br/v8.1.0/br restore db -s local:///opt/back1013 --pd 192.168.1.243:2379 --db test --log-file backuptable1701.log
Detail BR log in backuptable1701.log
DataBase Restore <--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------> 100.00%
[2024/06/12 17:15:26.060 +08:00] [INFO] [collector.go:77] [“DataBase Restore failed summary”] [total-ranges=1] [ranges-succeed=0] [ranges-failed=1] [split-region=369.652µs] [restore-ranges=1] [unit-name=file] [error="Cannot read local:///opt/back1013/1/139326_160_95267b81c5cc673196738318ace6fbc310cb9a12248f346dfb6bbe12bc57ccfc_1718183576724_write.sst into /tidb-data/tikv-20162/import/.temp/aed8f965-688a-4302-90ba-d7fcc095899f_158048_153_161_write_2.sst: No such file or directory (os error 2): [BR:KV:ErrKVDownloadFailed]download sst failed; Cannot read local:///opt/back1013/1/139326_160_95267b81c5cc673196738318ace6fbc310cb9a12248f346dfb6bbe12bc57ccfc_1718183576724_write.sst into /tidb-data/tikv-20162/import/.temp/1b1f3416-d4f8-470d-8dfb-f1d96532bb50_158048_153_161_write_2.sst: No such file or directory (os error 2): [BR:KV:ErrKVDownloadFailed]download sst failed; Cannot read local:///opt/back1013/1/139326_160_95267b81c5cc673196738318ace6fbc310cb9a12248f346dfb6bbe12bc57ccfc_1718183576724_write.sst into /tidb-data/tikv-20162/import/.temp/1325de64-b412-48f8-b63a-c8fb45147cee_158048_153_161_wr
Omitted Middle Part************
:KV:ErrKVDownloadFailed]download sst failed; Cannot read local:///opt/back1013/1/139326_160_95267b81c5cc673196738318ace6fbc310cb9a12248f346dfb6bbe12bc57ccfc_1718183576724_write.sst into /tidb-data/tikv-20162/import/.temp/287904c0-fc7c-4489-b7db-38cd609ec755_158048_153_161_write_2.sst: No such file or directory (os error 2): [BR:KV:ErrKVDownloadFailed]download sst failed; Cannot read local:///opt/back1013/1/139326_160_95267b81c5cc673196738318ace6fbc310cb9a12248f346dfb6bbe12bc57ccfc_1718183576724_write.sst into /tidb-data/tikv-20162/import/.temp/adef9b6d-520d-438a-a4cd-b2b2c39eb648_158048_153_161_write_2.sst: No such file or directory (os error 2): [BR:KV:ErrKVDownloadFailed]download sst failed; Cannot read local:///opt/back1013/1/139326_160_95267b81c5cc673196738318ace6fbc310cb9a12248f346dfb6bbe12bc57ccfc_1718183576724_write.sst into /tidb-data/tikv-20162/import/.temp/c23d3935-2b74-4812-aef7-aac3723c6301_158048_153_161_write_2.sst: No such file or directory (os error 2): [BR:KV:ErrKVDownloadFailed]download sst failed

| username: zhaokede | Original post link

Brother, is it possible that your backup data files are not accessible? Please check.

| username: zhaokede | Original post link

Omitting the middle part, it keeps reporting
[BR:KV:ErrKVDownloadFailed] download sst failed; Cannot read

| username: TiDBer_ydSkDlLw | Original post link

This is the directory I just successfully backed up. Backup was successful, but when I run the restore, there are issues. Where should I start checking?

| username: zhaokede | Original post link

You can take a look at the network and permissions. Compare the differences in backing up to different database environments.

| username: TiDBer_ydSkDlLw | Original post link

I tried restoring again this morning, and it was successful. No operations were performed.

I checked the backup files, and there are files in the backup directory on 192.168.1.243, while there is an .sst file in the backup directory on 192.168.1.233.

I repeated the process, deleted the files on both servers, and backed up again.

The results were different this time. The backup directory on 192.168.1.233 was empty, while a folder named “11” appeared on 192.168.1.243, containing .SST files. Then I tried restoring again, but it failed.

I’m wondering if it’s a permission issue. As shown in the image, why is it that when I run the backup command as root, the “11” folder is backed up under the tidb user group, while other files are under the root user group? If that’s the case, if I want to restore as root, it should be an access issue. The question then is, when I run the restore command, which user is actually running it in the background, and how should I handle these file permissions?

| username: lemonade010 | Original post link

Is it possible that the folder specified by local is not NFS, or has it not been merged?

| username: zhaokede | Original post link

Are the users logging into the control machine the same? Both when the restoration is successful and when it fails?

| username: zhaokede | Original post link

Or the server directory where the data files are stored has not granted access permissions to the user logging into the control machine.

| username: tidb菜鸟一只 | Original post link

Are all your TiKV nodes on one machine now?

| username: zhanggame1 | Original post link

Please describe the environment. How many TiKV instances are there? Are they deployed on the same machine? What kind of environment are you restoring?

| username: TiDBer_ydSkDlLw | Original post link

I just tested it in another environment using the same method.
1 PD and 3 KVs are all installed on one server, and using the same testing method, backup and restore work normally.

I haven’t yet identified the issue with the two-cluster setup.

My testing method is to first create a database, set up the table structure, and insert data. Then, I perform a backup.
I manually delete the table structure and data, making the current database an empty one. When I run the restore command, the result shows the permission issue as mentioned above.

| username: zhanggame1 | Original post link

BR backup has a copy of the data on each TiKV, and only when combined does it constitute the complete data. During restoration, each TiKV needs to be able to access the combined data. Please check if this has been achieved.

| username: ziptoam | Original post link

This question is quite difficult, I don’t really understand it.