この投稿はアイスタイル Advent Calendar 2020 の17日目の記事です。
はじめまして、アイスタイルでインフラ・DBAをやっているiwasakikです。
アイスタイルに入社して約1年半が経つのですが、日々新しい発見が得られる毎日を過ごしております。
今回はMySQLのバックアップに関する記事を書かせていただきます。
業務上MySQLの運用に関わる事が多いのですが、バックアップというのは取得されていて当たり前なんだけど、設定するのに手間かけない・負荷をかけないというのが大事だと考えています。
そこでMySQLのバックアップとしてどの手法を利用するのが良いのかについて、全てではないのですが複数のツールを比較・検証してみました。
結論、それぞれのバックアップ手法にメリット・デメリットがあるのですが、その詳細は後半お楽しみに！
目次
- 検証したMySQLバックアップ手法について
- mysqldump
- mysqlpump
- percona xtrabackup
- Instance Dump Utility and Dump Loading Utility
- 検証内容
- 検証結果について
- バックアップ手法ごとのバックアップ時間について
- バックアップ手法ごとのCPU負荷について
- バックアップ手法ごとのバックアップサイズについて
- バックアップのことじゃないけどどうしても紹介したいこと：圧縮形式zstd
- 検証結果からみる各バックアップ手法の特徴について
- まとめ
- さいごに
検証したMySQLバックアップ手法について
今回検証したバックアップ手法は下記となります。
簡単に内容を説明させていただきます。
その１：mysqldump
mysqlのダンプを行う専用ツールです。
ここでいうダンプとは独自フォーマットであるデータベースを人が読めるファイルに書き出す処理を言います。
このツールはMySQLのログインコマンドの前、コマンドラインの段階で利用するツールです。
MySQLのダンプファイルをSQLの形式で出力するので別のサーバでそのSQLを実行する事でデータベースを復元する事が可能です。
その2：mysqlpump
こちらもmysqlのダンプを行う専用ツールですが、mysqldumpの後続ツールとしてMySQL 5.7.8以降のバージョンに同梱されております。mysqldumpにはない進捗表示したり並列処理などさまざまなオプションが追加されております。
こちらもコマンドラインからの実行により利用するツールです。
その3：percona xtrabackup
Percona社が公開しているオープンソースのバックアップツールとなります。
上記2つのツールと違う点としては、mysqldumpとmysqlpumpはどちらも論理バックアップでバックアップファイルをSQLの形式として出力するものですが、xtrabackupは物理バックアップとなります。リストア時の挙動として物理ファイルの配置とログの適用を行うのみなので、SQLの実行を伴う論理バックアップのリストアよりも高速にリストアできます。
その4：Instance Dump Utility and Dump Loading Utility
こちらはMySQLのバックアップツールの中でも比較的リリースタイミングが新しいです。
2020年7月13日にリリースされたMySQL Shellのバージョン8.0.21より機能追加されたバックアップのスレッドを並列化させてパラレルで実行する機能と、そのバックアップを同じくパラレルでインポートするユーティリティです。
MySQL Shellがインストールできる環境であれば、mysqlのバージョン8系に限らずmysqlバージョン5.7系でも利用できるという優れものです。
データベースやテーブルのDDL、バックアップに関するjsonファイル、データをTSV化してZstandardで圧縮したファイルが出力されます。
検証内容
今回バックアップツールの比較の為に行った検証としましては、同じデータベースから上記で紹介したそれぞれのバックアップ手法でデータベースのバックアップを取得し、別サーバへリストアするというものです。
検証DBとして、Zabbixというモニタリングツールで利用しているDBを今回の検証の対象としてみました。このDBはサイズもなかなか大きく更新頻度も高いので検証に適していると判断しました。
こちらは検証用に用意しているReplicaサーバとなります。
参考までにスペックは下記の通りとなります。
OS：CentOS7
MySQLバージョン：5.7.18
データベース容量：126GB
MySQL 127.0.0.1:3306 SQL > show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| tmp |
| zabbix |
+--------------------+
インストール方法の紹介は省きますが、それぞれのバックアップツールで実行したコマンドは下記です。
mysqldump
バックアップ取得コマンド
$ mysqldump --user root --password ******** --all-databases --hex-blob --flush-logs --single-transaction --triggers --routines --events | zstd > <バックアップ取得ディレクトリの絶対パス>/dump.sql.zst
リストアコマンド
$ zstdcat <バックアップ取得ディレクトリの絶対パス>/dump.sql.zst | mysql --user root --password ********
mysqlpump
バックアップ取得コマンド
$ mysqlpump --user root --password ******** --all-databases --hex-blob --single-transaction --triggers --routines --events | zstd > <バックアップ取得ディレクトリの絶対パス>/pump.sql.zst
リストアコマンド
$ zstdcat <バックアップ取得ディレクトリの絶対パス>/pump.sql.zst | mysql --user root --password ********
percona xtrabackup
バックアップ取得コマンド
$ sudo xtrabackup \
--backup \
--stream=xbstream \
--datadir=/var/lib/mysql \
--user=root \
--password=******** \
--parallel=4 \
--compress \
--slave-info \
--safe-slave-backup \
--socket=/var/lib/mysql/mysql.sock |xbstream -x -C "<バックアップ取得先ディレクトリの絶対パス>"
リストアコマンド
#xtrabackupの解凍
sudo innobackupex --decompress <バックアップ取得ディレクトリの絶対パス>
#ログの再構成 sudo innobackupex --user=root --apply-log <バックアップ取得ディレクトリの絶対パス>
#MySQLのデータディレクトリを移動
$ sudo xtrabackup --move-back --target-dir=<バックアップ取得ディレクトリの絶対パス> --datadir=<MySQLのデータディレクトリの絶対パス>
Instance Dump Utility and Dump Loading Utility
バックアップ取得コマンド
下記のコマンドはどちらもバージョン8.0.21以降のmysqlshにログインしての実行コマンドです
--dryRun確認
MySQL 127.0.0.1:3306 JS >util.dumpInstance( "<バックアップ取得ディレクトリの絶対パス>", {dryRun: true})
--util.dumpInstance()実行
MySQL 127.0.0.1:3306 JS >util.dumpInstance( "<バックアップ取得ディレクトリの絶対パス>")
リストアコマンド
--dryRun確認
MySQL 127.0.0.1:3306 JS >util.loadDump( "<バックアップ取得ディレクトリの絶対パス>", {dryRun: true})
--util.loadDump()実行
MySQL 127.0.0.1:3306 JS >util.loadDump( "<バックアップ取得ディレクトリの絶対パス>")
検証結果について
今回検証した結果について下記の観点から比較しました。
1. バックアップ・リストア実行時間
2. バックアップ取得時のサーバCPU負荷
3. バックアップファイル数とサイズ
バックアップ手法ごとのバックアップ・リストア時間について
バックアップにかかる時間の検証結果をバックアップ手法毎にまとめると下記のようになりました。
▼バックアップ時間の比較結果
▼リストア時間の比較結果
こちらの結果より、バックアップ・リストアどちらにおいてもスピードが最も速いのはpercona xtrabackupとなりました。さすが物理的なバックアップ！
mysqldump,mysqlpumpは結果にそれほど差はないのですが、Instance Dump Utility and Dump Loading Utilityはリストア時間が早いです。
Instance Dump Utility and Dump Loading Utilityにはバックアップとリストアをパラレルで実行できる機能がありますので、バックアップ・リストアの合計時間で見るとmysqlpumpが一番スピードが遅かったです。
バックアップ手法ごとのCPU負荷について
バックアップ時のCPU負荷について最も高くなった時の値をバックアップ手法毎にまとめると下記のようになりました。
▼バックアップ時のCPU負荷の比較
こちらの結果より、バックアップ時にCPU負荷が高まるのはInstance Dump Utility and Dump Loading Utilityという結果となりました。
サーバへのCPU負荷という観点から見ると、mysqldump,percona xtrabackupがほぼ同列でバックアップ時の負荷がかからないバックアップ手法となります。
バックアップ手法ごとのバックアップサイズについて
バックアップサイズをバックアップ手法毎にまとめると下記のようになりました。
▼出力されるバックアップサイズの比較
こちらの結果より、取得されるバックアップサイズが最も大きいのはpercona xtrabackupでした。それ以外については大差ないもののバックアップサイズが最も小さいのはInstance Dump Utility and Dump Loading Utilityでした。
バックアップのことじゃないけどどうしても紹介したいこと：圧縮形式zstd
今回MySQLのバックアップ検証を行うにあたり、zstdという圧縮形式を初めて知りました。
知るに至ったきっかけとしてましては、今回検証したバックアップ手法の一つで最も新しいInstance Dump Utility and Dump Loading Utilityのデフォルトの圧縮形式だったからです。
zstdはFacebookに所属するYann Collet氏によって開発された可逆圧縮アルゴリズムで、2016年8月にBSDライセンスでオープンソースソフトウェアとして公開されています。
この圧縮形式の売りは、公式ベンチマーク（ https://facebook.github.io/zstd ）より、圧縮展開速度が速く、圧縮効率も従来のものより改善されていることがあげられます。
全てのバックアップでは検証できていないのですが、mysqldumpのバックアップファイルに関しましては圧縮時にgzipを利用した場合と比べて圧縮速度が50%以上速くなりました。
詳細はまた別の記事でご紹介したいと思っているのですが、アイスタイルの既存のバックアップ運用にも取り入れられ運用改善できる可能性が高いので知ることができて良かったです。
検証結果からみる各バックアップ手法の特徴について
まとめると下記のような検証結果になります。
| 比較対象 | スピード | 負荷 | サイズ |
|---|---|---|---|
| mysqldump | △ | ◎ | 〇 |
| mysqlpump | △ | △ | 〇 |
| percona xtrabackup | ◎ | ◎ | △ |
| Instance Dump Utility and Dump Loading Utility | 〇 | △ | ◎ |
今回の検証結果を考察すると、それぞれのバックアップ手法における特徴について下記の事が考えられます。
percona xtrabackup
– 圧倒的スピード
– 低いCPU負荷
「バックアップもリストアも一番早いのが良い！負荷もかけたくない！」という運用者にとってはこちらを利用する事をオススメします。
Instance Dump Utility and Dump Loading Utility
– 操作の手軽さ
– バックアップファイルの圧縮率が高い
私見も含みますがコマンドが最も簡単でバックアップファイルの容量も抑えられるため、「バックアップの為に煩雑な操作をなるべくしたくない！バックアップファイルを一番小さくできるものが良い！」という運用者にとってはこちらを利用する事をオススメします。
mysqldump,mysqlpump
– バックアップが一つのファイルに収まる
– リストア時の修正・変更が容易
性能面では多少見劣りしますが、バックアップ出力ファイルが1つのSQLファイルとなるため、リストア後のデータベースの内容に修正を加えたい(データベース名やテーブル名を変えたい etc)場合はこちらのバックアップ手法が便利です。
さらには、論理バックアップの良さとしてデータベース・テーブルを再作成するので、ダンプ・リストア後にフラグメンテーションなどで肥大化したテーブルのサイズを適正なサイズにすることができることも利点としてあげられます。肥大化したデータベースの適正化が必要になった場合には論理バックアップのダンプ・リストアによる改善も検討してみてください。
まとめ
今回検証してみて、スピードや負荷、バックアップサイズなどのどの項目を重要視するのかによって利用すべきMySQLのバックアップ手法は柔軟に変えて利用するのが良いと思いました。
特に会社のデータベースのアーキテクチャによって変わってくると思います。
また、今回はDBインスタンス丸ごとバックアップを取得する方法でご紹介しましたが、データベース単位ごとに取得するなどを行えば、もっともそれぞれのDB運用に合わせた適切なMySQLバックアップの形を見いだせるのではないかと考えております。
さいごに
今回検証したタイミングは2020年12月時点のMySQLバックアップ技術の中から紹介しています。
今後さらに便利な機能を兼ね備えたツールが出てきてもっと運用が楽になる事を楽しみにしてます。パフォーマンスを上げるような細かいオプションを変更すればもっと良い結果を出せる可能性がありますのでその検証は引き続き行って、また新たな発見があったらご紹介したいです。
また、MySQL5.7系だけでなくMySQL8.0系のデータベースでも今後検証を行いたいと思います。
取られていて当たり前だけどないがしろにできないDBバックアップ、一番最適な形を探していきましょう！
