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

In this issue, the editor will bring you about how to upgrade MySQL5.7 to 8.0. the article is rich in content and analyzes and narrates it from a professional point of view. I hope you can get something after reading this article.

1. Preparation and precautions before upgrade

First of all, we need to get a general idea of the differences between MySQL5.7 and 8.0. Refer to the official documents and other netizens' articles to summarize the following new features of MySQL8.0:

The default character set is changed from latin1 to utf8mb4.

All MyISAM system tables are replaced by InnoDB tables.

The JSON feature is enhanced.

Support invisible index, support histogram.

The default value of the sql_mode parameter changes.

The default password policy changes.

New role management.

Support window function, support Hash join.

According to the version changes and the official upgrade tutorial, list the following points for attention:

Note the character set settings. To avoid the inconsistency between the new and old object character sets, you can set the character set and verification rules to the old version of the character set and comparison rules in the configuration file.

Password authentication plug-in changes. To avoid connection problems, you can still use the mysql_native_password authentication plug-in of 5. 7.

Sql_mode support issues. Version 8.0 sql_mode does not support NO_AUTO_CREATE_USER, so avoid configuring sql_mode with NO_AUTO_CREATE_USER.

Whether you need to manually upgrade the system table. Before MySQL version 8.0.16, you need to manually execute mysql_upgrade to complete the upgrade step, and in and after MySQL version 8.0.16, it is mysqld to complete the upgrade step.

two。 Specific upgrade process

Take the Linux system as an example to show the specific upgrade process. My system is CentOS7.7, and the original version is MySQL5.7.23, which is directly upgraded to MySQL8.0.19 by In-Place.

2.1 download unzipped installation package

Download the corresponding version of the tar package on the official website, which can be downloaded via wget or uploaded locally.

Download address:

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

Select mysql-8.0.19-linux-glibc2.12-x86_64.tar.xz

Cdn.nlark.com/yuque/0/2020/png/119537/1589955685428-2a8b723e-505c-4952-8419-c2a2a67a3eb4.png ">

Perform the following steps to extract the tar package:

# upload the installation package to the original installation package directory my / usr/local/ cd / usr/local/# unzipped installation package xz-d mysql-8.0.19-linux-glibc2.12-x86_64.tar.xztar-xvf mysql-8.0.19-linux-glibc2.12-x86_64.tar# folder was renamed to belong to the mysql8 mv mysql-8.0.19-linux-glibc2.12-x86_64 mysql8# change folder Chown-R mysql.mysql / usr/local/mysql8/

2.2 change profile my.cnf

Because the parameters of version 5.7 are different from those of version 8.0, we need to change some configuration parameters in order to upgrade smoothly. Mainly pay attention to sql_mode, basedir, password authentication plug-in and character set settings, other parameters are best in accordance with the original 5.7, do not need to be adjusted. The changed configuration file is shown below:

# the parameters of the last few for8.0 should pay special attention to [mysqld] user = mysql datadir = / data/mysql/data port = 3306 socket = / data/mysql/tmp/mysql.sockpid-file = / data/mysql/tmp/mysqld.pidtmpdir = / data/mysql/tmp skip_name_resolve = 1max_connections = 2000group_concat_max_len = 1024000lower_case_table_names = 1log_timestamps=SYSTEMmax_allowed_packet = 32Mbinlog_cache_size = 4Msort_buffer _ size = 2Mread_buffer_size = 4Mjoin_buffer_size = 4Mtmp_table_size = 96Mmax_heap_table_size = 96Mmax_length_for_sort_data = 8096default_time_zone ='+ 8:00'#logsserver-id = 1003306log-error = / data/mysql/logs/error.logslow_query_log = 1slow_query_log_file = / data/mysql/logs/slow.loglong_query_time = 3log-bin = / data/mysql/logs/binlogbinlog_format = rowlog_bin_trust_function_ Creators = 1gtid_mode = ONenforce_gtid_consistency = ON#for8.0sql_mode = STRICT_TRANS_TABLES NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTIONcharacter-set-server = utf8collation_server = utf8_general_cibasedir = / usr/local/mysql8skip_ssldefault_authentication_plugin=mysql_native_password

2.3 execute the upgrade procedure

After all the pre-work is ready, you can start the formal upgrade, but it is recommended that you back up the full library before upgrading. When everything is ready, follow the instructions below for a formal upgrade.

# enter the original 5.7 mysql command line and correctly close the database mysql > select version (); +-+ | version () | +-+ | 5.7.23-log | +-+ 1 row in set (0.00 sec) 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 Change to 0mysql > set global innodb_fast_shutdown=0 Query OK, 0 rows affected (0.00 sec) mysql > shutdown Query OK 0 rows affected (0.00 sec) mysql > exitBye# exit to the terminal launch [root@centos ~] # / usr/local/mysql8/bin/mysqld_safe-- defaults-file=/etc/my.cnf-- user=mysql & [1] 23333 [root@centos ~] # 2020-05-20T07:07:02.337626Z mysqld_safe Logging to'/ data/mysql/logs/error.log'.2020-05-20T07:07:02.366244Z mysqld directly with the mysql8.0.19 client _ safe Starting mysqld daemon with databases from / data/mysql/data# can observe the error log to see if an error is reported, and then log in again to test [root@centos ~] # mysql-uroot-p123456 mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 17Server version: 8.0.19 MySQL Community Server-GPLCopyright (c) 2000, 2018, 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.00 sec)

2.4 Environment variable modification

Since basedir has changed from / usr/local/mysql to / usr/local/mysql8, it is recommended to modify the relevant environment variables. You can follow these steps to verify:

# modify mysql service startup item configuration vi / etc/init.d/mysql# modify basedir directory basedir=/usr/local/mysql8# modify PATH variable vi / etc/profile # change / usr/local/mysql/bin in PATH to / usr/local/mysql8/bin # effective verification [root@centos ~] # source / etc/profile [root@centos ~] # which mysql/usr/local/mysql8/bin/mysql [root@centos ~] # mysql-Vmysql Ver 8 .0.19 for linux-glibc2.12 on x861664 (MySQL Community Server-GPL)

So far, our database has been successfully upgraded from 5.7 to 8.0! Compared with the MySQL installation process and the upgrade process, it is found that the two are very similar, in fact, the upgrade process is not complex, the complexity is the verification and compatibility testing after the upgrade, especially for complex business libraries, MySQL version upgrade should be careful. In the real environment, it is recommended to upgrade the slave database first, verify it correctly, and then upgrade the master database step by step.

The above is the editor for you to share how to upgrade MySQL5.7 to 8.0, if you happen to have similar doubts, you might as well refer to the above analysis to understand. If you want to know more about it, you are welcome to follow the industry information channel.

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.