欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！

您现在的位置是：首页 > 数据库

load data方式导入的数据不可以用binlog日志进行恢复，因为binlo_MySQL

程序员文章站 2022-05-27 08:48:51

...

bitsCN.com

load data方式导入的数据不可以用binlog日志进行恢复，因为binlog里面不产生insert sql语句

QQ群里面有人问起这个问题: 用load data 导入数据的时候，在binlog文件中记录的不是insert 语句,这样的话，如果用load data 导入数据，当需要恢复数据库的时候 binlog恢复就不行了

load data local infile '/root/table.txt' into table test.table ;

生成binlog 日志

然后用mysqlbinlog解析binlog日志，再more查看如下：

# at 147405

#130718 3:15:02 server id 1 end_log_pos 147710 Query thread_id=97960 exec_time=0 error_code=0

use test/*!*/;

SET TIMESTAMP=1374117302/*!*/;

SET @@session.sql_mode=0/*!*/;

/*!/C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

create table `test`.`table`(

`c1` varchar(20) ,

`c2` varchar(20) ,

`c3` varchar(20) ,

`c4` varchar(20) ,

`c5` varchar(20) ,

`c6` varchar(20) ,

`c7` varchar(20) ,

`c8` varchar(20) ,

`c9` varchar(20)

)

/*!*/;

# at 147710

#130718 3:17:14 server id 1 end_log_pos 147783 Query thread_id=98119 exec_time=2 error_code=0

SET TIMESTAMP=1374117434/*!*/;

SET @@session.sql_mode=4194304/*!*/;

BEGIN

/*!*/;

# at 147783

#130718 3:17:14 server id 1 end_log_pos 164190

#Begin_load_query: file_id: 1 block_len: 16384

# at 164190

#130718 3:17:14 server id 1 end_log_pos 180597

#Append_block: file_id: 1 block_len: 16384

# at 180597

#130718 3:17:14 server id 1 end_log_pos 197004

#Append_block: file_id: 1 block_len: 16384

# at 197004

#130718 3:17:14 server id 1 end_log_pos 213411

#Append_block: file_id: 1 block_len: 16384

# at 213411

#130718 3:17:14 server id 1 end_log_pos 229818

#Append_block: file_id: 1 block_len: 16384

# at 229818

#130718 3:17:14 server id 1 end_log_pos 246225

#Append_block: file_id: 1 block_len: 16384

# at 246225

#130718 3:17:14 server id 1 end_log_pos 262632

#Append_block: file_id: 1 block_len: 16384

# at 262632

#130718 3:17:14 server id 1 end_log_pos 279039

#Append_block: file_id: 1 block_len: 16384

# at 279039

#130718 3:17:14 server id 1 end_log_pos 295446

#Append_block: file_id: 1 block_len: 16384

# at 295446

#130718 3:17:14 server id 1 end_log_pos 311853

#Append_block: file_id: 1 block_len: 16384

# at 311853

#130718 3:17:14 server id 1 end_log_pos 328260

#Append_block: file_id: 1 block_len: 16384

# at 328260

#130718 3:17:14 server id 1 end_log_pos 344667

#Append_block: file_id: 1 block_len: 16384

# at 344667

#130718 3:17:14 server id 1 end_log_pos 361074

#Append_block: file_id: 1 block_len: 16384

# at 361074

#130718 3:17:14 server id 1 end_log_pos 377481

#Append_block: file_id: 1 block_len: 16384

# at 377481

#130718 3:17:14 server id 1 end_log_pos 393888

#Append_block: file_id: 1 block_len: 16384

# at 393888

#130718 3:17:14 server id 1 end_log_pos 410295

#Append_block: file_id: 1 block_len: 16384

# at 410295

#130718 3:17:14 server id 1 end_log_pos 426702

#Append_block: file_id: 1 block_len: 16384

# at 426702

#130718 3:17:14 server id 1 end_log_pos 443109

#Append_block: file_id: 1 block_len: 16384

