Privileges Issue

Hi guys
Recently I was assigned a task to restore our database to a new instance which was successfull and re were able to access the server using our credentials.
But we were getting this issue with some mysql users where they had explicit privileges to some database but we were getting access denied to those databases

Hi Sumit,

Thanks for reaching out.

I understand that after restoring your existing database to new instance, some of your mysql users are not able to login into the database. Please correct me if I’m wrong.

Can you please confirm how you restored to a new instance ? Was this a upgradation of mysql server or just an import and export of backup to new mysql instance ? If yes then how you performed these operations ? Which mysql version was/is it ?

When you say these users are getting access denied, how do you know that ? As a basic check, is it confirmed that they are entering correct password ? Was there any certificate required while logging for these users ?

These details will help us to understand the background.

I was able to login to the mysql user. But unable to see the databases.
In our environment we store the backups in a s3 bucket.
It was just an import and export of backup, we used xtrabackup to restore the full backup

I used the following command to restore

xtrabackup --decompress --remove-original --target-dir=${RESTORED_DIR}
xtrabackup --prepare --target-dir=${RESTORED_DIR}
xtrabackup --copy-back --target-dir=${RESTORED_DIR}
chown -R mysql:mysql /var/lib/mysql

Mysql version 5.7.25
There is no certificate required while logging for the users.

@_Sumit_Debnath Please let us know the commands used to create the backup. If you are ignoring the mysql system database, then user accounts would not be restored.

$ xtrabackup --backup --compress --target-dir=/data/compressed/

A couple of things, you should consider adding --parallel 4 to the backup, decompress, and prepare stages, along with --compress-threads 4 and --decompress-threads 4 where appropriate. And, add --use-memory 4G on the prepare phase. Those will improve performance overall.

Since there are no filters on your backup command, you are indeed backing up and restoring the mysql users table. Go to the source server and run ‘SHOW GRANTS FOR XX@XX’ and then run the same command on the restored MySQL server. Are they the same?

Thank fir the additional parameters.

I check the GRANT on both server its same

Hi @_Sumit_Debnath

You can try to export just the users from the source server with:

pt-show-grants --host=source > grants.sql

Then restore them on the new server:

mysql --host=new < grants.sql

This will reset the restored server to the identical grants that are in force on the source