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

邹强发布于2019-08-01 14:17 / 3046人阅读

摘要：进行逻辑备份时，我一般习惯将系统库排除掉，这样备份里面就不包含数据库用户相关信息了。这时候如果想迁移用户相关信息可以采用以下三种方案，类似的我们也可以采用以下三种方案来备份数据库账号相关信息。

前言：

最近遇到实例迁移的问题，数据迁完后还需要将数据库用户及权限迁移过去。进行逻辑备份时，我一般习惯将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详细用法可参考：
http://www.cnblogs.com/zhoujinyi/p/5684903.html
https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html

总结：

本篇文章介绍了三种导出数据库用户信息的方案，每种方案都给出了脚本并进行演示。同时这三种方案稍加以封装都可以作为备份数据库用户权限的脚本。可能你还有其他方案，如：pt-show-grants等，欢迎分享出来哦，也欢迎大家收藏或者改造成更适合自己的脚本，说不定什么时候就会用到哦特别是一个实例有好多用户时，你会发现脚本更好用哈。

专线服务云服务器 ecs云服务器的相关信息变更账号的实名认证信息如何变更签约的支付宝账号腾讯视频如何登录别人的会员账号

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/49453.html

创业初期数据库云备份之laravel解决方案

摘要：在中注册发布之后，我们会产生一个监听器，文件已经存储在本地，获取该文件信息将文件上传至七牛云存入数据库上面的部分代码没写，大家可以去自己去试下，加深对七牛云及的理解，完全可以达到目的。最近项目用户徒增，购买的服务器还是和公司另外一个项目共用的同一台服务器，想着要是服务器崩了，还没个容灾方案，万一。。。不得被用户整死选用方案 laravel-backup 配合laravel的任务调...

2bdenny 2019-06-28 18:24 评论0 收藏0
活动实录|拒绝"删库到跑路"，探究饿了么数据安全保障体系

摘要：数人云告别人肉运维上海的实录第二弹来啦本次分享的嘉宾是饿了么团队负责人虢国飞。虢国飞饿了么团队负责人从事数据库领域年，主要关注于数据库管理自动化建设和等领域的研究。本次主题关于数据安全的保障。在这一层，饿了么做了一些数据方面相关的保护。数人云告别人肉运维上海Meetup的实录第二弹来啦！本次分享的嘉宾是饿了么DBA团队负责人虢国飞。实录将从用户访问、数据库架构体系、数据备份、数据流转...

xiaowugui666 2019-05-28 17:02 评论0 收藏0
活动实录|拒绝"删库到跑路"，探究饿了么数据安全保障体系

摘要：数人云告别人肉运维上海的实录第二弹来啦本次分享的嘉宾是饿了么团队负责人虢国飞。虢国飞饿了么团队负责人从事数据库领域年，主要关注于数据库管理自动化建设和等领域的研究。本次主题关于数据安全的保障。在这一层，饿了么做了一些数据方面相关的保护。数人云告别人肉运维上海Meetup的实录第二弹来啦！本次分享的嘉宾是饿了么DBA团队负责人虢国飞。实录将从用户访问、数据库架构体系、数据备份、数据流转...

qc1iu 2019-06-28 15:37 评论0 收藏0
云计算技术在高速公路上的应用实践

摘要：新的动力年月，国务院发布国务院关于促进云计算创新发展培育信息产业新业态的意见，云计算成为互联网行动计划的新基础设施。第一部分初识云计算、迁移上云1．1初识云计算、迁移上云2012年，当我们使用远程桌面客户端登录云端主机的那一刻开始，新IT基础设施时代来了，基于传统硬件模式的各业务系统架构将会发生巨变。我们在云端搭建好主机、数据库、网络等环境后，联系了网站、投资、人力资源、财务管理等系统的软...

dunizb 2019-04-29 19:30 评论0 收藏0
网易云 MySQL实例迁移的技术实现

摘要：本文将介绍网易云基础服务蜂巢实例迁移功能的实现，并探讨如何高效完成实例迁移任务。网易云基础服务蜂巢提供了负载监控阈值选项，在业务负载超过该阈值时，会暂停迁移操作，直到负载重新低于阈值。欢迎访问网易云社区，了解更多网易技术产品运营经验。我们把数据库里部分或全部 Schema和数据迁移到另一个实例的行为称为实例迁移，将导出数据的实例称为源实例，导入数据的实例称为目标实例。根据迁移数...

Olivia 2019-06-28 11:01 评论0 收藏0