After using BR to backup and restore TiDB, can the authorization-related MySQL database be exported and then imported using Mysqldump?

Translated
1

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

Original topic: tidb 用br备份还原后，授权相关的mysql库可以用Mysqldump的方式导出，再导入吗

| username: lxzkenney

tidb 5.0.3
The full backup of BR does not include the mysql database. Can the mysqldump method be used to export the user privilege database mysql and then import it into the new cluster?

2
| username: ddhe9527 | Original post link

You can use Dumpling to export the MySQL database and then use Lightning to import it.

dumpling -B mysql -f '*.*'

You can also use the combination of Mydumper and Myloader.

3
| username: ddhe9527 | Original post link

Or use BR to back up the MySQL database separately:

br backup db --db mysql
4
| username: lxzkenney | Original post link

Can mysqldump also be used?

5
| username: wisdom | Original post link

You can use mysqldump.

6
| username: lxzkenney | Original post link

Hello, do the tables in the MySQL database contain schema information unique to each cluster? Will there be issues when importing into a new cluster? I already imported the full backup data into the TiDB cluster yesterday. If some tables in the online cluster are rebuilt today, the table information might change. I randomly checked two tables and noticed that some tables’ IDs and range information were recorded. If the tables are rebuilt, the table IDs will change, and the range information might not be accurate. Will there be issues when importing into the new cluster?

7
| username: lxzkenney | Original post link

Hello, why does dumpling prompt that the mysql system database is not recognized?
I am using TiDB version 5.0.3. Does this version not support exporting the mysql database?

image
image1380×482 415 KB

8
| username: 啦啦啦啦啦 | Original post link

Yes, it is supported. This error is likely because only -B was added without -f. Try adding the -f parameter:
dumpling -h 127.0.0.1 -P 4000 -u root -t 16 -r 200000 -F 256MiB -B mysql -f 'mysql.*' -o /temp

9
| username: ddhe9527 | Original post link

I did not encounter the issue you mentioned in v5.4.0

Dingtalk_20220719135722
Dingtalk_202207191357221380×544 346 KB

Additionally, if you want to use mysqldump, it’s best to add the --hex-blob option because, based on my tests, without it, tables related to statistics might have garbled text issues.

Dingtalk_20220719140309
Dingtalk_20220719140309869×210 28.1 KB

Therefore, it is recommended to use tools from the TiDB ecosystem.

10
| username: lxzkenney | Original post link

Indeed, I only added -B. After adding -f, the task ran normally. :call_me_hand::call_me_hand::call_me_hand:

11
| username: lxzkenney | Original post link

Thank you very much! :pray::pray::pray:
I didn’t add -f to the dumpling backup, only added -B mysql, and it reported an error. Adding -f fixed it.
The mysqldump backup indeed had garbled text, now I’m using dumpling to export.

12
| username: cs58_dba | Original post link

The compatibility supported by dumping should be better.

13
| username: ablewang_xiaobo | Original post link

I’ve tried dumpling, mysqldump, and mydumper; all of them can be used for logical backups.

14
| username: system | Original post link

This topic was automatically closed 1 minute after the last reply. No new replies are allowed.