mysql版本升级方案

[TOC]

注意事项

• 升级版本之前，了解新版本变更的信息（哪些不再兼容，不再支持哪些功能），确认新版本是否有重大变更
• 注意 存储引擎及 SQL mode 的调整，比如：在MySQL5.7中发生了SQL mode的变化，对不再支持的SQL mode，部分SQL会跑不通
• mysql不能使用 mysql_upgrade 跨越大版本升级，不能从5.6直接升级到8.0，需要先升级5.7再升级至8.0

mysql 5.7  ->  mysql 8.0
mysql 5.6  ->  mysql 5.7  -> mysql 8.0
mysql 8.0.x ->  mysql 8.0.y

• 大版本升级的话，注意调整 my.cnf 部分参数，否则会导致mysql进程启动失败
• 若代码使用ip访问mysql则需要修改连接字符串，容易遗漏，且涉及发版（若使用域名访问mysql则只需要刷新dns域名即可，这也是为什么推荐使用域名的原因）
• 刷新dns域名之后，注意有些应用可能要重启服务才会生效，否则可能仍访问老的mysql实例
• 关于mysql版本升级前后的对比确认，可参考如下：

#.1.查看用户以及权限
mysql> select user,host,authentication_string,plugin,account_locked from mysql.user;

#.2.验证对象数量
mysql> select * from sys.schema_object_overview;
mysql> select engine,count(*) from information_schema.tables group by engine;

#.3.通过 count 统计每个表的总数据量，注意 information_schema.tables 可能不准确
mysql> select concat('select count(*) from  ',table_schema,'.',table_name,';') from information_schema.tables where table_schema='库名';
mysql> select table_schema,table_name,table_rows from information_schema.tables order by table_rows desc;

#.4.通过 checksum table 检验数据一致性
mysql> select concat ('checksum table ',table_schema,'.',table_name,';') from information_schema.tables where table_schema='库名';

方案1：备份+还原 停服升级

• 第1步，停止 应用服务，5分钟之后杀掉所有应用服务的mysql连接，确保数据无变更
• 第2步，执行 mysqldump 备份整个实例

mysqldump --single-transaction --master-data=2 --flush-logs --triggers --events --routines --skip-tz-utc -uroot -p****** -A > /mysqldump.all.sql

• 第3步，停止 mysql 进程，重命名 my.cnf 及目录（包括安装目录及数据目录）
• 第4步，安装高版本的 mysql
• 第5步，停止 mysql 进程，用老的 my.cnf 替换掉 /etc/my.cnf（如果是大版本升级则需要调整部分参数）
• 第6步，启动 mysql 进程，确保启动无报错
• 第7步，导入 mysql 备份文件

mysql -uroot -p****** < /mysqldump.all.sql

• 第8步，启动应用服务

方案2：主从实例 在线升级

• 第1步，[应用] 将所有域名指向mysql主库（主要是从库域名），确保从库无访问
• 第2步，[从库] 先执行 stop slave;，从库安装高版本的mysql（若升级失败则不会影响主库），再执行 start slave;
• 第3步，[选做] 针对MHA或MGR环境，执行failover主从切换（传统的主从复制不支持failover，只需要在从库上执行 stop slave;）
• 第4步，[应用] 将所有域名指向mysql从库，确保主库无访问
• 第5步，[从库] 先执行 stop slave;，主库安装高版本的mysql，再执行 start slave;
• 第6步，[选做] 若为传统的主从复制，则需要修复主从关系（支持failover的环境则忽略此步）
• 第7步，[应用] 将主库、从库的域名指向对应的mysql实例

方案3：单实例升级

• 如果mysql实例不大的话，建议通过备份+还原的方式来完成升级，参考 方案1
• 如果mysql实例较大、且允许原地停服升级的话，可以参考 方案4
• 有条件的话，建议搭建一套主从复制，并参考 方案2 来完成升级

方案4：mysql_upgrade 原地升级

• 第1步，确认当前mysql的版本，示例 mysql 从 5.7.34-37 升级到 5.7.42-45

[root@localhost opt]# mysql -uroot -pAdmin_147 -S /tmp/mysql_3306.sock -e"select @@version;"
+---------------+
| @@version     |
+---------------+
| 5.7.34-37-log |
+---------------+

• 第2步，下载高版本的mysql软件包并解压，示例升级到 5.7.42-45

[root@localhost opt]# cd /opt/
[root@localhost opt]# tar -xzvf Percona-Server-5.7.42-45-Linux.x86_64.glibc2.17.tar.gz
[root@localhost opt]# mv Percona-Server-5.7.42-45-Linux.x86_64.glibc2.17 mysql.new
[root@localhost opt]# chown -R mysql.mysql /opt/mysql.new

• 第3步，停止老版本的mysql

[root@localhost opt]# /opt/mysql/bin/mysqladmin -uroot -pAdmin_147 -S /tmp/mysql_3306.sock shutdown

• 第4步，替换新的二进制文件（示例mysql的安装目录为 /opt/mysql）

[root@localhost opt]# mv /opt/mysql /opt/mysql.old
[root@localhost opt]# mv /opt/mysql.new /opt/mysql

• 第5步，使用现有的数据目录启动mysql（示例mysql的数据目录为 /data/mysql_3306）

[root@localhost opt]# /opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf &

[root@localhost opt]# mysql -uroot -pAdmin_147 -S /tmp/mysql_3306.sock -e"select @@version;"
+---------------+
| @@version     |
+---------------+
| 5.7.42-45-log |
+---------------+

• 第6步，执行 mysql_upgrade 检查所有库的所有表是否与当前的新版本兼容，并更新系统库

[root@localhost opt]# /opt/mysql/bin/mysql_upgrade -uroot -pAdmin_147 -S /tmp/mysql_3306.sock
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
The sys schema is already up to date (version 1.5.2).
Checking databases.
sys.sys_config                                     OK
testdb.testable                                    OK
Upgrade process completed successfully.
Checking if update is needed.

• 第7步，重启mysql，确保对系统表所做的变更得以生效

[root@localhost opt]# /opt/mysql/bin/mysqladmin -uroot -pAdmin_147 -S /tmp/mysql_3306.sock shutdown

[root@localhost opt]# /opt/mysql/bin/mysqld_safe --defaults-file=/data/mysql_3306/my_3306.cnf &

注：MySQL 8.0.16之前需要手动执行 mysql_upgrade 升级数据库。MySQL 8.0.16开始 mysql_upgrade 升级程序已经废弃，从MySQL 8.0.16之后会在下次启动时自动执行所有必要的升级任务，不需要再调用执行mysql_upgrade（即不需要第6步了）。