8

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

RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
Glasnhost
  • 378
  • 1
  • 5
  • 14

4 Answers4

11

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

Glasnhost
  • 378
  • 1
  • 5
  • 14
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.

RolandoMySQLDBA
  • 171,728
  • 30
  • 293
  • 486
  • 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.

Peter
  • 19
  • 2
-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

Dr. Ruud
  • 1
  • 1