バイナリログのメモ

マスタでとあるクエリを実行した時のステータス。

mysql> create database hogehoge;
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000001 |      456 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

その時のマスタのバイナリログ。

shell> ll
total 21188
-rw-rw---- 1 mysql mysql      456 May  2 12:26 mysqld-bin.000001
shell> mysqlbinlog mysqld-bin.000001
(省略)
# at 365
#110502 12:26:39 server id 1  end_log_pos 456   Query   thread_id=11    exec_time=0     error_code=0
SET TIMESTAMP=1304353599/*!*/;
create database hogehoge
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

スレーブのステータス。

mysql> show slave status \G
*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: ip-XXX-XXX-XXX-XXX
                Master_User: repl
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: mysqld-bin.000001
        Read_Master_Log_Pos: 456
             Relay_Log_File: mysqld-relay-bin.000006
              Relay_Log_Pos: 327
      Relay_Master_Log_File: mysqld-bin.000001
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:

                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 456
            Relay_Log_Space: 327
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 0
1 row in set (0.00 sec)

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mysqld-bin.000002 |      189 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

リレーログ。

shell> ll mysqld-relay-bin.000006
-rw-rw---- 1 mysql mysql 327 May  2 12:26 mysqld-relay-bin.000006
shell> mysqlbinlog mysqld-relay-bin.000006
(省略)
# at 236
#110502 12:26:39 server id 1  end_log_pos 456   Query   thread_id=11    exec_time=0error_code=0
SET TIMESTAMP=1304353599/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
create database hogehoge
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

バイナリログ。

shell> ll mysqld-bin.000002
-rw-rw---- 1 mysql mysql 189 May  2 12:26 mysqld-bin.000002
shell> mysqlbinlog mysqld-bin.000002
(省略)
# at 98
#110502 12:26:39 server id 1  end_log_pos 189   Query   thread_id=11    exec_time=4294967295        error_code=0
SET TIMESTAMP=1304353599/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=1, @@session.unique_checks=1/*!*/;
SET @@session.sql_mode=0/*!*/;
/*!\C latin1 *//*!*/;
SET @@session.character_set_client=8,@@session.collation_connection=8,@@session.collation_server=8/*!*/;
create database hogehoge
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;