Backup all privileges related to specific user of MySQL

Question

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?

Answer 1

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.

Answer 2

By this script you can backup all of mysql's users except root:

mysql -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user != 'root'" | \
while read uh; do mysql -BNe "show grants for $uh" | sed 's/$/;/; s/\\\\/\\/g'; done > grants.sql

So, if you just need to export a specific user like (u_1)

mysql -BNe "select concat('\'',user,'\'@\'',host,'\'') from mysql.user where user = 'u_1'" | \
while read uh; do mysql -BNe "show grants for $uh" | sed 's/$/;/; s/\\\\/\\/g'; done > grants.sql

And you can restore it by:

mysql -u root <grants.sql