小虎博客

A-A+

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

2017年11月09日 编程技术 暂无评论

场景:每周日执行一次完全备份,每天下午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 //phpfensi.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*/;  
标签：

给我留言