• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

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

by admin

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!

Filed Under: mysql, MySQL Cluster

Some more articles you might also be interested in …

  1. MySQL: how to figure out which session holds which table level or global read locks
  2. How To Skip a Transaction on MySQL replication slave When GTIDs Are Enabled
  3. How To Rotate the MySQL Enterprise Audit Log Plugin Log Based On Size
  4. MySQL Server Error – “Can’t Create A New Thread (errno 11)”
  5. What are the various types of locking used in the MySQL Server
  6. Excluding a table or database from MySQL replication
  7. Beginners Guide to Implementing Table Maintenance in MySQL
  8. Can MySQL Cluster Run Multiple Nodes On a Single Server
  9. How can I get Excel data into MySQL, or vice versa?
  10. MySQL ‘show processlist’ statement

You May Also Like

Primary Sidebar

Recent Posts

  • pw-cat Command Examples in Linux
  • pvs: command not found
  • pulseaudio: command not found
  • pulseaudio Command Examples in Linux

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright