Skip to content
This repository was archived by the owner on Jul 24, 2024. It is now read-only.
This repository was archived by the owner on Jul 24, 2024. It is now read-only.

Restore system table mysql.user failed #1201

@fubinzh

Description

@fubinzh

Please answer these questions before submitting your issue. Thanks!

  1. What did you do?
    If possible, provide a recipe for reproducing the error.
    a. Add a user to TiDB
    b. Do full backup with "br backup ful ..."
    c. Delete the user from TiDB
    d. Do br full restore with below command:
    ./br restore full --pd "172.16.6.183:2379" -f "mysql*." -f "oomtest.*" --storage "local:///minio/fubin/tidb510_br510" --log-file system_full_backup2.restore.log

  2. What did you expect to see?
    System table mysql.user should be recovered correctly.

  3. What did you see instead?
    mysql.user table not recovered.

== error below is seen in system_full_backup2.restore.log: ==
[2021/06/10 14:37:28.639 +08:00] [INFO] [systable_restore.go:174] ["table existing, using replace into for restore"] [table=user] [schema=mysql]
[2021/06/10 14:37:28.639 +08:00] [WARN] [systable_restore.go:140] ["failed to execute SQL restore system database"] [table=user] [database=mysql] [sql="REPLACE INTO mysql.user SELECT * FROM __tidb_br_temporary_mysql.user;"] [error="[schema:1146]Table '__tidb_br_temporary_mysql.user' doesn't exist"] [errorVerbose="[schema:1146]Table '__tidb_br_temporary_mysql.user' doesn't exist

==detailed steps ==
[root@centos76_vm dev]# mysql -h172.16.6.151 -uroot -P4000 -E -e "CREATE USER 'toolsqa'@'%' IDENTIFIED BY 'changeme'; GRANT ALL PRIVILEGES ON . TO 'toolsqa'@'%'; FLUSH PRIVILEGES;"
[root@centos76_vm dev]# mysql -h172.16.6.151 -uroot -P4000 -E -e "select user from mysql.user;"
*************************** 1. row ***************************
user: root
*************************** 2. row ***************************
user: toolsqa

[root@centos76_vm dev]# ./br backup full --pd "172.16.6.183:2379" --storage "local:///minio/fubin/tidb510_br510" --log-file system_full_backup2.log
...
[ranges-succeed=28] [ranges-failed=0] [backup-fast-checksum=4.733924ms] [backup-checksum=55.927329ms] [backup-total-ranges=53] [backup-total-regions=53] [total-take=20.442842244s] [BackupTS=425540139813175297] [total-kv=1837] [total-kv-size=5.028MB] [average-speed=248.7kB/s] ["backup data size(after compressed)"=4.615MB]

[root@centos76_vm dev]# mysql -h172.16.6.151 -uroot -P4000 -E -e "drop user 'toolsqa'@'%';"
[root@centos76_vm dev]# mysql -h172.16.6.151 -uroot -P4000 -E -e "select user from mysql.user;"
*************************** 1. row ***************************
user: root

[root@centos76_vm dev]# mysql -h172.16.6.151 -uroot -P4000 -E -e "drop table oomtest.oomtest;"

[root@centos76_vm dev]# ./br restore full --pd "172.16.6.183:2379" -f "mysql*." -f "oomtest.*" --storage "local:///minio/fubin/tidb510_br510" --log-file system_full_backup2.restore.log
...
[2021/06/10 14:37:28.718 +08:00] [INFO] [collector.go:66] ["Full restore success summary"] [total-ranges=1] [ranges-succeed=1] [ranges-failed=0] [split-region=156.114092ms] [restore-checksum=9.020528ms] [restore-ranges=3] [total-take=1.478869855s] [total-kv=198] [total-kv-size=16.43kB] [average-speed=257.1kB/s] ["restore data size(after decompressed)"=4.615MB]

[root@centos76_vm dev]# mysql -h172.16.6.151 -uroot -P4000 -E -e "select count() from oomtest.oomtest;"
*************************** 1. row ***************************
count(
): 66
[root@centos76_vm dev]# mysql -h172.16.6.151 -uroot -P4000 -E -e "select user from mysql.user;"
*************************** 1. row ***************************
user: root

  1. What version of BR and TiDB/TiKV/PD are you using?

[root@centos76_vm dev]# ./br -V
Release Version: v5.0.0-master-dirty
Git Commit Hash: b9c4ea6
Git Branch: backupmetav2
Go Version: go1.16.5
UTC Build Time: 2021-06-09 12:33:55
Race Enabled: false

  1. Operation logs

    • Please upload br.log for BR if possible
    • Please upload tidb-lightning.log for TiDB-Lightning if possible
    • Please upload tikv-importer.log from TiKV-Importer if possible
    • Other interesting logs
  2. Configuration of the cluster and the task

    • tidb-lightning.toml for TiDB-Lightning if possible
    • tikv-importer.toml for TiKV-Importer if possible
    • topology.yml if deployed by TiUP
  3. Screenshot/exported-PDF of Grafana dashboard or metrics' graph in Prometheus if possible

Metadata

Metadata

Assignees

Type

No type

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions