この世の全てを自動化してぐうたら生活したすぎる

煩悩八百万クリエイター戦記

MySQL

EC2のnano環境でMySQL(MariaDB)構築+自動バックアップ(フル+差分)を実現する

更新日:

サイトを運営していると、もしものためにDBを複製しておくことはよくやりますよね。AWSのRDBも自動的にActive・Standby構成をとって、両方とも常に最新状態にしておいて、Active状態のDBがお亡くなりになった時に、自動的にStandby状態のDBをActiveにするみたいなことを普通にやれます。

ただ、常に2つサーバー起動するので費用面で問題があります。大したことないサーバ運営をしているのに、ここにそんなにコストをかけたくないみたいなところがあります。(本当はココこそコストをかけるところですが)

AWSのRDSで2サーバー構成をすると費用はいくらか

最小インスタンスでも、RDSのMySQLは「db.t2.micro」なので、一ヶ月2000円程/1サーバかかります。つまり冗長構成をとると、データベースで4000円かかります。(参考:https://qiita.com/saitotak/items/cdbbdb1be80912f8bb18)

とりあえず作ったアプリでDBに4000円も払いたくない

t2.nanoにMySQL(MariaDB)を入れて、バックアップを回す

1ヶ月800円…!規模が大きくなってきたらスケールアウトもしくはRDBに移行すればいいので、ひとまずはこっちで構築します。

EC2環境立ち上げ

EC2の新規作成にてサーバを立ち上げます。Amazon Linux 2 AMIで作りましたが他のRedHat系のディストリビューションでもおおよその流れは同じかと思います。

今回はnano環境で作っています。nano環境でMySQL(MariaDB)を立ち上げようとすると、立ち上がらないことがあるようで(どうやらメモリ不足)、このEC2のnano環境でのMySQLエラーを解消する方法もお伝えします。

MariaDB(10.2)のインストール

# EC2(Amazon Linux 2 AMI)でnano環境を立ち上げてSSHで入った直後
sudo su -

### MariaDB(10.2)のインストール ###
cat > /etc/yum.repos.d/mariadb.repo <<EOF
[mariadb] name = MariaDB
baseurl = http://yum.mariadb.org/10.2/centos7-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
EOF

yum install -y MariaDB-server MariaDB-client

### MariaDB設定 ###
cat > /etc/my.cnf.d/utf8mb4.cnf <<EOF
[mysql]
default-character-set=utf8mb4
[mysqld]
binlog_format = row
log-bin = mysql-bin
expire_logs_days = 3
character-set-server = utf8mb4
collation-server = utf8mb4_general_ci
init-connect = SET NAMES utf8mb4
performance_schema = off
EOF

### MariaDBの自動起動設定 ###
systemctl enable mariadb

### MariaDBの起動 ###
service mysqld restart

### ユーザ作成 ###
mysql -u root 
# mysqlに入った状態で下記コマンドを実行
GRANT ALL PRIVILEGES ON *.* TO 'ユーザ名'@'%' IDENTIFIED BY 'パスワード';
quit

 

上の「performance_schema = off」がnano環境でMySQLを構築するのに必須です。これは、SQLパフォーマンスを計測してより良いシステムにするのに有用なのですが、すごくメモリを食います。nano環境ということで、パフォーマンスを気にしていない皆様かと思いますので、思い切ってoffにしてしまいましょう。

自動バックアップの設定

バックアップ自体のコマンドはmysqldumpを活用します。

mysqldump --single-transaction --complete-insert --default-character-set=utf8mb4 --master-data=2 --flush-logs -u ユーザ名 -h ホスト名 -pパスワード バックアップしたいデータベース名 > バックアップファイルパス

このコマンドをcronで深夜に定期実行するなり、Railsの場合はWheneverに登録(内部はcronが動いてる)するなりすればOKです。

/etc/crontabファイルに

0 4 * * * root mysqldump --single-transaction --complete-insert --default-character-set=utf8mb4 --master-data=2 --flush-logs -u ユーザ名 -h ホスト名 -pパスワード バックアップしたいデータベース名 > バックアップファイルパス

こんな感じで書いておけばOKです。

 

これだと、定時バックアップだから、何かトラブルがあったときに、その日の深夜までは戻せるけど、日中のデータが戻せないじゃないというのがあります。ここも満たしたいシステムは、キチンと2サーバ動かしておきましょう…と言いたいところですが、一応最新状態まで復活させる方法があります。

実はこの定時バックアップファイルに加えて、DB内に保存されている、実行されたコマンドを使って最新状態に持っていきます。下記で説明します。

バックアップをする時

このあたりはシステムの状態にもよるので、皆様の環境に合わせてアレンジすると良いと思います。私のシステムではこんな風に書いたりしました。cronではなく、RailsのWheneverをつかっているので、cronの人はwheneverの行は不要です。また、delayed_jobもつかってない人は不要です。

### アプリサーバ側操作 ###
# 前準備
cd /var/app/current # Railsアプリがあるディレクトリに移動

# バックエンドプロセスの停止
bundle exec whenever --clear-crontab # 念の為Wheneverをクリア
service crond stop # cronの停止
bin/delayed_job stop # delayedバックアップというバックエンドプロセスも動いていたので停止

# バックアップを用意
# db_backupというディレクトリ配下にtest_backup.dump(例)というバックアップデータをおきます。

# フルバックアップ
backup_db_path="db/test_backup.dump"
master_log_file=`cat $backup_db_path | grep -i chang | sed -e "s/.*MASTER_LOG_FILE='\(.*\)', MASTER_LOG_POS.*/\1/g"` && echo $master_log_file
mysql --default-character-set=utf8mb4 -u ユーザ名 -pパスワード -h ホスト名 データベース名 < $backup_db_path


### 差分バックアップ ###
# DBに接続し差分をバックアップ
# (さっきの$master_log_fileという変数を利用)
# (sshでコマンドを流し込む必要はないので、直接入ってもOKです)
ssh DBサーバのユーザ@DBサーバホスト名 'ls'
ssh DBサーバのユーザ@DBサーバホスト名 "ls -al db_backup/$master_log_file"
ssh DBサーバのユーザ@DBサーバホスト名 "sudo mysqlbinlog db_backup/$master_log_file | mysql -u root"

### 後処理 ###
# バックエンドプロセスの開始
bin/delayed_job start
bundle exec whenever --update-crontab
service crond start

このあたりは複雑なので、実際のシステムでやる前に、予行演習をしておいておくことをオススメします。

 

おわりに

意外と面倒なので、お金に余裕のある人は、RDBの機能つかって、簡単にバックアップをしちゃいましょうw

RDBの1台構成でも定時バックアップは出来ますよ。

f-arts336

f-arts336

-MySQL

Copyright© 煩悩八百万クリエイター戦記 , 2018 All Rights Reserved.