MySQL如何復(fù)制表

這篇文章將為大家詳細(xì)講解有關(guān)MySQL如何復(fù)制表，小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

1、mysqldump

執(zhí)行過(guò)程：

一、將數(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 -h227.0.0.1 -P13000 -uroot db2 -e "source /client_tmp/t.sql"

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

特點(diǎn)

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

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

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

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

執(zhí)行過(guò)程

一、導(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ì)包含，并且使用 "\" 來(lái)轉(zhuǎn)義。

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

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

過(guò)程：

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

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

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

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

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

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

特點(diǎn)

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

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

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

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

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

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

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

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

　　關(guān)于 "local"：

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

　　　　2）加上“l(fā)ocal”，讀取的是客戶端的文件，只要 mysql 客戶端有訪問(wèn)這個(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 來(lái)同時(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 文件保存建表語(yǔ)句，同時(shí)創(chuàng)建一個(gè) t.txt 文件保存 CSV 數(shù)據(jù)。

3、物理拷貝（最快）

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

過(guò)程

假設(shè)我們現(xiàn)在的目標(biāo)是在 db1 庫(kù)下，復(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)程要有讀寫(xiě)權(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ù)頁(yè)中。因此，如果是一個(gè)很大的文件（比如 TB 級(jí)別），每個(gè)數(shù)據(jù)頁(yè)都需要修改，所以你會(huì)看到這個(gè) import 語(yǔ)句的執(zhí)行是需要一些時(shí)間的。當(dāng)然，如果是相比于邏輯導(dǎo)入的方法，import 語(yǔ)句的耗時(shí)是非常短的。

局限

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

2、需要到服務(wù)器上拷貝數(shù)據(jù)，在用戶無(wú)法登錄數(shù)據(jù)庫(kù)主機(jī)的場(chǎng)景下無(wú)法使用

3、由于是通過(guò)拷貝物理文件實(shí)現(xiàn)的，源表和目標(biāo)表都是使用 InnoDB 引擎時(shí)才能使用

總結(jié)

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

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

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

關(guān)于“MySQL如何復(fù)制表”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。