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

 

 

5 Comments on Show all grants in MySQL

  1. diaa says:

    This would be helpful in some cases when you have no idea who have access to your db :)

  2. Awesome prince ya sayed

  3. wael fareed says:

    yes it is useful , you can also do it using something like phpmyadmin , Thanks to the author anyways :).

    • broken says:

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

  4. zoghbi says:

    i share Diaa the same comment :D

Leave a Reply

Your email address will not be published.