mysqlのデータのバックアップにgithubを利用する方法

#コンテンツ
0. 環境説明

1. DBバックアップ概要
2. DBバックアップ手順
   1. DB dump
   2. 暗号化処理
   3. Logrotate
   4. git push
3. リストア概要
4. リストア方法
   1. バックアップデータの取得
      1. githubのデータを利用する
      2. 本番サーバからゲットする
   2. リストアを実行する

#環境説明
mysql DB（Version 5.7）のDBを遠隔バックアップするためにGithubを利用してみたので、その作業メモを公開します。

#DBバックアップ概要

DBバックアップは、大まかにいうと下記の手順でそれぞれの処理を行っている。
mysqlの機能を利用し、laravelのDBをdumpして、（SQL文として）ファイルに書き出している。
githubを利用して、BackUpを行っている。プライベートリポジトリではあるが、暗号化処理を行ってから、githubにpushする。
githubでも世代管理はできるが、緊急時の速度性や作業ミスを誘発しないために、Linuxのlogrotateを利用して、世代管理も行う。
準備できたものをgitにpushしていく。

この工程をcronで実行している。

#DBバックアップ手順
##①DB dump
mysqlのdumpコマンドを利用している。

 /usr/bin/mysqldump  --defaults-extra-file=/home/ubuntu/.mylogin.cnf seeker > /home/ubuntu/DBBK/db_dump.sql

ポイントは、cronで実行するので、PW情報などを設定ファイル(.mylogin.cnf)で渡していること。

ubuntu@la01:~$ cat ~/.mylogin.cnf 
[client]
user = myuser
password = 'XXXXXXXX'

passwordは、伏字にしていますので、適宜、変更ください。

##②暗号化処理
opensslのコマンドを利用して、暗号化。

/usr/bin/openssl enc -e -aes-256-cbc -salt -k "XYZXYZXYZ" -in /home/ubuntu/DBBK/db_dump.sql -out /home/ubuntu/DBBK/db_dump.sql.enc

passwordは、伏字にしていますので、適宜、変更ください。

##③Logrotate
logrotateコマンドを利用している。

/usr/sbin/logrotate  /home/ubuntu/DBBK/logrotate.conf 2>/home/ubuntu/DBBK/logrotate_error

特にこだわりポイントもないが、confファイルも記載しておく。

~/DBBK$ cat logrotate.conf 

logrotate.conf

/home/ubuntu/DBBK/db_dump.sql.enc {
        rotate 10
        size 1k
        dateformat -%Y%m%d_%H
}
/home/ubuntu/DBBK/logrotate_error {
        rotate 10
        size 1k
        dateformat -%Y%m%d_%H
}
ubuntu@la01:~/DBBK$ cd
ubuntu@la01:~$ cat  /home/ubuntu/DBBK/logrotate.conf 
/home/ubuntu/DBBK/db_dump.sql.enc {
        rotate 10
        size 1k
        dateformat -%Y%m%d_%H
}
/home/ubuntu/DBBK/logrotate_error {
        rotate 10
        size 1k
        dateformat -%Y%m%d_%H
}

##④git push
ディレクトリを移動し、git add、git commitして、git push　（特になんてことはない
コマンドにすると

 (cd /home/ubuntu/DBBK && git add . && git commit -m "Automatic Commit" && git push)

gitの連携は、confファイルと鍵交換。

まずは、confファイルを編集する。
ユーザ名はGitHubUserName

ubuntu@la01:~/DBBK/.git$ cat config 

[core]
        repositoryformatversion = 0
        filemode = true
        bare = false
        logallrefupdates = true
[remote "origin"]
#       url = https://github.com/GitHubUserName/krow.git
        url = [e-mail]:GitHubUserName/krow.git
        fetch = +refs/heads/*:refs/remotes/origin/*
[branch "master"]
        remote = origin
        merge = refs/heads/master
[user]
        name = GitHubUserName
        email = [e-mail]

githubのSSHの鍵は
webにログイン後　settings>SSH and GPG keys
で設定できる。
この辺りはWebにいっぱいあるので、割愛。

#リストア概要
リストアに関する情報をまとめる。これは、ほぼリストアの手順書である。

#リストア手順
DBバックアップ情報を取得し、リストアを実行する

##DBバックアップ情報を取得
###githubのデータを利用する
githubからGetする方法を記載します。

ubuntu@la02:~$ pwd
/home/ubuntu
ubuntu@la02:~$ mkdir restore
ubuntu@la02:~$ cd restore/
ubuntu@la02:~/restore$ git clone https://github.com/GitHubUserName/krow.git

gitのcloneで得たファイルは暗号化処理させているので、復号化する。

 openssl enc -d -aes-256-cbc  -salt -k "XYZXYZXYZ" -in db_dump.sql.enc.1 -out db_dump.sql

※PWは、バックアップ時に設定したものを利用してください。

###本番サーバからゲットする

本番サーバのDBが生きていて、テスト環境などを作りたい場合のため、簡易に実行できるので、こっちも記載しておく。
本当の障害時は、本番サーバのDBがおかしくなっているだろうから、こちらは使えないかもしれないが。

コピー先サーバのアカウント:ubuntu
コピー先サーバのIPアドレス：203.0.113.2

@本番サーバでの作業

~$ cd DBBK/
~/DBBK$ scp db_dump.sql ubuntu@203.0.113.2:

##リストアを実行する
###①databaseの作成
@リストアサーバでの作業
rootでmysqlを実行する。（これは、mysqlのセキュリティ設定にも依存するが）

:~$ sudo su
[sudo] password for ubuntu: 
root@# mysql -u root

以下はmysqlでの作業

mysql> create database ladb2;
Query OK, 1 row affected (0.00 sec)

mysql> grant all on ladb2.* to 'myuser2'@'localhost' identified by 'hoge1234';
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> quit
Bye

ladb2というDatabaseをつくり、myuser2というユーザにladb2への変更権限等すべての権限を付与する。
但し、myuser2がアクセスする際にはパスワードが必要で、そのパスワードは'hoge1234'だよって設定をした。

root@la02:/home/ubuntu/restore/krow# exit

mysqlでの作業が終わったら、rootで作業することを止める。

###②SQLの流し込み

ubuntu@la02:~/restore/krow$ mysql -u myuser2 -p ladb2 <  db_dump.sql
Enter password: 

###③確認

:~$ mysql -u myuser2 -p ladb2
Enter password: 

以下は、データの確認方法。

mysql> show tables;

テーブルが見えたら、まあ、大丈夫でしょう。