6

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

3
  • If I add --short-form I see only SET @@SESSION.GTID_NEXT='...' – Glasnhost May 15 '15 at 17:41
  • 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 May 15 '15 at 17:48
  • ok, it seems all table column are listed in order @1,@2 etc – Glasnhost May 15 '15 at 18:08
9

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

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.

2
  • 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! – Glasnhost May 15 '15 at 18:17
  • 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. – RolandoMySQLDBA May 15 '15 at 18:21
1

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.

-3

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

2
  • 6
    Please add some explanation - what will this do that the command in the OP won't? – dezso Apr 8 '19 at 12:57
  • Please edit your answer rather than replying in a comment. – Colin 't Hart Apr 10 '19 at 18:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.