mysql資料庫備份守則和方法（mysql資料庫備份守則和方法有哪些）

本文目錄一覽：

• 1、備份mysql數據
• 2、mysql資料庫備份和還原
• 3、MYSQL資料庫備份都備份什麼，除了數據表還需要什麼，怎麼進行備份

備份mysql數據

其實你的這個問題是mysql中的一個核心問題，既mysql數據的備份和恢復

你可以使用三種方式

1.使用sql語句導入導出

2.使用mysqldump 和mysqlimport 工具

3.直接copy 數據文件 既冷備份

你說說的詳細，就給積分，那我就說詳細些

一.使用sql語句完成mysql的備份和恢復

你可以使用SELECT INTO OUTFILE語句備份數據，並用LOAD DATA INFILE語句恢複數據。這種方法只能導出數據的內容，不包括表的結構，如果表的結構文件損壞，你必須要先恢復原來的表的結構。

語法：

SELECT * INTO {OUTFILE | DUMPFILE} ‘file_name’ FROM tbl_name

LOAD DATA [LOW_PRIORITY] [LOCAL] INFILE ‘file_name.txt’ [REPLACE | IGNORE]

INTO TABLE tbl_name

SELECT … INTO OUTFILE ‘file_name’格式的SELECT語句將選擇的行寫入一個文件。文件在伺服器主機上被創建，並且不能是已經存在的(不管別的，這可阻止資料庫表和文件例如「/etc/passwd」被破壞)。SELECT … INTO OUTFILE是LOAD DATA INFILE逆操作。

LOAD DATA INFILE語句從一個文本文件中以很高的速度讀入一個表中。如果指定LOCAL關鍵詞，從客戶主機讀文件。如果LOCAL沒指定，文件必須位於伺服器上。(LOCAL在MySQL3.22.6或以後版本中可用。)

為了安全原因，當讀取位於伺服器上的文本文件時，文件必須處於資料庫目錄或可被所有人讀取。另外，為了對伺服器上文件使用LOAD DATA INFILE，在伺服器主機上你必須有file的許可權。使用這種SELECT INTO OUTFILE語句，在伺服器主機上你必須有FILE許可權。

為了避免重複記錄，在表中你需要一個PRIMARY KEY或UNIQUE索引。當在唯一索引值上一個新記錄與一個老記錄重複時，REPLACE關鍵詞使得老記錄用一個新記錄替代。如果你指定IGNORE，跳過有唯一索引的現有行的重複行的輸入。如果你不指定任何一個選項，當找到重複索引值時，出現一個錯誤，並且文本文件的餘下部分被忽略時。

如果你指定關鍵詞LOW_PRIORITY，LOAD DATA語句的執行被推遲到沒有其他客戶讀取表後。

使用LOCAL將比讓伺服器直接存取文件慢些，因為文件的內容必須從客戶主機傳送到伺服器主機。在另一方面，你不需要file許可權裝載本地文件。如果你使用LOCAL關鍵詞從一個本地文件裝載數據，伺服器沒有辦法在操作的當中停止文件的傳輸，因此預設的行為好像IGNORE被指定一樣。

當在伺服器主機上尋找文件時，伺服器使用下列規則：

 如果給出一個絕對路徑名，伺服器使用該路徑名。

 如果給出一個有一個或多個前置部件的相對路徑名，伺服器相對伺服器的數據目錄搜索文件。

 如果給出一個沒有前置部件的一個文件名，伺服器在當前資料庫的資料庫目錄尋找文件。

假定表tbl_name具有一個PRIMARY KEY或UNIQUE索引，備份一個數據表的過程如下：

1、鎖定數據表，避免在備份過程中，表被更新

mysqlLOCK TABLES READ tbl_name;

關於表的鎖定的詳細信息，將在下一章介紹。

2、導出數據

mysqlSELECT * INTO OUTFILE 『tbl_name.bak』 FROM tbl_name;

3、解鎖表

mysqlUNLOCK TABLES;

相應的恢復備份的數據的過程如下：

1、為表增加一個寫鎖定：

mysqlLOCK TABLES tbl_name WRITE;

2、恢複數據

mysqlLOAD DATA INFILE 『tbl_name.bak』

-REPLACE INTO TABLE tbl_name;

如果，你指定一個LOW_PRIORITY關鍵字，就不必如上要對錶鎖定，因為數據的導入將被推遲到沒有客戶讀表為止：

mysqlLOAD DATA LOW_PRIORITY INFILE 『tbl_name』

-REPLACE INTO TABLE tbl_name;

3、解鎖表

mysql-UNLOCAK TABLES;

5.3.2使用mysqlimport恢複數據

如果你僅僅恢複數據，那麼完全沒有必要在客戶機中執行SQL語句，因為你可以簡單的使用mysqlimport程序，它完全是與LOAD DATA 語句對應的，由發送一個LOAD DATA INFILE命令到伺服器來運作。執行命令mysqlimport –help，仔細查看輸出，你可以從這裡得到幫助。

shell mysqlimport [options] db_name filename …

