MySQL5.5 Upgrade to 5.7

First, the preparatory work

1. New Server (10.12.21.184), as from the library
2. Download MySQL5.6, 5.7 's latest stable version of binary package on 21.184

Host	role
10.12.21.120	Master
10.12.21.184	Slave

Second, Operation 1. Master-Slave construction

1. Xtrbackup fully prepared (20.120)
2. Starting with a new 5.5 database on 20.184, as a 20.120 from the library
3. Start Master and slave, wait for the main library to be chased from the library

2. Upgrading from the Library

1. Unzip the package

cd /data0/mysql_updatetar xf mysql-5.6.40-linux-glibc2.12-x86_64.tar.gzcd /usr/localln -s /data0/mysql_upgrade/mysql-5.6.40-linux-glibc2.12-x86_64 mysql

2. Add Environment variables

export PATH=/usr/local/mysql/bin:$PATHexport LD_LIBRARY_PATH=/usr/local/mysql/lib:$LD_LIBRARY_PATH

3. Modify the configuration 5.6 file

cd /data0/mysql/config/cp my.cnf my56.cnf#修改basedir,并注释该参数# innodb_additional_mem_pool_size = 32M 从MySQL 5.6.3开始，   #innodb_additional_mem_pool_size已被弃用，并将在未来的MySQL版本中删除。#table_cache=8192#thread_concurrency=48basedir=/usr/local/mysql/plugin_dir=/usr/local/mysql/lib/plugin新增：skip-slave-startsql_mode=‘‘

4. Turn off the fast shutdown parameters

[[email protected] (none)]>set global innodb_fast_shutdown=0;Query OK, 0 rows affected (0.00 sec)[[email protected] (none)]>show variables like ‘innodb_fast_shutdown‘;+----------------------+-------+| Variable_name        | Value |+----------------------+-------+| innodb_fast_shutdown | 0     |+----------------------+-------+1 row in set (0.00 sec)

5. Close the Library

[[email protected] local]# /data0/mysql/product/bin/mysqladmin --defaults-extra-file=/data0/mysql/config/user.root.cnf shutdown

6. Start

[[email protected] local]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data0/mysql/config/my56.cnf &[1] 9446[[email protected] local]# 180605 15:37:12 mysqld_safe Logging to ‘/data0/mysql/mysqllog/logfile/mysqld.err‘.180605 15:37:12 mysqld_safe Starting mysqld daemon with databases from /data0/mysql/dbdata

• Startup time will appear a large number of error logs, do not affect the start, is normal phenomenon
• Because the table structure of MySQL5.5 and 5.6 is different

2018-06-05 15:37:16 10312 [ERROR] Native table ' Performance_schema '. ' Events_statements_summary_by_digest ' has the wrong structure2018-06-05 15:37:16 10312 [ERROR] Native table ' Performance_ Schema '. ' Users ' has the wrong structure2018-06-05 15:37:16 10312 [ERROR] Native table ' Performance_schema '. ' Accounts ' has the wrong structure2018-06-05 15:37:16 10312 [ERROR] Native table ' Performance_schema '. ' Hosts ' has the wrong structure2018-06-05 15:37:16 10312 [ERROR] Native table ' Performance_schema '. ' Socket_instances ' has the wrong structure2018-06-05 15:37:16 10312 [ERROR] Native table ' Performance_schema '. ' Socket_summary_by_instance ' has the wrong structure2018-06-05 15:37:16 10312 [ERROR] Native table ' Performance_schema '. ' Socket_summary_by_event_name ' has the wrong structure2018-06-05 15:37:16 10312 [ERROR] Native table ' Performance_schema ' .‘ Session_connect_attrs ' has the wrong structure2018-06-05 15:37:16 10312 [ERROR] Native table ' Performance_schema '. ' Session_account_connect_attrs ' hasThe wrong structure 

7. Upgrade 5.6

