升级到MySQL 5.7注意事项和准备工作

本教程将帮助您准备从较早版本升级到MySQL 5.7。它涵盖了`sql_mode`与查询响应该得到严格的实际例子。本教程还包含说明检查警告和错误,并包含一个示例配置文件,以便可以在升级之前测试新的设置。

介绍

MySQL 5.7是流行的开源数据库的最新版本。它提供了新的可扩展性功能,你应该及时更新,以发挥更好的性能。 为了突出其中一个变化,可扩展性已经大大提高。在高端,MySQL 5.7在48核服务器上线性扩展。在低端,MySQL 5.7也在512 MB的DigitalOcean Droplet服务器可用。 对于MySQL服务器的新的峰值性能为每秒超过640K查询,memcached的API,它直接使用InnoDB存储引擎,是能够维持的每秒超过110万的请求MySQL 5.7性能使用Memcached NoSQL API 在你可以使用mysql_upgrade ,但是,你应该确保你已做好准备。本教程可以帮助你做到这一点。

数据完整性更改,附带示例

MySQL 5.7的一个主要变化是,数据完整性已得到改进,更符合老牌开发人员和DBA的期望。以前,MySQL会将不正确的值调整为最接近的可能正确值,但在新的默认值下,它会返回错误。 这里有五个查询示例。您的应用程序是否使用这些行为中的任何一种?

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)在10个字符的列中插入20个字符的字符串

创建一个包含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)将非标准零日期插入日期时间列

创建具有datetime列的表:
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 ,并且没有聚集函数(例如MINMAX )施加到其上。 先前行为:
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_ZERONO_ZERO_DATENO_ZERO_IN_DATE 。 单击任何这些模式名称以访问MySQL手册,以了解更多信息。

关于如何转换的建议

如果你使用最新版本的Wordpress,Drupal或Magento的好消息是,你不需要做任何事情。这些应用程序已经知道MySQL的sql_mode功能,并在连接到MySQL将设置它们与兼容的选项。 如果你正在建设一个新的应用程序 ,那么它可能会改变你现有的MySQL 5.6服务器的配置与表现是一个好主意sql_mode被运的MySQL 5.7的设置。 如果你有一个现有的应用程序 ,你可能想通过更新的详细逐步工作。这些建议可能会帮助您转换:
  • 白名单 :让新的应用部分启用新的默认的<tt>sqlmode</tt>选项。 例如,如果你正在建设一个集cron作业的重建数据的高速缓存,这些设置可以<tt>sqlmode</tt>,尽快为他们连接到MySQL。现有的应用程序代码最初可以保持现有的非严格行为。
  • 黑名单 :当您在转换应用中取得了一些进展,现在是时候让新的<tt>sqlmode</tt>默认为您的服务器。 它可以通过让他们改变`SQL模式仍然有旧的应用程序以前的行为 当他们连接到MySQL。在个人陈述的基础上,MySQL还支持theIGNOREmodifier降级错误。 例如:INSERT IGNORE INTO my_table . . .
  • 分阶段推出 :如果你在你的应用程序的控制,你可以实现一个功能改变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。 作者Morgan Tocker在MySQL团队,拥有一个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。有请跟随MySQL的官方升级指南

结论

MySQL 5.7在改进现代应用程序的默认配置和数据完整性方面迈出了一大步。我们希望本文能帮助您顺利过渡! 有关5.7(到目前为止)的所有更改的概述,请查看MySQL服务器团队的博客帖子: