Issues with TiDB Backup and Restore

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

Original topic: tidb备份恢复问题

| username: l940399478

[TiDB Version] v6.5.2
Premise:
The old cluster was upgraded from v5.1.4 to v6.5.2, and the new cluster was directly deployed with v6.5.2.
When restoring data through RESTORE DATABASE, an error occurred:
mysql> RESTORE DATABASE * FROM ‘local:///disk1/tidb_backup’;
ERROR 8125 (HY000): Restore failed: the config ‘new_collations_enabled_on_first_bootstrap’ not match, upstream: False, downstream: True: [BR:Common:ErrUnknown] internal error
According to the documentation, this is because the new_collation_enabled configuration in the mysql.tidb of the cluster upgraded from v5.1.4 to v6.5.2 is different.
In this case, how should the backup and restore be performed?

| username: 芮芮是产品 | Original post link

Export backup

| username: 芮芮是产品 | Original post link

mysqldump backup.sql
mysql < backup.sql

| username: Fly-bird | Original post link

Dumpling export and Lightning import

| username: TiDBer_小阿飞 | Original post link

Versions prior to 6.0 did indeed have this feature set to false by default, but it has been available since 4.0.
TiDB 6.0 introduced new collation rules and enabled the new collation framework by default. The new collation rules were introduced in TiDB 4.0 but were always disabled by default, and could only be changed during cluster initialization. You can see the value of this variable through the system table.

select * from mysql.tidb where variable_name = 'new_collation_enabled';

When using BR for data backup and recovery, you also need to pay attention to the collation settings to ensure that the cluster settings before backup and after recovery are the same, preventing errors due to different settings of the new_collations_enabled_on_first_bootstrap configuration item.
Is it possible to change the parameter value to take effect through a restart? Why don’t you try and see if it works?

service_configs:
  tidb:
    new_collations_enabled_on_first_bootstrap: true
| username: 芮芮是产品 | Original post link

Unable to take effect through a restart.

| username: 芮芮是产品 | Original post link

The old version only had one method for logical backup and logical export.

| username: l940399478 | Original post link

When using dumpling and lightning for export and import, do you also need to ignore the mysql database?

| username: h5n1 | Original post link

By default, Dumpling will export all databases except for system databases (including mysql, sys, INFORMATION_SCHEMA, PERFORMANCE_SCHEMA, METRICS_SCHEMA, and INSPECTION_SCHEMA).

| username: zhanggame1 | Original post link

Ignore by default

| username: l940399478 | Original post link

There is a method: first set up the new cluster with the lower version, and then upgrade it. Would this solve the issue of different parameters?

| username: WalterWj | Original post link

Use logical backup and lightning local restore.

| username: xingzhenxiang | Original post link

dumpling export
lightling import

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

Reinitialize the new cluster and set new_collation_enabled to false.

| username: Jellybean | Original post link

In v4.0, a configuration switch new_collations_enabled_on_first_bootstrap was added to support collations semantically. This can only be decided during the initial cluster setup. Starting from v6.0.0, the default value has been changed from false to true, making it compatible with MySQL case-insensitive comparisons. For already initialized clusters, you can confirm this through the new_collation_enabled variable in the mysql.tidb table. For more details, please refer to Character Set and Collation.

RESTORE is used for distributed recovery, restoring backup files generated by the BACKUP statement to the TiDB cluster. It uses the same engine as BR, meaning it is a physical backup and recovery operation.

Therefore, to solve your problem, there are two solutions:

  • Solution 1: Adjust the collation framework used by the new cluster to be consistent with the upstream old cluster.
    • This operation requires destroying and rebuilding the new cluster.
  • Solution 2: Do not use physical backup, use logical backup and recovery.
    • The dumpling+lightning tools can help you achieve data import and export.
| username: Kongdom | Original post link

:joy: We just encountered this. Reinstall the new cluster to version v5.1.4, then upgrade to v6.5.2, and finally restore it. This should work.

| username: andone | Original post link

Dumpling export and Lightning import

| username: l940399478 | Original post link

Haha, we have the same version.

| username: 舞动梦灵 | Original post link

Did the collation parameter change after you upgraded from 5.1.4 to 6.5.2?

| username: Kongdom | Original post link

The parameter value changed automatically from false to true, but it didn’t take effect. It still executed as false because this configuration only takes effect during initialization.