likes
comments
collection
share

从 MySQL 5.7 迁移到 MySQL 8.0 的最佳实践升级 MySQL 5.7 至 8.0 需谨慎规划。备份数据

作者站长头像
站长
· 阅读数 56

引言

因为公司目前项目现场需要将mysql 5.7.30版本升级到mysql 8.故借此机会顺便学习一下如何升级，及需要注意点。

正文

MySQL 5.7 升级到 8.0 需要关注多个方面的变更，包括但不限于兼容性、性能、安全性和新功能

兼容性变更

性能和优化

  1. InnoDB 存储引擎：

    • InnoDB 在 MySQL 8.0 中得到了许多改进，包括 DDL 语句的原子性。
    • 需要调整 InnoDB 相关的配置以优化性能。

  2. 查询执行计划：

    • MySQL 8.0 引入了新的优化器特性，如“直方图”和“改进的排序算法”。
    • 需要分析查询计划，以确保查询仍然有效并且性能得到优化。

安全性

  1. 强密码策略：

    • MySQL 8.0 使用 caching_sha2_password 作为默认的身份验证插件，这可能需要更新连接数据库的客户端和库。

    • 需要确保所有用户密码符合新的策略。

    • 主要体现在以下几个方面：

默认身份验证插件

MySQL 8.0 将默认的身份验证插件从 mysql_native_password 更改为 caching_sha2_password。这个新的插件提供了更强的安全性，因为它使用 SHA-256 散列算法，并且支持密码缓存，提高了连接的认证效率。

密码验证策略

MySQL 8.0 提供了密码验证策略，可以通过 validate_password 组件来实现。这个组件可以帮助确保用户账户的密码复杂度符合安全要求。validate_password 组件提供以下几种策略：

  • LOW：密码必须至少包含 8 个字符。
  • MEDIUM：密码必须至少包含 8 个字符，并且至少有一个大写字母、一个小写字母、一个数字和一个特殊字符。
  • STRONG：使用字典文件来检查密码的强度，防止用户使用常见的弱密码。

密码过期功能

MySQL 8.0 允许数据库管理员设置密码过期策略，要求用户定期更改密码。可以为每个用户账户设置密码过期时间，或者全局设置默认的密码寿命。

密码历史和重用限制

MySQL 8.0 可以限制密码的重用，防止用户在一定时间内重复使用旧密码。这通过记录密码历史来实现，可以配置密码历史的长度。

密码锁定策略

可以设置账户在一定次数的登录失败后被锁定，增加了对暴力破解攻击的防护。

如何配置和使用

要启用和配置 validate_password 组件，可以在 MySQL 配置文件中设置相关参数，也可以在运行时通过 SQL 语句设置。例如，以下是一些常用的配置：

-- 安装 validate_password 组件
INSTALL COMPONENT 'file://component_validate_password';

-- 设置密码策略
SET GLOBAL validate_password.policy = 'MEDIUM';

-- 设置密码长度
SET GLOBAL validate_password.length = 12;

-- 设置密码过期时间
SET GLOBAL default_password_lifetime = 180;

-- 设置密码历史
SET GLOBAL validate_password.history = 5;
  1. SSL/TLS：
    • 默认启用 SSL/TLS 连接，需要确保应用程序支持加密连接。

新功能

  1. 数据字典：

    • MySQL 8.0 引入了新的数据字典，取代了旧的 .frm 文件格式。
    • 升级过程中会自动迁移，但了解这一变化对于故障排除很重要。

  2. 系统表和视图：

    • 有许多新的系统表和视图，可以用来监控和管理数据库。

  3. GIS 支持：

    • MySQL 8.0 强化了对空间数据的支持，包括对空间参考系统 (SRS) 的支持。

修改的 SQL 写法：

1. GROUP BY 的严格模式

在 MySQL 8.0 中，默认启用了 ONLY_FULL_GROUP_BY SQL 模式，这要求 GROUP BY 子句中列出的所有列也必须在 SELECT 列表中出现 ，或者在一个聚合函数中。这意味着之前可能有效的松散 GROUP BY 查询在 8.0 中可能会抛出错误。

示例：

-- MySQL 5.7，可能有效
SELECT a, b FROM table1 GROUP BY a;

-- MySQL 8.0，需要修改为
SELECT a, MAX(b) FROM table1 GROUP BY a;

2. 使用引号的关键字和保留字

MySQL 8.0 引入了新的保留字，如果在 5.7 中使用了这些词作为标识符而没有用引号括起来，那么在 8.0 中可能需要修改。

示例：

-- MySQL 5.7，可能有效
SELECT * FROM mytable WHERE rank = 1;

-- MySQL 8.0，如果“rank”成为保留字，需要修改为
SELECT * FROM mytable WHERE `rank` = 1;

3. JOIN 语法

MySQL 8.0 对 JOIN 语法的解析更加严格，不再支持一些非标准的用法。

示例：

-- MySQL 5.7，可能有效
SELECT * FROM table1, table2 LEFT JOIN table3 ON table1.id = table3.id;

