MySQL には binlog を出力する機能があります。binlog はレプリケーションなどで利用されます。binlog のフォーマットには STATEMENT,ROW,MIXED の三種類があります。MIXED は残り二つの混在モードのようなものですので STATEMENT,ROW の二つについて具体的に紹介します。検証用 MySQL のバージョンは 5.5 です。

参考ページ

MySQL Internals Manual::Chapter 19 The Binary Log

準備

まずはサービスを停止します。これを忘れると後々設定が反映されずに困ります。

$ sudo service mysqld stop

my.cnf を編集して binlog を出力するように設定しておきましょう。ついでに NFS など外部ストレージを /data にマウントする場合を想定したその他の設定も行います。

$ sudo cp /etc/my.cnf /etc/my.cnf.backup
$ sudo vi /etc/my.cnf

設定例

[mysqld]  # mysqld の設定
server-id=000001  # mysql サーバ ID
log-bin=mysql-bin  # binlog ファイル名
datadir=/data/mysql  # binlog などの出力先ディレクトリ
socket=/tmp/mysql.sock  # UNIX ドメインソケット
user=mysql  # mysqld ユーザ
binlog_format=2  # binlog フォーマット (0:MIXED, 1:STATEMENT, 2:ROW) ★

[mysqld_safe]  # mysqld_save の設定
log-error=/var/log/mysqld.log  # ログファイル
pid-file=/var/run/mysqld/mysqld.pid  # PID ファイル

[mysql]  # mysql クライアントの設定
socket=/tmp/mysql.sock  # UNIX ドメインソケット
                        # これを指定しないと mysql -uroot -p -S /tmp/mysql.sock が毎回必要

ディレクトリを作成して既存のものを移動します。

$ sudo mkdir /data
$ sudo mv /var/lib/mysql /data/

サービスの起動

$ sudo service mysqld start

ROW

my.cnf で binlog_format を 2 に設定したので ROW になっています。既定値は 1 STATEMENT です。

mysql> SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.00 sec)

以下のような SQL 文を発行してみます。

mysql> CREATE DATABASE mydb;
mysql> USE mydb;

mysql> CREATE TABLE my_table (
  f1 integer,
  f2 varchar(8)
);

mysql> INSERT INTO my_table VALUES (1,'abcd');

現在までの binlog を確認してみましょう。STATEMENT の SQL 文 (UPDATE,INSERT,DELETE) が base64 エンコードされた binlog になっています。

$ sudo mysqlbinlog /data/mysql/mysql-bin.000001 | less
...
CREATE DATABASE mydb
...
BINLOG '
qZQ3VRMBAAAAMgAAAKoBAAAAACEAAAAAAAEABG15ZGIACG15X3RhYmxlAAIDDwIIAAM=
qZQ3VRcBAAAAJwAAANEBAAAAACEAAAAAAAEAAv/8AQAAAARhYmNk
...

以下のコマンドで base64 デコードした状態で閲覧できます。

$ sudo mysqlbinlog /data/mysql/mysql-bin.000001 --base64-output=DECODE-ROWS -v | less
...
### INSERT INTO `mydb`.`my_table`
### SET
###   @1=1
###   @2='abcd'
...

STATEMENT

再起動すると my.cnf の値に戻ってしまいますが binlog_format は停止せずにオンラインで変更できる設定項目です。

mysql> SET binlog_format = 1;
mysql> SELECT @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| STATEMENT       |
+-----------------+
1 row in set (0.00 sec)

先程と同様に STATEMENT の SQL 文 (UPDATE,INSERT,DELETE) を発行してみましょう。

mysql> INSERT INTO my_table VALUES (2,'ABCD');

今度は base64 エンコードされずに binlog 出力されました。

$ sudo mysqlbinlog /data/mysql/mysql-bin.000001 | less
...
INSERT INTO my_table VALUES (2,'ABCD')
...

ROW と STATEMENT の比較

MySQL 5.5 において既定値は STATEMENT です。今回の例では明らかではありませんが ROW は STATEMENT と比較して情報量が多く従って binlog のサイズも大きくなります。例えば INSERT 文で AUTOINCREMENT や DEFAULT の列を空欄にして発行したとき、ROW の場合は実際に使用された値が binlog に書き込まれますが STATEMENT の場合はこの情報が落ちます。つまり STATEMENT の binlog は base64 エンコードされておらず視認性は一見よいかもしれませんが実際にどんな値が使用されたのかが binlog だけではよく分からないという欠点を有します。レプリケーション時にも ROW の方がスレーブはマスターの状態からずれにくくなります。