Show all grants in MySQL

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

 

 

5 Comments

    1. phpMyAdmin is considered an insecure application in many environments(it is insecure). Not to mention it’s unable to handle large databases..

Leave a Reply

Your email address will not be published.