さくら共用サーバでmysqlをcronで定期バックアップをする

概要
さくら共用サーバでmysqlデータベースの自動バックアップを設定する際のメモ

環境
さくらレンタルサーバースタンダード
MySQL 5.7

解決

シェルスクリプトとオプションファイルを用意してアップロードし、cronを設定する。

以下、

シェルスクリプトファイル：db_backup.sh

オプションファイル：db_backup.conf

ファイル設置場所：/home/username/db_backup/

バックアップファイル保存場所：/home/username/db_backup/db_backups

として手順をまとめる。（赤字は実際のものと読み替えること）

1．シェルスクリプトファイル（db_backup.sh）の作成

以下を記述（username, db_nameは実際のものを使うこと）

---------------------------------------------

#!/bin/sh

# max backup days.

period=30

# backup directory.

dirpath='/home/username/db_backup/db_backups'

# make filename for new backup.

prefix="mysql_"

filename="${prefix}`date +%Y%m%d`.sql.gz"

# mysqldump using file options.

/usr/local/bin/mysqldump -defaults-file=/home/username/db_backup/db_backup.conf db_name | gzip > $dirpath/$filename

# change permission of the new file.

chmod 0600 $dirpath/$filename

# delete old file

oldfile="${prefix}`date -v-$(($period + 1))d +%Y%m%d`.sql.gz"

rm -f $dirpath/$oldfile

---------------------------------------------

2．オプションファイル（db_backup.conf）の作成

以下を記述し保存する。

---------------------------------------------

[mysqldump]

user=db_user_name

password=db_password

host=db_host

---------------------------------------------

3．上記ファイルをFTP等でアップロード

ディレクトリ ：/home/username/db_backup/を作成しへアップロードする。

（FTPでアクセスした時は/home/username/はルートディレクトリ になる）

また、ディレクトリ にバックアップデータ保存用ディレクトリ を作成する。

ファイル構成とアクセス権は以下

/home/username/db_backup/db_backup.sh (600)

/home/username/db_backup/db_backup.conf (400)

/home/username/db_backup/db_backup/db_backups/ (600)

4. ターミナルからsshでアクセスし、スクリプトの動作を確認する

ssh username@ username.sakura.ne.jp

cd /home/username/db_backup

./db_backup.sh

上記で/home/username/db_backup/db_backup/db_backups/にファイルが作成されていればOK。

5．さくらのCRON設定で/home/username/db_backup/db_backup.shを実行するように設定する。

以上。

ネット上にある情報の多くはシェルスクリプトにデータベース情報を記述しているものが多いが、データベース情報はオプションファイルとして分けることが推奨されているようなので、ここではオプションファイルにデータベースのホスト名、ユーザ名、パスワードを設置した。さくらのCRONも現在この方法でないと動作しないように思われる。

参考：

mysqldumpのコマンド

https://dev.mysql.com/doc/refman/5.6/ja/mysqldump.html#option_mysqldump_defaults-file

mysqldumpのオプションファイル記述方法

https://dev.mysql.com/doc/refman/5.6/ja/mysql-config-editor.html