Oracle MySQL Cloud ServiceでMySQLインスタンスのPoint In Time Recoveryを実行
MySQLCS (MySQL Cloud Service)
ダッシュボードから時間指定でのリストアを実行した時に、バックグラウンドで動いているプロセスの確認になります。

STEP1) 既存データをフルバックアップ
STEP2) 差分データのINSERT
STEP3) 差分バックアップ
STEP4) データの削除
STEP5) STEP3の直前まで時間指定でリストア
STEP6) データの確認

■ FULL BACKUP実行時のデータ

mysql> show tables;
+--------------+
| Tables_in_SC |
+--------------+
| T_POT_TEST   |
+--------------+
1 row in set (0.00 sec)

mysql> select * from T_POT_TEST;
+----+-----------+---------+
| id | body      | type    |
+----+-----------+---------+
|  1 | 123456789 | INTEGER |
|  2 | "abcde"   | STRING  |
+----+-----------+---------+
2 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-01-27 14:20:56 |
+---------------------+
1 row in set (0.00 sec)

■ FULLバックアップ

mysql> select * from backup_history order by backup_id desc limit 1\G
*************************** 1. row ***************************
                backup_id: 14855264590951390
                tool_name: /u01/bin/meb/bin/mysqlbackup --user=oracle --backup-dir=/u01/backup/MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d --backup-image=backufull.mbi --compress backup-to-image 
               start_time: 2017-01-27 14:14:19
                 end_time: 2017-01-27 14:14:28
               binlog_pos: 154
              binlog_file: opc-mysql01-mysql-1.000001
        compression_level: 1
                  engines: CSV:InnoDB:MyISAM:PERFORMANCE_SCHEMA
    innodb_data_file_path: ibdata1:12M:autoextend
       innodb_file_format: Barracuda
                start_lsn: 6623068672
                  end_lsn: 6623068712
     incremental_base_lsn: 0
              backup_type: FULL
            backup_format: IMAGE
           mysql_data_dir: /u01/data/mysql/
     innodb_data_home_dir: 
innodb_log_group_home_dir: /u01/translog/inno-bin-logs
innodb_log_files_in_group: 6
     innodb_log_file_size: 1073741824
       backup_destination: /u01/backup/MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d
                lock_time: 0.809
               exit_state: SUCCESS
               last_error: NO_ERROR
          last_error_code: 0
1 row in set (0.01 sec)

■ FULL BACKUP後にデータをINSERT

mysql> insert into T_POT_TEST(body) values('"PITR TEST"');
Query OK, 1 row affected (0.11 sec)

mysql> select * from T_POT_TEST;
+----+-------------+---------+
| id | body        | type    |
+----+-------------+---------+
|  1 | 123456789   | INTEGER |
|  2 | "abcde"     | STRING  |
|  3 | "PITR TEST" | STRING  |
+----+-------------+---------+
3 rows in set (0.00 sec)

mysql> select now();
+---------------------+
| now()               |
+---------------------+
| 2017-01-27 14:21:46 |
+---------------------+
1 row in set (0.00 sec)

mysql> 

■ ここで差分を取得してみる。

mysql> select * from backup_history order by backup_id desc limit 1\G
*************************** 1. row ***************************
                backup_id: 14855271179669251
                tool_name: /u01/bin/meb/bin/mysqlbackup --user=oracle --backup-dir=/u01/backup/MySQLCS/OPC-MYSQL01/scheduledIncremental/63412628-3fcd-4b60-9e9d-ef1bacc15032 --backup-image=backup_incremental.mbi --incremental_base=dir:/u01/backup/MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d --incremental backup-to-image 
               start_time: 2017-01-27 14:25:17
                 end_time: 2017-01-27 14:25:21
               binlog_pos: 19807766
              binlog_file: opc-mysql01-mysql-1.000002
        compression_level: 0
                  engines: CSV:InnoDB:MyISAM:PERFORMANCE_SCHEMA
    innodb_data_file_path: ibdata1:12M:autoextend
       innodb_file_format: Barracuda
                start_lsn: 6623068713
                  end_lsn: 6633728074
     incremental_base_lsn: 0
              backup_type: INCREMENTAL
            backup_format: IMAGE
           mysql_data_dir: /u01/data/mysql/
     innodb_data_home_dir: 
