Need to quickly extract all the GRANT info for your database users?

Here’s a one-liner:

for i in $( mysql -u readuser -pReadPassword -h your.host -e "SELECT CONCAT(user,'@\'',host,'\'') AS ACL FROM mysql.user ORDER BY user" -s ); do mysql -u readuser -pReadPassword -h your.host -e "SHOW GRANTS FOR $i" -s ; done ;

Here’s a more elegant solution:

#!/bin/bash
################################################################################
echo -n ":: Enter MySQL username: "
read USER
echo -n ":: Enter MySQL password: "
read -s PASS
echo ""
echo -n ":: Enter MySQL hostname: "
read HOST
echo -n ":: Enter output filename: "
read FILENAME
echo "User: $USER Host: $HOST File: $FILENAME"
echo -n "Do you want to proceed? [Y/n]: "
read PROCEED

if [ "Y" == "$PROCEED" ]; then
 NOW=`date`
 echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "  >> $FILENAME
 echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "  >> $FILENAME
 echo " -- ACL extracted $NOW from $HOST"  >> $FILENAME
 echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "  >> $FILENAME
 echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "  >> $FILENAME
 for i in $( mysql -u $USER -p$PASS -h $HOST -e "SELECT CONCAT(user,'@\'',host,'\'') AS ACL FROM mysql.user ORDER BY user" -s );   
 do 
   echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "
   echo "-- $i "
   echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- "
   echo "-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- " >> $FILENAME
   echo "-- $i " >> $FILENAME
   ACL=`mysql -u $USER -p$PASS -h $HOST -e "SHOW GRANTS FOR $i" -s`
   echo "$ACL ;" >> $FILENAME; 
   echo ""
 done ;
 echo ""
 echo "# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #"
 echo "#   Output stored to: $FILENAME"
 echo "# # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # # #"
else
 echo "Bailing out"
fi
################################################################################

…