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

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • 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. Understanding MySQL Storage Engines – MyISAM, MEMORY, BLACKHOLE and ARCHIVE
  2. Can MySQL Cluster Run Multiple Nodes On a Single Server
  3. Changing the value of lower_case_table_names in MySQL 8
  4. Troubleshooting MySQL Query hung – “Waiting for Table Flush”
  5. How to find location of MySQL configuration file(s)
  6. MySQL Backup stuck at “Starting to lock all the tables”
  7. How to List Tables Per Tablespace In MySQL Cluster
  8. MySQL Enterprise Backup (MEB): Lock the Tables While Making the Backup?
  9. MySQL Grants – Setting User Permissions On Different Tables
  10. What is the meaning of the TRX_TABLES_LOCKED column in the information_schema.INNODB_TRX MySQL table

You May Also Like

Primary Sidebar

Recent Posts

  • How to disable ACPI in CentOS/RHEL 7
  • How to Use real-time query to access data on a physical standby database
  • CentOS/RHEL 8: “ACPI MEMORY OR I/O RESET_REG” Server Hung after reboot
  • How to Create a Physical Standby Database by Using SQL and RMAN Commands
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary