Use Linux commands to filter out the complete SQL statements in binlog

Use Linux commands to filter out the complete SQL statements in binlog

DB: MySQL 5.6.16
CentOS: CentOS release 6.3 (Final)

How to extract the complete insert statement when an insert statement is input across rows using spaces!

Create an empty table:
Mysql> create table yoon as select * from sakila. actor where 1 = 0;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

View the table name:
Mysql> show tables;
+ ---------------- +
| Tables_in_yoon |
+ ---------------- +
| Yoon |
+ ---------------- +
1 row in set (0.00 sec)

View data:
Mysql> select * from yoon;
Empty set (0.00 sec)

View the table structure:
Mysql> desc yoon;
+ ------------- + ---------------------- + ------ + ----- + ------------------- + ----------------------------- +
| Field | Type | Null | Key | Default | Extra |
+ ------------- + ---------------------- + ------ + ----- + ------------------- + ----------------------------- +
| Actor_id | smallint (8) unsigned | NO | 0 |
| First_name | varchar (45) | NO | NULL |
| Last_name | varchar (45) | NO | NULL |
| Last_update | timestamp | NO | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+ ------------- + ---------------------- + ------ + ----- + ------------------- + ----------------------------- +
4 rows in set (0.00 sec)

Insert test data using the exaggerated line method:
Mysql> insert into yoon
->
-> Values
->
-> (1, 'yoon', 'Hank', 04:34:33)
->
->;

View the mysql-binlog log:
[Root @ hank-yoon data] # ls
Auto. cnf hank ibdata1 ib_logfile0 ib_logfile2 mysql-bin.000043 mysql-bin.000045 performance_schema test yoon. SQL
Binlog-rollback.pl hank. SQL ibdata2 ib_logfile1 mysql mysql-bin.000044 mysql-bin.index sakila yoon

Convert binlog data to yoon. SQL:
[Root @ hank-yoon data] # mysqlbinlog mysql-bin.000045> yoon. SQL

Filter out the insert statement:
[Root @ hank-yoon data] # more yoon. SQL | grep -- ignore-case-E 'insert'-A2-B2 | grep yoon> hank. SQL

View the insert statement and find that the SQL statement inserted across rows ends as of insert into yoon:
[Root @ hank-yoon data] # cat hank. SQL
Insert into yoon (first_name, last_name) select first_name, last_name from hank
Insert into yoon

You can run the following command to view the complete SQL statement, even if it is inserted across rows, the points (;) will be carried to you:
[Root @ thank-yoon data] # sed-n "/insert into yoon/,/;/p" yoon. SQL | sed's #\/\*! \*\/##'
Insert into yoon (first_name, last_name) select first_name, last_name from hank
;
Insert into yoon

Values

(1, 'yoon', 'Hank', '2017-02-15 04:34:33 ')
;

Practices for restoring data using MySQL binlog

MySQL 5.6.12 switch binlog binary Log Path

Mysqlbinlog parsing binlog garbled issue decryption

MySQL uses binlog to restore Data

MySQL binlog group submission and XA (two-phase commit)

MySQL uses backup and binlog for data recovery

This article permanently updates the link address: