MySQL version upgrade 5.6 to 5.7

two ways to upgrade

• in-place Upgrade: involves shutting down the old MySQL version, replacing the old MySQL binaries or packages with The new ones, restarting MySQL on the existing data directory, and running mysql_upgrade.

• Logical Upgrade: involves exporting existing data from the old MySQL version using mysqldump, installing The new MySQL version, loading the dump file into the new MySQL version, and running mysql_upgrade.

Master-Slave Upgrade:

1. Master and slave want to replace the binary installation package with the latest version
2. Stop from, upgrade via Mysql_upgrade, add parameter--skip-slaves-start to start
3. Add parameter--skip-networking Restart the master, deny TCP/IP connections from the app, close Binlog, execute mysql_upgrade, and restart

• Note: When you close the service with parameter--innodb_fast_shutdown=0 (slow shutdown), the dirty pages for all committed transactions are flushed to the data file, which defaults to 1 (Fast Shutdown Reference:/HTTP/ Dev.mysql.com/doc/refman/5.7/en/glossary.html#glos_fast_shutdown)

MySQL5.6 version to version 5.7 updates include some incompatible features, and we need to know these incompatible features and update them manually before upgrading to 5.7, where instructions that involve the repair table and USE_FRM options must be completed before the updated version. Configuration Item Updates

• --early-plugin-load

MySQL5.7.11, the default value for this parameter is Keyring_file (a plug-in for a binary file), and InnoDB tablespace requires this plug-in to encrypt before initializing InnoDB. However, MySQL5.7.12 and later this parameter is empty by default, so after 5.7.11 upgrade to 5.7.12, if the InnoDB table space has been encrypted using this plugin in a previous version, you need to specify the parameters when the service is turned on--early-plugin-load

• 系统表

MySQL5.6中INFORMATION_SCHEMA 中存在系统变量和状态变量的表，show variables 和show status也是基于此库中的表，在5.7.6时被Performance Schema也存在这四张表，show 语句开始基于Performance Schema中的表，如果show_compatibility_56参数开启，则兼容5.6

The following test libraries are directly physically restored from the MySQL5.6 release to the MySQL5.7 environment.

Mysql> Selectversion ();+------------+|Version ()|+------------+| 5.7.Ten-Log |+------------+1Rowinch Set(0.00sec)

Mysql> Show variables like '%56% ';
ERROR 1146 (42S02): Table ' performance_schema.session_variables ' doesn ' t exist

mysql> use Performance_schema;
Reading table information for completion of table and column names
Can turn off this feature to get a quicker startup with-a

Database changed
Mysql> Show tables like '%variable% ';
Empty Set (0.00 sec)

mysql> set global show_compatibility_56=on;
Query OK, 0 rows Affected (0.00 sec)

Mysql> Show variables like '%56% ';
+-----------------------------+-------+
| variable_name | Value |
+-----------------------------+-------+
| Sha256_password_proxy_users | OFF |
| show_compatibility_56 | On |
+-----------------------------+-------+
2 rows in Set (0.00 sec)

• Use mysqld--initialize (or mysqld--initialize-insecure). Initialize an instance

SQL Mode

ONLY_FULL_GROUP_BY, STRICT_TRANS_TABLES which NO_ENGINE_SUBSTITUTION opens by default

The Any_value function can be used if the following SQL is in the name non-clustered column under only full group BY, if it does not care about the value of the address returned, so that the address field does not have to satisfy the full group by appearing in the group by In

SELECT MAX  from GROUP  by name;

changes to system tables

Mysql.user password field is removed in 5.7.6, authentication information is recorded in authentication_string, run In-place upgrade migrate password column value to Authentication_ String

If you are passing logical upgrade, you need to be aware that:

• You must include the --add-drop-table option

• You must not include the --flush-privileges option

Server-side changes

MySQL5.7.5 Start Mysql_old_password plugin removed

Secure-auth system variable only supports value 1

--skip-secure-auth option is deprecated

Old_password the value of the system variable 1 (hash value with a hash of 41 bits) is no longer allowed

The Old_password () function has been removed

Field type year (2) was changed to year (4)

MySQL5.7.2 Start mysql.user The plugin field in the system table is not allowed to be empty, running Mysql_upgrade does the following

UPDATE mysql.user SET plugin = ' Mysql_native_password ' WHERE plugin = ' and (password = ' OR LENGTH (password) = 41); FLUSH privileges;

Note the changes to Sql_mode, such as:

Mysql> SETSql_mode= "'; Query OK,0Rows Affected (0.00sec) MySQL> CREATE TABLET (d DATEDEFAULT 0);SETSql_mode= 'No_zero_date,strict_all_tables';INSERT  intoT (d)VALUES(DEFAULT); Query OK,0Rows Affected (0.52sec) MySQL> SETSql_mode= 'No_zero_date,strict_all_tables'; Query OK,0Rows affected,1Warning (0.00sec) MySQL> INSERT  intoT (d)VALUES(DEFAULT); ERROR1292(22007): Incorrect Date value:'0000-00-00'  for column 'D'At row1

SQL changes

MySQL5.7.5 before Get_lock () releases the previously acquired lock at the second time, enabling multiple locks to be obtained at the same time after this version, such as:

Mysql> Selectversion ();+------------+|Version ()|+------------+| 5.6. --Log |+------------+1Rowinch Set(0.00sec) MySQL> SELECTGet_lock ('Lock1',Ten);+----------------------+|Get_lock ('Lock1',Ten)|+----------------------+|                    1 |+----------------------+1Rowinch Set(0.00sec) MySQL> SELECTGet_lock ('Lock2',Ten);+----------------------+|Get_lock ('Lock2',Ten)|+----------------------+|                    1 |+----------------------+1Rowinch Set(0.00sec) MySQL> SELECTRelease_lock ('Lock2');+-----------------------+|Release_lock ('Lock2')|+-----------------------+|                     1 |+-----------------------+1Rowinch Set(0.00sec) MySQL> SELECTRelease_lock ('Lock1');+-----------------------+|Release_lock ('Lock1')|+-----------------------+|                  NULL |+-----------------------+1Rowinch Set(0.00Sec

Returns null indicating that the lock has been released

Reference Documents:

Http://dev.mysql.com/doc/refman/5.7/en/upgrading-from-previous-series.html

MySQL version upgrade 5.6 to 5.7