Show sql statements from mysql binlog (GTID)

Question

I have mysql binlogs for replication through GTID.

I'm trying to show executed update/insert statements like this:

mysqlbinlog --base64-output=DECODE-ROWS mysql-bin.000024

But all I see is something like this, no traces of update or insert stmt:

SET TIMESTAMP=1431681617/*!*/;
BEGIN
/*!*/;
# at 746987321
# at 746987392
# at 746987484
#150515 11:20:17 server id 1  end_log_pos 746987515 CRC32 0xeb874754    Xid = 997501767
COMMIT/*!*/;
# at 746987515
#150515 11:20:22 server id 1  end_log_pos 746987563 CRC32 0xc5ece64a    GTID [commit=yes]
SET @@SESSION.GTID_NEXT= 'a4ade293-c63a-11e4-94cf-005056944a56:2059057'/*!*/;
# at 746987563
#150515 11:20:22 server id 1  end_log_pos 746987650 CRC32 0x92296355    Query   thread_id=71622 exec_time=0 error_code=0

I'm not sure about format/configuration of GTID replication and where to look for it...

Answer 1

It seems that the option --verbose must be added:

mysqlbinlog  --base64-output=AUTO --verbose mysql-bin.000005 

In the result you see:

### UPDATE `customer`
### WHERE
###   @1=388442
###   @2=382023
###   @3='2015:05:30'
###   @4='2015:06:02'
###   @5=3
###   @6=1
###   @7=0

@x are the table columns in their order

Answer 2

I don't think GTID is your issue.

You are probably using row based binary logging

To verify this, run one of the following:

SELECT @@global.binlog_format;
SHOW GLOBAL VARIABLES LIKE 'binlog_format';
SELECT variable_value FROM information_schema.global_variables
WHERE variable_name='binlog_format';

You will either see ROW or MIXED. The only way to see the SQL, you would have to set binlog_format to STATEMENT in my.cnf and restart mysqld because the MySQL Documentation on Replication with Global Transaction Identifiers says in the first paragraph:

You can use either statement-based or row-based replication with GTIDs (see Section 17.1.2, “Replication Formats”); however, for best results, we recommend that you use the row-based format.

Nevertheless, you are not going to see the actual SQL with the given binary logs.

Answer 3

Try this tool binlog2sql, it parse the bin log to update/insert statements.
Its usage description is in Chinese, but I believe you can get the point from the example command.

Answer 4

To see the details of the transaction by GTID (may not be the actual SQL, may instead be the changes applied - either way, it's the [human-readable version of the] info that would be supplied to the replica for it to apply to match the primary):

Locate the binlog file concerned on the primary (the file's modification time may be helpful here), then ask for the details of the GTID (if troubleshooting a replica, Last_Error in show replica status \G output may help).

...for an example transaction ID of 1234 (the bit before the : is the source ID, unique to your primary),

# mysqlbinlog --include-gtids=...:1234 -vv --base64-output=DECODE-ROWS mysql-bin.5678 | grep -v '^# at ' > /tmp/results_file

(the grep removes some unnecessary info lines) then review the resulting file to see what happened for that GTID. Note that if the transaction isn't found you'll get a few lines of boilerplate SQL SET commands; if it is there you'll get these as well as the actual transaction.

Answer 5

I prefer to use

mysqlbinlog -v --base64-output=DECODE-ROWS /files/

because:

• "If no --base64-output option is given, the effect is the same as --base64-output=AUTO"
• "The combination of --base64-output=DECODE-ROWS and --verbose provides a convenient way to see row events only as SQL statements" (it surpresses the BINLOG statements)

"Specify --verbose or -v twice to also display data types and some metadata for each column."

Source of quotes: man mysqlbinlog