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

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

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

mysqldump方法

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

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

導(dǎo)出csv文件

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

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

這條命令不會幫你覆蓋文件，因此你需要確保/server_tmp/t.csv這個文件不存在，否則執(zhí)行語句時就會因為有同名文件的存在而報錯。

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

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

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

啟動事務(wù)。

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

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

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

物理拷貝方法

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

因為，一個InnoDB表，除了包含這兩個物理文件外，還需要在數(shù)據(jù)字典中注冊。直接拷貝這兩個文件的話，因為數(shù)據(jù)字典中沒有db2.t這個表，系統(tǒng)是不會識別和接受它們的。

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

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

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

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

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

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

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

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

mysql全局權(quán)限

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

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

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

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

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

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

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

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

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

mysqlDB權(quán)限

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

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

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

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

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

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

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

flush privileges的使用場景

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

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

以上就是本文的全部內(nèi)容，希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持好吧啦網(wǎng)。