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

This article is about how to upgrade from MySQL5.6.30 to MySQL5.7.18. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

This upgrade adopts: out of place logical upgrade mode:

Basic steps:

①: stop business and back up existing databases (mysqldump or physical backup)

②: download the MySQL5.7.18 package and install it to another directory

③: modify the configuration my.cnf configuration file to specify basedir as the new software directory

④: start the new version of the database, then perform mysql_upgrade-uroot-p to upgrade the database

⑤: upgrade completed, restart the database

⑥: check the upgrade result: select version ()

Upgrade MySQL check:

①: whether the existing MySQL database has been backed up

②: whether the business has stopped

1. Check the existing environment:

①: check the MySQL status:

[mysql@db2 ~] $ps-ef | grep mysql

Mysql 1806 1 0 14:36? 00:00:00 / bin/sh / mysql/bin/mysqld_safe-- defaults-file=/mysql/my.cnf

Mysql 1868 1806 0 14:36? 00:00:00 / mysql/bin/mysqld-defaults-file=/mysql/my.cnf-basedir=/mysql-datadir=/mysql/data-plugin-dir=/mysql/lib/plugin-log-error=/mysql/data/db2.err-pid-file=/mysql/data/db2.pid

②: viewing existing configuration fil

[mysql@db2 ~] $vim / mysql/my.cnf

[mysql]

No_auto_rehash

Default_character_set = utf8

Socket = / mysql/data/mysql.sock

[client]

Default_character_set = utf8

[mysqld]

Server_id = 1607

Port = 3306

Basedir = / mysql/

Datadir = / mysql/data/

Socket = / mysql/data/mysql.sock

Pid_file = / mysql/data/mysql.pid

Log_error = / mysql/data/mysql_error.log

Log_bin = / mysql/data/mysql_bin

Relay_log = / mysql/data/relay_bin

Character_set_server = utf8

Collation_server = utf8_general_ci

Innodb_buffer_pool_size = 8G

Innodb_buffer_pool_instances = 8

Innodb_log_file_size = 1G

Innodb_log_files_in_group = 3

Innodb_log_buffer_size = 24m

Innodb_flush_log_at_trx_commit = 1

Innodb_file_per_table = 1

Innodb_flush_method = O_DIRECT

Innodb_io_capacity = 200

Innodb_io_capacity_max = 600,

Innodb_thread_concurrency = 0

Innodb_autoinc_lock_mode = 2

Innodb_lock_wait_timeout = 60

Innodb_read_io_threads = 4

Innodb_write_io_threads = 4

Innodb_max_dirty_pages_pct = 80

Innodb_autoextend_increment = 512

Innodb_checksum_algorithm = NONE

Innodb_doublewrite = 0

Innodb_use_native_aio = 1

Innodb_open_files = 8192

Sync_binlog = 1

Sync_relay_log = 1

Relay_log_info_repository = TABLE

Master_info_repository = TABLE

Expire_logs_days = 10

Binlog_format = ROW

Transaction-isolation = READ-COMMITTED

Concurrent_insert = 2

Skip_slave_start = TRUE

Back_log = 2000

Thread_stack = 256k

Thread_cache_size = 256

Key_buffer_size = 256m

Tmp_table_size = 64m

Read_buffer_size = 2m

Read_rnd_buffer_size = 8m

Sort_buffer_size = 2m

Join_buffer_size = 2m

Query_cache_size = 0

Query_cache_type = 0

Max_heap_table_size = 64m

Binlog_cache_size = 2m

Table_open_cache = 8192

Max_allowed_packet = 64m

Bulk_insert_buffer_size = 64m

Max_connect_errors = 100000

Max_connections = 500,

Connect_timeout = 300

Wait_timeout = 86400

Interactive_timeout = 86400

Lower_case_table_names = 1

Open_files_limit = 20480

Skip_name_resolve

Skip_external_locking

Explicit_defaults_for_timestamp = TRUE

Sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

③: check the version information of existing MySQL:

[mysql@db2] $mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 6

Server version: 5.6.30 Source distribution

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >\ s

-

Mysql Ver 14.14 Distrib 5.6.30, for Linux (x86 / 64) using EditLine wrapper

Connection id: 6

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile:''

Using delimiter:

Server version: 5.6.30 Source distribution

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: latin1

Db characterset: latin1

Client characterset: utf8

Conn. Characterset: utf8

UNIX socket: / tmp/mysql.sock

Uptime: 5 min 32 sec

Threads: 1 Questions: 18 Slow queries: 0 Opens: 67 Flush tables: 1 Open tables: 60 Queries per second avg: 0.054

-

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.6.30 |

+-+

1 row in set (0.01 sec)

Mysql >

④: close the database:

[mysql@db2] $mysql-u root-p-- execute= "SET GLOBAL innodb_fast_shutdown=0"

Enter password:

[mysql@db2] $mysql-u root-p-- execute= "show global variables like 'innodb_fast_shutdown'"

Enter password:

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_fast_shutdown | 0 | |

+-+ +

[mysql@db2] $mysqladmin-uroot-p shutdown

Enter password:

Note: the innodb_fast_shutdown parameter explains:

Turn off: innodb_fast_shutdown=

0: complete all full purge and merge insert buffer operations (e. G. when upgrading InnoDB plugin)

1: by default, the above operation is not required, but the dirty pages in the buffer pool are refreshed.

2: instead of completing the above two operations, the log is written to the log file, and the recovery operation recovery will be performed the next time you start.

A restore operation is required when the database (such as the kill command) / innodb_fast_shutdown=2 is not shut down properly.

2. Download mysql5.7.18 and extract it to a new directory

Mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz (this package can be unzipped and can be used without installation)

