MySQL5.6のmysqlbinlogコマンド

MySQL5.6からmysqlbinlogコマンドにバイナリログをバックアップするためのオプションが多数追加されています。その中からめぼしいオプションについてまとめます。なお、一度は公式ドキュメントを読む事をお勧めします。

基本的にオプションは省略形で記載していません。適宜()で省略形を記載しています。

リモートのMySQLのバイナリログをコピー

オプションにより色々なコピーの仕方が用意されています。基本的には--read-from-remote-serverオプション(-R)をベースとして用途により追加でオプションを指定します。

接続されるMySQLから見るとSlaveからの接続と変わらないようなのでserver_idをオプションで指定可能な物もあります。指定しない場合は使用するオプションにより0、または65535がデフォルトで使用されます。条件については以下のようになります。

• --read-from-remote-serverのみの場合
  • server_idとして0がデフォルトで使用されます。終了し次第接続が切られるためです。
• --read-from-remote-server と --stop-neverの両方の場合
  • server_idとして65535がデフォルトで使用されます。--stop-never-slave-server-idオプション(省略形無し)でserver_idを指定可能です。

今回使用しているユーザの権限は REPLICATION SLAVE, REPLICATION CLIENTの2つのみで、レプリケーション用ユーザと同じ権限で確認しています。

指定したバイナリログのコピー

# mysqlbinlog -urepl -p --read-from-remote-server --host=192.168.1.237 --raw  mysql-bin.000001
Enter password: 
#
# ls -l
total 4
-rw-rw-r-- 1 root root 1698 Nov 14 02:40 mysql-bin.000001
#

この場合、実行したカレントディレクトリにmysql-bin.000001という同一ファイル名で保存されます。また、以下のように複数ファイルを指定する事でまとめてコピーが可能です。

# /usr/local/mysql/bin/mysqlbinlog -urepl -p --read-from-remote-server --host=192.168.1.237 \
     --raw  mysql-bin.000001 mysql-bin.000002
Enter password: 
# ls -l
total 8
-rw-rw-r-- 1 root root 1698 Nov 14 02:42 mysql-bin.000001
-rw-rw-r-- 1 root root  470 Nov 14 02:42 mysql-bin.000002
#

なお、--rawオプション(省略形無し)を指定しない場合は mysqlbinlog ./mysql-bin.000001 を実行した場合と同様、テキストとして標準出力されます。

# mysqlbinlog -urepl -p --read-from-remote-server --host=192.168.1.237 mysql-bin.000002
Enter password: 
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#121114  2:35:55 server id 237  end_log_pos 120 CRC32 0xc99a96dd 	Start: binlog v 4, server v 5.6.8-rc-log created 121114  2:35:55
BINLOG '
+4SiUA/tAAAAdAAAAHgAAAAAAAQANS42LjgtcmMtbG9nAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAd2W
msk=
'/*!*/;
# at 120
#121114  2:35:55 server id 237  end_log_pos 191 CRC32 0xa67e4728 	Previous-GTIDs
# C4F64A91-2A2B-11E2-9084-5254003E1CEC:1-6
# at 191
#121114  2:36:02 server id 237  end_log_pos 239 CRC32 0x97ca0ec0 	GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'C4F64A91-2A2B-11E2-9084-5254003E1CEC:7'/*!*/;
# at 239
#121114  2:36:02 server id 237  end_log_pos 326 CRC32 0xce3aa2a0 	Query	thread_id=19	exec_time=0	error_code=0
SET TIMESTAMP=1352828162/*!*/;
SET @@session.pseudo_thread_id=19/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
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.time_zone='SYSTEM'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 326
#121114  2:36:02 server id 237  end_log_pos 439 CRC32 0x3b6fbcf9 	Query	thread_id=19	exec_time=0	error_code=0
use `test`/*!*/;
SET TIMESTAMP=1352828162/*!*/;
insert into test values (NOW())
/*!*/;
# at 439
#121114  2:36:02 server id 237  end_log_pos 470 CRC32 0x32ddc489 	Xid = 217
COMMIT/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
#

コピーするバイナリログがコピー元サーバから削除されている場合(expire_logs_daysやpurge master logs to等で)はエラーとなります。

ERROR: Got error reading packet from server: Could not find first log file name in binary log index file

