pt-show-grants --user='USERNAME' --ask-pass --database=mysql --host=my_server.com > xxxx_mysql_user_grants.sql
Next, the grants can be imported. When I take logical backups with mydumper/mysqldump, I don't export the mysql schema. This is why having a method to backup and restores users is important.
There can be circumstances where the Percona Toolkit cannot be installed for whatever reason and you have to do it manually.
This question has been discussed here:
http://serverfault.com/questions/8860/how-can-i-export-the-privileges-from-mysql-and-then-import-to-a-new-server
Here are couple of unique ways to get the same data (copied from the above link):
mysql -uUSER -pPASSWORD -h'my_server.lan' --skip-column-names -A -e"SELECT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') FROM mysql.user WHERE user<>''" | mysql -uUSER -pPASSWORD -h'my_server.lan' --skip-column-names -A | sed 's/$/;/g' > MySQLUserGrants.sql
"-uUSER -pPASSWORD" could be replaced with a defaults file so you don't need user/password on the command line.
In a bash script you could create a function like this:
mygrants()
{
mysql -B -N --host=prod-db1 --user=admin --password=secret -e "SELECT DISTINCT CONCAT(
'SHOW GRANTS FOR ''', user, '''@''', host, ''';'
) AS query FROM mysql.user WHERE user NOT IN ('root','phpmyadmin','debian-sys-maint')" | \
mysql --host=prod-db1 --user=admin --password=secret | \
sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}
Another caveat though is the above query doesn't work with MySQL 5.7. From the manual, "As of MySQL 5.7.6,
SHOW GRANTS output does not include IDENTIFIED BY PASSWORD clauses. Use the SHOW CREATE USER statement instead. " If you used this syntax on a MyQL 5.7 system then it would save the grants but not the password hash and if restoring on a MySQL 5.6 system, your passwords would be empty. See here: http://dev.mysql.com/doc/refman/5.7/en/show-grants.htmlIn order to get the password hash, you need to use:
mysql> SHOW CREATE USER 'root'@'localhost'\G
*************************** 1. row ***************************
CREATE USER for root@localhost: CREATE USER 'root'@'localhost'
IDENTIFIED WITH 'mysql_native_password'
AS '*2470C0C06DEE42FD1618BB99005ADCA2EC9D1E19'
REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK
See here:
http://dev.mysql.com/doc/refman/5.7/en/show-create-user.html