MySQLのmysqlbinlogコマンドでは、MySQL5.6からリモートにあるバイナリーログを読み取る事が可能になりましたが、SSLを利用した通信の暗号化はサポートされておりませんでした。

次期メジャーバージョンのMySQL5.7では、ここら辺も機能拡張されておりmysqlbinlogコマンドを利用して、リモートのバイナリーログを読み込み場合にもSSLを利用した通信の暗号化を行う事が出来るようになりました。
Firewall、セキュリティ機器、セグメント分割された環境で、何処まで必要になるかは状況次第ですが、
リモートサーバーが自然災害対策の為に、自分でインフラを準備する必要が無いPublicクラウド上にある場合などに通信の暗号化が出来ると安心ですね。
※ MySQLのレプリケーションでは、以前からSSLを利用した通信の暗号化が可能です。

参照: 
MySQL 5.7: mysqlbinlog now supports SSL

MySQL5.7 mysqlbinlogマニュアル
4.6.7 mysqlbinlog — Utility for Processing Binary Log Files

MySQL5.6: mysqlbinlogコマンドでリモートデータベースからログを読みこむ機能の確認

4.6.8. mysqlbinlog — バイナリログファイルを処理するためのユーティリティー

[環境] リモートからのログ読み込み基本動作確認
MySQL: 5.6.24 (SSLの確認は別途)
remote-diag

特定ログファイルの読み込み

ログ取得元サーバー


[admin@GA01 ~]$ mysql -u root -p -e 'show master logs';
Enter password: 
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000117 |   1636700 |
| mysql-bin.000118 |       844 |
| mysql-bin.000119 |       222 |
| mysql-bin.000120 |       518 |
| mysql-bin.000121 |       470 |
| mysql-bin.000122 |       222 |
+------------------+-----------+
[admin@GA01 ~]$ ls -l /usr/local/mysql/data/mysql-bin.000117
-rw-rw----. 1 mysql mysql 1636700  7月  6 10:21 /usr/local/mysql/data/mysql-bin.000117
[admin@GA01 ~]$ 

ログ読み込みクライアントサーバー
ログが読みこまれているのが確認出来る。バイナリーのまま持ってくる場合は、
”–raw”オプションを付けてあげれば良い。


