はじめに
2021 年 10 月現在、MySQL 8.0 がリリースされて約 3 年半が経過しました。
その間、MySQL 8.0.17 でリリースされたクローンプラグインであったり、MySQL Shell 8.0.21 にリリースされたダンプロードユーティリティ(LoadDump())であったりと、データのバックアップを取得する効率的な手段は進化し続けており、弊社ブログでも何度か紹介させていただきました。
そんな中、今回は長らく MySQL のバックアップツールとして利用されているデータベースバックアッププログラムの mysqldump について、MySQL 8.0 において変更された点や注意事項について代表的なものを紹介したいと思います。
検証環境
今回、以下の環境で検証をおこないました。
- CentOS 7.5
- MySQL Community Server 8.0.26
- MySQL dump 10.13 Distrib 8.0.26
変更点
--all-databases
すべてのデータベース内のすべてのテーブルをダンプするオプションです。
MySQL 5.7 まではこのオプションを単体で使用しても問題ありませんが、MySQL 8.0 ではストアドルーチンおよびイベント情報をダンプすることができません。
これは、MySQL 8.0 から mysql.proc および mysql.event テーブルがデータディクショナリスキーマに移行されたことで、mysql システムテーブルのダンプで情報を取得できなくなったためです。
そのため、完全なデータベースのダンプを取得するためには、--events および --routines をオプションとして指定する必要があります。
--add-drop-database
ダンプファイルの CREATE DATABASE の前に DROP DATABASE を追記するオプションです。
MySQL 8.0 からは、mysql システムデータベースがユーザーによって削除できなくなっているため、このオプションを指定したときに mysql システムデータベースを含んでいると、リストアした際に以下のようなエラーが出力されるので注意してください。
|
1 2 3 |
# mysqldump --all-databases --add-drop-database > dump.sql # mysql < dump.sql ERROR 3552 (HY000) at line 24: Access to system schema 'mysql' is rejected. |
--set-gtid-purged
GTID が有効になっている環境で使用するオプションであり、ダンプファイルに SET @@GLOBAL.gtid_purged を追記します。
MySQL 8.0 からは、以下のように追加されるステートメントに + がつくようになっています。
|
1 2 3 |
# mysqldump --set-gtid-purged=ON --all-databases --triggers --routines --events > dump.sql # grep "GLOBAL.GTID_PURGED" dump.sql SET @@GLOBAL.GTID_PURGED=/*!80000 '+'*/ '2bfbc18e-23fa-11ec-bb15-000c29d6b890:1'; |
+ をつけずに gtid_purged の値を設定する場合、設定する値は gtid_executed と gtid_purged の値を全て含んだスーパーセットである必要があります。
そのため、たとえばマルチソースレプリカを作成するために複数のソースから mysqldump を適用することができませんでしたが、MySQL 8.0 からは GTID の整合性を保ったままダンプをインポートできるようになっています。
なお、MySQL 5.7 までのサーバーでは、gtid_purged を設定する際に gtid_executed が空である必要があるので注意してください。
また、MySQL 8.0 においても、gtid_executed と重複する値を gtid_purged の値として追加することはできず、以下のようなエラーが発生してしまいます。
|
1 2 3 4 5 6 7 8 |
# mysql -e "SHOW GLOBAL VARIABLES LIKE 'gtid_executed';" +---------------+----------------------------------------+ | Variable_name | Value | +---------------+----------------------------------------+ | gtid_executed | 2bfbc18e-23fa-11ec-bb15-000c29d6b890:1 | +---------------+----------------------------------------+ # mysql < dump.sql ERROR 3546 (HY000) at line 26: @@GLOBAL.GTID_PURGED cannot be changed: the added gtid set must not overlap with @@GLOBAL.GTID_EXECUTED |
このようなことが想定されるケースでは、MySQL 8.0.17 から追加された --set-gtid-purged=COMMENTED を使用することで、あらかじめダンプファイルの SET @@GLOBAL.GTID_PURGED をコメントアウトさせておき、後から手動で GTID を制御することが可能です。
接続圧縮オプション
MySQL 8.0.18 以降、クライアントとサーバー間で送信される情報を圧縮する方法が選択できるようになっています。
詳細の説明については省きますが、mysqldump でも上記の変更を受けて、従来の --compress オプションが非推奨になり、新たに以下のオプションが追加されました。
- --compression-algorithms
- --zstd-compression-level
バージョンアップ時の注意事項
mysqldump を利用してバージョンアップをおこなう場合、MySQL のバージョン間の非互換性によってうまく実行できないケースが発生することがあります。
ここでは、MySQL 5.7 で mysqldump を取得して MySQL 8.0 の環境にリストアをおこなう場合に、最低限気を付けておきたい内容について記載しています。
なお、バージョン間の非互換性をテストする際には、まず以下のように --no-data で取得したテーブル定義情報のみのダンプをリストアして、テーブル定義の非互換性をチェックすることが推奨されています。
|
1 2 |
[MySQL5.7 環境]# mysqldump --all-databases --no-data --routines --events > dump-defs.sql [MySQL8.0 環境]# mysql < dump-defs.sql |
その後、--no-create-info オプションでデータのみをリストアすることで、効率的にバージョンアップの試験が可能です。
|
1 2 |
[MySQL5.7 環境]# mysqldump --all-databases --no-create-info > dump-data.sql [MySQL8.0 環境]# mysql < dump-data.sql |
パーティションテーブル
MySQL 8.0 からは、InnoDB および NDB ストレージエンジン以外のパーティション化されたテーブルはサポートされなくなりました。
そのため、MyISAM ストレージエンジンを使用しているパーティション化されたテーブルは、パーティションを解除するか、InnoDB などに変換しておく必要があります。
SQL モード
MySQL 8.0 からは使用できる SQL モードが変更されています。
特に、MySQL 5.7 の sql_mode のデフォルトである NO_AUTO_CREATE_USER は MySQL 8.0 において削除されているため、この SQL モードが指定されたストアドプロシージャが存在するダンプファイルをリストアしようとすると、以下のようなエラーが発生します。
|
1 2 |
# mysql < /tmp/dump.sql ERROR 1231 (42000) at line 63: Variable 'sql_mode' can't be set to the value of 'NO_AUTO_CREATE_USER' |
なお、本事象は MySQL 5.7.24(MySQL 8.0.13) 以降は互換性のために自動で削除するようになっているため、それ以降の mysqldump で実行していれば特に気にする必要はありません。
innodb_index_stats、innodb_table_stats テーブル
MySQL 8.0 から mysql.innodb_index_stats および mysql.innodb_table_stats テーブルへの DDL が実行できなくなっています。そのため、mysqldump においても、これらのテーブル定義情報を取得しなくなりました。
For dumps of the mysql system database, mysqldump no longer generates DDL statements for the innodb_index_stats and innodb_table_stats tables. Such statements fail at dump reload time because those tables are not user accessible. (Bug #22655287)
参照 URL : Changes in MySQL 8.0.1 (2017-04-10, Development Milestone)
ただし、MySQL 5.7 の mysqldump で取得したダンプファイルにはこれらの DDL が入っているため、そのまま MySQL 8.0 にリストアしようとすると以下のようなエラーになります。(Bug #92675)
|
1 2 |
# mysql < /tmp/dump.sql ERROR 3554 (HY000) at line 318: Access to system table 'mysql.innodb_index_stats' is rejected. |
そのため、MySQL 5.7 で mysqldump を取得する場合、以下のようにテーブルを除外する必要があるので注意してください。
|
1 |
# mysqldump --all-databases --ignore-table=mysql.innodb_index_stats --ignore-table=mysql.innodb_table_stats > dump.sql |
まとめ
ここまで、MySQL 8.0 における mysqldump の注意事項や変更点について確認してきました。
改めて確認してみると、MySQL 8.0 の仕様変更による部分が大きく、あまり機能追加という面ではアップグレードしていない印象を受けます。
ただし、mysqldump は環境依存も少なく使いやすいバックアップツールであるため、今後も幅広く使用されることが予想されます。そのため、定期的にリファレンスなどを確認して、更新内容をチェックすることを推奨いたします。