/usr/local/mysql/bin/mysql_upgrade -S /data0/mysql/dbdata/mysql.sock -uroot -pXXXXX

8. Close the 5.6 library

[[email protected] local]# /usr/local/mysql/bin/mysqladmin--defaults-extra-file=/data0/mysql/config/user.root.cnf shutdown180605 16:02:30 mysqld_safe mysqld from pid file/data0/mysql/dbdata/mysqld.pid ended[1]+  Done                    /usr/local/mysql/bin/mysqld_safe --defaults-file=/data0/mysql/config/my56.cnf

9. Unzip the MySQL5.7 and establish a soft connection

cd /usr/local/[[email protected] local]# ln -s /data0/mysql_upgrade/mysql-5.7.22-linux-glibc2.12-x86_64 mysql

10. Modify the configuration file to start MySQL5.7

[[email protected] local]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data0/mysql/config/my57.cnf &[1] 11262[[email protected] local]# 2018-06-05T08:11:10.690409Z mysqld_safe Logging to ‘/data0/mysql/mysqllog/logfile/mysqld.err‘.2018-06-05T08:11:10.774997Z mysqld_safe Starting mysqld daemon with databases from /data0/mysql/dbdata

• The following error appears as a normal phenomenon
• The reason is that the system table structure differs between 5.6 and 5.7

018-06-05t08:11:14.340649z 0 [ERROR] Native table ' Performance_schema '. ' Events_transactions_current ' has the wrong structure2018-06-05t08:11:14.340681z 0 [ERROR] Native table ' Performance_ Schema '. ' Events_transactions_history ' has the wrong structure2018-06-05t08:11:14.340717z 0 [ERROR] Native table ' Performance_ Schema '. ' Events_transactions_history_long ' has the wrong structure2018-06-05t08:11:14.340774z 0 [ERROR] Native table ' Performance_schema '. ' Events_transactions_summary_by_thread_by_event_name ' has the wrong structure2018-06-05t08:11:14.340828z 0 [ERROR] Native table ' Performance_schema '. Events_transactions_summary_by_account_by_event_name ' has the wrong structure2018-06-05t08:11:14.340869z 0 [ERROR] Native table ' Performance_schema '. Events_transactions_summary_by_user_by_event_name ' has the wrong structure2018-06-05t08:11:14.340911z 0 [ERROR] Native table ' Performance_schema '. Events_transactions_summary_by_host_by_event_name ' has the wrong structure2018-06-05t08:11:14.340948z0 [ERROR] Native table ' Performance_schema '. ' Events_transactions_summary_global_by_event_name ' has the wrong structure2018-06-05t08:11:14.345136z 0 [ERROR] Incorrect definition of Table performance_schema.users:expected column ' USER ' at position 0 to having type char (+), found Type char (16).

11. Upgrade 5.7 (long time, put in the background)

 nohup /usr/local/mysql/bin/mysql_upgrade -S /data0/mysql/dbdata/mysql.sock -uroot -proot 2>&1 >/data0/mysql_upgrade/56to57.log &

www_fangdd_site.fdd_patsNote     : TIME/TIMESTAMP/DATETIME columns of old format have been upgraded to the new format.status   : OK`cat`.`alteration`Running  : ALTER TABLE `cat`.`alteration` FORCEstatus   : OK`cat`.`app_data_command_1`Running  : ALTER TABLE `cat`.`app_data_command_1` FORCEstatus   : OK

12. Restart the MySQL5.7 database

[[email protected] mysql_upgrade]# /usr/local/mysql/bin/mysqladmin--defaults-extra-file=/data0/mysql/config/user.root.cnf shutdown[[email protected] mysql_upgrade]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/data0/mysql/config/my57.cnf &

Iii. issues needing attention after the upgrade

1.sql_mode
MySQL5.7 that the following sql_mode, in order to avoid the previous statement of the hidden dangers caused by the sql_mode set to "

sql_mode                 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

MySQL5.5 Upgrade to 5.7