mysql版本降级

原文链接：https://www.cpweb.top/908

一、降级注意事项

  -  仅支持GA版本之间升级。
  -  从MySQL 5.7降级到5.6只能使用logical降级方法，不能使用In-place方式降级。
  -  不支持跳版本降级。例如，不支持直接从5.7降级到5.5。
  -  对于同一个发行版系列中，如5.7.y → 5.7.x 进行降级时，才支持In-place就地降级。

  接下来我们来进行降级演示，因为是模拟环境，数据库中没有数据，所以不做备份，以下只演示降级操作。实际环境还需谨慎谨慎再谨慎。
 

二、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' ;                             
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>