Restoring Database Privileges

I’ve seen several cases in the last few months where database users seemingly “disappear” or privileges appear to become invalid when restoring MySQL data or an entire server from raw backups. This may commonly be attributed to a MySQL version mismatch, for example, when a server is running MySQL 5.0 and then is suddenly running MySQL 5.1 or higher (or data is directly restored to a server running such), without having the database tables updated.

First things first, make sure to resolve the database version discrepancy, if one exists. Back up your data, then upgrade or downgrade MySQL to the version that your server should be running. Note that you should typically avoid downgrading MySQL to a previous version in order to prevent corrupting data. After you upgrade, run the command “mysql_upgrade” to fix the tables if the procedure in the referenced article doesn’t already do this for you.
To restore privileges:
Run the following command to dump the privilege table into a MySQL syntax that can be easily imported:

/scripts/grabmysqlprivs > /root/privileges.sql

*If you only need a certain user’s privileges, simply use grep to pull out that data:

/scripts/grabmysqlprivs | grep $user > /root/privileges.sql

On the server you need to restore to, simply import then SQL file:

mysql < /root/privileges.sql

This should be sufficient, however, if you see that the database users are not populated in cPanel, you can try remapping them:

/usr/local/cpanel/bin/setupdbmap