Masayan さんのところのデータベースがとんだのを期に高まる、MySQL バックアップ機運 🙂 ということで、シェル/cron をつかった MySQL バックアップ方法とか書いてみます。

phpMyAdmin とかその他プラグイン系でもバックアップはとれますが、データベースサイズが大きくなってくると http タイムアウトとの戦いになってきます。 また、バックアップ戻しも http アップロードから SQL 発行となりますので、ファイルのアップロード容量制限なども加わり難しい問題に直面しそうな雰囲気があります。

てなわけで、大抵のサーバは ssh の接続がサポートされていますので MySQL のバックアップ関連はこちらで操作するといいかもしれません。 まずは手動のシェル起動によるバックアップ、これがうまくいったら cron にこのシェルを登録してみます。

ここでは、XREA/CORESERVER でのやりかたです。 他のサーバでも大してかわらないでしょう。

まずシェルを配置するディレクトリをサーバ上につくります。 XREA/CORESERVER では Apache のドキュメントルートより上がつかえますのでここに FTP などでディレクトリを作成します。 public_html (ドキュメントルート)と同列にbackup-mysql としました。

で、そのディレクトリの中に以下のシェルを backup.sh として格納します。 このシェルは kohaku さんが使われているものを、設定しやすいように修正したものです。

#!/bin/sh

# 設定(ここを自分に合わせる)

DATABASE=another

DBUSERNAME=another

PASSWORD=********

XREAUSERNAME=another

BACKUPDIR=backup-mysql

KEEPDAY=14

# 初期化(XREA/CORESERVER用)

PREFIX=mysql

SERVER=localhost

NOWDATE=`date +%Y%m%d`

DESTDIR=/virtual/$XREAUSERNAME/$BACKUPDIR

DUMPFILE=$PREFIX.$NOWDATE.dump

TARFILE=$PREFIX.$NOWDATE.tar.gz

OLDDATE=`date "-d$KEEPDAY days ago" +%Y%m%d`

# MySQLダンプ/圧縮

cd $DESTDIR

/usr/local/mysql/bin/mysqldump $DATABASE --host=$SERVER -u $DBUSERNAME --password=$PASSWORD > $DUMPFILE

tar zcvf $TARFILE $DUMPFILE

# 処理判定

if [ $? != 0 -o ! -e $TARFILE ]; then

    echo "backup faild -- ($DUMPFILE)"

    exit 1

fi

# 圧縮前ファイル削除

rm -f $DUMPFILE

# n日ローテートを削除

rmfile=$DESTDIR/$PREFIX.$OLDDATE.tar.gz

if [ -e $rmfile ]; then

    rm -f $rmfile

fi

(2008/10/18 追記)

上記の mysqldump オプションであると、１テーブルに大量にデータがある場合に、mysql クライアントによってはうまくインポートできない場合があるようです。 また、テキストエディタで開いたときも、よこに長い行ができて操作しづらいため 以下にする指定して横の maxsize を指定すると良いようです。 ただしインポート速度は犠牲になります。

/usr/local/mysql/bin/mysqldump $DATABASE --host=$SERVER -u $USERNAME --password=$PASSWORD -e -O net_buffer_length=500 > $DUMPFILE

(2008/10/18 追記終わり)

DATABASE、USERNAME、PASSWORD を自分の MySQL アカウント・バックアップしたいデータベースに変更します。 バックアップファイル格納ディレクトリを backup-mysql 以外にしたときは BACKUPDIR も変更してください。

ファイルの改行コードは UNIX にしてください。 Linux をお使いの方はなにも考えないで FTP アップロードで OK です。

で、できたらこの backup.sh に FTP クライアントなどで実行権限をつけます。 700 にしましょう。

準備ができたら、サーバに ssh 接続しこのシェルを起動して実行確認します。 ssh 接続は Windows の方は以下を参考に・・・。

hiromasa.another :o) » Blog Archive » CORESERVER & XREA での SSH 接続

最近はレンタルサーバで SSH の接続を許可しているところも多くなりました。 SSH 経由で UNIX に接続できると、たとえば FTP では結構面倒な一括ファイル操作や、前のエントリで書いたような svn、また cron の設定とか、gcc を使った C ベースの CGI のコンパイルとかいろいろできるようになります。

