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

2023-07-17 20:08:03 180

前言：

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

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

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

#只导出mysql库中的user，db，tables_priv表数据
#如果你有针队column的赋权可以再导出columns_priv表数据
#若数据库开启了GTID导出时最好加上--set-gtid-purged=OFF
mysqldump-uroot-prootmysqluserdbtables_priv-t--skip-extended-insert>/tmp/user_info.sql

#导出的具体信息
--
--Dumpingdatafortable`user`
--

LOCKTABLES`user`WRITE;
/*!40000ALTERTABLE`user`DISABLEKEYS*/;
INSERTINTO`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-0603:03:15',NULL,'N');
INSERTINTO`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-0602:57:40',NULL,'Y');
INSERTINTO`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-0602:57:40',NULL,'Y');
INSERTINTO`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-1906:24:54',NULL,'N');
INSERTINTO`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-1906:27:45',NULL,'N');
INSERTINTO`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-1906:29:38',NULL,'N');
/*!40000ALTERTABLE`user`ENABLEKEYS*/;
UNLOCKTABLES;

--
--Dumpingdatafortable`db`
--

LOCKTABLES`db`WRITE;
/*!40000ALTERTABLE`db`DISABLEKEYS*/;
INSERTINTO`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');
INSERTINTO`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');
INSERTINTO`db`VALUES('%','test_db','test','Y','Y','Y','Y','Y','Y','N','N','N','Y','N','N','Y','Y','N','N','Y','N','N');
/*!40000ALTERTABLE`db`ENABLEKEYS*/;
UNLOCKTABLES;

--
--Dumpingdatafortable`tables_priv`
--

LOCKTABLES`tables_priv`WRITE;
/*!40000ALTERTABLE`tables_priv`DISABLEKEYS*/;
INSERTINTO`tables_priv`VALUES('localhost','mysql','mysql.session','user','boot@connectinghost','0000-00-0000:00:00','Select','');
INSERTINTO`tables_priv`VALUES('localhost','sys','mysql.sys','sys_config','root@localhost','2019-03-0602:57:40','Select','');
INSERTINTO`tables_priv`VALUES('%','test_db','test_user','t1','root@localhost','0000-00-0000:00:00','Select,Insert,Update,Delete','');
/*!40000ALTERTABLE`tables_priv`ENABLEKEYS*/;
UNLOCKTABLES;

#在新的实例插入所需数据就可以创建出相同的用户及权限了

2.自定义脚本导出

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

SELECT
	CONCAT(
		'createuser\'',
user,
'\'@\'',
Host,
'\''
'IDENTIFIEDBYPASSWORD\'',
authentication_string,
		'\';'
	)ASCreateUserQuery
FROM
	mysql.`user`
WHERE
	`User`NOTIN(
		'mysql.session',
		'mysql.sys'
	);
	
#结果在新实例执行后可以创建出相同密码的用户
mysql>SELECT
->CONCAT(
->'createuser\'',
->user,
->'\'@\'',
->Host,
->'\''
->'IDENTIFIEDBYPASSWORD\'',
->authentication_string,
->'\';'
->)ASCreateUserQuery
->FROM
->mysql.`user`
->WHERE
->`User`NOTIN(
->'mysql.session',
->'mysql.sys'
->);
+-------------------------------------------------------------------------------------------------+
|CreateUserQuery|
+-------------------------------------------------------------------------------------------------+
|createuser'root'@'%'IDENTIFIEDBYPASSWORD'*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B';|
|createuser'test'@'%'IDENTIFIEDBYPASSWORD'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29';|
|createuser'read'@'%'IDENTIFIEDBYPASSWORD'*2158DEFBE7B6FC24585930DF63794A2A44F22736';|
|createuser'test_user'@'%'IDENTIFIEDBYPASSWORD'*8A447777509932F0ED07ADB033562027D95A0F17';|
+-------------------------------------------------------------------------------------------------+
4rowsinset(0.00sec)

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

#导出权限脚本
#!/bin/bash
#Functionexportuserprivileges

pwd=root
expgrants()
{
mysql-B-u'root'-p${pwd}-N$@-e"SELECTCONCAT('SHOWGRANTSFOR''',user,'''@''',host,''';')ASqueryFROMmysql.user"|\
mysql-u'root'-p${pwd}$@|\
sed's/\(GRANT.*\)/\1;/;s/^\(Grantsfor.*\)/--\1/;/--/{x;p;x;}'
}

expgrants>/tmp/grants.sql
echo"flushprivileges;">>/tmp/grants.sql

#执行脚本后结果
--Grantsforread@%
GRANTSELECTON*.*TO'read'@'%';

