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如何优雅的备份账号相关信息的详细内容，更多关于mysql 备份账号相关信息的资料请关注其它相关文章！