MySQL Cluster: How to decode/interpret binary logs?

Question

I'm trying in vain to get some useful information from the binary log of my 4-node MySQL cluster. As a test, I manually executed an UPDATE statement at 11:01 am on the master (and it did successfully change some data in a table). Unfortunately, I can't get mysqlbinlog to show me the statement executed.

I've tried the --verbose option, the --hexdump option, and --base64-output=DECODE-ROWS --verbose. No matter what I do, the actual statements seem to still be encoded (or there's an INSERT INTO when there should be an UPDATE).

Below is the relevant output of my 11:01 am test, with the --base64-output=DECODE-ROWS --verbose options specified. Why is my UPDATE statement nowhere to be found? And, if the database optimized it to INSERT INTO, why don't I at least see the table name and other portions of my original statement?

# at 1744
#121213 11:01:56 server id 1  end_log_pos 1808  Query   thread_id=0     exec_time=0   error_code=0
SET TIMESTAMP=1355425316/*!*/;
BEGIN
/*!*/;
# at 1808
# at 1899
# at 1961
# at 2022
#121213 11:01:56 server id 1  end_log_pos 1899  Table_map: `source_words`.`players` mapped to number 1826
#121213 11:01:56 server id 1  end_log_pos 1961  Table_map: `mysql`.`ndb_apply_status` mapped to number 1817
#121213 11:01:56 server id 1  end_log_pos 2022  Write_rows: table id 1817
#121213 11:01:56 server id 1  end_log_pos 2071  Write_rows: table id 1826 flags: STMT_END_F
### INSERT INTO mysql.ndb_apply_status
### SET
###   @1=1
###   @2=14197993649471490
###   @3=''
###   @4=0
###   @5=0
### INSERT INTO source_words.players
### SET
###   @1='834770J\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00\x00!\x00\x00\x00\x00\x00\x00\x00\x17yê\x00\x00\x17yê\x00\x00 \x00\x00\x00\x00\x00\x00\x00À'
###   @6=0
# at 2071
#121213 11:01:56 server id 1  end_log_pos 2136  Query   thread_id=0     exec_time=0   error_code=0
SET TIMESTAMP=1355425316/*!*/;
COMMIT
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Thanks, Rob

score 1 · Accepted Answer · answered Dec 13 '12 at 19:31

The binlog records what alters, not necessarily what statement caused it (or if an update doesn't update anything, nothing will be shown either). If your BINLOG_FORMAT is STATEMENT, al would be statements, for ROW & MIXED, you get these entries where and when MySQL thinks it's apropriate. The binlog is NOT to monitor / examine the queries on your database (use the general query log for that), the binlog is only there to records alterations in data so you can reliably recover a backup or employ replication. It was never intended to be human readable.

Also note that there's possible settings what will and won't be binlogged, and your 'default schema' has quite a lot of influence on it on a connection. If you are really missing a statement (note that old logs get rotated), I'd advise to SHOW VARIABLES LIKE '%binlog%';

MySQL Cluster: How to decode/interpret binary logs?

1 Answers1