mysldump資料的完全備份+增量備份+故障後的恢複

學習了幾天mysql的知識。剛接觸單單以為是文法之類的指令記住就行了。但是遇到問題的時候缺不知道從何下手。網上找了相關的資料感覺還是不太實用。于是筆者就着手總結了備份與還原的例子。這篇是mysqldump的使用。進行了2個事例的操作。當然兩個事例大同小異。第二個為第一個的拓展。如果将這2個例子原理搞清楚了。接下來進行LVM備份還原以及Xtrabackup這個備份工具都會輕松很多。

MySQL的備份方式

前提：事先保證對備份類型，備份政策等mysql常用知識已經清楚。

mysqldump: 邏輯備份工具

事例一、我們自己建立了個小教務管理系統，隻在MySQL中存在一張jiaowu表，我們來對這一個資料庫進行備份+增量備份+還原

       mysqldump（完全備份）+二進制日志

準備：在xshell中打開2個linux連接配接視窗（主要用于鎖表操作）,我們将他們賦予編号以便于進行說明操作過程，如果沒有xshell工具，那麼可以使用linux中的screen。具體使用可以搜一下screen，我們這裡主要講備份。

<a target="_blank" href="http://blog.51cto.com/attachment/201305/154749421.png"></a>

首先在1中要對mysql進行鎖表操作

[root@localhost ~]# mysql  

mysql&gt; FLUSH TABLES WITH READ LOCK; ##以隻讀的方式進行鎖表  

mysql&gt; FLUSH LOGS;       ##滾動日志  

mysql&gt; SHOW BINARY LOGS; ##檢視  

<a target="_blank" href="http://blog.51cto.com/attachment/201305/154840647.png"></a>

回到2中進行備份

[root@localhost ~]# mysqldump -uroot -p --master-data=2 jiaowu &gt; /root/jiaowu-`date +%F-%H-%M-%S`.sql  

<a target="_blank" href="http://blog.51cto.com/attachment/201305/154934844.png"></a>

PS：備份出來的可以vim檢視下，是個腳本。

同時裡面存在了資料庫的版本資訊。資料庫的名字等等。

<a target="_blank" href="http://blog.51cto.com/attachment/201305/154951300.png"></a>

PS：到這裡。一張完整的資料庫已經備份完成。那麼我們來進行增量備份.

我們來到2（1和2均可）中對jiaowu進行操作

<a target="_blank" href="http://blog.51cto.com/attachment/201305/155017672.png"></a>

然後假如我們一天就對他做了這麼點東西，那麼就重新FLUSH下LOGS。

<a target="_blank" href="http://blog.51cto.com/attachment/201305/155103125.png"></a>

這個時候，這已經算一次增量了，我們可以去磁盤中找到這個二進制檔案并儲存下來

<a target="_blank" href="http://blog.51cto.com/attachment/201305/155136941.png"></a>

這個時候我們已經進行了一次增量備份了。

然後比如mysql的資料庫突然損壞了，我們的jiaowu不見了。（如果當天正操作的時候壞掉了，我們同樣可以找到當天的mysql-bin.****用mysqlbinlog轉換出去用來還原）

mysql&gt; DROP DATABASE jiaowu;           ##模拟的  

然後我們來進行完整資料的恢複。

首先到mysql中建立jiaowu資料庫，（因為我們備份的隻是jiaowu中各表）

mysql&gt; CREATE DATABASE jiaowu;  

mysql&gt; \q  

[root@localhost ~]# mysql -uroot -p jiaowu &lt; jiaowu-2013-05-04-19-18-18.sql 

然後這樣我們就完成完整的備份還原。但是。這個時候的資料隻是我們沒做任何修改時候的資料。我們還需要進行增量資料的恢複。

<a target="_blank" href="http://blog.51cto.com/attachment/201305/155425660.png"></a>

[root@localhost ~]# mysql -uroot -p &lt; increment-1.sql 

這個時候我們來進行資料的檢視

<a target="_blank" href="http://blog.51cto.com/attachment/201305/155511539.png"></a>

顯然我們已經恢複到了第一天操作結束的時候了，這樣一個完整的過程就示範下來了

下面為mysql備份的一些快捷的擴充 

備份單個資料庫，或庫中特定表  

       mysqldump DB_NAME [tb1] [tb2]  

--master-data={0|1|2}  

       0: 不記錄二進制檔案及路位置；  

       1：以CHANGE MASTER TO的方式記錄位置，可用于恢複後直接啟動從伺服器；  

       2：以CHANGE MASTER TO的方式記錄位置，但預設為被注釋；  

--lock-all-tables: 鎖定所有表，這樣就不用單獨開一個xshel去執行mysql的鎖表操作了;

--flush-logs：執行日志flush;  

如果指定庫中的表類型均為InnoDB, 可使用  

--single-transaction 啟動熱備 不要和lock-all-tables共用  

備份多個庫：  

       --all-databases: 備份所有庫  

       --databases DB_NAME, DB_NAME,...: 備份指定庫  

       --event 備份事件  

       --routines備份存儲過程和存儲函數  

       --triggers備份觸發器的  

事例二、對整個資料庫完全備份+增量備份

              備份政策：周完全+每日增量

                     完全備份：mysqldump

                     增量備份：備份二進制日志檔案(flush logs)

首先

#mysqldump -uroot -p --master-data=2 --flush-logs --all-databases --lock-all-tables &gt; /root/alldatabases.sql   

                            檢視備份到的内容中 --master的masterlog的位子。清空（删掉）以前的（當然備份走也行，萬一到什麼時候用得到呢）  

                            mysql&gt; PURGE BINARY LOGS TO 'mysql_bin.********';  

                            mysql&gt; SHOW BINARYLOG;  

然後這樣就ok 備份完全的Ok。

                            然後比如到周一了，開始做操作了~

                            進入資料庫各種操作。。（忽略）

到周二

 mysql&gt; FLUSH LOGS;   ##滾動下，前邊的是周一的  

然後cd /mydata/data下  

找到昨天的mysql_bin.*******然後複制走。或者# mysqlbinlog mysql-bin.*****　&gt; /root/1-incremental.sql  

  然後這是周二，那麼你就可以做操作了（我們模仿周二資料庫壞掉了）。

so這裡我們模仿壞掉了，事先mysqld stop停掉他。去資料庫删掉裡面的東西（當然要把當談的mysql-bin******+1給儲存出來。（我們平常的生産生活中二進制檔案和資料庫檔案肯定要分開放的親！安全保險！）  

 這個時候 mysql已經挂掉了。

我們這個時候要重新去你安裝的mysql檔案夾中執行下初始化腳本（編譯安裝過程中的，當然這裡不能解釋啦），scripts/mysql_install_db --user=mysql --datadir=/mydata/data/  

              然後啟動服務  

                 #mysql -uroot -p &lt; alldataases.sql(這個時候可以進入檢視下)  

              還原增量備份  ##這裡執行之前需要在mysql&gt;中執行set sql_log_bin = 0挂掉二進制，等到還原結束後在set sql_log_bin =1 開啟。

                  mysql &lt; 1-incremental.sql ##去檢視  ,

                            然後還原周二的操作  

                            仿照周一的操作二進制檔案各種~  

本文轉自 陳延宗 51CTO部落格，原文連結：http://blog.51cto.com/407711169/1193381，如需轉載請自行聯系原作者