2. Mysql 升级与升级后 mysql --version 和 select version() 不一致问题

最新推荐文章于 2023-05-19 10:04:58 发布

终回首

最新推荐文章于 2023-05-19 10:04:58 发布

阅读量3.5k

点赞数 4

分类专栏： Database 文章标签： mysql

本文链接：https://blog.csdn.net/qq_39945938/article/details/109658540

版权

Database 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

一、问题

版本：
原版本：mysql 5.7.17
目标版本：mysql 5.7.32

一个项目中mysql在过漏洞检查时，发现版本过低，需要打补丁或者升级到更高版本。
于是开始用在线升级的方式开始升级。升级后执行mysql --version或者 mysql -V发现仍旧是原本的版本。

注意：

只有在线升级遇到了这个问题，离线升级是没问题的。这个问题其实是漏掉了客户端升级导致的，再升级一下客户端即可
本文的升级操作只在小版本间验证过，未在大版本升级验证过

mysql -V
mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper
mysql --version
mysql  Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using  EditLine wrapper

试着mysql -u user -p 登录后用select version()和show variables like '%version%'查看却已经是升级后的版本了。

select version();
+-----------+
| version() |
+-----------+
| 5.7.32    |
+-----------+


show variables like '%version%';
+-------------------------+------------------------------+
| Variable_name           | Value                        |
+-------------------------+------------------------------+
| innodb_version          | 5.7.32                       |
| protocol_version        | 10                           |
| slave_type_conversions  |                              |
| tls_version             | TLSv1,TLSv1.1,TLSv1.2        |
| version                 | 5.7.32                       |
| version_comment         | MySQL Community Server (GPL) |
| version_compile_machine | x86_64                       |
| version_compile_os      | Linux                        |
+-------------------------+------------------------------+

升级操作如下

0.备份mysql数据和配置文件

备份数据

mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > mysql_data_for_bak.sql

备份配置文件

cp /etc/my.cnf /etc/my.cnf.bak

设置关闭时清空缓存

mysql -u root -p --execute="SET GLOBAL innodb_fast_shutdown=0"

关闭数据库

service mysqld stop

（一）在线升级（前提：服务器可以联外网）

这种升级步骤少一点

1.下载安装yum源

cd /usr/local/src
wget https://repo.mysql.com//mysql57-community-release-el7-11.noarch.rpm
rpm -ivh mysql57-community-release-el7-11.noarch.rpm
cat /etc/yum.repos.d/mysql-community.repo



[mysql-connectors-community]
name=MySQL Connectors Community
baseurl=http://repo.mysql.com/yum/mysql-connectors-community/el/7/$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/7/$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/7/$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/7/$basearch/
enabled=0
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-mysql

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

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

发现默认已经有5.7，且5.7是enabled=1。

yum repolist all|grep mysql


mysql-connectors-community/x86_64  MySQL Connectors Community    enabled:    175
mysql-connectors-community-source  MySQL Connectors Community -  disabled
mysql-tools-community/x86_64       MySQL Tools Community         enabled:    120
mysql-tools-community-source       MySQL Tools Community - Sourc disabled
mysql-tools-preview/x86_64         MySQL Tools Preview           disabled
mysql-tools-preview-source         MySQL Tools Preview - Source  disabled
mysql55-community/x86_64           MySQL 5.5 Community Server    disabled
mysql55-community-source           MySQL 5.5 Community Server -  disabled
mysql56-community/x86_64           MySQL 5.6 Community Server    disabled
mysql56-community-source           MySQL 5.6 Community Server -  disabled
mysql57-community/x86_64           MySQL 5.7 Community Server    enabled:    464
mysql57-community-source           MySQL 5.7 Community Server -  disabled

发现5.7已经激活

2.执行升级命令

升级命令

yum -y update mysql-server

报错

Transaction check error:
  file /usr/share/mysql/czech/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/danish/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/dutch/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/english/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/estonian/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/french/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/german/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/greek/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/hungarian/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/italian/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/japanese/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/korean/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/norwegian-ny/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/norwegian/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/polish/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/portuguese/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/romanian/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/russian/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/serbian/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/slovak/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/spanish/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/swedish/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/ukrainian/errmsg.sys from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/Index.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/armscii8.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/ascii.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/cp1250.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/cp1251.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/cp1256.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/cp1257.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/cp850.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/cp852.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/cp866.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/dec8.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/geostd8.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/greek.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/hebrew.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/hp8.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/keybcs2.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/koi8r.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/koi8u.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/latin1.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/latin2.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/latin5.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/latin7.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/macce.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/macroman.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /usr/share/mysql/charsets/swe7.xml from install of mysql-community-common-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64
  file /etc/my.cnf from install of mysql-community-server-5.7.32-1.el7.x86_64 conflicts with file from package mariadb-libs-1:5.5.56-2.el7.x86_64

Error Summary

移除mariadb

报错原因，要升级的包和mariadb冲突；移除mariadb，再次执行升级命令

sudo rpm -qa | grep mariadb
mariadb-libs-5.5.56-2.el7.x86_64
sudo rpm -e mariadb-libs-5.5.56-2.el7.x86_64

yum -y update mysql-server

根据日志看已经成功

Install             ( 1 Dependent package)
Upgrade  1 Package

