如何为MySQL5.7升级做准备

Oracle [1] MySQL 团队的一篇文章

介绍

MySQL 5.7 是流行的开源数据库的最新候选版本。它提供了新的可扩展性功能，应该让您渴望做出改变。

为了突出其中一项更改，可扩展性得到了极大的改进。在高端，MySQL 5.7 在 48 核服务器上线性扩展。在低端，MySQL 5.7 还可以在 512 MB DigitalOcean Droplet 上开箱即用（如果没有 MySQL 5.6 中的配置更改，这是不可能的）。

MySQL 服务器的新峰值性能超过每秒 640K 查询，而直接与 InnoDB 存储引擎对话的 memcached API 能够维持每秒超过 110 万次请求。

[2]

但是，在你急于运行 mysql_upgrade 之前，你应该确保你已经做好了准备。本教程可以帮助您做到这一点。

数据完整性更改，带有示例

MySQL 5.7 的一个主要变化是数据完整性得到了改进，更加符合资深开发人员和 DBA 的期望。以前，MySQL 会将不正确的值调整为最接近的正确值，但在新的默认值下，它会改为返回错误。

下面是五个需要修改才能在 MySQL 5.7 中开箱即用的查询示例。您的应用程序是否使用任何这些行为？

1) 将负值插入无符号列

创建一个包含无符号列的表：

CREATE TABLE test (  
 id int unsigned  
);

插入一个负值。

以前的行为：

INSERT INTO test VALUES (-1);
Query OK, 1 row affected, 1 warning (0.01 sec)

MySQL 5.7：

INSERT INTO test VALUES (-1);  
ERROR 1264 (22003): Out of range value for column 'a' at row 1

2）除以零

创建测试表：

CREATE TABLE test2 (  
 id int unsigned  
);

尝试除以零。

以前的行为：

INSERT INTO test2 VALUES (0/0);  
Query OK, 1 row affected (0.01 sec)

MySQL 5.7：

INSERT INTO test2 VALUES (0/0);  
ERROR 1365 (22012): Division by 0

3) 将 20 个字符的字符串插入 10 个字符的列

创建一个包含 10 个字符的列的表：

CREATE TABLE test3 (  
a varchar(10)  
);

尝试插入更长的字符串。

以前的行为：

INSERT INTO test3 VALUES ('abcdefghijklmnopqrstuvwxyz'); 
Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL 5.7：

INSERT INTO test3 VALUES ('abcdefghijklmnopqrstuvwxyz');  
ERROR 1406 (22001): Data too long for column 'a' at row 1

4) 将非标准零日期插入日期时间列

创建一个包含日期时间列的表：

CREATE TABLE test3 (  
a datetime  
);

插入 0000-00-00 00:00:00。

以前的行为：

INSERT INTO test3 VALUES ('0000-00-00 00:00:00');  
Query OK, 1 row affected, 1 warning (0.00 sec)

MySQL 5.7：

INSERT INTO test3 VALUES ('0000-00-00 00:00:00');  
ERROR 1292 (22007): Incorrect datetime value: '0000-00-00 00:00:00' for column 'a' at row 1

5) 使用 GROUP BY 并选择一个不明确的列

当描述不是 GROUP BY 的一部分，并且没有应用聚合函数（例如 MIN 或 MAX）时，就会发生这种情况。

以前的行为：

SELECT id, invoice_id, description FROM invoice_line_items GROUP BY invoice_id;  
+----+------------+-------------+  
| id | invoice_id | description |  
+----+------------+-------------+  
| 1 | 1 | New socks             |  
| 3 | 2 | Shoes                 |  
| 5 | 3 | Tie                   |  
+----+------------+-------------+  
3 rows in set (0.00 sec)

MySQL 5.7：

SELECT id, invoice_id, description FROM invoice_line_items GROUP BY invoice_id;  
ERROR 1055 (42000): Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'invoice_line_items.description' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

了解 sql_mode 设置的行为

在 MySQL 术语中，上一节中显示的每个行为都受到所谓的 sql_mode 的影响。

