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

Comments are closed.

Post Navigation