站長資訊網
本篇文章給大家帶來了關于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,'shanhe','male',concat('shanhe',i,'@helloworld')); set i=i+1; select concat('shanhe',i,'_ok'); end while; END$$ delimiter ; -- 3. 查看存儲過程 show create procedure auto_insert1G -- 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 TABLESs1.s1WRITE;，也就是說在導入數據時不鎖定數據表。 -
--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 線程轉發給從庫；
-
由從庫中的 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 這個文件。具體步驟如下：
-
主庫執行
load data infile '/var/lib/mysql-files/1.txt' into table s3.s1;； -
binlog 日志的工作模式如果是
STATEMENT時，將在 binlog 中記錄上方的 SQL； -
然后在從庫中重新執行 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視頻教程》
