MySQL備份與還原

標籤：mysql 備份與還原

1.備份類型

根據備份時伺服器是否能夠向外繼續提供服務分為：熱備份、溫備份、冷備份。

    熱備份：線上，讀、寫不受影響；

    溫備份：線上，但僅可以執行讀操作；

    冷備份：離線，讀、寫操作均不能進行；

根據備份時是直接複製資料檔案還是將資料匯出進行備份分為：物理備份、邏輯備份。

    物理備份：直接複製資料檔案；

    邏輯備份：將資料匯出至文字檔當中；

根據備份內容不同分為：完全備份、增量備份、差異備份。

    完全備份：備份全部資料；

    增量備份：僅備份上次完全備份或增量備份以後變化的資料；

    差異備份：僅備份上次完全備份以來變化的資料；

2.使用mysqldump工具備份還原資料

    mysqldump是一個邏輯備份工具，對MyISAM引擎可以實現溫備，對InnoDB引擎可以實現熱備。速度比較慢，不適合對大資料庫進行完全備份。資料還原時應該將二進位日誌功能臨時性關掉(sql_log_bin=OFF)，還原後在啟用。

mysqldump命令的用法及其常見選項：

mysqldump -uroot -p [db_name] [tb1] [tb2] > backfile    #備份單個資料庫，或庫中特定表    --master-data={0|1|2}        0: 不記錄二進位記錄檔及其位置；        1：以CHNAGE MASTER TO的方式記錄位置，可用於恢複後直接啟動從伺服器；        2：以CHANGE MASTER TO的方式記錄位置，但預設為被注釋；    --lock-all-tables：鎖定所有表    --flush-logs：執行日誌滾動    --all-databases：備份所有庫    --databases DB_NAME,DB_NAME,...：備份指定庫    --events：備份事件    --routines：備份預存程序和儲存函數    --triggers：備份觸發器    --no-data：只備份表結構    如果指定庫中的表類型均為InnoDB，可使用--single-transaction啟動熱備；

mysqldump完全備份加二進位日誌增量備份及還原執行個體：

備份：

mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables > /root/all.sql    #完全備份mysqlbinlog mysql-bin.000011 > /root/first-incremental.sql    #第一次增量備份

刪除資料檔案類比故障還原(假設故障時使用的二進位記錄檔為mysql-bin.000012且二進位記錄檔和資料檔案分開儲存)：

killall mysqld/usr/local/mysql/scripts/mysql_install_db --user=mysql --datadir=/mydata/data    #初始化資料庫service mysqld startmysql -uroot -p < /root/all.sql    #還原完全備份mysql -uroot -p < /root/first-incremental.sql    #還原增量備份mysqlbinlog mysql-bin.000012 > /tmp/tmp.sqlmysql -uroot -p < /tmp/tmp.sql    #即時間點復原

3.通過select命令備份資料並還原

    select通常用來備份單個表，還原時表結構需要預先建立好。

備份：

mysql> SELECT * INTO OUTFILE ‘/path/to/somefile.txt‘ FROM tb_name [WHERE clause];

還原：

mysql> LOAD DATA INFILE ‘/path/to/somefile.txt‘ INTO TABLE tb_name;

MySQL備份與還原