MySQL数据库升级那些事

 [[410420]]

说起MySQL升级,相信很多人都在工作中遇到过.尤其是运维和DBA们.那么大家对MySQL的升级又有哪些了解呢?我们为什么要升级?我们怎么升级?

1 升级准备工作

官网文档介绍:https://dev.mysql.com/doc/refman/5.7/en/upgrade-before-you-begin.html 

2 升级注意事项

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

3 升级方式了解

官方文档介绍:https://dev.mysql.com/doc/refman/5.7/en/upgrade-binary-package.html 
解释:升级方式两种.一是In-Place Upgrade 二是Logical Upgrade 
In-Place升级原理: 
a.  安装新版本软件 
b.  关闭原数据库业务（挂维护页） innodb_fast_shutdown=0 
备份原数据库数据（冷备） 
c.  使用新版本软件 “挂” 旧版本数据启动(--skip-grant-tables ,-- 
skip-networking) 
d.  升级 ： 只是升级系统表。升级时间和数据量无关的。 
e.  正常重启数据库。 
f.  验证各项功能是否正常。 
g.  业务恢复。 
 
建议： inpalce升级最好是主从环境，先从库再主库。 
 
Logical Upgrade升级原理: 
1. 使用mysqldump备份全库数据 
2. 停原库 
3. 下载新版MySQL软件 
4. 初始化新版MySQL 
5. 启动新库 
6. 把之前备份的数据导入新库 
 
目前企业中一般使用In-Place 方式升级的比较多,Logical 方式,数据量大的话就不合适了,几个T的数据mysqldump要dump多久,更别提导入库里了. 
所以接下来,我将介绍In-Place方式的升级过程.Logical方式大家可以根据官方文档介绍进行自己学习. 

4 In-Place方式升级过程

4.1 由MySQL5616升级到MySQL5651

首先我得环境是5616版本.要从5616版本升级到5733,我们需要先把5616升级到5.6的最新版本5651 
1 停原库 
[root@db01 opt]# /usr/local/mysql5616/bin/mysql -S /tmp/mysql5616.sock  
mysql> set global innodb_fast_shutdown=0; 
[root@db01 opt]# /usr/local/mysql5616/bin/mysqladmin -S /tmp/mysql5616.sock shutdown 
[root@db01 opt]# 210704 06:46:15 mysqld_safe mysqld from pid file /data/5616/data/db01.pid ended 
2 下载5733数据库软件(略) 
3  使用高版本软件挂载低版本数据启动 
[root@db01 opt]# /usr/local/mysql5651/bin/mysqld_safe --defaults-file=/data/5616/my.cnf --skip-grant-tables --skip-networking & 
[4] 11802 
[root@db01 opt]# 210704 07:15:27 mysqld_safe Logging to '/data/5616/data/db01.err'. 
210704 07:15:27 mysqld_safe Starting mysqld daemon with databases from /data/5616/data 
4 升级 
[root@db01 opt]# /usr/local/mysql5651/bin/mysql_upgrade -S /tmp/mysql5616.sock --force 
Looking for 'mysql' as: /usr/local/mysql5651/bin/mysql 
Looking for 'mysqlcheck' as: /usr/local/mysql5651/bin/mysqlcheck 
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysql5616.sock'  
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysql5616.sock'  
mysql.columns_priv                                 OK 
mysql.db                                           OK 
mysql.event                                        OK 
mysql.func                                         OK 
mysql.general_log                                  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.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 
Running 'mysql_fix_privilege_tables'... 
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysql5616.sock'  
Running 'mysqlcheck' with connection arguments: '--socket=/tmp/mysql5616.sock'  
OK 
现在数据库已经由5616升级到了5651 
[root@db01 opt]# /usr/local/mysql5651/bin/mysql -S /tmp/mysql5616.sock  
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 9 
Server version: 5.6.51 MySQL Community Server (GPL) 
 
Copyright (c) 2000, 2021, 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 重启数据库到正常状态 
[root@db01 opt]# /usr/local/mysql5651/bin/mysqladmin -S /tmp/mysql5616.sock shutdown 
210704 07:22:22 mysqld_safe mysqld from pid file /data/5616/data/db01.pid ended 
[4]+  Done                    /usr/local/mysql5651/bin/mysqld_safe --defaults-file=/data/5616/my.cnf 
[root@db01 opt]# /usr/local/mysql5651/bin/mysqld_safe --defaults-file=/data/5616/my.cnf & 
[4] 12006 
[root@db01 opt]# 210704 07:22:37 mysqld_safe Logging to '/data/5616/data/db01.err'. 
210704 07:22:37 mysqld_safe Starting mysqld daemon with databases from /data/5616/data 
 
