Shulou(Shulou.com)06/01 Report--

Foreword:

Recently, we have encountered the problem of instance migration, and the database users and permissions need to be migrated after the data migration. When making a logical backup, I am used to excluding the MySQL system library, so that the backup does not contain information about database users. 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 article is aimed at the MySQL5.7 version, while other versions are slightly different.)

1.mysqldump logic exports user-related information

We know that the database user password and permissions-related information is stored in the system library mysql. Mysqldump can be used to export the relevant table data. If there is a need to migrate the user, we can insert the data in another instance according to the demand. Let's demonstrate:

# only export the user,db,tables_ private table data in the mysql library # if you have the authorization of the needle team column, you can export the columns_ private table data again # if the database enables GTID export, it is best to add-- set-gtid-purged=OFFmysqldump-uroot-proot mysql user db tables_priv-t-skip-extended-insert > / tmp/user_info.sql# export details-Dumping data for table `user`-LOCK TABLES `user`WRITETM 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'', 0pje 0pje 0pr 0pr 0pl mysqlnativePasswordParticipe "81F5E21E35407D884A6CD4A731AEBFB6AF209E1BZ" Numerium "2019-03-06 03MWR 03WR 15" Null ") 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'', 0pje 0pje 0pl 0pr 0pl mysqlmnativePASORDTHATCANBEUSEDHEREE codified by THISNOTAVALPASORDTHATCANBEUSEDHEREE. 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'', 0pje 0pje 0pl 0pr 0pl mysqlmnativePASORDTHATCANBEUSEDHEREE codified by THISNOTAVALPASORDTHATCANBEUSEDHEREE. 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', 0BDCEBE19083CE2A1F959FD02F964C7AF4CFC29) 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'', 0pje 0pje 0pr 0pr 0pl mysqlnativePasswordEng. 2158DEFBE7B6FC24585930DF63794A2A44F22736 "reparations" Numi Leng "2019-04-19 06 FV 2745" parcels null ") 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' 8A447777509932F0ED07ADB033562027D95A0F17), 0pyrrhen0, _ binary', _ binary', _ binary', 0pyrrhen0, 0pyrrh, nativePasswordZhe, 8A447777509932F0ED07ADB033562027D95A0F17, "Noble", 2019-04-1906, "Null, 2938" / *! 40000 ALTER TABLE `user` ENABLE KEYS * /; UNLOCK TABLES;---- Dumping data for table `db`-- LOCK TABLES `db`WRITEAccording to 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','boot@connecting host','0000-00-0000: 0000: 00DISABLE KEYS'); INSERT INTO `tables_ priv` VALUES ('localhost','sys','mysql.sys','sys_config','root@localhost','2019-03-06 02DISABLE KEYS 5740') INSERT INTO `tables_ priv` VALUES ('%', 'test_db','test_user','t1','root@localhost','0000-00-0000: 00test_db','test_user','t1','root@localhost','0000-00-0000: 40000 ALTER TABLE inserting the required data into the new instance to create the same user and permissions 2. Custom script export

First concatenate the statement that creates the user:

SELECT CONCAT ('create user\', user,'\'@\', Host,'\ 'IDENTIFIED BY PASSWORD\', authentication_string,'\';') AS CreateUserQueryFROM mysql.`user`Were `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'* 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29signals; | | create user' read'@'%' IDENTIFIED BY PASSWORD'* 2158DEFBE7B6FC24585930DF63794A2A44F22736signals; | | create user' test_user'@'%' IDENTIFIED BY PASSWORD'* 8A4477509932F0ED07ADB033562027D95A0F17' | | | +-+ 4 rows in set (0.00 sec) |

Then export user permissions through a script:

# Export permission script #! / bin/bash # Function export user privileges pwd=root expgrants () {mysql-B-upright root'-p$ {pwd}-N $@-e "SELECT CONCAT ('SHOW GRANTS FOR', user,''@'', host,'';') AS query FROM mysql.user" |\ mysql-upright root'-p$ {pwd} $@ |\ sed's /\ (GRANT. *\) /\ AS query FROM mysql.user / S / ^\ (Grants for. *\) /--\ 1 /; /-- / {xtreptxx;}'} expgrants > / tmp/grants.sqlecho "flush privileges;" > > / tmp/grants.sql# results after execution of 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 a tool for logical backup of MySQL. Mysqlpump has more options available, and you can directly export the statements that create the user and the statements that give weight. Let's demonstrate:

# exclude-databases exclude database-- users specifies which users to export exclude-users to exclude # you can also add-- add-drop-user parameter to generate drop user statement # if the database has enabled GTID export, you must add-- set-gtid-purged=OFFmysqlpump-uroot-proot-- exclude-databases=%-- users-- exclude-users=mysql.session,mysql.sys > / tmp/user.sql# export result-- 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.23SET @ 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'* 8A4477509932F0ED07ADB0335620D95A0F17' 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 20pm you can see that the export result contains only the statements for creating users and authorizing users. # mysqlpump for more information, please see: http://www.cnblogs.com/zhoujinyi/p/5684903.htmlhttps://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html

Summary:

This article introduces three schemes for exporting database user information, each of which is scripted and demonstrated. At the same time, these three schemes can be used as scripts to back up the user rights of the database. You may have other solutions, such as pt-show-grants, etc., you are welcome to share them, and you are also welcome to collect or transform them into scripts that are more suitable for you. You may use them sometime, especially when an instance has a lot of users, you will find the script easier to use.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.