場景:每周日執行一次完全備份,每天下午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*/;