How to Specify the Target Database for TiDB Backup and Restore

Translated
1

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

Original topic: TiDB 备份还原如何指定目标数据库

| username: Hacker_92quIrMM

【TiDB Usage Environment】
Production Environment
【TiDB Version】
v4.0.12
【Encountered Issues】
1. Using dumpling for logical backup of a single table, the backup data includes the creation statements for the database and data table;
2. When using tidb-lightning, you need to specify the backup directory for restoration, and during restoration, it will recreate the database and data table;
3. Now, I want to restore to a different database instead of the original one. How should I proceed?

2
| username: songxuecheng | Original post link

Just like MySQL. If you want to switch databases, after exporting with Dumpling, try modifying the database in the create database schema to the one you want.

3
| username: HACK | Original post link

Modify the exported metadata file or manually create a database.

4
| username: Kongdom | Original post link

Provide an idea: can it be restored and then renamed to a new database name through rename?

5
| username: Hacker_92quIrMM | Original post link

Thank you for the support, but it doesn’t seem to work. Using rename in TiDB results in an error.

6
| username: Kongdom | Original post link

It does not directly support renaming the database name, but you can use Xiao Wang’s little trick to achieve it.

7
| username: Hacker_92quIrMM | Original post link

Oh, I see, it’s equivalent to transferring the table to another database.

8
| username: Kongdom | Original post link

Yes, that’s right.

9
| username: Hacker_92quIrMM | Original post link

Buddy, this doesn’t seem to work. There’s no error during execution, but no new database is created during recovery, and there are no corresponding tables either.

10
| username: Hacker_92quIrMM | Original post link

How can I modify this metadata that only contains basic timestamp data?

11
| username: weixiaobing | Original post link

There’s no need to make it so complicated. Lightning provides this functionality.
[[routes]]
schema-pattern = “test*”
target-schema = “test1”

12
| username: Hacker_92quIrMM | Original post link

Hello, does this configuration have any version requirements for tidb-lightning? I didn’t see this configuration item mentioned in the official documentation.

13
| username: db_user | Original post link

There are no version requirements, version 4 already has it.
Generally, there are two ways to operate:
Method 1: Use the routing function of Lightning

Method 2: Change the file name and file content through mv and sed

14
| username: Hacker_92quIrMM | Original post link

Thanks for the technical support, I’ll go find a table to test.

15
| username: cs58_dba | Original post link

Generally, logical backups can still modify the CREATE DATABASE statement, right?

16
| username: Hacker_92quIrMM | Original post link

Yes, you can directly modify this statement, but when restoring, it has no effect on the data.

17
| username: forever | Original post link

Directly restore, then rename table a.a to b.a; write a statement to rename it.

18
| username: cs58_dba | Original post link

Did it not work? What error was reported?

19
| username: Hacker_92quIrMM | Original post link

There were no errors, and the database was created successfully, but the data was still restored to the original database and has nothing to do with the newly created one.

20
| username: Hacker_92quIrMM | Original post link

Well, that’s one way to do it, but you still have to manually operate it once after restoring. What I’m thinking is to directly specify another database during the restoration.