1、升级

1.1两种方式
INPLACE -->就地升级
在一台服务器上，原版本升级到新版本（风险较大）
建议：不管哪种方式升级，都应该做好数据备份
Mergeing(logical)迁移
备份迁移
主从迁移

1.2升级注意事项

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. 降低停机时间（停业务的时间）,在业务不繁忙期间升级，做好足够的预演。

5.6.46 ----> 5.7.28 Inplace 升级演练

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
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

[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.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

[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、降级

5.7.28 ---> 5.7.10 inplace downgrade演练

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

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

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

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

3.2.4. 删除ib_logfile*

[root@db01 mysql5710]# rm -rf /data/3306/data/ib_logfile* #删除高版本格式的日志

3.2.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

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

[root@db01 ~ 21:36:54]# /data/app/mysql5710/bin/mysqld  --skip-grant-tables --skip-networking &

3.2.7. 执行upgrade

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

3.2.8 启动到正常模式

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

登录测试，是否降到5.7.10

[root@db01 ~ 21:36:54]# /data/app/mysql5710/bin/mysql -uroot -p123456 -S /tmp/mysql.sock

5.7.28 ---> 5.6.46 inplace downgrade演练

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

3.3.2 处理5.7.28高版本数据

[root@db01 ~ 22:14:16]# mysql

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;

3.3.3. 逻辑全备5.7.28数据

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

3.3.4. 初始化一套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
[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

3.3.5. 恢复备份数据到5.6.46中

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