[MDEV-25369] mysqlbinlog (mariadb-binlog) -T/--table option Created: 2021-04-08  Updated: 2023-09-11  Resolved: 2023-09-07

Status: Closed
Project: MariaDB Server
Component/s: Scripts & Clients
Affects Version/s: 10.4.18, 10.5.9
Fix Version/s: 10.4.32, 10.5.23, 10.6.16, 10.10.7, 10.11.6, 11.0.4, 11.1.3

Type: Bug Priority: Major
Reporter: V H Lemoine Assignee: Sergei Golubchik
Resolution: Fixed Votes: 0
Labels: None
Environment:

Ubuntu 18.04 / Ubuntu 20.04


 Description   

option --table=tablename for mysqlbinlog is shown in mysqlbinlog --help but not documented online. Specifying this option does not seem to select SQL statements for the specified table.

What is the purpose of this option and what should it do?

2023 08 16 - If, according to the current documentation (--table (-T)), list entries for just this table (local log only). Then this is a straight-up bug rather than a documentation bug. Current behavior, when satisfying all conditions (using a local binary log file), is that it is failing to filter for the table given. It is effectively ignoring -T.

 Comments   
Comment by Alice Sherepa [ 2021-04-14 ]

This option was added to mysqlbinlog during the work on the flashback feature https://mariadb.com/kb/en/flashback/. (MDEV-10570) 

-T, --table= List entries for just this table (local log only).

So -vv --table=X will have dml for table X +all ddl, while --flashback --table=X only inverted dml for the table X (INSERT to DELETE, DELETE to INSERT, and for UPDATEs the before and after images are swapped). For example:

CREATE TABLE t1 (i int);
 
CREATE TABLE t2 (i int);
 
 
 
INSERT into t1 values (1),(2);
 
INSERT into t2 values (3),(4);
 
 
 
CREATE TABLE t3 (i int);
 
UPDATE t1 set i=100;

mysqlbinlog -v --table=t1

CREATE TABLE t1 (i int)
 
/*!*/;
 
# at 461
 
#210414 13:22:37 server id 1  end_log_pos 503 CRC32 0x6e5bb1a1 	GTID 0-1-2 ddl
 
/*!100001 SET @@session.gtid_seq_no=2*//*!*/;
 
# at 503
 
#210414 13:22:37 server id 1  end_log_pos 593 CRC32 0xb1521a92 	Query	thread_id=10	exec_time=0	error_code=0
 
SET TIMESTAMP=1618399357/*!*/;
 
CREATE TABLE t2 (i int)
 
/*!*/;
 
# at 593
 
#210414 13:22:37 server id 1  end_log_pos 635 CRC32 0xf2f14206 	GTID 0-1-3
 
/*!100001 SET @@session.gtid_seq_no=3*//*!*/;
 
START TRANSACTION
 
/*!*/;
 
# at 635
 
# at 687
 
#210414 13:22:37 server id 1  end_log_pos 687 CRC32 0x80ab464d 	Annotate_rows:
 
#Q> INSERT into t1 values (1),(2)
 
#210414 13:22:37 server id 1  end_log_pos 732 CRC32 0xefae12b5 	Table_map: `test`.`t1` mapped to number 32
 
# at 732
 
#210414 13:22:37 server id 1  end_log_pos 775 CRC32 0x3e42cf41 	Write_rows: table id 32 flags: STMT_END_F
 
 
 
BINLOG '
 
fdB2YBMBAAAALQAAANwCAAAAACAAAAAAAAEABHRlc3QAAnQxAAEDAAG1Eq7v
 
fdB2YBcBAAAAKwAAAAcDAAAAACAAAAAAAAEAAf/+AQAAAP4CAAAAQc9CPg==
 
'/*!*/;
 
### INSERT INTO `test`.`t1`
 
### SET
 
###   @1=1
 
### INSERT INTO `test`.`t1`
 
### SET
 
###   @1=2
 
# Number of rows: 2
 
# at 775
 
#210414 13:22:37 server id 1  end_log_pos 848 CRC32 0xea6a5861 	Query	thread_id=10	exec_time=0	error_code=0
 
SET TIMESTAMP=1618399357/*!*/;
 
COMMIT
 
/*!*/;
 
# at 848
 
#210414 13:22:37 server id 1  end_log_pos 890 CRC32 0x9c613c64 	GTID 0-1-4
 
/*!100001 SET @@session.gtid_seq_no=4*//*!*/;
 
START TRANSACTION
 
/*!*/;
 
# at 890
 
# at 942
 
# at 987
 
# at 1030
 
#210414 13:22:37 server id 1  end_log_pos 1103 CRC32 0xba039fbd 	Query	thread_id=10	exec_time=0	error_code=0
 
SET TIMESTAMP=1618399357/*!*/;
 
COMMIT
 
/*!*/;
 
# at 1103
 
#210414 13:22:37 server id 1  end_log_pos 1145 CRC32 0x26bc0f30 	GTID 0-1-5 ddl
 
/*!100001 SET @@session.gtid_seq_no=5*//*!*/;
 
# at 1145
 
#210414 13:22:37 server id 1  end_log_pos 1235 CRC32 0x971cfcbc 	Query	thread_id=10	exec_time=0	error_code=0
 
SET TIMESTAMP=1618399357/*!*/;
 
CREATE TABLE t3 (i int)
 
/*!*/;
 
# at 1235
 
