数据库运维
记录DBA学习成长历程
当前位置：MySQL学习笔记 > MySQL > 正文

10.MySQL升级流程

2023-04-03 分类：MySQL 阅读(746)
文章目录

1 安装5.6&5.7版本

tar xf mysql-5.6.50-linux-glibc2.12-x86_64.tar.gz
tar xf mysql-5.7.32-linux-glibc2.12-x86_64.tar.gz
ln -s /opt/mysql-5.6.50-linux-glibc2.12-x86_64 /usr/local/mysql56
ln -s /opt/mysql-5.7.32-linux-glibc2.12-x86_64 /usr/local/mysql57
mkdir -p /data/mysql56/data
mkdir -p /data/mysql57/data
chown -R mysql.mysql /data
cp /etc/my.cnf /etc/my56.cnf
cp /etc/my.cnf /etc/my57.cnf
cat /etc/my56.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql56
datadir=/data/mysql56/data
port=3356
socket=/tmp/mysql56.sock
cat /etc/my57.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql57
datadir=/data/3357/data
port=3357
socket=/tmp/mysql3357.sock

MySQL5.6数据初始化

/usr/local/mysql56/scripts/mysql_install_db --user=mysql
--basedir=/usr/local/mysql56 --datadir=/data/3356/data  

MySQL5.7 数据初始化

/usr/local/mysql57/bin/mysqld --defaults-file=/etc/my3357.cnf --initialize-insecure

启动脚本验证 

/usr/local/mysql57/bin/mysqld_safe --defaults-file=/etc/my57.cnf &
mysql -S /tmp/mysql57.sock 
Server version: 5.7.32 MySQL Community Server (GPL)
/usr/local/mysql56/bin/mysqld_safe --defaults-file=/etc/my56.cnf &
mysql -S /tmp/mysql56.sock
Server version: 5.6.50 MySQL Community Server (GPL)

2 INPLACE就地升级

在一台服务器上，原版本升级到新版本。

风险较大。除非是主从环境。

建议：不管哪种方式升级，都应该先做了冷备。方便失败回退。

3 Mergeing(logical)迁移升级

逻辑备份方式

主从方式

野路子：

strings /usr/local/mysql56/bin/mysqld | grep 5.6.50
sed -i 's/5.6.50/5.6.55/' /usr/local/mysql56/bin/mysqld

4 MySQL升级官方注意事项

Upgrade is only supported between General Availability (GA) releases.
Upgrade from MySQL 5.6 to 5.7 is supported. Upgrading to the latest release is
recommended before upgrading to the next version. For example, upgrade to the
latest MySQL 5.6 release before upgrading to MySQL 5.7.
Upgrade that skips versions is not supported. For example, upgrading directly
from MySQL 5.5 to 5.7 is not supported.
Upgrade within a release series is supported. For example, upgrading from MySQL
5.7.x to 5.7.y is supported. Skipping a release is also supported. For example,
upgrading from MySQL 5.7.x to 5.7.z is supported.

翻译：
支持GA版本之间升级
5.6 --> 5.7 ,先将5.6升级至最新版，再升级到5.7
5.5 --> 5.7 ,先将5.5 升级至最新，再5.5---> 5.6最新，再5.6--->5.7 最新
回退方案要提前考虑好，最好升级前要备份(特别是往8.0版本升级)。
降低停机时间（停业务的时间）,在业务不繁忙期间升级，做好足够的预演。

5 INPLACE升级流程 

1.安装新版本软件
2.关闭原数据库业务（挂维护页），并设置参数innodb_fast_shutdown=0，默认是1，innodb_fast_shutdown指完整关闭，备份原数据库数据（冷备）。
3.使用新版本软件 “挂” 旧版本数据启动(--skip-grant-tables ,--skip-networking)
4.升级只是升级系统表。升级时间和数据量无关的。
5.正常重启数据库。
6.验证各项功能是否正常。
7.业务恢复。
建议： inpalce升级最好是主从环境，先从库再主库。

5 MySQL5.6.50 升级5.7.32 Inplace演练

—5.1 安装5.7.32新版本软件不初始化

—5.2 停原库（5.6.50）

/usr/local/mysql56/bin/mysqld_safe --defaults-file=/etc/my56.cnf &
mysql -S /tmp/mysql56.sock
mysql> show processlist;
mysql> select concat('kill',id,";") from information_schema.processlist;
mysql>set global innodb_fast_shutdown=0;
/usr/local/mysql56/bin/mysqladmin -S /tmp/mysql56.sock shutdown

—5.3 冷备

mkdir -p /data/mysql56bak
cd /data/mysql56/data/
cp -ra * /data/mysql56bak/

—5.4 高版本挂低版本数据启动

