mysqlのバックアップ(mysqldump)のロック問題

こんにちは、はらぐちです。

今回お話したいのは、mysqlのバックアップ方法についてのあれこれです。

バックアップ mysqldump

mysqlのバックアップといえばmysqldumpです。

以下のような形で使います。

1mysqldump -u root -p -x -A > my_dumpall.db
2

これで全データベースのダンプができます。

特定のデータベースをダンプしたい場合は、以下のようにデータベース名を指定します。

1mysqldump -u root -p -x データベース名 > dump.sql
2

定期的にバックアップを取りたい場合は、シェルスクリプトで以下のようなものを

cronで実行してあげるといいでしょう。

二日間のバックアップを保持するスクリプト例です。

1#!/bin/bash
2MPASS=パスワード
3mysqldump --defaults-extra-file=<(printf '[mysqldump]\npassword=%s\n' ${MPASS}) -u root -x -A > my_dumpall_`date +%Y%m%d`.db
4OLDDATE=`date "-d2 days ago" +%Y%m%d`
5rmfile=my_dumpall_$OLDDATE.db
6if [ -e $rmfile ]; then
7sleep 5m
8rm -f $rmfile
9fi
10

ちなみにリストアは以下のような形です。

1mysql -u root -p < dump.sql
2

1mysql -u root -p データベース名 < dump.sql
2

バックアップ datadirのコピー

mysqlのdatadirをOS上でコピーしてしまえばバックアップになります。

この方法の最大の利点は、リストアが非常に高速な事です。

通常のリストアはダンプファイル(SQL)をmysqlに渡す事で処理をするのでIndexの再作成などの処理もかかり、

mysqlのcpuがボトルネックとなってきますが、datadirによるリストアはdatadirのファイルを

そのまま置き換えてあげるだけですので、簡単かつ高速です。

しかし、同様に整合性の問題がありますので、mysqlを停止してからでなければいけないのが欠点です。

停止となるので完全に止まってしまいます。また、データベース単位でのバックアップなどはできません。

このバックアップ方法をとるのはサーバー移行などの高速なリストアが必要な時です。

mysqldumpのロック問題

mysqldumpコマンドのオプションで「-x」というものがありますが、

これはデータベースのすべてのテーブルをリードロックする、というものです。

データベースをリードロックすると参照系のクエリは発行できますが、

更新系のクエリはロックが解除されるまで待ち状態となります。

これはデータベースの整合性を保つ為に必要です。

しかし、サービス運用中ですと、この「更新系が待ち」になる事が許容できない事があります。

たかだか1Gくらいのデータベースですと、ミッションクリティカルなサーバーでない限り

ロックがかかる時間も少ないのでまだ許容できるかもしれませんが、

これが20GBほどのサイズとなると、30分ほどかかったりする事がざらにあります。

セッションをDBで管理してたりすると、すべてのユーザーがサービスを利用できなくなるというわけです。

ロック問題の解決策 single-transaction

データベースがすべて「innodb」であればシングルトランザクションオプションを使ったダンプを行う事で

ロックせずにバックアップする事ができます。

1mysqldump -u root -p --single-transaction -A > my_dumpall.db
2

内部的にはスナップショットをとって、そのデータをダンプする事でロックする事なく

整合性のとれたダンプを取る事が可能です。

通常のmysqldumpと異なる点は、ダンプデータが「ダンプが終了した時の状態」ではなく

「ダンプを開始した時の状態」であるという点です。

ちなみに–master-data=2を付けるとbinlogファイルと位置の情報をdumpに含めてくれるので

レプリケーションslaveを作る際に大変重宝します。

この方法の最大の欠点はすべてのデータがinnodbである必要がある点です。

myisamなどのテーブルは整合性が取れなくなる可能性があります。

ロック問題の解決策レプリケーションslaveにてdump

サーバーがもう一台必要ですがレプリケーションしたslaveサーバーにてバックアップを行うと、

ロックをかけてもサービスには影響を与えません。

ロック問題の解決策LVMのスナップショットでdatadirをコピー

こちらは実際には行った事がないのですが、LVMのスナップショットで

datadirのスナップショットを取ってしまえばmysqlの停止なくバックアップ可能です。

ですが、スナップショット作成中は非常に負荷が高くなるそうで、

該当時間の書き込み処理の性能がガタ落ちするそうです。

一番いいのはsingle-transaction

日々のバックアップ用途であれば今までの運用の経験から「single-transaction」オプションでの

mysqldumpが一番よかったかなぁと思います。

これからサーバーでmysqlを使う時は、意識的に「InnoDBを使う」という事を心がけていくといいと思います。