Linux の方は、ホスト名登録後、端末から以下のコマンドをうつだけでつながります。

ssh -l [ユーザ名] [サーバ名]

ex. ssh -l another s8.coreserver.jp

という感じです。

で、つながったらカレントディレクトリを先ほどのシェル格納ディレクトリに移します。 コマンドは cd ですね。 🙂

cd backup-mysql

そこのコマンド全部入力しようとしたあなた！ TAB キーをつかいましょう。  "cd ba" くらいまでコマンド入れて TAB おすとコマンド補完してくれます。 ファイル名などの入力ではこれをつかうと間違わずに済みます。 ご活用を。 🙂

カレントディレクトリがうつったら、一応 ls で backup.sh があることを確認です。

another@s8:~> cd backup-mysql/

another@s8:~/backup-mysql> ls -laF

total 5

drwxr-xr-x  2 another hpusers  80 2008-10-11 16:03 ./

drwx---r-x  9 another hpusers 608 2008-10-11 15:30 ../

-rwx------  1 another hpusers 801 2008-10-11 15:16 backup.sh*

another@s8:~/backup-mysql> 

こんな感じなら OK です。 ではシェルを実行。 ./backup.sh と入力します。 ドットスラッシュってうって TAB おせば補完してくれるでしょう。 なにか動いたようであれば ls でファイル確認です。

another@s8:~/backup-mysql> ls -laF

total 1986

drwxr-xr-x  2 another hpusers     120 2008-10-11 16:04 ./

drwx---r-x  9 another hpusers     608 2008-10-11 15:30 ../

-rwx------  1 another hpusers     801 2008-10-11 15:16 backup.sh*

-rw-r--r--  1 another hpusers 2023812 2008-10-11 16:04 mysql.20081011.tar.gz

another@s8:~/backup-mysql> 

こんなふうに .tar.gz で圧縮されたファイルができていればまずは成功。 圧縮ファイルを FTP でダウンロードして中身に .dump ファイルがありますので、テキストエディタでひらいて SQL の内容を確認してください。

うまくいったらこのシェルを cron 登録します。 cron は 3分の実行時間制限があるようなので、もし上の手動起動で 3 分をこえるようであれば残念ながら自動実行はできません。 まー、いくらなんでも 3分ってことはないとおもうので、ほとんどの場合大丈夫でしょう。 シェルの方は 14 日前の古い .tar.gz を削除するようになっています。

さてバックアップをとったら、バックアップ戻しを知っておく必要があります。

MySQL の場合はエクスポートがただの SQL なので、ここでとった .dump ファイルを phpMyAdmin にたべさせても復元できますが、前述のとおりアップロード時間などの制限がありますので、こちらもシェルでやったほうがいいでしょう。

ここから先は、戻しのときだけ。 以下のコマンドでいけるハズです。

インポートデータベース先とか間違うと大変なことになりますので、細心の注意で緊急事態までやらぬよう・・・。 戻し先のデータベースは事前に空で作成しておきます。ローカルで MySQL 動かしている方は、サーバから取得した .dump で実験するといいと思います。

引数には .tar.gz の圧縮ファイルではなく中身にはいっている .dump をわたします。

mysql -u [ユーザ名] -p --default-character-set=utf8 [データベース名] < [バックアップ.dumpファイル]

ex. mysql -u another -p --default-character-set=utf8 another < mysql.20081011.dump

パスワードきーてくるので、MySQL のパスワードいれればOKです。

XREA/CORESERVER の場合は、Web サーバと MySQL が同一(localhost) なので、サーバのハードディスク自体がクラッシュするとこのバックアップもろとも飛んでしまうという弱点があります。 まぁこっそり RAID くらいしている気もするのでそうそうないと思いますが、ファイル化されても安心せずに定期的に FTP 取得してください。

本来なら GMail なんかに送信できるといいのですが、添付ファイルつきのメール送信でうまい方法を思いつかなかったので今回はとりあえず・・・。 PHP キックすればいいかな、、とか。

AutoMySQLBackup など高機能なシェルもありますので、こちらを使うのもよいかもしれません。 ただ mail コマンドをつかっているようで、GMail にはうまくとばすことができませんでした。

てなわけで、転ばぬ先のバックアップの巻でありました。