mysql bin_log筛选语录_通过Linux命令过滤出binlog中完整的SQL语句

DB：5.6.16

CentOS：CentOS release 6.3 (Final)

当insert语句通过空格跨行输入的时候，如何提取完整的insert语句！

创建一个空表：

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

查看表名：

mysql> show tables;

+----------------+

| Tables_in_yoon |

+----------------+

| yoon           |

+----------------+

1 row in set (0.00 sec)

查看数据：

mysql> select * from yoon;

Empty set (0.00 sec)

查看表结构：

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)

夸行方式插入测试数据：

mysql> insert into yoon

->

-> values

->

-> (1,'YOON','HANK',2006-02-15 04:34:33)

->

-> ;

查看mysql-binlog日志：

[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

将binlog数据转换到yoon.sql：

[root@hank-yoon data]# mysqlbinlog mysql-bin.000045 > yoon.sql

过滤出insert语句：

[root@hank-yoon data]# more yoon.sql | grep --ignore-case -E 'insert' -A2 -B2 | grep yoon > hank.sql

查看insert语句，发现跨行插入的SQL语句截至到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

通过以下命令，可以查看完整的SQL语句，即使是跨行插入，分好(;)都给你带上：

[root@hank-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','2006-02-15 04:34:33')

;