MySQL 5.7 upgrade 8.0 process

Why upgrade to MySQL8.0

1) Based on security considerations
2) Based on performance and stability considerations:
mgr copy, parallel copy writeset and other functions, performance improvement
3) New functions:
Hash join, window function, DDL instant, joson support
4) Too many versions in the original environment , Unified version. The 8.0 version has basically reached a stable period. Can be put into production environment in large quantities

Need to know before upgrading

1) Database dictionary upgrade
schema, mysql, information_schema, performance_schema, sys
such as: password test mysql_native_password → caching_sha2_password
2) Is the keyword compatible with
https://dev.mysql.com/doc/refman/8.0/en/keywords.html
key The word added in query
3) Is SQL compatible with
Group by processing incompatibility, triggers, stored procedures
5.6 can run select id, count (*) from group by name;
5.7, 8.0 are allowed sql_mode control
4) data Can the file storage format be directly upgraded?
Perconal and mysql storage engines have always been fully compatible
5) Is the compatibility of existing applications satisfied with
custom functions, some non-standard SQL statements, etc.
6) Password policy

What Is New in MySQL 8.0

As a DBA, you need to have a basic understanding of some features of 8.0 https://dev.mysql.com/doc/refman/8.0/en/mysql-nutshell.html
Added in; Features Deprecated; Features Removed

image.png
image.png
image.png
Upgrade preparation

Know the features of 8.0, you need to verify and prepare in advance for the upgrade

1) Test library upgrade, application verification
2) Database upgrade, the last known problem occurs
3) My.cnf configuration information adjustment
4) Incompatible operation method, affecting replication
5) A smooth filter, such as upgrading a slave library first, To all slave libraries
6) Minimal downtime, the same production data is restored to the environment, simulation upgrade, evaluation time
7) How to verify data: number of rows, number of tables, etc.
8) Consider rollback plan
9) Database backup

Checking
Mysql8.0 before the upgrade still provides a lot of convenience, unlike the previous 5.6 upgrade to 5.7. It can now be confirmed through the mysql shell.
https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-upgrade.html
##The following 2 ways
#mysqlsh root:[email protected]:3410 -e 'util.checkForServerUpgrade({"targetVersion":"8.0.19","configPath":"/etc/my3410.cnf"})'; MySQL  JS > util.checkForServerUpgrade('[email protected]:3410', {"password":"123456", "targetVersion":"8.0.11", "configPath":"/etc/my3410.cnf"})
image.png
image.png
image.png

Follow the prompts to make changes

image.png

Although the shell does a good job, there are still some flaws.
For example, there will be no prompts for the following content:
1.basedir,
2.sql_mode,
3. semi-synchronous configuration,
4. password policy: default_authentication_plugin = mysql_native_password

Start to upgrade

Download the corresponding tar package from the official website
https://downloads.mysql.com/archives/community/
The following is a stand-alone upgrade. Under a high-availability architecture, you need to upgrade the slave library first, and then gradually upgrade the main library.

Execute the mysql_upgrade command, you will be prompted as follows:

#/mysql8.0.19/bin/mysql_upgrade -uroot -p123456
image.png

The mysql_upgrade client is now deprecated in MySQL 8. The operations performed by the upgrade client are now done by the server.
To upgrade, use the older data directory to start the new MySQL binaries. Automatically repair the user table. There is no need to restart after the upgrade.
Therefore, the corresponding SQL statement must be simulated and prepared in the test environment

***The correct operation is as follows:

1) Log in to the server to shut down normally: innodb_fast_shutdown is 1 by default, which is often considered to be a safe shutdown

##关闭innodb参数确认mysql> show variables like 'innodb_fast_shutdown';+----------------------+-------+| Variable_name        | Value |+----------------------+-------+| innodb_fast_shutdown | 1     |+----------------------+-------+1 row in set (0.00 sec)##确保数据都刷到硬盘上,更改成0mysql> set global innodb_fast_shutdown=0;Query OK, 0 rows affected (0.01 sec)mysql> shutdown;Query OK, 0 rows affected (0.00 sec)

* Make a backup.

2) Start directly with the mysql8.0.19 client.
Start the mysql service

[[email protected] bin]# /opt/mysql8.0.19/bin/mysqld_safe --defaults-file=/etc/my3400.cnf --user=mysql & [1] 15400[[email protected] bin]# 2020-04-25T13:07:16.591560Z mysqld_safe Logging to '/opt/data3400/logs/error.log'.2020-04-25T13:07:16.636879Z mysqld_safe Starting mysqld daemon with databases from /opt/data3400/mysql##打开另一个窗口查看error日志[[email protected] ~]# tail -f /opt/data3400/logs/mysql_error.log ##登录服务器确认[[email protected] ~]# mysql -uroot -p -S /opt/data3400/mysql/mysql.sockEnter password: Welcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 10Server version: 8.0.19 MySQL Community Server - GPLCopyright (c) 2000, 2020, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> select version();+-----------+| version() |+-----------+| 8.0.19    |+-----------+1 row in set (0.01 sec) ##无myisam引擎mysql> SELECT table_schema,table_name,engine FROM information_schema.tables where engine!='InnoDB';

The rest is verification and business confirmation whether the application is normal.

to sum up

The whole process from the start to the end of the upgrade preparation involves a lot of meticulous work. Such as version confirmation, function confirmation, testing, preparation, backup, verification, high-availability switching and so on. It takes a lot of energy to prepare in the early stage, so that it can be done in one step.
After upgrading, the next step is to embark on the journey of using 8.0.