Shulou(Shulou.com)06/01 Report--

Introduction to MySQL5.6 upgrade MySQL5.7 environment

Part1: write at the front

When it comes to MySQL upgrade, there are countless articles online, but most of them are old versions, such as 5.1 to 5.5,5.5 to 5.6. today, I will introduce the methods and precautions of upgrading MySQL5.6 to MySQL5.7 version.

Part2: upgrade method

There are generally two ways to upgrade:

1. Using mysqldump to directly export sql files, import into the new library, this method is the most convenient, but also the safest, disadvantages, but also obvious, large library of mysqldump time-consuming and laborious.

two。 Directly replace the installation directory of mysql and my.cnf, and use mysql_upgrade to upgrade the system table. This method needs to back up the original files, but it belongs to physical copy and is fast. On the downside, cross-version upgrades are not recommended, such as mysql5.1 to mysql5.6,mysql5.5 to mysql5.7 and so on.

This paper uses the second method of upgrade.

Part3: environment

Database software directory: / usr/local/mysql

My.cnf location: / etc/my.cnf

Database data directory: / data/mysql

Slow log directory: / data/slowlog

Preparatory work

Part1:MySQL5.6.25

[root@HE3] # mysql-uroot-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 1Server version: 5.6.25-log MySQL Community Server (GPL) Copyright (c) 2000, 2015, 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 () | +-+ | 5.6.25-log | +-+ 1 row in set (0.00 sec) mysql > use helei;Database changedmysql > show tables +-+ | Tables_in_helei | +-+ | helei | +-+ 1 row in set (0.00 sec)

What I use here is version 5.6.25 as the upgrade library, in which the database helei and table helei have been simulated and created to verify the upgrade later.

Part2: backup

The backup needs to back up the my.cnf, the database installation directory, and the data directory.

[root@HE3 ~] # / etc/init.d/mysqld stop

Shutting down MySQL.. SUCCESS!

[root@HE3 etc] # cp-rp my.cnf my_56_old.cnf

[root@HE3 local] # cp-rp mysql mysql_56_old

[root@HE3 ~] # rm-rf / usr/local/mysql

[root@HE3 data] # cp-rp mysql/ mysql_56_old

Here I directly use the cp way to do.

Part3: replacing

1. Replace / etc/my.cnf with my.cnf of mysql5.7

[root@HE3 ~] # vi / etc/ my.cnf [client] port=3306socket=/tmp/mysql.sockdefault-character-set=utf8 [mysql] no-auto-rehashdefault-character-set=utf8 [mysqld] port=3306character-set-server=utf8socket=/tmp/mysql.sockbasedir=/usr/local/mysqldatadir=/data/mysqlexplicit_defaults_for_timestamp=truelower_case_table_names=1back_log=103max_connections=3000max_connect_errors=100000table_open_cache=512external-locking=FALSEmax_allowed_packet=32Msort_buffer_size=2Mjoin_buffer_size=2Mthread_cache_size=51query_cache_size=32M#query_cache_limit=4Mtransaction_isolation=REPEATABLE-READtmp _ table_size=96Mmax_heap_table_size=96M###***slowqueryparameterslong_query_time=1slow_query_log = 1 query words, log accounts, files, figures, files, tables, figures, statistics, etc. Log-info-repository=table#relay-log-recovery=1#***MyISAMparameterskey_buffer_size=16Mread_buffer_size=1Mread_rnd_buffer_size=16Mbulk_insert_buffer_size=1M#skip-name-resolve###***master-slavereplicationparametersserver-id=$SERVERIDslave-skip-errors=all#***Innodbstorageengineparametersinnodb_buffer_pool_size=512Minnodb_data_file_path=ibdata1:10M:autoextend#innodb_file_io_threads=8innodb_thread_concurrency=16innodb_flush_log_at_trx_commit=1innodb_log_buffer_size=16Minnodb_log_file_size=512Minnodb_log_files_in_group=2innodb _ max_dirty_pages_pct=75innodb_buffer_pool_dump_pct=50innodb_lock_wait_timeout=50innodb_file_per_table=oninnodb_buffer_pool_dump_at_shutdown=1innodb_buffer_pool_load_at_startup=1sql_mode=STRICT_TRANS_TABLES NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_ SUBSTITUTION[mysqldump] quickmax_allowed_packet= 32M[myisamchk] key_buffer=16Msort_buffer_size=16Mread_buffer=8Mwrite_buffer= 8M[mysqld _ safe] open-files-limit=8192log-error=/data/mysql/error.logpid-file=/data/mysql/mysqld.pid