對於在命令行上命名的每個文本文件，mysqlimport剝去文件名的擴展名並且使用它決定哪個表導入文件的內容。例如，名為「patient.txt」、「patient.text」和「patient」將全部被導入名為patient的一個表中。

常用的選項為：

-C, –compress 如果客戶和伺服器均支持壓縮，壓縮兩者之間的所有信息。

-d, –delete 在導入文本文件前倒空表格。

l, –lock-tables 在處理任何文本文件前為寫入所定所有的表。這保證所有的表在伺服器上被同步。

–low-priority，–local，–replace，–ignore分別對應LOAD DATA語句的LOW_PRIORITY，LOCAL，REPLACE，IGNORE關鍵字。

例如恢復資料庫db1中表tbl1的數據，保存數據的文件為tbl1.bak，假定你在伺服器主機上：

shellmysqlimport –lock-tables –replace db1 tbl1.bak

這樣在恢複數據之前現對錶鎖定，也可以利用–low-priority選項：

shellmysqlimport –low-priority –replace db1 tbl1.bak

如果你為遠程的伺服器恢複數據，還可以這樣：

shellmysqlimport -C –lock-tables –replace db1 tbl1.bak

當然，解壓縮要消耗CPU時間。

象其它客戶機一樣，你可能需要提供-u，-p選項以通過身分驗證，也可以在選項文件my.cnf中存儲這些參數，具體方法和其它客戶機一樣，這裡就不詳述了。

二、使用mysqldump備份數據

同mysqlimport一樣，也存在一個工具mysqldump備份數據，但是它比SQL語句多做的工作是可以在導出的文件中包括SQL語句，因此可以備份資料庫表的結構，而且可以備份一個資料庫，甚至整個資料庫系統。

mysqldump [OPTIONS] database [tables]

mysqldump [OPTIONS] –databases [OPTIONS] DB1 [DB2 DB3…]

mysqldump [OPTIONS] –all-databases [OPTIONS]

如果你不給定任何錶，整個資料庫將被傾倒。

通過執行mysqldump –help，你能得到你mysqldump的版本支持的選項表。

1、備份資料庫的方法

例如，假定你在伺服器主機上備份資料庫db_name

shell mydqldump db_name

當然，由於mysqldump預設時把輸出定位到標準輸出，你需要重定向標準輸出。例如，把資料庫備份到bd_name.bak中：

shell mydqldump db_namedb_name.bak

你可以備份多個資料庫，注意這種方法將不能指定數據表：

shell mydqldump –databases db1 db1db.bak

你也可以備份整個資料庫系統的拷貝，不過對於一個龐大的系統，這樣做沒有什麼實際的價值：

shell mydqldump –all-databasesdb.bak

雖然用mysqldump導出表的結構很有用，但是恢復大量數據時，眾多SQL語句使恢復的效率降低。你可以通過使用–tab選項，分開數據和創建表的SQL語句。

-T，–tab= 在選項指定的目錄里，創建用製表符（tab）分隔列值的數據文件和包含創建表結構的SQL語句的文件，分別用擴展名.txt和.sql表示。該選項不能與–databases或–all-databases同時使用，並且mysqldump必須運行在伺服器主機上。

例如，假設資料庫db包括表tbl1，tbl2，你準備備份它們到/var/mysqldb

shellmysqldump –tab=/var/mysqldb/ db

其效果是在目錄/var/mysqldb中生成4個文件，分別是tbl1.txt、tbl1.sql、tbl2.txt和tbl2.sql。

2、mysqldump實用程序時的身份驗證的問題

同其他客戶機一樣，你也必須提供一個MySQL資料庫帳號用來導出資料庫，如果你不是使用匿名用戶的話，可能需要手工提供參數或者使用選項文件：

如果這樣：

shellmysql -u root –pmypass db_namedb_name.sql

或者這樣在選項文件中提供參數：

[mysqldump]

user=root

password=mypass

然後執行

shellmysqldump db_namedb_name.sql

那麼一切順利，不會有任何問題，但要注意命令歷史會泄漏密碼，或者不能讓任何除你之外的用戶能夠訪問選項文件，由於資料庫伺服器也需要這個選項文件時，選項文件只能被啟動伺服器的用戶（如，mysql）擁有和訪問，以免泄密。在Unix下你還有一個解決辦法，可以在自己的用戶目錄中提供個人選項文件（~/.my.cnf）,例如，/home/some_user/.my.cnf，然後把上面的內容加入文件中，注意防止泄密。在NT系統中，你可以簡單的讓c:\my.cnf能被指定的用戶訪問。

你可能要問，為什麼這麼麻煩呢，例如，這樣使用命令行：

shellmysql -u root –p db_namedb_name.sql

或者在選項文件中加入

[mysqldump]

user=root

password

然後執行命令行：

shellmysql db_namedb_name.sql

你發現了什麼？往常熟悉的Enter password:提示並沒有出現，因為標準輸出被重定向到文件db_name.sql中了，所以看不到往常的提示符，程序在等待你輸入密碼。在重定向的情況下，再使用交互模式，就會有問題。在上面的情況下，你還可以直接輸入密碼。然後在文件db_name.sql文件的第一行看到:

Enter password:#……..

