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
