This handy one-liner will display all grants for all users on your MySQL database:
mysql –batch –skip-column-names -e “SELECT user, host FROM user” mysql | sed ‘s,\t,”@”,g;s,^,show grants for “,g;s,$,”;,g;’ | mysql –batch –skip-column-names | sed ‘s,$,;,g’
Ex. mysql –batch –skip-column-names -e “SELECT user, host FROM user” mysql | sed ‘s,\t,”@”,g;s,^,show grants for “,g;s,$,”;,g;’ | mysql –batch –skip-column-names | sed ‘s,$,;,g’
GRANT USAGE ON *.* TO ‘user1’@’%’ IDENTIFIED BY PASSWORD ‘*B8B7B8C47EF8381F4C2BBD441DAF0E52AEE035DF’;
GRANT ALL PRIVILEGES ON `mydb`.* TO ‘user1’@’%’;
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’127.0.0.1′ WITH GRANT OPTION;
GRANT USAGE ON *.* TO ”@’dbhost.domain’;
GRANT ALL PRIVILEGES ON *.* TO ‘root’@’dbhost.domain’ WITH GRANT OPTION;
GRANT USAGE ON *.* TO ”@’localhost’;
