最近系统漏洞扫描，扫出来很多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版本，并创建测试数据库和测试表
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
|
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
changed
mysql>
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，并备份数据
|
1
2
3
4
5
6
7
|
[root@rhel6 mysql]# service mysql stop
Shutting down MySQL.. SUCCESS!
#这里直接用cp备份数据
[root@rhel6 lib]# cp -R mysql mysql_bak
[root@rhel6 lib]# du -sk mysql*
250880 mysql
250880 mysql_bak
|
3、安装新Msql软件，这里下载的是编译好的解压就可以使用
|
1
2
3
4
5
6
7
8
9
|
[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]# ll
total 4
drwxr-xr-x. 9 mysql mysql 4096 Mar 22 21:07 mysql-5.7.17
|
4、修改my.cnf
主要修改basedir参数：
|
1
2
|
[root@rhel6 opt]# vi /etc/my.cnf
basedir=/opt/mysql-5.7.17
|
5、启动新MySQL
|
1
|
[root@rhel6 mysql-5.7.17]# ./bin/mysqld_safe &
|
1)启动报错1：
|
1
2
3
4
5
|
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 error
2017-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]部分添加如下参数
|
1
|
innodb_data_file_path = ibdata1:10M:autoextend
|
再次启动
2)启动报错2：
|
1
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：
|
1
2
|
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参数跳过授权验证
再次启动
|
1
|
[root@rhel6 mysql-5.7.17]# ./bin/mysqld_safe
--skip-grant-tables&
|
启动成功
|
1
2
|
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、执行升级
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
|
[root@rhel6 mysql-5.7.17]# ./bin/mysql_upgrade -uroot
Checking if
update
is
needed.
Checking server version.
Running queries
to
upgrade MySQL server.
Checking system
database
.
mysql.columns_priv OK
mysql.db OK
mysql.engine_cost OK
mysql.event OK
mysql.func OK
mysql.general_log OK
mysql.gtid_executed OK
mysql.help_category OK
mysql.help_keyword OK
mysql.help_relation OK
mysql.help_topic OK
mysql.host OK
mysql.innodb_index_stats OK
mysql.innodb_table_stats OK
mysql.ndb_binlog_index OK
mysql.plugin OK
mysql.proc OK
mysql.procs_priv OK
mysql.proxies_priv OK
mysql.server_cost OK
mysql.servers OK
mysql.slave_master_info OK
mysql.slave_relay_log_info OK
mysql.slave_worker_info OK
mysql.slow_log OK
mysql.tables_priv OK
mysql.time_zone OK
mysql.time_zone_leap_second OK
mysql.time_zone_name OK
mysql.time_zone_transition OK
mysql.time_zone_transition_type OK
mysql.
user
OK
Upgrading the sys
schema
.
Checking databases.
sys.sys_config OK
zx.test_upgrade OK
Upgrade process completed successfully.
Checking if
update
is
needed.
|
7、重启MySQL，并验证
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
|
[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软件。
本文转自hbxztc 51CTO博客，原文链接：http://blog.51cto.com/hbxztc/1909466，如需转载请自行联系原作者
1140
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
