MySQL資料備份與恢複

一.備份資料的方式

MySQL參數說明：

MySQL管理之資料備份及恢複

1.物理備份 

    1.1冷備 cp tar等 

    1.2熱備 mysqlhotcopy    只能備份儲存引擎是myisam的表，依賴perl-DBD-MySQL

2.邏輯備份：用mysql自己提供的備份命令或第三放方軟體提供的備份命令，備份建立庫、表、和產生表記錄時的sql語句。

二.資料備份策略：

1.完整備份        備份資料庫伺服器上的所有庫所有表。

2.差異備份        備份自完整備份後所有產生的新資料。

3.增量備份        備份自上一次備份後所有產生的新資料

生產環境一般採用  完整備份+增量備份 或者 完整備份+差異備份

三.邏輯備份與恢複

1.mysqldump命令做資料的完整備份

命令格式：

mysqldump -h資料庫伺服器ip -u使用者名稱 -p密碼  資料庫名 > 目錄名/備份檔案名

資料庫名的表示方式：

--all-databases                    備份資料庫伺服器上的所有庫所有表

資料庫名                            對某個庫做完整備份

資料庫名  表                        對指定庫裡的指定表做完整備份

-B 資料庫名1 資料庫名2 資料庫N      對某幾個庫做完整備份

#目錄名 ：若備份時不寫目錄名備份檔案就存放在，執行備份命令時 所在的目錄下

#備份檔案名 ：可以自己定義，要有標識性，約定俗成mysql的備份檔案都以.sql 結尾

                      若檔案重名，上一次備份會被當前備份覆蓋。

# 備份用來串連資料庫伺服器的使用者，要有被備份的目標庫有相應的許可權才可以。

2.恢複完整備份資料

命令格式：

mysql  -h資料庫伺服器ip  -u使用者名稱  -p密碼  資料庫名  <  目錄名/xxx.sql

mysql  -h資料庫伺服器ip  -u使用者名稱  -p密碼    <  目錄名/xxx.sql

# 資料庫名：可選項，當備份檔案中有 create database,use  sql語句時,恢複資料時可以不指定資料庫名。

# 用完整備份檔案 恢複資料，只能把資料恢複到備份時的狀態，完整備份之後產生的資訊資料是無法恢複。

四.使用binlog日誌做增量備份與資料恢複

1.mysql日誌類型

mysql共有4種類型日誌：

binlog日誌（二進位日誌）  記錄除查詢之外sql語句。

錯誤記錄檔                  記錄mysql服務在啟動和運行過程中產生的錯誤。

查詢日誌                  記錄執行的所有的sql操作。

慢查詢日誌                只記錄超過指定時間，顯示查詢結果的sql語句。預設逾時時間是10秒。

#預設情況下，mysql只開啟錯誤記錄檔。

#預設情況下日誌都存放在資料庫目錄下。每種記錄檔都預設命名方式。

2.啟用binlog日誌

vim /etc/my.cnf

[mysqld]

log_bin=/binlogdir/filename        //設定存放binlog目錄和檔案名稱,自訂目錄必須讓mysql使用者有寫入許可權，目錄不設定則預設在資料庫目錄/var/lib/mysql/下，檔案名稱不設定則預設為：主機名稱-bin.000001                                   

max-binlog-size=100M                //設定binlog記錄檔的大小,不設定則預設為500M自動產生第二個binlog記錄檔

service  mysql  restart

# 對所有庫操作的使用資料發送改變的sql語句都會寫進當前編號最大的binlog記錄檔裡。

# localhost-bin.index  記錄當前已有binlog記錄檔

3.分析binlog記錄檔內容。

binlog日誌記錄sql語句的方式：

① 時間方式

② 字元位移量方式

 

命令格式：

mysqlbinlog  [選項]  binlog記錄檔名

選項：

位移量

--start-position=100        起始位置

--stop-position=1200        結束位置

時間點：

--start-datetime="YYYY-mm-dd  HH:MM:SS"  起始時間

--stop-datetime="YYYY-mm-dd  HH:MM:SS"    結束時間

# 不加任何選項則預設查看全部日誌內容

4.手動產生新的binlog記錄檔方式：

① mysql>flush logs;

② #mysql -h資料庫伺服器IP -u使用者名稱 -p密碼 -e "flush logs"

③ #mysqldump -h資料庫伺服器IP -u使用者名稱 -p密碼 --flush-logs 資料庫名> /目錄名/xxx.sql

④ service  mysql  restart              //一般不用

5.增量備份資料恢複

原理：使用mysqlbinlog提取曆史sql操作管道給mysql命令重做

命令格式：

mysqlbinlog  [選項]  binlog記錄檔名  |  mysql  -h資料庫伺服器ip  -u使用者名稱  -p密碼  資料庫名

大量復原增量備份：

mysqlbinlog  `cat localhost-bin.index`| mysql -h資料庫伺服器ip -u使用者名稱 -p密碼  [資料庫名]

6.清理binlog日誌

刪除早於指定編號的binlog記錄檔：

mysql> PURGE  MASTER LOGS TO  'binlog記錄檔名';

刪除所有binlog記錄檔，重建新日誌：

mysql> RESET  MASTER;

 

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

1 2 下一頁