How to back up account information gracefully

Time:2019-12-2

Preface:

Recently, we have encountered the problem of instance migration. After data migration, we need to migrate database users and permissions. When doing logical backup, I am usually used to exclude MySQL system library, so that the backup does not contain database user related information. At this time, if you want to migrate user related information, you can use the following three schemes. Similarly, we can also use the following three schemes to back up database account related information. (this scheme is for MySQL version 5.7, and other versions are slightly different.)

1. Mysqldump logic exports user related information

We know that the database user password and authority related information are saved in the system databasemysql Inside. Mysqldump can be used to export related table data. If there is a need for migrating users, we can insert these data into another instance as required. Let’s demonstrate:

#Only the user, DB, tables in MySQL database can be exported 
#If you have the authorization of needle column, you can export the columns priv table data
#If gtid export is enabled in the database, it is better to add -- set gtid purged = off
mysqldump -uroot -proot mysql user db tables_priv -t --skip-extended-insert > /tmp/user_info.sql

#Exported details
--
-- 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 '',_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','N','N','Y','N','N','N','N','N','N','N','N','N','N','N','N','N','',_binary '',_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','N','',_binary '',_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','N','',_binary '',_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','N','',_binary '',_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','N','',_binary '',_binary '',_binary '',0,0,0,0,'mysql_native_passwor
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');
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','Y','N','N','N','Y','N','N','Y','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','[email protected] host','0000-00-00 00:00:00','Select','');
INSERT INTO `tables_priv` VALUES ('localhost','sys','mysql.sys','sys_config','[email protected]','2019-03-06 02:57:40','Select','');
INSERT INTO `tables_priv` VALUES ('%','test_db','test_user','t1','[email protected]','0000-00-00 00:00:00','Select,Insert,Update,Delete','');
/*!40000 ALTER TABLE `tables_priv` ENABLE KEYS */;
UNLOCK TABLES;

#Insert the required data in the new instance to create the same users and permissions

2. Custom script export

First, splice the statement to create the 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'
    );
    
#As a result, users with the same password can be created after the new instance is executed
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 [email protected] -e "SELECT CONCAT(  'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
  mysql -u'root' -p${pwd} [email protected] | \
  sed 's/\(GRANT .*\)/;/;s/^\(Grants for .*\)/--  /;/--/{x;p;x;}'  
}  
 
expgrants > /tmp/grants.sql
echo "flush privileges;" >> /tmp/grants.sql

#Results after script execution
-- Grants for [email protected]% 
GRANT SELECT ON *.* TO 'read'@'%';

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

-- Grants for [email protected]% 
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 [email protected]% 
GRANT USAGE ON *.* TO 'test_user'@'%';
GRANT SELECT, INSERT, UPDATE, DELETE ON `test_db`.`t1` TO 'test_user'@'%';

-- Grants for [email protected] 
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 [email protected] 
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 exports users directly

Mysqlpump is a derivative of mysqldump and a tool for MySQL logical backup. Mysqlpump has more options available. It can directly export the statements for creating users and empowering statements. Let’s demonstrate:

#Exclude databases exclude databases -- users specifies which users are excluded by the export user exclude users 
#You can also add the -- add drop user parameter to generate a drop user statement
#If gtid export is enabled in the database, you must add -- set gtid purged = off
mysqlpump -uroot -proot --exclude-databases=% --users  --exclude-users=mysql.session,mysql.sys > /tmp/user.sql

#Exported 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 @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @[email protected]@SQL_MODE;
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET @@SESSION.SQL_LOG_BIN= 0;
SET @[email protected]@TIME_ZONE;
SET TIME_ZONE='+00:00';
SET @[email protected]@CHARACTER_SET_CLIENT;
SET @[email protected]@CHARACTER_SET_RESULTS;
SET @[email protected]@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 [email protected]_TIME_ZONE;
SET [email protected]_CHARACTER_SET_CLIENT;
SET [email protected]_CHARACTER_SET_RESULTS;
SET [email protected]_COLLATION_CONNECTION;
SET [email protected]_FOREIGN_KEY_CHECKS;
SET [email protected]_UNIQUE_CHECKS;
SET [email protected]_SQL_MODE;
-- Dump end time: Fri Apr 19 15:03:02 2019

#It can be seen that the export result is very easy to use only the statements that create users and empower them
#For detailed usage of mysqlpump, please refer to:
http://www.cnblogs.com/zhoujinyi/p/5684903.html
https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html

Conclusion:

This paper introduces three schemes of exporting database user information, each of which gives a script and demonstrates it. At the same time, these three schemes can be used as the script of backup database user rights. Maybe you have other solutions, such as Pt show grants. Welcome to share them. Welcome to collect or transform them into scripts that are more suitable for you. Maybe they will be used sometime, especially when there are many users in an instance, you will find that the scripts are better.