MySQL 的升级、降级

1.升级

1.1 INPLACE就地

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

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

1.2 Mergeing(logical)迁移 ----> 建议

备份迁移 
主从迁移

1.3 升级注意事项

#官网解释
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.

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

1.4 INPLACE 升级过程原理 （生产思路）

5.6.46版本—>5.7.28Inplace 升级演练

a. 安装 新版本软件 略以安装
b. 停原库 （5.6.46）

# 1. 快速关库功能关闭(优雅关闭、干净的关闭)  
vim /data/3317/my.cnf 
## 添加以下配置
innodb_fast_shutdown=0 
[root@db01 data]# systemctl stop mysqld3317

c. 使用高版本软件挂低版本数据启动

[root@db01 data]# vim /data/3317/my.cnf 
[mysqld]
user=mysql
basedir=/data/app/mysql		#挂接5.7版本数据
datadir=/data/3317/data
socket=/tmp/mysql3317.sock		
port=3317
server_id=17
innodb_fast_shutdown=0

[root@db01 data]# /data/app/mysql/bin/mysqld_safe --defaults-file=/data/3317/my.cnf --skip-grant-tables --skip-networking &

d. 升级 （升级到8.0可以省略这个一步）

[root@db01 data]# /data/app/mysql/bin/mysql_upgrade -S /tmp/mysql3317.sock --force 

e. 重启数据库到正常状态

#刚刚是安全模式启动的进行升级
[root@db01 data]# /data/app/mysql/bin/mysqladmin -S /tmp/mysql3317.sock shutdown

#修改systemd配置
[root@db01 data]# vim /etc/systemd/system/mysqld3317.service 
# 修改以下内容 
ExecStart=/data/app/mysql/bin/mysqld --defaults-file=/data/3317/my.cnf

# 启动数据库
[root@db01 data]# systemctl start mysqld3317 
# 连接查看
[root@db01 data]# /data/app/mysql/bin/mysql -S /tmp/mysql3317.sock

将5.7 升级至 8.0

# 新特性： 
1、 mysql-shell工具，8.0以后，可以调用这个命令，升级之前的预检查。
例子：
[root@db01 ~]# mysqlsh root:123@10.0.0.51:3306 -e "util.checkForServerUpgrade()"
2、升级时不再需要手工 mysql_upgrade
3、限制：升级前必须要备份。否则无法回退。

5.7.28 升级至 8.0.18 版本演练
a. 下载 8.0.18 版本的 mysql-shell，并安装 。

#下载地址
https://downloads.mysql.com/archives/
[root@db01 app]# yum install -y mysql-shell-8.0.18-1.el7.x86_64.rpm

b. 创建连接用户

#删除不必要的其他用户，不然检测不过
[root@db01 data]# /data/app/mysql/bin/mysql -S /tmp/mysql3317.sock
mysql> grant all on *.* to root@'10.0.0.%' identified by '123';
mysql> drop user root@'127.0.0.1';
mysql> drop user root@'db01';
mysql> drop user ''@'db01';
mysql> drop user ''@'localhost';
mysql> drop user root@'::1';
mysql> select user,host from mysql.user;
+---------------+-----------+
| user          | host      |
+---------------+-----------+
| root          | 10.0.0.%  |
| mysql.session | localhost |
| mysql.sys     | localhost |
| root          | localhost |
+---------------+-----------+

c. 预 检查

[root@db01 data]# mysqlsh root:123@10.0.0.51:3317 -e "util.checkForServerUpgrade()" >/tmp/up.log

d. 停原库（5.7.28）

vim /data/3317/my.cnf 
## 添加以下配置
innodb_fast_shutdown=0 
[root@db01 data]# systemctl stop mysqld3317

e. 使用高版本软件挂低版本数据启动

[root@db01 data]# vim /data/3317/my.cnf 
[mysqld]
user=mysql
basedir=/data/app/mysql80	#挂载高版本
datadir=/data/3317/data
socket=/tmp/mysql3317.sock
port=3317
server_id=17
innodb_fast_shutdown=0