# at 443109

#130718 3:17:14 server id 1 end_log_pos 459516

#Append_block: file_id: 1 block_len: 16384

# at 459516

#130718 3:17:14 server id 1 end_log_pos 475923

#Append_block: file_id: 1 block_len: 16384

# at 475923

#130718 3:17:14 server id 1 end_log_pos 492330

#Append_block: file_id: 1 block_len: 16384

# at 492330

#130718 3:17:14 server id 1 end_log_pos 508737

#Append_block: file_id: 1 block_len: 16384

# at 508737

#130718 3:17:14 server id 1 end_log_pos 525144

#Append_block: file_id: 1 block_len: 16384

# at 525144

#130718 3:17:14 server id 1 end_log_pos 541551

#Append_block: file_id: 1 block_len: 16384

# at 541551

#130718 3:17:14 server id 1 end_log_pos 557958

#Append_block: file_id: 1 block_len: 16384

# at 557958

#130718 3:17:14 server id 1 end_log_pos 574365

#Append_block: file_id: 1 block_len: 16384

# at 574365

#130718 3:17:14 server id 1 end_log_pos 590772

#Append_block: file_id: 1 block_len: 16384

# at 590772

#130718 3:17:14 server id 1 end_log_pos 607179

#Append_block: file_id: 1 block_len: 16384

# at 607179

#130718 3:17:14 server id 1 end_log_pos 623586

#Append_block: file_id: 1 block_len: 16384

# at 623586

#130718 3:17:14 server id 1 end_log_pos 639993

#Append_block: file_id: 1 block_len: 16384

# at 639993

#130718 3:17:14 server id 1 end_log_pos 656400

#Append_block: file_id: 1 block_len: 16384

# at 656400

#130718 3:17:14 server id 1 end_log_pos 672807

#Append_block: file_id: 1 block_len: 16384

# at 672807

#130718 3:17:14 server id 1 end_log_pos 689214

#Append_block: file_id: 1 block_len: 16384

# at 689214

#130718 3:17:14 server id 1 end_log_pos 705621

#Append_block: file_id: 1 block_len: 16384

# at 705621

#130718 3:17:14 server id 1 end_log_pos 722028

#Append_block: file_id: 1 block_len: 16384

# at 722028

#130718 3:17:14 server id 1 end_log_pos 738435

#Append_block: file_id: 1 block_len: 16384

# at 738435

#130718 3:17:14 server id 1 end_log_pos 754842

#Append_block: file_id: 1 block_len: 16384

# at 754842

#130718 3:17:14 server id 1 end_log_pos 771249

#Append_block: file_id: 1 block_len: 16384

# at 771249

#130718 3:17:14 server id 1 end_log_pos 787656

#Append_block: file_id: 1 block_len: 16384

# at 787656

#130718 3:17:14 server id 1 end_log_pos 804063

#Append_block: file_id: 1 block_len: 16384

# at 804063

#130718 3:17:14 server id 1 end_log_pos 820470

#Append_block: file_id: 1 block_len: 16384

# at 820470

#130718 3:17:14 server id 1 end_log_pos 836877

#Append_block: file_id: 1 block_len: 16384

# at 836877

#130718 3:17:14 server id 1 end_log_pos 853284

#Append_block: file_id: 1 block_len: 16384

# at 853284

#130718 3:17:14 server id 1 end_log_pos 869691

#Append_block: file_id: 1 block_len: 16384

# at 869691

#130718 3:17:14 server id 1 end_log_pos 886098

#Append_block: file_id: 1 block_len: 16384

# at 886098

#130718 3:17:14 server id 1 end_log_pos 902505

#Append_block: file_id: 1 block_len: 16384

# at 902505

#130718 3:17:14 server id 1 end_log_pos 918912

#Append_block: file_id: 1 block_len: 16384

# at 918912

#130718 3:17:14 server id 1 end_log_pos 935319

