Mysql復(fù)制表三種實(shí)現(xiàn)方法及grant解析

如何快速的復(fù)制一張表

首先創(chuàng)建一張表db1.t，并且插入1000行數(shù)據(jù)，同時(shí)創(chuàng)建一個(gè)相同結(jié)構(gòu)的表db2.t

假設(shè)，現(xiàn)在需要把db1.t里面的a>900的數(shù)據(jù)行導(dǎo)出來(lái)，插入到db2.t中

mysqldump方法

幾個(gè)關(guān)鍵參數(shù)注釋：

• –single-transaction的作用是，在導(dǎo)出數(shù)據(jù)的時(shí)候不需要對(duì)表db1.t加表鎖，而是使用
• START TRANSACTION WITH CONSISTENT SNAPSHOT的方法；
• –no-create-info的意思是，不需要導(dǎo)出表結(jié)構(gòu)；
• –result-file指定了輸出文件的路徑，其中client表示生成的文件是在客戶端機(jī)器上的。
  

導(dǎo)出csv文件

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

這條語(yǔ)句會(huì)將結(jié)果保存在服務(wù)端。如果你執(zhí)行命令的客戶端和MySQL服務(wù)端不在同一個(gè)機(jī)器上，客戶端機(jī)器的臨時(shí)目錄下是不會(huì)生成t.csv文件的。

這條命令不會(huì)幫你覆蓋文件，因此你需要確保/server_tmp/t.csv這個(gè)文件不存在，否則執(zhí)行語(yǔ)句時(shí)就會(huì)因?yàn)橛型募拇嬖诙鴪?bào)錯(cuò)。

得到.csv導(dǎo)出文件后，你就可以用下面的load data命令將數(shù)據(jù)導(dǎo)入到目標(biāo)表db2.t中。

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

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

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

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

• 若不相同，則直接報(bào)錯(cuò)，事務(wù)回滾；
• 若相同，則構(gòu)造成一行，調(diào)用InnoDB引擎接口，寫(xiě)入到表中。

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

物理拷貝方法

mysqldump方法和導(dǎo)出CSV文件的方法，都是邏輯導(dǎo)數(shù)據(jù)的方法，也就是將數(shù)據(jù)從表db1.t中讀出來(lái)，生成文本，然后再寫(xiě)入目標(biāo)表db2.t中。有物理導(dǎo)數(shù)據(jù)的方法嗎？比如，直接把db1.t表的.frm文件和.ibd文件拷貝到db2目錄下，是否可行呢？答案是不行的。

因?yàn)?，一個(gè)InnoDB表，除了包含這兩個(gè)物理文件外，還需要在數(shù)據(jù)字典中注冊(cè)。直接拷貝這兩個(gè)文件的話，因?yàn)閿?shù)據(jù)字典中沒(méi)有db2.t這個(gè)表，系統(tǒng)是不會(huì)識(shí)別和接受它們的。

在MySQL 5.6版本引入了可傳輸表空間(transportable tablespace)的方法，可以通過(guò)導(dǎo)出+導(dǎo)入表空間的方式，實(shí)現(xiàn)物理拷貝表的功能。

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

• 執(zhí)行create table r like t，創(chuàng)建一個(gè)相同表結(jié)構(gòu)的空表，
• 執(zhí)行alter table r discard tablespace，這時(shí)候r.ibd文件會(huì)被刪除
• 執(zhí)行flush table t for export這時(shí)候會(huì)生成一個(gè)t.cfg
• 在db1目錄下執(zhí)行cp t.cfg r.cfg; cp t.ibd r.ibd；這兩個(gè)命令；
• 執(zhí)行unlock tables，這時(shí)候t.cfg文件會(huì)被刪除；
• 執(zhí)行alter table r import tablespace，將這個(gè)r.ibd文件作為表r的新的表空間，由于這個(gè)文件的數(shù)據(jù)內(nèi)容和t.ibd是相同的，所以表r中就有了和表t相同的數(shù)據(jù)。
  

這三種方法的優(yōu)缺點(diǎn)

物理拷貝的方式速度最快，尤其對(duì)于大表拷貝來(lái)說(shuō)是最快的方法。但必須是全拷貝，不能是部分拷貝，需要到服務(wù)器上拷貝數(shù)據(jù)，在用戶無(wú)法登錄數(shù)據(jù)庫(kù)主機(jī)時(shí)無(wú)法使用，而且源表和目標(biāo)表都必須是innodb引擎。

