Backup and Recovery

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 -uUSER -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 -uUSER -p < user_privileges.sql
Conclusion

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.

TSPITR Using ACFS Snapshots

Starting with Oracle Grid Infrastructure 12c (12.1), Oracle Cloud File system (ASM Cluster File System in cluster environment) supports database files (database version 11.2.0.4 and up) in addition to general-purpose files so that entire Oracle databases can be stored inside Oracle Cloud FS. In my earlier articles I demonstrated that: For a database having its files stored on Oracle Cloud… Continue Reading →

Integrating ACFS Snapshots With RMAN

In my last article, I demonstrated that starting with Oracle Grid Infrastructure 12c (12.1), Oracle Cloud File system (ASM Cluster File System in cluster environment) supports database files (database version 11.2.0.4 and up) in addition to general purpose files, so that entire Oracle databases can be stored inside Oracle Cloud FS. In addition, for a database with its files stored… Continue Reading →

Upgrading A Database Using Recovery Manager (RMAN) Duplicate Command In Oracle 12c

Introduction For versions of Oracle 11g R2, Oracle Recovery Manager (RMAN) replicates a database to a different Oracle home and opens it with NORESET LOGS option. From Oracle 12cR1, database RMAN introduced an option called ‘NO OPEN’, which duplicates your database to a new location and keeps it in an unopened state. Using this feature we can duplicate a database… Continue Reading →

Como efetuar backups utilizando RMAN no Oracle Database 12c

by Introdução: Atualmente temos encontrado nas comunidades Oracle e nos principais fóruns de discussão sobre o assunto Oracle Database 12c, inúmeros questionamentos sobre como realizar tarefas de administração de suas respectivas bases de dados de maneira eficiente com os novos conceitos trazidos pela versão Oracle 12c. A  administração destas bases de dados compreendem também o …
Read more

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569