MySQL備份的三種方法

MySQL備份的三種方法

MySQL備份的主要作用是當出現硬體故障、軟體故障、自然災害、駭客攻擊、人為誤操作時能快速、高效、安全的解決問題，以免造成不必要的損失
MySQL備份時需要備份什嗎？
  1、需要用到的資料
  2、二進位日誌、InnoDB交易記錄
  3、儲存代碼；例如：預存程序、儲存函數、觸發器、時間調度器
  4、伺服器的設定檔

MySQL備份時需要注意的要點：
  1、能容忍最多丟失多少資料
  2、恢複資料需要在多長時間內完成
  3、需要恢複那些資料
  4、做好備份之後，必須要做還原測試，用於測試備份的可用性

MySQL備份時需要考慮的因素
  1、持鎖需要多長時間
  2、備份的過程時間長度
  3、在備份時，是否會增加系統業務的負載
  4、恢複過程的時間長度

MySQL備份的類型；
  以資料集角度來劃分：

    完全備份：備份整個資料集
    部份備份：只備份資料子集
  以時間軸角度來劃分：
    完全備份：備份整個資料集
    增量備份：備份最近一次完全備份或增量備份之後變化的資料
    差異備份：備份最近一次完全備份之後變化的資料
    注意：雖然增量備份比差異備份更節約磁碟空間，但是還原資料起來增量備份卻比差異備份更麻煩

  根據伺服器業務系統能否線上來劃分：
    熱備份：讀寫操作均可執行
    溫備份：讀操作可執行，但寫操作不可執行
    冷備份：讀寫操作均不可執行
    注意：InnoDB儲存引擎即支援熱備份，也支援溫備份；而MyISAM儲存引擎只支援溫備份，不支援熱備份
  物理備份：直接複製資料檔案進行備份
  邏輯備份：從資料庫中匯出資料從而進行備份；邏輯備份與儲存引擎無關，如果備份的是InnoDB儲存引擎，用在MyISAM儲存引擎上也是可以的

MySQL備份的方式：
  1、mysqldump+複製binlog來實現：使用mysqldump來實現資料的完全備份，複製binlog中指定時間範圍內的event來實現增量備份
  2、lvm2+複製binlog：利用lvm2快照以及使用cp等命令工具來實現物理備份、完全備份，複製binlog中指時間點事件範圍內的event來實現增量備份
  3、xtrabackup：由Percona提供的備份工具；對InnoDB支援熱備、完全備份和增量備份，MySIAM支援溫備、完全備份

一、基於mysqldump+複製binlog來實現備份
準備環境：

節點1	IP：172.18.42.100
節點2	IP：172.18.42.111

1、部署節點1
（1）安裝mysql服務，編輯其二進位檔案並進行完全備份
[root@localhost ~]# yum install mysql-server -y  ##安裝Mysql服務
[root@localhost ~]# vim /etc/my.cnf  ##編輯其設定檔
[mysqld]
innodb_file_per_table = ON
log_bin=mysql-bin  ##啟用二進位檔案
[root@localhost ~]# mysqldump -uroot --lock-tables --master-data=2 --databases Mydata > db1.sql  ##在未修改資料庫之前做一次完全備份
## --lock-tables：鎖定指定的資料庫，建議在備份單個資料庫是使用
## --lock-all-tables：鎖定所有的資料庫，建議在備份多個資料庫時使用
## --master-data=1 | 2
  #1：記錄為change master to語句，此語句不會被注釋
  #2：記錄為change master to語句，此語句會被注釋 
## --databases：指定需要備份的資料庫，當有多個資料庫時中間可用空格隔開
[root@localhost ~]# less db1.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=1106;  ##記錄當前備份時的二進位日誌，以及事件在二進位日誌中所在位置

（2）修改資料庫中的資料，並使用“mysqlbinlog”產生增量備份
mysql> select * from db1;  ##未修改資料之前查看一次資料
+----+------------+------+
| ID | Name    Age 
+----+------------+------+
|  1 | BaiGu jing  100 
|  2 | SunDa shen  200
|  3 | ZhuBa jie  150 
+----+------------+------+
mysql> insert into db1 (ID,Name,Age) values (4,'RuLai',999);
mysql> insert into db1 (ID,Name,Age) values (5,'YuDi',888);
mysql> select * from db1;  ##修改資料之後查看一次
+----+------------+------+
| ID | Name    Age 
+----+------------+------+
|  1 | BaiGu jing  100 
|  2 | SunDa shen  200 
|  3 | ZhuBa jie  150 
|  4 | RuLai    999 
|  5 | YuDi      888 
+----+------------+------+
[root@localhost ~]# mysqlbinlog -uroot --start-position=1106 /var/lib/mysql/mysql-bin.000003 > binary_log  ##產生增量備份
##--start-position：指明事件的pos
[root@localhost ~]# less binary_log  ##查看增量備份檔案
insert into db1 (ID,Name,Age) values (4,'RuLai',999)  ##記錄了修改表的相關操作
/*!*/;
# at 1223
#160612 21:44:35 server id 1  end_log_pos 1339  Query  thread_id=9    exec_time=0    error_code=0
SET TIMESTAMP=1465739075/*!*/;
insert into db1 (ID,Name,Age) values (5,'YuDi',888)
/*!*/;
# at 1339
#160612 21:45:41 server id 1  end_log_pos 1441  Query  thread_id=9    exec_time=0    error_code=0
SET TIMESTAMP=1465739141/*!*/;

2、部署節點2
（1）提供mysql服務，並將節點1的完全備份檔案“db1.sql”和增量備份檔案“binary_log”發送給節點2
[root@localhost ~]# yum install mysql-server -y
[root@localhost ~]# scp db1.sql binary_log root@172.18.42.111:/root

（2）節點2匯入“db1.sql"，並查看其資料
[root@localhost ~]# mysql < db1.sql 
mysql> select * from db1;
+----+------------+------+
| ID | Name    Age
+----+------------+------+
|  1 | BaiGu jing  100 
|  2 | SunDa shen  200 
|  3 | ZhuBa jie  150 
+----+------------+------+
##資料並不是我們修改之後的資料

（3）假設節點1掛掉了，需要讓節點2恢複到節點1當前修改資料庫時的資料，此時我們只需要節點2匯入“binary_log”增量備份檔案即可
[root@localhost ~]# mysql < binary_log 
mysql> select * from db1;    ##匯入增量備份之後，節點2的資料和節點1修改資料庫之後的資料一樣
+----+------------+------+
| ID | Name    Age 
+----+------------+------+
|  1 | BaiGu jing  100 
|  2 | SunDa shen  200 
|  3 | ZhuBa jie  150 
|  4 | RuLai    999 
|  5 | YuDi    888 
+----+------------+------+

更多詳情見請繼續閱讀下一頁的精彩內容：

1 2 3 下一頁