mysql dba系统学习（17）mysql的备份和恢复的完整实践

mysql dba系统学习（17）mysql的备份和恢复的完整实践2014-06-25mysql的备份和恢复的完整实践

一,备份数据库之间的环境设置

1，创建数据库test1，创建表tt插入如下数据

mysql> createdatabase test1;Query OK, 1 row affected （0.04 sec）mysql> use test1Database changedmysql> create table tt（id int,name varchar（100）,msg varchar（200）） engine=myisam;Query OK, 0 rows affected （0.18 sec）mysql> insert into tt values（1,"chenzhongyang","how are you"）;Query OK, 1 row affected （0.00 sec）mysql> insert into tt values（2,"tianhongyan","BMW"）;Query OK, 1 row affected （0.00 sec）mysql> insert into tt values（3,"jisuanji","why"）;Query OK, 1 row affected （0.00 sec）

2，由于我设置的二进制日志文件的记录格式是row，所以每一行的数据改变就会记录一次日志

mysql>showvariableslike"%format%"

->;

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

|Variable_name|Value|

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

|binlog_format|ROW|

3，此时只有一个二进制日志文件

mysql>showbinarylogs;

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

|Log_name|File_size|

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

|mysqlbin.000161|1133|

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

1rowinset（0.00sec）

4，查看二进制日志文件的内容

二进制日志文件end_log_pos1133

[root@test4 ~]# mysqlbinlog "/tmp/mysqlbin.000161"。。。。。。。。。。。。。。。。。。。# at 588#130905 22:26:42 server id 1end_log_pos 658 Query thread_id=7 exec_time=0 error_code=0SET TIMESTAMP=1378391202/*！*/;COMMIT/*！*/;# at 658#130905 22:27:15 server id 1end_log_pos 727 Query thread_id=7 exec_time=0 error_code=0SET TIMESTAMP=1378391235/*！*/;BEGIN/*！*/;# at 727# at 775#130905 22:27:15 server id 1end_log_pos 775 Table_map: `test1`.`tt` mapped to number 21#130905 22:27:15 server id 1end_log_pos 827 Write_rows: table id 21 flags: STMT_END_FBINLOG "w5QoUhMBAAAAMAAAAAcDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIHw5QoUhcBAAAANAAAADsDAAAAABUAAAAAAAEAA//4AgAAAAsAdGlhbmhvbmd5YW4DAEJNVw=="/*！*/;# at 827#130905 22:27:15 server id 1end_log_pos 897 Query thread_id=7 exec_time=0 error_code=0SET TIMESTAMP=1378391235/*！*/;COMMIT/*！*/;# at 897#130905 22:27:56 server id 1end_log_pos 966 Query thread_id=7 exec_time=0 error_code=0SET TIMESTAMP=1378391276/*！*/;BEGIN/*！*/;# at 966# at 1014#130905 22:27:56 server id 1end_log_pos 1014Table_map: `test1`.`tt` mapped to number 21#130905 22:27:56 server id 1end_log_pos 1063Write_rows: table id 21 flags: STMT_END_FBINLOG "7JQoUhMBAAAAMAAAAPYDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH7JQoUhcBAAAAMQAAACcEAAAAABUAAAAAAAEAA//4AwAAAAgAamlzdWFuamkDAHdoeQ=="/*！*/;# at 1063#130905 22:27:56 server id 1end_log_pos 1133Query thread_id=7 exec_time=0 error_code=0SET TIMESTAMP=1378391276/*！*/;COMMIT/*！*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;/*！50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;