你可能說問題不大，但是mysqldump之所以把結果輸出到標準輸出，是為了重定向到其它程序的標準輸入，這樣有利於編寫腳本。例如：

用來自於一個資料庫的信息充實另外一個MySQL資料庫也是有用的：

shellmysqldump –opt database | mysql –host=remote-host -C database

如果mysqldump仍運行在提示輸入密碼的交互模式下，該命令不會成功，但是如果mysql是否運行在提示輸入密碼的交互模式下，都是可以的。

如果在選項文件中的[client]或者[mysqldump]任何一段中指定了password選項，且不提供密碼，即使，在另一段中有提供密碼的選項password=mypass,例如

[client]

user=root

password

[mysqldump]

user=admin

password=mypass

那麼mysqldump一定要你輸入admin用戶的密碼：

mysqlmysqldump db_name

即使是這樣使用命令行：

mysqlmysqldump –u root –ppass1 db

也是這樣，不過要如果-u指定的用戶的密碼。

其它使用選項文件的客戶程序也是這樣

3、有關生成SQL語句的優化控制

–add-locks 生成的SQL 語句中，在每個表數據恢復之前增加LOCK TABLES並且之後UNLOCK TABLE。(為了使得更快地插入到MySQL)。

–add-drop-table 生成的SQL 語句中，在每個create語句之前增加一個drop table。

-e, –extended-insert 使用全新多行INSERT語法。（給出更緊縮並且更快的插入語句）

下面兩個選項能夠加快備份表的速度：

-l, –lock-tables. 為開始導出數據前，讀鎖定所有涉及的表。

-q, –quick 不緩衝查詢，直接傾倒至stdout。

理論上，備份時你應該指定上訴所有選項。這樣會使命令行過於複雜，作為代替，你可以簡單的指定一個–opt選項，它會使上述所有選項有效。

例如，你將導出一個很大的資料庫：

shell mysqldump –opt db_name db_name.txt

當然，使用–tab選項時，由於不生成恢複數據的SQL語句，使用–opt時，只會加快數據導出。

4、恢復mysqldump備份的數據

由於備份文件是SQL語句的集合，所以需要在批處理模式下使用客戶機

 如果你使用mysqldump備份單個資料庫或表，即：

shellmysqldump –opt db_name db_name.sql

由於db_name.sql中不包括創建資料庫或者選取資料庫的語句，你需要指定資料庫

shellmysql db2 db_name.sql

 如果，你使用–databases或者–all-databases選項，由於導出文件中已經包含創建和選用資料庫的語句，可以直接使用，不比指定資料庫，例如：

shellmysqldump –databases db_name db_name.sql

shellmysql db_name.sql

 如果你使用–tab選項備份數據，數據恢復可能效率會高些

例如，備份資料庫db_name後在恢復：

shellmysqldump –tab=/path/to/dir –opt test

如果要恢復表的結構，可以這樣：

shellmysql /path/to/dir/tbl1.sql

…

如果要恢複數據，可以這樣

shellmysqlimport -l db /path/to/dir/tbl1.txt

…

如果是在Unix平台下使用（推薦），就更方便了：

shellls -l *.sql | mysql db

shellmysqlimport –lock-tables db /path/to/dir/*.txt

三 .用直接拷貝的方法備份恢復

根據本章前兩節的介紹，由於MySQL的資料庫和表是直接通過目錄和表文件實現的，因此直接複製文件來備份資料庫數據，對MySQL來說特別方便。而且自MySQL 3.23起MyISAM表成為預設的表的類型，這種表可以為在不同的硬體體系中共享數據提供了保證。

使用直接拷貝的方法備份時，尤其要注意表沒有被使用，你應該首先對錶進行讀鎖定。

備份一個表，需要三個文件：

對於MyISAM表：

tbl_name.frm 表的描述文件

tbl_name.MYD 表的數據文件

tbl_name.MYI 表的索引文件

對於ISAM表：

tbl_name.frm 表的描述文件

tbl_name.ISD 表的數據文件

tbl_name.ISM 表的索引文件

你直接拷貝文件從一個資料庫伺服器到另一個伺服器，對於MyISAM表，你可以從運行在不同硬體系統的伺服器之間複製文件

像你這個問題，可以把遠程機器的mysql數據目錄ftp下載到你本地的mysql目錄下，重啟mysql就可以了

mysql資料庫備份和還原

MySQL有一種非常簡單的備份方法，先將伺服器停止，然後將MySQL中的資料庫文件直接複製出來。這是最簡單，速度最快的方法。

*將伺服器停止，這樣才可以保證在複製期間資料庫的數據不會發生變化。如果在複製資料庫的過程中還有數據寫入，就會造成數據不一致。

恢復也一樣，先將伺服器停止，然後將備份的資料庫覆蓋同名的資料庫即可。

MYSQL資料庫備份都備份什麼，除了數據表還需要什麼，怎麼進行備份

備份表結構，（主鍵、索引、等） 表數據；

備份方法2種：1 如果你的開發環境是php的， 下一個phpmyadmin 的mysql web 後台管理中心可進行備份等諸多操作

2：使用CMD 進入 mysql 控制台， 使用mysqldump命令進行備份