innodb_log_group_home_dir: /u01/translog/inno-bin-logs
innodb_log_files_in_group: 6
     innodb_log_file_size: 1073741824
       backup_destination: /u01/backup/MySQLCS/OPC-MYSQL01/scheduledIncremental/63412628-3fcd-4b60-9e9d-ef1bacc15032
                lock_time: 0.684
               exit_state: SUCCESS
               last_error: NO_ERROR
          last_error_code: 0
1 row in set (0.00 sec)

mysql> 

■テーブルからデータを削除します。

mysql> truncate table T_POT_TEST;select * from T_POT_TEST;select now();
Query OK, 0 rows affected (0.02 sec)

Empty set (0.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2017-01-27 14:28:32 |
+---------------------+
1 row in set (0.00 sec)

mysql> 

■ 目標リストア時間を指定してリストア

2017-01-27 14:21:45

※こちらの画像はサンプルですので、時間は上記の時間を指定しました。

■ダッシュボードから時間指定でリストア開始
STEP1) フルリストアが実行されている


[oracle@opc-mysql01-mysql-1 ~]$ ps -ef | grep mysql
oracle   14423 14348 20 14:31 ?        00:00:05 /u01/bin/meb/bin/mysqlbackup --user=oracle --cloud-service=openstack --cloud-user-id=Storage-xxxxxxx:xxxxx.xxxxx@oracle.com --cloud-password=xxxxxxxxxx --cloud-tempauth-url=https://xxxxxxx.storage.oraclecloud.com --cloud-container=mysqlstorage --cloud-object=MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d/backup_full.mbi --backup-dir=/u01/backup/MySQLCS/OPC-MYSQL01/scheduledFull/e27856f6-25d7-45d8-9214-a8a3eb899d0d --cloud-ca-info=/etc/ssl/certs/ca-bundle.crt --datadir=/u01/data/mysql-14348 --innodb_log_group_home_dir=/u01/translog/inno-bin-logs-14348 --backup-image=- --uncompress --force copy-back-and-apply-log
oracle   14459 10312  0 14:32 pts/0    00:00:00 grep mysql
[oracle@opc-mysql01-mysql-1 ~]$ 

STEP2) 差分リストアが実行されている

[oracle@opc-mysql01-mysql-1 ~]$ ps -ef | grep mysql
oracle   14509     1  0 14:33 ?        00:00:00 /bin/sh ./bin/mysqld_safe --skip-networking
oracle   15593 14509 25 14:33 ?        00:00:06 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --skip-networking --log-error=/u01/data/mysql/opc-mysql01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/opc-mysql01-mysql-1.pid
oracle   15721 14348  0 14:33 ?        00:00:00 /bin/sh -c /u01/bin/mysql/bin/mysqlbinlog --skip-gtids --start-position=154 --stop-datetime="2017-01-27 14:21:45"  /u01/backup/temp/datadir/opc-mysql01-mysql-1.000001 /u01/backup/temp/datadir/opc-mysql01-mysql-1.000002|/u01/bin/mysql/bin/mysql -uoracle 
oracle   15722 15721  1 14:33 ?        00:00:00 /u01/bin/mysql/bin/mysqlbinlog --skip-gtids --start-position=154 --stop-datetime=2017-01-27 14:21:45 /u01/backup/temp/datadir/opc-mysql01-mysql-1.000001 /u01/backup/temp/datadir/opc-mysql01-mysql-1.000002
oracle   15723 15721  3 14:33 ?        00:00:00 /u01/bin/mysql/bin/mysql -uoracle
oracle   15729 10312  0 14:34 pts/0    00:00:00 grep mysql
[oracle@opc-mysql01-mysql-1 ~]$ 

STEP3) MySQLの起動

[oracle@opc-mysql01-mysql-1 ~]$ ps -ef | grep mysql
oracle   15811     1  1 14:35 ?        00:00:00 /bin/sh ./bin/mysqld_safe
oracle   16875 15811 28 14:35 ?        00:00:00 /u01/bin/mysql/bin/mysqld --basedir=/u01/bin/mysql --datadir=/u01/data/mysql --plugin-dir=/u01/bin/mysql/lib/plugin --log-error=/u01/data/mysql/opc-mysql01-mysql-1.err --open-files-limit=20000 --pid-file=/u01/data/mysql/opc-mysql01-mysql-1.pid
oracle   16974 10312  0 14:35 pts/0    00:00:00 grep mysql
[oracle@opc-mysql01-mysql-1 ~]$ 

