MySQL 升级--1

在生产环境中，由于业务需要和安全我们需要进行mysql的升级，今天我们先来升级小版本从mysql5.7.23-升级到5.7.39。

升级前确认：

1、没有用户连接mysql 数据库

2、全量备份（可以进行异机备份）

 show processlist;     #查看 是否还有用户连接

mysqldump -u root -p --all-databases   --max_allowed_packet=512M  >/opt/a.sql 

一、解压安装最新版本数据库（5.7.39）

tar -zxvf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz -C /data/

cd /data/

mv mysql-5.7.39-linux-glibc2.12-x86_64 mysql

chown -R mysql. /data/mysql/

cp /etc/my.cnf{,.bak}  #备份my.cnf

vim /etc/my.cnf   #编辑并更改
[mysql]
default-character-set=utf8
socket=/data/mysql/mysql.sock
[mysqld]
basedir=/data/mysql
datadir=/opt/mysql/data
socket=/data/mysql/mysql.sock
port=3306
character-set-server=utf8
default-time-zone='+08:00'
default-storage-engine=INNODB
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
# Settings user and group are ignored when systemd is used.
# If you need to run mysqld under a different user or group,
# customize your systemd unit file for mariadb according to the
# instructions in http://fedoraproject.org/wiki/Systemd

lower_case_table_names=1
[mysqld_safe]
log-error=/data/mysql/error.log
pid-file=/opt/mysql/data/mysql.pid

#
# include all files from the config directory
#
!includedir /etc/my.cnf.d

# /opt/mysql  是mysql5.7.23版本，/data/mysql/ 是5.7.39版本。处理数据目录（data）使用原先的其他的更换最新目录

二、停止 mysql5.7.23版本的数据库

[root@localhost data]# /etc/init.d/mysqld status
 SUCCESS! MySQL running (1376)
[root@localhost data]# /etc/init.d/mysqld stop
Shutting down MySQL.. SUCCESS!
[root@localhost data]# lsof -i:3306
[root@localhost data]# ps aux | grep mysql

三、初始化并启动新数据

 ./bin/mysqld  --initialize --user=mysql --basedir=/data/mysql/ --datadir=/data/mysql/data --log-error=/data/mysql/error.log --pid-file=/data/mysql/data/mysql.pid

vim /etc/init.d/mysqld   

basedir=/data/mysql
mysqld_pid_file_path=/opt/mysql/data/mysql.pid

mysqld 里面主要是不动datadir参数，my.cnf 一样也是用原先的数据目录。

/etc/init.d/mysqld start  #启动成功
Starting MySQL...... SUCCESS!


 mysql]# mysql -u root -p   登录成功
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysqldump -u root -p --all-databases   --max_allowed_packet=512M  >/opt/a.sql

执行备份命令，无误则升级完成。

四、回退

回退的主要过程就是

停数据库---将/etc/init.d/mysqld 里面和 my.cnf 内容还原---启动即可。

小版本的升级整个过程并不复杂