/usr/local/mysql57/bin/mysqld_safe --defaults-file=/etc/my56.cnf --skip-grant-tables --skip-networking &
mysql -S /tmp/mysql56.sock

—5.5 升级

/usr/local/mysql57/bin/mysql_upgrade -S /tmp/mysql56.sock --force 
mysql -S /tmp/mysql56.sock

—5.6 升级完成重启数据库

mysql> shutdown ;
/usr/local/mysql57/bin/mysqld_safe --defaults-file=/etc/my3356.cnf &
mysql -S /tmp/mysql56.sock
>show databases;验证是否升级成功5.7相对于5.6有sys库

6 MySQL5.7.32升级至8.0.24演练 

—6.1安装8.0.24新版本软件

—6.2 新特性

1、 预检查：8.0以后新增mysql-shell工具，升级之前调用这个命令做预检查。

2、升级时不再需要手工 mysql_upgrade

3、限制：升级前必须要备份。否则无法回退。

—6.3 预检查

yum install -y mysql-shell-8.0.24-1.el7.x86_64.rpm
或者https://downloads.mysql.com/archives/ 下载上传解压
tar xf mysql-shell-8.0.24-linux-glibc2.12-x86-64bit.tar.gz
ln -s /opt/mysql-shell-8.0.24-linux-glibc2.12-x86-64bit /usr/local/mysqlsh
vim /etc/profile
export PATH=/usr/local/mysqlsh/bin:$PATH
source /etc/profile
/usr/local/mysql56/bin/mysqld_safe --defaults-file=/etc/my3356.cnf &
mysql -S /tmp/mysql57.sock
mysql> grant all on *.* to root@'10.0.0.%' identified by '123';
mysqlsh root@10.0.0.51:3357 -e "util.checkForServerUpgrade()" >/tmp/up.log
root为空密码的预检查命令：mysqlsh -S /tmp/mysql57.sock -e
"util.checkForServerUpgrade()"
Errors:   0
Warnings: 1
Notices:  1

—6.4 停原库（5.7.32）

mysql -S /tmp/mysql57.sock
mysql>set global innodb_fast_shutdown=0;
mysql>shutdown

—6.5 冷备

mkdir -p /data/mysql57bak
cd /data/mysql57/data/
cp -ra * /data/mysql57bak/

—6.6 高版本挂低版本数据启动升级

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my57.cnf --skip-grant-tables --skip-networking &
mysql -S /tmp/mysql57.sock

—6.7 升级完成重启数据库

/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my57.cnf &
mysql>restart;仅8.0支持restart，可验证是否升级成功 

7 MySQL5.7冷备回退5.6

cp /etc/my56.cnf /etc/my562.cnf
vim /etc/my562.cnf
datadir=/data/mysql56bak
port=33562
socket=/tmp/mysql562.sock
chown -R mysql.mysql /data/mysql56bak
/usr/local/mysql56/bin/mysql56/bin/mysqld_safe --defaults-file=/etc/my562.cnf &

8 降级

—8.1 限制

官方解释：
https://dev.mysql.com/doc/refman/5.7/en/downgrade-paths.html
Downgrade from MySQL 5.7 to 5.6 is supported using the logical downgrade method.
https://dev.mysql.com/doc/refman/5.7/en/downgrade-binary-package.html#downgrade
procedure-inplace
In-place downgrade is supported for downgrades between GA releases within the
same release series(5.7.y ---> 5.7.x)

—8.2 MySQL 5.7.30 降级至5.7.10 inplace downgrade演练

# 软件规划
原版本：
软件： 5.7.30 /usr/local/mysql + 数据：/data/3306/data
目标版本： 5.7.10 /usr/local/mysql5710

# 安装 5.7.10 （低） 二进制版本
[root@db01 app]# ln -s mysql-5.7.10-linux-glibc2.5-x86_64 mysql5710

# 针对5728版本（高）进行处理工作
https://dev.mysql.com/doc/refman/5.7/en/downgrading-to-previous-series.html
[root@db01 app]# cp /etc/my.cnf.bak /etc/my.cnf
[root@db01 app]# /etc/init.d/mysqld restart
[root@db01 app]# /usr/local/mysql/bin/mysql -uroot -p123456 -S /tmp/mysql.sock
set sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_
ENGINE_SUBSTITUTION' ;
set global
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_
ENGINE_SUBSTITUTION' ;
select @@sql_mode;
ALTER TABLE mysql.proc MODIFY definer char(77) CHARACTER SET utf8 COLLATE
utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.event MODIFY definer char(77) CHARACTER SET utf8 COLLATE
utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL
DEFAULT '';
ALTER TABLE mysql.procs_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL
DEFAULT '';