※ リストアが問題無く終わると自動的にFULLバックアップの取得が開始されます。

■リストア後のデータ確認


mysql> select * from T_POT_TEST;select now();
+----+-------------+---------+
| id | body        | type    |
+----+-------------+---------+
|  1 | 123456789   | INTEGER |
|  2 | "abcde"     | STRING  |
|  3 | "PITR TEST" | STRING  |
+----+-------------+---------+
3 rows in set (0.00 sec)

+---------------------+
| now()               |
+---------------------+
| 2017-01-27 14:36:26 |
+---------------------+
1 row in set (0.00 sec)

mysql> 


MYSQLにおけるバイナリーログ切り替え、操作、内容確認、リカバリーに関しての取りまとめ。

ログスイッチ
オラクルでオンラインバックアップを利用していた時には、バックアップの最後でREDOログスイッチを行ってArchiveログに書き込んで、
バックアップ中の処理も含めて確実にリストア出来る状況にしていましたが、MYSQLでもバイバリーログを同様にSwitchして同様に処理を
ログに書き込む事が出来ます。MYSQLDUMPのオプションでも(–flush-logs)を利用する事が出来ます。

ログFLUSHとSwitch

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.49 sec)

mysql> FLUSH LOGS;
Query OK, 0 rows affected (0.48 sec)

mysql>

flash-log

ログの中身を確認
2009年にもmysqlbinlogでログ操作にて一度検証した内容を改めて確認。

バイナリーログなのでCATでそのまま確認するのは難しいので以下のmysqlbinlogコマンドにて確認。


[root@HOME001 data]# mysqlbinlog --no-defaults mysql-bin.000034 | grep TABLE001
insert into TABLE001(title,comment) values('LOG20120111','after mysql-bin.000034')
insert into TABLE001(title,comment) values('LOG20120111','after mysql-bin.000034')
insert into TABLE001(title,comment) values('LOG20120111','after mysql-bin.000034')
[root@HOME001 data]#

mysqlbinlog

特定の時間以降のSQLコマンドを抜粋
リストアに利用可能:

mysqlbinlog --no-defaults --start-datetime="2013-01-13 15:19:00" mysql-bin.000034 > /tmp/today_recover.sql
[root@HOME001 data]# mysqlbinlog --no-defaults --start-datetime="2013-01-13 15:19:00" mysql-bin.000034
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130113 14:16:55 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.29-log created 130113 14:16:55
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
R0PyUA8BAAAAZwAAAGsAAAABAAQANS41LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 1179
#130113 15:19:02 server id 1  end_log_pos 1247  Query   thread_id=4     exec_time=0     error_code=0
SET TIMESTAMP=1358057942/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 1247
#130113 15:19:02 server id 1  end_log_pos 1275  Intvar
SET INSERT_ID=23/*!*/;
# at 1275
#130113 15:19:02 server id 1  end_log_pos 1424  Query   thread_id=4     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1358057942/*!*/;
insert into TABLE001(title,comment) values('LOG20120111--01','after mysql-bin.000034')
/*!*/;
# at 1424
#130113 15:19:02 server id 1  end_log_pos 1451  Xid = 33
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@HOME001 data]#

特定のPOSITION以降のSQLコマンドを抜粋
リストアに利用可能:

mysqlbinlog --no-defaults --start-position=1247 mysql-bin.000034 > /tmp/today_recover.sql
[root@HOME001 data]# mysqlbinlog --no-defaults --start-position=1247 mysql-bin.000034
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#130113 14:16:55 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.29-log created 130113 14:16:55
# Warning: this binlog is either in use or was not closed properly.
BINLOG '
R0PyUA8BAAAAZwAAAGsAAAABAAQANS41LjI5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 1247
#130113 15:19:02 server id 1  end_log_pos 1275  Intvar
SET INSERT_ID=23/*!*/;
# at 1275
#130113 15:19:02 server id 1  end_log_pos 1424  Query   thread_id=4     exec_time=0     error_code=0
use `test`/*!*/;
SET TIMESTAMP=1358057942/*!*/;
SET @@session.pseudo_thread_id=4/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=0/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
insert into TABLE001(title,comment) values('LOG20120111--01','after mysql-bin.000034')
/*!*/;
# at 1424
#130113 15:19:02 server id 1  end_log_pos 1451  Xid = 33
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@HOME001 data]# 

