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