前言：

最近遇到實例遷移的問題，數(shù)據(jù)遷完后還需要將數(shù)據(jù)庫用戶及權(quán)限遷移過去。進行邏輯備份時，我一般習(xí)慣將MySQL系統(tǒng)庫排除掉，這樣備份里面就不包含數(shù)據(jù)庫用戶相關(guān)信息了。這時候如果想遷移用戶相關(guān)信息 可以采用以下三種方案，類似的 我們也可以采用以下三種方案來備份數(shù)據(jù)庫賬號相關(guān)信息。（本文方案針對MySQL5.7版本，其他版本稍有不同）

1.mysqldump邏輯導(dǎo)出用戶相關(guān)信息

我們知道，數(shù)據(jù)庫用戶密碼及權(quán)限相關(guān)信息保存在系統(tǒng)庫mysql 里面。采用mysqldump可以將相關(guān)表數(shù)據(jù)導(dǎo)出來 如果有遷移用戶的需求 我們可以按照需求在另外的實例中插入這些數(shù)據(jù)。下面我們來演示下：

#只導(dǎo)出mysql庫中的user，db，tables_priv表數(shù)據(jù) #如果你有針隊column的賦權(quán) 可以再導(dǎo)出columns_priv表數(shù)據(jù)#若數(shù)據(jù)庫開啟了GTID 導(dǎo)出時最好加上 --set-gtid-purged=OFFmysqldump -uroot -proot mysql user db tables_priv -t --skip-extended-insert > /tmp/user_info.sql#導(dǎo)出的具體信息---- 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_native_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_password’,’*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’,’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;#在新的實例插入所需數(shù)據(jù) 就可以創(chuàng)建出相同的用戶及權(quán)限了

2.自定義腳本導(dǎo)出

首先拼接出創(chuàng)建用戶的語句：

SELECTCONCAT(’create user ’’, user, ’’@’’, Host, ’’’ ’ IDENTIFIED BY PASSWORD ’’, authentication_string,’’;’) AS CreateUserQueryFROMmysql.`user`WHERE`User` NOT IN (’mysql.session’,’mysql.sys’);#結(jié)果 在新實例執(zhí)行后可以創(chuàng)建出相同密碼的用戶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)

然后通過腳本導(dǎo)出用戶權(quán)限：

#導(dǎo)出權(quán)限腳本#!/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.sqlecho 'flush privileges;' >> /tmp/grants.sql#執(zhí)行腳本后結(jié)果-- 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直接導(dǎo)出用戶

mysqlpump是mysqldump的一個衍生，也是MySQL邏輯備份的工具。mysqlpump可用的選項更多，可以直接導(dǎo)出創(chuàng)建用戶的語句及賦權(quán)的語句。下面我們來演示下：

#exclude-databases排除數(shù)據(jù)庫 --users指定導(dǎo)出用戶 exclude-users排除哪些用戶 #還可以增加 --add-drop-user 參數(shù) 生成drop user語句#若數(shù)據(jù)庫開啟了GTID 導(dǎo)出時必須加上 --set-gtid-purged=OFFmysqlpump -uroot -proot --exclude-databases=% --users --exclude-users=mysql.session,mysql.sys > /tmp/user.sql#導(dǎo)出的結(jié)果-- 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 ’*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#可以看出 導(dǎo)出結(jié)果只包含創(chuàng)建用戶及賦權(quán)的語句 十分好用#mysqlpump詳細(xì)用法可參考：https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html

總結(jié)：

本篇文章介紹了三種導(dǎo)出數(shù)據(jù)庫用戶信息的方案，每種方案都給出了腳本并進行演示。同時 這三種方案稍加以封裝都可以作為備份數(shù)據(jù)庫用戶權(quán)限的腳本。可能你還有其他方案，如：pt-show-grants等，歡迎分享出來哦，也歡迎大家收藏或者改造成更適合自己的腳本，說不定什么時候就會用到哦 特別是一個實例有好多用戶時，你會發(fā)現(xiàn)腳本更好用哈。

以上就是MySQL如何優(yōu)雅的備份賬號相關(guān)信息的詳細(xì)內(nèi)容，更多關(guān)于MySQL 備份賬號相關(guān)信息的資料請關(guān)注好吧啦網(wǎng)其它相關(guān)文章！