MySQL 8.X 仅迁移非系统数据库账号和权限信息

 

一、需求说明

需要将A数据库里面非系统的数据库账号和权限迁移到另外一台B数据库上，同时不能影响B数据库现有的用户和权限。

二、处理思路

我们不能盲目的将A数据库整个mysql数据库导出到B数据库进行覆盖。

我们需要做下面２个步骤：
将A数据库上非系统数据库账号的信息，导出成insert语句，在B数据库上执行
将A数据库上非系统数据库账号相关的权限，导出成grant语句，在B数据库上执行

二、具体操作

１.生成用户创建语句

导出mysql.user表，排除系统内置账号，添加条件，不需要删除drop和create创建表的语句，

我们只需要插入、

# 导出mysql user表  (排除系统账号)mysqldump -uroot -p --where=" user NOT IN ('root','mysql.infoschema','mysql.session','mysql.sys') " mysql user --no-create-info > /file/mysql_user_create.sql

 这里生成的mysql_user_create.sql文件，就是账号创建的SQL语句。

２.生成用户权限授权的语句

ａ.先获取mysql库中用户的列表信息 (排除系统账号)

# 获取mysql库中用户账号名称列表　输出到txt文件 (排除系统账号)mysql -B -N -uroot -p -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user NOT IN ('root','mysql.infoschema','mysql.session','mysql.sys')" mysql > /file/mysql_users.txt

ｂ.获取用户权限信息

根据每行的账号名称，查询账号对应的授权信息，输出到文件

# 根据每一行的账号名称，查询账号对应的授权信息while read line; do mysql -B -N -uroot -p -e "SHOW GRANTS FOR $line"; done < /file/mysql_users.txt > /file/mysql_user_prv.sql

ｃ.每行结尾添加逗号作为结束符

# 每行结尾添加逗号作为结束符sed -i 's/$/;/' /tmp/mysql_all_users.sql

d.在最后一行增加 "flush privileges;" 来刷新权限

# 在最后一行增加 "flush privileges;" 来刷新权限echo "flush privileges;" >> /file/mysql_user_prv.sql

 这里生成的mysql_user_prv.sql文件，就是账号授权的SQL语句。

3.在B数据库上导入用户及权限

将上面处理过的sql脚本文件mysql_user_create.sql、mysql_user_prv.sql在目标mysql实例上执行

# 导入用户mysql -u root -p < mysql_user_create.sql# 导入用户权限mysql -u root -p < mysql_user_prv.sql

三、完整脚本

export_mysql_user_prv.sh

#!/bin/bash
# Export Mysql users and prv# mysql username
export username=root
# mysql password
export password=123456Abmkdir -p /file/# 导出mysql user表  (排除系统账号)mysqldump -u${username} -p${password} --where=" user NOT IN ('root','mysql.infoschema','mysql.session','mysql.sys') " mysql user --no-create-info > /file/mysql_user_create.sql# 获取mysql库中用户的列表信息 (排除系统账号)getuserlist.shmysql -B -N -u${username} -p${password} -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user NOT IN ('root','mysql.infoschema','mysql.session','mysql.sys')" mysql > /file/mysql_users.txt# 获取用户权限信息while read line; do mysql -B -N -u${username} -p${password} -e "SHOW GRANTS FOR $line"; done < /file/mysql_users.txt > /file/mysql_user_prv.sql# 每行结尾添加逗号作为结束符sed -i 's/$/;/' /file/mysql_user_prv.sql# 在最后一行增加 "flush privileges;" 来刷新权限echo "flush privileges;" >> /file/mysql_user_prv.sql