--Grantsforroot@%
GRANTALLPRIVILEGESON*.*TO'root'@'%'WITHGRANTOPTION;

--Grantsfortest@%
GRANTUSAGEON*.*TO'test'@'%';
GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,EXECUTE,CREATEVIEW,SHOWVIEWON`test_db`.*TO'test'@'%';

--Grantsfortest_user@%
GRANTUSAGEON*.*TO'test_user'@'%';
GRANTSELECT,INSERT,UPDATE,DELETEON`test_db`.`t1`TO'test_user'@'%';

--Grantsformysql.session@localhost
GRANTSUPERON*.*TO'mysql.session'@'localhost';
GRANTSELECTON`performance_schema`.*TO'mysql.session'@'localhost';
GRANTSELECTON`mysql`.`user`TO'mysql.session'@'localhost';

--Grantsformysql.sys@localhost
GRANTUSAGEON*.*TO'mysql.sys'@'localhost';
GRANTTRIGGERON`sys`.*TO'mysql.sys'@'localhost';
GRANTSELECTON`sys`.`sys_config`TO'mysql.sys'@'localhost';

3.mysqlpump直接导出用户

mysqlpump是mysqldump的一个衍生，也是MySQL逻辑备份的工具。mysqlpump可用的选项更多，可以直接导出创建用户的语句及赋权的语句。下面我们来演示下：

#exclude-databases排除数据库--users指定导出用户exclude-users排除哪些用户
#还可以增加--add-drop-user参数生成dropuser语句
#若数据库开启了GTID导出时必须加上--set-gtid-purged=OFF
mysqlpump-uroot-proot--exclude-databases=%--users--exclude-users=mysql.session,mysql.sys>/tmp/user.sql

#导出的结果
--DumpcreatedbyMySQLpumputility,version:5.7.23,linux-glibc2.12(x86_64)
--Dumpstarttime:FriApr1915:03:022019
--Serverversion: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;
SETSQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET@@SESSION.SQL_LOG_BIN=0;
SET@OLD_TIME_ZONE=@@TIME_ZONE;
SETTIME_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;
SETNAMESutf8mb4;
CREATEUSER'read'@'%'IDENTIFIEDWITH'mysql_native_password'AS'*2158DEFBE7B6FC24585930DF63794A2A44F22736'REQUIRENONEPASSWORDEXPIREDEFAULTACCOUNTUNLOCK;
GRANTSELECTON*.*TO'read'@'%';
CREATEUSER'root'@'%'IDENTIFIEDWITH'mysql_native_password'AS'*81F5E21E35407D884A6CD4A731AEBFB6AF209E1B'REQUIRENONEPASSWORDEXPIREDEFAULTACCOUNTUNLOCK;
GRANTALLPRIVILEGESON*.*TO'root'@'%'WITHGRANTOPTION;
CREATEUSER'test'@'%'IDENTIFIEDWITH'mysql_native_password'AS'*94BDCEBE19083CE2A1F959FD02F964C7AF4CFC29'REQUIRENONEPASSWORDEXPIREDEFAULTACCOUNTUNLOCK;
GRANTUSAGEON*.*TO'test'@'%';
GRANTSELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,EXECUTE,CREATEVIEW,SHOWVIEWON`test_db`.*TO'test'@'%';
CREATEUSER'test_user'@'%'IDENTIFIEDWITH'mysql_native_password'AS'*8A447777509932F0ED07ADB033562027D95A0F17'REQUIRENONEPASSWORDEXPIREDEFAULTACCOUNTUNLOCK;
GRANTUSAGEON*.*TO'test_user'@'%';
GRANTSELECT,INSERT,UPDATE,DELETEON`test_db`.`t1`TO'test_user'@'%';
SETTIME_ZONE=@OLD_TIME_ZONE;
SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT;
SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS;
SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION;
SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;
SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;
SETSQL_MODE=@OLD_SQL_MODE;
--Dumpendtime:FriApr1915:03:022019

#可以看出导出结果只包含创建用户及赋权的语句十分好用
#mysqlpump详细用法可参考：
https://dev.mysql.com/doc/refman/5.7/en/mysqlpump.html

总结：

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

以上就是MySQL如何优雅的备份账号相关信息的详细内容，更多关于MySQL备份账号相关信息的资料请关注毛票票其它相关文章！

声明：本文内容来源于网络，版权归原作者所有，内容由互联网用户自发贡献自行上传，本网站不拥有所有权，未作人工编辑处理，也不承担相关法律责任。如果您发现有涉嫌版权的内容，欢迎发送邮件至：czq8825#qq.com（发邮件时，请将#更换为@）进行举报，并提供相关证据，一经查实，本站将立刻删除涉嫌侵权内容。

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

热门推荐

随机推荐