Exporting all GRANT statements from MySQL

Posted by richard on February 3, 2012

I am provisioning a new server which needs to fit into an existing infrastructure. There are many mysql users defined on each of the existing servers that I also need to provision on the new machine.

What I want to do is to simply export all of the permissions from an existing server in a format that I can apply that to the new server.

The following one-liner will do that. 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}

You can then take that output and run the statements against mysql on the new server and all GRANTS will be set up.