HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

Backup all privileges related to specific user of MySQL

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
allprivilegesuserrelatedmysqlspecificbackup

Problem

I want to backup all privileges related to specific user (for example u_1) from a MySQL database and restore that in another server. As described here, The 'mysql' database contains users/privileges/passwords. So I have to backup all related data from all tables of mysql database (mysql.user,mysql.db, ...). I can run this command:

$ mysqldump -u root --flush-privileges -r mysql.sql -p mysql


But of course the mysql.sql contains all users and all privileges.

Also I tried this command:

$ mysqldump -u root -p --where="user.user='u_1'" mysql user> mysql.sql


But as expected, it only contains a row of mysql.user table.

Is there a way to strip out other users except of u_1?

Solution

Capture the output from

SHOW GRANTS FOR 'u_1'@localhost;
SHOW GRANTS FOR 'u_1'@'%';


(and any other 'hosts' involved.)

Then replay GRANTs to establish hist credentials elsewhere.

Code Snippets

SHOW GRANTS FOR 'u_1'@localhost;
SHOW GRANTS FOR 'u_1'@'%';

Context

StackExchange Database Administrators Q#186755, answer score: 3

Revisions (0)

No revisions yet.