![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
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>
ログの中身を確認
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]#
特定の時間以降の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]#
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 [/SQL] <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]
データがリカバリーされている事を確認済み
留意：
複数バイナリーログをまとめてリストア可能
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 — バイナリログファイルを処理するためのユーティリティ