DeutschEspañolFrançaisItalianoNederlandsPortuguêsΕλληνικάРусскийالعربية中文(简体)中文(繁體)日本語한국어
 
Donate Bitcoins
Google
 
www smithii.com
 

Dumping the GRANT statements in MySQL

The following script will display your GRANT statements in MySQL in *nix:

$ mysql -e "SELECT
CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')
FROM mysql.user" --column-names=false -B | 
mysql --column-names=false -B 2>/dev/null |
sed 's/$/;/'

Here's a sample output:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'
IDENTIFIED BY PASSWORD '*FD1111DEADBEEFDEADBEEFDEADBEEFDEADBEEFDE'
WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' 
IDENTIFIED BY PASSWORD '*FD1111DEADBEEFDEADBEEFDEADBEEFDEADBEEFDE'
WITH GRANT OPTION;

On Windows, replace /dev/null with NUL, and remove the sed command:

c:> mysql -e "SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')
FROM mysql.user" --column-names=false -B | mysql --column-names=false -B 2>nul

You will then need to add the ; character manually.

You could even do this from inside the mysql client, in *nix:

$ mysql
mysql> SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')
FROM mysql.user INTO OUTFILE '/tmp/grants.sql';
mysql> source /tmp/grants.sql

and in Windows:

C:> mysql
mysql> SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')
FROM mysql.user INTO OUTFILE 'c:/grants.sql';
mysql> source c:/grants.sql

If you get an error message, try your %TEMP% directory:

C:> cd /d "%TEMP%"
C:> dir /x ..
Volume in drive C is volname
Volume Serial Number is FEED-FACE

Directory of C:\DOCUME~1\ross\LOCALS~1

01/23/2008  11:09 AM    <DIR>                       Apps
08/07/2008  03:10 PM    <DIR>                       Temp
               0 File(s)              0 bytes
               2 Dir(s)   6,727,245,824 bytes free
c:> mysql
mysql> SELECT CONCAT('SHOW GRANTS FOR ',QUOTE(user),'@',QUOTE(host),';')
FROM mysql.user INTO OUTFILE 'C:/DOCUME~1/yourusername/LOCALS~1/Temp/grants.sql';
mysql> source C:/DOCUME~1/yourusername/LOCALS~1/Temp/grants.sql
DeutschEspañolFrançaisItalianoNederlandsPortuguêsΕλληνικάРусскийالعربية中文(简体)中文(繁體)日本語한국어
 
Donate Bitcoins
Google
 
www smithii.com
 
Special thanks to Riester Rente Online for a generous donation!