利用脚本迁移mysql.user库里的用户及其权限

5.13 迁移用户名密码权限

5.13.1 生成创建的用户和加密的密码

mysql -uroot -p -e "SELECT CONCAT('grant USAGE on ',user,'.* to \'', user,'\'@\'', host, '\' IDENTIFIED WITH \'mysql_native_password\' AS \'',authentication_string,'\';') FROM mysql.user WHERE user != 'root' AND user != 'mysql.sys' AND user != 'mysql.session' and user !='';" > /tmp/passwd.sql

8.0 用如下脚本

mysql -uroot -p -e "SELECT CONCAT('create user ', user,'\'@\'', host, '\' IDENTIFIED WITH \'mysql_native_password\' AS \'',authentication_string,'\';') FROM mysql.user WHERE user != 'root' AND user != 'mysql.sys' AND user != 'mysql.session' and user !='';" > /tmp/passwd.sql

5.13.2 生成权限迁移的用户

mysql -B -N -uroot -p -e "SELECT CONCAT('\'', user,'\'@\'', host, '\'') FROM user WHERE user != 'mysql.sys' AND user != 'root' AND user != '' AND user != 'mysql.session' " mysql > /tmp/mysql_all_users.txt

cd /tmp/

5.13.3 循环产生授权命令

while read line; do mysql -B -N -uroot -p'xxxxxx' -e "SHOW GRANTS FOR $line"; done mysql_all_users_sql.sql

5.13.4 文件中增加分号

sed -i 's/$/;/' mysql_all_users_sql.sql