Backing up users & privileges in MySQL

In this article we will see and learn how to backing up Users and privileges in MySQL. There are two simple ways to backup only the users and privileges in MySQL:

1) Using mysqlpump utility (as create user and grant statements):

[shell ~]$ mysqlpump -u USER -p --exclude-databases=% --add-drop-user --users > /tmp/pump-all -users_privileges -timestamp.sql
Dump completed in 1364 milliseconds

Sample output:

[shell ~]$ head /tmp/pump -all -users_privileges -timestamp.sql
-- Dump created by MySQL pump utility, version: 5.7.21-20, Linux (x86_64)
-- Dump start time: Sun May 13 23:30:49 2018
-- Server version: 5.7.21

SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @OLD_SQL_MODE=@@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @OLD_TIME_ZONE=@@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
SET NAMES utf8mb4;
DROP USER 'check'@'%';
CREATE USER 'check'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*B865CAE8F340F6CE1485A06F4492BB49718DF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE ON *.* TO 'check'@'%';

You can also use --exclude-users=[comma separated usernames] to exclude some users from the backup or --include-users=[comma separated usernames] to include only some users in the backup.

2) Using Percona toolkit (pt-show-grants) :

[shell ~]$ pt-show-grants -uUSER --ask-pass --drop > /tmp/ptshowgrants-all-users_privileges-timestamp.sql

Sample output:

[shell ~]$ head /tmp/ptshowgrants -all -users_privileges-timestamp.sql
-- Grants dumped by pt-show-grants
-- Dumped from server Localhost via UNIX socket, MySQL 5.7.21-20-log at 2018-05-13 23:39:55
DROP USER 'check'@'%';
DELETE FROM `mysql`.`user` WHERE `User`='check' AND `Host`='%';
-- Grants for 'check'@'%'
CREATE USER IF NOT EXISTS 'check'@'%';
ALTER USER 'check'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*B865CAE8F340F6CE1485A06F4492BB49718DF' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT PROCESS, REPLICATION SLAVE, SELECT, SUPER ON *.* TO 'check'@'%';

Similarly, you can also use --only or --ignore options to include/exclude specific users in the backup.

Note: pt-show-grants used to print only the grants statements (no create user info) which caused issues in the restore, especially if the sql-mode NO_AUTO_CREATE_USERS is enabled. Although – as you can see from the output – percona team has fixed this issue but I still see unnecessary statements, e.g. delete the user record from mysql.user table (isn’t DROP USER sufficient enough to drop the user?!).

Restore the grants

You can simply restore the privileges from either methods above by the following command:

mysql -u USER -p < user_privileges.sql

So Conclusion will be:

• Backing up the user and privileges – as Create User and grant statements – using mysqlpump or pt-show-grants is much better than backing up the grant tables in mysql system database.

• It’s recommended to run either of the above commands in a scheduled job beside your normal backups.

That's all in this article. Please LIKE, SHARE & SUBSCRIBE to get more content like this.