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...

• Glasnhost: If I add --short-form I see only SET @@SESSION.GTID_NEXT='...'
• Glasnhost: oh, If I add --verbose I see something like UPDATE table @1=121212 @2=34343....I don't understand what are the @ parameters though
• Glasnhost: ok, it seems all table column are listed in order @1,@2 etc

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.

• Glasnhost: I have "ROW"...however it seems that I can see the statements with --verbose. The replication format reference in your link is useful to read though, thanks!
• RolandoMySQLDBA: Although you cannot see the original SQL with real column names, at least you know what it should be because you own the actual schema. Thus, row replication is secure in this aspect.

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

• András Váczi: Please add some explanation - what will this do that the command in the OP won't?
• Colin 't Hart: Please edit your answer rather than replying in a comment.