MySQL完全备份、增量备份与恢复实例

场景:每周日执行一次完全备份,每天下午1点执行增量备份.

配置:执行增量备份的前提条件是MySQL打开log-bin 日志开关,例如在my.ini或my.cnf中加入:log-bin=/opt/data/mysql-bin.

“log-bin=”后的字符串为日志记载目录,一般建议放在不同于mysql数据目录的磁盘上.

完全备份:假定星期日下午1点执行完全备份,适用于MyISAM存储引擎.

mysqldump –lock-all-tables –flush-logs –master-data=2 -u root -p test > backup_sunday_1_PM.sql

对于InnoDB 将–lock-all-tables替换为–single-transaction

–flush-logs 为结束当前日志,生成新日志文件

–master-data=2 选项将会在输出SQL中记录下完全备份后新日志文件的名称,用于日后恢复时参考,例如输出的备份SQL文件中含有如下代码:

1. – CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000002′, MASTER_LOG_POS=106; 
2. --master-data[=value] 
3.           Write the binary log filename and position to the output. This option requires the RELOAD privilege and the binary log must be 
4.           enabled. If the option value is equal to 1, the position and filename are written to the dump output in the form of a CHANGE MASTER 
5.           statement. If the dump is from a master server and you use it to set up a slave server, the CHANGE MASTER statement causes the 
6.           slave to start from the correct position in the masters binary logs. If the option value is equal to 2, the CHANGE MASTER 
7.           statement is written as an SQL comment. (This is the default action if value is omitted.) 

其他说明:如果 mysqldump加上–delete-master-logs 则清除以前的日志,以释放空间,但是如果服务器配置为镜像的复制主服务器,用mysqldump –delete-master-logs删掉MySQL二进制日志很危险,因为从服务器可能还没有完全处理该二进制日志的内容,在这种情况下,使用 PURGE MASTER LOGS更为安全.

增量备份:每日定时使用 mysqladmin flush-logs来创建新日志,并结束前一日志写入过程,并把前一日志备份,例如上例中开始保存数据目录下的日志文件 mysql-bin.000002,…

从备份中恢复:

恢复完全备份:mysql -u root -p < backup_sunday_1_PM.sql

恢复增量备份mysqlbinlog mysql-bin.000002 … | mysql -u root -p

注意此次恢复过程亦会写入日志文件,如果数据量很大,建议先关闭日志功能.

binlog增量备份需要注意

1.用到临时表时,恢复binlog需要注意,只能在一个session里恢复所有binlog文件的数据,因为临时表只存在于单个session时间.

--- 如果用到临时表的事务数据跨了多个binlog文件,就得用一个session恢复此多个binlog文件.

2.针对5.0版本的,一定要注意statement binlog模式对create table b select * from a; 只能记录语句而不是数据,如果表a删除了,恢复表b的数据就麻烦了.

--- 如果是statement based binlog,这样种情况你就得先恢复a表再恢复b表.

看我一个公司的实例:

1. 用到临时表时,恢复binlog需要注意,只能在一个session里恢复所有binlog文件的数据,因为临时表只存在于单个session时间.

2. 针对5.0版本的,一定要注意statement binlog模式对create table b select * from a;只能记录语句而不是数据,如果表a删除了,恢复表b的数据就麻烦了.

1. ###刚开始binlog_format为row 
2. create table tbl_a (a int) 
3. /*!*/; 
4. # at 195 
5. #100422 23:23:20 server id 1  end_log_pos 263   Query   thread_id=1     exec_tim 
6. e=0     error_code=0 
7. SET TIMESTAMP=1271949800/*!*/; 
8. BEGIN 
9. /*!*/; 
10. # at 263 
11. # at 307 
12. #100422 23:23:20 server id 1  end_log_pos 307   Table_map: `test`.`tbl_a` mapped 
13. to number 135 
14. #100422 23:23:20 server id 1  end_log_pos 351   Write_rows: table id 135 flags: 
15. STMT_END_F 
16.  
17. BINLOG ' 
18. 6GnQSxMBAAAALAAAADMBAAAAAIcAAAAAAAAABHRlc3QABXRibF9hAAEDAAE= 
19. 6GnQSxcBAAAALAAAAF8BAAAQAIcAAAAAAAEAAf/+AQAAAP4CAAAA/gMAAAA= 
20. '/*!*/; 
21. # at 351 
22. #100422 23:23:20 server id 1  end_log_pos 420   Query   thread_id=1     exec_tim 
23. e=0     error_code=0 
24. SET TIMESTAMP=1271949800/*!*/; 
25. COMMIT 
26. /*!*/; 
27. # at 420 
28. #100422 23:24:43 server id 1  end_log_pos 488   Query   thread_id=1     exec_tim 
29. e=0     error_code=0 
30. SET TIMESTAMP=1271949883/*!*/; 
31. BEGIN 
32. /*!*/; 
33. # at 488 
34.  
35.  
36. ###binlog_format没变，通过create table select from来创建表。在row_based mode下。创建的语句，变成先创建表再插入值 
37. #100422 23:24:43 server id 1  end_log_pos 602   Query   thread_id=1     exec_tim 
38. e=0     error_code=0 
39. SET TIMESTAMP=1271949883/*!*/; 
40. CREATE TABLE `tbl_b` ( 
41.   `a` int(11) DEFAULT NULL 
42. ) 
43. /*!*/; 
44. # at 602 
45. # at 646 
46. #100422 23:24:43 server id 1  end_log_pos 646   Table_map: `test`.`tbl_b` mapped 
47. to number 136 
48. #100422 23:24:43 server id 1  end_log_pos 690   Write_rows: table id 136 flags: 
49. STMT_END_F 
50.  
51. BINLOG ' 
52. O2rQSxMBAAAALAAAAIYCAAAAAIgAAAAAAAAABHRlc3QABXRibF9iAAEDAAE= 
53. O2rQSxcBAAAALAAAALICAAAQAIgAAAAAAAEAAf/+AQAAAP4CAAAA/gMAAAA= 
54. '/*!*/; 
55. # at 690 //Vevb.com
56. #100422 23:24:43 server id 1  end_log_pos 759   Query   thread_id=1     exec_tim 
57. e=0     error_code=0 
58. SET TIMESTAMP=1271949883/*!*/; 
59. COMMIT 
60. /*!*/; 
61.  
62. ###接着把session的binlog_format设置成statement，通过create table select from来创建表。对create table b select * from a; 只能记录语句而不是数据 
63. # at 759 
64. #100422 23:27:57 server id 1  end_log_pos 860   Query   thread_id=1     exec_tim 
65. e=1     error_code=0 
66. SET TIMESTAMP=1271950077/*!*/; 
67. create table tbl_c select * from tbl_a 
68. /*!*/; 
69.  
70. ###最后把session的binlog_format设置成mixed，通过create table select from来创建表。对create table b select * from a; 只能记录语句而不是数据 
71. # at 860 
72. #100422 23:30:04 server id 1  end_log_pos 961   Query   thread_id=1     exec_tim 
73. e=0     error_code=0 
74. SET TIMESTAMP=1271950204/*!*/; 
75. create table tbl_d select * from tbl_a 
76. /*!*/; 
77. DELIMITER ; 
78. # End of log file 
79. ROLLBACK /* added by mysqlbinlog */; 
80. /*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;