如何快速的復制一張表

首先創建一張表db1.t，并且插入1000行數據，同時創建一個相同結構的表db2.t

假設，現在需要把db1.t里面的a>900的數據行導出來，插入到db2.t中

mysqldump方法

幾個關鍵參數注釋：

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

導出csv文件

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

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

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

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

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

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

啟動事務。

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

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

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

物理拷貝方法

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

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

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

假設現在的目標是在db1的庫下，復制一個跟表t相同的表r，具體執行步驟：

• 執行create table r like t，創建一個相同表結構的空表，
• 執行alter table r discard tablespace，這時候r.ibd文件會被刪除
• 執行flush table t for export這時候會生成一個t.cfg
• 在db1目錄下執行cp t.cfg r.cfg; cp t.ibd r.ibd；這兩個命令；
• 執行unlock tables，這時候t.cfg文件會被刪除；
• 執行alter table r import tablespace，將這個r.ibd文件作為表r的新的表空間，由于這個文件的數據內容和t.ibd是相同的，所以表r中就有了和表t相同的數據。
  

這三種方法的優缺點

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

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

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

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

mysql全局權限

SELECT * FROM MYSQL.USER WHERE USER='UA'\G 顯示所有權限

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

賦予用戶ua一個最高權限：

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

這個grant命令做了兩個動作：分別將磁盤中的mysql.user表里將權限的字段都修改為Y，和內存中的acl_user中用戶對應的對象將access值修改為‘全1'

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

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

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

同樣也是相對應的兩個操作，磁盤中權限字段修改位N，內存中對象的access的值修改位0。

mysqlDB權限

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

使用SELECT * FROM MYSQL.DB WHERE USER = 'UA'\G來查看當前用戶的db權限，同樣的也是對磁盤和內存中的對象修改權限。

db權限存儲在mysql.db表中

注意：和全局權限不同，db權限會對已經存在的連接對象產生影響。

mysql表權限和列權限

表權限放在mysql.tables_priv中，列權限存放在mysql.columns_priv中，這兩類權限組合起來存放在內存的hash結構column_priv_hash中。

跟db權限類似，這兩個權限每次grant的時候都會修改數據表，也會同步修改內存中的hash結構，因此，這兩類權限的操作，也會影響到已經存在的連接。

flush privileges的使用場景

有些文檔里提到，grant之后馬上執行flush privileges命令，才能使賦權語句生效。其實更準確的說法應該是在數據表中的權限跟內存中的權限數據不一致的時候，flush privileges語句可以用來重建內存數據，達到一致狀態。

比如某時刻刪除了數據表的記錄，但是內存的數據還存在，導致了給用戶賦權失敗，因為在數據表中找不到記錄。
同時重新創建這個用戶也不行，因為在內存判斷的時候，會認為這個用戶還存在。

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