Showing all mysql grants

 

Quick oneliner script to retrieve all grants

There will be times when you need to migrate all of your mysql accounts from one system to another. From experience I have found that it is far more efficient to user access through grants than to import the mysql table. To make the job easier I have written a script to show all of the grants.


#!/bin/bash

for i in `mysql -B -N -e "SELECT user,host FROM user" mysql|sed 's/\t/,/g'`;
  do
   USER=$(echo $i | cut -d, -f1);
   HOST=$(echo $i | cut -d, -f2);
   mysql -B -N -e "SHOW GRANTS FOR '$USER'@'$HOST'";
done


Sample Output

GRANT USAGE ON `main`.* TO 'mainRO'@'localhost' IDENTIFIED BY PASSWORD ''
GRANT SELECT, LOCK TABLES ON `main`.* TO 'mainRO'@'localhost'
GRANT USAGE ON `main`.* TO 'mainRW'@'localhost' IDENTIFIED BY PASSWORD ''
GRANT INSERT, UPDATE ON `main`.* TO 'mainRW'@'localhost'

Comments

 

To fear love is to fear life, and those who fear life are already
three parts dead.
-- Bertrand Russell