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 -uUSER -p --exclude-databases=% --add-drop-user --users > /tmp/pump-all-users_privileges-timestamp.sql
Dump completed in 1364 milliseconds
[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 @@SESSION.SQL_LOG_BIN= 0;
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
[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
--ignore options to include/exclude specific users in the backup.
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 -uUSER -p < user_privileges.sql
- 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.
Kjeld Flarup said:
My output of pt-show-grants –drop is
DROP USER ‘kjeld’@’localhost’;
DELETE FROM `mysql`.`user` WHERE `User`=’kjeld’ AND `Host`=’localhost’;
— Grants for ‘kjeld’@’localhost’
GRANT ALL PRIVILEGES ON *.* TO ‘kjeld’@’localhost’ WITH GRANT OPTION;
There is no statement like this:
CREATE USER IF NOT EXISTS ‘check’@’%’;
ALTER USER ‘check’@’%’ IDENTIFIED WITH ‘mysql_native_password’ AS ‘*B865CAE8F340F6CE1485A06F4492BB49718DF’ REQUIRE NONE PASSWORD EXPIRE
Kjeld Flarup said:
Found out that from MySql version 5.7 show grants nolonger exports passwords. Now there is a show create user.
Glad you found it out!
Pingback: Weekly Reading for Week of 6/16/2018 | Digital Owl's Prose
Mark Sullivan said:
Great information. Since last week, I am gathering details about the MySQL experience. There are some amazing details on your blog which I didn’t know. Thanks.
LikeLiked by 1 person
I like your article very much.
May i share your blog to following portal.
I’m fine if you share the links but not copying and pasting the contents.
Very good, practical, and precise info! However, you could use some code prettifier to highlight the commands. Currently, there seems no delimitation between the text and code.
Thanks for your feedback. I do use code blocking but I’d consider changing the style to make it more clear.