MySQL的升级与降级

MySQL的升级与降级

微信搜索 zze_coding 或扫描 👉 二维码关注我的微信公众号获取更多资源推送:

升级

注意事项

  1. 仅支持 GA 版本之间升级;
  2. 5.6 到 5.7 ,先将 5.6 升级至最新版,再升级到 5.7;
  3. 5.5 到 5.7,先将 5.5 升级至最新,再从 5.5 升级到 5. 6 最新,再将 5.6 升级到 5.7 最新;
  4. 回退方案要提前考虑好,最好升级前要备份(特别是往 8.0 版本升级);

参考:https://dev.mysql.com/doc/refman/5.7/en/upgrade-paths.html

升级预检查

升级预检查仅支持目标版本为 MySQL 8.0 以后的升级。

1、要升级到哪个版本的 MySQL,就下载对应版本的 mysql-shell,点我下载 mysql-shell 安装包
2、这里以下载的 RPM 包安装为例,如下:

$ yum install mysql-shell-8.0.19-1.el7.x86_64.rpm -y

3、使用 mysqlsh 连接到要升级的低版本 MySQL,执行下面命令便会输出对应的升级建议:

$ mysqlsh root:123@127.0.0.1:3306 -e "util.checkForServerUpgrade()"

这里的 root:123 分别是用户名和密码,要保证执行 mysqlsh 命令的主机使用该用户能够正常连接到低版本的 MySQL 服务。

就地升级

就地升级指的是在一台服务器上,直接从原版本升级到新版本,风险较大。

升级流程:

  1. 安装新版本软件;
  2. 关闭原数据库(挂维护页);
  3. 使用新版本软件“挂”旧版本数据启动(--skip-grant-tables --skip-networking);
  4. 升级只是升级系统表。升级时间和数据量无关的;
  5. 正常重启数据库;
  6. 验证各项功能是否正常;
  7. 业务恢复;

建议,不管是哪种方式升级,都应该先做备份,方便失败回退。
目标:将已安装的 MySQL 5.6.46 升级到 5.7.28,下面操作同样适用于将 MySQL 5.7 升级到 8.0+,仅需省略第 5 步。
在执行下面操作之前,请参考 https://www.zze.xyz/archives/msyql-multi-instance.html 安装 MySQL 5.6.46 并启动服务。

1、安装新版本软件:

# 上传文件到 /data/app,解压
$ cd /data/app
$ tar xf mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
# 创建软链接
$ ln -s mysql-5.7.28-linux-glibc2.12-x86_64 mysql57

2、停原库:

# 关闭快速关库功能,添加如下配置
$ vim /data/3316/my.cnf
# 修改原来安装路径为新版本安装路径
basedir=/data/app/mysql57
# 安全关闭
innodb_fast_shutdown=0

$ systemctl stop mysqld3316

3、使用新软件挂旧版本数据库的数据启动:

$ /data/app/mysql57/bin/mysqld_safe --defaults-file=/data/3316/my.cnf --skip-grant-tables --skip-networking &

4、登录:

$ /data/app/mysql57/bin/mysql -S /tmp/mysql3316.sock      
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.28 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> 

5、执行升级操作(MySQL 5.7 升级到 8.0 忽略此部操作即可,其它操作相同):

$ /data/app/mysql57/bin/mysql_upgrade -S /tmp/mysql3316.sock --force
Checking if update is needed.
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
Upgrading the sys schema.
Checking databases.
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

6、关闭数据库:

$ /data/app/mysql57/bin/mysqladmin -S /tmp/mysql3316.sock -p shutdown 
Enter password: 123
2020-04-24T03:42:00.424000Z mysqld_safe mysqld from pid file /data/3316/data/mysql-51.pid ended
[1]+  Done                    /data/app/mysql/bin/mysqld_safe --defaults-file=/data/3316/my.cnf --skip-grant-tables --skip-networking

7、修改原来的 unit 文件以新版本的 mysqld 启动:

$ cat << EOF > /etc/systemd/system/mysqld3316.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql57/bin/mysqld --defaults-file=/data/3316/my.cnf
LimitNOFILE = 5000
EOF

8、重启数据库到正常状态:

$ systemctl restart mysqld3316

9、测试登录检查输出的版本号是否已是 5.7.28

$ /data/app/mysql57/bin/mysql -uroot -p123 -S /tmp/mysql3316.sock 
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.28 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> 

参考:https://dev.mysql.com/doc/refman/5.7/en/upgrade-binary-package.html