#安全模式启动
[root@db01 data]# /data/app/mysql80/bin/mysqld_safe --defaults-file=/data/3317/my.cnf --skip-grant-tables --skip-networking &

f. 重启数据库到正常状态

[root@db01 data]# /data/app/mysql80/bin/mysqladmin -S /tmp/mysql3317.sock shutdown

#配置systemd管理
[root@db01 data]# vim /etc/systemd/system/mysqld3317.service 
# 修改以下内容 
ExecStart=/data/app/mysql80/bin/mysqld --defaults-file=/data/3317/my.cnf
# 启动数据库
[root@db01 data]# systemctl start mysqld3317 
# 连接查看
[root@db01 data]# /data/app/mysql80/bin/mysql -S /tmp/mysql3317.sock

2.降级

2.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).

2.2、 5.7.28 —》 5.7.10 inplace downgrade演练

原版本：
软件： 5.7.28 /data/app/mysql + 数据：/data/3306/data
目标版本： 5.7.10 /data/app/mysql5710

1 .安装 5.7.10 （低） 二进制版本

[root@db01 app]# ln -s mysql-5.7.10-linux-glibc2.5-x86_64 mysql5710

2. 针对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]# /data/app/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 '';

3. 优雅的关闭5.7.28（高）

[root@db01 app]# /data/app/mysql/bin/mysql -uroot -p123456 -S /tmp/mysql.sock
set global innodb_fast_shutdown=0 ;

[root@db01 app]# /data/app/mysql/bin/mysqladmin  -uroot -p123456 shutdown

4. 删除原来高版本的日志文件ib_logfile*

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

5. 替换配置文件（替换成低版本）

[root@db01 mysql5710]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql5710
#basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

6. 低版本启动高版本数据库

/data/app/mysql5710/bin/mysqld  --skip-grant-tables --skip-networking &

执行upgrade

[root@db01 ~]# /data/app/mysql5710/bin/mysql_upgrade -uroot -p123456 --force

8. 启动到正常模式

[root@db01 ~]# /etc/init.d/mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

/data/app/mysql5710/bin/mysql -uroot -p123456 -S /tmp/mysql.sock

2.3、5.7.28 —> 5.6.46logical downgrade演练

1.跨大版本降级(5.7.28环境准备)

恢复5.7.28 环境

[root@db01 data]# pkill mysqld
[root@db01 data]# rm -rf /data/3306/data/*

恢复配置文件

[root@db01 data]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock

恢复环境变量

vim /etc/profile 
export PATH=/data/app/mysql/bin:$PATH
[root@db01 data]# source /etc/profile

[root@db01 data]# mysql -V
mysql  Ver 14.14 Distrib 5.7.28, for linux-glibc2.12 (x86_64) using  EditLine wrapper

初始化数据

mysqld --initialize-insecure --user=mysql --basedir=/data/app/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.28    |
+-----------+
1 row in set (0.00 sec)

2. 安装5.6.46二进制版本软件（略)

3.处理5.7.28高版本数据

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;

4.逻辑全备5.7.28数据

[root@db01 ~]# mysqldump -A >/tmp/full.sql

5. 初始化一套5.6.46的空环境

[root@db01 ~]# vim /etc/profile
export PATH=/data/app/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]# /data/app/mysql56/scripts/mysql_install_db  --user=mysql --basedir=/data/app/mysql56 --datadir=/data/3317/data

#配置system的管理
[root@db01 data]# vim /etc/systemd/system/mysqld3317.service 
ExecStart=/data/app/mysql56/bin/mysqld --defaults-file=/data/3317/my.cnf

[root@db01 data]# systemctl daemon-reload
[root@db01 data]# systemctl start mysqld3317

6. 恢复备份数据到5.6.46中

[root@db01 data]# mysql -S /tmp/mysql3317.sock 
mysql> source /tmp/full.sql