指定したバイナリログ以降を全てコピーする

--to-last-logオプション(-t)を使用すると指定したバイナリログ以降を全てコピーする事が出来ます。

# mysqlbinlog -urepl -p --read-from-remote-server --host=192.168.1.237 \
     --raw --to-last-log mysql-bin.000004
Enter password: 
# ls -l
total 16
-rw-rw-r-- 1 root root 517 Nov 14 02:51 mysql-bin.000004
-rw-rw-r-- 1 root root 796 Nov 14 02:51 mysql-bin.000005
-rw-rw-r-- 1 root root 517 Nov 14 02:51 mysql-bin.000006
-rw-rw-r-- 1 root root 470 Nov 14 02:51 mysql-bin.000007
#

指定したバイナリログ以降を停止するまでコピーし続ける

--stop-neverオプション(省略形無し)を使用することでctrl+c等で停止するまで追加されるバイナリログをコピーし続ける事が出来ます。

実行前のコピー元のMySQLの状態

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1698 |
| mysql-bin.000002 |       517 |
| mysql-bin.000003 |       796 |
| mysql-bin.000004 |       517 |
| mysql-bin.000005 |       796 |
| mysql-bin.000006 |       517 |
| mysql-bin.000007 |       470 |
+------------------+-----------+
7 rows in set (0.00 sec)

mysql> 

--stop-neverを使用してバイナリログをコピー

# mysqlbinlog -urepl -p --read-from-remote-server --host=192.168.1.237 --raw --stop-never mysql-bin.000001
Enter password: 

コピーしたバイナリログを確認

# ls -l
total 28
-rw-rw-r-- 1 root root 1698 Nov 14 03:03 mysql-bin.000001
-rw-rw-r-- 1 root root  517 Nov 14 03:03 mysql-bin.000002
-rw-rw-r-- 1 root root  796 Nov 14 03:03 mysql-bin.000003
-rw-rw-r-- 1 root root  517 Nov 14 03:03 mysql-bin.000004
-rw-rw-r-- 1 root root  796 Nov 14 03:03 mysql-bin.000005
-rw-rw-r-- 1 root root  517 Nov 14 03:03 mysql-bin.000006
-rw-rw-r-- 1 root root  439 Nov 14 03:03 mysql-bin.000007
#

コピー元のMySQLでflush binary logsを実行

mysql> flush binary logs;
Query OK, 0 rows affected (0.03 sec)

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |      1698 |
| mysql-bin.000002 |       517 |
| mysql-bin.000003 |       796 |
| mysql-bin.000004 |       517 |
| mysql-bin.000005 |       796 |
| mysql-bin.000006 |       517 |
| mysql-bin.000007 |       517 |
| mysql-bin.000008 |       191 |
+------------------+-----------+
8 rows in set (0.00 sec)

mysql> 

コピー先でmysql-bin.000008がコピーされている事を確認

# ls -l
total 32
-rw-rw-r-- 1 root root 1698 Nov 14 03:03 mysql-bin.000001
-rw-rw-r-- 1 root root  517 Nov 14 03:03 mysql-bin.000002
-rw-rw-r-- 1 root root  796 Nov 14 03:03 mysql-bin.000003
-rw-rw-r-- 1 root root  517 Nov 14 03:03 mysql-bin.000004
-rw-rw-r-- 1 root root  796 Nov 14 03:03 mysql-bin.000005
-rw-rw-r-- 1 root root  517 Nov 14 03:03 mysql-bin.000006
-rw-rw-r-- 1 root root  517 Nov 14 03:03 mysql-bin.000007
-rw-rw-r-- 1 root root  120 Nov 14 03:03 mysql-bin.000008
#

上記のような振る舞いとなりますので--rawオプションを使用しない場合、垂れ流し状態に出来ます。

また、従来通りに開始ポジションを指定可能ですので例えばmysqldumpコマンドで --single-transaction --master-data=2オプションを使用してダンプを取得した場合、ロールフォワードリカバリ用に以下のように必要なバイナリログをコピーする事も可能です。

ダンプファイルには以下のように記載されていたとします。

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000008', MASTER_LOG_POS=749;

指定したポジションからコピーする。

