Shulou(Shulou.com)05/31 Report--

This article mainly explains "how to upgrade MySQL 5.7to 8.0". The content in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn" how to upgrade from MySQL 5.7to 8.0".

Why upgrade to MySQL 8.0

Based on security considerations

Based on performance and stability considerations:

Mgr replication, parallel replication writeset and other functions to improve performance

New features:

Hash join, window function, DDL instant, json support

There are too many versions in the original environment, unified version

Version 8.0 has basically reached a stable period and can be put into production environment in large quantities.

You need to know before upgrading

Database dictionary upgrade

Schema,mysql,information_schema,performance_schema,sys

For example: password testing mysql_native_password → caching_sha2_password

two。 Is the keyword compatible?

Https://dev.mysql.com/doc/refman/8.0/en/keywords.html

Keyword added in query

3. Is SQL compatible?

Incompatibility in Group by processing, triggers, stored procedures

5.6 you can run select id,count (*) from group by name

5.7 sql_mode 8.0 is not allowed for control

4. Can the data file storage format be upgraded directly?

Perconal and mysql storage engines have always been fully compatible

5. Whether the compatibility of existing applications is satisfied

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 functions of 8.0.

Added in add function

Features Deprecated deprecation function

Features Removed removal function

Upgrade preparation

Now that you know the features of 8.0, you need to verify and prepare for the upgrade in advance.

Test library upgrade, application verification

Database upgrade, unknown problem occurs

My.cnf configuration information adjustment

Incompatible methods of operation, affecting replication

A smooth filter, such as upgrading a slave library first, to all slave libraries

Minimum downtime, same production data recovery to the environment, simulation upgrade, evaluation time

How to validate data: number of rows, number of tables, etc.

Consider the rollback scheme

Database backup

Pre-upgrade check

Mysql8.0 still offers a lot of convenience, unlike the previous 5.6 upgrade 5.7. It can now be confirmed through mysql shell.

The following two ways

# mysqlsh root:123456@192.168.244.130:3410-e 'util.checkForServerUpgrade ({"targetVersion": "8.0.19", "configPath": "/ etc/my3410.cnf"})'; MySQL JS > util.checkForServerUpgrade ('root@192.168.244.130:3410', {"password": "123456", "targetVersion": "8.0.11", "configPath": "/ etc/my3410.cnf"})

Make changes as prompted

Although shell is doing well, it still has some flaws.

For example, there are no hints for the following:

1. Basedir

2. Sql_mode

3. Semi-synchronous configuration

4. Password policy: default_authentication_plugin = mysql_native_password

Start the upgrade

Download the corresponding tar package on the official website

Https://downloads.mysql.com/archives/community/

The following is a stand-alone upgrade. Under the highly available architecture, you need to upgrade the slave library first and upgrade the master library step by step.

When you execute the mysql_upgrade command, you will be prompted as follows:

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

The mysql_upgrade client is now deprecated in MySQL 8. The actions performed by the upgrade client are now done by the server.

To upgrade, start the new MySQL binaries using the older data directory. Automatically repair the user table. No reboot is required after the upgrade.

Therefore, the corresponding SQL statements must be prepared by simulation in the test environment.

The correct actions are as follows:

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

Turn off innodb parameter confirmation

Mysql > show variables like 'innodb_fast_shutdown' +-- +-+ | Variable_name | Value | +-- +-+ | innodb_fast_shutdown | 1 | +-- +-+ 1 row in set (0.00 sec)

Make sure the data is brushed to the hard drive and changed to 0

Mysql > set global innodb_fast_shutdown=0;Query OK, 0 rows affected (0.01 sec) mysql > shutdown;Query OK, 0 rows affected (0.00 sec) * for backup.

2) start directly with mysql8.0.19 client

Start the mysql service

[root@ss30 bin] # / opt/mysql8.0.19/bin/mysqld_safe-- defaults-file=/etc/my3400.cnf-- user=mysql & [1] 15400 [root@ss30 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 # # Open another window to view error logs [root@ss30 ~] # tail-f / opt/data3400/logs/mysql_error.log

Log in to the server to confirm

[root@ss30] # 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.01sec)

No myisam engine

Mysql > SELECT table_schema,table_name,engine FROM information_schema.tables where engineered innovative DB'

All that is left is to verify and business to confirm that the application is normal.

Thank you for your reading, the above is the content of "how to upgrade MySQL 5.7to 8.0". After the study of this article, I believe you have a deeper understanding of how to upgrade MySQL 5.7to 8.0. the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.