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. –CHANGEMASTERTOMASTER_LOG_FILE=’mysql-bin.000002′,MASTER_LOG_POS=106;
2. --master-data[=value]
3. Writethebinarylogfilenameandpositiontotheoutput.ThisoptionrequirestheRELOADprivilegeandthebinarylogmustbe
4. enabled.Iftheoptionvalueisequalto1,thepositionandfilenamearewrittentothedumpoutputintheformofaCHANGEMASTER
5. statement.Ifthedumpisfromamasterserverandyouuseittosetupaslaveserver,theCHANGEMASTERstatementcausesthe
6. slavetostartfromthecorrectpositioninthemastersbinarylogs.Iftheoptionvalueisequalto2,theCHANGEMASTER
7. statementiswrittenasanSQLcomment.(Thisisthedefaultactionifvalueisomitted.)

其他说明:如果 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. createtabletbl_a(aint)
3. /*!*/;
4. #at195
5. #10042223:23:20serverid1end_log_pos263Querythread_id=1exec_tim
6. e=0error_code=0
7. SETTIMESTAMP=1271949800/*!*/;
8. BEGIN
9. /*!*/;
10. #at263
11. #at307
12. #10042223:23:20serverid1end_log_pos307Table_map:`test`.`tbl_a`mapped
13. tonumber135
14. #10042223:23:20serverid1end_log_pos351Write_rows:tableid135flags:
15. STMT_END_F
17. BINLOG'
18. 6GnQSxMBAAAALAAAADMBAAAAAIcAAAAAAAAABHRlc3QABXRibF9hAAEDAAE=
19. 6GnQSxcBAAAALAAAAF8BAAAQAIcAAAAAAAEAAf/+AQAAAP4CAAAA/gMAAAA=
20. '/*!*/;
21. #at351
22. #10042223:23:20serverid1end_log_pos420Querythread_id=1exec_tim
23. e=0error_code=0
24. SETTIMESTAMP=1271949800/*!*/;
25. COMMIT
26. /*!*/;
27. #at420
28. #10042223:24:43serverid1end_log_pos488Querythread_id=1exec_tim
29. e=0error_code=0
30. SETTIMESTAMP=1271949883/*!*/;
31. BEGIN
32. /*!*/;
33. #at488
36. ###binlog_format没变，通过createtableselectfrom来创建表。在row_basedmode下。创建的语句，变成先创建表再插入值
37. #10042223:24:43serverid1end_log_pos602Querythread_id=1exec_tim
38. e=0error_code=0
39. SETTIMESTAMP=1271949883/*!*/;
40. CREATETABLE`tbl_b`(
41. `a`int(11)DEFAULTNULL
42. )
43. /*!*/;
44. #at602
45. #at646
46. #10042223:24:43serverid1end_log_pos646Table_map:`test`.`tbl_b`mapped
47. tonumber136
48. #10042223:24:43serverid1end_log_pos690Write_rows:tableid136flags:
49. STMT_END_F
51. BINLOG'
52. O2rQSxMBAAAALAAAAIYCAAAAAIgAAAAAAAAABHRlc3QABXRibF9iAAEDAAE=
53. O2rQSxcBAAAALAAAALICAAAQAIgAAAAAAAEAAf/+AQAAAP4CAAAA/gMAAAA=
54. '/*!*/;
55. #at690 //phpfensi.com
56. #10042223:24:43serverid1end_log_pos759Querythread_id=1exec_tim
57. e=0error_code=0
58. SETTIMESTAMP=1271949883/*!*/;
59. COMMIT
60. /*!*/;
62. ###接着把session的binlog_format设置成statement，通过createtableselectfrom来创建表。对createtablebselect*froma;只能记录语句而不是数据
63. #at759
64. #10042223:27:57serverid1end_log_pos860Querythread_id=1exec_tim
65. e=1error_code=0
66. SETTIMESTAMP=1271950077/*!*/;
67. createtabletbl_cselect*fromtbl_a
68. /*!*/;
70. ###最后把session的binlog_format设置成mixed，通过createtableselectfrom来创建表。对createtablebselect*froma;只能记录语句而不是数据
71. #at860
72. #10042223:30:04serverid1end_log_pos961Querythread_id=1exec_tim
73. e=0error_code=0
74. SETTIMESTAMP=1271950204/*!*/;
75. createtabletbl_dselect*fromtbl_a
76. /*!*/;
77. DELIMITER;
78. #Endoflogfile
79. ROLLBACK/*addedbymysqlbinlog*/;
80. /*!50003SETCOMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;