●やりたい事と条件

ローカルサーバのDBのバックアップを自動でクラウド上のバックアップサーバにアップロードしたい
DBの中にあるデータベースは複数あるが、いちいち名前を指定したくない
無料でやりたい（クラウド代は別）
毎晩勝手にやってほしい

●事前準備

バックアップ元サーバとバックアップ先サーバをCentOS6系で構築し、グローバルIPアドレスと
FQDNをDNSサーバに登録して名前で引けるようにしておく。
バックアップ元サーバとバックアップ先サーバに同じ名前のバックアップ用ユーザを作成

sudo adduser backupuser
上記を両方のサーバで実行
3. バックアップ用ユーザにパスワード無しの公開鍵を作成する

su - backupuser
ssh-keygen
鍵は全てエンター押下のみで作成することによりパスワード無しになる
作成はどちらかのサーバだけで行えば良い、ここではバックアップ元で作成
4. バックアップ元からバックアップ先にバックアップ用ユーザの公開鍵を使用してパスワード無しでログインできるようにする
cd .ssh
cat id_rsa.pub
表示された公開鍵をコピペしておく
バックアップ先にbackupuserでログインして以下を実行
mkdir .ssh
chmod 700 .ssh
cd .ssh
vi authorized_keys
コピペしておいた公開鍵の内容を貼り付けて保存
(コピペミスして頭一文字切れるだけでログインできなくなるので注意）
5. バックアップ元で以下のコマンドを実行して何も聞かれずにログインできたら成功
ssh バックアップ先サーバ名
6. SSHのポートをデフォルト22から変更する
vi /etc/ssh/sshd_config
以下のコメントになっている行の数字をウェルノウンポート以外に変更して有効にします
#Port 22

●バックアップ用シェルスクリプト作成

スクリプトを全部は載せられないので、一部の要点のみ記載します。

■mysqldumpのオプション設定

--quote-names --skip-lock-tables --single-transaction --skip-dump-date
順番に意味を説明すると以下です。

識別子をバックティックで囲む
テーブルロックを防ぐ
ダンプを1トランザクション内で行うことで、テーブルをロックせずに整合性を保つ
出力日時だけ出力しない

■rsyncのオプション設定

-acvzu
オプションは定番中の定番なので、意味は割愛します。

■rsyncが使用するデフォルトSSHポートの指定

export RSYNC_RSH='ssh -p xxxx'
上記をスクリプトの中で定義しておくと変更したポートを使用してくれます。

■全データベース名の取得

find /var/lib/mysql/ -maxdepth 1 -type d -print | sed -e s"#/var/lib/mysql/##"g | tr '\012' ' '

上記コマンドは、ディレクトリのフルパス一覧をまず表示させて、sedでフルパスを消してディレクトリ名だけにして、trで改行コードをホワイトスペースに一括変換しています。
これをデータベースのリストとして、for文などで回して使用します。

■スクリプト全体の流れ

スクリプトの全体の動きとしては、取得したDB名全てに対してmysqldumpコマンドでダンプを出力して圧縮し、それを順次バックアップサーバにrsyncコマンドで同期していき、終わったら元の圧縮ファイルとダンプファイルは消します。
スクリプトは実行時にログファイルを日々バックアップ元サーバに残しておいて、それがあれば今日は終わっていると判断して何もしない処理を入れます。
またバックアップ先サーバに実行中はフラグとなるファイルを作成し、終わったら消します。
バックアップ元サーバが複数ある場合を考えて、そのフラグファイルがあったら、実行をスキップしてスクリプト終了します。

●自動実行の設定

自動実行にはcrontabを利用します。

su - backupuser
crontab -e
以下を記載して保存
* 3-4 * * * /home/backupuser/mysql_backup.sh >/dev/null 2>&1

上記のcrontabの説明は以下です。
毎日3時から4時の間1分おきにスクリプトを実行してその時の標準出力と標準エラー出力は/dev/nullに捨てる。

3時から4時にしているのは、台数が増えた時に他と実行が被っていたら、スキップするので、リトライさせる為です。

●おわりに

mysqlのデータを毎晩自動でバックアップできる仕組みを今回作ってみましたが、バックアップサーバの容量は無限ではないので、古いものは順次消していくような仕組みも必要だなぁと思いました。

バックアップを作成していないまま、業務に使うWEBアプリケーションを使い続けるのは非常に不安だったのですが、これで万が一の保険となってよかったです。