#Append_block: file_id: 1 block_len: 16384

# at 935319

#130718 3:17:14 server id 1 end_log_pos 951726

#Append_block: file_id: 1 block_len: 16384

# at 951726

#130718 3:17:14 server id 1 end_log_pos 968133

#Append_block: file_id: 1 block_len: 16384

# at 968133

#130718 3:17:14 server id 1 end_log_pos 984540

#Append_block: file_id: 1 block_len: 16384

# at 984540

#130718 3:17:14 server id 1 end_log_pos 1000947

#Append_block: file_id: 1 block_len: 16384

# at 1000947

#130718 3:17:14 server id 1 end_log_pos 1017354

#Append_block: file_id: 1 block_len: 16384

# at 1017354

#130718 3:17:14 server id 1 end_log_pos 1033761

#Append_block: file_id: 1 block_len: 16384

# at 1033761

#130718 3:17:14 server id 1 end_log_pos 1050168

#Append_block: file_id: 1 block_len: 16384

# at 1050168

#130718 3:17:14 server id 1 end_log_pos 1066575

#Append_block: file_id: 1 block_len: 16384

# at 1066575

#130718 3:17:14 server id 1 end_log_pos 1082982

#Append_block: file_id: 1 block_len: 16384

# at 1082982

#130718 3:17:14 server id 1 end_log_pos 1099389

#Append_block: file_id: 1 block_len: 16384

# at 1099389

#130718 3:17:14 server id 1 end_log_pos 1115796

#Append_block: file_id: 1 block_len: 16384

# at 1115796

#130718 3:17:14 server id 1 end_log_pos 1132203

#Append_block: file_id: 1 block_len: 16384

# at 1132203

#130718 3:17:14 server id 1 end_log_pos 1148610

#Append_block: file_id: 1 block_len: 16384

# at 1148610

#130718 3:17:14 server id 1 end_log_pos 1165017

#Append_block: file_id: 1 block_len: 16384

# at 1165017

#130718 3:17:14 server id 1 end_log_pos 1181424

#Append_block: file_id: 1 block_len: 16384

# at 1181424

#130718 3:17:14 server id 1 end_log_pos 1197831

#Append_block: file_id: 1 block_len: 16384

# at 1197831

#130718 3:17:14 server id 1 end_log_pos 1214238

#Append_block: file_id: 1 block_len: 16384

# at 1214238

#130718 3:17:14 server id 1 end_log_pos 1230645

#Append_block: file_id: 1 block_len: 16384

# at 1230645

#130718 3:17:14 server id 1 end_log_pos 1247052

#Append_block: file_id: 1 block_len: 16384

# at 1247052

#130718 3:17:14 server id 1 end_log_pos 1263459

#Append_block: file_id: 1 block_len: 16384

# at 1263459

#130718 3:17:14 server id 1 end_log_pos 1279866

#Append_block: file_id: 1 block_len: 16384

# at 1279866

#130718 3:17:14 server id 1 end_log_pos 1296273

#Append_block: file_id: 1 block_len: 16384

# at 1296273

#130718 3:17:14 server id 1 end_log_pos 1312680

#Append_block: file_id: 1 block_len: 16384

# at 1312680

#130718 3:17:14 server id 1 end_log_pos 1329087

#Append_block: file_id: 1 block_len: 16384

# at 1329087

#130718 3:17:14 server id 1 end_log_pos 1345494

.........

#Append_block: file_id: 1 block_len: 16384

# at 4298754

#130718 3:17:14 server id 1 end_log_pos 4315161

#Append_block: file_id: 1 block_len: 16384

# at 4315161

#130718 3:17:14 server id 1 end_log_pos 4331568

#Append_block: file_id: 1 block_len: 16384

# at 4331568

#130718 3:17:14 server id 1 end_log_pos 4347975

#Append_block: file_id: 1 block_len: 16384

# at 4347975

#130718 3:17:14 server id 1 end_log_pos 4364382