[mysql@db2 ~] $tar zxvf mysql-5.7.18-linux-glibc2.5-x86_64.tar.gz (extract the software directly to / home/mysql)

[mysql@db2] $mv mysql-5.7.18-linux-glibc2.5-x86_64/ mysql5718 (mysql5718 this is the new software directory)

3. Modify the configuration file: my.cnf

[mysql@db2 mysql5718] $cp / mysql/my.cnf. /

[mysql@db2 mysql5718] $vim my.cnf

Basedir = / home/mysql/mysql5718/-just modify this line and point to the new directory

4. Start the MySQL database with the new software:

[mysql@db2 mysql5718] $/ home/mysql/mysql5718/bin/mysqld_safe-- defaults-file=/home/mysql/mysql5718/my.cnf-- socket=/mysql/data/mysql.sock &

5. Upgrade MySQL:

[mysql@db2 mysql5718] $/ home/mysql/mysql5718/bin/mysql_upgrade-uroot-p-- socket=/mysql/data/mysql.sock

Enter password:

Checking if update is needed.

Checking server version.

Running queries to upgrade MySQL server.

Checking system database.

Mysql.columns_priv OK

Mysql.db OK

Mysql.engine_cost OK

Mysql.event OK

Mysql.func OK

Mysql.general_log OK

Mysql.gtid_executed OK

Mysql.help_category OK

Mysql.help_keyword OK

Mysql.help_relation OK

Mysql.help_topic OK

Mysql.innodb_index_stats OK

Mysql.innodb_table_stats OK

Mysql.ndb_binlog_index OK

Mysql.plugin OK

Mysql.proc OK

Mysql.procs_priv OK

Mysql.proxies_priv OK

Mysql.server_cost OK

Mysql.servers OK

Mysql.slave_master_info OK

Mysql.slave_relay_log_info OK

Mysql.slave_worker_info OK

Mysql.slow_log OK

Mysql.tables_priv OK

Mysql.time_zone OK

Mysql.time_zone_leap_second OK

Mysql.time_zone_name OK

Mysql.time_zone_transition OK

Mysql.time_zone_transition_type OK

Mysql.user OK

Upgrading the sys schema.

Checking databases.

Sys.sys_config OK

Upgrade process completed successfully.

Checking if update is needed.

The above information indicates that the MySQL upgrade is complete.

6. Restart the database after the upgrade is completed

[mysql@db2 mysql5718] $/ home/mysql/mysql5718/bin/mysqladmin shutdown-u root-p

[mysql@db2 mysql5718] $/ home/mysql/mysql5718/bin/mysqld_safe-- defaults-file=/home/mysql/mysql5718/my.cnf-- socket=/mysql/data/mysql.sock &

7. Log in to the database to check the upgrade status:

[mysql@db2 bin] $mysql-u root-p

Enter password:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket'/ tmp/mysql.sock' (2)

Sometimes the above error occurs when we log in to MySQL, but we have already specified the directory of mysql.sock in the configuration file, so why look for other directories? there are two solutions:

First, add the following information to the my.cnf configuration file: (sometimes it doesn't work, for example, our above configuration file has been added but still reported an error)

[mysql]

Socket = / mysql/data/mysql.sock

Second: let's just make a soft connection to the tmp:

[mysql@db2 bin] $ln-s / mysql/data/mysql.sock / tmp/mysql.sock

After completing the soft connection, we wait for the database to OK:

[mysql@db2 bin] $mysql-u root-p

Enter password:

Welcome to the MySQL monitor. Commands end with; or\ g.

Your MySQL connection id is 3

Server version: 5.7.18-log MySQL Community Server (GPL)

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

Affiliates. Other names may be trademarks of their respective

Owners.

Type 'help;' or'\ h' for help. Type'\ c'to clear the current input statement.

Mysql >\ s

-

Mysql Ver 14.14 Distrib 5.6.30, for Linux (x86 / 64) using EditLine wrapper

Connection id: 3

Current database:

Current user: root@localhost

SSL: Not in use

Current pager: stdout

Using outfile:''

Using delimiter:

Server version: 5.7.18-log MySQL Community Server (GPL)

Protocol version: 10

Connection: Localhost via UNIX socket

Server characterset: utf8

Db characterset: utf8

Client characterset: utf8

Conn. Characterset: utf8

UNIX socket: / tmp/mysql.sock

Uptime: 4 min 16 sec

Threads: 1 Questions: 6 Slow queries: 0 Opens: 110 Flush tables: 1 Open tables: 25 Queries per second avg: 0.023

-

Mysql > select version ()

+-+

| | version () |

+-+

| | 5.7.18-log |

+-+

1 row in set (0.39 sec)

Mysql >

#

Knowledge expansion:

Turn off: innodb_fast_shutdown=

0: complete all full purge and merge insert buffer operations (e. G. when upgrading InnoDB plugin)

1: by default, the above operation is not required, but the dirty pages in the buffer pool are refreshed.

2: instead of completing the above two operations, the log is written to the log file, and the recovery operation recovery will be performed the next time you start.

A restore operation is required when the database (such as the kill command) / innodb_fast_shutdown=2 is not shut down properly.

Restore: innodb_force_recovery=

0: default, but perform all restore operations when recovery is required

1: ignore checked corrupt pages

2: prevent the main thread from running. If the main thread needs to perform full purge operations, it will cause crash.

3: no transaction rollback operation is performed

4: merge operation of insert buffer is not performed

5: do not view the undo log undo log,InnoDB storage engine will treat all uncommitted transactions as committed

6: do not roll forward

Thank you for reading! This is the end of the article on "how to upgrade MySQL5.6.30 to MySQL5.7.18". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.