MySQLのバイナリーログからデータ更新過程を確認したい

logo-mysql
CMSなどで同じ記事が何度も更新されたとして、DBにある最終形は別にして、記事が更新された過程を知りたいといことがまれにある。この過程をバイナリーログ(binlog)から復元する方法を RDS MySQL 固有の動きを中心にメモ。

環境は

  • データベースは RDS 版 MySQL(5.6)
  • クライアントは EC2 上の Amazon Linux

バイナリーログの用途

バイナリーログはテーブルへの更新を連続的に記録するもので

  • レプリケーション
  • 差分バックアップ/リストア

などで利用することが可能。

binlog の中を覗いてみる

次にメインの binlog を覗いてみる。

binlog を確認

MySQL に接続して show binary logs コマンドで確認する。

mysql> show binary logs;
+----------------------------+-----------+
| Log_name                   | File_size |
+----------------------------+-----------+
| mysql-bin-changelog.073463 |  14947249 |
| mysql-bin-changelog.073464 |   7125230 |
| mysql-bin-changelog.073465 |   7796951 |
| mysql-bin-changelog.073466 |  14170845 |
+----------------------------+-----------+
4 rows in set (0.00 sec)

binlog をローカルに落とす

binlog のファイル一覧を確認したところで、binlog ファイルをローカルに落として中身を確認する。

$ mysqlbinlog --read-from-remote-server -h HOST_NAME -u USER_NAME -p mysql-bin-changelog.073466 --result-file=mysql-bin-changelog.07346
Enter password:
$ ls
mysql-bin-changelog.073466
$ vi mysql-bin-changelog.073466

あとはファイル内のレプリケーション用SQLからお目当てのSQLを探し当てれば OK。

リカバリ目的などで生の binlog が欲しい時は MySQL5.6 から追加された --raw も渡すこと。バイナリーログも mysqlbinlog に直接食わせればテキスト形式に変換できる。

binlog 関連の設定

binlog retention period

通常の MySQL であれば、バイナリーログの最終更新日からの経過日数に従って自動的にバイナリログを削除するためのオプション expire_logs_days が実装されている。(奥野 pp.93)
しかし、RDB MySQL ではこのオプションを操作できない。

mysql> SHOW VARIABLES LIKE 'expire_logs_days';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| expire_logs_days | 0     |
+------------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL expire_logs_days=3;
ERROR 1227 (42000): Access denied; you need (at least one of) the SUPER privilege(s) for this operation

かわりにストアド経由で binlog の保持期間(binlog retention hours) を操作する。

Amazon RDS normally purges a binary log as soon as possible, but the binary log must still be available on the instance to be accessed by mysqlbinlog. To specify the number of hours for RDS to retain binary logs, use the mysql.rds_set_configuration stored procedure and specify a period with enough time for you to download the logs. After you set the retention period, monitor storage usage for the DB instance to ensure that the retained binary logs do not take up too much storage.
http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.MySQL.html

binlog retention hours のデフォルト値を確認

mysql> call mysql.rds_show_configuration \G
*************************** 1. row ***************************
       name: binlog retention hours
      value: NULL
description: binlog retention hours specifies the duration in hours before binary logs are automatically deleted.
1 row in set (0.01 sec)


Query OK, 0 rows affected (0.01 sec)

ログの保持期間を 72 時間に変更する。

mysql> call mysql.rds_set_configuration('binlog retention hours', 72);
Query OK, 0 rows affected (0.01 sec)


mysql> call mysql.rds_show_configuration \G
*************************** 1. row ***************************
       name: binlog retention hours
      value: 72
description: binlog retention hours specifies the duration in hours before binary logs are automatically deleted.
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ログの保持期間を初期値に戻す

デフォルト値 null に戻すには、ストアドにそのまま null を渡せばよい。

mysql> call mysql.rds_set_configuration('binlog retention hours', null);
Query OK, 0 rows affected (0.01 sec)

mysql> call mysql.rds_show_configuration \G
*************************** 1. row ***************************
       name: binlog retention hours
      value: NULL
description: binlog retention hours specifies the duration in hours before binary logs are automatically deleted.
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

バイナリーログの形式

通常の MySQL はバイナリーログの形式として

  • STATEMENT causes logging to be statement based.
  • ROW causes logging to be row based.
  • MIXED causes logging to use mixed format.

の中から選択可能。

http://dev.mysql.com/doc/refman/5.6/en/replication-options-binary-log.html#sysvar_binlog_format

RDS MySQL は ROW または MIXED が選択可能でデフォルトは MIXED

mysql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | MIXED |
+---------------+-------+
1 row in set (0.01 sec)

ROW にすると、ログサイズは増加する。

MySQL 5.6 Client のインストール

RHEL ベースの Amazon Linux でインストール可能な最新版の MySQL は --enablerepo=epel を有効にしても 5.5 系。
バックアップ・リカバリ目的でバイナリー形式の binlog が欲しい時は mysqlbinlog コマンドに –raw オプションを渡す。
ただし、このオプションは 5.6.0 で導入されたものなので、5.6 の MySQL クライアントをインストールする。

まずは 5.5 系の MySQL クライアントをアンインストールする。

$ mysql -V
mysql  Ver 14.14 Distrib 5.5.42, for Linux (x86_64) using readline 5.1
$ mysqlbinlog -V
mysqlbinlog Ver 3.3 for Linux at x86_64

$ sudo yum remove mysql

次に 5.6 系を rpm からインストールする。
ダウンロードサイト http://dev.mysql.com/downloads/mysql/ からアーキテクチャ(32/64)にあった “Client Utilities” を選択する。

$ sudo yum localinstall http://dev.mysql.com/get/Downloads/MySQL-5.6/MySQL-client-5.6.23-1.el6.x86_64.rpm
...
Installed:
  MySQL-client.x86_64 0:5.6.23-1.el6


Complete!
$ mysql --version
mysql  Ver 14.14 Distrib 5.6.23, for Linux (x86_64) using  EditLine wrapper
$ mysqlbinlog --version
mysqlbinlog Ver 3.4 for Linux at x86_64

5.6 系の MySQL クライアントをインストールできた。

References

Advertisements
Tagged with: ,
Posted in database
3 comments on “MySQLのバイナリーログからデータ更新過程を確認したい
  1. matsubobo says:

    –が-と表記されているようです。

    • matsubobo says:

      ハイフン2つが1つとして表記されてしまっています。

      • siguniang says:

        “$ mysqlbinlog –read-from-remote-server …” の行でよろしいしょうか?
        たしかにハイフンの解釈がおかしかったので、修正しました。

        ご指摘ありがとうございます。

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Archives
%d bloggers like this: