个人中心
gpt4 book ai didi

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

转载 作者：qq735679552 更新时间：2022-09-28 22:32:09 26 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章MySQL如何优雅的备份账号相关信息由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

前言:

最近遇到实例迁移的问题，数据迁完后还需要将数据库用户及权限迁移过去。进行逻辑备份时，我一般习惯将MySQL系统库排除掉，这样备份里面就不包含数据库用户相关信息了。这时候如果想迁移用户相关信息 可以采用以下三种方案，类似的 我们也可以采用以下三种方案来备份数据库账号相关信息。（本文方案针对MySQL5.7版本，其他版本稍有不同） 。

1.mysqldump逻辑导出用户相关信息 。

我们知道，数据库用户密码及权限相关信息保存在系统库mysql 里面。采用mysqldump可以将相关表数据导出来 如果有迁移用户的需求 我们可以按照需求在另外的实例中插入这些数据。下面我们来演示下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
#只导出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.自定义脚本导出 。

首先拼接出创建用户的语句:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
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)

然后通过脚本导出用户权限:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
#导出权限脚本
#!/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可用的选项更多，可以直接导出创建用户的语句及赋权的语句。下面我们来演示下:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
#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如何优雅的备份账号相关信息的详细内容，更多关于MySQL 备份账号相关信息的资料请关注我其它相关文章！ 。

原文链接：https://cloud.tencent.com/developer/article/1500381 。

最后此篇关于MySQL如何优雅的备份账号相关信息的文章就讲到这里了,如果你想了解更多关于MySQL如何优雅的备份账号相关信息的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

26 4 0
文章推荐： Python3爬虫带上cookie的实例代码
文章推荐： kafka与storm集群环境的安装步骤详解
文章推荐： python实现批处理文件
文章推荐： MySQL Aborted connection告警日志的分析
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com