mysql的备份和恢复的完整实践
一,备份数据库之间的环境设置
1，创建数据库test1，创建表tt插入如下数据
1 2 3 4 5 6 7 8 9 10 11 12 | mysql> create database test1;Query OK, 1row affected (0.04sec)mysql> usetest1Database changedmysql> create table tt(id int,name varchar(100),msg varchar(200)) engine=myisam;Query OK, 0rows affected (0.18sec)mysql> insert into tt values(1,‘chenzhongyang‘,‘how are you‘);Query OK, 1row affected (0.00sec)mysql> insert into tt values(2,‘tianhongyan‘,‘BMW‘);Query OK, 1row affected (0.00sec)mysql> insert into tt values(3,‘jisuanji‘,‘why‘);Query OK, 1row affected (0.00sec) |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 | [root@test4 ~]# mysqlbinlog ‘/tmp/mysqlbin.000161‘。。。。。。。。。。。。。。。。。。。# at 588#13090522:26:42server id 1end_log_pos 658Query thread_id=7exec_time=0error_code=0SET TIMESTAMP=1378391202/*!*/;COMMIT/*!*/;# at 658#13090522:27:15server id 1end_log_pos 727Query thread_id=7exec_time=0error_code=0SET TIMESTAMP=1378391235/*!*/;BEGIN/*!*/;# at 727# at 775#13090522:27:15server id 1end_log_pos 775Table_map: `test1`.`tt` mapped to number 21#13090522:27:15server id 1end_log_pos 827Write_rows: table id 21flags: STMT_END_FBINLOG ‘w5QoUhMBAAAAMAAAAAcDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIHw5QoUhcBAAAANAAAADsDAAAAABUAAAAAAAEAA//4AgAAAAsAdGlhbmhvbmd5YW4DAEJNVw==‘/*!*/;# at 827#13090522:27:15server id 1end_log_pos 897Query thread_id=7exec_time=0error_code=0SET TIMESTAMP=1378391235/*!*/;COMMIT/*!*/;# at 897#13090522:27:56server id 1end_log_pos 966Query thread_id=7exec_time=0error_code=0SET TIMESTAMP=1378391276/*!*/;BEGIN/*!*/;# at 966# at 1014#13090522:27:56server id 1end_log_pos 1014Table_map: `test1`.`tt` mapped to number 21#13090522:27:56server id 1end_log_pos 1063Write_rows: table id 21flags: STMT_END_FBINLOG ‘7JQoUhMBAAAAMAAAAPYDAAAAABUAAAAAAAEABXRlc3QxAAJ0dAADAw8PBCwBWAIH7JQoUhcBAAAAMQAAACcEAAAAABUAAAAAAAEAA//4AwAAAAgAamlzdWFuamkDAHdoeQ==‘/*!*/;# at 1063#13090522:27:56server id 1end_log_pos 1133Query thread_id=7exec_time=0error_code=0SET TIMESTAMP=1378391276/*!*/;COMMIT/*!*/;DELIMITER ;# End of log fileROLLBACK /* added by mysqlbinlog */;
五，还原数据库 这个时候我们发现只恢复了三条数据，但是我们一共有五条数据，这个时候就要通过二进制日志文件来恢复了。 注意的是当我们在恢复数据库的时候也会产生二进制日志文件，所以一定要分清楚备份前的二进制日志文件和恢复之后的二进制日志文件 mysql>dropdatabasetest1; QueryOK,0rowsaffected(0.00sec) mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |test| +--------------------+ 3rowsinset(0.00sec) [root@test4~]#mysql-uroot-p123456</tmp/test1.sql mysql>showdatabases; +--------------------+ |Database| +--------------------+ |information_schema| |mysql| |test| |test1| +--------------------+ 4rowsinset(0.00sec) mysql>usetest1 Databasechanged mysql>select*fromtt; +------+---------------+-------------+ |id|name|msg| +------+---------------+-------------+ |1|chenzhongyang|howareyou| |2|tianhongyan|BMW| |3|jisuanji|why| +------+---------------+-------------+ 3rowsinset(0.00sec) 六，恢复到误操作之前恢复其他的两条数据 这是时候恢复就要从开始备份的位置到删除表位置。因为这个位置是插入另外两条数据的位置 我们可以很清楚的看到这两条数据 正式开始恢复数据 [root@test4~]#mysqlbinlog--start-position=1133--stop-position=1622-vv/tmp/mysqlbin.000161|mysql-uroot-p123456 这个时候数据就回来了 mysql>select*fromtt; +------+---------------+----------------------+ |id|name|msg| +------+---------------+----------------------+ |1|chenzhongyang|howareyou| |2|tianhongyan|BMW| |3|jisuanji|why| |4|shuijunyi|boss| |5|zhujun|mayIknowyourname| +------+---------------+----------------------+ 5rowsinset(0.00sec) |
本文出自 “Linux运维” 博客，请务必保留此出处http://2853725.blog.51cto.com/2843725/1379913
mysql的备份和恢复的完整实践,布布扣,bubuko.com
原文：http://2853725.blog.51cto.com/2843725/1379913