一文詳解如何快速遷移MySQL中的數據

這篇文章為大家帶來了關於MySQL的相關知識，其中主要跟大家聊聊怎麼才能快速地遷移MySQL中的數據，感興趣的朋友下面一起來看一下吧，希望對大家有幫助。

我們通常會遇到這樣的一個場景，就是需要將一個資料庫的資料遷移到一個效能更加強悍的資料庫伺服器上。這個時候需要我們做的就是快速遷移資料庫的資料。

那麼，要如何快速地遷移資料庫中的資料呢？今天我們就來聊聊這個話題。

資料庫的資料遷移無外乎有兩種方式，一種是物理遷移，另一種則是邏輯遷移。

首先，我們產生 5 萬個測試資料。具體如下：

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

-- 2. 创建存储过程，实现批量插入记录
delimiter $$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<50000)do
        insert into s1 values(i,&#39;shanhe&#39;,&#39;male&#39;,concat(&#39;shanhe&#39;,i,&#39;@helloworld&#39;));
        set i=i+1;
        select concat(&#39;shanhe&#39;,i,&#39;_ok&#39;);
    end while;
END$$
delimiter ;

-- 3. 查看存储过程
show create procedure auto_insert1\G 

-- 4. 调用存储过程
call auto_insert1()

邏輯遷移

#邏輯遷移的原理是根據MySQL 資料庫中的資料和表格結構轉換成SQL文件。採用此原理常用的遷移工具有 mysqldump。

下面我們就來測試一下：

[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

我們可以看到的是，產生了對應的 SQL 。現在我們透過產生的 SQL 遷移到另一個資料庫。

mysql> use s2;
Database changed

mysql> source /opt/s1.sql

透過簡單的時間累積計算，大約消耗了 1 秒鐘的時間，但隨著資料庫遞增，遷移的時長也會相應地增加。此時，如果需要遷移的資料表中的資料夠大（假設上千萬條），mysqldump 很有可能會將記憶體撐爆進而導致遷移失敗。所以，在遷移這樣的資料表的時候，我們可以簡單優化一下 mysqldump ，具體如下。

• --add-locks=0：這個參數表示在遷移資料的時候不加 LOCK TABLES s1.s1 WRITE;，也就是說在匯入資料時不鎖定資料表。

• --single-transaction：表示的是在匯出資料時，不鎖定資料表。

• --set-gtid-purged=OFF：表示在匯入資料時，不輸出 GTID 相關的資訊。

加上這三個參數主要是為了減少所有的操作導致不必要的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

透過上面的案例，我們看最終結果，優化的效果微乎其微。所以，這種邏輯優化的方式，在資料量比較大的情況下（百萬條以上）不可取。

檔案遷移

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

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

我們可以看到的是，將5 萬多條資料匯出到檔案時，只花了0.04 秒左右的時間。相比較 mysqldump 來說快了一倍以上。

注意：這種方式匯出的資料只能匯出到 MySQL 資料庫的目錄中。設定這個目錄的參數是 secure_file_priv，如果不這麼做，資料庫會回報一個 ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement 的錯誤。

匯出資料之後，我們再將該檔案中的資料匯入到資料庫中，看看效果，如下：

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 是不會產生表格結構的，因此在匯入資料之前，需要手動建立表格結構。

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

這種方式主要是將每個資料都以\n換行的方式直接保存在檔案中。

匯入的時候，首先會判斷匯入的資料表的欄位是否與每一行的資料的列數一致，如果一致則一行一行地匯入，如果不一致則直接報錯。

這裡面有一個問題需要我們注意，如果我們的資料庫是主從架構的資料庫，這裡很可能就會產生一個問題。在講這個問題之前，我們得先在這裡稍微說明一下主從複製的原理。

主從複製的原理主要是依賴 binlog 日誌，binlog 日誌特定步驟如下：

• ##主函式庫上執行SQL ，並且把修改的資料保存在binlog 日誌之中；

• 由主庫上的dump 執行緒轉送給從庫;

##由主庫上的dump 執行緒轉送給從庫;
由從庫中的IO 執行緒接收主庫發送過來的binlog 日誌；

• ####將binlog 日誌資料寫入中繼日誌之中;###

• 通过从库上的 SQL 线程从中继日志中重放 binlog 日志，进而达到主从数据一致。

在这个过程之中，我相信仔细阅读本小册第 15 篇文章的朋友一定有一个疑问，当 binlog 日志的工作模式为 STATEMENT 时，在主库上执行上面的 SQL load data infile '/var/lib/mysql-files/1.txt' into table s3.s1; 时，就会导致从库无法重复上方 SQL 的结果，这是因为从库中并没有 /var/lib/mysql-files/1.txt 这个文件。具体步骤如下：

1. 主库执行 load data infile '/var/lib/mysql-files/1.txt' into table s3.s1;；

2. binlog 日志的工作模式如果是 STATEMENT 时，将在 binlog 中记录上方的 SQL；

3. 然后在从库中重新执行 binlog 中记录上方的 SQL。

很显然，从库上执行该 SQL 时，会立即报错，这个时候怎么办呢？

这个时候我需要再介绍上方 SQL 的 load 关键字：

• 如果增加 local 关键字，则该条 SQL 会在本地寻找 /var/lib/mysql-files/1.txt；

• 如果不加 local 关键字，则该条 SQL 会在主库端寻找 /var/lib/mysql-files/1.txt。

所以，在主从架构中，要使用文件迁移的方式迁移数据，不加 local 关键字即可。

物理迁移

物理迁移也是迁移文件，所不同是物理迁移一般是直接迁移 MySQL 的数据文件。这种迁移方式性能很好但是操作过程麻烦，容易出错。具体我们来详细解释一下

首先是非常干脆的迁移方式迁移，就是直接 MySQL 数据库的数据文件打包迁移，下面我们做一个案例：

-- 我们将s1数据库中的所有数据迁移到s4数据库之中
[root@dxd mysql]# pwd
/var/lib/mysql
[root@dxd mysql]# cp -r s1 s4
[root@dxd mysql]# chown -R mysql.mysql s4

-- 重启数据库
[root@dxd mysql]# systemctl restart mysqld

-- 查看该表数据
mysql> select count(*) from s1;
ERROR 1146 (42S02): Table 's4.s1' doesn't exist

我们可以看到的是查询数据的时候报了一个 1146 的错误，这是因为 INnoDB 存储引擎中的数据表是需要在 MySQL 数据库的数据字典中注册的，我们直接将数据文件复制过去的时候并没有在数据字典中注册，换句话说就是在把数据复制过去之后，还需要在数据字典中注册数据库系统才能正常识别。

下面我们就来介绍一下在数据字典中该如何注册，具体步骤如下。

注：物理迁移数据表数据实际上最主要的就是迁移表空间，因为对于 InnoDB 存储引擎来说，数据是存储在数据表空间中的，也就是.idb文件。

我们在迁移到的数据库中创建与需要迁移的数据表完全相同的数据表。

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)

删除新创建的数据表的表空间，这是因为新创建的数据库的表空间没有数据且会跟迁移过来的数据表空间冲突，我们提前删除，具体删除步骤如下：

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

创建一个原有数据表的配置文件，这样做的目的是将原有数据表的一些配置复制过来（注意：这一步会自动将数据表上锁）。

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

查看是否已经创建 .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

将配置文件和表空间文件迁移至新的数据库。

复制文件的方式可以灵活多变

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

设置权限，很重要，如果权限不一致会导致数据读取表空间数据失败

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

将原有数据表解锁。

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)

我们看到此时就实现了数据迁移。

这种数据迁移虽然性能很好，但是过程非常麻烦，很容易出现操作失误的情况。

总结

今天，我们介绍了三种数据库迁移的方式，分别是：逻辑迁移、文件迁移和物理迁移。

逻辑迁移的方式主要是使用 mysqldump 命令进行迁移，其原理主要是将数据库中的数据和结构生成 SQL 文件，再导入即可。这种迁移方式主要适用于数据量比较小且服务器性能较好的场景下，例如数据连少于 500 万条以下的场景。

文件迁移的方式其实也算是逻辑迁移的范畴，它主要通过命令将数据保存在文件中，然后再导入数据库即可，这种迁移方式是不会迁移表结构的，所以在导入数据之前需要手动创建表结构，其原理跟逻辑迁移的方式相同。

物理遷移的方式適用於資料量比較大的場景，這種場景不易導致伺服器因資源佔用過多而宕機，但是操作過程麻煩且會鎖定原始資料表。

在實際應用過程中，我們通常選擇使用mysqldump 的方式進行資料遷移；如果資料量大，我們首選方式應該是提升伺服器的效能，以至於它能夠承載處理對應資料量的效能；如果必須遷移，可以考慮使用第三方專業的資料遷移工具。

推薦學習：《MySQL影片教學》

以上是一文詳解如何快速遷移MySQL中的數據的詳細內容。更多資訊請關注PHP中文網其他相關文章！