我在上一篇文章最後，給你留下的問題是怎麼在兩張表中拷貝資料。如果可以控制對源表的掃描行數和加鎖範圍很小的話，我們簡單地使用 insert … select 語句即可實作。
當然，為了避免對源表加讀鎖，更穩妥的方案是先将資料寫到外部文本檔案，然後再寫回目标表。這時，有兩種常用的方法。接下來的内容，我會和你詳細展開一下這兩種方法。
為了便于說明，我還是先建立一個表 db1.t，并插入 1000 行資料，同時建立一個相同結構的表 db2.t。
create database db1;
use db1;
create table t(id int primary key, a int, b int, index(a))engine=innodb;
delimiter ;;
create procedure idata()
begin
declare i int;
set i=1;
while(i<=1000)do
insert into t values(i,i,i);
set i=i+1;
end while;
end;;
delimiter ;
call idata();
create database db2;
create table db2.t like db1.t
複制代碼
假設，我們要把 db1.t 裡面 a>900 的資料行導出來，插入到 db2.t 中。
mysqldump 方法一種方法是，使用 mysqldump 指令将資料導出成一組 INSERT 語句。你可以使用下面的指令：
mysqldump -h$host -P$port -u$user --add-locks=0 --no-create-info --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --result-file=/client_tmp/t.sql
複制代碼
把結果輸出到臨時檔案。
這條指令中，主要參數含義如下：
- –single-transaction 的作用是，在導出資料的時候不需要對表 db1.t 加表鎖，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；
- –add-locks 設定為 0，表示在輸出的檔案結果裡，不增加" LOCK TABLES t WRITE;" ；
- –no-create-info 的意思是，不需要導出表結構；
- –set-gtid-purged=off 表示的是，不輸出跟 GTID 相關的資訊；
- –result-file 指定了輸出檔案的路徑，其中 client 表示生成的檔案是在用戶端機器上的。
通過這條 mysqldump 指令生成的 t.sql 檔案中就包含了如圖 1 所示的 INSERT 語句。
圖 1 mysqldump 輸出檔案的部分結果
可以看到，一條 INSERT 語句裡面會包含多個 value 對，這是為了後續用這個檔案來寫入資料的時候，執行速度可以更快。
如果你希望生成的檔案中一條 INSERT 語句隻插入一行資料的話，可以在執行 mysqldump 指令時，加上參數–skip-extended-insert。
然後，你可以通過下面這條指令，将這些 INSERT 語句放到 db2 庫裡去執行。
mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"
複制代碼
需要說明的是，source 并不是一條 SQL 語句，而是一個用戶端指令。mysql 用戶端執行這個指令的流程是這樣的：
- 打開檔案，預設以分号為結尾讀取一條條的 SQL 語句；
- 将 SQL 語句發送到服務端執行。
也就是說，服務端執行的并不是這個“source t.sql"語句，而是 INSERT 語句。是以，不論是在慢查詢日志（slow log），還是在 binlog，記錄的都是這些要被真正執行的 INSERT 語句。
導出 CSV 檔案另一種方法是直接将結果導出成.csv 檔案。MySQL 提供了下面的文法，用來将查詢結果導出到服務端本地目錄：
select * from db1.t where a>900 into outfile '/server_tmp/t.csv';
複制代碼
我們在使用這條語句時，需要注意如下幾點。
- 這條語句會将結果儲存在服務端。如果你執行指令的用戶端和 MySQL 服務端不在同一個機器上，用戶端機器的臨時目錄下是不會生成 t.csv 檔案的。
- into outfile 指定了檔案的生成位置（/server_tmp/），這個位置必須受參數 secure_file_priv 的限制。參數 secure_file_priv 的可選值和作用分别是： 如果設定為 empty，表示不限制檔案生成的位置，這是不安全的設定； 如果設定為一個表示路徑的字元串，就要求生成的檔案隻能放在這個指定的目錄，或者它的子目錄； 如果設定為 NULL，就表示禁止在這個 MySQL 執行個體上執行 select … into outfile 操作。
- 這條指令不會幫你覆寫檔案，是以你需要確定 /server_tmp/t.csv 這個檔案不存在，否則執行語句時就會因為有同名檔案的存在而報錯。
- 這條指令生成的文本檔案中，原則上一個資料行對應文本檔案的一行。但是，如果字段中包含換行符，在生成的文本中也會有換行符。不過類似換行符、制表符這類符号，前面都會跟上“\”這個轉義符，這樣就可以跟字段之間、資料行之間的分隔符區分開。
得到.csv 導出檔案後，你就可以用下面的 load data 指令将資料導入到目标表 db2.t 中。
load data infile '/server_tmp/t.csv' into table db2.t;
複制代碼
這條語句的執行流程如下所示。
- 打開檔案 /server_tmp/t.csv，以制表符 (\t) 作為字段間的分隔符，以換行符（\n）作為記錄之間的分隔符，進行資料讀取；
- 啟動事務。
- 判斷每一行的字段數與表 db2.t 是否相同： 若不相同，則直接報錯，事務復原； 若相同，則構造成一行，調用 InnoDB 引擎接口，寫入到表中。
- 重複步驟 3，直到 /server_tmp/t.csv 整個檔案讀入完成，送出事務。
你可能有一個疑問，
如果 binlog_format=statement，這個 load 語句記錄到 binlog 裡以後，怎麼在備庫重放呢？由于 /server_tmp/t.csv 檔案隻儲存在主庫所在的主機上，如果隻是把這條語句原文寫到 binlog 中，在備庫執行的時候，備庫的本地機器上沒有這個檔案，就會導緻主備同步停止。
是以，這條語句執行的完整流程，其實是下面這樣的。
- 主庫執行完成後，将 /server_tmp/t.csv 檔案的内容直接寫到 binlog 檔案中。
- 往 binlog 檔案中寫入語句 load data local infile ‘/tmp/SQL_LOAD_MB-1-0’ INTO TABLE `db2`.`t`。
- 把這個 binlog 日志傳到備庫。
-
備庫的 apply 線程在執行這個事務日志時：
a. 先将 binlog 中 t.csv 檔案的内容讀出來，寫入到本地臨時目錄 /tmp/SQL_LOAD_MB-1-0 中；
b. 再執行 load data 語句，往備庫的 db2.t 表中插入跟主庫相同的資料。
執行流程如圖 2 所示：
圖 2 load data 的同步流程
注意，這裡備庫執行的 load data 語句裡面，多了一個“local”。它的意思是“将執行這條指令的用戶端所在機器的本地檔案 /tmp/SQL_LOAD_MB-1-0 的内容，加載到目标表 db2.t 中”。
也就是說，
load data 指令有兩種用法- 不加“local”，是讀取服務端的檔案，這個檔案必須在 secure_file_priv 指定的目錄或子目錄下；
- 加上“local”，讀取的是用戶端的檔案，隻要 mysql 用戶端有通路這個檔案的權限即可。這時候，MySQL 用戶端會先把本地檔案傳給服務端，然後執行上述的 load data 流程。
另外需要注意的是，
select …into outfile 方法不會生成表結構檔案, 是以我們導資料時還需要單獨的指令得到表結構定義。mysqldump 提供了一個–tab 參數，可以同時導出表結構定義檔案和 csv 資料檔案。這條指令的使用方法如下：
mysqldump -h$host -P$port -u$user ---single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv
複制代碼
這條指令會在 $secure_file_priv 定義的目錄下，建立一個 t.sql 檔案儲存建表語句，同時建立一個 t.txt 檔案儲存 CSV 資料。
實體拷貝方法前面我們提到的 mysqldump 方法和導出 CSV 檔案的方法，都是邏輯導資料的方法，也就是将資料從表 db1.t 中讀出來，生成文本，然後再寫入目标表 db2.t 中。
你可能會問，有實體導資料的方法嗎？比如，直接把 db1.t 表的.frm 檔案和.ibd 檔案拷貝到 db2 目錄下，是否可行呢？
答案是不行的。
因為，一個 InnoDB 表，除了包含這兩個實體檔案外，還需要在資料字典中注冊。直接拷貝這兩個檔案的話，因為資料字典中沒有 db2.t 這個表，系統是不會識别和接受它們的。
不過，在 MySQL 5.6 版本引入了
可傳輸表空間(transportable tablespace) 的方法，可以通過導出 + 導入表空間的方式，實作實體拷貝表的功能。
假設我們現在的目标是在 db1 庫下，複制一個跟表 t 相同的表 r，具體的執行步驟如下：
- 執行 create table r like t，建立一個相同表結構的空表；
- 執行 alter table r discard tablespace，這時候 r.ibd 檔案會被删除；
- 執行 flush table t for export，這時候 db1 目錄下會生成一個 t.cfg 檔案；
- 在 db1 目錄下執行 cp t.cfg r.cfg; cp t.ibd r.ibd；這兩個指令（這裡需要注意的是，拷貝得到的兩個檔案，MySQL 程序要有讀寫權限）；
- 執行 unlock tables，這時候 t.cfg 檔案會被删除；
- 執行 alter table r import tablespace，将這個 r.ibd 檔案作為表 r 的新的表空間，由于這個檔案的資料内容和 t.ibd 是相同的，是以表 r 中就有了和表 t 相同的資料。
至此，拷貝表資料的操作就完成了。這個流程的執行過程圖如下：
圖 3 實體拷貝表
關于拷貝表的這個流程，有以下幾個注意點：
- 在第 3 步執行完 flsuh table 指令之後，db1.t 整個表處于隻讀狀态，直到執行 unlock tables 指令後才釋放讀鎖；
- 在執行 import tablespace 的時候，為了讓檔案裡的表空間 id 和資料字典中的一緻，會修改 r.ibd 的表空間 id。而這個表空間 id 存在于每一個資料頁中。是以，如果是一個很大的檔案（比如 TB 級别），每個資料頁都需要修改，是以你會看到這個 import 語句的執行是需要一些時間的。當然，如果是相比于邏輯導入的方法，import 語句的耗時是非常短的。
今天這篇文章，我和你介紹了三種将一個表的資料導入到另外一個表中的方法。
我們來對比一下這三種方法的優缺點。
- 實體拷貝的方式速度最快，尤其對于大表拷貝來說是最快的方法。如果出現誤删表的情況，用備份恢複出誤删之前的臨時庫，然後再把臨時庫中的表拷貝到生産庫上，是恢複資料最快的方法。但是，這種方法的使用也有一定的局限性： a.必須是全表拷貝，不能隻拷貝部分資料； b.需要到伺服器上拷貝資料，在使用者無法登入資料庫主機的場景下無法使用； c.由于是通過拷貝實體檔案實作的，源表和目标表都是使用 InnoDB 引擎時才能使用。
- 用 mysqldump 生成包含 INSERT 語句檔案的方法，可以在 where 參數增加過濾條件，來實作隻導出部分資料。這個方式的不足之一是，不能使用 join 這種比較複雜的 where 條件寫法。
- 用 select … into outfile 的方法是最靈活的，支援所有的 SQL 寫法。但，這個方法的缺點之一就是，每次隻能導出一張表的資料，而且表結構也需要另外的語句單獨備份。
後兩種方式都是邏輯備份方式，是可以跨引擎使用的。
最後，我給你留下一個思考題吧。
我們前面介紹 binlog_format=statement 的時候，binlog 記錄的 load data 指令是帶 local 的。既然這條指令是發送到備庫去執行的，那麼備庫執行的時候也是本地執行，為什麼需要這個 local 呢？如果寫到 binlog 中的指令不帶 local，又會出現什麼問題呢？
你可以把你的分析寫在評論區，我會在下一篇文章的末尾和你讨論這個問題。感謝你的收聽，也歡迎你把這篇文章分享給更多的朋友一起閱讀。
上期問題時間我在上篇文章最後給你留下的思考題，已經在今天這篇文章的正文部分做了回答。
上篇文章的評論區有幾個非常好的留言，我在這裡和你分享一下。
@huolang 同學提了一個問題：如果 sessionA 拿到 c=5 的記錄鎖是寫鎖，那為什麼 sessionB 和 sessionC 還能加 c=5 的讀鎖呢？
這是因為 next-key lock 是先加間隙鎖，再加記錄鎖的。加間隙鎖成功了，加記錄鎖就會被堵住。如果你對這個過程有疑問的話，可以再複習一下第 30 篇文章中的相關内容。
@一大隻 同學做了一個實驗，驗證了主鍵沖突以後，insert 語句加間隙鎖的效果。比我在上篇文章正文中提的那個復原導緻死鎖的例子更直覺，展現了他對這個知識點非常好的了解和思考，很贊。
轉載于:https://juejin.im/post/5d0601db51882540b71045b3
![【MySQL資料庫】資料庫索引事務1.索引2.事務[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)