# mysqlbinlog -urepl -p --read-from-remote-server --host=192.168.1.237 \
     --stop-never --raw --start-position=749 mysql-bin.000008

公式ドキュメントにも記載されていますが、以下のようにすることでリストア後のMySQLに直接バイナリログを流し込む事が可能です。

# mysqlbinlog -urepl -p --read-from-remote-server --host=192.168.1.237 \
     --start-position=749 mysql-bin.000008 mysql-bin.000009 | mysql --host=host_name -u root -p

その他オプション

これまでに記載したオプションで基本的な事は可能かと思いますが、以下のようにちょっと便利なオプションがあります。

保存するバイナリログにプリフィックスを付与する

--result-fileオプション(-r)を使用する事でプリフィックスを付与出来ます。コピー元となるサーバの情報を付けるのがおそらく一般的かと。

# mysqlbinlog -urepl -p --read-from-remote-server --host=192.168.1.237 \
     --to-last-log --raw --result-file=DBM- mysql-bin.000001
Enter password: 
# ls -l
total 32
-rw-rw-r-- 1 root root 1698 Nov 14 03:33 DBM-mysql-bin.000001
-rw-rw-r-- 1 root root  517 Nov 14 03:33 DBM-mysql-bin.000002
-rw-rw-r-- 1 root root  796 Nov 14 03:33 DBM-mysql-bin.000003
-rw-rw-r-- 1 root root  517 Nov 14 03:33 DBM-mysql-bin.000004
-rw-rw-r-- 1 root root  796 Nov 14 03:33 DBM-mysql-bin.000005
-rw-rw-r-- 1 root root  517 Nov 14 03:33 DBM-mysql-bin.000006
-rw-rw-r-- 1 root root  517 Nov 14 03:33 DBM-mysql-bin.000007
-rw-rw-r-- 1 root root  749 Nov 14 03:33 DBM-mysql-bin.000008
# 

binlog_checksumによるチェックを有効にする

--verify-binlog-checksumオプション(-c)を使用する事で可能なようです。解説が少なくて合ってるか若干怪しいですがbinlog_checksumオプション(デフォルトCRC32)にてチェックするものと考えられます。

.mylogin.cnfを使用してログイン情報を省略

mysql_config_editorコマンドで予め所定の.mylogin.cnfを作成しておくことにより、--login-pathオプション(省略形無し)を使う事でログイン情報を省略する事が可能です。

以下のようにdbmというlogin-pathで作成します。

# mysql_config_editor set --login-path=dbm --host=192.168.1.237 --user=repl --password
Enter password: 
#

実行後、rootの場合は/root/.mylogin.cnfファイルが作成されます。作成した内容は以下のように確認可能です。

# mysql_config_editor print --all
[dbm]
user = repl
password = *****
host = 192.168.1.237
#

--login-pathを使用した場合は以下のようになります。

# mysqlbinlog --login-path=dbm --read-from-remote-server \
      --to-last-log --raw --start-position=749 mysql-bin.000008
# ls -l
total 12
-rw-rw-r-- 1 root root 167 Nov 14 04:08 mysql-bin.000008
-rw-rw-r-- 1 root root 517 Nov 14 04:08 mysql-bin.000009
-rw-rw-r-- 1 root root 470 Nov 14 04:08 mysql-bin.000010
# 

このようにだいぶすっきりします。ユーザ情報・パスワードを入力する必要がないのでより楽ですし、自動実行のためにパスワードを平文で保存する必要もないためmysql_config_editorコマンドが存在しなかったころよりもセキュアにすることが出来ます。

まとめ

MySQL5.6からはgtidが追加されたこともあり、バイナリログのバックアップは以前のバージョンよりは必要性が上がったと思いますのでmysqlbinlogコマンドによるバックアップはそこそこ使われるのではないかと思います。
これまででもバイナリログのバックアップを希望される所は実際にあったため、その作業が結構楽になると思います。rsyncなりscpなりのシェルスクリプトを書かなくて良いだけでもどんだけ楽か…

個人的にはrsyncコマンドの--bwlimit的なオプションがあったら良いのになぁと。一気にコピー元に対して読み取り負荷がかかるのはサービス中だとちょっと… となりますので。

gtidに絡みそうなオプションも見受けられたため、何かしら重要そうな点があったら何かしらまた書くと思います。