在5.6版本服务器做备份
/usr/local/mysql/bin/mysqldump -S /tmp/mysql3306.sock -A -p --set-gtid-purged=OFF > backup_20160204.sql
在5.7版本服务器操作
mkdir -p /neworiental/data/mysql/mysql3307/{data,logs,relaylog,sock,tmp}
chown -R mysql:mysql /neworiental/data
初始化数据库
/usr/local/mysql/bin/mysqld --defaults-file=/neworiental/data/mysql/mysql3307/my3307.cnf --initialize-insecure
恢复数据库(全量数据)
/usr/local/mysql/bin/mysql -S /neworiental/data/mysql/mysql3307/sock/mysql3307.sock -p < backup_20160204.sql
查看所有用户没有密码!!!
select user,host,authentication_string,plugin from mysql.user;
修改密码
UPDATE mysql.user SET authentication_string=PASSWORD('password') WHERE User='root' and host='localhost';
FLUSH PRIVILEGES;
验证登入,登出,正常
重启之后,还是正常
只有日志有报错
2016-02-04T08:26:56.700344Z 0 [ERROR] Incorrect definition of table mysql.db: expected column 'User' at position 2 to have type char(32), found type char(16).
2016-02-04T08:26:56.700391Z 0 [ERROR] mysql.user has no `Event_priv` column at position 28
2016-02-04T08:26:56.700574Z 0 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.
进行数据库升级
/usr/local/mysql/bin/mysqld_safe --defaults-file=/neworiental/data/mysql/mysql3307/my3307.cnf --skip-grant-tables &
/usr/local/mysql/bin/mysql_upgrade --defaults-file=/neworiental/data/mysql/mysql3307/my3307.cnf -p --force
重启之后,日志正常了
问题语句:(5.7.10)
GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
系统表字段已乱
日志中记录 noboby can currently load!!!
无法登陆
解决办法:
UPDATE mysql.user SET authentication_string=PASSWORD('password') WHERE User='root' and host='localhost';
UPDATE mysql.user SET plugin='mysql_native_password' WHERE User='root' and host='localhost';
FLUSH PRIVILEGES;
1.已验证,此语句在5.6.28版本运行正常。(因为是源库,因为是一个版本,因为没有发生迁移)
2.无论是覆盖,还是新建,都不行 (版本问题,后面会说)
之前还残留两个5.7问题
1.恢复之后的数据库,mysql.user所有用户没有密码!!! (见结论)
2.使用全库恢复,会把'mysql.sys'@'localhost'覆盖,不知是否有影响?->必须有影响! (见结论)
结论&解决办法已验证
因为数据库版本问题,全库恢复之后,系统表会被覆盖,(变相的给降级了!!!)所以夸版本的情况,全库dump,恢复之后,必须做版本update,然后重启数据库!!!
转载于:https://blog.51cto.com/bigbear1206/1741130