The Geek Diary

MySQL – How to Backup User Privileges as CREATE USER and/or GRANT Statements

by

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
Note: You may need to add connection parameters such as –socket, –host, –port, etc.

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
Note: You may need to add connection parameters such as –socket, –host, –port, etc.

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.

Note: CREATE USER also exists in earlier version of MySQL but with limited support for options. For example, there is not full support for specifying the authentication plugin and password.

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 '.*'//"
Important: The example should only be used to backup users to be imported into MySQL 5.7 or later. In MySQL 5.6 and earlier this will create users with empty passwords!

You May Also Like