MySQL導出備份(數據導出)方法詳解

數據庫導出數據或備份數據都是我們一個運維人員會要干的事情，下面我來總結一下mysql中MySQL導出備份操作技巧，希望對各位同學有所幫助。

表數據導出到一個文本文件最簡單的方法是使用SELECT... INTO OUTFILE語句的查詢結果直接導出到一個文件在服務器主機上。

導出數據的SELECT...INTO OUTFILE聲明：

這句話的語法結合了常規的SELECT INTO OUTFILE文件名的末尾。默認的輸出格式是相同的LOAD DATA，所以下面的語句導出tutorials_tbl的表制表符分隔的，換行結尾的文件到/tmp/tutorials.txt：

 代碼如下 復制代碼 mysql> SELECT * FROM tutorials_tbl
    -> INTO OUTFILE '/tmp/tutorials.txt';

也可以更改輸出格式，使用選項來指示如何引用和分隔列和記錄。 CRLF終止線要導出tutorial_tbl的CSV格式的表格，使用這樣的語句：

 代碼如下 復制代碼 mysql> SELECT * FROM passwd INTO OUTFILE '/tmp/tutorials.txt'
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY 'rn';
SELECT ... INTO OUTFILE

具有以下屬性：

由MySQL服務器直接創建輸出文件，這樣的文件名應指定您希望在服務器主機上的文件將被寫入。沒有本地版本的LOAD DATA LOCAL版本的類似的語句。

必須要有MySQL的FILE權限才能執行的SELECT... INTO語句。

輸出文件必須不存在。這可以防止MySQL從會破壞文件可能重要。

應該在服務器主機上或一些方法來檢索文件從該主機的登錄帳戶。否則SELECT... INTO OUTFILE可能是沒有價值的。

在Unix下，創造了世界可讀的文件和所擁有的MySQL服務器。這意味著雖然就可以讀取該文件，但是不能夠刪除它。

作為原始數據的導出表：
使用mysqldump程序復制或備份表和數據庫。它可以寫表輸出的原始數據文件或為一組的INSERT語句重新創建該表中的記錄。
轉儲一個表，必須指定一個數據文件 - 選項卡上的選項，表示想要的MySQL服務器寫入文件所在的目錄。

使用mysqldump程序復制或備份表和數據庫。它可以寫表輸出的原始數據文件，或為一組的INSERT語句重新創建該表中的記錄。
轉儲一個表，必須指定一個數據文件 - 選項卡上的選項，表示想要的MySQL服務器寫入文件所在的目錄。

 代碼如下 復制代碼 $ mysqldump -u root -p --no-create-info
            --tab=/tmp TUTORIALS tutorials_tbl
password ******

在SQL格式導出表的內容或定義：
一個表中的SQL格式導出到一個文件，使用這樣的命令：

 代碼如下 復制代碼 $ mysqldump -u root -p TUTORIALS tutorials_tbl > dump.txt
password ******

這將創建文件的內容如下：

 代碼如下 復制代碼

--------------------------------------------------------------------------------

-- MySQL dump 8.23
--
-- Host: localhost    Database: TUTORIALS
---------------------------------------------------------
-- Server version       3.23.58

--
-- Table structure for table `tutorials_tbl`
--

CREATE TABLE tutorials_tbl (
  tutorial_id int(11) NOT NULL auto_increment,
  tutorial_title varchar(100) NOT NULL default '',
  tutorial_author varchar(40) NOT NULL default '',
  submission_date date default NULL,
  PRIMARY KEY  (tutorial_id),
  UNIQUE KEY AUTHOR_INDEX (tutorial_author)
) TYPE=InnoDB;

--
-- Dumping data for table `tutorials_tbl`
--

INSERT INTO tutorials_tbl
       VALUES (1,'Learn PHP','John Poul','2007-05-24');
INSERT INTO tutorials_tbl
       VALUES (2,'Learn MySQL','Abdul S','2007-05-24');
INSERT INTO tutorials_tbl
       VALUES (3,'JAVA Tutorial','Sanjay','2007-05-06');

要轉儲多個表，他們的名字所有的數據庫名稱參數。要轉儲整個數據庫，不指定任何表後的數據庫，如下所示：

 代碼如下 復制代碼 $ mysqldump -u root -p TUTORIALS > database_dump.txt
