Question: How to backup the users and their privileges as CREATE USER and/or GRANT statements?
The method you can use depends on the version of MySQL you are using.
MySQL 5.7 and Later
In MySQL 5.7 and later, the recommended way to backup the users is to use mysqlpump. This has built-in support for backing up the users and their privileges through the –users option:
$ mysqlpump --exclude-databases=% --users
MySQL 5.6 and Earlier
The easiest way to do this is to generate queries automatically and pass them back in to MySQL:
$ mysql --skip-column-names --execute "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user" | mysql --skip-column-names
This creates the GRANT statements required for each user, however there will be no semicolon at the end as delimiter. To add a semicolon you can use the sed command if that exists or on Windows a FOR construction:
Using sed:
$ mysql --skip-column-names --execute "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user" | mysql --skip-column-names | sed -e 's/$/;/'
Using FOR:
FOR /F "usebackq delims=" %G IN (`mysql --skip-column-names --execute "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user" | mysql --skip-column-names`) DO echo %G;
For Export to MySQL 5.7 and Later
In MySQL 5.7 it has become deprecated to create a user using the GRANT statement, and in MySQL 8.0 it is no longer supported at all. Instead a user is created using the CREATE USER statement.
You can generate the CREATE USER statements with the following query:
$ mysql --silent --skip-column-names --execute "SELECT CONCAT('CREATE USER ', QUOTE(user), '@', QUOTE(host), IF(LENGTH(plugin) > 0, CONCAT(' IDENTIFIED WITH ', plugin, IF(LENGTH(password) > 0, CONCAT(' AS ', QUOTE(password)), '')), IF(LENGTH(password) > 0, CONCAT(' IDENTIFIED BY PASSWORD ', QUOTE(password)), '')), ';') FROM mysql.user"
The GRANT statements can be exported as above, but you will need to remove the part setting the password. On Linux you can for example do that using sed, like:
$ mysql --skip-column-names --execute "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user" | mysql --skip-column-names | sed -e "s/ IDENTIFIED BY PASSWORD '.*'//"