バックアップ取得とリカバリー

データを追加後にデータベースのバックアップしバイナリーログを適用する。

mysql> insert into TABLE001(title,comment) values('LOG20120113','mysql-bin.000034 Take Full Backup');
Query OK, 1 row affected (0.43 sec)

バックアップ取得

[root@HOME001 data]# mysqldump -u root -p --single-transaction --flush-logs test > mysqldump_`date +%Y%m%d%H%M`.sql  Enter password:
[root@HOME001 data]#

dump

FULLバックアップ後にデータを3件追加

mysql> insert into TABLE001(title,comment) values('LOG20120113','mysql-bin.000034 After  Full Backup');
Query OK, 1 row affected (0.45 sec)

mysql> insert into TABLE001(title,comment) values('LOG20120113','mysql-bin.000034 After  Full Backup');
Query OK, 1 row affected (0.32 sec)

mysql> insert into TABLE001(title,comment) values('LOG20120113','mysql-bin.000034 After  Full Backup');
Query OK, 1 row affected (0.39 sec)

mysql>

リストア

[root@HOME001 data]# mysql -u root -p test < mysqldump_201301131536.sql
&#91;/SQL&#93;

<strong>データ確認</strong>
[SQL]
mysql> select * from TABLE001;
+----+-----------------------+-------------------------------------------+
| id | title                 | comment                                   |
+----+-----------------------+-------------------------------------------+
|  1 | タイトル 20121224     | This is replication test data001@20121221 |
|  2 | タイトル 20121225     | This is replication test when slave down  |
|  3 | Title 20121231        | This will go to two nodes                 |
|  4 | Title 20121231        | This will go to two nodes 2               |
|  5 | Semi-Sync20120110     | Install Plugin                            |
|  6 | Semi-Sync20120110     | Install semi-sync Plugin                  |
|  7 | Semi-Sync20120110     | Install semi-sync Plugin                  |
|  8 | Semi-Sync20120110     | Install semi-sync Plugin                  |
|  9 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 10 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 11 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 12 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 13 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 14 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 15 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 16 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 17 | Semi-Sync20120110     | Install semi-sync Plugin                  |
| 18 | Semi-Sync20120111     | Install semi-sync Plugin                  |
| 19 | LOG20120111           | after mysql-bin.000034                    |
| 20 | LOG20120111           | after mysql-bin.000034                    |
| 21 | LOG20120111           | after mysql-bin.000034                    |
| 22 | LOG20120111           | after mysql-bin.000034                    |
| 23 | LOG20120111--01       | after mysql-bin.000034                    |
| 24 | LOG20120111--01       | after mysql-bin.000034                    |
| 25 | LOG20120113           | mysql-bin.000034 Take Full Backup         |
+----+-----------------------+-------------------------------------------+
25 rows in set (0.00 sec)

mysql>

バイナリーログから最後のFULLバックアップからFULLリストア直前までのSQLコマンドを戻す。


[root@HOME001 data]# mysqlbinlog –no-defaults –stop-datetime=”2013-01-13 15:45:00″ mysql-bin.000035 > recovery.sql
[root@HOME001 data]# mysql -u root -p test < recovery.sql Enter password: [root@HOME001 data]# [/SHELL] データがリカバリーされている事を確認済み
final

留意:
複数バイナリーログをまとめてリストア可能

mysqlbinlog --no-defaults mysql-bin.000003 mysql-bin.000004 > recovery.sql

mysqlbinlogコマンドは以下のように最後にROLLBACKを追記するので、
トランザクションが複数のバイナリーログにまたがる場合は上記のように纏めて
実行しないとトランザクションの不整合が発生する。

DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
[root@HOME001 data]#

参考

How to Read MySQL Binary Log Files (BinLog) with mysqlbinlog

4.9.3.1. リカバリの時刻指定
mysqlbinlog — バイナリログファイルを処理するためのユーティリティ

その他
MySQLバックアップ頂上決戦!! LVMスナップショット vs InnoDB Hot Backup