如何快速的復制一張表

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

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

mysqldump方法

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

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

導出csv文件

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

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

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

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

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

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

啟動事務。

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

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

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

物理拷貝方法

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

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

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

假設現(xiàn)在的目標是在db1的庫下，復制一個跟表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)缺點

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

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

用select … into outfile的方法是最靈活的，支持所有的SQL寫法。但，這個方法的缺點之一就是，每次只能導出一張表的數(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中用戶對應的對象將access值修改為‘全1'

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

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

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

同樣也是相對應的兩個操作，磁盤中權(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來查看當前用戶的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)語句生效。其實更準確的說法應該是在數(shù)據(jù)表中的權(quán)限跟內(nèi)存中的權(quán)限數(shù)據(jù)不一致的時候，flush privileges語句可以用來重建內(nèi)存數(shù)據(jù)，達到一致狀態(tài)。

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

以上就是本文的全部內(nèi)容，希望對大家的學習有所幫助，也希望大家多多支持腳本之家。

最新評論