Total size: 174 M
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Warning: RPMDB altered outside of yum.
** Found 1 pre-existing rpmdb problem(s), 'yum check' output follows:
mysql-community-libs-5.7.17-1.el7.x86_64 has missing requires of mysql-community-common(x86-64) >= ('0', '5.7.9', None)
  Installing : mysql-community-common-5.7.32-1.el7.x86_64                                                                                                                                                                   1/3 
  Updating   : mysql-community-server-5.7.32-1.el7.x86_64                                                                                                                                                                   2/3 
  Cleanup    : mysql-community-server-5.7.17-1.el7.x86_64                                                                                                                                                                   3/3 
  Verifying  : mysql-community-server-5.7.32-1.el7.x86_64                                                                                                                                                                   1/3 
  Verifying  : mysql-community-common-5.7.32-1.el7.x86_64                                                                                                                                                                   2/3 
  Verifying  : mysql-community-server-5.7.17-1.el7.x86_64                                                                                                                                                                   3/3 

Dependency Installed:
  mysql-community-common.x86_64 0:5.7.32-1.el7                                                                                                                                                                                  

Updated:
  mysql-community-server.x86_64 0:5.7.32-1.el7                                                                                                                                                                                  

Complete!

启动mysql，检查并更新兼容问题

service mysqld start
mysql_upgrade -uroot -p

（二）离线升级（服务器不需要联网）

1.下载要升级到的对应版本的包

oracle官方下载地址：https://dev.mysql.com/downloads/mysql/5.7.html
这里我要升级到mysql5.7.32所以下载的是5.7.32的包

wget https://cdn.mysql.com//Downloads/MySQL-5.7/mysql-5.7.32-1.el7.x86_64.rpm-bundle.tar

2.解压下载好的包

tar -xvf mysql-5.7.32-1.el7.x86_64.rpm-bundle.tar

# ls
mysql-5.7.32-1.el7.x86_64.rpm-bundle.tar                 mysql-community-embedded-devel-5.7.32-1.el7.x86_64.rpm
mysql-community-client-5.7.32-1.el7.x86_64.rpm           mysql-community-libs-5.7.32-1.el7.x86_64.rpm
mysql-community-common-5.7.32-1.el7.x86_64.rpm           mysql-community-libs-compat-5.7.32-1.el7.x86_64.rpm
mysql-community-devel-5.7.32-1.el7.x86_64.rpm            mysql-community-server-5.7.32-1.el7.x86_64.rpm
mysql-community-embedded-5.7.32-1.el7.x86_64.rpm         mysql-community-test-5.7.32-1.el7.x86_64.rpm
mysql-community-embedded-compat-5.7.32-1.el7.x86_64.rpm

3.找到之前安装过的mysql的所有包

# rpm -qa | grep mysql
mysql-community-common-5.7.17-1.el7.x86_64
mysql-community-client-5.7.17-1.el7.x86_64
mysql-community-libs-compat-5.7.17-1.el7.x86_64
mysql-community-libs-5.7.17-1.el7.x86_64
mysql-community-server-5.7.17-1.el7.x86_64

这些就是之前安装过的mysql包，对应的要升级的包如下所示

mysql-community-common-5.7.32-1.el7.x86_64.rpm
mysql-community-client-5.7.32-1.el7.x86_64.rpm
mysql-community-libs-compat-5.7.32-1.el7.x86_64.rpm
mysql-community-libs-5.7.32-1.el7.x86_64.rpm
mysql-community-server-5.7.32-1.el7.x86_64.rpm

4.创建一个文件夹,拷贝要升级的包到创建好的文件夹下

mkdir mysql_pkg
cp /opt/tmp/{mysql-community-common-5.7.32-1.el7.x86_64.rpm,mysql-community-client-5.7.32-1.el7.x86_64.rpm,mysql-community-libs-compat-5.7.32-1.el7.x86_64.rpm,mysql-community-libs-5.7.32-1.el7.x86_64.rpm,mysql-community-server-5.7.32-1.el7.x86_64.rpm} ./mysql_pkg

5.执行升级命令

cd mysql_pkg
rpm -Uvh ./*.rpm

6.查看版本

已经升级完毕

# mysql --version
mysql  Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using  EditLine wrapper
# mysqld --version
mysqld  Ver 5.7.32 for Linux on x86_64 (MySQL Community Server (GPL))

二、解决

经过研究发现mysql --version或者 mysql -V查看的是mysql客户端的版本，而select version()和show variables like '%version%'还有 mysqld --version查看的是mysql服务端的版本

两个不一致是因为上面的yum update mysql-server只升级了mysql服务端，所以再升级一下mysql客户端即可

1.执行升级客户端命令

yum -y update mysql

升级后再执行mysql --version或者 mysql -V

mysql --version
mysql  Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using  EditLine wrapper
mysql -V
mysql  Ver 14.14 Distrib 5.7.32, for Linux (x86_64) using  EditLine wrapper

发现已经和select version()和show variables like '%version%'还有 mysqld --version所查出来的版本一致了

终回首

关注

4
点赞
踩
5

收藏

觉得还不错? 一键收藏
2
评论
2. Mysql 升级与升级后 mysql --version 和 select version() 不一致问题

一、问题一个项目中mysql在过漏洞检查时，发现版本过低，需要打补丁或者升级到更高版本。于是开始升级。升级执行mysql --version 或者 mysql -V 发现仍旧是原本的版本mysql -Vmysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using EditLine wrappermysql --versionmysql Ver 14.14 Distrib 5.7.17, for Linux (x86_64) using
复制链接

扫一扫