password ******

所有可用的數據庫備份主機上使用以下命令：

 代碼如下 復制代碼 $ mysqldump -u root -p --all-databases > database_dump.txt
password ******

在SQL格式導出表的內容或定義：

這些方法可用於實現數據庫的備份策略。

表或數據庫復制到另一台主機：
如果要復制的表或數據庫從一個MySQL服務器，然後使用mysqldump數據庫名和表名。

在源主機上運行以下命令。這將轉儲完整的數據庫到dump.txt文件：

 代碼如下 復制代碼 $ mysqldump -u root -p database_name table_name > dump.txt
password *****

復制完整的數據庫，而不使用一個特定的表名稱，如上面所解釋。

現在，FTP dump.txt文件在另一台主機上，使用下面的命令。在運行此命令之前，請確保已創建目標服務器上的數據庫名稱。

 代碼如下 復制代碼 $ mysql -u root -p database_name < dump.txt
password *****

另一種方法，而無需使用一個中間文件來完成，mysqldump輸出直接通過網絡發送到遠程的MySQL服務器。如果可以連接到兩台服務器的cookbook 數據庫所在的主機上，使用以下命令：

 代碼如下 復制代碼 $ mysqldump -u root -p database_name
            states | mysql -h other-host.com database_name

命令mysqldump的一半連接到本地服務器，並寫入轉儲輸出管道。 MySQL的一半的命令連接到遠程MySQL服務器otherhost.com。它讀取輸入管和其他host.com服務器發送的每個語句。

上面介紹的都是手動備份或導出數據，下面我們來寫一個自動定時備份的

windows自帶的“任務計劃”功能，設定一個時間，讓系統定時跑腳本，不就實現了自動備份數據庫的功能了嗎？首先把腳本代碼貼出來：

 代碼如下 復制代碼

    @echo on 
    
    REM------------------------backup bugdb which is InnoDB----------------------------- 
    del F:/backup/website/bugdb_*.sql 
    cd F:/usr/wamp/mysql/bin 
    set year=%date:~0,4% 
    set month=%date:~5,2% 
    set day=%date:~8,2% 
    set filename=bugdb_%year%%month%%day%.sql 
    mysqldump.exe bugdb -uroot -p123456 > F:/backup/website/%filename% 
    
    @echo off

依次解釋一下每句代碼的意思：

    第四行：刪除指定目錄下的文件名包含有“bugdb_”字樣的sql文件。因為這個代碼是我先前寫的，在公司的服務器上每天晚上跑一次。所以每次備份之前，先刪除頭天已備份成功的文件。
    第五行：進入MySQL的bin目錄，因為在此目錄下有個mysqldump.exe的文件，該文件時MySQL數據庫自帶的備份和恢復MySQL數據庫的工具，這個腳本文件正是用到該工具。
    第六行：取當前系統日期的年份，以四位數字表示，如2010。
    第七行：取當前系統日期的月份，以兩位數字表示，如03。
    第八行：取當前系統日期的日期，以兩位數字表示，如12。
    第九行：定義備份文件名，最終的文件名以bugdb_20100312.sql形式存在，即文件名加日期的形式。
    第十行：執行備份。

再來解釋一下mysqldump的語法格式。格式為：

    mysqldump.exe "要備份的數據庫名" -u(接用戶名) -p(接密碼) > "備份文件存放的路徑及文件名"

其中“>”的作用是輸出重定向，即把mysqldump.exe備份的數據輸出到一個文件裡並保存。

將以上腳本復制到一個文本文件裡，並另存為*.bat，如backup.bat的批處理文件，接下來會要用到該文件，我把它存在D:/scripts/backup_bugdb.bat。

打開“控制面板”裡的“任務計劃”，新建一個計劃任務：

　　

在“運行”裡面通過浏覽按鈕找到剛保存的backup.bat批處理文件，在“計劃”選項卡和“設置”選項卡裡根據自己的實際需要進行設定，設置完畢後點“確定”進行保存該任務。接下來系統會在指定的時間裡周期性地運行此腳本，從而達到自動備份數據庫的目的。

　　

另外附上還原數據庫的命令

 代碼如下 復制代碼

    D:htmlwampmysqlbinmysql.exe -uroot -p123456
    --default-character-set=utf8 bugdb < F:bugdb_20100312.sql