MySQL Bugs: #29043: Add mysqldump option to dump privileges in GRANT format

Bug #29043	Add mysqldump option to dump privileges in GRANT format
Submitted:	12 Jun 2007 6:37	Modified:	13 Jun 2007 9:54
Reporter:	Alexander Keremidarski	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The prefered way of setting privileges in MySQL is by using GRANT commands. 

However there is no way to dump such privileges in a file which can be reused for restoring privileges of particular customer or set of customers. 

MySQL has SHOW GRANTS syntax which can dump the privileges for particular users. 

However:

mysql -e "SHOW GRANTS .." > file 

doesn't work well because it is not supposed to produce list of executable statements.

mysqldump mysql 

also can't do the job because the privilege information is spread across several tables. 

It would be best to introduce an option to mysqldump for that purpose. 

Something like:

mysqldump --grants-for='user'@'host'

Some variant for all users. --grants-for-all or --grants-for=ALL maybe?

How to repeat:
NULL

Thank you for a reasonable feature request.

This page describes how to roll your own script:
http://serverfault.com/questions/8860/how-can-i-export-the-privileges-from-mysql-and-then-...

I need this too!
This is a basic feature , why missing this for so long ?  
All other databases vendors have at least a easy way to do this...

In information_schema there are USER_PRIVILEGES, SCHEMA_PRIVILEGES, COLUMN_PRIVILEGES and TABLE_PRIVILEGES tables available.

http://dev.mysql.com/doc/refman/5.6/en/user-privileges-table.html
http://dev.mysql.com/doc/refman/5.6/en/schema-privileges-table.html
http://dev.mysql.com/doc/refman/5.6/en/table-privileges-table.html
http://dev.mysql.com/doc/refman/5.6/en/column-privileges-table.html

The mysqluserclone utility from MySQL Utilities can be used to do dump users with the --dump option.
http://dev.mysql.com/doc/mysql-utilities/1.6/en/mysqluserclone.html