MySQL升级从5.1.71到5.7.17

最近系统漏洞扫描，扫出来很多MySQL的漏洞，没有什么好的办法，先升级到最新版本。在梳理MySQL数据库信息时发现有一套zabbix用的MySQL版本是5.1.71的，现在的最新版本是5.7.17，版本跨越的太大了，不知道直接升级是否可行。库的数据量大概15G，于是就想先备份，备份完后用mysql_upgrade做升级。如果实在不行就再新创建一套库把数据导进去。

下面是在虚拟机上测试的升级步骤，中间也遇到了一些问题，不过好在最后还是升级成功了，说明这条路还是可行了。

操作系统版本RHEL 6.7 旧的MySQL版本5.1.71，新的MySQL版本5.7.17。

1、查看旧的MySQL版本，并创建测试数据库和测试表

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)

现在test_upgrade表里有33554432条数据。

2、停止旧MySQL，并备份数据

[root@rhel6 mysql]# service mysql stopShutting down MySQL.. SUCCESS! #这里直接用cp备份数据[root@rhel6 lib]# cp -R mysql mysql_bak[root@rhel6 lib]# du -sk mysql*250880  mysql250880  mysql_bak

3、安装新Msql软件，这里下载的是编译好的解压就可以使用

[root@rhel6 ~]# tar -zxvf mysql-5.7.17-linux-glibc2.5-x86_64.tar.gz #解压完后移动到/opt目录下[root@rhel6 ~]# mv mysql-5.7.17-linux-glibc2.5-x86_64 mysql-5.7.17[root@rhel6 ~]# mv mysql-5.7.17 /opt[root@rhel6 ~]# cd /opt[root@rhel6 opt]# chown -R mysql:mysql mysql-5.7.17/[root@rhel6 opt]# lltotal 4drwxr-xr-x. 9 mysql mysql 4096 Mar 22 21:07 mysql-5.7.17

4、修改my.cnf

主要修改basedir参数：

[root@rhel6 opt]# vi /etc/my.cnfbasedir=/opt/mysql-5.7.17

5、启动新MySQL

[root@rhel6 mysql-5.7.17]# ./bin/mysqld_safe &

1)启动报错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

解决方法在my.cnf中的[mysqld]部分添加如下参数

innodb_data_file_path = ibdata1:10M:autoextend

再次启动

2)启动报错2：

2017-03-22T13:16:03.374717Z 0 [ERROR] unknown option '--skip-locking'2017-03-22T13:16:03.374735Z 0 [ERROR] Aborting

解决方法从my.cnf中注释掉相关参数skip-locking

再次启动

3)启动报错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无法读取5.1的mysql.user表，解决方法使用--skip-grant-tables参数跳过授权验证

再次启动

[root@rhel6 mysql-5.7.17]# ./bin/mysqld_safe --skip-grant-tables&

启动成功

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、执行升级

[root@rhel6 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、重启MySQL，并验证

[root@rhel6 mysql-5.7.17]# ./bin/mysqladmin -uroot -p shutdown[root@rhel6 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)

验证通过后，删除旧版本的MySQL软件。