• 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. How to set the default character set in MySQL and how to propagate it in a master-master replication scenario
  2. How to Backup a MySQL Cluster
  3. What is the purpose of “mysql.sys@localhost” user
  4. “expect” script to provide password to mysql_config_editor
  5. How to Restore a Cluster Slave Using its Own Backups
  6. Understanding mysqlcheck and myisamchk utilities
  7. How to Configure 2-way replication in an existing Master, Slave MySQL Replication environment
  8. How to Create a MySQL Docker Container for Testing
  9. Managing MySQL Using Systemd As A Non Root User
  10. How to Use External Python modules in MySQL Shell

You May Also Like

Primary Sidebar

Recent Posts

  • What are /dev/zero and /dev/null files in Linux
  • grpck command – Remove corrupt or duplicate entries in the /etc/group and /etc/gshadow files.
  • xxd command – Expressed in hexadecimal form
  • sesearch: command not found

© 2022 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright