如何备份MySQL的用户和权限？

使用 MySQLpump 或 pt-show-grants 备份用户和权限——作为 Create User 和 grant 语句——比备份 MySQL 系统数据库中的授权表要好得多。

概述

对于mysql的备份，常见的有物理备份和逻辑备份。对于逻辑备份，我们常用的工具是mydump，它可以对库、表进行逻辑的备份。但是在我们的实际工作中，我们有时需要对用户和权限进行备份，下面分享一下实现的具体方法。

解决方案

1、使用mysqlpump实用程序（作为创建用户和授权语句）

[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 @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'@'%';

您还可以使用--exclude-users=[comma separated usernames]从备份中排除某些用户或--include-users=[comma separated usernames]仅将某些用户包含在备份中。

2、使用 Percona 工具包 ( 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'@'%';

同样，您也可以使用--only或--ignore选项在备份中包含/排除特定用户。

注意：pt-show-grants 过去只打印导致恢复出现问题的授权语句（没有创建用户信息），尤其是在启用了sql 模式的情况下。 虽然 正如您从输出中看到的那样 - percona 团队已经解决了这个问题，但我仍然看到不必要的语句，例如从 mysql.user 表中删除用户记录NO_AUTO_CREATE_USERS 、DROP USER。

如何恢复

您可以通过以下命令简单地从上述任一方法恢复权限：

mysql -uUSER -p < user_privileges.sql

结论

• 使用 mysqlpump 或 pt-show-grants 备份用户和权限——作为 Create User 和 grant 语句——比备份 mysql 系统数据库中的授权表要好得多。
• 建议在正常备份计划作业中运行上述任一命令。