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

相关广告
  • MySQL完全备份、增量备份与恢复实例 MySQL完全备份、增量备份与恢复实例 MySQL完全备份、增量备份与恢复实例
相关阅读

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

2019/10/10 17:36:24 | 谷歌SEO算法 | 手机网站制作