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 ******

所有可用的Database Backup主機上使用以下命令：

代碼如下	複製代碼
$ 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