关注公众号，将获取更多运维干货

背景

如果是整个实例迁移的，可以直接备份恢复mysql库，对于一些场景没迁移mysql库，如阿里云RDS迁移时使用了DTS工具，源实例数据库账号也比较多，不适合一个个重建。

脚本

创建用户和迁移密码



SELECT CONCAT('grant USAGE on ',user,'.* to \'', user,'\'@\'', host, '\' IDENTIFIED WITH \'mysql_native_password\' AS \'',authentication_string,'\' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;') FROM mysql.user WHERE user != 'cc_dba' AND user != 'root' AND user != '' and user !='testAdmin'

最终样式



grant USAGE on test.* to 'test'@'%' IDENTIFIED WITH 'mysql_native_password' AS '*BBBB52A57EA9E648FFDEFBA69458608E20984721';

这里可以使用“ create user ” 替代 “grant USAGE on test.* to ” 5.7 grant 命令默认可以新建用户

迁移用户权限

#拿到所有用户
shell>/usr/local/mysql/bin/mysql --socket=/usr/local/mysql/mysql.sock -B -N -uroot -pca  -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'cc_dba' AND user != 'root' AND user != '' and user !='testAdmin'" mysql > /admin/mysql_all_users.txt


#拿到所有权限
shell>cd /home/admin
shell> while read line; do /usr/local/mysql/bin/mysql --socket=/usr/local/mysql/mysql.sock -B -N -uroot -pca -e "SHOW GRANTS FOR $line"; done < mysql_all_users.txt > mysql_all_users_sql.sql   


#行尾加分号
shell>sed -i 's/$/;/' mysql_all_users_sql.sql  


#刷新权限
shell>echo "flush privileges;" >> mysql_all_users_sql.sql


#执行SQL


mysql> source /home/admin/mysql_all_users_sql.sql;


文章参考

https://zhuanlan.zhihu.com/p/349231526

多文章请扫一扫

 扫描下面二维码关注公众号，获取更多学习资源

Mysql 用户权限与密码迁移_ide