# 优雅的关闭5.7.30（高）。
[root@db01 app]# /usr/local/mysql/bin/mysql -uroot -p123456 -S /tmp/mysql.sock
set global innodb_fast_shutdown=0 ;
[root@db01 app]# /usr/local/mysql/bin/mysqladmin -uroot -p123456 shutdown

# 删除ib_logfile*
[root@db01 mysql5710]# rm -rf /data/3306/data/ib_logfile*

# 替换配置文件（替换成低版本）
[root@db01 mysql5710]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql5710
#basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

# 低版本启动高版本数据库
/usr/local/mysql5710/bin/mysqld --skip-grant-tables --skip-networking &

# 执行upgrade
[root@db01 ~]# /usr/local/mysql5710/bin/mysql_upgrade -uroot -p123456 --force

# 启动到正常模式
[root@db01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
/usr/local/mysql5710/bin/mysql -uroot -p123456 -S /tmp/mysql.sock

—8.3 MySQL 5.7.30 降级至5.6.46 logical downgrade演练

# 恢复5.7.30 环境
[root@db01 data]# pkill mysqld
[root@db01 data]# rm -rf /data/3306/data/*

# 恢复配置文件
[root@db01 data]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

# 恢复环境变量
vim /etc/profile
export PATH=/usr/local/mysql/bin:$PATH
[root@db01 data]# source /etc/profile
[root@db01 data]# mysql -V
mysql Ver 14.14 Distrib 5.7.30, for linux-glibc2.12 (x86_64) using EditLine
wrapper

# 初始化数据
mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --
datadir=/data/3306/data

# 启动数据库
[root@db01 data]# /etc/init.d/mysqld start
Starting MySQL.Logging to '/data/3306/data/db01.err'.
SUCCESS!
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.30 |
+-----------+
1 row in set (0.00 sec)

# 安装5.6.46二进制版本软件
略。

# 处理5.7.30高版本数据
set
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_
ENGINE_SUBSTITUTION' ;
set global
sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_
ENGINE_SUBSTITUTION' ;
select @@sql_mode;
ALTER TABLE mysql.proc MODIFY definer char(77) CHARACTER SET utf8 COLLATE
utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.event MODIFY definer char(77) CHARACTER SET utf8 COLLATE
utf8_bin NOT NULL DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL
DEFAULT '';
ALTER TABLE mysql.procs_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL
DEFAULT '';
ALTER TABLE mysql.tables_priv MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.columns_priv MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.user MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.db MODIFY User char(16) NOT NULL default '';
ALTER TABLE mysql.procs_priv MODIFY User char(16) binary DEFAULT '' NOT NULL;
ALTER TABLE mysql.user ADD Password char(41) character set latin1
collate latin1_bin NOT NULL default '' AFTER user;
UPDATE mysql.user SET password = authentication_string WHERE
LENGTH(authentication_string) = 41 AND plugin = 'mysql_native_password';
UPDATE mysql.user SET authentication_string = '' WHERE
LENGTH(authentication_string) = 41 AND plugin = 'mysql_native_password';
ALTER TABLE mysql.help_category ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_keyword ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_relation ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.help_topic ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_leap_second ENGINE='MyISAM'
STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_name ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_transition ENGINE='MyISAM'
STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.time_zone_transition_type ENGINE='MyISAM'
STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.plugin ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.servers ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
ALTER TABLE mysql.user MODIFY plugin CHAR(64) COLLATE utf8_bin
DEFAULT 'mysql_native_password';
DROP DATABASE sys;

# 逻辑全备5.7.30数据
[root@db01 ~]# mysqldump -A >/tmp/full.sql

# 初始化一套5.6.46的空环境
[root@db01 ~]# vim /etc/profile
export PATH=/usr/local/mysql56/bin:$PATH
[root@db01 ~]# source /etc/profile
[root@db01 ~]# mysql -V
mysql Ver 14.14 Distrib 5.6.46, for linux-glibc2.12 (x86_64) using EditLine
wrapper
[root@db01 ~]# mv /etc/my.cnf /etc/my.cnf.bak
mv: overwrite ‘/etc/my.cnf.bak’? y
[root@db01 data]# rm -rf /data/3317/data/*
[root@db01 data]# /usr/local/mysql56/scripts/mysql_install_db --user=mysql --
basedir=/usr/local/mysql56 --datadir=/data/3317/data
[root@db01 data]# vim /etc/systemd/system/mysqld3317.service
ExecStart=/usr/local/mysql56/bin/mysqld --defaults-file=/data/3317/my.cnf
[root@db01 data]# systemctl daemon-reload
[root@db01 data]# systemctl start mysqld3317

# 恢复备份数据到5.6.46中
[root@db01 data]# mysql -S /tmp/mysql3317.sock
mysql> source /tmp/full.sql
赞(0)
MySQL学习笔记 » 10.MySQL升级流程

近期文章

友情链接