#Append_block: file_id: 1 block_len: 16384

# at 4364382

#130718 3:17:14 server id 1 end_log_pos 4380789

#Append_block: file_id: 1 block_len: 16384

# at 4380789

#130718 3:17:14 server id 1 end_log_pos 4397196

#Append_block: file_id: 1 block_len: 16384

# at 4397196

#130718 3:17:14 server id 1 end_log_pos 4413603

#Append_block: file_id: 1 block_len: 16384

# at 4413603

#130718 3:17:14 server id 1 end_log_pos 4430010

#Append_block: file_id: 1 block_len: 16384

# at 4430010

#130718 3:17:14 server id 1 end_log_pos 4446417

#Append_block: file_id: 1 block_len: 16384

# at 4446417

#130718 3:17:14 server id 1 end_log_pos 4462824

#Append_block: file_id: 1 block_len: 16384

# at 4462824

#130718 3:17:14 server id 1 end_log_pos 4479231

#Append_block: file_id: 1 block_len: 16384

# at 4479231

#130718 3:17:14 server id 1 end_log_pos 4495638

#Append_block: file_id: 1 block_len: 16384

# at 4495638

#130718 3:17:14 server id 1 end_log_pos 4512045

#Append_block: file_id: 1 block_len: 16384

# at 4512045

#130718 3:17:14 server id 1 end_log_pos 4528452

#Append_block: file_id: 1 block_len: 16384

# at 4528452

#130718 3:17:14 server id 1 end_log_pos 4544859

#Append_block: file_id: 1 block_len: 16384

# at 4544859

#130718 3:17:14 server id 1 end_log_pos 4561266

#Append_block: file_id: 1 block_len: 16384

# at 4561266

#130718 3:17:14 server id 1 end_log_pos 4577673

#Append_block: file_id: 1 block_len: 16384

# at 4577673

#130718 3:17:14 server id 1 end_log_pos 4594080

#Append_block: file_id: 1 block_len: 16384

# at 4594080

#130718 3:17:14 server id 1 end_log_pos 4610487

#Append_block: file_id: 1 block_len: 16384

# at 4610487

#130718 3:17:14 server id 1 end_log_pos 4626894

#Append_block: file_id: 1 block_len: 16384

# at 4626894

#130718 3:17:14 server id 1 end_log_pos 4643301

#Append_block: file_id: 1 block_len: 16384

# at 4643301

#130718 3:17:14 server id 1 end_log_pos 4659708

#Append_block: file_id: 1 block_len: 16384

# at 4659708

#130718 3:17:14 server id 1 end_log_pos 4676115

#Append_block: file_id: 1 block_len: 16384

# at 4676115

#130718 3:17:14 server id 1 end_log_pos 4692522

#Append_block: file_id: 1 block_len: 16384

# at 4692522

#130718 3:17:14 server id 1 end_log_pos 4708929

#Append_block: file_id: 1 block_len: 16384

# at 4708929

#130718 3:17:14 server id 1 end_log_pos 4725336

#Append_block: file_id: 1 block_len: 16384

# at 4725336

#130718 3:17:14 server id 1 end_log_pos 4735417

#Append_block: file_id: 1 block_len: 10058

# at 4735417

#130718 3:17:14 server id 1 end_log_pos 4735701 Execute_load_query thread_id=98119 exec_time=2 error_code=0

SET TIMESTAMP=1374117434/*!*/;

LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-1-0' INTO TABLE `table` FIELDS TERMINATED BY '/t' ENCLOSED BY '' ESCAPED BY '//' LINES TERMINATED BY '/n' (`c1`, `c2`, `c3`, `

