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
This would be helpful in some cases when you have no idea who have access to your db :)
Awesome prince ya sayed
yes it is useful , you can also do it using something like phpmyadmin , Thanks to the author anyways :).
phpMyAdmin is considered an insecure application in many environments(it is insecure). Not to mention it’s unable to handle large databases..
i share Diaa the same comment :D