降级

注意事项

  1. 仅支持 GA 版本之间降级。
  2. 对于 MySQL 5.7 到 MySQL 5.6 仅支持逻辑降级;
  3. 不支持跳过版本的降级,例如,不支持直接从 MySQL 5.7 降级到 5.5;
  4. 仅支持在同发行系列中进行就地降级。例如,从 MySQL 5.7.y 降级 至5.7.x;

参考:https://dev.mysql.com/doc/refman/5.7/en/downgrade-paths.html

就地降级

目标:将已安装的 MySQL 5.7.28 降级到 5.7.10。
在执行下面操作之前,请参考 https://www.zze.xyz/archives/msyql-multi-instance.html 安装 MySQL 5.7.28 并启动服务。

1、调整 sql_mode 向下兼容:

-- 调整 sql_mode 向下兼容
mysql> set sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
mysql> set global sql_mode='STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION' ;
-- 检查是否设置成功
mysql> select @@sql_mode;

2、由于 5.7.13 版本以后用户表增加了部分字段的长度,所以降级时需要将长度调整为低版本兼容的长度:

mysql> ALTER TABLE mysql.proc MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
mysql> ALTER TABLE mysql.event MODIFY definer char(77) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL DEFAULT '';
mysql> ALTER TABLE mysql.tables_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT '';
mysql> ALTER TABLE mysql.procs_priv MODIFY Grantor char(77) COLLATE utf8_bin NOT NULL DEFAULT '';

对于降级到 MySQL 5.7.10,仅执行到这里也就是第 2 步就 OK 了,下一步请直接跳到第 8 步的优雅关闭及后续操作。

3、由于 5.7.8 版本以后,在授权表中 user 列的长度从 16 调整为 32 字符,所以要降级到 5.7.8 更低的版本,需要调整为 16 长度:

mysql> ALTER TABLE mysql.tables_priv MODIFY User char(16) NOT NULL default '';
mysql> ALTER TABLE mysql.columns_priv MODIFY User char(16) NOT NULL default '';
mysql> ALTER TABLE mysql.user MODIFY User char(16) NOT NULL default '';
mysql> ALTER TABLE mysql.db MODIFY User char(16) NOT NULL default '';
mysql> ALTER TABLE mysql.procs_priv MODIFY User char(16) binary DEFAULT '' NOT NULL;

4、在 5.7.6 版本以后,user 表中 password 字段已经取消,被替换为了 authentication_string,所以降级回来需要将其替换回来:

mysql> ALTER TABLE mysql.user ADD Password char(41) character set latin1
  collate latin1_bin NOT NULL default '' AFTER user;
mysql> UPDATE mysql.user SET password = authentication_string WHERE
  LENGTH(authentication_string) = 41 AND plugin = 'mysql_native_password';
mysql> UPDATE mysql.user SET authentication_string = '' WHERE
  LENGTH(authentication_string) = 41 AND plugin = 'mysql_native_password';

5、在 5.7.5 版本以后,有一些系统表的存储引擎从 MYISAM 到 INNODB,所以需要修改回去:

mysql> ALTER TABLE mysql.help_category ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
mysql> ALTER TABLE mysql.help_keyword ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
mysql> ALTER TABLE mysql.help_relation ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
mysql> ALTER TABLE mysql.help_topic ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
mysql> ALTER TABLE mysql.time_zone ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
mysql> ALTER TABLE mysql.time_zone_leap_second ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
mysql> ALTER TABLE mysql.time_zone_name ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
mysql> ALTER TABLE mysql.time_zone_transition  ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
mysql> ALTER TABLE mysql.time_zone_transition_type ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
mysql> ALTER TABLE mysql.plugin ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;
mysql> ALTER TABLE mysql.servers ENGINE='MyISAM' STATS_PERSISTENT=DEFAULT;

6、在 5.7 版本以后,user 表的 plugin 列的长度被修改了,这里要修改回去:

-- 如果要降级到 5.6.23 及其更高版本,执行下面操作
mysql> ALTER TABLE mysql.user MODIFY plugin CHAR(64) COLLATE utf8_bin
  DEFAULT 'mysql_native_password';
-- 如果要降级到 5.6.22 以前,则需要执行下面操作:  
mysql> ALTER TABLE mysql.user MODIFY plugin CHAR(64) COLLATE utf8_bin DEFAULT '';

7、如果要降级到 5.7.7 之前,则需要删除 sys 库:

mysql> DROP DATABASE sys;

