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

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: