MySQL如何復制表

這篇文章將為大家詳細講解有關MySQL如何復制表，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

1、mysqldump

執行過程：

一、將數據導出為 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

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

1、–single-transaction 的作用是，在導出數據的時候不需要對表 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 表示生成的文件是在客戶端機器上的。

二、執行文件，添加到表中

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

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

特點

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

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

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

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

執行過程

一、導出為 CSV 文件

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

注意：

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

參數 secure_file_priv 的可選值和作用分別是：

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

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

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

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

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

二、導入數據

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

過程：

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

2、啟動事務。

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

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

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

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

特點

1、文件保存在服務器端

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

　　1）主庫執行完成后，將 /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 線程在執行這個事務日志時：

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

　　　　b. 再執行 load data 語句，往備庫的 db2.t 表中插入跟主庫相同的數據。

　　關于 "local"：

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

　　　　2）加上“local”，讀取的是客戶端的文件，只要 mysql 客戶端有訪問這個文件的權限即可。這時候，MySQL 客戶端會先把本地文件傳給服務端（其他會話涉及的操作），然后執行上述的 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 定義的目錄下，創建一個 t.sql 文件保存建表語句，同時創建一個 t.txt 文件保存 CSV 數據。

3、物理拷貝（最快）

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

過程

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

1、執行 create table r like t，創建一個相同表結構的空表；

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

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

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

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

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

注意：

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

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

局限

1、必須是全表拷貝，不能條件拷貝

2、需要到服務器上拷貝數據，在用戶無法登錄數據庫主機的場景下無法使用

3、由于是通過拷貝物理文件實現的，源表和目標表都是使用 InnoDB 引擎時才能使用

總結

1、前兩個都是邏輯備份，也就是可以跨引擎使用，最后一個不行

2、前兩個可以條件拷貝，最后一個不行

3、第二個功能是最靈活的，但是在集群從庫接收時會比較耗時（需要先拷貝 CSV 文件數據到本地臨時文件），最后一個執行效率是最高的，但是不能跨引擎，且只能進行全量拷貝。

關于“MySQL如何復制表”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。