本篇文章給大家?guī)?lái)了關(guān)于MySQL的相關(guān)知識(shí)，其中主要跟大家聊聊怎么才能快速地遷移MySQL中的數(shù)據(jù)，感興趣的朋友下面一起來(lái)看一下吧，希望對(duì)大家有幫助。

我們通常會(huì)遇到這樣的一個(gè)場(chǎng)景，就是需要將一個(gè)數(shù)據(jù)庫(kù)的數(shù)據(jù)遷移到一個(gè)性能更加強(qiáng)悍的數(shù)據(jù)庫(kù)服務(wù)器上。這個(gè)時(shí)候需要我們做的就是快速遷移數(shù)據(jù)庫(kù)的數(shù)據(jù)。

那么，如何才能快速地遷移數(shù)據(jù)庫(kù)中的數(shù)據(jù)呢？今天我們就來(lái)聊一聊這個(gè)話題。

數(shù)據(jù)庫(kù)的數(shù)據(jù)遷移無(wú)外乎有兩種方式，一種是物理遷移，另一種則是邏輯遷移。

首先，我們生成 5 萬(wàn)條測(cè)試數(shù)據(jù)。具體如下：

-- 1. 準(zhǔn)備表 create table s1(   id int,   name varchar(20),   gender char(6),   email varchar(50) );  -- 2. 創(chuàng)建存儲(chǔ)過(guò)程，實(shí)現(xiàn)批量插入記錄 delimiter $$ create procedure auto_insert1() BEGIN     declare i int default 1;     while(i<50000)do         insert into s1 values(i,'shanhe','male',concat('shanhe',i,'@helloworld'));         set i=i+1;         select concat('shanhe',i,'_ok');     end while; END$$ delimiter ;  -- 3. 查看存儲(chǔ)過(guò)程 show create procedure auto_insert1G   -- 4. 調(diào)用存儲(chǔ)過(guò)程 call auto_insert1()

邏輯遷移

邏輯遷移的原理是根據(jù) MySQL 數(shù)據(jù)庫(kù)中的數(shù)據(jù)和表結(jié)構(gòu)轉(zhuǎn)換成 SQL 文件。采用這一原理常用的遷移工具有 mysqldump。

下面我們就來(lái)測(cè)試一下：

[root@dxd ~]# mysqldump -h172.17.16.2 -uroot -pTest123!  s1 s1 --result-file=/opt/s1.sql  [root@dxd ~]# ll /opt/ -rw-r--r--  1 root root 2684599 5月  10 00:24 s1.sql

我們可以看到的是，生成了相應(yīng)的 SQL 。現(xiàn)在我們通過(guò)生成的 SQL 遷移到另一個(gè)數(shù)據(jù)庫(kù)中。

mysql> use s2; Database changed  mysql> source /opt/s1.sql

通過(guò)簡(jiǎn)單的時(shí)間累加計(jì)算，大約消耗了 1 秒鐘的時(shí)間，但是隨著數(shù)據(jù)庫(kù)遞增，遷移的時(shí)長(zhǎng)也會(huì)相應(yīng)地增加。此時(shí)，如果需要遷移的數(shù)據(jù)表中的數(shù)據(jù)足夠大（假設(shè)上千萬(wàn)條），mysqldump 很有可能會(huì)將內(nèi)存撐爆進(jìn)而導(dǎo)致遷移失敗。所以，在遷移這樣的數(shù)據(jù)表的時(shí)候，我們可以簡(jiǎn)單優(yōu)化一下 mysqldump ，具體如下。

• --add-locks=0：這個(gè)參數(shù)表示在遷移數(shù)據(jù)的時(shí)候不加 LOCK TABLES s1.s1 WRITE;，也就是說(shuō)在導(dǎo)入數(shù)據(jù)時(shí)不鎖定數(shù)據(jù)表。

• --single-transaction：表示的是在導(dǎo)出數(shù)據(jù)時(shí)，不鎖定數(shù)據(jù)表。

• --set-gtid-purged=OFF：表示在導(dǎo)入數(shù)據(jù)時(shí)，不輸出 GTID 相關(guān)的信息。

加上這三個(gè)參數(shù)主要是為了減少所有的操作導(dǎo)致不必要的 IO ，具體如下：

[root@dxd ~]# mysqldump -h172.17.16.2 -uroot -pTest123! --add-locks=0 --single-transaction --set-gtid-purged=OFF s1 s1 --result-file=/opt/s1.sql

