本篇內(nèi)容主要講解“如何快速遷移MySQL數(shù)據(jù)”，感興趣的朋友不妨來看看。本文介紹的方法操作簡單快捷，實用性強。下面就讓小編來帶大家學(xué)習“如何快速遷移MySQL數(shù)據(jù)”吧!

我們擁有10多年網(wǎng)頁設(shè)計和網(wǎng)站建設(shè)經(jīng)驗，從網(wǎng)站策劃到網(wǎng)站制作，我們的網(wǎng)頁設(shè)計師為您提供的解決方案。為企業(yè)提供成都網(wǎng)站制作、網(wǎng)站設(shè)計、外貿(mào)網(wǎng)站建設(shè)、微信開發(fā)、成都微信小程序、手機網(wǎng)站制作設(shè)計、H5開發(fā)、等業(yè)務(wù)。無論您有什么樣的網(wǎng)站設(shè)計或者設(shè)計方案要求，我們都將富于創(chuàng)造性的提供專業(yè)設(shè)計服務(wù)并滿足您的需求。

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

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

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

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

-- 1. 準備表
create table s1(
  id int,
  name varchar(20),
  gender char(6),
  email varchar(50)
);

-- 2. 創(chuàng)建存儲過程，實現(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. 查看存儲過程
show create procedure auto_insert1\G 

-- 4. 調(diào)用存儲過程
call auto_insert1()

邏輯遷移

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

下面我們就來測試一下：

[root@dxd ~]# mysqldump -h272.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 ?，F(xiàn)在我們通過生成的 SQL 遷移到另一個數(shù)據(jù)庫中。

mysql> use s2;
Database changed

mysql> source /opt/s1.sql

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

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

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

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

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

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

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

文件遷移

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

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

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

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

導(dǎo)出數(shù)據(jù)之后，我們再將該文件中的數(shù)據(jù)導(dǎo)入到數(shù)據(jù)庫中，看一下效果，具體如下：

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 是不會生成表結(jié)構(gòu)的，因此在導(dǎo)入數(shù)據(jù)之前，需要手動創(chuàng)建表結(jié)構(gòu)。

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

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

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

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

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

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

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

• 由從庫中的 IO 線程接收主庫發(fā)送過來的 binlog 日志；

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

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

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

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

• binlog 日志的工作模式如果是 STATEMENT 時，將在 binlog 中記錄上方的 SQL；

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

很顯然，從庫上執(zhí)行該 SQL 時，會立即報錯，這個時候怎么辦呢？

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

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

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

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

物理遷移

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

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

-- 我們將s1數(shù)據(jù)庫中的所有數(shù)據(jù)遷移到s4數(shù)據(jù)庫之中
[root@dxd mysql]# pwd
/var/lib/mysql
[root@dxd mysql]# cp -r s1 s4
[root@dxd mysql]# chown -R mysql.mysql s4

-- 重啟數(shù)據(jù)庫
[root@dxd mysql]# systemctl restart mysqld

-- 查看該表數(shù)據(jù)
mysql> select count(*) from s1;
ERROR 1146 (42S02): Table 's4.s1' doesn't exist

我們可以看到的是查詢數(shù)據(jù)的時候報了一個 1146 的錯誤，這是因為 INnoDB 存儲引擎中的數(shù)據(jù)表是需要在 MySQL 數(shù)據(jù)庫的數(shù)據(jù)字典中注冊的，我們直接將數(shù)據(jù)文件復(fù)制過去的時候并沒有在數(shù)據(jù)字典中注冊，換句話說就是在把數(shù)據(jù)復(fù)制過去之后，還需要在數(shù)據(jù)字典中注冊數(shù)據(jù)庫系統(tǒng)才能正常識別。

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

注：物理遷移數(shù)據(jù)表數(shù)據(jù)實際上最主要的就是遷移表空間，因為對于 InnoDB 存儲引擎來說，數(shù)據(jù)是存儲在數(shù)據(jù)表空間中的，也就是.idb文件。

1、我們在遷移到的數(shù)據(jù)庫中創(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)

2、刪除新創(chuàng)建的數(shù)據(jù)表的表空間，這是因為新創(chuàng)建的數(shù)據(jù)庫的表空間沒有數(shù)據(jù)且會跟遷移過來的數(shù)據(jù)表空間沖突，我們提前刪除，具體刪除步驟如下：

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

3、創(chuàng)建一個原有數(shù)據(jù)表的配置文件，這樣做的目的是將原有數(shù)據(jù)表的一些配置復(fù)制過來（注意：這一步會自動將數(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ù)庫。

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

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

設(shè)置權(quán)限，很重要，如果權(quán)限不一致會導(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)

• 測試。

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

我們看到此時就實現(xiàn)了數(shù)據(jù)遷移。這種數(shù)據(jù)遷移雖然性能很好，但是過程非常麻煩，很容易出現(xiàn)操作失誤的情況。

到此，相信大家對“如何快速遷移MySQL數(shù)據(jù)”有了更深的了解，不妨來實際操作一番吧！這里是創(chuàng)新互聯(lián)網(wǎng)站，更多相關(guān)內(nèi)容可以進入相關(guān)頻道進行查詢，關(guān)注我們，繼續(xù)學(xué)習！

標題名稱：如何快速遷移MySQL數(shù)據(jù)
URL地址：http://aaarwkj.com/article6/iipeig.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供關(guān)鍵詞優(yōu)化、用戶體驗、小程序開發(fā)、Google、網(wǎng)站設(shè)計公司、面包屑導(dǎo)航

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)