Month: April 2012

Show all grants in MySQL

Posted by – 20/04/2012

Recently I needed to migrate a database server to a new machine. The databases were reloaded on the new machine from backup. However, only the production databases were in backup.

I needed to copy all the privileges and users to the new database server quickly. This was a busy server with many users and hosts using it.

The following one-liner did the trick:

mysql -N -u root -p$passwd -e "select user, host from mysql.user" | (while read user host; do echo "$(mysql -N -u root -p$passwd -e "show grants for '$user'@'$host'")" ";"; done ) > privileges.sql

Once you did this on the original server, you can feed the privileges.sql to MySQL on the new server:

cat privileges.sql | mysql -u root -p