Backup all privileges related to specific user of MySQL

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.


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