主頁 > 知識庫 > MySQL 復制表的方法

MySQL 復制表的方法

1、mysqldump

執(zhí)行過程：

一、將數(shù)據(jù)導出為 sql 文件。

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

將數(shù)據(jù)導出為 sql 文件保存。上面幾個參數(shù)的含義分別是：

1、–single-transaction 的作用是，在導出數(shù)據(jù)的時候不需要對表 db1.t 加表鎖，而是使用 START TRANSACTION WITH CONSISTENT SNAPSHOT 的方法；

2、–add-locks 設置為 0，表示在輸出的文件結果里，不增加" LOCK TABLES t WRITE;" ；

3、–no-create-info 的意思是，不需要導出表結構；

4、–set-gtid-purged=off 表示的是，不輸出跟 GTID 相關的信息；

5、–result-file 指定了輸出文件的路徑，其中 client 表示生成的文件是在客戶端機器上的。

二、執(zhí)行文件，添加到表中

mysql -h127.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"

source 并不是一條 SQL 語句，而是一個客戶端命令。也就是服務器端具體執(zhí)行的是文件中的一條條 sql 語句，所以 binlog 記錄的都是具體的 sql。

特點

1、生成的 sql 文件保存在客戶端

2、默認保存數(shù)據(jù)方式是多個記錄對，如下面格式

如果想要保存為一條語句只保存一條記錄，那么可以加上參數(shù)–skip-extended-insert。

2、導出 CSV 文件（最靈活）

執(zhí)行過程

一、導出為 CSV 文件

select * from db1.t where a>900 into outfile '/server_tmp/t.csv';

注意：

1、into outfile 指定了文件的生成位置（/server_tmp/），這個位置必須受參數(shù) secure_file_priv 的限制。

參數(shù) secure_file_priv 的可選值和作用分別是：

　　1）如果設置為 empty，表示不限制文件生成的位置，這是不安全的設置；

　　2）如果設置為一個表示路徑的字符串，就要求生成的文件只能放在這個指定的目錄，或者它的子目錄；

　　3）如果設置為 NULL，就表示禁止在這個 MySQL 實例上執(zhí)行 select … into outfile 操作。

2、如果同一個目錄下存在同名文件，就會報錯

3、一般情況下一條記錄就對應 CSV 文件中的一行，但是如果某個字段值中有 "換行、制表符" 那么文件中也會包含，并且使用 "\" 來轉義。

二、導入數(shù)據(jù)

load data infile '/server_tmp/t.csv' into table db2.t;

過程：

1、打開文件 /server_tmp/t.csv，以制表符 (\t) 作為字段間的分隔符，以換行符（\n）作為記錄之間的分隔符，進行數(shù)據(jù)讀取；

2、啟動事務。

3、判斷每一行的字段數(shù)與表 db2.t 是否相同：

　　1）若不相同，則直接報錯，事務回滾；

　　2）若相同，則構造成一行，調用 InnoDB 引擎接口，寫入到表中。

4、重復步驟 3，直到 /server_tmp/t.csv 整個文件讀入完成，提交事務。

特點

1、文件保存在服務器端

2、關于 binlog 的記錄，過程如下：

　　1）主庫執(zhí)行完成后，將 /server_tmp/t.csv 文件的內容直接寫到 binlog 文件中。

　　2）往 binlog 文件中寫入語句 load data local infile ‘/tmp/SQL_LOAD_MB-1-0' INTO TABLE `db2`.`t`。

　　3）把這個 binlog 日志傳到備庫。

　　4）備庫的 apply 線程在執(zhí)行這個事務日志時：

　　　　a. 先將 binlog 中 t.csv 文件的內容讀出來，寫入到本地臨時目錄 /tmp/SQL_LOAD_MB-1-0 中；

　　　　b. 再執(zhí)行 load data 語句，往備庫的 db2.t 表中插入跟主庫相同的數(shù)據(jù)。

　　關于 "local"：

　　　　1）不加“l(fā)ocal”，是讀取服務端的文件，這個文件必須在 secure_file_priv 指定的目錄或子目錄下；

　　　　2）加上“l(fā)ocal”，讀取的是客戶端的文件，只要 mysql 客戶端有訪問這個文件的權限即可。這時候，MySQL 客戶端會先把本地文件傳給服務端（其他會話涉及的操作），然后執(zhí)行上述的 load data 流程。

3、上面的導出操作并不會導出表結構，所以，如果向導出表結構，可以使用 mysqldump 來同時導出 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 定義的目錄下，創(chuàng)建一個 t.sql 文件保存建表語句，同時創(chuàng)建一個 t.txt 文件保存 CSV 數(shù)據(jù)。

3、物理拷貝（最快）

在5.6之前，想要直接把.frm和.ibd文件拷貝到要拷貝的目錄下是不行的，因為一個Innodb表除了需要這兩個文件還需要在數(shù)據(jù)字典中注冊。但是從 5.6 開始可以解決這一問題，在 5.6 引入了可傳輸空間，可以通過導出 + 導入表空間來實現(xiàn)拷貝

過程

假設我們現(xiàn)在的目標是在 db1 庫下，復制一個跟表 t 相同的表 r，具體的執(zhí)行步驟如下：

1、執(zhí)行 create table r like t，創(chuàng)建一個相同表結構的空表；

2、執(zhí)行 alter table r discard tablespace，這時候 r.ibd 文件會被刪除；

3、執(zhí)行 flush table t for export，這時候 db1 目錄下會生成一個 t.cfg 文件；

4、在 db1 目錄下執(zhí)行 cp t.cfg r.cfg; cp t.ibd r.ibd；這兩個命令（這里需要注意的是，拷貝得到的兩個文件，MySQL 進程要有讀寫權限）；

5、執(zhí)行 unlock tables，這時候 t.cfg 文件會被刪除；

6、執(zhí)行 alter table r import tablespace，將這個 r.ibd 文件作為表 r 的新的表空間，由于這個文件的數(shù)據(jù)內容和 t.ibd 是相同的，所以表 r 中就有了和表 t 相同的數(shù)據(jù)。

注意：

1、在第 3 步執(zhí)行完 flsuh table 命令之后，db1.t 整個表處于只讀狀態(tài)，直到執(zhí)行 unlock tables 命令后才釋放讀鎖；

2、在執(zhí)行 import tablespace 的時候，為了讓文件里的表空間 id 和數(shù)據(jù)字典中的一致，會修改 r.ibd 的表空間 id。而這個表空間 id 存在于每一個數(shù)據(jù)頁中。因此，如果是一個很大的文件（比如 TB 級別），每個數(shù)據(jù)頁都需要修改，所以你會看到這個 import 語句的執(zhí)行是需要一些時間的。當然，如果是相比于邏輯導入的方法，import 語句的耗時是非常短的。