2021/10/27 更新

初出から3年ほど経過していますが、基本的には、2021年現在もこの記事のままで動作するはずです。

経緯

中小企業や個人ユースでお馴染み、さくらレンタルサーバ（スタンダードプラン）。
今までメンテのときについでで手動バックアップを取っていましたが、かえって面倒くさくなってきたので、真面目に自動バックアップを取ることにしました。普通は自動化しているのが当たり前だろとかいうのはその通りです、すいません。

さくらのレンタルサーバでデータベースを自動的にバックアップして、いざというときに復元する
こうなる前に。
自分もマジで、いつかやらかす自信だけはあります。

なお、さくらの場合、サーバコントロールパネルからcronを設定できるのは5個までという制限がありますが、個人に毛が生えたクラスの案件で、cronを10個も20個も動かすとかほぼ無いので、ここは素直にコントロールパネルで設定してみることにします。

バックアップ自動化の手順

1. ディレクトリを作成

設置場所は、/home/アカウント 以下好きなように決めて良いと思いますが、ここでは下記のように設定しました。

/home/アカウント/www と同じ階層にバックアップ用ディレクトリを作成。

$ mkdir backup

同じ階層にスクリプト用ディレクトリも作成して移動。

$ mkdir script && cd ./script

2. シェルスクリプトを作成

ファイルを作成。今回はシェルスクリプトにしました。
（PHPスクリプトで動作させる方法もありますが、Qiita上でもちらほらと「大変だった」という体験談が見受けられますので、勉強がてらシェルスクリプトを書いてみるか、下記コピペの方が楽だと思います）
さくらのレンタルサーバのCRONでmysqldump、がすんなりできなかったお話

※「アカウント」「パスワード」「ホスト名」「データベース名」は適宜お使いの情報に置き換えてください。

$ vi backup.sh

mysql_backup.sh

#!/bin/sh

# MySQL のバックアップ

# バックアップの保存期間（1ヶ月）
period=31

# バックアップを保存するディレクトリ
dirpath='/home/アカウント/_backup'

# ファイル名
prefix="mysql_"
filename="${prefix}`date +%Y%m%d`.sql.gz"

# mysqldump
# gzipで圧縮する
/usr/local/bin/mysqldump -u アカウント -pパスワード -h ホスト名 データベース名 | gzip > $dirpath/$filename

# パーミッションの変更
chmod 0600 $dirpath/$filename

# 古いバックアップファイルを削除
oldfile="${prefix}`date -v-$(($period + 1))d +%Y%m%d`.sql.gz"
rm -f $dirpath/$oldfile

もしくはローカルでファイルを作成して、上記コピペ後 /home/アカウント/script にアップロード。

3. スクリプトのパーミッションを変更

スクリプトにはDBの接続パスワードが書かれていますので、他のユーザからはファイルの中身を覗けないようにしておきます。

$ chmod 0600 mysql_backup.sh

4. 動作確認してみる

$ sh /home/アカウント/script/mysql_backup.sh
$ ls -al ../backup/
# 下記のようなファイルができていればOK
mysql_20181125.sql.gz

# 問題なければ一旦削除
$ rm -rf mysql_20181125.sql.gz

5. cron設定

「サーバコントロールパネル」画面左のメニューから「CRONの設定」メニューをクリック。
「CRON設定一覧」画面右下の「>> 新規項目の追加」リンクをクリック。
「実行コマンド」「実行日時」を入力。毎日04:00に実行したいので下記のように指定。入力が完了したら、「送信」ボタンをクリック。（postmasterにメールを飛ばしたい場合は、 1>/dev/null を書かないでおく）

項目名設定値

実行コマンド sh /home/アカウント/script/mysql_backup.sh 1>/dev/null

月 *

日 *

時 4

分 0

曜日全て未選択
「CRON設定一覧」で今設定したコマンドが登録されていることを確認。
一晩待ってみましよう。バックアップが走っていれば、/home/アカウント/backup ディレクトリにバックアップファイルができているはず。
※できていなければ、shスクリプトの見直し、対象ディレクトリのパーミッションを再度チェックするか、1>/dev/null の記述がある場合は、そちらを外してpostmasterにメールを飛ばしてエラー等出ていないか確認してみましょう。

おまけ

sh のパスを確認

$ which sh
/bin/sh

実行コマンドに単にファイル名だけ書いて実行させたい場合

$ chmod 0700 mysql_backup.sh

項目名	設定値
実行コマンド	/home/アカウント/script/mysql_backup.sh

gzip圧縮したファイルを解凍せずに中身を見る場合は以下コマンド。

$ zcat xxxxx.sql.gz

解凍する場合は以下コマンド。

$ gunzip xxxxx.sql.gz