Exporting all MySQL user privileges

To export all MySQL user privileges run following script.

Note : Replace {host_name}, {user_name} and {password} with your values.

mysql -h {host_name} -u {user_name} -p{password} -Ne "select distinct concat( \"SHOW GRANTS FOR '\",user,\"'@'\",host,\"';\" ) from user;" mysql | mysql -h {host_name} -u {user_name} -p{password} | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'

Above script will generate all grants statements.You can then take that output and run the statements against MySQL on the new server.

 

Advertisements

2 thoughts on “Exporting all MySQL user privileges

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s