通過(guò)上面的案例，我們看最終結(jié)果，優(yōu)化的效果微乎其微。所以，這種邏輯優(yōu)化的方式，在數(shù)據(jù)量比較大的情況下（百萬(wàn)條以上）不可取。

文件遷移

文件遷移顧名思義就是直接遷移數(shù)據(jù)庫(kù)的存儲(chǔ)文件。這種遷移方式相對(duì)于邏輯遷移的方式來(lái)說(shuō)，性能上要高出很多，同時(shí)也很少會(huì)把內(nèi)存撐爆；在面對(duì)數(shù)據(jù)量較大的場(chǎng)景下遷移數(shù)據(jù)，建議使用文件遷移的方式，具體如下：

mysql> select * from s1 into outfile '/var/lib/mysql-files/1.txt'; Query OK, 55202 rows affected (0.04 sec)

我們可以看到的是，將 5 萬(wàn)多條數(shù)據(jù)導(dǎo)出到文件中時(shí)，只花了 0.04 秒左右的時(shí)間。相比較 mysqldump 來(lái)說(shuō)快了一倍多。

注意：這種方式導(dǎo)出的數(shù)據(jù)只能導(dǎo)出到 MySQL 數(shù)據(jù)庫(kù)的目錄中。配置這個(gè)目錄的參數(shù)是 secure_file_priv，如果不這樣做，數(shù)據(jù)庫(kù)會(huì)報(bào)一個(gè) ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 的錯(cuò)誤。

導(dǎo)出數(shù)據(jù)之后，我們?cè)賹⒃撐募械臄?shù)據(jù)導(dǎo)入到數(shù)據(jù)庫(kù)中，看一下效果，具體如下：

mysql> load data infile '/var/lib/mysql-files/1.txt' into table s3.s1; Query OK, 55202 rows affected (0.27 sec) Records: 55202  Deleted: 0  Skipped: 0  Warnings: 0

注意：into outfile 是不會(huì)生成表結(jié)構(gòu)的，因此在導(dǎo)入數(shù)據(jù)之前，需要手動(dòng)創(chuàng)建表結(jié)構(gòu)。

我們可以看出，導(dǎo)入花費(fèi)的時(shí)間總共是0.27秒，相比較 mysqldump 而言，也要快兩倍多。

這種方式主要是將每一條數(shù)據(jù)都以n換行的方式直接保存在文件之中。

導(dǎo)入的時(shí)候，首先會(huì)判斷導(dǎo)入的數(shù)據(jù)表的字段是否與每一行的數(shù)據(jù)的列數(shù)一致，如果一致則一行一行地導(dǎo)入，如果不一致則直接報(bào)錯(cuò)。

這里面有一個(gè)問(wèn)題需要我們注意，如果我們的數(shù)據(jù)庫(kù)是主從架構(gòu)的數(shù)據(jù)庫(kù)，這里很可能就會(huì)產(chǎn)生一個(gè)問(wèn)題。講這個(gè)問(wèn)題之前，我們得首先在這里稍微說(shuō)明一下主從復(fù)制的原理。

主從復(fù)制的原理主要是依賴(lài)于 binlog 日志，binlog 日志具體步驟如下：

• 主庫(kù)上執(zhí)行 SQL ，并且把修改的數(shù)據(jù)保存在 binlog 日志之中；

• 由主庫(kù)上的 dump 線程轉(zhuǎn)發(fā)給從庫(kù)；

• 由從庫(kù)中的 IO 線程接收主庫(kù)發(fā)送過(guò)來(lái)的 binlog 日志；

• 將 binlog 日志數(shù)據(jù)寫(xiě)入中繼日志之中；

• 通過(guò)從庫(kù)上的 SQL 線程從中繼日志中重放 binlog 日志，進(jìn)而達(dá)到主從數(shù)據(jù)一致。

在這個(gè)過(guò)程之中，我相信仔細(xì)閱讀本小冊(cè)第 15 篇文章的朋友一定有一個(gè)疑問(wèn)，當(dāng) binlog 日志的工作模式為 STATEMENT 時(shí)，在主庫(kù)上執(zhí)行上面的 SQL load data infile '/var/lib/mysql-files/1.txt' into table s3.s1; 時(shí)，就會(huì)導(dǎo)致從庫(kù)無(wú)法重復(fù)上方 SQL 的結(jié)果，這是因?yàn)閺膸?kù)中并沒(méi)有 /var/lib/mysql-files/1.txt 這個(gè)文件。具體步驟如下：

