CODEWITHSUNDEEP
mysqldatabase-backupssql-grant
PiyusG
PiyusG

Reputation: 1157

Backup grant privileges for a selected mysql database

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.

Upvotes: 2

Views: 3332

Answers (1)

GregD
GregD

Reputation: 2877

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';

Upvotes: 2

Related Questions