使用 MySQLpump 或 pt-show-grants 備份用戶和權(quán)限——作為 Create User 和 grant 語句——比備份 MySQL 系統(tǒng)數(shù)據(jù)庫中的授權(quán)表要好得多。
概述
對(duì)于mysql的備份，常見的有物理備份和邏輯備份。對(duì)于邏輯備份，我們常用的工具是mydump，它可以對(duì)庫、表進(jìn)行邏輯的備份。但是在我們的實(shí)際工作中，我們有時(shí)需要對(duì)用戶和權(quán)限進(jìn)行備份，下面分享一下實(shí)現(xiàn)的具體方法。
解決方案
1、使用mysqlpump實(shí)用程序（作為創(chuàng)建用戶和授權(quán)語句）
[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選項(xiàng)在備份中包含/排除特定用戶。
注意： pt-show-grants 過去只打印導(dǎo)致恢復(fù)出現(xiàn)問題的授權(quán)語句（沒有創(chuàng)建用戶信息），尤其是在啟用了sql 模式的情況下。 雖然 正如您從輸出中看到的那樣 - percona 團(tuán)隊(duì)已經(jīng)解決了這個(gè)問題，但我仍然看到不必要的語句，例如從 mysql.user 表中刪除用戶記錄NO_AUTO_CREATE_USERS 、DROP USER。
如何恢復(fù)
您可以通過以下命令簡(jiǎn)單地從上述任一方法恢復(fù)權(quán)限：
mysql -uUSER -p < user_privileges.sql
結(jié)論
- 使用 mysqlpump 或 pt-show-grants 備份用戶和權(quán)限——作為 Create User 和 grant 語句——比備份 mysql 系統(tǒng)數(shù)據(jù)庫中的授權(quán)表要好得多。
- 建議在正常備份計(jì)劃作業(yè)中運(yùn)行上述任一命令。