c4`, `c5`, `c6`, `c7`, `c8`, `c9`)

/*!*/;

# file_id: 1

# at 4735701

#130718 3:17:14 server id 1 end_log_pos 4735728 Xid = 297745

COMMIT/*!*/;

DELIMITER ;

# End of log file

ROLLBACK /* added by mysqlbinlog */;

/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

确实没有insert之类的sql语句，当用binlog日志进行恢复的时候，会报错的，所以在生产环境上面，最好不要使用load data的方式进行数据导入。以免需要及时恢复数据的时候发生异常。

-- 察看binlog事件,会看到最后的load data local infile纪录.

[sql] view plaincopyprint?

mysql> show binlog events in "mysql-bin.000001";

......

| mysql-bin.000001 | 4708929 | Append_block | 1 | 4725336 | ;file_id=1;block_len=16384 |

| mysql-bin.000001 | 4725336 | Append_block | 1 | 4735417 | ;file_id=1;block_len=10058 |

| mysql-bin.000001 | 4735417 | Execute_load_query | 1 | 4735701 | use `test`; LOAD DATA LOCAL INFILE '/root/table.txt' IGNORE INTO TABLE `table` FIELDS TERMINATED BY '/t' ENCLOSED BY '' ESCAPED BY '//' LINES TERMINATED BY '/n' (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`) ;file_id=1 |

| mysql-bin.000001 | 4735701 | Xid | 1 | 4735728 | COMMIT /* xid=297745 */

-- 再察看从库数据也都同步复制过去了,再看从库的binlog日志

# at 31990800

#130718 11:15:02 server id 1 end_log_pos 31991105 Query thread_id=97960 exec_time=4294967292 error_code=0

use test/*!*/;

SET TIMESTAMP=1374117302/*!*/;

SET @@session.sql_mode=0/*!*/;

/*!/C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;

create table `test`.`table`(

`c1` varchar(20) ,

`c2` varchar(20) ,

`c3` varchar(20) ,

`c4` varchar(20) ,

`c5` varchar(20) ,

`c6` varchar(20) ,

`c7` varchar(20) ,

`c8` varchar(20) ,

`c9` varchar(20)

)

/*!*/;

# at 31991105

#130718 11:17:14 server id 1 end_log_pos 31991169 Query thread_id=98119 exec_time=4 error_code=0

SET TIMESTAMP=1374117434/*!*/;

SET @@session.sql_mode=4194304/*!*/;

BEGIN

/*!*/;

# at 31991169

#130718 11:17:14 server id 1 end_log_pos 34088344

#Begin_load_query: file_id: 1 block_len: 2097152

# at 34088344

#130718 11:17:14 server id 1 end_log_pos 36185519

#Append_block: file_id: 1 block_len: 2097152

# at 36185519

#130718 11:17:14 server id 1 end_log_pos 36572432

#Append_block: file_id: 1 block_len: 386890

# at 36572432

#130718 11:17:14 server id 1 end_log_pos 36572723 Execute_load_query thread_id=98119 exec_time=4 error_code=0

SET TIMESTAMP=1374117434/*!*/;

LOAD DATA LOCAL INFILE '/tmp/SQL_LOAD_MB-1-0' INTO TABLE `table` FIELDS TERMINATED BY '/t' ENCLOSED BY '' ESCAPED BY '//' LINES TERMINATED BY '/n' (`c1`, `c2`, `c3`, `c4`, `c5`, `c6`, `c7`, `c8`, `c9`)

/*!*/;

# file_id: 1

# at 36572723

#130718 11:17:14 server id 1 end_log_pos 36572750 Xid = 839475877

COMMIT/*!*/;

-- 再去主库从库上面的tmp目录察看临时文件存在情况,果然看到SQL_LOAD_MB-1-0文件存在.

[root@eanshlt2mydbc001db002 tmp]# ll -t /tmp/ |more

total 2212772

-rw-r----- 1 root root 4581194 Jul 18 11:46 SQL_LOAD_MB-1-0

bitsCN.com

相关标签： insert 记录

上一篇： php分支树排除某个分支该如何处理？

下一篇： phpcms v9 模板标签说明拾掇

推荐阅读

load data方式导入的数据不可以用binlog日志进行恢复，因为binlo_MySQL