patternsqlMinor
Backup all privileges related to specific user of MySQL
Viewed 0 times
allprivilegesuserrelatedmysqlspecificbackup
Problem
I want to backup all privileges related to specific user (for example
But of course the
Also I tried this command:
But as expected, it only contains a row of
Is there a way to strip out other users except of
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 mysqlBut 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.sqlBut 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
(and any other 'hosts' involved.)
Then replay
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.