MySQLのバイナリログを活用しリストア＆リカバリで障害時でもDB完全復旧可能な体制を整える。

[mysqld]

# Binary log
server-id=1
log-bin=/var/log/mysql/bin_log/mysql-bin-log
log_bin_index=/var/log/mysql/bin_log/bin.list
max_binlog_size=256M
expire_logs_days=2

# GTID enable
gtid_mode=ON
enforce-gtid-consistency

# Related settings
innodb_flush_log_at_trx_commit=1
sync_binlog=1
sysdate_is_now

# 必要なディレクトリを作成する
[demo@localhost ~]# mkdir -p /var/log/mysql/bin_log
[demo@localhost ~]# chown -R mysql:mysql /var/log/mysql/bin_log

service mysqld restart

# mysqlbinlogコマンドでバイナリログを確認する
mysqlbinlog /var/log/mysql/bin_log/mysql-bin-log.000001

# mysqldumpコマンドでバックアップを取る
mysqldump --single-transaction --all-databases --triggers --routines --events --quick -u username -p > /path/to/backup.sql

# mysqldumpコマンドでバックアップを取る（cnffile指定）
mysqldump --defaults-extra-file=/path/to/.my.db.cnf --single-transaction --all-databases --triggers --routines --events --quick > /path/to/backup.sql

vim .my.db.cnf
##################################################
[client]
user=username
password="password"
host=192.168.XX.XX
##################################################

mysql> select * from numbers;
+-------------+
| test_number |
+-------------+
|          10 |
|          20 |
|          30 |
|          40 |
|          50 |
|          60 |
|          70 |
|          80 |
|          90 |
|         100 |
|         110 |
|         120 |
|         130 |
|         140 |
|         150 |
|         160 |
|         170 |
|         180 |
|         190 |
|         200 |
+-------------+
20 rows in set (0.00 sec)

mysql> select * from numbers;
+-------------+
| test_number |
+-------------+
|          10 |
|          20 |
|          30 |
|          40 |
|          50 |
|          60 |
|          70 |
|          80 |
|          90 |
|         100 |
|         110 |
|         120 |
|         130 |
|         140 |
|         150 |
|         160 |
|         170 |
|         180 |
|         190 |
|         200 |
|         210 |
|         220 |
|         230 |
|         240 |
|         250 |
|         260 |
|         270 |
|         280 |
|         290 |
|         300 |
+-------------+
30 rows in set (0.00 sec)

# バイナリログから適用させるものを出力
mysqlbinlog /var/log/mysql/bin_log/mysql-bin-log.000001 > /path/to/restore_bin.sql

# ダンプファイルからリストアを行う
mysql -u root -p bin_test < /path/to/mysql_dump.sql

# リストアを行った状態
mysql> select * from numbers;
+-------------+
| test_number |
+-------------+
|          10 |
|          20 |
|          30 |
|          40 |
|          50 |
|          60 |
|          70 |
|          80 |
|          90 |
|         100 |
|         110 |
|         120 |
|         130 |
|         140 |
|         150 |
|         160 |
|         170 |
|         180 |
|         190 |
|         200 |
+-------------+
20 rows in set (0.00 sec)

@@GLOBAL.GTID_PURGED can only be set when @@GLOBAL.GTID_EXECUTED is empty.

# gtid_executedの状態を確認
mysql> SHOW GLOBAL VARIABLES LIKE '%GTID%';
+----------------------------------+-------------------------------------------+
| Variable_name                    | Value                                     |
+----------------------------------+-------------------------------------------+
| binlog_gtid_simple_recovery      | ON                                        |
| enforce_gtid_consistency         | ON                                        |
| gtid_executed                    | 448d2ef6-3a1d-11e8-a41a-0800272bd935:1-27 |
| gtid_executed_compression_period | 1000                                      |
| gtid_mode                        | ON                                        |
| gtid_owned                       |                                           |
| gtid_purged                      |                                           |
| session_track_gtids              | OFF                                       |
+----------------------------------+-------------------------------------------+
8 rows in set (0.00 sec)

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin-log.000005
         Position: 6693
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set: 448d2ef6-3a1d-11e8-a41a-0800272bd935:1-27
1 row in set (0.00 sec)


# ログファイルを削除しポジションを初期化する
mysql> RESET MASTER;
Query OK, 0 rows affected (0.22 sec)


# 改めてgtid_executedの状態を確認
mysql> SHOW GLOBAL VARIABLES LIKE '%GTID%';
+----------------------------------+-------+
| Variable_name                    | Value |
+----------------------------------+-------+
| binlog_gtid_simple_recovery      | ON    |
| enforce_gtid_consistency         | ON    |
| gtid_executed                    |       |
| gtid_executed_compression_period | 1000  |
| gtid_mode                        | ON    |
| gtid_owned                       |       |
| gtid_purged                      |       |
| session_track_gtids              | OFF   |
+----------------------------------+-------+
8 rows in set (0.00 sec)

mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
             File: mysql-bin-log.000001
         Position: 154
     Binlog_Do_DB:
 Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)

# バイナリログを使ってリカバリを行う
mysql -u username -p < /path/to/restore_bin.sql

# バイナリログからリカバリを行った後
mysql> select * from numbers;
+-------------+
| test_number |
+-------------+
|          10 |
|          20 |
|          30 |
|          40 |
|          50 |
|          60 |
|          70 |
|          80 |
|          90 |
|         100 |
|         110 |
|         120 |
|         130 |
|         140 |
|         150 |
|         160 |
|         170 |
|         180 |
|         190 |
|         200 |
|         210 |
|         220 |
|         230 |
|         240 |
|         250 |
|         260 |
|         270 |
|         280 |
|         290 |
|         300 |
+-------------+
30 rows in set (0.00 sec)

# バイナリログを指定して直接リカバリ
mysqlbinlog /path/to/mysql-bin-log.000001 | mysql -u username -p

# mysqlユーザの作成
mysql> GRANT ALL ON *.* TO 'username'@'192.168.33.98' IDENTIFIED BY 'password';

# ユーザ確認
mysql> SELECT user,host FROM mysql.user;
mysqlbinlog_user | 192.168.33.98

vim binary_log_sync.sh
#######################
#!/bin/bash

mysqlbinlog --defaults-extra-file=/root/mysqlbinlog/.mylogin.cnf --read-from-remote-server --server-id=1 --stop-never --raw --result-file=/root/mysqlbinlog/ mysql-bin-log.000001 &
#######################

# ログイン用設定ファイル作成
vim .mylogin.cnf
#######################
[client]
user=username
password=passowrd
host=192.168.33.97
#######################

# シェルを実行する
sh binary_log_sync.sh

ps lc -p "PID"
#######################
F   UID   PID  PPID PRI  NI    VSZ   RSS WCHAN  STAT TTY        TIME COMMAND
0     0  3783     1  20   0  25672  2476 sk_wai S    pts/0      0:00 mysqlbinlog
#######################

# mysqlbinlogのプロセス管理
vim chk_mysqlbinlog.sh
#######################
while true
 do
  chk=`ps -ef | grep " mysqlbinlog " | \
  grep -v grep | wc -l`
  if [ $chk != 1 ]; then
   # プロセスが死んでいる場合
   sh /root/binary_log_sync.sh
   echo "再実行しました"
  fi
 sleep 3
done
#######################