通过Linux命令过滤出binlog中完整的SQL语句

mysql> show tables;
+-------------------+
| Tables_in_test    |
+-------------------+
| jindong_vote      |
| jindong_wholesale |
| users             |
+-------------------+
3 rows in set (0.00 sec)

mysql> desc users;
+----------+--------------+------+-----+---------+----------------+
| Field    | Type         | Null | Key | Default | Extra          |
+----------+--------------+------+-----+---------+----------------+
| id       | int(11)      | NO   | PRI | NULL    | auto_increment |
| name     | varchar(20)  | NO   | UNI | NULL    |                |
| password | varchar(100) | NO   |     | NULL    |                |
| address  | varchar(200) | YES  |     | NULL    |                |
+----------+--------------+------+-----+---------+----------------+
4 rows in set (0.01 sec)

 

mysql> select * from users;
Empty set (0.04 sec)

mysql> 

 

mysql> create table yoon as select * from users ;
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show tables;
+-------------------+
| Tables_in_test    |
+-------------------+
| jindong_vote      |
| jindong_wholesale |
| users             |
| yoon              |
+-------------------+
4 rows in set (0.00 sec)

mysql> desc yoon
    -> ;
+----------+--------------+------+-----+---------+-------+
| Field    | Type         | Null | Key | Default | Extra |
+----------+--------------+------+-----+---------+-------+
| id       | int(11)      | NO   |     | 0       |       |
| name     | varchar(20)  | NO   |     | NULL    |       |
| password | varchar(100) | NO   |     | NULL    |       |
| address  | varchar(200) | YES  |     | NULL    |       |
+----------+--------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

 

mysql> insert into yoon values (1,"bass","bass","beijing");
Query OK, 1 row affected (0.19 sec)

 

mysql> select * from yoon;
+----+------+----------+---------+
| id | name | password | address |
+----+------+----------+---------+
|  1 | bass | bass     | beijing |
+----+------+----------+---------+
1 row in set (0.01 sec)

 

[root@DB data]# mysqlbinlog mysql-bin.000076 >yoon.sql
[root@DB data]# more yoon.sql |grep --ignore-case -E "insert" -A2 -B2|grep yoon >yoon-sql
[root@DB data]# cat yoon-sql 
insert into yoon values (1,"bass","bass","beijing")