-
Notifications
You must be signed in to change notification settings - Fork 100
Restore system table mysql.user failed #1201
Description
Please answer these questions before submitting your issue. Thanks!
-
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 -
What did you expect to see?
System table mysql.user should be recovered correctly. -
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
- 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
-
Operation logs
- Please upload
br.logfor BR if possible - Please upload
tidb-lightning.logfor TiDB-Lightning if possible - Please upload
tikv-importer.logfrom TiKV-Importer if possible - Other interesting logs
- Please upload
-
Configuration of the cluster and the task
tidb-lightning.tomlfor TiDB-Lightning if possibletikv-importer.tomlfor TiKV-Importer if possibletopology.ymlif deployed by TiUP
-
Screenshot/exported-PDF of Grafana dashboard or metrics' graph in Prometheus if possible