MySQL優化之——備份和恢復

MySQL優化之——備份和恢復

備份

邏輯備份方法

使用MYSQLDUMP命令備份

MYSQLDUMP是MYSQL提供的一個非常有用的數據庫備份工具。mysqldump命令執行時將數據庫備份成一個文本文件，該文件中實際上包含了多個CREATE 和INSERT語句，使用這些語句可以重新創建表和插入數據

MYSQLDUMP的語法和選項

 

mysqldump -u user -p pwd -h host dbname[tbname,[tbname...]]>filename.sql

選項/Option 作用/Action Performed
--add-drop-table
這個選項將會在每一個表的前面加上DROP TABLE IF EXISTS語句，這樣可以保證導回MySQL數據庫的時候不會出錯，因為每次導回的時候，都會首先檢查表是否存在，存在就刪除
--add-locks
這個選項會在INSERT語句中捆上一個LOCK TABLE和UNLOCK TABLE語句。這就防止在這些記錄被再次導入數據庫時其他用戶對表進行的操作
-c or - complete_insert
這個選項使得mysqldump命令給每一個產生INSERT語句加上列（field）的名字。當把數據導出導另外一個數據庫時這個選項很有用。
--delayed-insert 在INSERT命令中加入DELAY選項
-F or -flush-logs 使用這個選項，在執行導出之前將會刷新MySQL服務器的log.
-f or -force 使用這個選項，即使有錯誤發生，仍然繼續導出
--full 這個選項把附加信息也加到CREATE TABLE的語句中
-l or -lock-tables 使用這個選項，導出表的時候服務器將會給表加鎖。
-t or -no-create- info
這個選項使的mysqldump命令不創建CREATE TABLE語句，這個選項在您只需要數據而不需要DDL（數據庫定義語句）時很方便。
-d or -no-data 這個選項使的mysqldump命令不創建INSERT語句。
在您只需要DDL語句時，可以使用這個選項。
--opt 此選項將打開所有會提高文件導出速度和創造一個可以更快導入的文件的選項。
-q or -quick 這個選項使得MySQL不會把整個導出的內容讀入內存再執行導出，而是在讀到的時候就寫入導文件中。
-T path or -tab = path 這個選項將會創建兩個文件，一個文件包含DDL語句或者表創建語句，另一個文件包含數據。DDL文件被命名為table_name.sql,數據文件被命名為table_name.txt.路徑名是存放這兩個文件的目錄。目錄必須已經存在，並且命令的使用者有對文件的特權。
-w "WHERE Clause" or -where = "Where clause "
如前面所講的，您可以使用這一選項來過篩選將要放到 導出文件的數據。
假定您需要為一個表單中要用到的帳號建立一個文件，經理要看今年（2004年）所有的訂單（Orders），它們並不對DDL感興趣，並且需要文件有逗號分隔，因為這樣就很容易導入到Excel中。 為了完成這個任務，您可以使用下面的句子：
bin/mysqldump –p –where "Order_Date >='2000-01-01'"
–tab = /home/mark –no-create-info –fields-terminated-by=, Meet_A_Geek Orders
這將會得到您想要的結果。
schema：模式
The set of statements, expressed in data definition language, that completely describe the structure of a data base.
一組以數據定義語言來表達的語句集,該語句集完整地描述了數據庫的結構。
SELECT INTO OUTFILE ：

 

mysqldump提供了很多選項，包括調試和壓縮的，在這裡只是列舉最有用的。

運行幫助命令mysqldump --help可以獲得特定版本的完整選項列表

user表示用戶名稱；

host表示登錄用戶的主機名稱；

pwd為登錄密碼；

dbname為需要備份的數據庫名稱；

tbname為dbname數據庫中需要備份的數據表，可以指定多個需要備份的表；

右箭頭“>”告訴mysqldump將備份數據庫表定義和數據寫入備份文件；

filename為備份文件的名稱

---

1、使用mysqldump備份單個數據庫中的所有表

數據庫的記錄是這樣的

打開cmd，然後執行下面的命令

可以看到C盤下面已經生成了school_2014-7-10.sql文件

使用editplus來打開這個sql文件

 

-- MySQL dump 10.13  Distrib 5.5.20, for Win32 (x86)
--
-- Host: 127.0.0.1    Database: school
-- ------------------------------------------------------
-- Server version    5.5.20-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' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `book`
--

