MySQL怎么備份和恢復文件

本篇內容介紹了“MySQL怎么備份和恢復文件”的有關知識，在實際案例的操作過程中，不少人都會遇到這樣的困境，接下來就讓小編帶領大家學習一下如何處理這些情況吧！希望大家仔細閱讀，能夠學有所成！

MySQL備份與恢復

數據庫分邏輯備份\物理備份；物理備份又分冷備和熱備

A.直接拷貝數據文件到安全地方進行保存

B.使用MYSQLHOSTCOPY備分數據

C.使用MYSQLDUMP備份數據

D.使用MYSQL的同步復制，實現數據實時數據同步備份

1） 邏輯備份

常用的邏輯備份主要就是兩種：

l  一種是將數據生成為可以完全重現當前數據庫中的數據的insert語句

l  一種是將數據通過邏輯備份軟件，將數據庫表的數據以特定分隔符進行分割后記錄在文本中。

對于第一種生成insert語句來說我們可以直接使用mysql自帶的工具mysqldump來完成。這種方式不好之處在于可能導致數據的不一致，或是不完整。

解決辦法：一個是通過在數據庫系統中加入寫入鎖，只提供數據庫的查詢服務；第二種是對于支持事務的存儲引擎來說，INNODB BDB可以通過將整個備份過程控制在一個事務中，來達到備份數據的一致性和完整性；并且可以不用影響到數據庫的正常運行。

如何保證數據備份的一致性？

要想保證數據的一致性可以通過以下兩種方法做到：

第一、同一時刻取出所有數據

對于事務支持的存儲引擎，如Innodb或者BDB 等，可以通過控制將整個備份過程在同一個事務中，使用“--single-transaction”選項。

例如：

# mysqldump --single-transaction test > test_backup.sql

第二、數據庫中的數據處于靜止狀態

通過鎖表參數

--lock-tables 每次鎖定一個數據庫的表，此參數是默認為true(見上面備份內容實例)；

--lock-all-tables 一次鎖定所有的表，適用于dump的表分別處于各個不同的數據庫中的情況

恢復方式則是通過mysql<backup.sql直接運行的。

例如：mysql -uroot -p mydb1 <bkmydb1.dt

第二種直接生成數據格式。占用的空間小，數據格式清晰。可是沒有數據庫結構的腳本。不容易控制

實現方式：通過select******* to outfile from***命令來實現。

例如：mysql> select * into outfile '/tmp/tb1.txt'

    -> fields terminated by ','

    -> optionally enclosed by '"'

    -> lines terminated by '\n' --默認

    -> from tb1 limit 50;

恢復方式就是通過，load data infile和mysqlimport命令來做。

這一過程相當的復雜需要實時的進行恢復測試，保證備份數據是可用的。

2） 物理備份

數據庫的物理備份，主要的對象是數據庫的物理數據文件，日志文件以及配置文件等。物理數據文件有哪些呢？

一是日志文件6大類：錯誤日志error Log、二進制日志binary Log、更新日志 update log（5.0后被bin log代替）、查詢日志 query log、慢查詢日志 slow query log、innodb的redo日志。

二是數據文件：對于myisam來說的話，.frm表結構信息、.myd數據信息、.myi數據的索引信息。對于Innodb來說的話.ibd文件（獨享表空間）和.ibdata（共享表空間）文件。

三是replication文件：master.info存儲在slave端的數據目錄下，存放了slave和master的相關信息，relay log和 relay log index主要存儲了I/O進程從Master端讀取到的binary log信息，然后由slave端的SQL線程從該binary log中讀取解析過的日志信息，轉化成master所能執行的query語句。index則是存放binarylog的路徑也就是目錄文件。

四是系統文件：如my.cnf、pid文件是mysqld應用程序中的一個進程文件存放自己的進程id，還有就是socket文件它只有在linux下才有的，可以不通過tcp/ip網絡協議直接連接mysql

l  如果是做冷備的話，直接復制所有的數據文件和日志文件到備份集存放的地方，

l  熱備的方法針對不多的數據庫有不同的方案

對于MYISAM存儲引擎來說，做法就是給數據庫表加鎖來阻止寫操作，可以直接復制物理文件，或者也可以通過mysql專門的mysqlhotcopy（原理就是現鎖住表，然后進行操作）程序來完成相應的備份任務。

flush tables with read lock

cp -R test /tmp/backup/test

unlock tables;

innodb數據庫引擎來說，有一款商業軟件ibbackup，在線物理備份功能。還有一款開源的工具xtrabackup；

如果在備份過程中，把INNODB數據文件備份完成后，會鎖住整個庫，并開始復制MYISAM等非事務引擎的數據和.frm；所以如果你擁有比較多的MYISAM表，鎖庫的時候會持續很長。如果是在主庫上運行，千萬注意。

同樣也只能通過xtrabackup進行增量的備份，其實這一工具只是備份innobd的日志信息。

4 xtrabackup/ibbackup

xtrabackup --backup --datadir=/var/lib/mysql/ --target-

dir=/data/backups/mysql/

xtrabackup --backup  --defaults-file=/etc/my.cnf --target-

dir=/data/backups/mysql/