-- MySQL 8.0，需要修改为
SELECT * FROM table1 LEFT JOIN table3 ON table1.id = table3.id, table2;

4. 不支持的函数和特性

一些在 MySQL 5.7 中已经被标记为已弃用的函数和特性在 MySQL 8.0 中被移除。

示例：

  • CREATE TABLE ... SELECT 语法在 8.0 中有所变化。
  • YEAR(2) 数据类型不再支持，需要修改为 YEAR(4)。

5. 变量赋值

在 MySQL 8.0 中，对用户变量的赋值语法更加严格。在 SELECT 语句中赋值用户变量的行为可能会有所不同。

示例：

-- MySQL 5.7，可能有效
SELECT @var := col1 FROM table1;

-- MySQL 8.0，推荐使用 SET 语句或在 SELECT 列表的最后进行赋值
SET @var = (SELECT col1 FROM table1 LIMIT 1);

6. 引擎特定的表选项

在 MySQL 8.0 中，某些存储引擎特定的表选项不再支持或有所变化。

示例：

  • MyISAM 引擎的一些特定选项可能需要移除或更改。

7. PARTITION BY RANGE COLUMNS 语法

在 MySQL 8.0 中，PARTITION BY RANGE COLUMNS 语法需要使用括号包围列名列表。

示例：

-- MySQL 5.7，可能有效
CREATE TABLE t1 (col1 INT) PARTITION BY RANGE COLUMNS(col1) (...);

-- MySQL 8.0，需要修改为
CREATE TABLE t1 (col1 INT) PARTITION BY RANGE COLUMNS(col1) (...);

升级步骤

  1. 备份数据：

    • 在进行任何升级之前，确保备份所有数据和配置。

  2. 检查和升级应用程序：

    • 确保应用程序兼容 MySQL 8.0。
    • 更新应用程序中的数据库连接代码。

  3. 测试：

    • 在升级生产环境之前，在测试环境中彻底测试升级过程和应用程序的兼容性。

  4. 执行升级：

    • 遵循 MySQL 官方的升级指南进行操作。

  5. 监控和调优：

    • 在升级后监控数据库的性能，并根据需要进行调优。

  6. 文档和培训：

    • 更新相关文档，并为数据库管理员和开发人员提供关于新特性和变更的培训。

注意事项

数据库引擎变更

  • MyISAM 引擎的淘汰：MySQL 8.0 继续推动向 InnoDB 的过渡。虽然 MyISAM 仍然可用，但许多新特性都是专为 InnoDB 引擎设计的。你应该计划将 MyISAM 表迁移到 InnoDB。

系统变量和配置

  • 配置文件变更：某些系统变量和选项在 MySQL 8.0 中已被废弃或更改。需要审查 my.cnf 或 my.ini 配置文件，并根据新版本的要求进行更新。
  • 持久化系统变量：MySQL 8.0 引入了系统变量的持久化，这意味着可以在运行时更改变量并将这些更改持久化到服务器重启后。

存储过程和触发器

  • SQL 模式变更：某些 SQL 模式设置在 MySQL 8.0 中有所变更，这可能会影响存储过程和触发器的行为。确保它们在新版本中仍然按预期工作。

插件和扩展

  • 身份验证插件变更：如前所述，新的默认身份验证插件是 caching_sha2_password。如果你使用外部程序或库连接到 MySQL，需要确保它们支持新的插件。
  • 其他插件：检查你使用的任何插件是否与 MySQL 8.0 兼容，并更新或替换不兼容的插件。

日志文件

  • 错误日志格式变更：MySQL 8.0 中的错误日志格式有所变更。你可能需要更新监控脚本和工具以适应新格式。

部署和运维

  • 升级工具：使用 MySQL 提供的升级检查工具 mysql_upgrade 来检查数据库的兼容性，并执行必要的系统表升级。
  • 备份策略：确保你有一套完整的备份和恢复策略，因为在升级过程中总有数据丢失的风险。

应用程序代码

  • 代码审查：审查应用程序代码以确保与 MySQL 8.0 兼容，特别是那些紧密依赖于数据库特定行为的代码。

升级后的测试

  • 全面测试：在升级后，进行全面的测试以确保所有功能按预期工作，包括但不限于应用程序的 CRUD 操作、报表生成、数据导入导出等。

文档和教育

  • 更新文档：更新所有相关的技术文档和操作手册以反映新版本的变更。
  • 团队培训：确保数据库管理员、开发人员和最终用户了解新版本的变更，并接受适当的培训。

结论

升级数据库系统是一个重大的任务，需要仔细规划和执行。考虑到可能的问题和变更，应该安排足够的时间来准备和执行升级，以确保系统的平稳过渡。

MySQL 5.7 升级到 8.0 是一个涉及广泛考量的过程，需要仔细规划和执行。最好阅读 MySQL 官方的 升级指南，并在升级前进行充分的测试和评估。

本文皆为个人原创，请尊重创作，未经许可不得转载。

转载自:https://juejin.cn/post/7410259203175710730
评论
请登录