Wednesday, March 14, 2012

Dumping MySQL Privileges and Users

Doing a mysqldump of a database is easy. You can take the resulting SQL file and re-create the database on another server. What you are missing is the users and their privileges that your app depends on for accessing the database. Here is how you can dump your users and privileges to a SQL file that can be easily imported on another server.

** Make sure to replace XXXXX with your actual root password. There is no space after -p

mysql -u root -pXXXXX -B -N $@ -e "SELECT DISTINCT CONCAT('SHOW GRANTS FOR ''', user, '''@''', host, ''';') AS query FROM mysql.user" | mysql -u root -pXXXXX $@ | sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}' > users.sql

The resulting users.sql file can be edited down to just the users you need and then used with the source command to import them into another MySQL server.