Backup grant privileges for a selected mysql database

Question

I have backup the mysql database db1 from a bunch of database hosted on the same server. Now need to backup only the selected users with privileges who have access to db1 so that I can restore these privileged users to my new server before the db1 restore.

Answer 1

Similar problem was already posted on StackOverflow: Backup MySQL users

To find users, who have only access to a given database, use this query (given you have sufficient privileges for the INFORMATION_SCHEMA and mysql database):

SELECT * FROM INFORMATION_SCHEMA.SCHEMA_PRIVILEGES WHERE TABLE_SCHEMA = 'db1';

or

SELECT * FROM mysql.db WHERE db = 'db1';

Also, you can view table-specific privileges for a user:

SELECT * FROM INFORMATION_SCHEMA.TABLE_PRIVILEGES WHERE TABLE_SCHEMA = 'db1';

or

SELECT * FROM mysql.tables_priv WHERE db = 'db1';