[admin@misc01 tmp]$ /usr/local/mysql/bin/mysqlbinlog --read-from-remote-server \
> --host=192.168.56.201 --user=admin --password  \
> mysql-bin.000117 > RemoteLogBackup_`date "+%Y%m%d_%H%M%S"`.sql
Enter password: 
[admin@misc01 tmp]$ ls -l
合計 3632
-rw-rw-r--. 1 admin admin 3715955  7月  9 11:06 RemoteLogBackup_20150709_110610.sql
[admin@misc01 tmp]$ cat RemoteLogBackup_20150709_110610.sql | grep CREATE
CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET utf8 */
CREATE TABLE `City` (
CREATE TABLE `Country` (
CREATE TABLE `CountryLanguage` (
CREATE DATABASE `audit_information`  /*!40100 DEFAULT CHARACTER SET utf8 */
CREATE DATABASE `audit_information`  /*!40100 DEFAULT CHARACTER SET utf8mb4 */
CREATE TABLE `audit_parsed` (
CREATE TABLE `audit_history` (
[admin@misc01 tmp]$ 

binlogread

指定したログファイル以降のログも全て持ってくる場合


[admin@misc01 tmp]$ /usr/local/mysql/bin/mysqlbinlog --read-from-remote-server \
> --host=192.168.56.201 --user=admin --password  --to-last-log \
> mysql-bin.000117 > RemoteLogBackup_`date "+%Y%m%d_%H%M%S"`.sql
Enter password: 
[admin@misc01 tmp]$ ls -l
合計 7268
-rw-rw-r--. 1 admin admin 3715955  7月  9 11:06 RemoteLogBackup_20150709_110610.sql
-rw-rw-r--. 1 admin admin 3722407  7月  9 11:46 RemoteLogBackup_20150709_114625.sql
[admin@misc01 tmp]$ cat RemoteLogBackup_*.sql | grep CREATE
CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET utf8 */
CREATE TABLE `City` (
CREATE TABLE `Country` (
CREATE TABLE `CountryLanguage` (
CREATE DATABASE `audit_information`  /*!40100 DEFAULT CHARACTER SET utf8 */
CREATE DATABASE `audit_information`  /*!40100 DEFAULT CHARACTER SET utf8mb4 */
CREATE TABLE `audit_parsed` (
CREATE TABLE `audit_history` (
CREATE DATABASE `world` /*!40100 DEFAULT CHARACTER SET utf8 */
CREATE TABLE `City` (
CREATE TABLE `Country` (
CREATE TABLE `CountryLanguage` (
CREATE DATABASE `audit_information`  /*!40100 DEFAULT CHARACTER SET utf8 */
CREATE DATABASE `audit_information`  /*!40100 DEFAULT CHARACTER SET utf8mb4 */
CREATE TABLE `audit_parsed` (
CREATE TABLE `audit_history` (
[admin@misc01 tmp]$ 


ログの中身を確認してみると、先頭行のポジションが同じである事が確認出来る。


[admin@misc01 tmp]$ cat RemoteLogBackup_20150709_110610.sql | grep \# | head 
# at 4
#290328 15:21:27 server id 1  end_log_pos 120 CRC32 0x3a600c07  Start: binlog v 4, server v 5.6.24-enterprise-commercial-advanced-log created 290328 15:21:27 at startup
# at 120
#290328 15:21:27 server id 1  end_log_pos 151 CRC32 0xe9d5507f  Previous-GTIDs
# [empty]
# at 151
#380119 12:14:09 server id 1  end_log_pos 222 CRC32 0x5aae6a2b  Previous-GTIDs
# 8560c2ac-e1dc-11e4-88ff-0800275399c1:1-612
# at 222
#150703 11:57:42 server id 1  end_log_pos 270 CRC32 0xb9607b03  GTID    last_committed=0        sequence_number=0

[admin@misc01 tmp]$ cat RemoteLogBackup_20150709_114625.sql | grep \# | head 
# at 4
#700101  9:00:00 server id 1  end_log_pos 0     Rotate to mysql-bin.000117  pos: 4
# at 4
#290328 15:21:27 server id 1  end_log_pos 120 CRC32 0x3a600c07  Start: binlog v 4, server v 5.6.24-enterprise-commercial-advanced-log created 290328 15:21:27 at startup
# at 120
#290328 15:21:27 server id 1  end_log_pos 151 CRC32 0xe9d5507f  Previous-GTIDs
# [empty]
# at 151
#380119 12:14:09 server id 1  end_log_pos 222 CRC32 0x5aae6a2b  Previous-GTIDs
# 8560c2ac-e1dc-11e4-88ff-0800275399c1:1-612
[admin@misc01 tmp]$ 

ログの中身を確認してみると、特定のログ以降の全てのログを読みこんだログは、
最終変更までのバイナリーログが読みこまれている事が確認出来る。

[admin@misc01 tmp]$ cat RemoteLogBackup_20150709_110610.sql | grep \# | tail -n 20
#150703 19:06:58 server id 1  end_log_pos 1634541 CRC32 0x49844ce7      GTID    last_committed=0        sequence_number=0
# at 1634541
#150703 19:06:58 server id 1  end_log_pos 1634716 CRC32 0x271b8e30      Query   thread_id=6     exec_time=0     error_code=0
# at 1634716
#150703 19:07:50 server id 1  end_log_pos 1634764 CRC32 0x92c5292d      GTID    last_committed=0        sequence_number=0
# at 1634764
#150703 19:07:50 server id 1  end_log_pos 1634875 CRC32 0x1b73a468      Query   thread_id=6     exec_time=0     error_code=0
# at 1634875
#150703 19:08:03 server id 1  end_log_pos 1634923 CRC32 0x62aa0722      GTID    last_committed=0        sequence_number=0
# at 1634923
#150703 19:08:03 server id 1  end_log_pos 1635101 CRC32 0x10ed801d      Query   thread_id=6     exec_time=0     error_code=0
# at 1635101
#150703 19:08:21 server id 1  end_log_pos 1635149 CRC32 0x805c979c      GTID    last_committed=0        sequence_number=0
# at 1635149
#150703 19:08:21 server id 1  end_log_pos 1636236 CRC32 0x78f51539      Query   thread_id=6     exec_time=0     error_code=0
# at 1636236
#150703 19:08:41 server id 1  end_log_pos 1636284 CRC32 0xf6128e4b      GTID    last_committed=0        sequence_number=0
# at 1636284
#150703 19:08:41 server id 1  end_log_pos 1636700 CRC32 0xeda14d3e      Query   thread_id=6     exec_time=0     error_code=0
# End of log file
[admin@misc01 tmp]$ cat RemoteLogBackup_20150709_114625.sql | grep \# | tail -n 20
# at 151
#380119 12:14:09 server id 1  end_log_pos 222 CRC32 0x1bbf07f9  Previous-GTIDs
# 8560c2ac-e1dc-11e4-88ff-0800275399c1:1-5933
# at 222
#150708 14:30:37 server id 1  end_log_pos 270 CRC32 0x7fdb0132  GTID    last_committed=0        sequence_number=0
# at 270
#150708 14:30:35 server id 1  end_log_pos 370 CRC32 0xdc99f8eb  Query   thread_id=1     exec_time=0     error_code=0
# at 370
#150708 14:30:35 server id 1  end_log_pos 470 CRC32 0xa8b91492  Query   thread_id=1     exec_time=0     error_code=0
# at 4
#700101  9:00:00 server id 1  end_log_pos 0 CRC32 0xc2515710    Rotate to mysql-bin.000122  pos: 4
# at 4
#700101  9:00:00 server id 1  end_log_pos 120 CRC32 0x07fa7f88  Start: binlog v 4, server v 5.6.24-enterprise-commercial-advanced-log created 700101  9:00:00
# at 120
#700101  9:00:00 server id 1  end_log_pos 151 CRC32 0x218282e8  Previous-GTIDs
# [empty]
# at 151
#380119 12:14:09 server id 1  end_log_pos 222 CRC32 0xd7150767  Previous-GTIDs
# 8560c2ac-e1dc-11e4-88ff-0800275399c1:1-5934
# End of log file
[admin@misc01 tmp]$ 

リモートデータベースからログを読み込み、定期的に遠隔地に保存してしておく方法の一つとしても使えそうです。



MYSQLDUMPを利用する時のOPTION確認とDUMPした後にバイナリーログを適用して、
最新のデータをリカバリーする方法の再確認。

以下のようなOPTIONでDB全体の整合性とバイナリーログのロールフォワードに必要なデータをダンプする。

–master-dataオプションと対象ロック

mysqldump --lock-all-tables --flush-logs --master-data=2 --databases test > DB_DUMP.sql -u root -p
mysqldump --single-transaction --flush-logs --master-data=2 --databases test > DB_DUMP.sql -u root -p

–lock-all-tablesと–single-transactionは同時に利用する事が出来ません。HELPを読むと–lock-all-tablesを利用すると
自動的に–single-transactionはOFFにされるようです。–single-transactionはトランザクションをサポートする
InnoDBにのみ利用可能なのでMyISAMなどには有効では無いようです。同時にオプションを指定すると以下のようなエラーになる。
また–master-dataオプションを指定している場合は自動的に–lock-all-tablesがONになるようです。
暗黙的にONになるので運用者が明確にしておく為に意図的に指定しておいても良いかもしれません。
但し、–single-transactionが指定されていると–master-dataを指定していても–lock-all-tablesはONにならない様です。

[root@HOME002 data]# mysqldump --single-transaction --lock-all-tables --flush-logs --master-data=2 --databases test > /home/mysql/MYSQL_dump20130125.sql -u root -p
mysqldump: You can't use --single-transaction and --lock-all-tables at the same time.
[root@HOME002 data]#

こちらのバックアップオプションで特定データベースもしくはDB全体をバックアップ
InnoDBやMYISAMを含む全てのデータベースをDumpする場合
(–lock-all-tablesはあえて残してあります)
例)

 mysqldump --lock-all-tables --flush-logs --master-data=2 --databases test > DB_DUMP.sql -u root -p
 mysqldump --lock-all-tables --flush-logs --master-data=2 --all-databases >  ALL_DB_DUMP.sql -u root -p

InnoDBのみ利用していて整合性の取れたデータをDumpする場合
例)

 mysqldump --single-transaction --flush-logs --master-data=2 --databases test > DB_DUMP.sql -u root -p
 mysqldump --single-transaction --flush-logs --master-data=2 --all-databases >  ALL_DB_DUMP.sql -u root -p

—————————————————
以下のHELP抜粋
—————————————————
-x, –lock-all-tables
Locks all tables across all databases. This is achieved
by taking a global read lock for the duration of the
whole dump. Automatically turns –single-transaction and
–lock-tables off.

–single-transaction
Creates a consistent snapshot by dumping all tables in a
single transaction. Works ONLY for tables stored in
storage engines which support multiversioning (currently
only InnoDB does); the dump is NOT guaranteed to be
consistent for other storage engines. While a
–single-transaction dump is in process, to ensure a
valid dump file (correct table contents and binary log
position), no other connection should use the following
statements: ALTER TABLE, DROP TABLE, RENAME TABLE,
TRUNCATE TABLE, as consistent snapshot is not isolated
from them. Option automatically turns off –lock-tables.

–master-data[=#] This causes the binary log position and filename to be
appended to the output. If equal to 1, will print it as a
CHANGE MASTER command; if equal to 2, that command will
be prefixed with a comment symbol. This option will turn
–lock-all-tables on, unless –single-transaction is
specified too (in which case a global read lock is only
taken a short time at the beginning of the dump; don’t
forget to read about –single-transaction below). In all
cases, any action on logs will happen at the exact moment
of the dump. Option automatically turns –lock-tables
off.

-F, –flush-logs Flush logs file in server before starting dump. Note that
if you dump many databases at once (using the option
–databases= or –all-databases), the logs will be
flushed for each database dumped. The exception is when
using –lock-all-tables or –master-data: in this case
the logs will be flushed only once, corresponding to the
moment all tables are locked. So if you want your dump
and the log flush to happen at the same exact moment you
should use –lock-all-tables or –master-data with
–flush-logs.

-A, –all-databases Dump all the databases. This will be same as –databases
with all databases selected.

–dump-date Put a dump date to the end of the output.
(Defaults to on; use –skip-dump-date to disable.)

-q, –quick Don’t buffer query, dump directly to stdout.
(Defaults to on; use –skip-quick to disable.)
—————————————————


MYISAMを含むデータもあるのでFULLをあえて–lock-all-tablesで取得して、
InnoDBしか含まないtestデータベースを–single-transactionで取得してます。

※本番環境では状況によって使い分けして下さい。

[root@HOME001 backup]# dumpdate=`date '+%F'`
[root@HOME001 backup]# dumpfile=MYSQL_DUMP_$dumpdate
[root@HOME001 backup]# dumpfile_dir=/home/mysql/backup/
[root@HOME001 backup]# mysqldump --lock-all-tables --flush-logs --master-data=2 --all-databases >  $dumpfile_dir$dumpfile\_ALL.sql -u root -p
Enter password:
[root@HOME001 backup]# 
[root@HOME001 backup]# mysqldump --single-transaction --flush-logs --master-data=2 --databases test > $dumpfile_dir$dumpfile\_test.sql -u root -p
Enter password:
[root@HOME001 backup]# ls -l /home/mysql/backup/MYSQL_DUMP_*                                                          
-rw-r--r--. 1 root root 516386  1月 26 11:00 2013 /home/mysql/backup/MYSQL_DUMP_2013-01-26_ALL.sql
-rw-r--r--. 1 root root   4000  1月 26 11:03 2013 /home/mysql/backup/MYSQL_DUMP_2013-01-26_test.sql
[root@HOME001 backup]#


FULLバックアップを取得したので先ずはデータの確認して、
データを追記した後に擬似的な操作ミスとしてTruncateしてデータを空にしてみる。

mysql> select * from TABLE001 order by id desc limit 0,10;
+----+----------------------+-----------------------------------------------+
| id | title                | comment                                       |
+----+----------------------+-----------------------------------------------+
| 32 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 31 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 30 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 29 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 28 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 27 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 26 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 25 | LOG20120113          | mysql-bin.000034 Take Full Backup             |
| 24 | LOG20120111--01      | after mysql-bin.000034                        |
| 23 | LOG20120111--01      | after mysql-bin.000034                        |
+----+----------------------+-----------------------------------------------+
10 rows in set (0.00 sec)

mysql>

mysql> insert into TABLE001(title,comment) values("Recreate Replication","after dump 20120126");
Query OK, 1 row affected (0.45 sec)

mysql> insert into TABLE001(title,comment) values("Recreate Replication","after dump 20120126");
Query OK, 1 row affected (0.62 sec)

mysql> insert into TABLE001(title,comment) values("Recreate Replication","after dump 20120126");
Query OK, 1 row affected (0.13 sec)

mysql> select * from TABLE001 order by id desc limit 0,10;
+----+----------------------+-----------------------------------------------+
| id | title                | comment                                       |
+----+----------------------+-----------------------------------------------+
| 35 | Recreate Replication | after dump 20120126                           |
| 34 | Recreate Replication | after dump 20120126                           |
| 33 | Recreate Replication | after dump 20120126                           |
| 32 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 31 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 30 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 29 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 28 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 27 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 26 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
+----+----------------------+-----------------------------------------------+
10 rows in set (0.00 sec)

mysql>


mysql> truncate table TABLE001;
Query OK, 0 rows affected (0.40 sec)

mysql> select * from TABLE001 order by id desc limit 0,10;
Empty set (0.00 sec)

mysql>

confirmation1


上記でバックアップしたダンプファイルの中身を確認してLOGのポジションを確認。

[root@HOME001 backup]# cat MYSQL_DUMP_2013-01-26_test.sql | egrep -i master
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=107;
[root@HOME001 backup]#


擬似オペミス直前まで戻す為にどのバイナリーログまで戻したら良いか確認。

最後のバックアップから全てのデータを戻したい場合は、
バックアップのログポジションを確認した後に確認したポジション
以降の全てのログを戻せば良い。

[root@HOME001 data]# mysqlbinlog --no-defaults mysql-bin.000007 | egrep -i -B5 truncate
#130126 11:26:50 server id 1  end_log_pos 929   Xid = 366
COMMIT/*!*/;
# at 929
#130126 11:41:11 server id 1  end_log_pos 1015  Query   thread_id=5     exec_time=0     error_code=0
SET TIMESTAMP=1359168071/*!*/;
truncate table TABLE001
[root@HOME001 data]#

ロールフォワードする為には以下のログの中身を適用する必要がある。
今回の検証で確認したケースでのStartとEndのポジション

mysql-bin.000007
 –start-position=107
 –stop-position=929

バイナリーログから戻すべきデータを再作成する為のスクリプトを作成する。
OracleでいうArchiveログを適用するRecoveryモード時に適用するログをマニュアル作成する感じ?
Positionは上記で確認したPositionを設定。

[root@HOME001 data]# mysqlbinlog --no-defaults --start-position=107 --stop-position=929 mysql-bin.000007 > /home/mysql/backup/recovery.sql
[root@HOME001 data]#

障害発生前まで戻す為の材料が揃ったのでリカバリー開始

[root@HOME001 backup]# ls -l
合計 524
drwxr-xr-x. 6 root root   4096  1月 24 14:45 2013 2013-01-24_14-44-57
drwxr-xr-x. 6 root root   4096  1月 24 15:23 2013 2013-01-24_15-23-31
-rw-r--r--. 1 root root 516386  1月 26 11:00 2013 MYSQL_DUMP_2013-01-26_ALL.sql
-rw-r--r--. 1 root root   4000  1月 26 11:03 2013 MYSQL_DUMP_2013-01-26_test.sql
-rw-r--r--. 1 root root   2713  1月 26 12:17 2013 recovery.sql
[root@HOME001 backup]#

[root@HOME001 backup]# mysql -u root -p test < MYSQL_DUMP_2013-01-26_test.sql
Enter password:
&#91;root@HOME001 backup&#93;# mysql -u root -p test < recovery.sql
Enter password:
&#91;root@HOME001 backup&#93;#
&#91;/SHELL&#93;

<strong>リカバリー完了したのでデータを確認して完了。</strong>
[SHELL]
mysql> use test
Database changed
mysql> select * from TABLE001 order by id desc limit 0,10;
+----+----------------------+-----------------------------------------------+
| id | title                | comment                                       |
+----+----------------------+-----------------------------------------------+
| 35 | Recreate Replication | after dump 20120126                           |
| 34 | Recreate Replication | after dump 20120126                           |
| 33 | Recreate Replication | after dump 20120126                           |
| 32 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 31 | Recreate Replication | 20130125 mysql-bin.000004 and relay-bin.00000 |
| 30 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 29 | Recreate Replication | mysql-bin.000004 and relay-bin.000008         |
| 28 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 27 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
| 26 | LOG20120113          | mysql-bin.000034 After  Full Backup           |
+----+----------------------+-----------------------------------------------+
10 rows in set (0.00 sec)

mysql>

after

スレーブでmysqldumpを取得時にバイナリーログPOSITIONを記録する場合の留意:

--master-data

オプションは、バックアップを取得したサーバーでのポジションになるので
スレーブでバックアップ取得していてマスターDBが破損した場合でも、
スレーブのバイナリーログから対象のデータをリカバリーする必要があります。
もしマスター側のバイナリーログから必要なDDL、DMLを追加で取得する場合は、
調査と対応に時間がかなりかかる事が想定されます。
MYSQL5.5以降であれば、スレーブで取得する場合は

--dump-slave

を使用する事で、
MasterデータベースのバイナリーログのPOSITIONを記録してくれるので、
マスターのログを利用してのリカバリーが可能です。
但し、マスターが全損する場合もあるので用途によって運用でカバーする必要があります。

検証結果

mysqldump

参考サイト
How to obtain a correct dump using mysqldump and single-transaction when DDL is used at the same time?
mysqldump –single-transaction に –flush-logs をつけてはいけない
mysqldump の option 復習
7.12. mysqldump — データベースバックアッププログラム
mysqlbinlog — バイナリログファイルを処理するためのユーティリティ


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


バイナリーログをマスターしてデータのリカバリーをマスター


--start-datetime
バイナリログ開始日時を指定した時点からのログがファイルに出力される。

--stop-datetime
バイナリログ終了日時を指定した時点までのログがファイルに出力される。

--start-position
指定された位置からログを読む

--stop-position
指定された位置までログを読み込む


--start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--start-position=# Start reading the binlog at position N. Applies to the
first binlog passed on the command line.
--stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).
--stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line.

「例」

[root@colinux data]# ls -l mysql-bin.00002*
-rwxrwxrwx 1 root mysql 125 2009-02-25 11:09 mysql-bin.000020
-rwxrwxrwx 1 root mysql 125 2009-02-25 11:19 mysql-bin.000021
-rwxrwxrwx 1 root mysql 125 2009-02-25 11:24 mysql-bin.000022
-rw-rw—- 1 mysql mysql 125 2009-02-25 11:26 mysql-bin.000023
-rw-rw—- 1 mysql mysql 125 2009-02-25 11:33 mysql-bin.000024
-rw-rw—- 1 mysql mysql 125 2009-02-25 11:34 mysql-bin.000025
-rw-rw—- 1 mysql mysql 125 2009-02-25 11:38 mysql-bin.000026
-rw-rw—- 1 mysql mysql 388 2009-02-25 16:28 mysql-bin.000027
-rw-rw—- 1 mysql mysql 1200 2009-02-26 17:01 mysql-bin.000028
-rw-rw—- 1 mysql mysql 18361 2009-02-27 15:10 mysql-bin.000029
[root@colinux data]# mysqlbinlog --no-defaults --start-datetime="2009-02-27 00:01:00" mysql-bin.000029 > /tmp/today_recover.sql

開始時間を指定
mysqlbinlog

出力結果
mysqlbinlog_read


mysqlbinlog (バイナリログファイルを処理ユーティリティ)

no-defaults

サーバが生成するバイナリログファイルはバイナリフォーマットで書かれています。
これらのファイルをテキストフォーマットで確認するには、mysqlbinlogユーティリティを使用してください。
それに加えて、複製セットアップ内のスレーブサーバによって書き出されたリレイログファイルを読み取るのに
mysqlbinlogを使用することもできます。リレイログはバイナリログファイルと同じフォーマットです。

[root@localhost data]#mysqlbinlog --no-defaults mysql-bin.000008

http://dev.mysql.com/doc/refman/5.1/ja/mysqlbinlog.html

※–no-defaultsオプションはmy.cnfの中でDefaultの文字コードが設定されてある場合に必要。

[root@localhost data]# mysqlbinlog --no-defaults mysql-bin.000008
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#090127 17:15:55 server id 1 end_log_pos 106 Start: binlog v 4, server v 5.1.25-rc-log created 090127 17:15:55 at startup
ROLLBACK/*!*/;
BINLOG ‘
u8J+SQ8BAA4xLjI1LXJjLWxvZwAAAAAAA
‘/*!*/;
# at 106
#090127 17:16:08 server id 1 end_log_pos 453 Query thread_id=2 exec_time=0 error_code=0
use DBNAME /*!*/;
SET TIMESTAMP=1233044168/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;

■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
mysqlbinlogコマンドは特定の時間までリカバリーするときなどにも使われる、
mysqlの管理者は理解しておく必要がある。
※ Oracleのアーカイブログ適用と同じOracleを運用していた時には、
時間指定で戻した(mysqlオプション: –stop-datetime)経験はありませんが
特定のArchiveログの適用(mysqlオプション: –stop-position)する事は
頻繁に発生しておりました。

==========================================================
MYSQLBINLOGのヘルプ抜粋
==========================================================
mysqlbinlog Ver 3.3 for pc-linux-gnu at i686
By Monty and Sasha, for your professional use
This software comes with NO WARRANTY: This is free software,
and you are welcome to modify and redistribute it under the GPL license

Dumps a MySQL binary log in a format usable for viewing or for piping to
the mysql command line client

MYSQLのバイナリーログを読むViewとして利用する事が出来ます。

Usage: mysqlbinlog [options] log-files
-?, –help Display this help and exit.
–base64-output[=name]
Determine when the output statements should be
base64-encoded BINLOG statements: ‘never’ disables it and
works only for binlogs without row-based events; ‘auto’
is the default and prints base64 only when necessary
(i.e., for row-based events and format description
events); ‘always’ prints base64 whenever possible.
‘always’ is for debugging only and should not be used in
a production system. The default is ‘auto’.
–base64-output is a short form for
–base64-output=always.
–character-sets-dir=name
Directory where character sets are.
-d, –database=name List entries for just this database (local log only).

指定されたデータベースのログのみをsqlファイルに出力する。
例)
$ mysqlbinlog --database="TEST" \
/backup/mysql-bin.000019 > /backup/recovery.sql

–debug-check Check memory and open file usage at exit .
–debug-info Print some debug info at exit.
-D, –disable-log-bin
Disable binary log. This is useful, if you enabled
–to-last-log and are sending the output to the same
MySQL server. This way you could avoid an endless loop.
You would also like to use it when restoring after a
crash to avoid duplication of the statements you already
have. NOTE: you will need a SUPER privilege to use this
option.

バイナリログの出力を無効にする。

-F, –force-if-open Force if binlog was not closed properly.
-f, –force-read Force reading unknown binlog events.
-H, –hexdump Augment output with hexadecimal and ASCII event dump.
-h, –host=name Get the binlog from server.
-l, –local-load=name
Prepare local temporary files for LOAD DATA INFILE in the
specified directory.
-o, –offset=# Skip the first N entries.
-p, –password[=name]
Password to connect to remote server.
-P, –port=# Port number to use for connection or 0 for default to, in
order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/services, built-in default (3306).
-j, –position=# Deprecated. Use –start-position instead.
–protocol=name The protocol of connection (tcp,socket,pipe,memory).
-R, –read-from-remote-server
Read binary logs from a MySQL server
-r, –result-file=name
Direct output to a given file.
–server-id=# Extract only binlog entries created by the server having
the given id.
–set-charset=name Add ‘SET NAMES character_set’ to the output.
-s, –short-form Just show regular queries: no extra info and no row-based
events. This is for testing only, and should not be used
in production systems. If you want to suppress
base64-output, consider using –base64-output=never
instead.
-S, –socket=name Socket file to use for connection.
–start-datetime=name
Start reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).

バイナリログからの開始日時を指定し、その時点からのログがsqlファイルに出力される。
例)
$ mysqlbinlog --start-datetime="2006-12-13 06:30:00" \
/backup/mysql-bin.000019 > /backup/recovery.sql

–start-position=# Start reading the binlog at position N. Applies to the
first binlog passed on the command line.

指定された位置からログを読み始める。
このオプションはコマンドラインで指定された最初のバイナリログファイルに適用される。
例)
$ mysqlbinlog --start-position=7743 \
/backup/mysql-bin.000017 /backup/mysql-bin.000018 > /backup/recovery.sql

–stop-datetime=name
Stop reading the binlog at first event having a datetime
equal or posterior to the argument; the argument must be
a date and time in the local time zone, in any format
accepted by the MySQL server for DATETIME and TIMESTAMP
types, for example: 2004-12-25 11:25:56 (you should
probably use quotes for your shell to set it properly).

バイナリログからの終了日時を指定し、その時点までのログがsqlファイルに出力される。
例)
$ mysqlbinlog --stop-datetime="2006-12-13 08:00:00" \
/backup/mysql-bin.000019 > /backup/recovery.sql

–stop-position=# Stop reading the binlog at position N. Applies to the
last binlog passed on the command line.

指定された位置までログを読み込む。
このオプションはコマンドラインで指定された最後のバイナリログファイルに適用される。
例)
$ mysqlbinlog --stop-position=15324 \
/backup/mysql-bin.000017 /backup/mysql-bin.000018 > /backup/recovery.sql

-t, –to-last-log Requires -R. Will not stop at the end of the requested
binlog but rather continue printing until the end of the
last binlog of the MySQL server. If you send the output
to the same MySQL server, that may lead to an endless
loop.
-u, –user=name Connect to the remote server as username.
-v, –verbose Reconstruct SQL statements out of row events. -v -v adds
comments on column data types
-V, –version Print version and exit.
–open_files_limit=#
Used to reserve file descriptors for usage by this
program

==========================================================