8、优雅关闭:

-- 设定优雅关闭
mysql> set global innodb_fast_shutdown = 0;
mysql> select @@innodb_fast_shutdown;
-- 关闭数据库
mysql> shutdown;

9、旧版本的 logfile 可能和现有版本的不兼容,需要将数据目录的 logfile 删除让其重新生成:

# 进入数据存放目录,删除 ib_logfile
$ rm -rf ib_logfile*

10、上传 MySQL 5.7.10 的二进制安装包到 /data/app 目录,解压并创建软链接:

$ tar xf mysql-5.7.10-linux-glibc2.5-x86_64.tar.gz 
$ ln -s mysql-5.7.10-linux-glibc2.5-x86_64 mysql5710

11、修改原来高版本的配置文件将其安装目录指向旧版本:

$ vim /data/3307/my.cnf
[mysqld]
user=mysql
basedir=/data/app/mysql5710
datadir=/data/3307/data
socket=/tmp/mysql3307.sock
port=3307
server_id=7

12、启动目标旧版本数据库:

$ /data/app/mysql5710/bin/mysqld --defaults-file=/data/3307/my.cnf --skip-grant-tables --skip-networking &

13、执行降级操作:

$ /data/app/mysql5710/bin/mysql_upgrade --force -S /tmp/mysql3307.sock
Checking server version.
Running queries to upgrade MySQL server.
Checking system database.
mysql.columns_priv                                 OK
mysql.db                                           OK
mysql.engine_cost                                  OK
mysql.event                                        OK
mysql.func                                         OK
mysql.general_log                                  OK
mysql.gtid_executed                                OK
mysql.help_category                                OK
mysql.help_keyword                                 OK
mysql.help_relation                                OK
mysql.help_topic                                   OK
mysql.innodb_index_stats                           OK
mysql.innodb_table_stats                           OK
mysql.ndb_binlog_index                             OK
mysql.plugin                                       OK
mysql.proc                                         OK
mysql.procs_priv                                   OK
mysql.proxies_priv                                 OK
mysql.server_cost                                  OK
mysql.servers                                      OK
mysql.slave_master_info                            OK
mysql.slave_relay_log_info                         OK
mysql.slave_worker_info                            OK
mysql.slow_log                                     OK
mysql.tables_priv                                  OK
mysql.time_zone                                    OK
mysql.time_zone_leap_second                        OK
mysql.time_zone_name                               OK
mysql.time_zone_transition                         OK
mysql.time_zone_transition_type                    OK
mysql.user                                         OK
The sys schema is already up to date (version 1.5.2).
Checking databases.
sys.sys_config                                     OK
Upgrade process completed successfully.
Checking if update is needed.

14、停止 MySQL 进程,修改 unit 文件:

$ pkill mysqld
$ vim /etc/systemd/system/mysqld3307.service 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/data/app/mysql5710/bin/mysqld --defaults-file=/data/3307/my.cnf
LimitNOFILE = 5000

15、使用 systemd 启动 MySQL:

$ systemctl start mysqld3307

16、连接上检查版本:

$ mysql -uroot -p123 -S /tmp/mysql3307.sock 
...
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.10    |
+-----------+
1 row in set (0.00 sec)

降级成功~

逻辑降级

目标:将已安装的 MySQL 5.7.28 降级到 5.6.46。
在执行下面操作之前,请参考 https://www.zze.xyz/archives/msyql-multi-instance.html 安装 MySQL 5.7.28 并启动服务。

1、连接上 MySQL 5.7.28 执行下面 SQL:

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、逻辑备份 MySQL 5.7.28 的数据:

$ /data/app/mysql/bin/mysqldump -uroot -p123 -S /tmp/mysql3307.sock -A > /tmp/full.sql 
mysqldump: [Warning] Using a password on the command line interface can be insecure.

3、参考 https://www.zze.xyz/archives/msyql-multi-instance.html 安装 MySQL 5.6.46 并启动,然后恢复数据到 MySQL 5.6.46 中:

$ /data/app/mysql56/bin/mysql -uroot -p123 -S /tmp/mysql3316.sock < /tmp/full.sql                         
Warning: Using a password on the command line interface can be insecure.

参考:https://dev.mysql.com/doc/refman/5.7/en/downgrading-to-previous-series.html

Copyright: 采用 知识共享署名4.0 国际许可协议进行许可

Links: https://www.zze.xyz/archives/mysql的升级与降级.html

Buy me a cup of coffee ☕.