一、降级注意事项
注意事项：
- 仅支持GA版本之间升级。
- 从MySQL 5.7降级到5.6只能使用logical降级方法，不能使用In-place方式降级。
- 不支持跳版本降级。例如，不支持直接从5.7降级到5.5。
- 对于同一个发行版系列中，如5.7.y → 5.7.x 进行降级时，才支持In-place就地降级。官方文档：https://dev.mysql.com/doc/refman/5.7/en/downgrading.html
接下来我们来进行降级演示，因为是模拟环境，数据库中没有数据，所以不做备份。
实际环境还需谨慎谨慎再谨慎。
二、5.7.28 → 5.7.10
现有5.7.28版本数据库，需要降级到5.7.10。其数据库软件目录为/data/app/mysql，数据目录为/data/3306/data。
1、安装5.7.10数据库
[root@db01 ~]# tar xf mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz -C /data/app/
2、降级之前需要对5.7.28数据库执行的操作
官方文档：https://dev.mysql.com/doc/refman/5.7/en/downgrading-to-previous-series.html
降级到不同版本，做的操作也不相同，可以查看官方文档来获知需要做的操作。
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、优雅关闭原数据库
[root@db01 ~]# vim /etc/my.cnf //添加innodb那个配置，即关闭数据库时将未写入数据全部写入落盘
[mysqld]
user=mysql
basedir=/data/app/mysql
datadir=/data/3306/data
socket=/tmp/mysql.sock
innodb_fast_shutdown=0
[root@db01 ~]# /data/app/mysql/support-files/mysql.server stop
4、删除ib_logfile*
[root@db01 ~]# rm -rf /data/3306/data/ib_logfile* //不同版本日志格式可能不同
5、使用底版本软件去加载高版本数据启动
[root@db01 app]# ll
total 0
lrwxrwxrwx. 1 mysql mysql 35 Oct 14 08:35 mysql -> mysql-5.7.28-linux-glibc2.12-x86_64
drwxr-xr-x. 9 mysql mysql 151 Nov 29 2015 mysql-5.7.10-linux-glibc2.5-x86_64
drwxr-xr-x. 9 mysql mysql 129 Oct 14 08:35 mysql-5.7.28-linux-glibc2.12-x86_64
[root@db01 app]# rm -f mysql
[root@db01 app]# ln -s mysql-5.7.10-linux-glibc2.5-x86_64 mysql
[root@db01 app]# ll
total 0
lrwxrwxrwx. 1 root root 34 Oct 15 06:56 mysql -> mysql-5.7.10-linux-glibc2.5-x86_64
drwxr-xr-x. 9 mysql mysql 151 Nov 29 2015 mysql-5.7.10-linux-glibc2.5-x86_64
drwxr-xr-x. 9 mysql mysql 129 Oct 14 08:35 mysql-5.7.28-linux-glibc2.12-x86_64
[root@db01 app]# mkdir -p /usr/local/mysql/bin
[root@db01 app]# ln -s /data/app/mysql/bin/mysqld /usr/local/mysql/bin/mysqld
[root@db01 app]# /data/app/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf --skip-grant-tables --skip-networking &
6、降级
[root@db01 app]# /data/app/mysql/bin/mysql_upgrade --force
7、重启数据库到正常状态
[root@db01 app]# /data/app/mysql/support-files/mysql.server restart
8、连接测试
[root@db01 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.10 MySQL Community Server (GPL)
Copyright (c) 2000, 2015, 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.
mysql>
三、5.7.28 → 5.6.46
现有5.7.28版本数据库，需要降级到5.6.46。其数据库软件目录为/data/app/mysql，数据目录为/data/3306/data。
1、降级之前需要对5.7.28数据库执行的操作（以下大概是修改表结构、数据类型、引擎、默认值、字符长度等等符合5.6.46数据库）
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;
2、逻辑全备5.7.28数据
[root@db01 app]# mysqldump -uroot -p -A > /root/full.sql
[root@db01 app]# /data/app/mysql/support-files/mysql.server stop
3、部署mysql 5.6.46
[root@db01 app]# tar xf mysql-5.6.46-linux-glibc2.5-x86_64.tar.gz -C /data/app/
[root@db01 app]# rm -f mysql
[root@db01 app]# ln -s mysql-5.6.46-linux-glibc2.12-x86_64 mysql
[root@db01 app]# rm -rf /data/3306/data/*
[root@db01 app]# yum -y install autoconf
[root@db01 app]# /data/app/mysql/scripts/mysql_install_db --user=mysql --basedir=/data/app/mysql --datadir=/data/3306/data
4、启动
[root@db01 app]# /data/app/mysql/support-files/mysql.server start
5、将备份数据导入到5.6.46中重启
[root@db01 app]# mysql < /root/full.sql
[root@db01 app]# /data/app/mysql/support-files/mysql.server restart
6、连接测试
[root@db01 app]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.6.46 MySQL Community Server (GPL)
Copyright (c) 2000, 2019, 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.
mysql>