MySQL のデータベースバックアップ

転ばぬ先のバックアップって PC88 のコピーツール、大昔ありましたよね。 気のせいですか?(挨拶

WordPress の MySQL に関するバックアップ手法のひとつを書いてみます。

現在の WP はコアに XML によるバックアップ機能がついていて、データ移行やアップグレード前のバックアップにはこれを使うのが確実そうです。

それ以外にも不測の事態というのはあるもので、バックアップは定期的に行っておくと、被害を最小限に抑えることができます。 フェイルセーフってやつです。 しかしながら、バックアップなんて動いているうちは不要とも言える作業なので、なるべくやりたくない。 

てなわけで、こういうのはコンピュータに自動的にやってもらうのが一番です。 WordPress のプラグインでも、スケジュールバックアップしてくれるのがいくつかあると思うのですが、この場合、WP がおちるとバックアップも落ちてしまうので、ぼくは MySQL の世界で定期バックアップをしています。

データベースのバックアップというのは、実は非常に奥が深くて、さらに、いわゆるデータベース起動中に行う、ホットバックアップ(オンラインバックアップ)というのはなかなかに難しい技術です。

なぜならばアプリケーションからのトランザクションを確定している状態で行わないと、データに不整合が起きてしまうからです。 最も簡単な例だと、テーブルA と B がリレーションしている場合で、A だけに書いた瞬間にバックアップしたら、システムがおかしくなるみたいなイメージです。

まぁ WP みたいな自分で使う Web アプリは更新処理が極端に少ないですし、自分で更新をコントロールすることができます(サイトメンテ中にすればよい)のであまり気にしなくてもいいのかもしれませんが、、一応 MySQL でホットバックアップをするにはどうすればいいのかを調べてみました。

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 4.5.1 データベースのバックアップ

MySQL テーブルはファイルとして保存されるため、バックアップが簡単です。一貫したバックアップを行うには、そのテーブルに対して LOCK TABLES を実行してから、FLUSH TABLES を実行します。 See 項6.7.5. 「LOCK TABLES および UNLOCK TABLES 構文」。 See 項4.6.4. 「FLUSH 構文」。 読み取りロックだけが必要なので、データベースディレクトリのファイルをコピーしている間も、他のスレッドはテーブルに対してクエリを続行できます。FLUSH TABLE は、バックアップを開始する前に、キャッシュされているページをすべてディスクに書き込むために必要です。

root をもって MySQL を構築しているのであれば、(たとえば XAMPP だと)

C:\xampp\mysql\data

この下のファイルがデータベースの実体で、ここをコピーしておけばバックアップが取れます。 コピー前に LOCK TABLE で読み取りロックをかけて、FLUSH TABLES でキャッシュを吐かせてからコピーすればトランザクションも確定されるようです。

これをいちいち手でやっていたら間違うので、MySQL にはこれを自動でやってくれる Perl のスクリプトがついています。

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 4.9.8 mysqlhotcopy(MySQL のデータベースとテーブルのコピー)

mysqlhotcopy は、LOCK TABLESFLUSH TABLES、および cp(または scp)を使用して、すばやくデータベースのバックアップを行う Perl スクリプトです。これは、データベースや単一のテーブルのバックアップを行う最速の方法ですが、データベースディレクトリのある同一マシンだけでしか実行できません。 mysqlhotcopy は、Unix のみ、および MyISAM テーブルと ISAM テーブルでのみ使用できます。

おー、これつかえばバイナリダンプできるんね、ってことでデータ領域からファイルコピーもしてきてくれそうなので、 CORESERVER でためしてみる。 ぽち。

permission denied.

予想はしていたが、無念。(笑

MySQL にはもうひとつバックアップの方法が用意されていて、 mysqldump というコマンドで SQL でデータをエクスポートする手段があります。(SQLテキストでダンプするのは個人的にはいかにもトラブりそうであまり好きではないのですが!)

MySQL AB :: MySQL 4.1 リファレンスマニュアル :: 4.9.7 mysqldump(テーブル構造とデータのダンプ)

バックアップ用のデータベースまたはデータベースの集合をダンプしたり、他の SQL サーバ(MySQL サーバである必要はない)にデータを移動するためのユーティリティです。ダンプには、テーブル作成や入力のための SQL ステートメントが含まれます。

CORESERVER でもこのコマンドは動きますので、これをうまいことシェルにして cron に登録すれば履歴でバックアップを自動的にとることができます。 (これがテーブルロックしているかどうかは分かりませんでした)

JSeries で使っている sourceforge でも、ぼくが適当にかいたシェルで1週間に一度 WordPress のデータベースのバックアップがとられています。 (自分のホームディレクトリに格納されていますので、ぼくに何かあったらホームをさぐってください。> JSeries 関係者のみなさま)

mysqldump を利用して高度なバックアップをとりたい場合は、次のようなちゃんとしたシェルをかぶせて使うといいかもしれません。

SourceForge.net: AutoMySQLBackup

A script to take daily, weekly and monthly backups of your MySQL databases using mysqldump. Features – Backup mutiple databases – Single backup file or to a seperate file for each DB – Compress backup files – Backup remote servers – E-mail logs – More..

bash 動くサーバなら、おそらく動くのではないかと思います。

転ばぬ先のバックアップ。 あ、逆に、バックアップ取得しようとして誤って消したりしないようにご注意ください。 :-)

via ワードプレスとデータベースとバックアップ – ひかげStyle

MySQL のデータベースバックアップ」への7件のフィードバック

  1. おはようさんです
    僕の場合この手の作業を導入する前にバックアップが必要です(笑)
    結構難しそうですが、どれがおすすめでしょう?

  2. こんにちはー。
    なかなかバックアップって情報はあっても、実行しないことが多いですよね。(^^;
    私も、Coreserverでautomysqlbackup.shを少し修正してcronでバックアップ取っているのですが、ひろまささんの書いたシェル見たいっす。

  3. こんにちは~。

    washo さんの wiki、分かりやすくていいですね!
    実績のある automysqlbackup.sh、良いかもしれないです~。 > yutaka さん

    ぼくのシェルは、dump して日付で名前つけて、gzip するだけの 5行くらいのひどいものでして…。

    ローテートもさせてなくて、たまる一方の危険な物なので、後日 wiki 参考にさせていただいて automysqlbackup.sh に乗り換えてみます!

  4. 私のwikiなんて、忘れないようにメモ書きしてるだけなので、他の人が見たら、わかりづらいだろうなぁって自分でも思ってるのですが。(^^;
    シェルの件ですが、”dump して日付で名前つけて、gzip するだけ”って事で、何となくわかりました。
    mysqlのバックアップ方法で、また、良い方法がありましたら、教えて下さいね。

  5. いえいえ、よく理解できました!。
    自前でもちょっとやってみようかなと計画中です。 🙂

  6. ひろまささん、こんばんは~

    ただいま高知に帰ってまいりましたひかげです。

    遅ればせながら、トラックバックありがとうございました。

    バックアップがもう少し楽になると良いのですが
    実力不足のひかげです。

    でも、手動で頑張っていきたいと思います。

  7. ひかげさん、こんにちは~。
    おお、東京に行ってらっしゃったんですね! おかえりなさい。 🙂

    実は話のネタに PHP でバックアップシェルの代わり書いてみようと思って、手つかずでした。。 ダンプをメールで送ろうとして、、なにかライブラリの使い方をみて挫折した覚えが。。(笑

    こんどまた挑戦してみます!

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です