物理備份就是備份數據文件了，比較形象點就是cp下數據文件，但真正備份的時候自然不是的cp這么簡單。

（1）使用 xtrabackup 工具

是一個用來備份 MySQL數據庫的開源工具。

主要特點：

<1>. 在線熱備份。可以備份innodb和myisam。innodb主要應用recovery原理。myisam直接拷貝文件。

<2>. 支持流備份。可以備份到disk，tape和remote host。–stream=tar ./ | ssh user@remotehost cat “>” /backup/dir/

<3>. 支持增量備份。可以利用lsn和基礎備份目錄來進行增量備份。

<4>. 支持記錄slave上的master log和master position信息。

<5>. 支持多個進程同時熱備份，xtrabackup的穩定性還是挺好的。

（2）LVM

特點：熱備、支持所有基于本地磁盤的存儲引擎、快速備份、低開銷、容易保持完整性、快速恢復等。

（3）cp + tar

使用直接拷貝數據庫文件的方式進行打包備份，需要注意的是執行步驟：鎖表、備份、解表。

恢復也很簡單，直接拷貝到之前的數據庫文件的存放目錄即可。

注意：對于Innodb引擎的表來說，還需要備份日志文件，即ib_logfile*文件。因為當Innodb表損壞時，就可以依靠這些日志文件來恢復。

（4）mysqlhotcopy

mysqlhotcopy是一個perl程序，是lock tables、flush tables 和cp或scp來快速備份數據庫。

它是備份數據庫或單個表的最快的途徑，但它只能運行在數據庫文件（包括數據表文件、數據文件、索引文件）所在的機器上。

mysqlhotcopy只能用于備份MyISAM。

（5）使用mysql主從復制

mysql的復制是指將主數據庫的DDL和DML操作通過二進制文件（bin-log）傳送到從服務器上，然后在從服務器上對這些日志做重新執行的操作，從而使得從服務器和主服務器保持數據的同步。

3） mysql的備份方式

1.mysqldump

效率比較低，備份和還原的速度都很慢,任何數據插入和更新操作都會被掛起

2.mysqlhotcopy

mysqlhotcopy 是專門針對myisam 數據表進行備份,備份的過程中，任何數據插入和更新操作都會被掛起

3.準備一臺從服務器,專門做備份（master-slave方式）

4.xtrabackup 是 percona 的一個開源項目，可熱備份innodb ,XtraDB,MyISAM(會鎖表)

Xtrabackup有兩個主要的工具：xtrabackup、innobackupex

xtrabackup只能備份InnoDB和XtraDB兩種數據表，而不能備份MyISAM數據表

innobackupex-1.5.1則封裝了xtrabackup，是一個腳本封裝，所以能同時備份處理innodb和myisam，但在處理myisam時需要加一個讀鎖

?xtra備份原理

記住開始時候的LSN號，然后開始拷備文件，同時運行一個后臺進程監視重做日志，并將變化拷備下來到xtrabackup_logfile。

innobackupex可以備份myisam表和frm文件。當xtrabackup結束后，執行flush tables with read lock，避免數據更改，然后刷新所有myisam表到磁盤。拷備結束后，釋放鎖。

4） mysqlbinlog

mysqlbinlog也是一個恢復工具，是基于時間點來處理二進制文件的

?備份：直接拷備

?還原：

ü時間點還原：mysqlbinlog --stop-date="2005-04-20 9:59:59"

/var/log/mysql/bin.123456 | mysql -u root –pmypwd

mysqlbinlog --start-date="2005-04-20 10:01:00"

/var/log/mysql/bin.123456| mysql -u root –pmypwd

?日志點還原：

mysqlbinlog --stop-position="368312" /var/log/mysql/bin.123456 \

| mysql -u root -pmypwd

mysqlbinlog --start-position="368315" /var/log/mysql/bin.123456 \

| mysql -u root -pmypwd \

LVM是 Logical Volume Manager（邏輯卷管理）的簡寫，它是Linux環境下對磁盤分區進行管理的一種機制。

LSN定義：

5） 日志序號

日志序號 (LSN) 標識特定日志文件記錄在日志文件中的位置。

LSN 由 DB2? 產品中的許多組件用來維護數據庫一致性和完整性。除其他作用外，LSN 還對于分區數據庫環境中的落實和回滾操作、崩潰和前滾恢復以及數據庫操作同步起非常重要的作用。

日志文件中 LSN 的增長率與數據庫活動直接相關聯。也就是說，隨著事務發生并且條目被寫入日志文件，LSN 會不斷增大。數據庫中的活動越多，LSN 增長得越快。

日志序號的上限

在 DB2 V9.5 和較早版本中，日志序號 (LSN) 是 6 字節數字。從 FP3 開始，LSN 的范圍在 0x0000 0000 0000（首次創建數據庫時）到 0xFFFF 0000 0000（約 256 太字節）之間。在 FP3 之前，上限為 0xFFFF FFFF FFFF。隨著記錄被添加到日志文件中，LSN 在數據庫生命期不斷增長。

“MySQL怎么備份和恢復文件”的內容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業相關的知識可以關注億速云網站，小編將為大家輸出更多高質量的實用文章！