该功能在 MySQL 4.1 (2004) 中首次亮相，但默认情况下尚未编译。 MySQL 5.7 默认开启以下模式：

模式 STRICT_TRANS_TABLES 也变得更加严格，并启用先前在模式 ERROR_FOR_DIVISION_BY_ZERO、NO_ZERO_DATE 和 NO_ZERO_IN_DATE 下指定的行为。

单击这些模式名称中的任何一个以访问 MySQL 手册，以了解更多信息。

关于如何过渡的建议

如果您使用的是最新版本的 Wordpress、Drupal 或 Magento，那么好消息是您无需执行任何操作。这些应用程序已经知道 MySQL 的 sql_mode 功能，并且在连接到 MySQL 时将设置它们兼容的选项。

如果您当前正在 构建一个新应用程序 ，那么最好更改现有 MySQL 5.6 服务器的配置以使用 MySQL 5.7 中附带的 sql_mode 设置。

如果您有 现有应用程序 ，您可能希望逐步完成更新。这些建议可以帮助您过渡：

Whitelist：让应用程序的新部分启用新的默认 sql_mode 选项。例如，如果您正在构建一组 cron 作业来重建数据缓存，它们可以在连接到 MySQL 后立即设置 sql_mode。现有的应用程序代码最初可以保持现有的非严格行为。
Blacklist：当您在转换应用程序方面取得了一些进展时，是时候将新的 sql_mode 设为服务器的默认值了。通过让旧应用程序在连接到 MySQL 时更改 sql_mode，它们仍然可以保持以前的行为。在单个语句的基础上，MySQL 还支持 IGNORE 修饰符来降级错误。例如：INSERT IGNORE INTO my_table . . .
Staged Rollout：如果您可以控制您的应用程序，您可能能够实现一个功能来根据每个用户更改 sql_mode。一个很好的用例是允许内部用户对所有内容进行 Beta 测试，以实现更渐进的过渡。

第 1 步 — 查找产生警告或错误的不兼容语句

首先，查看您当前的任何查询是否产生警告或错误。这很有用，因为多个查询的行为已从 5.6 中的警告更改为 5.7 中的错误，因此您现在可以在升级之前捕获警告。

MySQL performance_schema 是一个诊断功能，在 MySQL 5.6 及更高版本上默认启用。使用 performance_schema，可以编写一个查询来返回服务器遇到的所有产生错误或警告的语句。

MySQL 5.6+ 查询报告产生错误或警告的语句：