two。 Extract the new version of mysql

[root@HE3 ~] # tar xvf mysql-5.7.15-linux-glibc2.5-x86_64.tar.gz

[root@HE3 ~] # mv mysql-5.7.15-linux-glibc2.5-x86_64 / usr/local/mysql

[root@HE3] # chown-R mysql. / usr/local/mysql

3. Replace the new version of mysqld startup script

[root@HE3 ~] # cp / usr/local/mysql/support-files/mysql.server / etc/init.d/mysqld

Cp: overwrite `/ etc/init.d/mysqld'? Y

Start the upgrade

Part1: start

[root@HE3] # / etc/init.d/mysqld startStarting MySQL. SUCCESS! [root@HE3] # ps-ef | grep mysqlroot 26467 10 20:30 pts/2 00:00:00 / bin/sh / usr/local/mysql/bin/mysqld_safe-- datadir=/data/mysql-- pid-file=/data/mysql/HE3.pidmysql 27197 26467 4 20:30 pts/2 00:00:01 / usr/local/mysql/bin/mysqld-- basedir=/usr/local/mysql-- datadir=/data/mysql-- plugin-dir=/usr/local/mysql/ Lib/plugin-user=mysql-log-error=/data/mysql/error.log-open-files-limit=8192-pid-file=/data/mysql/HE3.pid-socket=/tmp/mysql.sock-port=3306root 27235 25656 0 20:31 pts/2 00:00:00 grep mysql

Although the startup is successful here, a lot of error messages can be caught in the error log because there is no upgrade dictionary, such as:

2016-10-20T03:30:27.375466Z 0 [ERROR] Native table 'performance_schema'.'events_statements_summary_by_program' has the wrong structure2016-10-20T03:30:27.375506Z 0 [ERROR] Native table' performance_schema'.'events_transactions_current' has the wrong structure2016-10-20T03:30:27.375536Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_history' has the wrong structure2016-10-20T03:30:27.375568Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_history_long' has the wrong structure2016-10-20T03:30:27.375598Z 0 [ERROR] Native table 'performance_schema'.'events_transactions_summary_by_thread_by_event_name' has the wrong structure

Part2: system table upgrade

1. Upgrade data dictionary

[root@HE3] # / usr/local/mysql/bin/mysql_upgrade-uroot-pMANAGERmysql_upgrade: [Warning] Using a password on the command line interface can be insecure.Checking 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.helei.helei OKsys.sys_config OKUpgrade process completed successfully.Checking if update is needed.

two。 Restart the instance and check the error log again

[root@HE3 ~] # / etc/init.d/mysqld restart

Shutting down MySQL.. SUCCESS!

Starting MySQL. SUCCESS!

3. Make sure that the settings of some paths are the same as those of the previous my.cnf. If you set them incorrectly, the database may not be started.

[ERROR] Could not use / data/slowlog/slow.log for logging

I am here because the location of the slow log is not consistent with the old mysql5.6 path, so you can create / data/slowlog. Here, you need to read the error carefully, and then find and solve the problem according to the error.

Verification result

Part1: enter the user name and password check result

[root@HE3] # mysql-uroot-pEnter password: Welcome to the MySQL monitor. Commands end with; or\ g.Your MySQL connection id is 2Server version: 5.7.15-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 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 () | +-+ | 5.7.15-log | +-+ 1 row in set (0.00 sec)

Part2: verify that libraries and tables are missing

Mysql > show databases;+-+ | Database | +-+ | information_schema | | helei | | mysql | | performance_schema | | sys | | test | +-+ 6 rows in set (0.00 sec)

After upgrading 5.7, we will find that there is an additional sys library, which has many functions. We will introduce it to you later.

-- Summary.

There are many ways to upgrade MySQL, but no matter which way you use, you should be prepared for backup and rollback to avoid the loss caused by upgrade failure. As the author's level is limited and the writing time is very short, it is inevitable that there will be some errors or inaccuracies in the article. I urge readers to criticize and correct them.

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.