モーダルを閉じる工作HardwareHub ロゴ画像

工作HardwareHubは、ロボット工作や電子工作に関する情報やモノが行き交うコミュニティサイトです。さらに詳しく

利用規約プライバシーポリシー に同意したうえでログインしてください。

工作HardwareHub ロゴ画像 (Laptop端末利用時)
工作HardwareHub ロゴ画像 (Mobile端末利用時)

MySQL binlog のフォーマット (STATEMENT, ROW, MIXED)

モーダルを閉じる

ステッカーを選択してください

モーダルを閉じる

お支払い内容をご確認ください

購入商品
」ステッカーの表示権
メッセージ
料金
(税込)
決済方法
GooglePayマーク
決済プラットフォーム
確認事項

利用規約をご確認のうえお支払いください

※カード情報はGoogleアカウント内に保存されます。本サイトやStripeには保存されません

※記事の執筆者は購入者のユーザー名を知ることができます

※購入後のキャンセルはできません

作成日作成日
2015/04/23
最終更新最終更新
2021/10/07
記事区分記事区分
一般公開

目次

    アカウント プロフィール画像 (サイドバー)

    MySQLの運用やレプリケーション設定など、実用的なノウハウを共有します。

    0
    ステッカーを贈るとは?

    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 の方がスレーブはマスターの状態からずれにくくなります。

    0
    詳細設定を開く/閉じる
    アカウント プロフィール画像 (本文下)

    MySQLの運用やレプリケーション設定など、実用的なノウハウを共有します。

    記事の執筆者にステッカーを贈る

    有益な情報に対するお礼として、またはコメント欄における質問への返答に対するお礼として、 記事の読者は、執筆者に有料のステッカーを贈ることができます。

    さらに詳しく →
    ステッカーを贈る コンセプト画像

    Feedbacks

    Feedbacks コンセプト画像

      ログインするとコメントを投稿できます。

      関連記事

      • MySQL レプリケーション設定 (2段, 3段)
        MySQL DB サーバは自身へのクエリをバイナリログとして書き出すことができます。レプリケーションとは、追加の MySQL DB サーバが、別の MySQL DB サーバが出力したバイナリログを自分のリレーログとよばれるログにコピーして、更にリレーログに記載されたクエリを自分自身のテーブルに実行する機能です。バイナリログを出力する DB をマスターとよび、自分のリレーログにコピーする DB をス...
        しおまめしおまめ10/7/2021に更新
        いいねアイコン画像0
      • MySQL 5.7 インストール手順
        2016/11/07 時点、多くの環境では yum レポジトリ等に mysql 5.7 が含まれていません。公式ページからダウンロードしてインストールする手順を二つまとめます。 yum レポジトリを追加する手順 [Installing MySQL on Linux Using the MySQL Yum Repository (mysql 5.7)](http://dev.mysql.com/do...
        しおまめしおまめ1/12/2021に更新
        いいねアイコン画像0
      • MyBatis 3 サンプルコード (Java/MySQL/Gradle)
        MyBatis は、JDBC を直接利用せずに、XML ファイル等で用意した SQL 文を利用して DB にアクセスするための Java ライブラリです。内部的には JDBC が利用されているため、JDBC のインストールは必要です。同様のライブラリに Hibernate ORM があります。DB は特に MySQL を対象として、MyBatis バージョン 3 の
        HARUTOHARUTO8/19/2019に更新
        いいねアイコン画像0
      • MySQL HandlerSocket Plugin の簡単な使用方法
        HandlerSocket は MySQL プロセス内のスレッドとして動作する、MySQL の NoSQL フロントエンドです。独自のプロトコルを用いて TCP 通信を行います。SQL 構文の解析が不要、プロトコルがシンプルなどの理由によって、単純な DB アクセスが高速に実行可能になります。MariaDB であれば[標準プラグインとして付属](https://mariadb.co
        しおまめしおまめ10/7/2021に更新
        いいねアイコン画像0
      • Spring Boot から MyBatis を利用するための設定 (Gradle/MySQL)
        MyBatis を Spring Boot で利用するための基本的な設定およびサンプルコードをまとめます。サンプルコードにおいては、特に MySQL を対象とします。 MyBatis Spring-Boot-Starter チュートリアル
        suzusuzu10/4/2021に更新
        いいねアイコン画像0