Showing entries 1 to 2
Displaying posts with tag: pt-show-grants (reset)
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 …

[Read more]
Is your backup complete? – backup MySQL users privileges / grants – pt-show-grants

Everybody knows the criticality of backup. You might have had your database dumped, data directory copied, flat files exported and even config and binary log files backed-up. But did you backup your MySQL user privileges? One can easily forget to backup the user privileges but it is much easier to do and you should backup your MySQL server user privileges / grants regularly. 1. Backup MySQL user privileges using mysqldump: $] mysqldump -uUSER -pPASS --databases mysql > mysql.sql 2. Copy MySQL data directory: $] cd /var/lib/mysql/ [ assuming default data directory ] mysql> flush tables with read lock; $] cp...

The post Is your backup complete? – backup MySQL users privileges / grants – pt-show-grants first appeared on ..::CHANGE is …

[Read more]
Showing entries 1 to 2