演练一：二进制版本5.7.28—->5.7.10
一、安装5.7.10版本软件
[root@db1 ~]# ll /data/app/mysql5710 -d
drwxr-xr-x 9 mysql mysql 151 Nov 30 2015 /data/app/mysql5710二、对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 '';三、优雅的关闭数据库
mysql> set global innodb_fast_shutdown=0;四、5.7.28删除log_ibfile*
[root@db01 ～]# rm -rf /data/3306/data/ib_logfile*五、替换配置文件、变量信息
[root@db01 ～]# vim /etc/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql5710
#basedir=/data/app/mysql56
datadir=/data/3306/data
socket=/tmp/mysql.sock
[mysql]
socket=/tmp/mysql.sock[root@db01 ～]# vim /etc/profile
export PATH=/data/app/mysql5710/bin:$PATH
[root@db01 ～]# source /etc/profile
[root@db01 ～]# mysql -V
mysql Ver 14.14 Distrib 5.7.10, for linux-glibc2.5 (x86_64) using EditLine wrapper六、启动数据库
[root@db01 ～]# /etc/init.d/mysqld start
Starting MySQL.. SUCCESS! 七、检查降级结果
[root@db01 ～]# mysql_upgrade --force 演练二：二进制版本5.7.28—>5.6.46
一、安装5.6.46二进制软件
[root@db1 ~]# ll /data/app/mysql56/ -d
drwxr-xr-x 13 mysql mysql 205 Apr 23 17:05 /data/app/mysql56/二、处理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;三、逻辑备份5.7.28数据
[root@db01 ~]# mysqldump -A >/tmp/full.sql四、初始化一套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/3316/data/*
[root@db01 data]# /data/app/mysql56/scripts/mysql_install_db --user=mysql --basedir=/data/app/mysql56 --datadir=/data/3316/data五、启动数据库
[root@db01 data]# systemctl start mysqld3316六、导入备份数据
[root@db01 data]# mysql -S /tmp/mysql3316.sock
mysql> source /tmp/full.sql
0 Comments