MySQL5.6到8.0的账号迁移

SELECT CONCAT('create user "',USER,'"@"',HOST,'";' )  
FROM mysql.user 
where user 
not in ('root','mysql.infoschema','mysql.session','mysql.sys') 
;

# 只提取出grant sql，过滤掉创建账号的语句
pt-show-grants -uroot -p'xxxxx'  | egrep -v 'IDENTIFIED BY PASSWORD' | egrep -v '-- Grants' > grants.sql

select concat('update mysql.user set authentication_string="', password,'" ','where user=',"'",user,"' and host=","'",host,"'",';' )  
from mysql.user 
where user 
not in ('root','mysql.infoschema','mysql.session','mysql.sys') 
;

将上面几步获取到的结果，在8.x的MySQL上执行，注意最后要 flush privileges; 下，因为我们是直接改用户表的！