MySQL5.5 upgrade to 5.7
First, prepare to work on a new server (10.12.21.184), as a binary package hostrole 10.12.21.120master10.12.21.184 slave, which downloads the latest stable version of MySQL5.6 from the library on 21.184, operation 1. Master-Slave build xtrbackup complete standby (20.120) start the new 5.5database on 20.184 according to the full standby, start the master-slave database as the slave library of 20.120, and wait for the slave database to catch up with the master database 2. Upgrade slave library
1. Extract 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
two。 Add environment variabl
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# modifies basedir and notes that the parameter # innodb_additional_mem_pool_size = 32m starts with MySQL 5.6.3 and # innodb_additional_mem_pool_size has been deprecated and will be removed in future MySQL versions. # added to table_cache=8192#thread_concurrency=48basedir=/usr/local/mysql/plugin_dir=/usr/local/mysql/lib/plugin: skip-slave-startsql_mode=''
4. Turn off the parameters of fast shutdown
[root@localhost (none)] > set global innodb_fast_shutdown=0;Query OK, 0 rows affected (0.00 sec) [root@localhost (none)] > show variables like 'innodb_fast_shutdown' +-- +-+ | Variable_name | Value | +-+-+ | innodb_fast_shutdown | 0 | +-+-+ 1 row in set (0.00 sec)
5. Close the slave library
[root@localhost local] # / data0/mysql/product/bin/mysqladmin-- defaults-extra-file=/data0/mysql/config/user.root.cnf shutdown
6. Start
[root@localhost local] # / usr/local/mysql/bin/mysqld_safe-- defaults-file=/data0/mysql/config/my56.cnf & [1] 9446 [root@localhost local] # 180605 15:37:12 mysqld_safe Logging to'/ data0/mysql/mysqllog/logfile/mysqld.err'.180605 15:37:12 when mysqld_safe Starting mysqld daemon with databases from / data0/mysql/dbdata starts, a large number of error logs appear as follows, which does not affect startup It is normal because the table structure of MySQL5.5 and MySQL5.5 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' has the wrong structure
7. Upgrade 5.6
/ usr/local/mysql/bin/mysql_upgrade-S / data0/mysql/dbdata/mysql.sock-uroot-pXXXXX
8. Close the library of 5.6
[root@localhost 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. Extract the MySQL5.7 and establish a soft connection
Cd / usr/local/ [root@localhost 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
[root@localhost local] # / usr/local/mysql/bin/mysqld_safe-- defaults-file=/data0/mysql/config/my57.cnf & [1] 11262 [root@localhost 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 is reported as normal for 5. 6 is different from the system table structure of 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: 11performance_schema'.'events_transactions_summary_by_thread_by_event_name' has the wrong structure2018 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.340948Z 0 [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 have type char (32) Found type char (16).
11. Upgrade 5.7 (for a long time, 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`Running: OK`cat`.`app _ data_command_ 1`Running: ALTER TABLE `cat`.`app _ data_command_ 1`Running: OK
twelve。 Restart the MySQL5.7 database
[root@localhost mysql_upgrade] # / usr/local/mysql/bin/mysqladmin--defaults-extra-file=/data0/mysql/config/user.root.cnf shutdown [root@localhost mysql_upgrade] # / usr/local/mysql/bin/mysqld_safe-- defaults-file=/data0/mysql/config/my57.cnf & III. Problems to pay attention to after upgrading.
1.sql_mode
MySQL5.7 defaults to the following sql_mode. To avoid the hidden trouble caused by previous non-standard statements, set sql_mode 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
