Today, a question suddenly comes to mind. Is the DELETE, DROP, TRUNCATE command the same as the binlog in ROW format? It's not the same to remember vaguely, but it's not sure; so let's take a look at the experiment.

MySQL:5.6.20
MySQL:5.7.22

1. View binlog settings

mysql> show  variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.00 sec)

Make sure that the current session binlog uses the ROW format.

2. Prepare experimental data

mysql> create table tt (  id int not null, name varchar(20) ,primary key(id) ) engine=innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> insert into tt values(1,'a'),(2,'b'),(3,'c');
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> select * from tt;
+----+------+
| id | name |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
3 rows in set (0.00 sec)

Test table tt and 3 records are prepared;

3. Execute TRUNCATE command

mysql> truncate table tt;
Query OK, 0 rows affected (0.01 sec)

4. Parsing binlog to view the record content

# mysqlbinlog --no-defaults --set-charset=utf8 --skip-gtids -vv --base64-output=decode-rows mysql-bin.000009
...
# at 486
#200519 16:57:42 server id 6501  end_log_pos 570 CRC32 0x5ca07de7       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1589878662/*!*/;
truncate table tt
/*!*/;
DELIMITER ;

...

As you can see, for TRUNCATE command, binlog in row format only records the original SQL command.

5. DROP command verification
Skip the data preparation process and give the results directly

...
# at 777
#200519 16:58:00 server id 6501  end_log_pos 892 CRC32 0x5ce03bb6       Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1589878680/*!*/;
DROP TABLE `tt` /* generated by server */
/*!*/;
DELIMITER ;
...

As you can see, for the DROP command, the binlog in row format only records the original SQL command.

6. DELETE command validation
Skip the data preparation process and give the results directly

# at 1258
#200519 17:28:40 server id 6501  end_log_pos 1330 CRC32 0xb89c707e      Query   thread_id=3     exec_time=0     error_code=0
SET TIMESTAMP=1589880520/*!*/;
BEGIN
/*!*/;
# at 1330
#200519 17:28:40 server id 6501  end_log_pos 1378 CRC32 0x76ba3836      Table_map: `test`.`tt` mapped to number 74
# at 1378
#200519 17:28:40 server id 6501  end_log_pos 1428 CRC32 0xc9b8282a      Delete_rows: table id 74 flags: STMT_END_F
### DELETE FROM `test`.`tt`
### WHERE
###   @1=1 /* INT meta=0 nullable=0 is_null=0 */
### DELETE FROM `test`.`tt`
### WHERE
###   @1=2 /* INT meta=0 nullable=0 is_null=0 */
### DELETE FROM `test`.`tt`
### WHERE
###   @1=3 /* INT meta=0 nullable=0 is_null=0 */
# at 1428
#200519 17:28:40 server id 6501  end_log_pos 1459 CRC32 0x3f78e3be      Xid = 49
COMMIT/*!*/;
DELIMITER ;

As you can see, for the DELETE command, the binlog in row format records the details of each data.

explain:
The test results on MySQL 5.6 and MySQL 5.7 are the same.

complete!