MySQL5.7アップグレードの準備方法
OracleのMySQLチームからの記事
序章
MySQL 5.7は、人気のあるオープンソースデータベースの最新リリース候補です。 これは、変更を熱心に行う必要がある新しいスケーラビリティ機能を提供します。
変更点の1つを強調するために、スケーラビリティが大幅に改善されました。 ハイエンドでは、MySQL5.7は48コアサーバーで線形にスケーリングします。 ローエンドでは、MySQL5.7は512MBのDigitalOceanDropletでもすぐに使用できます(MySQL 5.6での構成変更なしでは不可能でした)。
MySQLサーバーの新しいピークパフォーマンスは1秒あたり64万クエリを超え、InnoDBストレージエンジンと直接通信するmemcached APIは、1秒あたり110万を超えるリクエストを維持できます。
ただし、急いでmysql_upgrade
を実行する前に、準備ができていることを確認する必要があります。 このチュートリアルは、まさにそれを行うのに役立ちます。
例を含むデータ整合性の変更
MySQL 5.7の大きな変更点の1つは、データの整合性が改善され、ベテランの開発者やDBAが期待するものとより一致するようになったことです。 以前は、MySQLは誤った値を可能な限り最も近い正しい値に調整していましたが、新しいデフォルトでは、代わりにエラーが返されます。
これは、MySQL5.7ですぐに使用できるように変更が必要なクエリの5つの例です。 アプリケーションはこれらの動作のいずれかを使用していますか?
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
と呼ばれるものの影響を受けます。
この機能はMySQL4.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サーバーの構成を変更して、MySQL5.7に同梱されているsql_mode
設定で動作するようにすることをお勧めします。
既存のアプリケーションを使用している場合は、更新をより段階的に処理することをお勧めします。 これらの提案は、移行に役立つ場合があります。
- ホワイトリスト:アプリケーションの新しい部分で新しいデフォルトのsql_modeオプションを有効にします。 たとえば、データのキャッシュを再構築するための一連のcronジョブを構築している場合、これらはMySQLに接続するとすぐにsql_modeを設定できます。 既存のアプリケーションコードは、最初は既存の非厳密な動作を維持できます。
- ブラックリスト:アプリケーションの変換を少し進めたら、新しいsql_modeをサーバーのデフォルトにします。 MySQLに接続するときに
sql_mode
を変更することで、レガシーアプリケーションを以前の動作のままにすることができます。 個々のステートメントに基づいて、MySQLはエラーをダウングレードするためのIGNORE
修飾子もサポートしています。 例:INSERT IGNORE INTO my_table . . .
- 段階的ロールアウト:アプリケーションを管理している場合は、ユーザーごとに
sql_mode
を変更する機能を実装できる場合があります。 これの良いユースケースは、内部ユーザーがすべてをベータテストして、より段階的な移行を可能にすることです。
ステップ1—警告またはエラーを生成する互換性のないステートメントを見つける
まず、現在のクエリのいずれかが警告またはエラーを生成していないかどうかを確認します。 これは、いくつかのクエリの動作が5.6の警告から5.7のエラーに変更されたため、アップグレードする前に警告をキャッチできるので便利です。
MySQL performance_schema は、MySQL5.6以降でデフォルトで有効になっている診断機能です。 performance_schema
を使用すると、エラーまたは警告を生成したサーバーで検出されたすべてのステートメントを返すクエリを作成できます。
エラーまたは警告を生成するステートメントを報告するためのMySQL5.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` > 0) OR (`SUM_WARNINGS` > 0)) ORDER BY `SUM_ERRORS` DESC, `SUM_WARNINGS` DESC;
エラーを生成するステートメントを報告するためのMySQL5.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` > 0 ORDER BY `SUM_ERRORS` DESC, `SUM_WARNINGS` DESC;
ステップ2—MySQL5.6をMySQL5.7のように動作させる
MySQL 5.6でテスト実行を実行して、5.7のように動作させることもできます。
著者のMySQLチームのMorganTockerは、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
アップグレードする準備ができました
この時点で、MySQL5.7にアップグレードする準備ができていることを確認する必要があります。 MySQLの公式アップグレードガイドに従って、スイッチを切り替えます。
結論
MySQL 5.7は、最新のアプリケーションのデフォルト構成とデータ整合性を改善する上で大きな一歩を踏み出しました。 この記事がスムーズな移行に役立つことを願っています。
5.7でのすべての変更の概要(これまでのところ)については、MySQLサーバーチームのブログ投稿を確認してください。