Some minor issues with upgrading MySQL version 5.7

Backup on version 5.6 server

/usr/local/mysql/bin/mysqldump-s/tmp/mysql3306.sock-a-P--set-gtid-purged=off > Backup_20160204.sql

Server operation in version 5.7

Mkdir-p/neworiental/data/mysql/mysql3307/{data,logs,relaylog,sock,tmp}

Chown-r Mysql:mysql/neworiental/data

Initializing the database
/usr/local/mysql/bin/mysqld--defaults-file=/neworiental/data/mysql/mysql3307/my3307.cnf--initialize-insecure

Recover database (full data)
/usr/local/mysql/bin/mysql-s/neworiental/data/mysql/mysql3307/sock/mysql3307.sock-p < Backup_20160204.sql

View all users without password!!!

Select User,host,authentication_string,plugin from Mysql.user;

Change Password

UPDATE mysql.user SET authentication_string=password (' PASSWORD ') WHERE user= ' root ' and host= ' localhost ';
FLUSH privileges;
Verify log in, logout, normal
After rebooting, it's still normal.

Only logs have an error.
2016-02-04t08:26:56.700344z 0 [ERROR] incorrect definition of table mysql.db:expected column ' User ' at position 2 to has Type char (+), found type char (16).
2016-02-04t08:26:56.700391z 0 [ERROR] mysql.user have 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.

Perform a database upgrade
/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
After rebooting, the logs are fine.

Problem statement: (5.7.10)
GRANT all privileges on * * to ' root ' @ ' localhost ' identified by ' password ' with GRANT OPTION;

650) this.width=650; "title=" 1.jpg "style=" Float:none; "src=" http://s4.51cto.com/wyfs02/M02/7A/C1/ Wkiom1azhktrvszwaajw5jqbkky348.jpg "alt=" Wkiom1azhktrvszwaajw5jqbkky348.jpg "/>

system table field is scrambled

650) this.width=650; "title=" 2.jpg "style=" Float:none; "src=" http://s5.51cto.com/wyfs02/M00/7A/C0/ Wkiol1azhpeynchcaajclnc0nru008.jpg "alt=" Wkiol1azhpeynchcaajclnc0nru008.jpg "/>

Log records of Noboby can currently load!!!

650) this.width=650; "title=" 3.jpg "style=" Float:none; "src=" http://s1.51cto.com/wyfs02/M00/7A/C1/ Wkiom1azhkecumtwaac-cfpwq0k749.jpg "alt=" Wkiom1azhkecumtwaac-cfpwq0k749.jpg "/>

Unable to login

650) this.width=650; "title=" 4.jpg "style=" Float:none; "src=" http://s3.51cto.com/wyfs02/M01/7A/C1/ Wkiom1azhkfslg8jaadw6uxr48s673.jpg "alt=" Wkiom1azhkfslg8jaadw6uxr48s673.jpg "/>

Cannot stop

Here are the workarounds:

650) this.width=650; "title=" 5.jpg "style=" Float:none; "src=" http://s2.51cto.com/wyfs02/M02/7A/C1/ Wkiom1azhkqdncx3aapcmo0vbxe208.jpg "alt=" Wkiom1azhkqdncx3aapcmo0vbxe208.jpg "/>

Workaround:
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. Verified that this statement is functioning correctly in version 5.6.28. (because it is a source library because it is a version because no migration has occurred)
2. No matter the overwrite, or the new, neither (version problem, later said)

There were two remaining 5.7 questions.
1. After the recovery of the database, mysql.user all users no password!!! (See CONCLUSION)
2. Using the full library restore, will be ' mysql.sys ' @ ' localhost ' coverage, do not know whether there is an impact? Must have an Impact! (See CONCLUSION)

Conclusions & Solutions verified
Because the database version problem, the whole library after the recovery, the system table will be overwritten, (in disguise to downgrade!!!) So Kua version of the case, the whole repository dump, after recovery, must do version update, and then restart the database!!!

This article is from the "bigbear" blog, make sure to keep this source http://bigbear1206.blog.51cto.com/1671415/1741130

Some minor issues with upgrading MySQL version 5.7