用mysqldump生成包含INSERT語(yǔ)句文件的方法，可以在where參數(shù)增加過(guò)濾條件，來(lái)實(shí)現(xiàn)只導(dǎo)出部分?jǐn)?shù)據(jù)。這個(gè)方式的不足之一是，不能使用join這種比較復(fù)雜的where條件寫(xiě)法。

用select … into outfile的方法是最靈活的，支持所有的SQL寫(xiě)法。但，這個(gè)方法的缺點(diǎn)之一就是，每次只能導(dǎo)出一張表的數(shù)據(jù)，而且表結(jié)構(gòu)也需要另外的語(yǔ)句單獨(dú)備份。

后兩種都是邏輯備份方式，可以跨引擎使用的。

mysql全局權(quán)限

SELECT * FROM MYSQL.USER WHERE USER='UA'\G 顯示所有權(quán)限

作用域整個(gè)mysql，信息保存在mysql的user表里

賦予用戶ua一個(gè)最高權(quán)限：

grant all privileges on *.* to 'ua'@'%' with grant option;

這個(gè)grant命令做了兩個(gè)動(dòng)作：分別將磁盤中的mysql.user表里將權(quán)限的字段都修改為Y，和內(nèi)存中的acl_user中用戶對(duì)應(yīng)的對(duì)象將access值修改為‘全1'

如果有新的客戶端使用用戶名ua登錄成功，mysql會(huì)為新連接維護(hù)一個(gè)線程對(duì)象，所有關(guān)于全局權(quán)限的判斷，都是直接使用線程對(duì)象內(nèi)部保存的權(quán)限位。

grant命令對(duì)于全局權(quán)限，同時(shí)更新了磁盤和相應(yīng)的內(nèi)存，接下來(lái)新創(chuàng)建的連接會(huì)使用新的權(quán)限
對(duì)于已經(jīng)存在的連接，它的全局權(quán)限不受grant的影響。
如果要回收上面權(quán)限：

revoke all privileges on *.* from 'ua'@'%';

同樣也是相對(duì)應(yīng)的兩個(gè)操作，磁盤中權(quán)限字段修改位N，內(nèi)存中對(duì)象的access的值修改位0。

mysqlDB權(quán)限

grant all privileges on db1.* to 'ua'@'%' with grant option;

使用SELECT * FROM MYSQL.DB WHERE USER = 'UA'\G來(lái)查看當(dāng)前用戶的db權(quán)限，同樣的也是對(duì)磁盤和內(nèi)存中的對(duì)象修改權(quán)限。

db權(quán)限存儲(chǔ)在mysql.db表中

注意：和全局權(quán)限不同，db權(quán)限會(huì)對(duì)已經(jīng)存在的連接對(duì)象產(chǎn)生影響。

mysql表權(quán)限和列權(quán)限

表權(quán)限放在mysql.tables_priv中，列權(quán)限存放在mysql.columns_priv中，這兩類權(quán)限組合起來(lái)存放在內(nèi)存的hash結(jié)構(gòu)column_priv_hash中。

跟db權(quán)限類似，這兩個(gè)權(quán)限每次grant的時(shí)候都會(huì)修改數(shù)據(jù)表，也會(huì)同步修改內(nèi)存中的hash結(jié)構(gòu)，因此，這兩類權(quán)限的操作，也會(huì)影響到已經(jīng)存在的連接。

flush privileges的使用場(chǎng)景

有些文檔里提到，grant之后馬上執(zhí)行flush privileges命令，才能使賦權(quán)語(yǔ)句生效。其實(shí)更準(zhǔn)確的說(shuō)法應(yīng)該是在數(shù)據(jù)表中的權(quán)限跟內(nèi)存中的權(quán)限數(shù)據(jù)不一致的時(shí)候，flush privileges語(yǔ)句可以用來(lái)重建內(nèi)存數(shù)據(jù)，達(dá)到一致?tīng)顟B(tài)。

比如某時(shí)刻刪除了數(shù)據(jù)表的記錄，但是內(nèi)存的數(shù)據(jù)還存在，導(dǎo)致了給用戶賦權(quán)失敗，因?yàn)樵跀?shù)據(jù)表中找不到記錄。
同時(shí)重新創(chuàng)建這個(gè)用戶也不行，因?yàn)樵趦?nèi)存判斷的時(shí)候，會(huì)認(rèn)為這個(gè)用戶還存在。

以上就是本文的全部?jī)?nèi)容，希望對(duì)大家的學(xué)習(xí)有所幫助，也希望大家多多支持腳本之家。