咔叽游戏

 找回密码
 立即注册

QQ登录

只需一步,快速开始

搜索
查看: 415|回复: 0

[Mysql] MySQL如何优雅的备份账号相关信息

[复制链接]
  • TA的每日心情
    无聊
    2019-5-27 08:20
  • 签到天数: 4 天

    [LV.2]圆转纯熟

    发表于 2020-10-4 16:11:50 | 显示全部楼层 |阅读模式
    前言:
    最近遇到实例迁移的问题,数据迁完后还需要将数据库用户及权限迁移过去。进行逻辑备份时,我一般习惯将MySQL系统库排除掉,这样备份里面就不包含数据库用户相关信息了。这时候如果想迁移用户相关信息 可以采用以下三种方案,类似的 我们也可以采用以下三种方案来备份数据库账号相关信息。(本文方案针对MySQL5.7版本,其他版本稍有不同)
    1.mysqldump逻辑导出用户相关信息

    我们知道,数据库用户密码及权限相关信息保存在系统库mysql 里面。采用mysqldump可以将相关表数据导出来 如果有迁移用户的需求 我们可以按照需求在另外的实例中插入这些数据。下面我们来演示下:
    1. #只导出mysql库中的user,db,tables_priv表数据
    2. #如果你有针队column的赋权 可以再导出columns_priv表数据
    3. #若数据库开启了GTID 导出时最好加上 --set-gtid-purged=OFF
    4. mysqldump -uroot -proot mysql user db tables_priv -t --skip-extended-insert > /tmp/user_info.sql
    5. #导出的具体信息
    6. --
    7. -- Dumping data for table `user`
    8. --
    9. LOCK TABLES `user` WRITE;
    10. /*!40000 ALTER TABLE `user` DISABLE KEYS */;
    11. 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','*
    12. 81F5E21E35407D884A6CD4A731AEBFB6AF209E1B','N','2019-03-06 03:03:15',NULL,'N');
    13. 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
    14. tive_password','*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE','N','2019-03-06 02:57:40',NULL,'Y');
    15. 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
    16. _password','*THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE','N','2019-03-06 02:57:40',NULL,'Y');
    17. 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','*
    18. 94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29','N','2019-04-19 06:24:54',NULL,'N');
    19. 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','*
    20. 2158DEFBE7B6FC24585930DF63794A2A44F22736','N','2019-04-19 06:27:45',NULL,'N');
    21. 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
    22. d','*8A447777509932F0ED07ADB033562027D95A0F17','N','2019-04-19 06:29:38',NULL,'N');
    23. /*!40000 ALTER TABLE `user` ENABLE KEYS */;
    24. UNLOCK TABLES;
    25. --
    26. -- Dumping data for table `db`
    27. --
    28. LOCK TABLES `db` WRITE;
    29. /*!40000 ALTER TABLE `db` DISABLE KEYS */;
    30. 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');
    31. 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');
    32. 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');
    33. /*!40000 ALTER TABLE `db` ENABLE KEYS */;
    34. UNLOCK TABLES;
    35. --
    36. -- Dumping data for table `tables_priv`
    37. --
    38. LOCK TABLES `tables_priv` WRITE;
    39. /*!40000 ALTER TABLE `tables_priv` DISABLE KEYS */;
    40. INSERT INTO `tables_priv` VALUES ('localhost','mysql','mysql.session','user','boot@connecting host','0000-00-00 00:00:00','Select','');
    41. INSERT INTO `tables_priv` VALUES ('localhost','sys','mysql.sys','sys_config','root@localhost','2019-03-06 02:57:40','Select','');
    42. INSERT INTO `tables_priv` VALUES ('%','test_db','test_user','t1','root@localhost','0000-00-00 00:00:00','Select,Insert,Update,Delete','');
    43. /*!40000 ALTER TABLE `tables_priv` ENABLE KEYS */;
    44. UNLOCK TABLES;
    45. #在新的实例插入所需数据 就可以创建出相同的用户及权限了
    复制代码
    2.自定义脚本导出

    首先拼接出创建用户的语句:
    1. SELECT
    2.         CONCAT(
    3.                 'create user \'',
    4.   user,
    5.   '\'@\'',
    6.   Host,
    7.   '\''
    8.   ' IDENTIFIED BY PASSWORD \'',
    9.   authentication_string,
    10.                 '\';'
    11.         ) AS CreateUserQuery
    12. FROM
    13.         mysql.`user`
    14. WHERE
    15.         `User` NOT IN (
    16.                 'mysql.session',
    17.                 'mysql.sys'
    18.         );
    19. #结果 在新实例执行后可以创建出相同密码的用户
    20. mysql> SELECT
    21.   -> CONCAT(
    22.   -> 'create user \'',
    23.   ->   user,
    24.   ->   '\'@\'',
    25.   ->   Host,
    26.   ->   '\''
    27.   ->   ' IDENTIFIED BY PASSWORD \'',
    28.   ->   authentication_string,
    29.   -> '\';'
    30.   -> ) AS CreateUserQuery
    31.   -> FROM
    32.   -> mysql.`user`
    33.   -> WHERE
    34.   -> `User` NOT IN (
    35.   -> 'mysql.session',
    36.   -> 'mysql.sys'
    37.   -> );
    38. +-------------------------------------------------------------------------------------------------+
    39. | CreateUserQuery                                         |
    40. +-------------------------------------------------------------------------------------------------+
    41. | create user 'root'@'%' IDENTIFIED BY PASSWORD '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B';   |
    42. | create user 'test'@'%' IDENTIFIED BY PASSWORD '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';   |
    43. | create user 'read'@'%' IDENTIFIED BY PASSWORD '*2158DEFBE7B6FC24585930DF63794A2A44F22736';   |
    44. | create user 'test_user'@'%' IDENTIFIED BY PASSWORD '*8A447777509932F0ED07ADB033562027D95A0F17'; |
    45. +-------------------------------------------------------------------------------------------------+
    46. 4 rows in set (0.00 sec)
    复制代码
    然后通过脚本导出用户权限:
    1. #导出权限脚本
    2. #!/bin/bash
    3. #Function export user privileges
    4. pwd=root
    5. expgrants()
    6. {
    7. mysql -B -u'root' -p${pwd} -N $@ -e "SELECT CONCAT( 'SHOW GRANTS FOR ''', user, '''@''', host, ''';' ) AS query FROM mysql.user" | \
    8. mysql -u'root' -p${pwd} $@ | \
    9. sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/-- \1 /;/--/{x;p;x;}'
    10. }
    11. expgrants > /tmp/grants.sql
    12. echo "flush privileges;" >> /tmp/grants.sql
    13. #执行脚本后结果
    14. -- Grants for read@%
    15. GRANT SELECT ON *.* TO 'read'@'%';
    16. -- Grants for root@%
    17. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
    18. -- Grants for test@%
    19. GRANT USAGE ON *.* TO 'test'@'%';
    20. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW ON `test_db`.* TO 'test'@'%';
    21. -- Grants for test_user@%
    22. GRANT USAGE ON *.* TO 'test_user'@'%';
    23. GRANT SELECT, INSERT, UPDATE, DELETE ON `test_db`.`t1` TO 'test_user'@'%';
    24. -- Grants for mysql.session@localhost
    25. GRANT SUPER ON *.* TO 'mysql.session'@'localhost';
    26. GRANT SELECT ON `performance_schema`.* TO 'mysql.session'@'localhost';
    27. GRANT SELECT ON `mysql`.`user` TO 'mysql.session'@'localhost';
    28. -- Grants for mysql.sys@localhost
    29. GRANT USAGE ON *.* TO 'mysql.sys'@'localhost';
    30. GRANT TRIGGER ON `sys`.* TO 'mysql.sys'@'localhost';
    31. GRANT SELECT ON `sys`.`sys_config` TO 'mysql.sys'@'localhost';
    复制代码
    3.mysqlpump直接导出用户
    mysqlpump是mysqldump的一个衍生,也是MySQL逻辑备份的工具。mysqlpump可用的选项更多,可以直接导出创建用户的语句及赋权的语句。下面我们来演示下:
    1. #exclude-databases排除数据库 --users指定导出用户 exclude-users排除哪些用户
    2. #还可以增加 --add-drop-user 参数 生成drop user语句
    3. #若数据库开启了GTID 导出时必须加上 --set-gtid-purged=OFF
    4. mysqlpump -uroot -proot --exclude-databases=% --users --exclude-users=mysql.session,mysql.sys > /tmp/user.sql
    5. #导出的结果
    6. -- Dump created by MySQL pump utility, version: 5.7.23, linux-glibc2.12 (x86_64)
    7. -- Dump start time: Fri Apr 19 15:03:02 2019
    8. -- Server version: 5.7.23
    9. SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
    10. SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
    11. SET @OLD_SQL_MODE=@@SQL_MODE;
    12. SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
    13. SET @@SESSION.SQL_LOG_BIN= 0;
    14. SET @OLD_TIME_ZONE=@@TIME_ZONE;
    15. SET TIME_ZONE='+00:00';
    16. SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT;
    17. SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS;
    18. SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION;
    19. SET NAMES utf8mb4;
    20. CREATE USER 'read'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*2158DEFBE7B6FC24585930DF63794A2A44F22736' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
    21. GRANT SELECT ON *.* TO 'read'@'%';
    22. CREATE USER 'root'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
    23. GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
    24. CREATE USER 'test'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
    25. GRANT USAGE ON *.* TO 'test'@'%';
    26. GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER, EXECUTE, CREATE VIEW, SHOW VIEW ON `test_db`.* TO 'test'@'%';
    27. CREATE USER 'test_user'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*8A447777509932F0ED07ADB033562027D95A0F17' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
    28. GRANT USAGE ON *.* TO 'test_user'@'%';
    29. GRANT SELECT, INSERT, UPDATE, DELETE ON `test_db`.`t1` TO 'test_user'@'%';
    30. SET TIME_ZONE=@OLD_TIME_ZONE;
    31. SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
    32. SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
    33. SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
    34. SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
    35. SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
    36. SET SQL_MODE=@OLD_SQL_MODE;
    37. -- Dump end time: Fri Apr 19 15:03:02 2019
    38. #可以看出 导出结果只包含创建用户及赋权的语句 十分好用
    39. #mysqlpump详细用法可参考:
    40. https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html
    复制代码
    总结:
    本篇文章介绍了三种导出数据库用户信息的方案,每种方案都给出了脚本并进行演示。同时 这三种方案稍加以封装都可以作为备份数据库用户权限的脚本。可能你还有其他方案,如:pt-show-grants等,欢迎分享出来哦,也欢迎大家收藏或者改造成更适合自己的脚本,说不定什么时候就会用到哦 特别是一个实例有好多用户时,你会发现脚本更好用哈。
    以上就是MySQL如何优雅的备份账号相关信息的详细内容,更多关于MySQL 备份账号相关信息的资料请关注咔叽论坛其它相关文章!

    原文地址:https://www.jb51.net/article/194392.htm

    QQ|免责声明|小黑屋|手机版|Archiver|咔叽游戏

    GMT+8, 2024-3-29 06:14

    Powered by Discuz! X3.4

    © 2001-2023 Discuz! Team.

    快速回复 返回顶部 返回列表