SELECT 
`DIGEST_TEXT` AS `query`,
`SCHEMA_NAME` AS `db`,
`COUNT_STAR` AS `exec_count`,
`SUM_ERRORS` AS `errors`,
(ifnull((`SUM_ERRORS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `error_pct`,
`SUM_WARNINGS` AS `warnings`,
(ifnull((`SUM_WARNINGS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `warning_pct`,
`FIRST_SEEN` AS `first_seen`,
`LAST_SEEN` AS `last_seen`,
`DIGEST` AS `digest`
FROM
 performance_schema.events_statements_summary_by_digest
WHERE
((`SUM_ERRORS` &gt; 0) OR (`SUM_WARNINGS` &gt; 0))
ORDER BY
 `SUM_ERRORS` DESC,
 `SUM_WARNINGS` DESC;

MySQL 5.6+ 查询报告产生错误的语句：

SELECT 
`DIGEST_TEXT` AS `query`,
`SCHEMA_NAME` AS `db`,
`COUNT_STAR` AS `exec_count`,
`SUM_ERRORS` AS `errors`,
(ifnull((`SUM_ERRORS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `error_pct`,
`SUM_WARNINGS` AS `warnings`,
(ifnull((`SUM_WARNINGS` / nullif(`COUNT_STAR`,0)),0) * 100) AS `warning_pct`,
`FIRST_SEEN` AS `first_seen`,
`LAST_SEEN` AS `last_seen`,
`DIGEST` AS `digest`
FROM
 performance_schema.events_statements_summary_by_digest
WHERE
 `SUM_ERRORS` &gt; 0
ORDER BY
 `SUM_ERRORS` DESC,
 `SUM_WARNINGS` DESC;

第 2 步 — 使 MySQL 5.6 表现得像 MySQL 5.7

您还可以使用 MySQL 5.6 进行测试运行，使其表现得像 5.7。

作者，来自 MySQL 团队的 Morgan Tocker，有一个 GitHub 项目和一个示例配置文件可以让你这样做。通过使用 MySQL 5.6 中即将出现的默认值，您将能够消除您的应用程序依赖于不太严格的行为的可能性。

该文件相当短，因此我们也将其包括在此处：

# This makes a MySQL 5.6 server behave similar to the new defaults
# in MySQL 5.7

[mysqld]

# MySQL 5.7 enables more SQL modes by default, but also
# merges ERROR_FOR_DIVISION_BY_ZERO, NO_ZERO_DATE, NO_ZERO_IN_DATE
# into the definition of STRICT_TRANS_TABLES.
# Context: http://dev.mysql.com/worklog/task/?id=7467

sql-mode="ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,ERROR_FOR_DIVISION_BY_ZERO,NO_ZERO_DATE,NO_ZERO_IN_DATE"

# The optimizer changes the default from 10 dives to 200 dives by default
# Context: http://mysqlserverteam.com/you-asked-for-it-new-default-for-eq_range_index_dive_limit/

eq_range_index_dive_limit=200

# MySQL 5.7 contains a new internal server logging API.
# The setting log_warnings is deprecated in 5.7.2 in favour of log_error_verbosity.
# *But* the default fo log_warnings also changes to 2 as well:

log_warnings=2

# MySQL 5.7.7 changes a number of replication defaults
# Binary logging is still disabled, but will default to ROW when enabled.

binlog_format=ROW
sync_binlog=1
slave_net_timeout=60

# InnoDB defaults to the new Dynamic Row format with Barracuda file format.
# large_prefix is also enabled, which allows for longer index values.

innodb_strict_mode=1
innodb_file_format=Barracuda
innodb_large_prefix=1
innodb_purge_threads=4 # coming in 5.7.8
innodb_checksum_algorithm=crc32

# In MySQL 5.7 only 20% of the pool will be dumped, 
# But 5.6 does not support this option

innodb_buffer_pool_dump_at_shutdown=1
innodb_buffer_pool_load_at_startup=1

# These two options had different names in previous versions
# (binlogging_impossible_mode,simplified_binlog_gtid_recovery)
# This config file targets 5.6.23+, but includes the 'loose' modifier to not fail
# prior versions.

loose-binlog_error_action=ABORT_SERVER
loose-binlog_gtid_recovery_simplified=1

# 5.7 enable additional P_S consumers by default
# This one is supported in 5.6 as well.
performance-schema-consumer-events_statements_history=ON

（可选）第 3 步 — 基于每个会话更改 sql_mode

有时您想分阶段测试或升级您的服务器。无需更改 MySQL 服务器范围的配置文件以使用新的 SQL 模式，还可以在每个会话的基础上更改它们。这是一个例子：

CREATE TABLE sql_mode_test (a int);

未设置 SQL 模式：

set sql_mode = '';
INSERT INTO sql_mode_test (a) VALUES (0/0);
Query OK, 1 row affected (0.01 sec)

更严格的 SQL 模式集：

set sql_mode = 'STRICT_TRANS_TABLES';
INSERT INTO sql_mode_test (a) VALUES (0/0);
ERROR 1365 (22012): Division by 0

准备升级

此时，您应该确信您已准备好升级到 MySQL 5.7。跟着【X18X】MySQL官方升级指南【X52X】来翻转开关。

结论

MySQL 5.7 在改进现代应用程序的默认配置和数据完整性方面向前迈出了一大步。我们希望本文能帮助您顺利过渡！

有关 5.7（到目前为止）中所有更改的概述，请查看 MySQL 服务器团队的博客文章：