How to elegantly back up MySQL account information

Preface:

I recently encountered the problem of instance migration. After the data was migrated, the database users and permissions also needed to be migrated. When performing a logical backup, I usually exclude the MySQL system library so that the backup does not contain information related to database users. At this time, if you want to migrate user-related information, you can use the following three solutions. Similarly, we can also use the following three solutions to back up database account-related information. (This solution is for MySQL 5.7, other versions are slightly different)

1.mysqldump logically exports user-related information

We know that the database user password and permission-related information are stored in the system library mysql. Use mysqldump to export the relevant table data. If there is a need to migrate users, we can insert this data into another instance as required. Let's demonstrate this:

#Only export the user, db, and tables_priv tables in the mysql database. #If you have the privileges for the columns, you can export the columns_priv table data. #If the database has GTID enabled, it is best to add --set-gtid-purged=OFF when exporting.
mysqldump -uroot -proot mysql user db tables_priv -t --skip-extended-insert > /tmp/user_info.sql

#Export specific information--
-- Dumping data for table `user`
--

LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;
INSERT INTO `user` VALUES ('%','root','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','','','_binary '',_binary '',0,0,0,0,'mysql_native_password','*
81F5E21E35407D884A6CD4A731AEBFB6AF209E1B','N','2019-03-06 03:03:15',NULL,'N');
INSERT INTO `user` VALUES ('localhost','mysql.session','N','N','N','N','N','N','N','N','N','N','N','N','N','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','',_binary '',_binary '',0,0,0,0,'mysql_na
tive_password','*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE','N','2019-03-06 02:57:40',NULL,'Y');
INSERT INTO `user` VALUES ('localhost','mysql.sys','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','_binary '',_binary '',0,0,0,0,'mysql_native
_password','*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE','N','2019-03-06 02:57:40',NULL,'Y');
INSERT INTO `user` VALUES ('%','test','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','',_binary '',_binary '',0,0,0,0,'mysql_native_password','*'
94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29','N','2019-04-19 06:24:54',NULL,'N');
INSERT INTO `user` VALUES ('%','read','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','_binary '',_binary '',0,0,0,0,'mysql_native_password','*'
2158DEFBE7B6FC24585930DF63794A2A44F22736','N','2019-04-19 06:27:45',NULL,'N');
INSERT INTO `user` VALUES ('%','test_user','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','','_binary '',_binary '',0,0,0,0,'mysql_native_password
d','*8A447777509932F0ED07ADB033562027D95A0F17','N','2019-04-19 06:29:38',NULL,'N');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping data for table `db`
--

LOCK TABLES `db` WRITE;
/*!40000 ALTER TABLE `db` DISABLE KEYS */;
INSERT INTO `db` VALUES ('localhost','performance_schema','mysql.session','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N');
INSERT INTO `db` VALUES ('localhost','sys','mysql.sys','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','N','Y');
INSERT INTO `db` VALUES ('%','test_db','test','Y','Y','Y','Y','Y','N','N','Y','N','Y','N','Y','Y','N','N','Y','N','Y','N','N','Y','N','N');
/*!40000 ALTER TABLE `db` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Dumping data for table `tables_priv`
--

LOCK TABLES `tables_priv` WRITE;
/*!40000 ALTER TABLE `tables_priv` DISABLE KEYS */;
INSERT INTO `tables_priv` VALUES ('localhost','mysql','mysql.session','user','boot@connecting host','0000-00-00 00:00:00','Select','');
INSERT INTO `tables_priv` VALUES ('localhost','sys','mysql.sys','sys_config','root@localhost','2019-03-06 02:57:40','Select','');
INSERT INTO `tables_priv` VALUES ('%','test_db','test_user','t1','root@localhost','0000-00-00 00:00:00','Select,Insert,Update,Delete','');
/*!40000 ALTER TABLE `tables_priv` ENABLE KEYS */;
UNLOCK TABLES;

#Insert the required data into the new instance to create the same user and permissions

2. Custom script export

First, concatenate the statements to create a user:

SELECT
	CONCAT(
		'create user \'',
  user,
  '\'@\'',
  Host,
  '\''
  ' IDENTIFIED BY PASSWORD \'',
  authentication_string,
		'\';'
	) AS CreateUserQuery
FROM
	mysql.`user`
WHERE
	`User` NOT IN (
		'mysql.session',
		'mysql.sys'
	);
	
#Result: After executing in the new instance, a user with the same password can be created mysql> SELECT
  -> CONCAT(
  -> 'create user \'',
  -> user,
  -> '\'@\'',
  -> Host,
  -> '\''
  -> ' IDENTIFIED BY PASSWORD \'',
  -> authentication_string,
  -> '\';'
  -> ) AS CreateUserQuery
  -> FROM
  -> mysql.`user`
  -> WHERE
  -> `User` NOT IN (
  -> 'mysql.session',
  -> 'mysql.sys'
  -> );
+-----------------------------------------------------------------------------------------------------------------+
| CreateUserQuery |
+-----------------------------------------------------------------------------------------------------------------+
| create user 'root'@'%' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'; |
| create user 'test'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'; |
| create user 'read'@'%' IDENTIFIED BY PASSWORD '*2158DEFBE7B6FC24585930DF63794A2A44F22736'; |
| create user 'test_user'@'%' IDENTIFIED BY PASSWORD '*8A447777509932F0ED07ADB033562027D95A0F17'; |
+-----------------------------------------------------------------------------------------------------------------+
4 rows in set (0.00 sec)

Then export the user permissions through the script:

#Export permission script#!/bin/bash 
#Function export user privileges 
 
pwd=root 
expgrants() 
{ 
 mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
 mysql -u'root' -p${pwd} $@ | \
 sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}' 
} 
 
expgrants > /tmp/grants.sql
echo "flush privileges;" >> /tmp/grants.sql

#Result after executing the script-- Grants for read@% 
GRANT SELECT ON *.* TO 'read'@'%';

-- Grants for root@% 
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;

-- Grants for test@% 
GRANT USAGE ON *.* TO 'test'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW ON `test_db`.* TO 'test'@'%';

-- Grants for test_user@% 
GRANT USAGE ON *.* TO 'test_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `test_db`.`t1` TO 'test_user'@'%';

-- Grants for mysql.session@localhost 
GRANT SUPER ON *.* TO 'mysql.session'@'localhost';
GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';
GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost';

-- Grants for mysql.sys@localhost 
GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';
GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';
GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';

3.mysqlpump directly exports users

mysqlpump is a derivative of mysqldump and is also a tool for MySQL logical backup. mysqlpump has more options available, and can directly export statements for creating users and granting permissions. Let's demonstrate this:

#exclude-databases excludes databases --users specifies export users exclude-users excludes which users #You can also add the --add-drop-user parameter to generate a drop user statement #If the database has GTID enabled, you must add --set-gtid-purged=OFF when exporting
mysqlpump -uroot -proot --exclude-databases=% --users --exclude-users=mysql.session,mysql.sys > /tmp/user.sql

#Export results-- Dump created by MySQL pump utility, version: 5.7.23, linux-glibc2.12 (x86_64)
-- Dump start time: Fri Apr 19 15:03:02 2019
-- Server version: 5.7.23

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;
CREATE USER 'read'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*2158DEFBE7B6FC24585930DF63794A2A44F22736' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT SELECT ON *.* TO 'read'@'%';
CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
CREATE USER 'test'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT USAGE ON *.* TO 'test'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW ON `test_db`.* TO 'test'@'%';
CREATE USER 'test_user'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*8A447777509932F0ED07ADB033562027D95A0F17' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
GRANT USAGE ON *.* TO 'test_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `test_db`.`t1` TO 'test_user'@'%';
SET TIME_ZONE=@OLD_TIME_ZONE;
SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SET SQL_MODE=@OLD_SQL_MODE;
-- Dump end time: Fri Apr 19 15:03:02 2019

#It can be seen that the export results only contain statements for creating users and granting permissions, which is very useful. #For detailed usage of mysqlpump, please refer to:
https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html

Summarize:

This article introduces three solutions for exporting database user information. Each solution is given a script and demonstrated. At the same time, these three solutions can be slightly encapsulated and used as scripts for backing up database user permissions. Maybe you have other solutions, such as pt-show-grants, etc. You are welcome to share them. You are also welcome to collect or modify them into scripts that are more suitable for you. You never know when you will need them. Especially when there are many users on an instance, you will find that the script is more useful.

The above is the detailed information about how to elegantly back up MySQL accounts. For more information about MySQL backup accounts, please pay attention to other related articles on 123WORDPRESS.COM!

You may also be interested in:

How to write a MySQL backup script
A brief analysis of MySQL backup and recovery
Detailed explanation of mysql backup and recovery
MySQL scheduled backup solution (using Linux crontab)
Brief analysis of mysql scheduled backup tasks
How to automatically backup the script for Linux servers (mysql, attachment backup)
Linux regularly backs up the MySQL database and deletes previous backup files (recommended)
How to implement scheduled automatic backup of MySQL under CentOS7
Mysql backup multiple database code examples

<<: JavaScript implementation of classic snake game

>>: Detailed explanation of several ways to install CMake on Ubuntu