#210414 13:22:37 server id 1  end_log_pos 1277 CRC32 0xbab7c0f9 	GTID 0-1-6
 
/*!100001 SET @@session.gtid_seq_no=6*//*!*/;
 
START TRANSACTION
 
/*!*/;
 
# at 1277
 
# at 1319
 
#210414 13:22:37 server id 1  end_log_pos 1319 CRC32 0xa6134208 	Annotate_rows:
 
#Q> UPDATE t1 set i=100
 
#210414 13:22:37 server id 1  end_log_pos 1364 CRC32 0xea2a325b 	Table_map: `test`.`t1` mapped to number 32
 
# at 1364
 
#210414 13:22:37 server id 1  end_log_pos 1418 CRC32 0x64738dc8 	Update_rows: table id 32 flags: STMT_END_F
 
 
 
BINLOG '
 
fdB2YBMBAAAALQAAAFQFAAAAACAAAAAAAAEABHRlc3QAAnQxAAEDAAFbMirq
 
fdB2YBgBAAAANgAAAIoFAAAAACAAAAAAAAEAAf///gEAAAD+ZAAAAP4CAAAA/mQAAADIjXNk
 
'/*!*/;
 
### UPDATE `test`.`t1`
 
### WHERE
 
###   @1=1
 
### SET
 
###   @1=100
 
### UPDATE `test`.`t1`
 
### WHERE
 
###   @1=2
 
### SET
 
###   @1=100
 
# Number of rows: 2
 
# at 1418
 
#210414 13:22:37 server id 1  end_log_pos 1491 CRC32 0x11dd1ff0 	Query	thread_id=10	exec_time=0	error_code=0
 
SET TIMESTAMP=1618399357/*!*/;
 
COMMIT
 
/*!*/;

with --flashback --table=t1 we have update and then delete:

#210414 13:22:37 server id 1  end_log_pos 285 CRC32 0x72f8db35 	Gtid list []
 
#210414 13:22:37 server id 1  end_log_pos 329 CRC32 0x10f5ed58 	Binlog checkpoint master-bin.000001
 
#210414 13:22:37 server id 1  end_log_pos 687 CRC32 0x80ab464d 	Annotate_rows:
 
#Q> INSERT into t1 values (1),(2)
 
#210414 13:22:37 server id 1  end_log_pos 732 CRC32 0xefae12b5 	Table_map: `test`.`t1` mapped to number 32
 
# Number of rows: 2
 
#210414 13:22:37 server id 1  end_log_pos 1319 CRC32 0xa6134208 	Annotate_rows:
 
#Q> UPDATE t1 set i=100
 
#210414 13:22:37 server id 1  end_log_pos 1364 CRC32 0xea2a325b 	Table_map: `test`.`t1` mapped to number 32
 
# Number of rows: 2
 
#210414 13:22:37 server id 1  end_log_pos 1539 CRC32 0x0af3a99d 	Rotate to master-bin.000002  pos: 4
 
#210414 13:22:37 server id 1  end_log_pos 1491 CRC32 0x11dd1ff0 	Query	thread_id=10	exec_time=0	error_code=0
 
SET TIMESTAMP=1618399357/*!*/;
 
START TRANSACTION
 
/*!*/;
 
#210414 13:22:37 server id 1  end_log_pos 1418 CRC32 0x64738dc8 	Update_rows: table id 32 flags: STMT_END_F
 
 
 
BINLOG '
 
fdB2YBMBAAAALQAAAFQFAAAAACAAAAAAAAEABHRlc3QAAnQxAAEDAAFbMirq
 
fdB2YBgBAAAANgAAAIoFAAAAACAAAAAAAAEAAf///mQAAAD+AgAAAP5kAAAA/gEAAADIjXNk
 
'/*!*/;
 
COMMIT
 
/*!*/;
 
#210414 13:22:37 server id 1  end_log_pos 1235 CRC32 0x971cfcbc 	Query	thread_id=10	exec_time=0	error_code=0
 
SET TIMESTAMP=1618399357/*!*/;
 
#210414 13:22:37 server id 1  end_log_pos 1103 CRC32 0xba039fbd 	Query	thread_id=10	exec_time=0	error_code=0
 
SET TIMESTAMP=1618399357/*!*/;
 
START TRANSACTION
 
/*!*/;
 
COMMIT
 
/*!*/;
 
#210414 13:22:37 server id 1  end_log_pos 848 CRC32 0xea6a5861 	Query	thread_id=10	exec_time=0	error_code=0
 
SET TIMESTAMP=1618399357/*!*/;
 
START TRANSACTION
 
/*!*/;
 
#210414 13:22:37 server id 1  end_log_pos 775 CRC32 0x3e42cf41 	Delete_rows: table id 32 flags: STMT_END_F
 
 
 
BINLOG '
 
fdB2YBMBAAAALQAAANwCAAAAACAAAAAAAAEABHRlc3QAAnQxAAEDAAG1Eq7v
 
fdB2YBkBAAAAKwAAAAcDAAAAACAAAAAAAAEAAf/+AgAAAP4BAAAAQc9CPg==
 
'/*!*/;
 
COMMIT
 
/*!*/;

Comment by Ian Gilfillan [ 2021-04-14 ]

Added the brief mention in the man page on Knowlendge Base, I'll leave this open to incorporate a longer description at some point

Comment by Sergei Golubchik [ 2023-08-17 ]

I've clarified the help text to match the alice comment above

Generated at Thu Feb 08 09:37:10 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.