1. 主庫(kù)執(zhí)行 load data infile '/var/lib/mysql-files/1.txt' into table s3.s1;；

2. binlog 日志的工作模式如果是 STATEMENT 時(shí)，將在 binlog 中記錄上方的 SQL；

3. 然后在從庫(kù)中重新執(zhí)行 binlog 中記錄上方的 SQL。

很顯然，從庫(kù)上執(zhí)行該 SQL 時(shí)，會(huì)立即報(bào)錯(cuò)，這個(gè)時(shí)候怎么辦呢？

這個(gè)時(shí)候我需要再介紹上方 SQL 的 load 關(guān)鍵字：

• 如果增加 local 關(guān)鍵字，則該條 SQL 會(huì)在本地尋找 /var/lib/mysql-files/1.txt；

• 如果不加 local 關(guān)鍵字，則該條 SQL 會(huì)在主庫(kù)端尋找 /var/lib/mysql-files/1.txt。

所以，在主從架構(gòu)中，要使用文件遷移的方式遷移數(shù)據(jù)，不加 local 關(guān)鍵字即可。

物理遷移

物理遷移也是遷移文件，所不同是物理遷移一般是直接遷移 MySQL 的數(shù)據(jù)文件。這種遷移方式性能很好但是操作過(guò)程麻煩，容易出錯(cuò)。具體我們來(lái)詳細(xì)解釋一下

首先是非常干脆的遷移方式遷移，就是直接 MySQL 數(shù)據(jù)庫(kù)的數(shù)據(jù)文件打包遷移，下面我們做一個(gè)案例：

-- 我們將s1數(shù)據(jù)庫(kù)中的所有數(shù)據(jù)遷移到s4數(shù)據(jù)庫(kù)之中 [root@dxd mysql]# pwd /var/lib/mysql [root@dxd mysql]# cp -r s1 s4 [root@dxd mysql]# chown -R mysql.mysql s4  -- 重啟數(shù)據(jù)庫(kù) [root@dxd mysql]# systemctl restart mysqld  -- 查看該表數(shù)據(jù) mysql> select count(*) from s1; ERROR 1146 (42S02): Table 's4.s1' doesn't exist

我們可以看到的是查詢(xún)數(shù)據(jù)的時(shí)候報(bào)了一個(gè) 1146 的錯(cuò)誤，這是因?yàn)?INnoDB 存儲(chǔ)引擎中的數(shù)據(jù)表是需要在 MySQL 數(shù)據(jù)庫(kù)的數(shù)據(jù)字典中注冊(cè)的，我們直接將數(shù)據(jù)文件復(fù)制過(guò)去的時(shí)候并沒(méi)有在數(shù)據(jù)字典中注冊(cè)，換句話說(shuō)就是在把數(shù)據(jù)復(fù)制過(guò)去之后，還需要在數(shù)據(jù)字典中注冊(cè)數(shù)據(jù)庫(kù)系統(tǒng)才能正常識(shí)別。

下面我們就來(lái)介紹一下在數(shù)據(jù)字典中該如何注冊(cè)，具體步驟如下。

注：物理遷移數(shù)據(jù)表數(shù)據(jù)實(shí)際上最主要的就是遷移表空間，因?yàn)閷?duì)于 InnoDB 存儲(chǔ)引擎來(lái)說(shuō)，數(shù)據(jù)是存儲(chǔ)在數(shù)據(jù)表空間中的，也就是.idb文件。

我們?cè)谶w移到的數(shù)據(jù)庫(kù)中創(chuàng)建與需要遷移的數(shù)據(jù)表完全相同的數(shù)據(jù)表。

mysql> create database t1; Query OK, 1 row affected (0.01 sec) mysql> use t1; Database changed mysql> CREATE TABLE s1 (

->   `id` int(11) DEFAULT NULL, ->   `name` varchar(20) DEFAULT NULL, ->   `gender` char(6) DEFAULT NULL, ->   `email` varchar(50) DEFAULT NULL -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Query OK, 0 rows affected (0.04 sec)

刪除新創(chuàng)建的數(shù)據(jù)表的表空間，這是因?yàn)樾聞?chuàng)建的數(shù)據(jù)庫(kù)的表空間沒(méi)有數(shù)據(jù)且會(huì)跟遷移過(guò)來(lái)的數(shù)據(jù)表空間沖突，我們提前刪除，具體刪除步驟如下：

