mysql之備份恢複

標籤：伺服器   mysql   資料庫   資料恢複   資料備份   

一、為什麼要備份？

    災難恢複

    需求改變

    測試

二、事先考慮的問題

    可以容忍丟失多長時間的資料？

    恢複要在多長時間內完成？

    是否需要持續提供服務？

    需要恢複什麼，整個資料庫伺服器？單個資料庫？一個或多個表？某個語句？

三、備份類型

    根據是否需要資料庫離線分為：

      冷備：cold backup,關閉mysql服務，或不允許讀寫請求

      溫備：warm backup，備份的同時僅支援讀請求

      熱備：hot backup，備份的同時，業務功能不受影響，需要工具和資料庫引擎支援

    根據要備份的資料範圍可分為：

      完全備份：full backup，備份全部資料集，伺服器、資料庫等根據關注點覺定

      增量備份：incremental backup，上次完全備份或增量備份以來改變了的資料；

      差異備份：differential backup，上次完全備份以來改變了的資料

    根據備份資料或是檔案，可以分為：

      物理備份：直接備份資料檔案

      邏輯備份：備份表中資料和庫代碼等

四、備份對象：

    資料：

    設定檔：經常備份/etc目錄

    代碼：預存程序、儲存函數、觸發器等；

    OS相關的設定檔：

    二進位日誌：

    註：備份完成後在測試機上測試備份內容

    mysql> CHECK TABLES

五、備份工具：

    mysqldump：

      邏輯備份工具

      InnoDB支援熱備、MyISAM僅溫備

      備份和恢複的速度較慢

    mydumper：

      多線程備份工具

    lvm-snapshot：

      接近於熱備的工具

      物理備份

      備份和恢複較快

    SELECT INTO OUTFILE  通常備份單表

      LOAD DATA INFILE   使用LOAD載入恢複資料

      邏輯備份工具

    ibbackup：

    Xtrabackup：開源版ibbackup

      物理備份工具

      支援InnoDB熱備、MyISAM溫備

      速度快

    mysqlhotcopy：冷備工具

六、恢複操作：

    1.停止MySQL服務

    2.記錄服務的配置和檔案許可權

    3.複本備份檔案至資料目錄

    4.按需調整配置

    5.按需調整檔案許可權

    6.嘗試啟動服務

    7.裝載邏輯備份

    8.檢查和重放二進位日誌

    9.確定資料還原正常完成

    10.以完全許可權重啟伺服器

例一：使用SELECT INTO OUTFILE備份恢複單表

  mysql> SELECT * FROM test1 INTO OUTFILE ‘/tmp/sql1.backup‘

  mysql> DELETE FROM TABLE 

  myslq> LOAD DATA INFILE ‘/tmp/sql1.backup‘ INTO TABLE test1

例二：mysqldump的MySQL用戶端工具使用

  mysqldump可以備份整個伺服器，單個或部分資料庫，單個或部分表，表中某些行，預存程序，儲存函   數，觸發器

  能自動記錄備份時的二進位記錄檔及相應position

    -uusername  -hhostname  -ppassword

    -B, --databases dbname 備份指定資料庫

    -x，--lock-all-tables 鎖定所有表

    -l，--lock-tables 鎖定單張表

    --master-data=[0|1|2]  0不啟用，1啟用，2記錄正在訪問的二進位日誌並記錄訪問點

    --single-transaction  基於此選項能實現InnoDB熱備，不需要同時使用--lock-all-tables;

    -A,--all-databases;備份整個伺服器上的所有庫

  mysql> FLUSH TABLES WITH READ LOCK;請求關閉所有表

  mysql> SHOW ENGINE INNODB STATUS; 查看INNODB狀態，確保不再有寫入

  [[email protected] ~]# mysqldump -uroot -pmysql --databases TESTDB --lock-all-tables >         /tmp/backupdb.sql

  mysql> DROP DATABESE TESTDB;

  mysql> source /tmp/backupdb.sql

  mysql> UNLOCK TABLES; 釋放鎖

  註：恢複時注意當前所在庫，如果原庫不存在請建立並use.

  myslqdump熱備-->   完全備份 + 增量備份 + 二進位 

  # mysqldump -uroot -pmypass --single-transaction --master-data=2 --all-databases > 

    /backup/`date +%F`.sql     完全備份

  # mysqlbinlog --start-position=4579 --stop-position=4868 mysql-bin.000021 > 

    /backup/`date +%F_%H`.sql   增量備份

  # mysqlbinlog --start-position=4868 --stop-position=5062 mysql-bin.000021 > 

    /tmp/backup.sql   最近一次增量備份之後的資料從二進位檔案匯出

 備份恢複需要mysql伺服器離線，

    mysql> SET sql_log_bin=0; 停止二進位日誌

    mysql> flush logs；  滾動日誌

    mysql> source /backup/filename.sql   完全備份檔案恢複

    mysql> source /backup/filename.sql   增量備份檔案恢複

    mysql> source /backup/filename.sql   二進位檔案恢複

  註：也可以使用mysql -uroot -pmypass < /backup/filename.sql 輸入重新導向恢複

例三：使用二進位日誌操作

  mysql> SHOW {BINARY| MASTER} LOGS; 查看二進位和主伺服器日誌

  mysql> SHOW MASTER STATUS;查看當前正在使用的二進位日誌及當前所處位置

  mysql> SHOW BINLOG EVENTS IN ‘mysql-bin.000021‘ FROM 3321（指定查看位置）; 查看日誌內容

  mysql> FLUSH LOGS; 滾動日誌，可以理解為建立並使用新的二進位日誌

  mysql> PURGE BINARY LOGS TO ‘mysql-bin.000010‘;清除10以前的日誌(不建議清除或備份後再清     除）

  誤操作恢複：

  mysql> drop database test;

  [[email protected] data]# mysqlbinlog mysql-bin.000021 查看二進位日誌的操作位置

  [[email protected] data]# mysqlbinlog --stop-position=3447 mysql-bin.000021 > /tmp/a.sql

 mysql> source /tmp/a.sql;

  mysql> SHOW DATABASES;

  mysql> SHOW TABLE test;

  註：在恢複的過程中如果繼續有二進位日誌寫入，則需FLUSH LOGS之後再恢複。

本文出自 “滴不盡相思血淚拋紅豆” 部落格，請務必保留此出處http://beijgh.blog.51cto.com/8272564/1545243

mysql之備份恢複