DROP TABLE IF EXISTS `book`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `BkNameIdx` (`bookname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `book`
--

LOCK TABLES `book` WRITE;
/*!40000 ALTER TABLE `book` DISABLE KEYS */;
INSERT INTO `book` VALUES (1,'鍓戝湥','灏忔槑','13','hao',2013);
/*!40000 ALTER TABLE `book` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `student`
--

DROP TABLE IF EXISTS `student`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `student` (
  `stuno` int(11) DEFAULT NULL,
  `stuname` varchar(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `student`
--

LOCK TABLES `student` WRITE;
/*!40000 ALTER TABLE `student` DISABLE KEYS */;
INSERT INTO `student` VALUES (2,'xiaofang'),(3,'zhanghai'),(6,'haojie');
/*!40000 ALTER TABLE `student` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `stuinfo`
--

DROP TABLE IF EXISTS `stuinfo`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `stuinfo` (
  `stuno` int(11) DEFAULT NULL,
  `class` varchar(60) DEFAULT NULL,
  `city` varchar(60) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `stuinfo`
--

LOCK TABLES `stuinfo` WRITE;
/*!40000 ALTER TABLE `stuinfo` DISABLE KEYS */;
INSERT INTO `stuinfo` VALUES (1,'wuban','henan'),(2,'liuban','hebei'),(3,'qiban','shandong');
/*!40000 ALTER TABLE `stuinfo` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!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 2014-07-23 22:04:16

 

可以看到，備份文件包含了一些信息，文件開頭首先寫明了mysqldump工具的版本號；

然後是主機信息，以及備份的數據庫名稱，最後是mysql服務器的版本號5.5.20

備份文件接下來的部分是一些SET語句，這些語句將一些系統變量賦值給用戶定義變量，以確保被恢復的數據庫的系統變量和原來

備份時的變量相同

例如：

 

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

 

該set語句將當前系統變量character_set_client的值賦值給用戶變量@OLD_CHARACTER_SET_CLIENT

備份文件的最後幾行mysql使用set語句恢復服務器系統變量原來的值，例如：

 

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

 

該語句將用戶定義變量@OLD_CHARACTER_SET_CLIENT 中保存的值賦值給實際的系統變量OLD_CHARACTER_SET_CLIENT

備份文件中的“--”字符開頭的行為注釋語句；以“/*!”開頭、以“*/”結尾的語句為可執行的mysql注釋，這些語句可以被mysql執行

但在其他數據庫管理系統將被作為注釋忽略，這可以提高數據庫的可移植性

另外注意到，備份文件開始的一些語句以數字開頭，這些數字代表了mysql版本號，該數字告訴我們這些語句只有在指定的mysql版本

或者比該版本高的情況下才能執行。

例如：40101，表明這些語句只有在mysql版本為4.01.01或者更高版本的條件下才可以執行

---

2、使用mysqldump備份數據庫中的某個表

備份school數據庫裡面的book表

 

-- MySQL dump 10.13  Distrib 5.5.20, for Win32 (x86)
--
-- Host: 127.0.0.1    Database: school
-- ------------------------------------------------------
-- Server version    5.5.20-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' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `book`
--

DROP TABLE IF EXISTS `book`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `book` (
  `bookid` int(11) NOT NULL,
  `bookname` varchar(255) NOT NULL,
  `authors` varchar(255) NOT NULL,
  `info` varchar(255) DEFAULT NULL,
  `comment` varchar(255) DEFAULT NULL,
  `year_publication` year(4) NOT NULL,
  KEY `BkNameIdx` (`bookname`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `book`
--

LOCK TABLES `book` WRITE;
/*!40000 ALTER TABLE `book` DISABLE KEYS */;
INSERT INTO `book` VALUES (1,'劍聖','小明','13','hao',2013);
/*!40000 ALTER TABLE `book` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!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 2014-07-23 22:24:29

 

備份文件中的內容跟前面的介紹是一樣的，唯一不同的是只包含了book表的CREATE語句和INSERT語句

---

3、使用mysqldump備份多個數據庫

如果要使用mysqldump備份多個數據庫，需要使用--databases參數。

使用--databases參數之後，必須指定至少一個數據庫的名稱，多個數據庫名稱之間用空格隔開

使用mysqldump備份school庫和test庫

備份文件裡的內容，基本上跟第一個例子一樣，但是指明了裡面的內容那一部分屬於test庫，哪一部分屬於school庫

---

4、使用--all-databases參數備份系統中所有的數據庫

使用--all-databases不需要指定數據庫名稱

執行完畢之後會產生all_2014-7-10.sql的備份文件，裡面會包含了所有數據庫的備份信息

提示：如果在服務器上進行備份，並且表均為myisam，應考慮使用mysqlhotcopy

因為可以更快地進行備份和恢復

使用mysqlhotcopy，如果是Windows操作系統，需要先安裝perl腳本組件才能使用，因為mysqlhotcopy是使用perl來編寫的

提示

(1)如果你未使用--quick或者--opt選項，那麼mysqldump將在轉儲結果之前把全部內容載入到內存中。這在你轉儲大數據量的數據庫時將會有些問題。該選項默認是打開的，但可以使用--skip-opt來關閉它。 (2)使用--skip-comments可以去掉導出文件中的注釋語句 (3)使用--compact選項可以只輸出最重要的語句，而不輸出注釋及刪除表語句等等 (4)使用--database或-B選項，可以轉儲多個數據庫，在這個選項名後的參數都被認定為數據庫名

---

SQLSERVER邏輯備份

我發現SQLSERVER的備份概念並沒有ORACLE和MYSQL那麼多

我們通常都會使用下面的兩個SQL語句來備份SQLSERVER數據庫，例如備份test庫

 

BACKUP DATABASE test TO DISK='c:\test.bak' 
BACKUP LOG test  TO DISK='c:\test_log.bak' 

 

第一個SQL是完整備份test庫，如果加上WITH DIFFERENTIAL就是差異備份

第二個SQL是備份test庫的日志

實際上從我眼中的理解，SQLSERVER就是將數據文件和必要的日志信息放入一個壓縮包裡面，類似於MYSQL的物理備份，直接復制文件，只是MYSQL並沒有進行打包壓縮

SQLSERVER的邏輯備份

邏輯備份就是生成表定義腳本和數據插入腳本，SQLSERVER2008開始支持生成數據腳本，在SQLSERVER2008之前只支持生成表定義腳本

我所用的數據庫是SQLSERVER2012 SP1

選中需要生成腳本的數據庫

比如我要導出test表的數據和表定義

要選擇架構和數據，並且要選擇索引，這樣就會生成表的數據、定義、索引

生成的腳本如下

 

USE [sss]
GO
/****** Object:  Table [dbo].[test]    Script Date: 2014/7/24 11:27:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[test](
    [a] [int] NULL
) ON [PRIMARY]

GO
INSERT [dbo].[test] ([a]) VALUES (10)
GO

 

由於test表是沒有任何索引的，所以腳本裡看不到CREATE INDEX語句

實際上各種數據庫的備份恢復方法都是大同小異的

ORACLE冷備份與恢復

邏輯備份和物理備份

1、導出create table 、create index、insert into 表等語句（邏輯備份）

mysql：mysqldump、load data infile、select into outfile

sqlserver：生成腳本、導入導出向導

oracle：（exp/imp）

2、直接復制文件（物理備份）

sqlserver：backup database語句、backup log語句、停SQLSERVER服務直接拷貝數據文件

mysql：mysqlhotcopy、innobackupex

oracle：rman、直接將關鍵性文件拷貝到另外的位置、（exp/imp）、（expdp/impdp）

相似點：上面的各種數據庫的各種備份還原方法，每一種基本上都會有一個單獨的工具來做

例如sqlserver導入導出向導就是一個單獨的exe來做

oracle的rman也是一個單獨的工具

冷備份和熱備份：無論oracle、sqlserver、mysql都有冷備份和熱備份的概念

冷備份其實可以簡單理解為：停止服務進行備份

熱備份其實可以簡單理解為：不停止服務進行備份（在線）

上面的停止服務，正確的來講應該是停止數據庫的寫入

為什麼mysql的myisam引擎只支持冷備份呢？

大家可以先想一下innodb引擎，innodb引擎是事務性存儲引擎，每一條語句都會寫日志，並且每一條語句在日志裡面都有時間點

那麼在備份的時候，mysql可以根據這個日志來進行redo和undo，將備份的時候沒有提交的事務進行回滾，已經提交了的進行重做

但是myisam不行，myisam是沒有日志的，為了保證一致性，只能停機或者鎖表進行備份

在書《MYSQL性能調優和架構設計》裡面說到了事務的作用

大家可以想一想，為什麼sqlserver支持從某一個lsn或者時刻進行恢復數據庫，他也是從日志裡面讀取日志的lsn號來進行恢復到某一個lsn時刻的數據或者某一個時刻的數據

假如沒有事務日志，那麼sqlserver是做不到時點還原的

熱備份、冷備份

為什麼SQLSERVER需要停止SQLSERVER服務才可以拷貝物理數據文件，為的都是保證數據一致性

---

物理備份方法

1、直接復制整個數據庫目錄

因為MYSQL表保存為文件方式，所以可以直接復制MYSQL數據庫的存儲目錄以及文件進行備份。

MYSQL的數據庫目錄位置不一定相同，在Windows平台下，MYSQL5.6存放數據庫的目錄通常默認為

C:\Documents and Settings\All User\Application Data\MySQL\MYSQL Server 5.6\data

或者其他用戶自定義的目錄；

在Linux平台下，數據庫目錄位置通常為/var/lib/mysql/，不同Linux版本下目錄會有不同

這是一種簡單、快速、有效的備份方式。要想保持備份一致，備份前需要對相關表執行LOCK TABLES操作，然後對表執行

FLUSH TABLES。這樣當復制數據庫目錄中的文件時，允許其他客戶繼續查詢表。需要FLUSH TABLES語句來確保開始

備份前將所有激活的索引頁寫入磁盤。

當然，也可以停止MYSQL服務再進行備份操作

這種方法雖然簡單，但並不是最好的方法。因為這種方法對INNODB存儲引擎的表不適用。使用這種方法備份的數據最好還原

到相同版本的服務器中，不同的版本可能不兼容。

注意：在mysql版本中，第一個數字表示主版本號，主版本號相同的MYSQL數據庫文件格式相同

2、使用mysqlhotcopy工具快速備份

mysqlhotcopy是一個perl腳本，最初由Tim Bunce編寫並提供。他使用LOCK TABLES 、FLUSH TABLES和cp或scp

來快速備份數據庫。他是備份數據庫或單個表的最快途徑，但他只能運行在數據庫目錄所在機器上，並且只能備份myisam類型的表。

語法

 

mysqlhotcopy db_name_1,...db_name_n /path/to/new_directory

 

db_name_1...n代表要備份的數據庫的名稱；

path/to/new_directory指定備份文件目錄

示例

在Linux下面使用mysqlhotcopy備份test庫到/usr/backup

 

mysqlhotcopy -u root -p test /usr/backup

 

要想執行mysqlhotcopy，必須可以訪問備份的表文件，具有那些表的SELECT權限、RELOAD權限（以便能夠執行FLUSH TABLES）

和LOCK TABLES權限

提示：mysqlhotcopy只是將表所在目錄復制到另一個位置，只能用於備份myisam和archive表。備份innodb表會出現錯誤信息

由於他復制本地格式的文件，故也不能移植到其他硬件或操作系統下

---

還原

邏輯還原

1、使用mysql命令進行還原

對於已經備份的包含CREATE、INSERT語句的文本文件，可以使用myslq命令導入數據庫中

備份的sql文件中包含CREATE、INSERT語句（有時也會有DROP語句）。mysql命令可以直接執行文件中的這些語句

其語法如下：

 

mysql -u user -p [dbname]
 
user是執行backup.sql中語句的用戶名；-p表示輸入用戶密碼；dbname是數據庫名
如果filename.sql文件為mysqldump工具創建的包含創建數據庫語句的文件，執行的時候不需要指定數據庫名
用mysql命令將school_2014-7-10.sql文件中的備份導入到數據庫中
 
mysql -u root -h 127.0.0.1 -p school
 
執行語句之前我們必須建好school數據庫，如果不存在恢復過程將會出錯。
可以看到表數據都已經導入到數據庫了
 
如果已經登錄mysql，那麼可以使用source命令導入備份文件
使用source命令導入備份文件school_2014-7-10.sql
執行source命令前必須使用use 語句選擇好數據庫，不然會出現ERROR 1046(3D000):NO DATABASE SELECTED 的錯誤
還有一點要注意的是只能在cmd界面下執行，不能在mysql工具裡面執行source命令，否則會報錯
因為cmd是直接調用mysql.exe來執行命令的
而這些mysql 編輯工具只是利用mysql connector連接mysql，來管理mysql並不是直接調用mysql.exe，所以執行source會報錯
物理還原
2、直接復制到數據庫目錄
如果數據庫通過復制數據庫文件備份，可以直接復制備份文件到MYSQL數據目錄下實現還原。通過這種方式還原時，
必須保證備份數據的數據庫和待還原的數據庫服務器的主版本號相同。
而且這種方式只對MYISAM引擎有效，對於innodb引擎的表不可用
執行還原以前關閉mysql服務，將備份的文件或目錄覆蓋mysql的data目錄，啟動mysql服務。
對於Linux操作系統來說，復制完文件需要將文件的用戶和組更改為mysql運行的用戶和組，通常用戶是mysql，組也是mysql
3、mysqlhotcopy快速恢復
mysqlhotcopy備份後的文件也可以用來恢復數據庫，在mysql服務器停止運行時，將備份的數據庫文件復制到mysql存放數據的位置
（mysql的data文件夾），重新啟動mysql服務即可。
如果根用戶執行該操作，必須指定數據庫文件的所有者，輸入語句如下：
 
chown -R mysql.mysql /var/lib/mysql/dbname
 
從mysqlhotcopy復制的備份恢復數據庫
 
cp -R /usr/backup/test  usr/local/mysql/data
 
執行完該語句，重啟服務器，mysql將恢復到備份狀態
注意：如果需要恢復的數據庫已經存在，則在使用DROP語句刪除已經存在的數據庫之後，恢復才能成功。
另外mysql不同版本之間必須兼容，恢復之後的數據才可以使用！！

數據庫遷移
數據庫遷移就是把數據從一個系統移動到另一個系統上。
遷移的一般原因：
1、需要安裝新的數據庫服務器
2、mysql版本更新
3、數據庫管理系統變更（從SQLSERVER遷移到mysql）
相同版本的MYSQL數據庫之間遷移
相同版本mysql數據庫間的遷移就是主版本號相同的mysql數據庫直接進行數據庫移動。
前面講解備份和還原的時候，知道最簡單的方法就是復制數據庫文件目錄，但是這種方法只適合於myisam表
對於innodb表，不能直接復制文件來備份數據庫
最常用的方法是使用mysqldump導出數據，然後在目標數據庫服務器使用mysql命令導入
將www.abc.com主機上的mysql數據庫全部遷移到www.bcd.com主機上。
在www.abc.com主機上執行以下命令：
 
mysqldump -h www.abc.com -u root -p dbname |
mysql -h www.bcd.com -u root -p
 
mysqldump導入的數據直接通過管道符|，傳給mysql命令導入到主機www.bcd.com數據庫中，dbname為需要遷移的數據庫名稱
如果要遷移全部數據庫，可以使用--all -databases參數

不同版本的mysql數據庫之間的遷移
因為數據庫升級，需要將舊版本mysql數據庫中的數據遷移到新版本數據庫中。
mysql服務器升級，需要先停止服務，然後卸載舊版本，並安裝新版本的mysql，這種更新方法很簡單。
如果想保留舊版本中的用戶訪問控制信息，則需要備份mysql的mysql庫，
在新版本mysql安裝完成後，重新讀入mysql備份文件中的信息
舊版本和新版本的mysql可能使用不同的默認字符集，例如mysql.4.x中大多數使用latin1作為默認字符集，
而mysql5.x的默認字符集為utf8。如果數據庫中有中文數據，遷移過程中需要對默認字符集進行修改，不然可能無法正常顯示結果
新版本對舊版本有一定兼容性。從舊版本的mysql向新版本mysql遷移時，對於myisam引擎的表，可以直接復制數據庫文件，
也可以用mysqlhotcopy工具、mysqldump工具。
對於innodb引擎的表一般只能使用mysqldump將數據導出。然後使用mysql命令導入目標服務器。
從新版本向舊版本mysql遷移數據時要小心，最好使用mysqldump命令導出，然後導入目標數據庫中。

不同數據庫之間的遷移
不同類型的數據庫之間的遷移，是指把mysql數據庫遷移到其他的數據庫，例如從mysql遷移到oracle，從oracle遷移到mysql
從mysql遷移到SQLSERVER等。
遷移之前，需要了解不同數據庫的結構，比較他們的差異。不同數據庫定義相同類型的數據的關鍵字可能不同。
例如：mysql中日期字段分為DATE 和TIME兩種，而ORACLE的日期字段只有DATE。
數據庫遷移可以使用一些工具，例如，在Windows系統下，可以使用MyODBC實現mysql和SQLSERVER之間的遷移（使用SQLSERVER導入導出向導）
mysql官方提供的工具：MYSQL Migration Toolkit也可以在不同數據庫間進行數據遷移。

表的導入導出
MYSQL數據庫可以將數據導出成sql文本文件、xml文件、html文件。同樣這些導出文件也可以導入到MYSQL數據庫中
一般異構數據庫遷移都是采用文本文件的方式來導數據
導出
1、用SELECT...INTO OUTFILE導出文本文件
mysql導出數據時，允許使用包含表定義的select語句進行數據的導出操作
該文件被創建在服務器主機上，因此必須有文件寫入權限(FILE權限)，才能使用此語法
SELECT INTO…OUTFILE語法：
 
select columnlist  from Table WHERE condition  into outfile 'filename' [OPTIONS]
fields terminated by 'VALUE'
fields [OPTIONALLY]  ENCLOSED BY 'VALUE'
fields ESCAPED BY 'VALUE'
lines STARTING by 'VALUE'
lines terminated by 'VALUE'
 
into outfile語句的作用就是把前面select語句查詢出來的結果導出到名稱為“filename”的外部文件中
[OPTIONS]部分為可選參數，[OPTIONS]部分的語法包括FILED和LINES子句，其可能取值為：
● fields子句：在FIELDS子句中有三個子句：TERMINATED BY、 [OPTIONALLY] ENCLOSED BY和ESCAPED BY。
如果指定了FIELDS子句，則這三個子句中至少要指定一個。

（1）TERMINATED BY用來指定字段值之間的符號，例如，“TERMINATED BY ','”指定了逗號作為兩個字段值之間的標志，默認為“\t”制表符。

（2）ENCLOSED BY子句用來指定包裹文件中字符值的符號，例如，“ENCLOSED BY ' " '”表示文件中字符值放在雙引號之間，
若加上關鍵字OPTIONALLY表示所有的值都放在雙引號之間，則只有CHAR和VARCHAR等字符數據字段被包括。
（3）ESCAPED BY子句用來指定轉義字符，例如，“ESCAPED BY '*'”將“*”指定為轉義字符，取代“\”，如空格將表示為“*N”。

● LINES子句：在LINES子句中使用TERMINATED BY指定一行結束的標志，如“LINES TERMINATED BY '?'”表示一行以“?”作為結束標志，默認值為“\n”。
TERMINATED BY也是同樣的原理
FIELDS子句和LINES子句都是自選的，但是如果兩個都被指定了，FIELDS子句必須位於LINES子句的前面
SELECT INTO…OUTFILE只能在本機執行，如果要在其他服務器上導出數據，則需要使用下面命令來生成文件
 
mysql -e "select ...">filename
 
-e, --execute=name Execute command and quit. (Disables --force and history
SELECT INTO…OUTFILE是LOAD DATA INFILE的補語。用於語句的OPTIONS部分的語法包括部分FIELDS子句和LINES子句
這些子句與LOAD DATA INFILE語句同時使用
使用SELECT INTO…OUTFILE將test數據庫中的person表的記錄導出到文本文件
輸入命令如下
 
SELECT * FROM test.person  INTO  OUTFILE  "C:\person0.txt" ;
 
由於指定了INTO OUTFILE 子句，SELECT將查詢出來的3個字段的值保存到C:\person0.txt文件，打開文件內容如下
 
1    green    29    lawer
2    suse    26    dancer
3    evans    27    sports man
4    mary    26    singer
 
可以看到默認情況下，MYSQL使用制表符“\t”分隔不同的字段，字段沒有被其他字符括起來
另外在Windows平台下，使用記事本打開該文件，顯示的格式與這裡並不相同，這是因為Windows系統下回車換行為“\r\n”
默認換行符為“\n”,因此會在person.txt中可能看到類似黑色方塊的字符，所有的記錄也會在同一行顯示
默認情況下，NULL值會顯示為“\N”,轉義字符會顯示為“\”
使用SELECT ..INTO OUTFILE將test庫中的person表中的記錄導出到文本文件，使用FIELDS選項和LINES選項，要求字段之間
使用逗號“，”間隔，所有字段值用雙引號括起來，定義轉移字符為單引號“\'”
 
SELECT * FROM test.person  INTO  OUTFILE  "C:\person1.txt" 
FIELDS
TERMINATED BY ','
ENCLOSED BY '\''
ESCAPED BY '\''
LINES
TERMINATED BY '\r\n';
 
在C盤下生成的person1文件內容
 
'1','green','29','lawer'
'2','suse','26','dancer'
'3','evans','27','sports man'
'4','mary','26','singer'
 
FIELDS TERMINATED BY ','表示字段之間用逗號分隔
ENCLOSED BY '\''表示每個字段用雙引號括起來
ESCAPED BY '\''表示將系統默認的轉移字符替換為單引號
LINES TERMINATED BY '\r\n'表示每行以回車換行符結尾，保證每一條記錄占一行

2、用mysqldump命令導出文本文件
除了使用SELECT...INTO OUTFILE導出文本文件之外，也可以使用mysqldump
mysqldump不僅可以將數據導出包含CREATE、INSERT的sql文件，也可以導出為純文本文件
mysqldump創建一個包含創建表的CREATE TABLE語句的tablename.sql文件，和一個包含其數據
的tablename.txt文件。mysqldump導出文本文件的基本語法如下
 
mysqldump -T path -u root -p dbname [tables][OPTIONS]
--fields-terminated-by=
--fields-enclosed-by=
--fields-optionally-enclosed-by=
--fields-escaped-by=
--lines-terminated-by=
 
只有指定了-T參數才可以導出純文本文件；path表示導出數據的目錄
tables為指定要導出的表名稱，如果不指定，將導出dbname的所有表
基本上每個選項跟SELECT ..INTO OUTFILE語句中的OPTIONS各個參數設置相同
不同的是，等號後面的value值不要用引號括起來
使用mysqldump將test庫的person表的記錄導出到文本文件，執行的命令如下
 
mysqldump -T C:\ -u root -h 127.0.0.1  -p test person
 
這裡要注意的是，路徑這裡不能先創建好person.txt文件，否則會報錯，跟SELECT ..INTO OUTFILE語句是一樣的
在C盤會生成一個person.txt文件和person.sql文件，內容如下

person.sql
 
-- MySQL dump 10.13  Distrib 5.5.28, for Win32 (x86)
--
-- Host: 127.0.0.1    Database: test
-- ------------------------------------------------------
-- Server version    5.5.28-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 `person`
--

DROP TABLE IF EXISTS `person`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `person` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `Name` varchar(20) NOT NULL,
  `Age` int(10) unsigned DEFAULT NULL,
  `job` varchar(90) NOT NULL,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=5 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 2014-07-27 23:56:01
 
person.sql的內容跟之前解釋的是一樣的
person.txt
 
1    green    29    lawer
2    suse    26    dancer
3    evans    27    sports man
4    mary    26    singer 

 

3、使用mysql命令導出文本文件
mysql是一個功能豐富的工具命令，使用mysql還可以在命令行模式下執行SQL指令，將查詢結果導入到文本文件中。
相比mysqldump，mysql工具導出的結果可讀性更強
如果mysql服務器是單獨的機器，用戶是在一個client上進行操作，用戶要把數據結果導入到client機器上，可以使用mysql -e語句
基本格式如下：
 
mysql -u root -p --execute="SELECT 語句" dbname >filename.txt
 
該命令使用--execute 選項，表示執行該選項後面的語句並退出，後面的語句必須用雙引號括起來
dbname為要導出的數據庫名稱，導出的文件中不同列之間使用制表符分隔，第一行包含了字段名稱
使用mysql命令，導出test庫的person表記錄到文本文件，輸入語句如下：
 
mysql -u root -p --execute="SELECT * FROM person;" test>C:\person3.txt
 
person3.txt的內容如下
 
ID    Name    Age    job
1    green    29    lawer
2    suse    26    dancer
3    evans    27    sports man
4    mary    26    singer
 
可以看到，person3.txt文件中包含了每個字段的名稱和各條記錄，如果某行記錄字段很多，可能一行不能完全顯示，可以使用
--vertical參數，將每條記錄分為多行顯示
使用mysql命令導出test庫的person表使用--vertical參數顯示
 
mysql -u root -p  --vertical --execute="SELECT * FROM person;" test>C:\person4.txt

*************************** 1. row ***************************
  ID: 1
Name: green
 Age: 29
 job: lawer
*************************** 2. row ***************************
  ID: 2
Name: suse
 Age: 26
 job: dancer
*************************** 3. row ***************************
  ID: 3
Name: evans
 Age: 27
 job: sports man
*************************** 4. row ***************************
  ID: 4
Name: mary
 Age: 26
 job: singer
 
如果person表中記錄內容太長，這樣顯示將會更加容易閱讀
使用mysql命令導出test庫的person表記錄到html文件，輸入語句如下
 
mysql -u root -p --html --execute="SELECT * FROM PERSON;"test >C:\person5.html
 

如果要導出為xml文件，那麼使用--xml選項
使用mysql命令導出test庫的person表的中記錄到xml文件
 
mysql -u root -p --xml --execute="SELECT * FROM PERSON;" test >C:\person6.xml




  
    1
    green
    29
    lawer
  

  
    2
    suse
    26
    dancer
  

  
    3
    evans
    27
    sports man
  

  
    4
    mary
    26
    singer
  

 

導入
1、使用LOAD DATA INFILE 方式導入文本文件
mysql允許將數據導出到外部文件，也可以從外部文件導入數據。
MYSQL提供了一些導入數據的工具，這些工具有：LOAD DATA語句、source命令、mysql命令
LOAD DATA INFILE語句用於高速地從一個文本文件中讀取行，並裝入一個表中。文件名稱必須為文字字符串
語法如下：
 
LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name.txt' 
[REPLACE | IGNORE] 
INTO TABLE tbl_name 
[FIELDS 
[TERMINATED BY 'string'] 
[[OPTIONALLY] ENCLOSED BY 'char'] 
[ESCAPED BY 'char' ] 
] 
[LINES 
[STARTING BY 'string'] 
[TERMINATED BY 'string'] 
] 
[IGNORE number LINES] 
[(col_name_or_user_var,...)] 
[SET col_name = expr,...]] 
 
load data infile語句從一個文本文件中以很高的速度讀入一個表中。

使用這個命令之前，mysqld進程（服務） 必須已經在運行。

當讀取的文本文件不在本機，而是位於服務器上的文本文件時，使用load data infile語句，在服務器主機上你必須有file的權限。
1 、如果你指定關鍵詞low_priority，那麼MySQL將會等到沒有其他人讀取這個表的時候，

才插入數據。例如如下的命令：
 
load data low_priority infile "/home/mark/data.sql" into table Orders;
 
2 、如果指定local關鍵詞，則表明讀取的文件在本機，那麼必須指定local參數。
3 、replace和ignore參數控制對現有表的唯一鍵記錄重復的處理。
如果你指定replace，新行將代替有相同的唯一鍵值的現有行。
（1）如果你指定ignore，跳過有唯一鍵的現有行的重復行的輸入。
（2）如果你不指定任何一個選項，當找到重復鍵時，出現一個錯誤，並且文本文件的余下部分被忽略。
FIELDS TERMINATED BY ','表示字段之間用逗號分隔
ENCLOSED BY '\''表示每個字段用雙引號括起來
ESCAPED BY '\''表示將系統默認的轉移字符替換為單引號
LINES STARTING BY ''表示每行數據開頭的字符，可以為單個或多個，默認不是有任何字符
LINES TERMINATED BY '\r\n'表示每行以回車換行符結尾，保證每一條記錄占一行
[IGNORE number LINES] 選項表示忽略文件開始處的行數，number表示忽略的行數。
基本上格式上的參數跟SELECT...INTO OUTFILE是一樣的
使用LOAD DATA命令將C:\person0。txt文件中的數據導入到test庫中的test表
 
LOAD DATA INFILE 'C:\person0.txt' INTO TABLE test.person
 
先刪除person表裡的數據，然後執行LOAD DATA命令

使用mysqlimport命令導入文本文件
2、使用mysqlimport命令導入文本文件
mysqlimport是一個單獨的exe，他提供了許多與LOAD DATA INFILE語句相同的功能
大多數選項直接對應LOAD DATA INFILE子句

mysqlimport的語法如下
 
mysqlimport -u root -p dbname filename.txt  [OPTIONS]
--[OPTIONS] 選項
FIELDS  TERMINATED BY 'value'
ENCLOSED BY 'value'
ESCAPED BY 'value'
LINES TERMINATED BY 'value'
IGNORE LINES
 
[OPTIONS] 選項基本上與LOAD DATA INFILE 語句是一樣的，這裡不做介紹了
mysqlimport不能指定導入的表名稱，表名稱由導入文件名稱確定，即文件名作為表名，導入數據之前該表必須存在
使用mysqlimport命令將C:\目錄下person.txt文件內容導入到test庫
先刪除test庫的person表的數據
 
DELETE FROM `person`;
 
person.txt文件內容
 
1    green    29    lawer
2    suse    26    dancer
3    evans    27    sports man
4    mary    26    singer
 
命令如下
 
mysqlimport -u root -p  test C:\person.txt
 

導入成功

mysqlimport的常見選項：
 
顯示幫助消息並退出。

·         --columns=column_list, -c column_list

該選項采用用逗號分隔的列名作為其值。列名的順序指示如何匹配數據文件列和表列。

·         --compress，-C

壓縮在客戶端和服務器之間發送的所有信息（如果二者均支持壓縮）。

·         ---debug[=debug_options]，-# [debug_options]

寫調試日志。debug_options字符串通常是'd:t:o,file_name'。

·         --delete，-D

導入文本文件前清空表。

·         --fields-terminated-by=...，--fields-enclosed-by=...，--fields-optionally-enclosed-by=...，--fields-escaped-by=...，--lines-terminated-by=...

這些選項與LOAD DATA INFILE相應子句的含義相同。參見13.2.5節，“LOAD DATA INFILE語法”。

·         --force，-f

忽視錯誤。例如，如果某個文本文件的表不存在，繼續處理其它文件。不使用--force，如果表不存在則mysqlimport退出。

·         --host=host_name，-h host_name

將數據導入給定主機上的MySQL服務器。默認主機是localhost。

·         --ignore，-i

參見--replace選項的描述。

·         --ignore-lines=n

忽視數據文件的前n行。

·         --local，-L

從本地客戶端讀入輸入文件。

·         --lock-tables，-l

處理文本文件前鎖定所有表以便寫入。這樣可以確保所有表在服務器上保持同步。

·         --password[=password]，-p[password]

當連接服務器時使用的密碼。如果使用短選項形式(-p)，選項和 密碼之間不能有空格。如果在命令行中--password或-p選項後面沒有 密碼值，則提示輸入一個密碼。

·         --port=port_num，-P port_num

用於連接的TCP/IP端口號。

·         --protocol={TCP | SOCKET | PIPE | MEMORY}

使用的連接協議。

·         --replace，-r

--replace和--ignore選項控制復制唯一鍵值已有記錄的輸入記錄的處理。如果指定--replace，新行替換有相同的唯一鍵值的已有行。如果指定--ignore，復制已有的唯一鍵值的輸入行被跳過。如果不指定這兩個選項，當發現一個復制鍵值時會出現一個錯誤，並且忽視文本文件的剩余部分。

·         --silent，-s

沉默模式。只有出現錯誤時才輸出。

·         --socket=path，-S path

當連接localhost時使用的套接字文件(為默認主機)。

·         --user=user_name，-u user_name

當連接服務器時MySQL使用的用戶名。

·         --verbose，-v

冗長模式。打印出程序操作的詳細信息。

·         --version，-V

顯示版本信息並退出。 

 
提示：
LOAD DATA INFILE語句中有一個mysqlimport工具中沒有特點：
LOAD DATA INFILE 可以按指定的字段把文件導入到數據庫中。
當我們要把數據的一部分內容導入的時候，這個特點就很重要。
比方說，我們要從Access數據庫升級到MySQL數據庫的時候，需要加入一些字段（列/字 段/field）到MySQL數據庫中，以適應一些額外的需要。
這個時候，我們的Access數據庫中的數據仍然是可用的，但是因為這些數據的字段(field)與MySQL中的不再匹配，因此而無法再使用mysqlimport工具。
盡管如此，我們仍然可以使用LOAD DATA INFILE，下面的例子顯示了如何向指定的字段(field)中導入數據：
 
LOAD DATA INFILE "/home/Order.txt" INTO TABLE Orders(Order_Number, Order_Date, Customer_ID); 
 
如您所見，我們可以指定需要的字段（fields）。這些指定的字段依然是以括號括起，由逗號分隔的，如果您遺漏了其中任何一個，MySQL將會提醒您^_^

如何選擇備份工具？
直接復制數據文件是最為直接、快速的備份方法，但缺點是基本上不能實現增量備份。
備份時必須確保沒有使用這些表。如果在復制一個表的物理數據文件的同時服務器正在修改他，則復制無效。
備份文件時，最好關閉服務器，然後重新啟動服務器，為了保證數據的一致性，需要在備份文件前執行以下SQL
 
FLUSH TABLES WITH READ LOCK;
 
也就是把內存中的數據刷新到磁盤中，同時鎖定數據表，以保證復制過程中不會有新的數據寫入。
這種方法備份出來的數據恢復很簡單，直接復制回原來的數據庫目錄下即可
mysqlhotcopy是一個PERL程序，他使用LOCK TABLES、FLUSH TABLES和CP或SCP來快速備份數據庫
他是備份數據庫或單個表的最快的途徑，但他只能運行在數據庫文件所在機器上，並且mysqlhotcopy只能用於備份myisam表
mysqlhotcopy適合於小型數據庫的備份，數據量不大，可以使用mysqlhotcopy程序每天進行一次完全備份
mysqldump將數據表導出為SQL腳本，在不同的MYSQL版本之間升級時相對比較合適，這也是最常用的備份方法。
mysqldump比直接復制要慢些。

使用mysqldump備份整個數據庫成功，把表和數據庫刪除了，但使用備份文件卻不能恢復數據庫？
出現這種情況是因為備份的時候沒有指定--databases參數。默認情況下，如果只指定數據庫名稱，mysqldump
備份的是數據庫中的所有表，而不包括數據庫的創建語句，如下
 
mysqldump -u root -p booksdb >c:\booksdb_2014-7-1.sql
 
該語句只備份了booksdb數據庫下的所有表，讀者打開該文件，可以看到文件中不包含創建booksdb數據庫
的CREATE DATABASE語句，因此如果把booksdb也刪除了，使用該sql文件不能還原以前的表，
還原時會出現ERROR 1046(3D000):NO DATABASE SELECTED 的錯誤信息
而下面的語句，數據庫刪除之後，可以正常還原備份時的狀態
 
mysqldump -u root -p --databases booksdb>C:\booksdb_db_2014-7-1.sql
 
該語句不僅備份了所有數據庫下的表結構，而且包括創建數據庫的語句

總結
這一節介紹了MYSQL中的備份和還原，還有數據庫的遷移，異構數據庫之間的遷移基本上都用導出文件文件的方法
如果是小數據量尚可以，如果數據量比較大，導出文本文件也會很大，不是太可取