MySQL upgrade from 5.1.71 to 5.7.17

The last system vulnerability scan, sweep out a lot of MySQL loopholes, there is no good way, first upgrade to the latest version. When combing the MySQL database information found a set of Zabbix MySQL version is 5.1.71, the latest version is 5.7.17, the version spans too big, do not know whether the direct upgrade is feasible. The amount of data in the library is about 15G, so I want to backup first, and then upgrade with Mysql_upgrade after backup. If it doesn't work, create a new set of libraries to bring in the data.

Here are the upgrade steps that were tested on the virtual machine, and there were some problems in the middle, but fortunately the upgrade succeeded at the end, indicating that the road is still viable.

OS version Rhel 6.7 old MySQL version 5.1.71, new MySQL version 5.7.17.

1. View the old MySQL version and create the test database and test table

Mysql> select version (); +------------------+| version ()          |+------------------+| 5.1.71-community |+------------------+mysql> create  database zx; query ok, 1 row affected  (0.00 sec) mysql> show databases;+--------- -----------+| database           |+--------------- -----+| information_schema | |  mysql              | |  test               | |  zx                  |+--------------------+4 rows in set  (0.00 sec) mysql> use zx;database  changedmysql> create table test_upgrade  (Id int); query ok, 0 rows affected  (0.07 sec) ... mysql> select count (*)   from test_upgrade;+----------+| count (*)  |+----------+| 33554432 |+----------+1  row in set  (0.00 SEC)

Now there are 33,554,432 data in the Test_upgrade table.

2. Stop the old MySQL and back up the data

[[Email protected] mysql]# service MySQL stopshutting down MySQL. success! #这里直接用cp备份数据 [[email protected] lib]# cp-r mysql mysql_bak[[email protected] lib]# Du-sk mysql*250880mysql250880mysql_ Bak

3, install the new msql software, here download is compiled good decompression can use

[Email protected] ~]# tar-zxvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz #解压完后移动到/opt directory [[email protected] ~]# MV mysql-5.7.17-linux-glibc2.5-x86_64 Mysql-5.7.17[[email protected] ~]# mv Mysql-5.7.17/opt[[email protected] ~]# cd/opt [Email protected] opt]# chown-r mysql:mysql mysql-5.7.17/[[email protected] opt]# lltotal 4drwxr-xr-x. 9 MySQL mysql 4096 Mar 21:07 mysql-5.7.17

4, modify the MY.CNF

Main modification Basedir Parameters:

[Email protected] opt]# vi/etc/my.cnfbasedir=/opt/mysql-5.7.17

5. Start new MySQL

[Email protected] mysql-5.7.17]#./bin/mysqld_safe &

1) Start error 1:

2017-03-22t13:12:39.131998z 0 [error] innodb: the auto-extending innodb_system  data file  './ibdata1 '  is of a different size 640 pages   (ROUNDED DOWN TO MB)  than specified in the .cnf file:  initial 768 pages, max 0  (Relevant if non-zero)  pages!2017-03-22T13 :12:39.132028z 0 [error] innodb: plugin initialization aborted with  error generic error2017-03-22t13:12:39.733953z 0 [error] plugin  ' InnoDB '   init function returned error.2017-03-22t13:12:39.733986z 0 [error] plugin  ' InnoDB '  registration as a storage engine failed.2017-03-22t13:12:39.733994z  0 [ERROR] Failed to initialize plugins.2017-03-22T13:12:39.734000Z 0  [Error] aborting

Workaround Add the following parameters in the [mysqld] section of MY.CNF

Innodb_data_file_path = Ibdata1:10m:autoextend

Start again

2) Start error 2:

2017-03-22t13:16:03.374717z 0 [ERROR] unknown option '--skip-locking ' 2017-03-22t13:16:03.374735z 0 [ERROR] aborting

Workaround comment out the relevant parameters from the MY.CNF skip-locking

Start again

3) Start Error 3:

2017-03-22t13:18:20.278752z 0 [ERROR] Fatal error:mysql.user table is damaged. Please run mysql_upgrade.2017-03-22t13:18:20.278954z 0 [ERROR] aborting

5.7 Unable to read 5.1 mysql.user table, workaround use--skip-grant-tables parameter to skip authorization validation

Start again

[Email protected] mysql-5.7.17]#/bin/mysqld_safe--skip-grant-tables&

Startup success

2017-03-22t13:20:23.919677z 0 [Note]./bin/mysqld:ready for connections. Version: ' 5.7.17 ' socket: '/var/lib/mysql/mysql.sock ' port:3306 mysql Community Server (GPL)

6. Perform the upgrade

[[Email protected] mysql-5.7.17]# ./bin/mysql_upgrade -urootchecking 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.host                                           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                                       OKzx.test_upgrade                                      OKUpgrade process completed  Successfully. Checking if update is needed.

7, restart MySQL, and verify

[[Email protected] mysql-5.7.17]# ./bin/mysqladmin -uroot -p shutdown[[email  protected] mysql-5.7.17]# ./bin/mysqld_safe &mysql> select version (); +-- ---------+| version ()  |+-----------+| 5.7.17    |+-----------+1 row  in set  (0.00 sec) mysql> show databases;+--------------------+| database            |+--------------------+| information_ schema | |  mysql              | |  performance_schema | |  sys                | |  test               | |  zx                 |+--------------------+6 rows in set  (0.00 sec) mysql> select count (*)  from zx.test_upgrade;+----------+| count (*)  |+----------+| 33554432 |+--------- -+1 row in set  (0.01 SEC)

After verifying the pass, remove the old version of the MySQL software.

This article is from the "DBA fighting!" blog, so be sure to keep this source http://hbxztc.blog.51cto.com/1587495/1909466

MySQL upgrade from 5.1.71 to 5.7.17