Centos7 Mysql 5.7系列升级版本过程
前言
在操作系统为centos的生产环境服务器需要进行等保2.0评测,使用绿盟科技的扫描软件扫描后检测出一大批漏洞,因此需要进行漏洞修复虽然报告中有一大堆的漏洞,但是细分下来分为mysql数据库方面
Mysql的漏洞可直接通过升级Mysql最新版本解决,以下通过模拟Mysql低版本5.7.28 到高版本 5.7.31 的升级做演示
安装Mysql 5.7.28 版本
1. 官网获取Mysql-5.7.28 的包.
Mysql5.7.28-Centos7 rpm 包下载地址: https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
2. 上传到Centos7中的/opt 路径
3. 编写一键安装脚本
vim mysql5.7.28_install.sh#!/bin/bash#RPM包位置https://dev.mysql.com/downloads/mysql/#选择【Looking for the latest GA version?】#操作系统选择Red Hat Enterprise Linux /Oracle Linux#选择第一个500多MB的tar包echo "================================"echo "正在卸载Mysql"echo "======================================"rpm -e $(rpm -qa|grep -i mysql) --nodepsrpm -e $(rpm -qa|grep -i maria) --nodepsrpm -e $(rpm -qa|grep -i postfix) --nodepsecho "================================"echo "正在安装Mysql"echo "======================================"mkdir mysql5.7.28tar -xvf mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar -C mysql5.7.28 && cd mysql5.7.28rpm -ivh mysql-community-common-5.7.28-1.el7.x86_64.rpmrpm -ivh mysql-community-libs-5.7.28-1.el7.x86_64.rpmrpm -ivh mysql-community-libs-compat-5.7.28-1.el7.x86_64.rpmrpm -ivh mysql-community-embedded-compat-5.7.28-1.el7.x86_64.rpmrpm -ivh mysql-community-devel-5.7.28-1.el7.x86_64.rpmrpm -ivh mysql-community-client-5.7.28-1.el7.x86_64.rpmrpm -ivh mysql-community-server-5.7.28-1.el7.x86_64.rpmecho "=========Mysql安装成功============================="systemctl start mysqld && systemctl enable mysqldnetstat -anp|grep 3306cat /var/log/mysqld.log | grep 'temporary password'echo "第一次登陆修改root密码的语句是:"echo "ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1qe3!QE#';"echo "创建远程用户的语句是:"echo "grant all privileges on *.* to 'root'@"%" identified by '1qe3!QE#' with grant option;"#grant all privileges on *.* to 'sdata1'@"%" identified by '1qe3!QE#' with grant option;echo "创建新用户"
4. 执行脚本
sh mysql5.7.28_install.sh
5. 登陆数据库并修改账号密码
mysql -uroot -p#输入临时密码然后回车ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '1qe3!QE#'; #修改本地管理员密码grant all privileges on *.* to 'root'@'%' identified by '1qe3!QE#' with grant option; #创建远程管理员,带grant权限
6. 迁移Mysql存储目录
systemctl stop mysqldmkdir /data_lvmmv /var/lib/mysql/ /data/lvm/
迁移Mysql存储目录后可以删除/var/lib/mysql 但由于配置没有改socket的位置,因此如果删除了/var/lib/mysql目录导致mysqld无法启动
方法一:需要执行
mkdir /var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
方法二:修改socket默认路径
[mysqld]
socket=/data_lvm/mysql/mysql.sock
[mysql]
socket=/data_lvm/mysql/mysql.sock #不加的话使用本地的mysql命令登陆会报错
7. 修改数据库配置,与生产环境一致
datadir=/data_lvm/mysqlsocket=/var/lib/mysql/mysql.sock# Disabling symbolic-links is recommended to prevent assorted security riskssymbolic-links=0log-error=/var/log/mysqld.logpid-file=/var/run/mysqld/mysqld.pid# szyd need to setsql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTIONcharacter-set-server=utf8mb4collation-server=utf8mb4_general_cilower_case_table_names=1#sql_policeplugin-load=validate_password.sovalidate_password_policy=2validate_password_length=8validate_password_mixed_case_count=1validate_password_number_count=2validate_password_special_char_count=1validate-password=FORCE_PLUS_PERMANENT#WO-19 ser max_connectionmax_connections=1000#WO20 set wait-timeoutwait_timeout=28800# start bin-loginit-connect=insert into accesslog.accesslog(id,time,localname,matchname) values (connection_id(),now(),user(),current_user())log_bin=/data_lvm/mysqllog/mysql_binbinlog-format=Rowserver-id=1log-bin-trust-function-creators=1#set connection-controlplugin-load-add=connection_control.soconnection-control=FORCEconnection-control-failed-login-attempts=FORCEconnection_control_min_connection_delay=1000connection_control_max_connection_delay=86400connection_control_failed_connections_threshold=3#set slowslow_query_log=onslow_query_log_file=/var/lib/mysql/mysql-slow.log#log_query_time=1[mysql]#sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
8. 同步数据
备份Mysql
备份的目的是避免数据丢失,一定要备份好才接下来操作。
- 备份配置
- 逻辑备份
- 物理备份(数据卷,日志)
查看/etc/my.cnf,然后进行备份/etc/my.cnf 和Mysql存储目录(生产环境任何操作一定要先备份,在操作)
1. 备份配置文件
cp /etc/my.cnf /home/cgt/mysqlbak/my.cnf
2. 备份逻辑文件
mysqldump -uroot -p --all-databases > all.sql #全库备份mysqldump -uroot -p --databases XXX > XXX.sql #业务库备份
3. 停机后备份完整的数据文件
systemctl stop msyqld #暂停Mysql tar -zcvf mysqlbak.tar.gz /var/lib/mysql # 数据卷归档
升级Mysql到5.7.31
1. 获取最新版本
上mysql官网,下载最新版的rpm包。登陆 https://dev.mysql.com/downloads/mysql/ 网站默认是Mysql 8最新版本的 如果是用Mysql5.7的,点击下图红色框框,
根据自己的操作系统和位数进行选择,这里是Centos7 64位。
截至 2020/09/21,官网上 Mysql5.7 系列最新的版本为 5.7.31
下载地址:https://dev.mysql.com/get/Downloads/MySQL-5.7/mysql-5.7.28-1.el7.x86_64.rpm-bundle.tar
2. 上传tar包到centos7的/opt 目录
3. 编写Mysql一键升级脚本
vim mysql5.7.31_upgrade.sh
这里我们使用先卸载的方法,RPM包虽然可以直接 rpm -Uvh 升级,但mysql中有6个rpm包,相互依赖,升级会报依赖的问题。
#!/bin/bash#rpm包安装的服务使用rpm -Uvh 升级mkdir mysql5.7.31tar xvf mysql-5.7.31-1.el7.x86_64.rpm-bundle.tar -C mysql5.7.31 && cd mysql5.7.31rpm -Uvh mysql-community-common-5.7.29-1.el7.x86_64.rpmrpm -Uvh mysql-community-libs-5.7.29-1.el7.x86_64.rpmrpm -Uvh mysql-community-devel-5.7.29-1.el7.x86_64.rpmrpm -Uvh mysql-community-libs-compat-5.7.29-1.el7.x86_64.rpmrpm -Uvh mysql-community-client-5.7.29-1.el7.x86_64.rpmrpm -Uvh mysql-community-server-5.7.29-1.el7.x86_64.rpm#还原本来的my.cnfmv /etc/my.cnf /etc/my.cnf.bak && mv /etc/my.cnf.rpmsave /etc/my.cnf#重新启动Mysql并查询进程systemctl restart mysqldnetstat -anp|grep 3306
4. 执行脚本,一键更新
sh mysql5.7.31_upgrade.sh
5. 升级Mysql系统表
mysql_upgrade -uroot -p #密码和本来的一样#-s 参数是仅升级系统表的意思,避免数据库升级后无法打开一些系统表
6. 登陆数据库验证
mysql -uroot -p
这个时候,完成Mysql的版本更新,漏洞也随之消失了。