[root@db01 opt]# /usr/local/mysql5651/bin/mysql -S /tmp/mysql5616.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 1 
Server version: 5.6.51 MySQL Community Server (GPL) 
 
Copyright (c) 2000, 2021, 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>  
 
现在数据库版本已经由MySQL5616升级到了MySQL5651 

4.2 由MySQL5651升级到MySQL5733

1. 关闭原库 
2. 修改配置文件,指定当前basedir为5733的目录 
3. 使用高版本软件带起低版本数据 
[root@db01 opt]# /usr/local/mysql5733/bin/mysqld_safe --defaults-file=/data/5616/my.cnf --skip-grant-tables --skip-networking & 
[4] 12193 
[root@db01 opt]# 2021-07-04T11:28:57.280601Z mysqld_safe Logging to '/data/5616/data/db01.err'. 
2021-07-04T11:28:57.337826Z mysqld_safe Starting mysqld daemon with databases from /data/5616/data 
 
4. 升级 
[root@db01 opt]# /usr/local/mysql5733/bin/mysql_upgrade -S /tmp/mysql5616.sock --force 
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. 
5 重启数据库到正常状态 
[root@db01 opt]# /usr/local/mysql5733/bin/mysqladmin -S /tmp/mysql 
mysql5616.sock       mysql5616.sock.lock  mysql5733.sock       mysql5733.sock.lock  mysql8021.sock       mysql8021.sock.lock  mysqlx.sock          mysqlx.sock.lock      
[root@db01 opt]# /usr/local/mysql5733/bin/mysqladmin -S /tmp/mysql 
mysql5616.sock       mysql5616.sock.lock  mysql5733.sock       mysql5733.sock.lock  mysql8021.sock       mysql8021.sock.lock  mysqlx.sock          mysqlx.sock.lock      
[root@db01 opt]# /usr/local/mysql5733/bin/mysqladmin -S /tmp/mysql5616.sock shutdown 
2021-07-04T11:31:39.620201Z mysqld_safe mysqld from pid file /data/5616/data/db01.pid ended 
[4]+  Done                    /usr/local/mysql5733/bin/mysqld_safe --defaults-file=/data/5616/my.cnf --skip-grant-tables --skip-networking 
[root@db01 opt]# /usr/local/mysql5733/bin/mysqld_safe --defaults-file=/data/5616/my.cnf  & 
[4] 12431 
[root@db01 opt]# 2021-07-04T11:31:52.666976Z mysqld_safe Logging to '/data/5616/data/db01.err'. 
2021-07-04T11:31:52.727277Z mysqld_safe Starting mysqld daemon with databases from /data/5616/data 
 
[root@db01 opt]# /usr/local/mysql5733/bin/mysql -S /tmp/mysql5616.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 2 
Server version: 5.7.33 MySQL Community Server (GPL) 
 
Copyright (c) 2000, 2021, Oracle and/or its affiliates. 
 
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>  
至此,MySQL已经由5616升级到了5733 接下来我们将把MySQL从5733升级到8021 

5 将数据库从5733升级到8021

MySQL8.0的升级方式发生了变化.不再使用mysql_upgrade 而是使用mysql-shell对升级前数据库进行校验.

MySQL升级8.0和8.0之间的小版本升级需要注意:升级前必须备份.因为8.0不支持回退.

1 下载对应要升级到的8.0版本的mysql-shell  https://downloads.mysql.com/archives/shell/ 
2  解压做软连接 
tar xf mysql-shell-8.0.21-linux-glibc2.12-x86-64bit.tar.gz 
ln -s /opt/mysql-shell-8.0.21-linux-glibc2.12-x86-64bit /usr/local/mysqlsh 
2  添加环境变量 
vim /etc/profile 
export PATH=/usr/local/mysqlsh/bin:$PATH 
[root@db01 opt]# source /etc/profile 
[root@db01 opt]# mysqlsh -V 
mysqlsh   Ver 8.0.21 for Linux on x86_64 - for MySQL 8.0.21 (MySQL Community Server (GPL)) 
3 连接到5733创建mysql-shell的连接用户 
[root@db01 opt]# /usr/local/mysql5733/bin/mysql -S /tmp/mysql5616.sock 
mysql> grant all on *.* to root@'10.0.0.%' identified by '123'; 
4 使用mysql-shell进行升级前的预检查 
[root@db01 opt]# mysqlsh root:123@10.0.0.110:3307 -e "util.checkForServerUpgrade()" >/tmp/up.log 
WARNING: Using a password on the command line interface can be insecure. 
[root@db01 opt]# cat /tmp/up.log  
The MySQL server at 10.0.0.110:3307, version 5.7.33 - MySQL Community Server 
(GPL), will now be checked for compatibility issues for upgrade to MySQL 
8.0.21... 
 
1) Usage of old temporal type 
  No issues found 
 
