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

Today, I will talk to you about how to upgrade MySQL 5.6.47 to 5.7.20. Many people may not know much about it. In order to make you understand better, the editor has summarized the following content for you. I hope you can get something from this article.

There are two ways to upgrade MySQL, one is called In-Place Upgrade, the other is called Logical Upgrade (logical upgrade).

This article demonstrates the use of In-Place Upgrade to upgrade MySQL.

1. Introduction to upgrade method

There are two ways to upgrade MySQL, one is called In-Place Upgrade, the other is called Logical Upgrade (logical upgrade).

Logical Upgrade: use mysqldump to directly export the SQL file, and then import it into the new library, which is suitable for the upgrade scheme across large versions, which is relatively safe and can defragment the table. However, if there is a library with a large amount of data that needs to be exported by mysqldump, the time consumption will be very large, and the upgrade efficiency will be affected.

In-Place Upgrade: it works simply and quickly by directly replacing the installation directory and my.cnf configuration file of the original version of MySQL and using the mysql_upgrade script to upgrade the system table.

This article demonstrates the use of In-Place Upgrade to upgrade MySQL.

two。 Review the version before upgrading step 2.1

[root@source bin] # / usr/local/mysql/bin/mysql-V

Mysql Ver 14.14 Distrib 5.6.47, for linux-glibc2.12 (x86 / 64) using EditLine wrapper

2.2 set the parameter innodb_fast_shutdown to 0

The innodb_fast_shutdown parameter needs to be set to 0

Set global innodb_fast_shutdown=0

Note: innodb_fast_shutdown has three values of 0, 1 and 2.

A parameter value of 0 means that MySQL is turned off, and InnoDB needs to complete all full purge and merge Insert buffer operations, which takes a certain amount of time, sometimes several hours.

A parameter value of 1 is the default value for this parameter, which means that full purge and Merge insert buffe operations are not completed when MySQL is turned off, but dirty pages in the buffer pool are still written to disk.

A parameter value of 2 means that neither the full purge and Merge insert buffer operations are completed nor the dirty pages in the buffer pool are flushed to disk, but the log is written to the log file.

2.3 shut down the MySQL service

The command is as follows:

[root@source bin] # / usr/local/mysql/bin/mysqladmin-uroot-poracle123 shutdown

2.4 replace MySQL software

You need to execute the unlink mysql command to unlink to the MySQL version 5.6.47 file.

Extract the new version of the MySQL package, then re-link and grant MySQL permissions. The command is as follows:

Cd / usr/local

Unlink mysql

Tar-zxvf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

Ln-s mysql-5.7.20-linux-glibc2.12-x86_64 mysql

Chown-R mysql:mysql mysql

2.5 replace parameter file

Replace the MySQL5.6 configuration file with version 5. 7 my.cnf.

2.6 start the MySQL instance

Note: during the startup process, you need to add-skip-grant-tables and-skip-networking parameters to ensure that there are no application connections, making the upgrade process more secure.

/ usr/local/mysql/bin/mysqld_safe-defaults-file=/etc/my.cnf-skip-grant-tables-skip-networking &

2.7 upgrade system table data dictionary information

The command is as follows:

/ usr/local/mysql/bin/mysql_upgrade

Output result:

[root@source local] # / usr/local/mysql/bin/mysql_upgradeChecking if update is needed.Checking server version.Running queries to upgrade MySQL server.Checking system database.mysql.columns_priv OKmysql.db OKmysql.engine_cost OKmysql.event OKmysql.func OKmysql.general_log OKmysql.gtid_executed OKmysql.help_category OKmysql.help_keyword OKmysql.help_relation OKmysql.help_topic OKmysql.innodb_index_stats OKmysql.innodb_table_stats OKmysql.ndb_binlog_index OKmysql.plugin OKmysql. Proc OKmysql.procs_priv OKmysql.proxies_priv OKmysql.server_cost OKmysql.servers OKmysql.slave_master_info OKmysql.slave_relay_log_info OKmysql.slave_worker_info OKmysql.slow_log OKmysql.tables_priv OKmysql.time_zone OKmysql.time_zone_leap _ second OKmysql.time_zone_name OKmysql.time_zone_transition OKmysql.time_zone_transition_type OKmysql.user OKUpgrading the sys schema.Checking databases.sys.sys_config OKtest.BONUS OKtest.DEPT OKtest.EMP OKtest.SALGRADE OKUpgrade process completed successfully.Checking if update is needed. [root@source local] #

No error is reported, which indicates that the system table data dictionary information has been upgraded successfully.

2.8 restart the MySQL service

First stop the MySQL service with the following command:

/ usr/local/mysql/bin/mysqladmin-uroot-poracle123 shutdown

Then start the MySQL service normally with the following command:

/ usr/local/mysql/bin/mysqld_safe-defaults-file=/etc/my.cnf &

Description: start the MySQL database normally, do not use the-skip-grant-tables and-skip-networking parameters.

2.9 verify the MySQL version

At present, it is a MySQL5.7 version, which proves that the upgrade is successful:

[root@source local] # / usr/local/mysql/bin/mysql-V

/ usr/local/mysql/bin/mysql Ver 14.14 Distrib 5.7.20, for linux-glibc2.12 (x86 / 64) using EditLine wrapper

After reading the above, do you have any further understanding of how to upgrade MySQL 5.6.47 to 5.7.20? If you want to know more knowledge or related content, please follow the industry information channel, thank you for your support.

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.