How to Copy User Privileges Between MySQL Servers

Question How to copy user privileges from one MySQL server to another, including:

• between different versions
• without losing existing users and privileges on the destination server
• without duplicate key errors if a user already exists on the destination server

For 5.7.8 or later, the simplest way is to use mysqlpump.

$ mysqlpump --exclude-databases=% --users

For older versions, one approach is to copy the privilege tables in the ‘mysql’ database directly, either by shutting down the server or locking the tables and copying the files, or with a logical backup such as:

$ mysqldump -h sourceHost mysql | mysql -h destinationHost mysql

This approach raises several problems:

• Existing users and privileges will be lost. If they are retained by using ‘mysqldump –no-create-info –skip-drop-table’, any user that exists in both servers (such as ‘root’@’localhost’) will cause a duplicate key error.
• If the destination server runs a newer version of MySQL than the host server, extra steps are required to repair the privilege tables. If the versions are far enough apart, it may not be possible to simply maintain the privileges from the old system, e.g. when the password hash changed format and length.
• There is no simple way to downgrade the privilege tables, so copying privileges from a new version to an old version is very difficult.

A better approach is to recreate the GRANT statements for each user.

• GRANT works with existing users. If the user already exists, new privileges will be added.
• GRANT is generally forward compatible, and is backward compatible if no specific privileges for new features are used (e.g., GRANT ALL ON… is the same in 5.5 and 5.0, but GRANT PROXY ON… is not available in 5.0).

MySQL provides a SHOW GRANTS statement which lists all grants for a given user. The list of all users may be found in the ‘mysql’.‘user’ table. A script can fetch that list of users and call SHOW GRANTS for each of them. The output can be sent to the destination server. Because SHOW GRANTS does not include a statement delimiter, some extra care is needed to make the output usable by the mysql client.

On systems where sed exists (generally Unix-like, or Windows with Cygwin):

$ mysql -h [sourceHost] -N -e "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user" | mysql -h [sourceHost] -N | sed 's/$/;/' | mysql -h [destinationHost]

On Windows:

FOR /F "usebackq delims=" %G IN (`mysql -h [sourceHost] -N -e "SELECT CONCAT('SHOW GRANTS FOR ', QUOTE(user), '@', QUOTE(host), ';') FROM mysql.user" ^| mysql -h [sourceHost] -N`) DO echo %G; | mysql -h [destinationHost]