2) Usage of db objects with names conflicting with new reserved keywords 
  No issues found 
 
3) Usage of utf8mb3 charset 
  No issues found 
 
4) Table names in the mysql schema conflicting with new tables in 8.0 
  No issues found 
 
5) Partitioned tables using engines with non native partitioning 
  No issues found 
 
6) Foreign key constraint names longer than 64 characters 
  No issues found 
 
7) Usage of obsolete MAXDB sql_mode flag 
  No issues found 
 
8) Usage of obsolete sql_mode flags 
  No issues found 
 
9) ENUM/SET column definitions containing elements longer than 255 characters 
  No issues found 
 
10) Usage of partitioned tables in shared tablespaces 
  No issues found 
 
11) Circular directory references in tablespace data file paths 
  No issues found 
 
12) Usage of removed functions 
  No issues found 
 
13) Usage of removed GROUP BY ASC/DESC syntax 
  No issues found 
 
14) Removed system variables for error logging to the system log configuration 
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary 
  More information: 
    https://dev.mysql.com/doc/relnotes/mysql/8.0/en/news-8-0-13.html#mysqld-8-0-13-logging 
 
15) Removed system variables 
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary 
  More information: 
    https://dev.mysql.com/doc/refman/8.0/en/added-deprecated-removed.html#optvars-removed 
 
16) System variables with new default values 
  To run this check requires full path to MySQL server configuration file to be specified at 'configPath' key of options dictionary 
  More information: 
    https://mysqlserverteam.com/new-defaults-in-mysql-8-0/ 
 
17) Zero Date, Datetime, and Timestamp values 
  No issues found 
 
18) Schema inconsistencies resulting from file removal or corruption 
  No issues found 
 
19) Tables recognized by InnoDB that belong to a different engine 
  No issues found 
 
20) Issues reported by 'check table x for upgrade' command 
  No issues found 
 
21) New default authentication plugin considerations 
  Warning: The new default authentication plugin 'caching_sha2_password' offers 
    more secure password hashing than previously used 'mysql_native_password' 
    (and consequent improved client connection authentication). However, it also 
    has compatibility implications that may affect existing MySQL installations.  
    If your MySQL installation must serve pre-8.0 clients and you encounter 
    compatibility issues after upgrading, the simplest way to address those 
    issues is to reconfigure the server to revert to the previous default 
    authentication plugin (mysql_native_password). For example, use these lines 
    in the server option file: 
     
    [mysqld] 
    default_authentication_plugin=mysql_native_password 
     
    However, the setting should be viewed as temporary, not as a long term or 
    permanent solution, because it causes new accounts created with the setting 
    in effect to forego the improved authentication security. 
    If you are using replication please take time to understand how the 
    authentication plugin changes may impact you. 
  More information: 
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-compatibility-issues 
    https://dev.mysql.com/doc/refman/8.0/en/upgrading-from-previous-series.html#upgrade-caching-sha2-password-replication 
 
Errors:   0 
Warnings: 1 
Notices:  0 
 
No fatal errors were found that would prevent an upgrade, but some potential issues were detected. Please ensure that the reported issues are not significant before upgrading. 
5 校验没问题之后停原库 
[root@db01 opt]# /usr/local/mysql5733/bin/mysql -S /tmp/mysql5616.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 6 
Server version: 5.7.33 MySQL Community Server (GPL) 
 
mysql> set global innodb_fast_shutdown=0; 
mysql> shutdown; 
6 修改配置文件中程序目录路径为8.0的程序路径 
[root@db01 opt]# cat /data/5616/my.cnf 
[mysqld]   
user=mysql 
basedir=/usr/local/mysql8021 
datadir=/data/5616/data 
socket=/tmp/mysql5616.sock 
server_id=56 
port=3307 
 
7 使用8.0的软件挂载5.7的数据启动 
[root@db01 opt]# /usr/local/mysql8021/bin/mysqld_safe --defaults-file=/data/5616/my.cnf & 
[4] 12714 
[root@db01 opt]# 2021-07-04T11:53:53.629634Z mysqld_safe Logging to '/data/5616/data/db01.err'. 
2021-07-04T11:53:53.686412Z mysqld_safe Starting mysqld daemon with databases from /data/5616/data  
[root@db01 opt]# /usr/local/mysql8021/bin/mysql -S /tmp/mysql5616.sock 
Welcome to the MySQL monitor.  Commands end with ; or \g. 
Your MySQL connection id is 11 
Server version: 8.0.21 MySQL Community Server - GPL 
 
Copyright (c) 2000, 2020, 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>  

6 后话

当然生产环境中的升级没有这么简单容易.会遇到很多问题.当然作为一名优秀的DBA.我相信大家都会迎刃而解.

当然在升级过程中尤其要注意sql_mode的变化.也要和业务及开发一起协同好!