mysql> alter table t1.s1 discard tablespace; Query OK, 0 rows affected (0.01 sec)

創(chuàng)建一個(gè)原有數(shù)據(jù)表的配置文件，這樣做的目的是將原有數(shù)據(jù)表的一些配置復(fù)制過(guò)來(lái)（注意：這一步會(huì)自動(dòng)將數(shù)據(jù)表上鎖）。

mysql> use s1; Database changed mysql> flush table s1 for export; Query OK, 0 rows affected (0.01 sec)

查看是否已經(jīng)創(chuàng)建 .cfg 文件

[root@dxd mysql]# pwd /var/lib/mysql [root@dxd mysql]# ll s1/

總用量 12312

-rw-r——- 1 mysql mysql 65 5月 10 00:26 db.opt -rw-r——- 1 mysql mysql 520 5月 10 15:15 s1.cfg -rw-r——- 1 mysql mysql 8652 5月 10 00:27 s1.frm -rw-r——- 1 mysql mysql 12582912 5月 10 00:27 s1.ibd

將配置文件和表空間文件遷移至新的數(shù)據(jù)庫(kù)。

復(fù)制文件的方式可以靈活多變

[root@dxd mysql]# cp s1/s1.cfg t1/ [root@dxd mysql]# cp s1/s1.ibd t1/

設(shè)置權(quán)限，很重要，如果權(quán)限不一致會(huì)導(dǎo)致數(shù)據(jù)讀取表空間數(shù)據(jù)失敗

[root@dxd mysql]# chown -R mysql.mysql t1/

將原有數(shù)據(jù)表解鎖。

mysql> use s1; Database changed mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)

載入新的表空間。

mysql> use t1; mysql> alter table s1 import tablespace; Query OK, 0 rows affected (0.09 sec)

測(cè)試。

mysql> select count( ) from s1; +—————+ | count( ) | +—————+ | 55202 | +—————+ 1 row in set (0.03 sec)

我們看到此時(shí)就實(shí)現(xiàn)了數(shù)據(jù)遷移。

這種數(shù)據(jù)遷移雖然性能很好，但是過(guò)程非常麻煩，很容易出現(xiàn)操作失誤的情況。

總結(jié)

今天，我們介紹了三種數(shù)據(jù)庫(kù)遷移的方式，分別是：邏輯遷移、文件遷移和物理遷移。

邏輯遷移的方式主要是使用 mysqldump 命令進(jìn)行遷移，其原理主要是將數(shù)據(jù)庫(kù)中的數(shù)據(jù)和結(jié)構(gòu)生成 SQL 文件，再導(dǎo)入即可。這種遷移方式主要適用于數(shù)據(jù)量比較小且服務(wù)器性能較好的場(chǎng)景下，例如數(shù)據(jù)連少于 500 萬(wàn)條以下的場(chǎng)景。

文件遷移的方式其實(shí)也算是邏輯遷移的范疇，它主要通過(guò)命令將數(shù)據(jù)保存在文件中，然后再導(dǎo)入數(shù)據(jù)庫(kù)即可，這種遷移方式是不會(huì)遷移表結(jié)構(gòu)的，所以在導(dǎo)入數(shù)據(jù)之前需要手動(dòng)創(chuàng)建表結(jié)構(gòu)，其原理跟邏輯遷移的方式相同。

物理遷移的方式適用于數(shù)據(jù)量比較大的場(chǎng)景，這種場(chǎng)景不易導(dǎo)致服務(wù)器因資源占用過(guò)多而宕機(jī)，但是操作過(guò)程麻煩且會(huì)鎖定原數(shù)據(jù)表。

在實(shí)際應(yīng)用過(guò)程中，我們通常選擇使用 mysqldump 的方式進(jìn)行數(shù)據(jù)遷移；如果數(shù)據(jù)量大，我們首選方式應(yīng)該是提升服務(wù)器的性能，以至于它能夠承載處理相應(yīng)數(shù)據(jù)量的性能；如果必須遷移，可以考慮使用第三方專(zhuān)業(yè)的數(shù)據(jù)遷移工具。

推薦學(xué)習(xí)：《MySQL視頻教程》