记一次CentOS6本地MySQL数据库在线升级(5.6.51->5.7.33)

一、升级前环境

升级时间:2021-03-30
MySQL数据库版本:5.6.51
操作系统版本:CentOS release 6.10 (Final)

如下图:

升级前版本

升级前repos(/etc/yum.repos.d/mysql-community.repo)文件如下:

[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

[mysql-tools-community]
name=MySQL Tools Community
baseurl=http://repo.mysql.com/yum/mysql-tools-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.5
[mysql55-community]
name=MySQL 5.5 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.5-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Enable to use MySQL 5.6
[mysql56-community]
name=MySQL 5.6 Community Server
baseurl=http://repo.mysql.com/yum/mysql-5.6-community/el/6/$basearch/
enabled=1
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

# Note: MySQL 5.7 is currently in development. For use at your own risk.
# Please read with sub pages: https://dev.mysql.com/doc/relnotes/mysql/5.7/en/
[mysql57-community-dmr]
name=MySQL 5.7 Community Server Development Milestone Release
baseurl=http://repo.mysql.com/yum/mysql-5.7-community/el/6/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:/etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

二、升级

1、修改repos文件

/etc/yum.repos.d/mysql-community.repo

将repos文件中MySQL5.6中的enabled=1改为enabled=0,将MySQL5.7中的enabled=0改为enabled=1

2、升级命令如下

yum update

执行结果如下:

[root@= /etc/yum.repos.d]# yum update
Loaded plugins: fastestmirror, refresh-packagekit, security
Setting up Update Process
Loading mirror speeds from cached hostfile
mysql-connectors-community                                                                                 | 2.6 kB     00:00     
mysql-tools-community                                                                                      | 2.6 kB     00:00     
mysql57-community-dmr                                                                                      | 2.6 kB     00:00     
mysql57-community-dmr/primary_db                                                                           | 257 kB     00:00     
Resolving Dependencies
--> Running transaction check
---> Package mysql-community-client.x86_64 0:5.6.51-2.el6 will be updated
---> Package mysql-community-client.x86_64 0:5.7.33-1.el6 will be an update
---> Package mysql-community-common.x86_64 0:5.6.51-2.el6 will be updated
---> Package mysql-community-common.x86_64 0:5.7.33-1.el6 will be an update
---> Package mysql-community-libs.x86_64 0:5.6.51-2.el6 will be updated
---> Package mysql-community-libs.x86_64 0:5.7.33-1.el6 will be an update
---> Package mysql-community-server.x86_64 0:5.6.51-2.el6 will be updated
---> Package mysql-community-server.x86_64 0:5.7.33-1.el6 will be an update
--> Finished Dependency Resolution

Dependencies Resolved

==================================================================================================================================
 Package                              Arch                 Version                      Repository                           Size
==================================================================================================================================
Updating:
 mysql-community-client               x86_64               5.7.33-1.el6                 mysql57-community-dmr                25 M
 mysql-community-common               x86_64               5.7.33-1.el6                 mysql57-community-dmr               367 k
 mysql-community-libs                 x86_64               5.7.33-1.el6                 mysql57-community-dmr               2.4 M
 mysql-community-server               x86_64               5.7.33-1.el6                 mysql57-community-dmr               162 M

Transaction Summary
==================================================================================================================================
Upgrade       4 Package(s)

Total download size: 189 M
Is this ok [y/N]: y
Downloading Packages:
(1/4): mysql-community-client-5.7.33-1.el6.x86_64.rpm                                                      |  25 MB     00:04     
(2/4): mysql-community-common-5.7.33-1.el6.x86_64.rpm                                                      | 367 kB     00:00     
(3/4): mysql-community-libs-5.7.33-1.el6.x86_64.rpm                                                        | 2.4 MB     00:00     
(4/4): mysql-community-server-5.7.33-1.el6.x86_64.rpm                                                      | 162 MB     00:24     
----------------------------------------------------------------------------------------------------------------------------------
Total                                                                                             6.3 MB/s | 189 MB     00:30     
Running rpm_check_debug
Running Transaction Test
Transaction Test Succeeded
Running Transaction
  Updating   : mysql-community-common-5.7.33-1.el6.x86_64                                                                     1/8 
  Updating   : mysql-community-libs-5.7.33-1.el6.x86_64                                                                       2/8 
  Updating   : mysql-community-client-5.7.33-1.el6.x86_64                                                                     3/8 
  Updating   : mysql-community-server-5.7.33-1.el6.x86_64                                                                     4/8 
  Cleanup    : mysql-community-server-5.6.51-2.el6.x86_64                                                                     5/8 
  Cleanup    : mysql-community-client-5.6.51-2.el6.x86_64                                                                     6/8 
  Cleanup    : mysql-community-libs-5.6.51-2.el6.x86_64                                                                       7/8 
  Cleanup    : mysql-community-common-5.6.51-2.el6.x86_64                                                                     8/8 
  Verifying  : mysql-community-common-5.7.33-1.el6.x86_64                                                                     1/8 
  Verifying  : mysql-community-libs-5.7.33-1.el6.x86_64                                                                       2/8 
  Verifying  : mysql-community-client-5.7.33-1.el6.x86_64                                                                     3/8 
  Verifying  : mysql-community-server-5.7.33-1.el6.x86_64                                                                     4/8 
  Verifying  : mysql-community-server-5.6.51-2.el6.x86_64                                                                     5/8 
  Verifying  : mysql-community-common-5.6.51-2.el6.x86_64                                                                     6/8 
  Verifying  : mysql-community-libs-5.6.51-2.el6.x86_64                                                                       7/8 
  Verifying  : mysql-community-client-5.6.51-2.el6.x86_64                                                                     8/8 

Updated:
  mysql-community-client.x86_64 0:5.7.33-1.el6                    mysql-community-common.x86_64 0:5.7.33-1.el6                   
  mysql-community-libs.x86_64 0:5.7.33-1.el6                      mysql-community-server.x86_64 0:5.7.33-1.el6                   

Complete!

查看版本:

[root@= /etc/yum.repos.d]# mysql -V
mysql  Ver 14.14 Distrib 5.7.33, for Linux (x86_64) using  EditLine wrapper
[root@= /etc/yum.repos.d]#

三、升级后使用Navicat连接报错:Error occurred: Cannot setup server variables.

解决

1、修改/etc/my.cnf

添加root用户登录信息,如下:


新增用户信息

2、重启mysql

# CentOS6
service mysqld restart
# CentOS7
systemctl restart mysqld

3、升级mysql

cd /usr/bin
mysql_upgrade -s -p # 执行后,输入root密码等待一会儿即可

如下图:


升级ing

4、登录测试

登录后查看数据库

再次使用Navicat连接就可以了!

后记

留存CentOS6.10的yum文件

/etc/yum.repos.d/CentOS-Base.repo如下

[base]
name=CentOS-6
failovermethod=priority
# 这个是官方的源 但是速度特别的慢,大概20kb吧,直接注释掉
#baseurl=https://vault.centos.org/6.10/os/x86_64/
# 搜狐源  网速200k左右,比较给力饿了
baseurl=http://mirrors.sohu.com/centos/6.10/os/x86_64/
gpgcheck=0

End


参考

推荐阅读更多精彩内容