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

mysqldump-best-optionテクノロジー

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

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

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

mysqldumpの基本的な使い方

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

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

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

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

  1. –opt
  2. –single-transaction
  3. –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]オプションです。
こちらは各テーブルの行に存在する、主キーまたは一意のインデックスでソートしてダンプします。
つまり、バラバラのデータを綺麗に並び替えてダンプしてくれるオプションです。
しかし、公式サイトにはこのような注意書きもありますので、気を付けて使用しましょう。

ダンプ操作にかかる時間がかなり長くなります。

引用:MySQL :: MySQL 8.0 リファレンスマニュアル :: 4.5.4 mysqldump — データベースバックアッププログラム

そりゃそうだわな。
整形しながらダンプするから、確かに時間はかかると思います。

【まとめ】mysqldumpの完成形!

以上の内容を踏まえて完成したコマンドがこちらです!

mysqldump --opt --single-transaction --order-by-primary -uUSERNAME -pPASSWORD > /Path/to/file.dump

以上、ぜひ試してみてください!

東京生まれ福岡育ちの文系プログラマー。
テクノロジーの恩恵を感じながら日々精進しています。
広く浅くをモットーに、最近ではプログラミングだけでなく、仮想サーバーからセキュリティ、監視システムなども勉強中です。

管理者をフォローする

コメント

タイトルとURLをコピーしました