DB:MySQL 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
DB:MySQL 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')
;
关于使用MySQL binlog对数据进行恢复的实战 http://www.68idc.cn/Linux/2016-01/127808.htm
MySQL 5.6.12切换binlog二进制日志路径 http://www.68idc.cn/Linux/2016-01/126979.htm
mysqlbinlog解析binlog乱码问题解密 http://www.68idc.cn/Linux/2016-01/126978.htm
MySQL通过binlog来恢复数据 http://www.68idc.cn/Linux/2015-12/126897.htm
MySQL binlog 组提交与 XA(两阶段提交) http://www.68idc.cn/Linux/2015-11/124942.htm
MySQL使用备份和binlog进行数据恢复 http://www.68idc.cn/Linux/2014-12/110875.htm