前書き

MySQL 5.7は、人気のあるオープンソースデータベースの最新のリリース候補です。 それはあなたが変更を行うことに熱心でなければならない新しいスケーラビリティ機能を提供します。

変更の1つを強調するために、スケーラビリティが大幅に改善されました。 ハイエンドでは、MySQL 5.7は48コアサーバーで直線的にスケーリングします。 ローエンドでは、MySQL 5.7は、512 MBのDigitalOcean Droplet(MySQL 5.6の構成を変更しないと不可能だったもの)でもそのまま使用できます。

MySQLサーバーの新しいピークパフォーマンスは1秒あたり640Kクエリを超え、InnoDBストレージエンジンと直接通信するmemcached APIはhttps://blogs.oracle.com/mysqlinnodb/entry/mysql_5_7_3_deep[over 1秒あたり110万リクエスト]。

Memcached NoSQL APIを使用したMySQL 5.7パフォーマンス

急いで「+ mysql_upgrade」を実行する前に、準備が整っていることを確認する必要があります。 このチュートリアルは、まさにそれを行うのに役立ちます。

データ整合性の変更、例付き

MySQL 5.7での大きな変更点の1つは、ベテランの開発者とDBAが期待するものとより一致するようにデータの整合性が改善されたことです。 以前は、MySQLは誤った値を可能な限り最も近い正しい値に調整していましたが、新しいデフォルトでは代わりにエラーを返します。

MySQL 5.7ですぐに機能するために変更が必要なクエリの5つの例を次に示します。 アプリケーションはこれらの動作のいずれかを使用していますか?

[[1-inserting-a-negative-value-into-an-unsigned-column]]
==== 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-division-by-zero]]
==== 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-inserting-a-20-character-string-into-a-10-character-column]]
==== 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-inserting-the-non-standard-zero-date-into-a-datetime-column]]
==== 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-using-group-by-and-selecting-an-ambiguous-column]]
==== 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では、デフォルトで次のモードが有効になっています。

モードhttp://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_strict_trans_tables [+ STRICT_TRANS_TABLES +]もより厳密になり、モードhttpで以前に指定された動作を有効にします。 //dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_error_for_division_by_zero[ERROR_FOR_DIVISION_BY_ZERO]、http://dev.mysql.com/doc/refman/5.7/en/sql- mode.html#sqlmode_no_zero_date [+ NO_ZERO_DATE +]およびhttp://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_in_date [+ NO_ZERO_IN_DATE +]。

これらのモード名のいずれかをクリックして、MySQLマニュアルにアクセスし、詳細を確認してください。

移行方法に関する提案

WordPress、Drupal、またはMagentoの最新バージョンを使用している場合、良いことは何もする必要がないことです。 これらのアプリケーションは、MySQLの「+ sql_mode +」機能をすでに認識しており、MySQLに接続すると、互換性のあるオプションが設定されます。

現在*新しいアプリケーションを構築中*の場合、MySQL 5.7に同梱されている `+ sql_mode +`設定で動作するように、既存のMySQL 5.6サーバーの設定を変更することをお勧めします。

*既存のアプリケーション*を使用している場合は、更新をより段階的に進めたい場合があります。 これらの提案は、移行に役立つ場合があります。

  • * http://en.wikipedia.org/wiki/Whitelist [Whitelist] *:アプリケーションの新しい部分で、新しいデフォルトの<tt> sqlmode </ tt>オプションを有効にします。 たとえば、データのキャッシュを再構築するために一連のcronジョブを構築している場合、これらはMySQLに接続するとすぐに<tt> sqlmode </ tt>を設定できます。 既存のアプリケーションコードは、最初は既存の非厳密な動作を維持できます。

  • * http://en.wikipedia.org/wiki/blacklist [Blacklist] *:アプリケーションの変換にある程度の準備ができたら、新しい<tt> sqlmode </ tt>をサーバーのデフォルトに設定します。 MySQLに接続するときに sqlmode を変更することにより、従来のアプリケーションに以前の動作を維持することができます。 MySQLは個々のステートメントに基づいて、エラーをダウングレードするための IGNORE 修飾子もサポートしています。 例: INSERT IGNORE INTO my_table

  • * Staged Rollout *:アプリケーションを制御している場合、ユーザーごとに `+ sql_mode +`を変更する機能を実装できる場合があります。 これの適切な使用例は、内部ユーザーがすべてをベータテストして、より緩やかな移行を可能にすることです。

手順1-警告またはエラーを生成する互換性のないステートメントの検索

最初に、現在のクエリのいずれかが警告またはエラーを生成しているかどうかを確認します。 これは、いくつかのクエリの動作が5.6の警告から5.7のエラーに変更され、アップグレードする前に警告をキャッチできるため便利です。

MySQL http://dev.mysql.com/doc/refman/5.6/en/performance-schema.html [+ 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には、https://github.com/morgo/mysql-compatibility-config/でhttps://github.com/morgo/mysql-compatibility-config[GitHubプロジェクト]を利用できますこれを可能にするblob / master / mysql-56 / mysql-57.cnf [サンプル構成ファイル]。 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サーバーチームのブログ投稿をご覧ください。