Dumping mysql users with pt-grants or manually

In my opinion, the best way to dump and restore mysql users is using pt-grants from the Percona Toolkit. The tool can be run like this or with a combination of other parameters:

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.html

In 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