久久久久久久视色,久久电影免费精品,中文亚洲欧美乱码在线观看,在线免费播放AV片

<center id="vfaef"><input id="vfaef"><table id="vfaef"></table></input></center>

    <p id="vfaef"><kbd id="vfaef"></kbd></p>

    
    
    <pre id="vfaef"><u id="vfaef"></u></pre>

      <thead id="vfaef"><input id="vfaef"></input></thead>

    1. 站長資訊網(wǎng)
      最全最豐富的資訊網(wǎng)站

      一文詳解如何快速遷移MySQL中的數(shù)據(jù)

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

      一文詳解如何快速遷移MySQL中的數(shù)據(jù)

      我們通常會遇到這樣的一個場景,就是需要將一個數(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_insert1G   -- 4. 調(diào)用存儲過程 call auto_insert1()
      登錄后復(fù)制

      邏輯遷移

      邏輯遷移的原理是根據(jù) MySQL 數(shù)據(jù)庫中的數(shù)據(jù)和表結(jié)構(gòu)轉(zhuǎn)換成 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
      登錄后復(fù)制

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

      mysql> use s2; Database changed  mysql> source /opt/s1.sql
      登錄后復(fù)制

      通過簡單的時間累加計算,大約消耗了 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 -h172.17.16.2 -uroot -pTest123! --add-locks=0 --single-transaction --set-gtid-purged=OFF s1 s1 --result-file=/opt/s1.sql
      登錄后復(fù)制

      通過上面的案例,我們看最終結(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)
      登錄后復(fù)制

      我們可以看到的是,將 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
      登錄后復(fù)制

      注意: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 這個文件。具體步驟如下:

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

      2. binlog 日志的工作模式如果是 STATEMENT 時,將在 binlog 中記錄上方的 SQL;

      3. 然后在從庫中重新執(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
      登錄后復(fù)制

      我們可以看到的是查詢數(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文件。

      我們在遷移到的數(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 (
      登錄后復(fù)制

      ->   `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;
      登錄后復(fù)制

      Query OK, 0 rows affected (0.04 sec)
      登錄后復(fù)制

      刪除新創(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)
      登錄后復(fù)制

      創(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)
      登錄后復(fù)制

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

      [root@dxd mysql]# pwd /var/lib/mysql [root@dxd mysql]# ll s1/
      登錄后復(fù)制

      總用量 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
      登錄后復(fù)制

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

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

      [root@dxd mysql]# cp s1/s1.cfg t1/ [root@dxd mysql]# cp s1/s1.ibd t1/
      登錄后復(fù)制

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

      [root@dxd mysql]# chown -R mysql.mysql t1/
      登錄后復(fù)制

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

      mysql> use s1; Database changed mysql> unlock tables; Query OK, 0 rows affected (0.00 sec)
      登錄后復(fù)制

      載入新的表空間。

      mysql> use t1; mysql> alter table s1 import tablespace; Query OK, 0 rows affected (0.09 sec)
      登錄后復(fù)制

      測試。

      mysql> select count( ) from s1; +—————+ | count( ) | +—————+ | 55202 | +—————+ 1 row in set (0.03 sec)
      登錄后復(fù)制

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

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

      總結(jié)

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

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

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

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

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

      推薦學(xué)習:《MySQL視頻教程》

      贊(0)
      分享到: 更多 (0)
      網(wǎng)站地圖   滬ICP備18035694號-2    滬公網(wǎng)安備31011702889846號