MySQL如何优雅的备份账号相关信息
程序员文章站
2022-06-18 13:46:37
前言:最近遇到实例迁移的问题,数据迁完后还需要将数据库用户及权限迁移过去。进行逻辑备份时,我一般习惯将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 备份账号相关信息的资料请关注其它相关文章!