停止せずにMySQLバックアップ！mysqldumpベストオプション

2022年4月22日2023年1月23日

URLをコピーしました！

データベースのバックアップって、いつも考えさせられますよね。
きっちりフルバックアップしたいなら、停止してデータディレクトリごとバックアップするのがベターだと思いますが、稼働中のサービスを停止したくない場合はダンプコマンドを利用します。
しかし、当然稼働中のデータベースはバックアップ中も読み書きが発生しているので、整合性の取れたデータのバックアップを取得するためには、色々と気を付けなければなりません。
そこで今回は、MySQLでmysqldumpコマンドを利用したバックアップのベストプラクティスをご紹介します！

mysqldumpによるMySQLのバックアップ

まずは、よく知らない方もいると思いますので、mysqldumpの使い方について簡単におさらいしておきましょう。
既にご存じの方は読み飛ばしちゃってください。
また、後術にもありますが、データベースエンジンはInnoDBになります。

mysqldumpの基本的な使い方

本来なら、ご自身の環境に合わせて様々なオプションを使ったりしますが、基本はこれですな。

1	mysqldump -u[ユーザー名] -p[パスワード] [データベース名] > [出力先ファイル名]

このコマンドに、停止せずにバックアップするための最適なオプションを3つ加えます。

mysqldumpに付けたい3つのオプション！

–opt
–single-transaction
–order-by-primary

1. [–opt]オプション

公式サイトではこのように書かれています。

このオプションはデフォルトで有効で、–add-drop-table –add-locks –create-options –disable-keys –extended-insert –lock-tables –quick –set-charset の組み合わせの短縮形です。
高速ダンプ操作が可能になり、MySQL サーバーに迅速にリロードできるダンプファイルを生成します。
引用：MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム

デフォルトで有効になっているので、明示的に指定する必要はないのですが、特に注目していただきたいのが[–-quick]が含まれている点です。
mysqldumpはメモリにバッファリングすることもできますが、大きなテーブルをダンプしているとメモリへの負荷が問題になります。
その問題を解決するため、バッファリングせずテーブルを1行ずつダンプする[–quick]オプションを使用します。
[–opt]を指定すれば、自動的に[–quick]が有効になります。（デフォルトでも有効ですが…）
ただし、ディスクIOに左右され、時間がかかるのではないかと思われます。

2. [–single-transaction]オプション

先程の[–opt]に含まれている[–lock-tables]オプションはダンプするすべてのテーブルをダンプ前にロックしますが、[–single-transaction]はテーブルをロックせずにトランザクションが開始された時点のデータベースの状態をダンプします。
ただし、前手条件として記述していた通リ、データベースエンジンはInnoDBに限ります。
公式サイトでもこのような記載があります。

大規模なテーブルをダンプするには、–single-transaction オプションを –quick オプションと組み合わせてください。
引用：MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム

大規模なダンプだと、その分処理に時間がかかって整合性が取れなくなるので、テーブルをロックせずにダンプできるのは嬉しいですね！

3. [–order-by-primary]オプション

最後は[–order-by-primary]オプションです。
こちらは各テーブルの行に存在する、主キーまたは一意のインデックスでソートしてダンプします。
つまり、バラバラのデータを綺麗に並び替えてダンプしてくれるオプションです。
しかし、公式サイトにはこのような注意書きもありますので、気を付けて使用しましょう。