快捷導(dǎo)航

MySQL 復(fù)制表的方法

更新時(shí)間：2021年03月09日 09:54:43 作者：萌新J

這篇文章主要介紹了MySQL 復(fù)制表的方法，幫助大家更好的理解和學(xué)習(xí)使用MySQL，感興趣的朋友可以了解下

1、mysqldump

執(zhí)行過程：

一、將數(shù)據(jù)導(dǎo)出為 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ù)導(dǎo)出為 sql 文件保存。上面幾個(gè)參數(shù)的含義分別是：

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

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

3、–no-create-info 的意思是，不需要導(dǎo)出表結(jié)構(gòu)；

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

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

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

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

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

特點(diǎn)

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

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

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

2、導(dǎo)出 CSV 文件（最靈活）

執(zhí)行過程

一、導(dǎo)出為 CSV 文件

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

注意：

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

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

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

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

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

2、如果同一個(gè)目錄下存在同名文件，就會(huì)報(bào)錯(cuò)

3、一般情況下一條記錄就對(duì)應(yīng) CSV 文件中的一行，但是如果某個(gè)字段值中有 "換行、制表符" 那么文件中也會(huì)包含，并且使用 "\" 來轉(zhuǎn)義。

二、導(dǎo)入數(shù)據(jù)

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

過程：

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

2、啟動(dòng)事務(wù)。

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

　　1）若不相同，則直接報(bào)錯(cuò)，事務(wù)回滾；

　　2）若相同，則構(gòu)造成一行，調(diào)用 InnoDB 引擎接口，寫入到表中。

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

特點(diǎn)

1、文件保存在服務(wù)器端

2、關(guān)于 binlog 的記錄，過程如下：

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

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

　　3）把這個(gè) binlog 日志傳到備庫。

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

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

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

　　關(guān)于 "local"：

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

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

3、上面的導(dǎo)出操作并不會(huì)導(dǎo)出表結(jié)構(gòu)，所以，如果向?qū)С霰斫Y(jié)構(gòu)，可以使用 mysqldump 來同時(shí)導(dǎo)出 CSV 和表結(jié)構(gòu)

mysqldump -h$host -P$port -u$user --single-transaction --set-gtid-purged=OFF db1 t --where="a>900" --tab=$secure_file_priv

會(huì)在$secure_file_priv 定義的目錄下，創(chuàng)建一個(gè) t.sql 文件保存建表語句，同時(shí)創(chuàng)建一個(gè) t.txt 文件保存 CSV 數(shù)據(jù)。

3、物理拷貝（最快）

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

過程

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

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

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

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

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

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

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

注意：

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

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