MySQL的備份與復原詳解

MySQL資料備份

在mySQL裡面，有邏輯備份和實體備份。邏輯備份最大優點是各種儲存引擎，都可以使用相同的方法來備份。而實體備份則不同，不同的儲存引擎有不同的備份方法。

邏輯備份與恢復

備份

在MySQL中邏輯備份是使用mysqldump將資料庫中的資料備份為一個文字文件，備份的文件可以被檢視和編輯。依照備份範圍，可以將備份分為以下三種備份。

• 備份指定的一個資料庫或該資料庫中的某些表
  mysqldump [options] 資料庫名稱[表名] > data.sql

• #備份指定的多個資料庫
  mysqldump  [options] –database > data.sql 資料庫1名資料庫2名資料庫3名…

• 備份所有的資料庫
  mysqldump [options] –all-database > data.sql
  解析：[options] 備份時候，需要的權限資訊等。 mysqldump的選項很多，具體可以透過mysqldump –help來查看。 為了確保資料備份一致性，MySQL儲存引擎在北方時候，需要加上-l 參數，表示將所有表加上讀鎖，在備份期間，所有的表將只能讀，而不能寫。但是對於InnoDB引擎來說，可以採用–single-transaction。 data.sql備份的資料檔
  參數：
  l:代表給所有表格加鎖
  f:代表產生一個新的日子檔案

實例將xxpt資料庫中所有表備份到dequan.sql表中。指令如下：
mysqldump -uroot -p xxpt >dequan.sql

因為上面我沒有指定備份路徑，預設情況下，會備份到目前路徑，所以備份到D:\ wamp\bin\mysql\mysql5.6.17\bin路徑下了。

恢復

完全恢復

mysqldump的恢復也很簡單，將備份作為輸入執行即可。結果如下：
mysql -uroot -p dbname

不完全恢復

#不完全恢復包含基於時間點的復原和基於位置的復原。時間點與位置是相對應二進位日誌（binlog日誌）裡面的時間點和位置。

基於時間點

將設下午4:00到下午5:00之前的資料室錯誤的，恢復時候，需要跳過。首先我們來看binlog日誌。如下午4點多更新資料是錯誤的，恢復時候需要跳過，下午5點多刪除資料是正確的，需要保留。

1.透過mysql -uroot -p dbname

基於位置的恢復

使用位置恢復時候，我們需要先查看binlog日誌文件，確定位置編號，然後使用以下指令進行還原：
mysqlbinlog D:\wamp\bin\mysql\mysql5.6.17\data\mybinlog.000012 –stop-position=716406|mysql -uroot -p
恢復5點之後的操作
D:\wamp\bin\mysql\mysql5.6.17\data\mybinlog.000012 –start-position=723613|mysql -uroot -p

實體備份與復原

實體備份分為冷備份和熱備份。和邏輯備份相比，它的最大優點是備份和復原的速度快。因為物理備份的原理都是基於檔案的cp。

冷備份與恢復

冷備份其實就是將停掉資料庫服務，複製資料檔的方法。這種方法對MyISAM和InnoDB都適合。
恢復：先停掉Mysql服務，在作業系統層級恢復MySQL的資料文件，然後重新啟動Mysql服務，使用Mysqlbinlog工具恢復備份以來的所有binlog。

熱備份

在mysql中不同儲存引擎的熱備份時不相同的。

MyISAM儲存引擎

MyISAM儲存引擎備份原理就是將要備份的表格加讀鎖，然後在cp資料檔案到備份目錄。常用方法

• 方法一:使用mysqlhotcop
  mysqlhotcop db_name [目錄]

• ##方法二:手動鎖定表copy

  首先為資料庫中所有表格加讀鎖，然後cp資料。
  鎖定所有表格flush tables with read lock;
  
  

#InnoDB儲存引擎

正在學習中···

表的導入與導出

導出

• 使用SELECT …INTO OUTFILE …+[options]指令實作
  關於options參數如下
  
  預設路徑為該資料對應的路徑下：
  
  

• #使用mysqldump
  mysqldump -u username -T targetDir dbname tableName[options]
  例如：
  mysqldump -uroot -p -T  D:/wamp/bin/mysql/mysql5.6.17/  xxpt t1  
  產生了兩個文件，如下圖：
  
  t1.txt中儲存中資料訊息，t1.sql檔案內容如下

-- MySQL dump 10.13  Distrib 5.6.17, for Win32 (x86)
---- Host: localhost    Database: xxpt
-- ------------------------------------------------------
-- Server version   5.6.17-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
---- Table structure for table `t1`--DROP TABLE IF EXISTS `t1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `t1` (  `id1` int(11) NOT NULL DEFAULT '0',  
`id2` int(3) unsigned zerofill NOT NULL DEFAULT '000') ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2016-04-18 19:30:51

匯入

方法一：
load data infile
eg:
load data infile 'D:/wamp/bin/mysql/mysql5.6.17/t1.txt' into table t1;

方法二：使用mysqlinport

 以上就是MySQL的備份與恢復詳解的內容，更多相關內容請關注PHP中文網（www.php.cn）！