MySQL完全備份、增量備份與恢復實例

本文章來給各位同學介紹一下關於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文件中含有：
– CHANGE MASTER TO MASTER_LOG_FILE=’mysql-bin.000002′, MASTER_LOG_POS=106;
--master-data[=value]
          Write the binary log filename and position to the output. This option requires the RELOAD privilege and the binary log must be
          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
          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
          slave to start from the correct position in the masters binary logs. If the option value is equal to 2, the CHANGE MASTER
          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的數據就麻煩了

###剛開始binlog_format為row
create table tbl_a (a int)
/*!*/;
# at 195
#100422 23:23:20 server id 1  end_log_pos 263   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1271949800/*!*/;
BEGIN
/*!*/;
# at 263
# at 307
#100422 23:23:20 server id 1  end_log_pos 307   Table_map: `test`.`tbl_a` mapped
to number 135
#100422 23:23:20 server id 1  end_log_pos 351   Write_rows: table id 135 flags:
STMT_END_F

BINLOG '
6GnQSxMBAAAALAAAADMBAAAAAIcAAAAAAAAABHRlc3QABXRibF9hAAEDAAE=
6GnQSxcBAAAALAAAAF8BAAAQAIcAAAAAAAEAAf/+AQAAAP4CAAAA/gMAAAA=
'/*!*/;
# at 351
#100422 23:23:20 server id 1  end_log_pos 420   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1271949800/*!*/;
COMMIT
/*!*/;
# at 420
#100422 23:24:43 server id 1  end_log_pos 488   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1271949883/*!*/;
BEGIN
/*!*/;
# at 488


###binlog_format沒變，通過create table select from來創建表。在row_based mode下。創建的語句，變成先創建表再插入值
#100422 23:24:43 server id 1  end_log_pos 602   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1271949883/*!*/;
CREATE TABLE `tbl_b` (
  `a` int(11) DEFAULT NULL
)
/*!*/;
# at 602
# at 646
#100422 23:24:43 server id 1  end_log_pos 646   Table_map: `test`.`tbl_b` mapped
to number 136
#100422 23:24:43 server id 1  end_log_pos 690   Write_rows: table id 136 flags:
STMT_END_F

BINLOG '
O2rQSxMBAAAALAAAAIYCAAAAAIgAAAAAAAAABHRlc3QABXRibF9iAAEDAAE=
O2rQSxcBAAAALAAAALICAAAQAIgAAAAAAAEAAf/+AQAAAP4CAAAA/gMAAAA=
'/*!*/;
# at 690
#100422 23:24:43 server id 1  end_log_pos 759   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1271949883/*!*/;
COMMIT
/*!*/;

###接著把session的binlog_format設置成statement，通過create table select from來創建表。對create table b select * from a; 只能記錄語句而不是數據
# at 759
#100422 23:27:57 server id 1  end_log_pos 860   Query   thread_id=1     exec_tim
e=1     error_code=0
SET TIMESTAMP=1271950077/*!*/;
create table tbl_c select * from tbl_a
/*!*/;

###最後把session的binlog_format設置成mixed，通過create table select from來創建表。對create table b select * from a; 只能記錄語句而不是數據
# at 860
#100422 23:30:04 server id 1  end_log_pos 961   Query   thread_id=1     exec_tim
e=0     error_code=0
SET TIMESTAMP=1271950204/*!*/;
create table tbl_d select * from tbl_a
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;