@[toc]
MySQL數(shù)據(jù)庫備份與恢復(fù)
盡管采取了一些管理措施來保證數(shù)據(jù)庫的安全，但是在不確定的意外情況下，總是有可能造成數(shù)據(jù)的損失。
- 例如，意外的停電，不小心的操作失誤等都可能造成數(shù)據(jù)的丟失。
所以為了保證數(shù)據(jù)的安全，我們需要定期對數(shù)據(jù)進行備份。如果數(shù)據(jù)庫中的數(shù)據(jù)出現(xiàn)了錯誤，就需要使用備份好的數(shù)據(jù)進行數(shù)據(jù)還原，這樣可以將損失降至最低。
MySQL 提供了多種方法對數(shù)據(jù)進行備份和恢復(fù)。
數(shù)據(jù)庫為什么需要備份
任何數(shù)據(jù)庫都需要備份，備份數(shù)據(jù)是維護數(shù)據(jù)庫必不可少的操作。
備份就是為了防止原數(shù)據(jù)丟失，保證數(shù)據(jù)的安全。當(dāng)數(shù)據(jù)庫因為某些原因造成部分或者全部數(shù)據(jù)丟失后，備份文件可以幫我們找回丟失的數(shù)據(jù)。
因此，數(shù)據(jù)備份是很重要的工作。
常見數(shù)據(jù)庫備份的應(yīng)用場景如下。
數(shù)據(jù)丟失應(yīng)用場景：
- 人為操作失誤造成某些數(shù)據(jù)被誤操作
- 軟件 BUG 造成部分數(shù)據(jù)或全部數(shù)據(jù)丟失
- 硬件故障造成數(shù)據(jù)庫部分數(shù)據(jù)或全部數(shù)據(jù)丟失
- 安全漏洞被入侵數(shù)據(jù)惡意破壞
非數(shù)據(jù)丟失應(yīng)用場景：
- 特殊應(yīng)用場景下基于時間點的數(shù)據(jù)恢復(fù)
- 開發(fā)測試環(huán)境數(shù)據(jù)庫搭建
- 相同數(shù)據(jù)庫的新環(huán)境搭建
- 數(shù)據(jù)庫或者數(shù)據(jù)遷移
以上列出的是一些數(shù)據(jù)庫備份常見的應(yīng)用場景，數(shù)據(jù)庫備份還有其它應(yīng)用場景，這里就不一一列舉了。
比如磁盤故障導(dǎo)致整個數(shù)據(jù)庫所有數(shù)據(jù)丟失，并且無法從已經(jīng)出現(xiàn)故障的硬盤上面恢復(fù)出來時，可以通過最近時間的整個數(shù)據(jù)庫的物理或邏輯備份數(shù)據(jù)文件，盡可能的將數(shù)據(jù)恢復(fù)到故障之前最近的時間點。
操作失誤造成數(shù)據(jù)被誤操作后，我們需要有一個能恢復(fù)到錯誤操作時間點之前的瞬間的備份文件存在，當(dāng)然這個備份可能是整個數(shù)據(jù)庫的備份，也可以僅僅只是被誤操作的表的備份。
MySQL備份類型
備份是以防萬一的一種必要手段，在出現(xiàn)硬件損壞或非人為的因素而導(dǎo)致數(shù)據(jù)丟失時，可以使用備份恢復(fù)數(shù)據(jù)，以將損失降低到最小程度，因此備份是必須的。
備份可以分為以下幾個類型。根據(jù)備份的方法（是否需要數(shù)據(jù)庫離線）可以將備份分為：
- 熱備（Hot Backup）
- 熱備份可以在數(shù)據(jù)庫運行中直接備份，對正在運行的數(shù)據(jù)庫操作沒有任何的影響，數(shù)據(jù)庫的讀寫操作可以正常執(zhí)行。這種方式在 MySQL 官方手冊中稱為 Online Backup（在線備份）。
- 按照備份后文件的內(nèi)容，熱備份又可以分為：
- 邏輯備份：在 MySQL 數(shù)據(jù)庫中，邏輯備份是指備份出的文件內(nèi)容是可讀的，一般是文本內(nèi)容。內(nèi)容一般是由一條條 SQL 語句，或者是表內(nèi)實際數(shù)據(jù)組成。如 mysqldump 和 SELECT * INTO OUTFILE 的方法。這類方法的好處是可以觀察導(dǎo)出文件的內(nèi)容，一般適用于數(shù)據(jù)庫的升級、遷移等工作。但其缺點是恢復(fù)的時間較長。
- 裸文件備份：裸文件備份是指復(fù)制數(shù)據(jù)庫的物理文件，既可以在數(shù)據(jù)庫運行中進行復(fù)制（如 ibbackup、xtrabackup 這類工具），也可以在數(shù)據(jù)庫停止運行時直接復(fù)制數(shù)據(jù)文件。這類備份的恢復(fù)時間往往比邏輯備份短很多。
- 按照備份數(shù)據(jù)庫的內(nèi)容來分，備份又可以分為：
- 完全備份：是指對數(shù)據(jù)庫進行一個完整的備份，即備份整個數(shù)據(jù)庫，如果數(shù)據(jù)較多會占用較大的時間和空間。
- 部分備份：是指備份部分數(shù)據(jù)庫（例如，只備份一個表）。部分備份又分為：
- 增量備份需要使用專業(yè)的備份工具。指的是在上次完全備份的基礎(chǔ)上，對更改的數(shù)據(jù)進行備份。也就是說每次備份只會備份自上次備份之后到備份時間之內(nèi)產(chǎn)生的數(shù)據(jù)。因此每次備份都比差異備份節(jié)約空間，但是恢復(fù)數(shù)據(jù)麻煩。
- 差異備份指的是自上一次完全備份以來變化的數(shù)據(jù)。和增量備份相比，浪費空間，但恢復(fù)數(shù)據(jù)比增量備份簡單。
- 冷備（Cold Backup）
- 冷備份必須在數(shù)據(jù)庫停止的情況下進行備份，數(shù)據(jù)庫的讀寫操作不能執(zhí)行。這種備份最為簡單，一般只需要復(fù)制相關(guān)的數(shù)據(jù)庫物理文件即可。這種方式在 MySQL 官方手冊中稱為 Offline Backup（離線備份）。
- 溫備（Warm Backup）
- 溫備份同樣是在數(shù)據(jù)庫運行中進行的，但是會對當(dāng)前數(shù)據(jù)庫的操作有所影響，備份時僅支持讀操作，不支持寫操作。
MySQL 中進行不同方式的備份還要考慮存儲引擎是否支持，如 MyISAM 不支持熱備，支持溫備和冷備。而 InnoDB 支持熱備、溫備和冷備。
一般情況下，我們需要備份的數(shù)據(jù)分為以下幾種：
- 表數(shù)據(jù)
- 二進制日志、InnoDB 事務(wù)日志
- 代碼（存儲過程、存儲函數(shù)、觸發(fā)器、事件調(diào)度器）
- 服務(wù)器配置文件
下面是幾種常用的備份工具：
- mysqldump：邏輯備份工具，適用于所有的存儲引擎，支持溫備、完全備份、部分備份、對于 InnoDB 存儲引擎支持熱備。
- cp、tar 等歸檔復(fù)制工具：物理備份工具，適用于所有的存儲引擎、冷備、完全備份、部分備份。
- lvm2 snapshot：借助文件系統(tǒng)管理工具進行備份。
- mysqlhotcopy：名不副實的一個工具，僅支持 MyISAM 存儲引擎。
- xtrabackup：一款由 percona 提供的非常強大的 InnoDB/XtraDB 熱備工具，支持完全備份、增量備份。
MySQL熱備份及恢復(fù)
熱備份是在數(shù)據(jù)庫處于運行狀態(tài)時直接備份，不影響現(xiàn)有業(yè)務(wù)的正常進行。熱備份又細分為邏輯備份和裸文件備份，
邏輯備份
邏輯備份的最大優(yōu)點就是對于各種存儲引擎，都可以用同樣的方法來備份。而冷備份則不同，不同的存儲引擎的備份方法也各不相同。因此，對于不同存儲引擎混合的數(shù)據(jù)庫，用邏輯備份會更簡單一些。
邏輯備份可以說是最簡單，也是目前中小型系統(tǒng)最常用的備份方法。邏輯備份主要有以下幾種方法：
mysqldump
mysqldump 是 MySQL 自帶的邏輯備份工具。它的備份原理是通過協(xié)議連接到 MySQL 數(shù)據(jù)庫，將需要備份的數(shù)據(jù)查詢出來，然后將查詢出的數(shù)據(jù)轉(zhuǎn)換成對應(yīng)的 INSERT 語句。當(dāng)我們需要還原恢復(fù)這些數(shù)據(jù)時，只要執(zhí)行這些 INSERT 語句，就能將對應(yīng)的數(shù)據(jù)還原。所以有的資料也將這種備份方式稱為 INSERT 備份。
恢復(fù)數(shù)據(jù)時可以使用 mysql -uroot -p <backup.sql 直接調(diào)用備份文件執(zhí)行所有命令，將數(shù)據(jù)完全恢復(fù)到備份時候的狀態(tài)。如果已經(jīng)連接上了 MySQL 服務(wù)器，那么可以通過 source /path/backup.sql 來進行恢復(fù)。
SELECT INTO…OUTFILE
SELECT INTO…OUTFILE 語句可以把表數(shù)據(jù)導(dǎo)出到一個文本文件中，且能將數(shù)據(jù)庫中的表數(shù)據(jù)以特定分隔符進行分隔后記錄在文本文件中，以達到邏輯備份的效果。
這種備份方式與 mysqldump 方法相比，使用的存儲空間更小，數(shù)據(jù)格式更加清晰明確，編輯方便。但是這種方法只能導(dǎo)出或?qū)霐?shù)據(jù)的內(nèi)容，不包括表的結(jié)構(gòu)，如果表的結(jié)構(gòu)文件損壞，則必須先恢復(fù)原來的表的結(jié)構(gòu)。而且這種方法不能在同一個備份文件中存在多個表的備份數(shù)據(jù)，增加了文件維護和恢復(fù)的成本。
這種備份方法恢復(fù)起來會稍微麻煩一點，需要一個表一個表通過相關(guān)命令來進行恢復(fù)。當(dāng)然如果是通過腳本來實現(xiàn)自動多表恢復(fù)也是比較方便的?；謴?fù)方法有 2 個，一個是通過 MySQL 的 LOAD DATA INFILE 命令來恢復(fù)數(shù)據(jù)，另一種方法就是通過 MySQL 提供的使用工具 mysqlimport 來進行恢復(fù)。
mydumper
mydumper 是針對 MySQL 數(shù)據(jù)庫備份的一個輕量級第三方的開源工具，備份方式為邏輯備份。它支持多線程，備份速度遠高于原生態(tài)的 mysqldump 以及其它眾多優(yōu)異特性。與其配套的相應(yīng)恢復(fù)數(shù)據(jù)為 myloader 工具。DBA 稱 mydumper 和 myloader 為備份界的“小鋼炮”。
我們可以看出所謂的邏輯備份就是備份 SQL 語句，然后恢復(fù)數(shù)據(jù)時執(zhí)行備份 SQL，從而實現(xiàn)數(shù)據(jù)庫數(shù)據(jù)的重現(xiàn)。邏輯備份完成后所形成的文件都可以直接編輯。
邏輯備份的作用如下：
- 通過邏輯備份，我們可以僅僅恢復(fù)備份中的部分數(shù)據(jù)而不需要全部恢復(fù)。不會影響不相關(guān)的數(shù)據(jù)；
- 通過全庫的邏輯備份，我們可以在新的 MySQL 環(huán)境下完全重建出一個與備份時完全一樣的數(shù)據(jù)庫，并且不受平臺類型限制；
- 通過特定條件的邏輯備份，我們可以將某些特定數(shù)據(jù)輕松遷移（或者同步）到其它的 MySQL 或另外的數(shù)據(jù)庫環(huán)境。
裸文件備份
裸文件備份主要在底層復(fù)制數(shù)據(jù)文件，所以備份速度要比邏輯備份更快。
我們利用 Percona 公司發(fā)布的一個 XtraBackup 熱備份工具來完成裸文件備份，它是 Percona 公司的開源項目，據(jù)官方介紹它是世界上唯一 一款開源的能夠?qū)?InnoDB 和 XtraDB 數(shù)據(jù)庫進行熱備的工具。
它的優(yōu)點就是備份與恢復(fù)過程的速度很快，安全可靠，而且在備份過程中不會縮表，不影響現(xiàn)有業(yè)務(wù)。但它目前還是不能對表結(jié)構(gòu)文件和其它非事務(wù)類型的表進行備份。
MySQL冷備份及恢復(fù)
冷備份可以稱為 Offline Backup（離線備份）。這種備份最為簡單，一般只需要復(fù)制相關(guān)的數(shù)據(jù)庫物理文件到另外的位置即可。
由于 MySQL 服務(wù)器中的數(shù)據(jù)文件是基于磁盤的文本文件，所以最簡單、最直接的備份操作，就是將數(shù)據(jù)文件直接復(fù)制出來。但是由于 MySQL 服務(wù)器的數(shù)據(jù)文件在運行時期，總是處于打開和使用狀態(tài)，因此備份文件不一定有效。為了解決該問題，在復(fù)制數(shù)據(jù)文件時，需要先停止 MySQL 服務(wù)器。
這樣做的好處是可以保證數(shù)據(jù)庫的完整性，備份過程簡單且恢復(fù)速度相對快一些，但是關(guān)閉數(shù)據(jù)庫會影響現(xiàn)有業(yè)務(wù)的進行。服務(wù)器停止運行期間，用戶不能再繼續(xù)訪問網(wǎng)站。例如，一些電商網(wǎng)站店慶促銷時，如果為了備份要停庫，那么帶來的代價損失將不可估量。所以冷備一般用于不是很重要、非核心的業(yè)務(wù)上面。
冷備份的優(yōu)點如下：
- 備份簡單、快速，只要復(fù)制相關(guān)文件即可。
- 備份文件易于在不同操作系統(tǒng)，不同 MySQL 版本上進行恢復(fù)。
- 恢復(fù)相當(dāng)簡單，只需要把文件恢復(fù)到指定位置即可。
- 恢復(fù)速度快，不需要執(zhí)行任何 SQL 語句，也不需要重建索引。
- 低度維護，高度安全。
冷備份的缺點如下：
- 備份過程中，數(shù)據(jù)庫不能做其它的工作，且必須是關(guān)閉狀態(tài)。
- InnoDB 存儲引擎冷備的文件通常比邏輯文件大很多，因為表空間存放著很多其它的數(shù)據(jù)，如 undo 段，插入緩沖等信息。
- 若磁盤空間有限，只能拷貝到磁帶等其它外部存儲設(shè)備上，速度會很慢。
- 冷備也不總是可以輕易的跨平臺。操作系統(tǒng)、MySQL 的版本、文件大小寫敏感和浮點數(shù)格式都會成為問題。
數(shù)據(jù)庫的物理文件主要由數(shù)據(jù)庫的數(shù)據(jù)文件、日志文件以及配置文件等組成。MySQL 系統(tǒng)有一些共有的日志文件和系統(tǒng)表的數(shù)據(jù)文件。每種存儲引擎的物理文件也不一樣，
冷備的備份與恢復(fù)過程也很簡單。僅僅需要如下幾步：
- 為了保證所備份數(shù)據(jù)的完整性，在停止 MySQL 數(shù)據(jù)庫服務(wù)器之前，需要先執(zhí)行 FLUSH TABLES 語句將所有數(shù)據(jù)寫入到數(shù)據(jù)文件的文本文件里。
- 停掉 MySQL 服務(wù)，命令（2種方式）如下：
- mysqladmin -uroot -proot shutdown
- NET START mysql
- 備份過程就是復(fù)制整個數(shù)據(jù)目錄到遠程備份機或者本地磁盤上，Linux 和 Windows 命令如下：
- Scp -r /data/mysql/ root@遠程備份機ip:/新的目錄
- Copy -r /data/mysql/ 本地新目錄
- 備份到本次磁盤也可以手動復(fù)制上述相關(guān)目錄里的數(shù)據(jù)文件。
- 恢復(fù)過程就更簡單了，僅僅需要把已備份的數(shù)據(jù)目錄替換原有的目錄就可以了，最后重啟 MySQL 服務(wù)。
- 恢復(fù)數(shù)據(jù)是數(shù)據(jù)庫維護中最常用的操作，利用備份文件可以將 MySQL 數(shù)據(jù)庫服務(wù)器恢復(fù)到備份時的狀態(tài)，這樣就可以將管理員的非常操作和計算機的故障造成的相關(guān)損失降到最小。
需要注意的是，通過復(fù)制數(shù)據(jù)文件這種方式實現(xiàn)數(shù)據(jù)恢復(fù)時，必須保證兩個 MySQL 數(shù)據(jù)庫的主版本號一致。只有兩個 MySQL 數(shù)據(jù)庫主版本號相同時，才能保證它們的數(shù)據(jù)文件類型是相同的。
MySQL冷備份所需物理文件
MyISAM存儲引擎
MyISAM 存儲引擎的所有數(shù)據(jù)默認存放在 C:/ProgramData/MySQL/MySQL Server 5.7/Data 路徑下，即配置文件（my.ini或my.cnf）中 datadir 參數(shù)的值。
實際上不管我們使用的是 MyISAM 存儲引擎還是其他存儲引擎，每一個數(shù)據(jù)庫都會在“datadir”目錄下有一個文件夾（包括系統(tǒng)信息的數(shù)據(jù)庫 mysql 也是一樣）。
在各個數(shù)據(jù)庫中每一個 MyISAM 存儲引擎的表都會有 3 個文件存在，即記錄表結(jié)構(gòu)元數(shù)據(jù)的“.frm”文件，存儲表數(shù)據(jù)的“.MYD”文件，存儲索引數(shù)據(jù)的“.MYI”文件。
MyISAM 屬于非事務(wù)性存儲引擎，它沒有自己的日志文件。所以 MyISAM 存儲引擎的物理備份除了需要備份 MySQL 系統(tǒng)共有的物理文件之外，還需要備份上面的 3 種文件。
Innodb 存儲引擎
Innodb 存儲引擎屬于事務(wù)性存儲引擎，存放數(shù)據(jù)的位置也可能與 MyISAM 存儲引擎有所不同，這主要取決于 Innodb 的相關(guān)配置。
指定 Innodb 存放數(shù)據(jù)和日志文件的位置參數(shù)為 innodb_data_home_dir、innodb_data_file_path 和 innodb_log_group_home_dir。以及決定 Innodb 的表空間存儲方式參數(shù) innodb_file_per_table，它決定 Innodb 是以共享表空間存放數(shù)據(jù)還是以獨享表空間方式存儲數(shù)據(jù)。
如果使用的是共享表空間的存儲方式，那么需要備份 innodb_data_home_dir 和 innodb_data_file_path 參數(shù)設(shè)定的所有數(shù)據(jù)文件，以及 datadir 中相應(yīng)數(shù)據(jù)庫目錄下的所有 Innodb 存儲引擎表的 .frm 文件。
而如果使用的是獨享表空間，那么除了需要備份上面共享表空間方式所需要備份的所有文件之外，我們還需要備份 datadir 中相應(yīng)數(shù)據(jù)庫目錄下的所有 .idb 文件，該文件中存放的才是獨享表空間方式下 Innodb 存儲引擎表的數(shù)據(jù)。
那么既然是使用獨享表空間，為什么還要備份共享表空間“才使用到”的數(shù)據(jù)文件呢？其實這是很多人的一個共性誤區(qū)，以為使用獨享表空間的時候 Innodb 的所有信息就都存放在“datadir”所設(shè)定數(shù)據(jù)庫目錄下的“.ibd”文件中。實際上并不是這樣的，“.ibd”文件中所存放的僅僅只是我們的表數(shù)據(jù)而已。
大家都很清楚，Innodb 是事務(wù)性存儲引擎，它需要 undo 和 redo 信息，而不管 Innodb 使用的是共享還是獨享表空間的方式來存儲數(shù)據(jù)。與事務(wù)相關(guān)的 undo 信息以及其他的一些元數(shù)據(jù)信息，都是存放在 innodb_data_home_dir 和 innodb_data_file_path 這兩個參數(shù)所設(shè)定的數(shù)據(jù)文件中的。所以要想 Innodb 的物理備份有效，就必須備份 innodb_data_home_dir 和 innodb_data_file_path 參數(shù)所設(shè)定的數(shù)據(jù)文件。
此外，除了上面所說的數(shù)據(jù)文件之外，Innodb 還有存放自己的 redo 信息和相關(guān)事務(wù)信息的日志文件在 innodb_log_group_home_dir 參數(shù)所設(shè)定的位置。所以要想 Innodb 物理備份能夠有效使用，我們還必須要備份 innodb_log_group_home_dir 參數(shù)所設(shè)定的位置的所有日志文件。
MySQL數(shù)據(jù)庫遷移
數(shù)據(jù)庫遷移就是把數(shù)據(jù)從一個系統(tǒng)移動到另一個系統(tǒng)上，遷移過程其實就是在源數(shù)據(jù)庫備份和目標(biāo)數(shù)據(jù)庫恢復(fù)的過程組合。遷移的原因是多種多樣的，比如：
- 需要安裝新的數(shù)據(jù)庫服務(wù)器
- MySQL 版本更新
- 數(shù)據(jù)庫管理系統(tǒng)的變更（如從 SQL Server 遷移到 MySQL）
根據(jù)實際操作等情況，可以將數(shù)據(jù)庫遷移操作分成以下 3 種形式。
- 相同版本 MySQL 數(shù)據(jù)庫之間的遷移。
- 不同版本 MySQL 數(shù)據(jù)庫之間的遷移。
- 不同數(shù)據(jù)庫間的遷移。
相同版本的遷移
相同版本的 MySQL 數(shù)據(jù)庫是指主版本號一致的數(shù)據(jù)庫。主版本號一致的數(shù)據(jù)庫遷移最容易實現(xiàn)。由于遷移前后 MySQL 數(shù)據(jù)庫的主版本號相同，所以可以通過復(fù)制數(shù)據(jù)庫目錄來實現(xiàn)數(shù)據(jù)庫遷移。
最安全和最常用的方式是通過使用 mysqldump 命令進行數(shù)據(jù)庫備份，然后使用 mysql 命令將備份文件還原到新的 MySQL 數(shù)據(jù)庫。遷移時的備份和還原操作可以同時執(zhí)行。
假設(shè)從一個名為 hostname1 的機器中備份出所有數(shù)據(jù)庫，然后將這些數(shù)據(jù)庫遷移到名為 hostname2 的機器上，具體語法形式如下：
mysqldump -h hostname1 -u root -password=password1 -all-databases
|
mysql -h hostname2 -u root -password=password2
其中：
- 符號“|”用來實現(xiàn)將命令 mysqldump 備份的文件送給 mysql 命令；
- password1 為 hostname1 主機上 root 用戶的密碼；
- password2 為 hostname2 主機上 root 用戶的密碼；
- -all-databases 表示遷移全部的數(shù)據(jù)庫，可省略。
不用版本的遷移
不同版本的 MySQL 數(shù)據(jù)庫之間的數(shù)據(jù)遷移通常是 MySQL 升級的原因。例如，服務(wù)器使用 4.0 版本的 MySQL 數(shù)據(jù)庫，現(xiàn)在要升級為 5.7 版本的。這樣就需要不同版本的 MySQL 數(shù)據(jù)庫之間進行數(shù)據(jù)遷移。
不同版本下的數(shù)據(jù)庫遷移，分為 2 種方式：
- 低版本數(shù)據(jù)庫向高版本數(shù)據(jù)庫進行遷移
- 高版本數(shù)據(jù)庫向低版本數(shù)據(jù)庫進行遷移
低版本數(shù)據(jù)庫向高版本數(shù)據(jù)庫進行遷移時，由于高版本會兼容低版本，所以該種方式也是最容易實現(xiàn)的操作。對于存儲類型為 MyISAM 的表，最安全和最常用的操作是直接復(fù)制數(shù)據(jù)文件。對于存儲類型為 InnoDB 的表，最安全和最常用的操作是執(zhí)行 mysqldump 命令進行備份和執(zhí)行 mysql 命令還原恢復(fù)數(shù)據(jù)。
但是高版本數(shù)據(jù)庫向低版本數(shù)據(jù)庫進行遷移時，因為高版本數(shù)據(jù)庫可能有一些新的特性，這些特性是低版本數(shù)據(jù)庫所不具有的，所以數(shù)據(jù)庫遷移時要特別小心，最好使用 mysqldump 命令來進行備份，避免遷移時造成數(shù)據(jù)丟失。
不同數(shù)據(jù)庫的遷移
不同數(shù)據(jù)庫之間的遷移是指從其它類型的數(shù)據(jù)庫遷移到 MySQL 數(shù)據(jù)庫，或者從 MySQL 數(shù)據(jù)庫遷移到其他類型的數(shù)據(jù)庫。例如，某個網(wǎng)站原來使用 Oracle 數(shù)據(jù)庫，因為運營成本太高等諸多原因，希望改用 MySQL 數(shù)據(jù)庫?；蛘?，某個管理系統(tǒng)原來使用 MySQL 數(shù)據(jù)庫，因為某種特殊性能的要求，希望改用 Oracle 數(shù)據(jù)庫。這樣的不同數(shù)據(jù)庫之間的遷移也經(jīng)常會發(fā)生。但是這種遷移沒有普通適用的解決辦法。
其它數(shù)據(jù)庫也有類似 mysqldump 這樣的備份工具，可以將數(shù)據(jù)庫中的文件備份成 sql 文件或普通文本。但是，不同的數(shù)據(jù)庫廠商并沒有完全按照 SQL 標(biāo)準(zhǔn)來設(shè)計數(shù)據(jù)庫，這就造成了不同數(shù)據(jù)庫使用的 SQL 語句的差異。例如，微軟的 SQL Server 軟件使用的是 T-SQL 語言。T-SQL 中包含了非標(biāo)準(zhǔn)的 SQL 語句。這就造成了 SQL Server 和 MySQL 的 SQL 語句不能兼容。
除了 SQL 語句存在不兼容的情況外，不同的數(shù)據(jù)庫之間的數(shù)據(jù)類型也有差異。例如，MySQL 不支持 SQL Server 中的 ntext、 Image 等數(shù)據(jù)類型。同樣，SQL Server 也不支持 MySQL 中的 ENUM 和 SET 等數(shù)據(jù)類型。數(shù)據(jù)類型的差異也造成了遷移的困難。
從某種意義上說，這種差異是商業(yè)數(shù)據(jù)庫公司故意造成的壁壘，這種行為是阻礙數(shù)據(jù)庫市場健康發(fā)展的。
但是不同數(shù)據(jù)庫服務(wù)器間的遷移并不是完全不可能。在 Windows 操作系統(tǒng)下，如果要實現(xiàn)從 MySQL 數(shù)據(jù)庫服務(wù)器向 SQL SERVER 數(shù)據(jù)庫服務(wù)器遷移，可以通過 MyODBC 來實現(xiàn)；如果要實現(xiàn)從 MySQL 數(shù)據(jù)庫服務(wù)器向 ORACLE 數(shù)據(jù)庫服務(wù)器遷移，可以先通過執(zhí)行 mysqldump 命令導(dǎo)出 sql 文件，然后手動修改 sql 文件中的 CREATE 語句。
MySQL mysqldump備份數(shù)據(jù)庫
數(shù)據(jù)庫的主要作用就是對數(shù)據(jù)進行保存和維護，所以備份數(shù)據(jù)是數(shù)據(jù)庫管理中最常用的操作。為了防止數(shù)據(jù)庫意外崩潰或硬件損傷而導(dǎo)致的數(shù)據(jù)丟失，數(shù)據(jù)庫系統(tǒng)提供了備份和恢復(fù)策略。
保證數(shù)據(jù)安全的最重要的一個措施就是定期的對數(shù)據(jù)庫進行備份。這樣即使發(fā)生了意外，也會把損失降到最低。
數(shù)據(jù)庫備份是指通過導(dǎo)出數(shù)據(jù)或者復(fù)制表文件的方式來制作數(shù)據(jù)庫的副本。當(dāng)數(shù)據(jù)庫出現(xiàn)故障或遭到破壞時，將備份的數(shù)據(jù)庫加載到系統(tǒng)，從而使數(shù)據(jù)庫從錯誤狀態(tài)恢復(fù)到備份時的正確狀態(tài)。
MySQL 中提供了兩種備份方式，即 mysqldump 命令以及 mysqlhotcopy 腳本。由于 mysqlhotcopy 只能用于 MyISAM 表，所以 MySQL 5.7 移除了 mysqlhotcopy 腳本。
mysqldump 命令執(zhí)行時，可以將數(shù)據(jù)庫中的數(shù)據(jù)備份成一個文本文件。數(shù)據(jù)表的結(jié)構(gòu)和數(shù)據(jù)將存儲在生成的文本文件中。
備份一個數(shù)據(jù)庫
使用 mysqldump 命令備份一個數(shù)據(jù)庫的語法格式如下：
mysqldump -u username -p dbname [tbname ...]> filename.sql
對上述語法參數(shù)說明如下：
- username：表示用戶名稱；
- dbname：表示需要備份的數(shù)據(jù)庫名稱；
- tbname：表示數(shù)據(jù)庫中需要備份的數(shù)據(jù)表，可以指定多個數(shù)據(jù)表。省略該參數(shù)時，會備份整個數(shù)據(jù)庫；
- 右箭頭“>”：用來告訴 mysqldump 將備份數(shù)據(jù)表的定義和數(shù)據(jù)寫入備份文件；
- filename.sql：表示備份文件的名稱，文件名前面可以加絕對路徑。通常將數(shù)據(jù)庫備份成一個后綴名為
.sql的文件。
注意：mysqldump 命令備份的文件并非一定要求后綴名為.sql，備份成其他格式的文件也是可以的。例如，后綴名為.txt的文件。通常情況下，建議備份成后綴名為.sql 的文件。因為，后綴名為.sql的文件給人第一感覺就是與數(shù)據(jù)庫有關(guān)的文件。
備份多個數(shù)據(jù)庫
如果要使用 mysqldump 命令備份多個數(shù)據(jù)庫，需要使用 --databases 參數(shù)。備份多個數(shù)據(jù)庫的語法格式如下：
mysqldump -u username -P --databases dbname1 dbname2 ... > filename.sql
加上“--databases”參數(shù)后，必須指定至少一個數(shù)據(jù)庫名稱，多個數(shù)據(jù)庫名稱之間用空格隔開。
備份所有數(shù)據(jù)庫
mysqldump 命令備份所有數(shù)據(jù)庫的語法格式如下：
mysqldump -u username -P --all-databases>filename.sql
使用“--all-databases”參數(shù)時，不需要指定數(shù)據(jù)庫名稱。
MySQL恢復(fù)數(shù)據(jù)庫
當(dāng)數(shù)據(jù)丟失或意外損壞時，可以通過恢復(fù)已經(jīng)備份的數(shù)據(jù)來盡量減少數(shù)據(jù)的丟失和破壞造成的損失。
在 MySQL 中，可以使用 mysql 命令來恢復(fù)備份的數(shù)據(jù)。mysql 命令可以執(zhí)行備份文件中的 CREATE 語句和 INSERT 語句，也就是說，mysql 命令可以通過 CREATE 語句來創(chuàng)建數(shù)據(jù)庫和表，通過 INSERT 語句來插入備份的數(shù)據(jù)。
mysql 命令語法格式如下：
mysql -u username -P [dbname] < filename.sql
其中：
- username 表示用戶名稱；
- dbname 表示數(shù)據(jù)庫名稱，該參數(shù)是可選參數(shù)。如果 filename.sql 文件為 mysqldump 命令創(chuàng)建的包含創(chuàng)建數(shù)據(jù)庫語句的文件，則執(zhí)行時不需要指定數(shù)據(jù)庫名。如果指定的數(shù)據(jù)庫名不存在將會報錯；
- filename.sql 表示備份文件的名稱。
注意：mysql 命令和 mysqldump 命令一樣，都直接在命令行（cmd）窗口下執(zhí)行。
注意：如果使用--all-databases參數(shù)備份了所有的數(shù)據(jù)庫，那么恢復(fù)時不需要指定數(shù)據(jù)庫。因為，其對應(yīng)的 sql 文件中含有 CREATE DATABASE 語句，可以通過該語句創(chuàng)建數(shù)據(jù)庫。創(chuàng)建數(shù)據(jù)庫之后，可以執(zhí)行 sql 文件中的 USE 語句選擇數(shù)據(jù)庫，然后在數(shù)據(jù)庫中創(chuàng)建表并且插入記錄。
MySQL導(dǎo)出表數(shù)據(jù)
通過對數(shù)據(jù)表的導(dǎo)入導(dǎo)出，可以實現(xiàn) MySQL 數(shù)據(jù)庫服務(wù)器與其它數(shù)據(jù)庫服務(wù)器間移動數(shù)據(jù)。導(dǎo)出是指將 MySQL 數(shù)據(jù)表的數(shù)據(jù)復(fù)制到文本文件。數(shù)據(jù)導(dǎo)出的方式有多種，本節(jié)主要介紹使用 SELECTI...INTO OUTFILE 語句導(dǎo)出數(shù)據(jù)。
在 MySQL 中，可以使用 SELECTI...INTO OUTFILE 語句將表的內(nèi)容導(dǎo)出成一個文本文件。SELECT...INTO OUTFILE 語句基本格式如下:
SELECT 列名 FROM table [WHERE 語句] INTO OUTFILE '目標(biāo)文件'[OPTIONS]
該語句用 SELECT 來查詢所需要的數(shù)據(jù)，用 INTO OUTFILE 來導(dǎo)出數(shù)據(jù)。其中，目標(biāo)文件用來指定將查詢的記錄導(dǎo)出到哪個文件。這里需要注意的是，目標(biāo)文件不能是一個已經(jīng)存在的文件。
[OPTIONS] 為可選參數(shù)選項，OPTIONS 部分的語法包括 FIELDS 和 LINES 子句，其常用的取值有：
- FIELDS TERMINATED BY '字符串'：設(shè)置字符串為字段之間的分隔符，可以為單個或多個字符，默認情況下為制表符‘\t’。
- FIELDS [OPTIONALLY] ENCLOSED BY '字符'：設(shè)置字符來括上 CHAR、VARCHAR 和 TEXT 等字符型字段。如果使用了 OPTIONALLY 則只能用來括上 CHAR 和 VARCHAR 等字符型字段。
- FIELDS ESCAPED BY '字符'：設(shè)置如何寫入或讀取特殊字符，只能為單個字符，即設(shè)置轉(zhuǎn)義字符，默認值為‘\’。
- LINES STARTING BY '字符串'：設(shè)置每行開頭的字符，可以為單個或多個字符，默認情況下不使用任何字符。
- LINES TERMINATED BY '字符串'：設(shè)置每行結(jié)尾的字符，可以為單個或多個字符，默認值為‘\n’ 。
注意：FIELDS 和 LINES 兩個子句都是自選的，但是如果兩個都被指定了，F(xiàn)IELDS 必須位于 LINES的前面。
注意：導(dǎo)出時可能會出現(xiàn)下面的錯誤：
The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
這是因為MySQL 限制了數(shù)據(jù)的導(dǎo)出路徑。MySQL 導(dǎo)入導(dǎo)出文件只能在 secure-file-priv 變量的指定路徑下的文件才可以導(dǎo)入導(dǎo)出。
有以下 2 種解決辦法：
首先使用show variables like '%secure%';語句查看 secure-file-priv 變量配置。
mysql> show variables like '%secure%' \G
*************************** 1. row ***************************
Variable_name: require_secure_transport
Value: OFF
*************************** 2. row ***************************
Variable_name: secure_auth
Value: ON
*************************** 3. row ***************************
Variable_name: secure_file_priv
Value: C:\ProgramData\MySQL\MySQL Server 5.7\Uploads\
3 rows in set, 1 warning (0.04 sec)
secure_file_priv 的值指定的是 MySQL 導(dǎo)入導(dǎo)出文件的路徑。將 SQL 語句中的導(dǎo)出文件路徑修改為該變量的指定路徑，再執(zhí)行導(dǎo)入導(dǎo)出操作即可。也可以在 my.ini 配置文件中修改 secure-file-priv 的值，然后重啟服務(wù)即可。
如果 secure_file_priv 值為 NULL，則為禁止導(dǎo)出，可以在 MySQL 安裝路徑下的 my.ini 文件中添加secure_file_priv=設(shè)置路徑語句，然后重啟服務(wù)即可。
使用 SELECT...INTO OUTFILE 語句將 test 數(shù)據(jù)庫中的 person 表中的記錄導(dǎo)出到文本文件，使用 FIELDS 選項和 LINES 選項，要求字段之間用、隔開，字符型數(shù)據(jù)用雙引號括起來。每條記錄以-開頭。SQL 語句如下：
SELECT * FROM test.person INTO OUTFILE 'C:/person.txt'
FIELDS TERMINATED BY '\、' OPTIONALLY ENCLOSED BY '\"' LINES STARTING BY '\-'
TERMINATED BY '\r\n';
其中：
- FIELDS TERMINATED BY '、’：表示字段之間用
、分隔； - ENCLOSED BY '"'：表示每個字段都用雙引號括起來；
- LINES STARTING BY '-'：表示每行以
-開頭； - TERMINATED BY '\r\n' 表示每行以回車換行符結(jié)尾，保證每一條記錄占一行。
person.txt 文件內(nèi)容如下：
-1、"Java"、12
-2、"MySQL"、13
-3、"C"、15
-4、"C++"、22
-5、"Python"、18
可以看到，每條記錄都以-開頭，每個數(shù)據(jù)之間以都以、隔開，所有的字段值都被雙引號包括。
MySQL數(shù)據(jù)庫恢復(fù)
數(shù)據(jù)庫恢復(fù)是指以備份為基礎(chǔ)，與備份相對應(yīng)的系統(tǒng)維護和管理操作。
系統(tǒng)進行恢復(fù)操作時，先執(zhí)行一些系統(tǒng)安全性的檢查，包括檢查所要恢復(fù)的數(shù)據(jù)庫是否存在、數(shù)據(jù)庫是否變化及數(shù)據(jù)庫文件是否兼容等，然后根據(jù)所采用的數(shù)據(jù)庫備份類型采取相應(yīng)的恢復(fù)措施。
數(shù)據(jù)庫恢復(fù)機制設(shè)計的兩個關(guān)鍵問題是：
- 第一，如何建立冗余數(shù)據(jù)；
- 第二，如何利用這些冗余數(shù)據(jù)實施數(shù)據(jù)庫恢復(fù)。
建立冗余數(shù)據(jù)最常用的技術(shù)是數(shù)據(jù)轉(zhuǎn)儲和登錄日志文件。通常在一個數(shù)據(jù)庫系統(tǒng)中，這兩種方法是一起使用的。
數(shù)據(jù)轉(zhuǎn)儲是 DBA 定期地將整個數(shù)據(jù)庫復(fù)制到磁帶或另一個磁盤上保存起來的過程。這些備用的版本成為后備副本或后援副本。
可使用 LOAD DATA…INFILE 語句來恢復(fù)先前備份的數(shù)據(jù)。
例子：將之前導(dǎo)出的數(shù)據(jù)備份文件 file.txt 導(dǎo)入數(shù)據(jù)庫 test_db 的表 tb_students_copy 中，其中 tb_students_copy 的表結(jié)構(gòu)和 tb_students_info 相同。
首先創(chuàng)建表 tb_students_copy，輸入的 SQL 語句和執(zhí)行結(jié)果如下所示。
mysql> CREATE TABLE tb_students_copy
-> LIKE tb_students_info;
Query OK, 0 rows affected (0.52 sec)
mysql> SELECT * FROM tb_students_copy;
Empty set (0.00 sec)
導(dǎo)入數(shù)據(jù)與查詢表 tb_students_copy 的過程如下所示。
mysql> LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 5.7/
Uploads/file.txt'
-> INTO TABLE test_db.tb_students_copy
-> FIELDS TERMINATED BY ','
-> OPTIONALLY ENCLOSED BY '"'
-> LINES TERMINATED BY '?';
Query OK, 10 rows affected (0.14 sec)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0
mysql> SELECT * FROM test_db.tb_students_copy;
+----+--------+---------+------+------+--------+------------+
| id | name | dept_id | age | sex | height | login_date |
+----+--------+---------+------+------+--------+------------+
| 1 | Dany | 1 | 25 | F | 160 | 2015-09-10 |
| 2 | Green | 3 | 23 | F | 158 | 2016-10-22 |
| 3 | Henry | 2 | 23 | M | 185 | 2015-05-31 |
| 4 | Jane | 1 | 22 | F | 162 | 2016-12-20 |
| 5 | Jim | 1 | 24 | M | 175 | 2016-01-15 |
| 6 | John | 2 | 21 | M | 172 | 2015-11-11 |
| 7 | Lily | 6 | 22 | F | 165 | 2016-02-26 |
| 8 | Susan | 4 | 23 | F | 170 | 2015-10-01 |
| 9 | Thomas | 3 | 22 | M | 178 | 2016-06-07 |
| 10 | Tom | 4 | 23 | M | 165 | 2016-08-05 |
+----+--------+---------+------+------+--------+------------+
10 rows in set (0.00 sec)
