教你如何優雅的用MySQL備份賬號相關資訊
導讀 | 最近遇到例項遷移的問題,資料遷完後還需要將資料庫使用者及許可權遷移過去。進行邏輯備份時,我一般習慣將MySQL系統庫排除掉,這樣備份裡面就不包含資料庫使用者相關資訊了。這時候如果想遷移使用者相關資訊 可以採用以下三種方案,類似的 我們也可以採用以下三種方案來備份資料庫賬號相關資訊。(本文方案針對MySQL5.7版本,其他版本稍有不同) |
1.mysqldump邏輯匯出使用者相關資訊
我們知道,資料庫使用者密碼及許可權相關資訊儲存在系統庫mysql 裡面。採用mysqldump可以將相關表資料匯出來 如果有遷移使用者的需求 我們可以按照需求在另外的例項中插入這些資料。下面我們來演示下:
#只匯出mysql庫中的user,db,tables_priv表資料 #如果你有針隊column的賦權 可以再匯出columns_priv表資料 #若資料庫開啟了GTID 匯出時最好加上 --set-gtid-purged=OFF mysqldump -uroot -proot mysql user db tables_priv -t --skip-extended-insert > /tmp/user_info.sql #匯出的具體資訊 -- -- 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','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; #在新的例項插入所需資料 就可以建立出相同的使用者及許可權了
2.自定義
匯出
首先拼接出建立使用者的語句:
SELECT CONCAT( 'create user \'', user, '\'@\'', Host, '\'' ' IDENTIFIED BY PASSWORD \'', authentication_string, '\';' ) AS CreateUserQuery FROM mysql.`user` WHERE `User` NOT IN ( 'mysql.session', 'mysql.sys' ); #結果 在新例項執行後可以建立出相同密碼的使用者 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)
然後透過 匯出使用者許可權:
#匯出許可權指令碼 #!/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 #執行指令碼後結果 -- 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直接匯出使用者
mysqlpump是mysqldump的一個衍生,也是MySQL邏輯備份的工具。mysqlpump可用的選項更多,可以直接匯出建立使用者的語句及賦權的語句。下面我們來演示下:
#exclude-databases排除資料庫 --users指定匯出使用者 exclude-users排除哪些使用者 #還可以增加 --add-drop-user 引數 生成drop user語句 #若資料庫開啟了GTID 匯出時必須加上 --set-gtid-purged=OFF mysqlpump -uroot -proot --exclude-databases=% --users --exclude-users=mysql.session,mysql.sys > /tmp/user.sql #匯出的結果 -- 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 #可以看出 匯出結果只包含建立使用者及賦權的語句 十分好用 #mysqlpump詳細用法可參考: https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html
總結:
本篇文章介紹了三種匯出資料庫使用者資訊的方案,每種方案都給出了指令碼並進行演示。同時 這三種方案稍加以封裝都可以作為備份資料庫使用者許可權的指令碼。可能你還有其他方案,如:pt-show-grants等,歡迎分享出來哦,也歡迎大家收藏或者改造成更適合自己的指令碼,說不定什麼時候就會用到哦 特別是一個例項有好多使用者時,你會發現指令碼更好用哈。
以上就是MySQL如何優雅的備份賬號相關資訊的詳細內容
原文來自:
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/69955379/viewspace-2719026/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Apple開發者賬號相關APP
- mongo 監控備份業務賬號建立Go
- Votingdisk, OCR, ASM 相關資訊及備份與恢復ASM
- 教你如何優雅處理Golang中的異常Golang
- 教你如何使用 PHP 優雅的生成 PDF 和 ImagePHP
- 如何消除MySQL賬號的安全隱患MySql
- MySQL如何優雅的刪除大表MySql
- 獲取app版本號相關資訊APP
- 程式中的敏感資訊如何優雅的處理?
- UNIX系統備份關鍵的資訊
- 如何優雅的關閉Go Channel「譯」Go
- Mysql 顯示錶的相關資訊 --命令MySql
- 關於 MySQL root 賬號的預設密碼MySql密碼
- Mysql的優化的相關知識MySql優化
- 看promise教你如何優雅的寫js非同步程式碼PromiseJS非同步
- mysql之 xtrabackup原理、備份日誌分析、備份資訊獲取MySql
- 如何在微信公眾號優雅的展示程式碼
- 備份容災相關概念總結
- 部落格園資料備份相關
- 關於開發人員申請蘋果賬號相關事宜蘋果
- RMAN備份中的通道(CHANNEL)相關 - PARALLELISM 、FILESPERSETParallel
- RMAN備份相關的動態效能檢視
- 學習NGUI前的準備NGUI的相關資訊NGUI
- 申請 mysql 賬號MySql
- MySQL中如何選擇合適的備份策略和備份工具MySql
- 如何優雅的關閉Java執行緒池Java執行緒
- mysql的冷備份與熱備份MySql
- MySQL索引統計資訊更新相關的引數MySql索引
- 停止使用迴圈 教你用underscore優雅的寫程式碼
- 如何優雅的搞垮伺服器,再優雅的救活伺服器
- 10個教程教你輕鬆備份MySQL資料庫MySql資料庫
- Linux根據程式號,檢視相關資訊Linux
- 如何優雅的將Flutter引入現有應用?Flutter
- 如何優雅的使用介面
- 如何優雅的使用MyBatis?MyBatis
- 如何優雅的寫Markdown
- 教你在Linux中如何歸檔和備份Linux
- 備份相關的動態效能檢視及監控