Dumping the GRANT statements in MySQL
Submitted by ross on Sun, 08/03/2008 - 14:42.
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
»
- ross's blog
- Login or register to post comments
