mysql備份dump文件（mysqldump備份原理）

本文目錄一覽：

• 1、mysql中的dump命令用法
• 2、MySQL 常用備份工具流程解析
• 3、用ssh遠程執行mysql備份，我怎麼得到dump文件的名字
• 4、mysql數據庫如何導入.dump文件
• 5、MySQL dump 備份出的SQL文件中文全部顯示亂碼，請教如何導入新數據庫中

mysql中的dump命令用法

用到MySQL的mysqldump工具，基本用法是：

shell mysqldump [OPTIONS] database [tables]

如果你不給定任何錶，整個數據庫將被導出。

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

注意，如果你運行mysqldump沒有–quick或–opt選項，mysqldump將在導出結果前裝載整個結果集到內存中，如果你正在導出一個大的數據庫，這將可能是一個問題。

1.1、mysqldump支持下列選項：

–add-locks

在每個表導出之前增加LOCK TABLES並且之後UNLOCK TABLE。(為了使得更快地插入到MySQL)。

–add-drop-table

在每個create語句之前增加一個drop table。

–allow-keywords

允許創建是關鍵詞的列名字。這由表名前綴於每個列名做到。

-c, –complete-insert

使用完整的insert語句(用列名字)。

-C, –compress

如果客戶和服務器均支持壓縮，壓縮兩者間所有的信息。

–delayed

用INSERT DELAYED命令插入行。

-e, –extended-insert

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

-#, –debug[=option_string]

跟蹤程序的使用(為了調試)。

–help

顯示一條幫助消息並且退出。

LOAD DATA INFILE

–fields-terminated-by=…

–fields-enclosed-by=…

–fields-optionally-enclosed-by=…

–fields-escaped-by=…

–fields-terminated-by=…

這些選擇與-T選擇一起使用，並且有相應的LOAD DATA INFILE子句相同的含義。

LOAD DATA INFILE語法。

-F, –flush-logs

在開始導出前，洗掉在MySQL服務器中的日誌文件。

-f, –force,

即使我們在一個表導出期間得到一個SQL錯誤，繼續。

-h, –host=..

從命名的主機上的MySQL服務器導出數據。缺省主機是localhost。

-l, –lock-tables.

為開始導出鎖定所有表。

-t, –no-create-info

不寫入表創建信息(CREATE TABLE語句）

-d, –no-data

不寫入表的任何行信息。如果你只想得到一個表的結構的導出，這是很有用的！

–opt

同–quick –add-drop-table –add-locks –extended-insert –lock-tables。

應該給你為讀入一個MySQL服務器的儘可能最快的導出。

-pyour_pass, –password[=your_pass]

與服務器連接時使用的口令。如果你不指定“=your_pass”部分，mysqldump需要來自終端的口令。

-P port_num, –port=port_num

與一台主機連接時使用的TCP/IP端口號。（這用於連接到localhost以外的主機，因為它使用 Unix套接字。）

-q, –quick

不緩衝查詢，直接導出至stdout；使用mysql_use_result()做它。

-S /path/to/socket, –socket=/path/to/socket

與localhost連接時（它是缺省主機)使用的套接字文件。

-T, –tab=path-to-some-directory

對於每個給定的表，創建一個 table_name.sql文件，它包含SQL CREATE 命令，和一個table_name.txt文件，它包含數據。 注意：這隻有在mysqldump運行在mysqld守護進程運行的同一台機器上的時候才工作。.txt文件的格式根據–fields-xxx和 –lines–xxx選項來定。

-u user_name, –user=user_name

與服務器連接時，MySQL使用的用戶名。缺省值是你的Unix登錄名。

-O var=option, –set-variable var=option設置一個變量的值。可能的變量被列在下面。

-v, –verbose

冗長模式。打印出程序所做的更多的信息。

-V, –version

打印版本信息並且退出。

-w, –where=””where-condition””

只導出被選擇了的記錄；注意引號是強制的！

“–where=user=””jimf””” “-wuserid1” “-wuserid1”

1.2、最常見的mysqldump的一個備份：

雖然mysqldump支持的命令有很多，對於大多數人而言，我們只需要使用-opt這個命令就已經足夠了，為你的數據庫做一個完整的備份：

mysqldump –opt database backup-file.sql

但是它對用來自於一個數據庫的信息充實另外一個MySQL數據庫也是有用的：

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

1.3、使用mysqldump導出的文件恢複數據庫

由於mysqldump導出的是完整的SQL語句，所以用mysql客戶程序很容易就能把數據導入了：

shell mysqladmin create target_db_name

shell mysql target_db_name backup-file.sql

就是

shell mysql 庫名 文件名

二、使用mysqldump定時備份數據庫的腳本

2.1、備份腳本

使用腳本每天定期執行數據庫備份操作，對每個使用mysql數據庫的人來說都很有必要，這樣的腳本網上有很多，這裡摘抄一個朋友的腳本 dbbackup：

這個腳本每天最多只執行一次，而且只保留最近五天的備份在服務器上。

dbbackup代碼:

#!/bin/bash

#This is a ShellScript For Auto DB Backup

#Powered by aspbiz

#2004-09

#Setting

#設置數據庫名，數據庫登錄名，密碼，備份路徑，日誌路徑，數據文件位置，以及備份方式

#默認情況下備份方式是tar，還可以是mysqldump,mysqldotcopy

#默認情況下，用root(空)登錄mysql數據庫，備份至/root/dbxxxxx.tgz

DBName=mysql

DBUser=root

DBPasswd=

BackupPath=/root/

LogFile=/root/db.log

DBPath=/var/lib/mysql/

#BackupMethod=mysqldump

#BackupMethod=mysqlhotcopy

#BackupMethod=tar

#Setting End

NewFile=”$BackupPath”db$(date +%y%m%d).tgz

DumpFile=”$BackupPath”db$(date +%y%m%d)

OldFile=”$BackupPath”db$(date +%y%m%d –date=””5 days ago””).tgz

echo “——————————————-” $LogFile

echo $(date +”%y-%m-%d %H:%M:%S”) $LogFile

echo “————————–” $LogFile

#Delete Old File

if [ -f $OldFile ]

then

rm -f $OldFile $LogFile 21

echo “[$OldFile]Delete Old File Success!” $LogFile

else

echo “[$OldFile]No Old Backup File!” $LogFile

fi

if [ -f $NewFile ]

then

echo “[$NewFile]The Backup File is exists,Can””t Backup!” $LogFile

else

case $BackupMethod in

mysqldump)

if [ -z $DBPasswd ]

then

mysqldump -u $DBUser –opt $DBName $DumpFile

else

mysqldump -u $DBUser -p$DBPasswd –opt $DBName $DumpFile

fi

tar czvf $NewFile $DumpFile $LogFile 21

echo “[$NewFile]Backup Success!” $LogFile

rm -rf $DumpFile

;;

mysqlhotcopy)

rm -rf $DumpFile

mkdir $DumpFile

if [ -z $DBPasswd ]

then

mysqlhotcopy -u $DBUser $DBName $DumpFile $LogFile 21

else

mysqlhotcopy -u $DBUser -p $DBPasswd $DBName $DumpFile $LogFile 21

fi

tar czvf $NewFile $DumpFile $LogFile 21

echo “[$NewFile]Backup Success!” $LogFile

rm -rf $DumpFile

;;

*)

/etc/init.d/mysqld stop /dev/null 21

tar czvf $NewFile $DBPath$DBName $LogFile 21

/etc/init.d/mysqld start /dev/null 21

echo “[$NewFile]Backup Success!” $LogFile

;;

esac

fi

echo “——————————————-” $LogFile

2.2、放入crontab定期執行dbbackup

假定dbbackup在/root目錄下。我們通過使用crontab命令，設置每天0點10分執行/root/dbbakup腳本。

1、 使用 crontab –e編輯crontab

2、 在crontab中加入：

#back for jabber database

10 0 * * * /root/dbbackup

MySQL 常用備份工具流程解析

下面我們就看一下常見的備份工具，以及目前最流行的 Percona XtraBackup 的備份流程。

MySQL 常見的備份工具主要分為三種：

這裡先說一下 binlog 備份，它只是把 binlog 又複製了一份，並且需要在邏輯備份或者物理備份的基礎上才能進行數據恢復，無法單獨進行數據恢復。

mysqldump 備份出的文件就是 sql 文件，其核心就是對每個表執行 select ，然後轉化成相應的 insert 語句。mysqldump 的備份流程大致如下：

從上面可以看出在 mysqldump 備份期間，備份到某個數據庫時，該數據庫下的表都會處於只讀狀態，無法對錶進行任何變更，直到該庫下的表備份完畢，這對於線上環境一般是無法接受的。若是指定了–master-data或者 –dump-slave 則會在備份開始時加全局讀鎖（FLUSH TABLES WITH READ LOCK），直到備份結束。當然我們可以選一個從庫進行備份，這樣就不會影響線上業務。另外使用 mysqldump 備份還有一個最大的好處，因為備份出來的是 sql 語句，所以它支持跨平台和跨版本的數據遷移或者恢復，這是物理備份無法做到的。

但是也正是因為 mysqldump 備份出來的是 sql 語句，在使用時要更加註意，否則可能會釀成大禍。例如，使用 mysqldump 常見的問題有：

所以使用 mysqldump 時一定要了解各個選項的作用，以及確認備份出來的 sql 文件里會有什麼操作，會對現有數據造成什麼影響。

Mydumper 原理與 Mysqldump 原理類似，最大的區別是引入了多線程備份，每個備份線程備份一部分表，當然並發粒度可以到行級，達到多線程備份的目的。這裡不再單獨介紹。

Percona XtraBackup 是 Percona 公司開發的一個用於 MySQL 數據庫物理熱備的備份工具，是基於 InnoDB 的崩潰恢復功能來實現的。它的基本工作原理如下：

Percona XtraBackup 在進行恢復時會應用拷貝的 redo log ，應用已提交的事務，回滾未提交的事物，將數據庫恢復到一致性狀態。因為 Percona XtraBackup 備份出來的是物理文件，所以在使用備份出的文件進行恢復或者遷移時，不會像 mysqldump 那樣會存在很多問題。

使用 XtraBackup 備份時根據備份參數設置不同，對數據庫的變更會造成不同程度的影響，具體影響會在下文分析。

通過對比發現，XtraBackup 具有對數據庫影響小，且能快速恢復的優點，在日常備份中是首選；mysqldump 使用相對更加靈活，但是使用是要注意對數據庫原有數據的影響。

備份策略主要有：全量備份和增量備份，再加上 binlog 備份。

目前去哪兒網數據庫備份主要採用 XtraBackup 全量備份 +binlog 備份。數據庫的重要級別不同，全量備份的頻率不同。備份程序主要架構如下：

說明：

Percona XtraBackup 是目前備份 MySQL 使用最廣泛的工具。在備份過程中，數據庫可以進行正常的讀寫或者其他變更操作，但是偶爾也會遇見備份引起的元數據鎖，或提交事務時發現被 binlog lock 阻塞等情況。下面我們就看一下 Percona XtraBackup 的備份流程和加鎖時機。

說明：以下對 Percona XtraBackup 的分析都是基於 2.4.23 的版本，其他版本會略有差別，但是關鍵步驟基本相同。

XtraBackup 在備份開始時，會創建一個後台線程，專門用於拷貝數據庫的 redo log 。首先 XtraBackup 會掃描每組 redo log 的頭部，找出當前的 checkpoint lsn ，然後從該 lsn 後順序拷貝所有的 redo log ，包括後續新產生的 redo log 。該線程會一直持續到將非事務表完全拷貝完成，才會安全退出。備份日誌輸出中會記錄拷貝開始時的 checkpoint lsn 。日誌輸出如下：

在拷貝ibd文件之前，會先掃描數據庫的數據文件目錄，獲取ibdata1，undo tablespaces及所有的ibd文件列表，並會記錄相應的 space id，因為在恢復時需要這些 space id來找到對應 doublewrite buffer里頁面的內容，以及對應的redo log條目。然後開始循環拷貝ibdata1，undo tablespaces及所有的ibd文件。

這裡可通過設置–parallel進行多線程備份，提高物理文件的拷貝效率。不設置則默認為1。

在所有ibd文件拷貝完成後，XtraBackup開始備份非ibd文件。這一部分的邏輯比較複雜，因為備份非ibd文件前需要加鎖，具體是否會加鎖主要受到–no-lock 參數設置的影響。

若是設置了–no-lock為TRUE，則不會使用”FLUSH TABLES WITH READ LOCK”去加全局讀鎖，但是若備份過程中對non-InnoDB表執行了DDL或者DML操作， 這會導致備份的不一致，恢復出來的數據就會有問題。所以是不建議將–no-lock為TRUE，默認值是FALSE，也就是在不指定該選項的情況下會在備份非ibd文件前加全局讀鎖。

下面我們結合源碼來看看判斷是否加全局鎖這部分的具體流程邏輯：

流程圖如下：

總結來看：

1）若–no-lock為FALSE（默認值），則先施加全局讀鎖，然後再進行拷貝文件，另外若 –safe-slave-backup 設置為TRUE ，則會在加全局鎖之前關閉SQL_THREAD線程；

2）若–no-lock為TRUE，則不會施加鎖，直接進行拷貝文件。

加鎖的邏輯主要由lock_tables_maybe實現,先看一下lock_tables_maybe源代碼，如下：

lock_tables_maybe 函數簡化處理流程如下：

1）若備份實例上已經加鎖（ LOCK TABLES FOR BACKUP / FLUSH TABLES WITH READ LOCK）或者設置lock-ddl-per-table 則直接返回；

2）若支持備份鎖，則執行LOCK TABLES FOR BACKUP；

3）若不支持備份鎖，則執行 FLUSH TABLES WITH READ LOCK。根據相應選項設置，在執行該操作前會判斷是否有執行中的DDL/DML，以及等待超時時間，是否kill 對應的未結束的事務等。

從上文中我們還看到一個參數–safe-slave-backup ，該參數的主要作用是：

若是在從庫執行的備份操作時設置了該參數，可以防止因從庫同步主庫操作，而導致XtraBackup長時間請求不到鎖而造成備份失敗。

若是設置了 –safe-slave-backup 為TRUE，那麼會執行”STOP SLAVE SQL_THREAD”，並等待Slave_open_temp_tables 為零才開始拷貝非 ibd 文件，Slave_open_temp_tables 為零說明SQL thread執行的事務都已經完成，這樣就能保證備份的一致性。並且此時也不會有在執行的事務阻塞 XtraBackup 施加全局鎖。

備份完非 ibd 文件後，將會備份 slave 和 binlog 信息。

mysql-bin.000004 2004 6b7bda9f-15f0-11ec-ba14-fa163ea367a4:1-83,9841546e-15f0-11ec-9557-fa163e736db4:1

需要注意，在支持備份鎖的實例上備份，指定了 –slave-info 或–binlog-info 均會先施加 binlog 備份鎖（ LOCK BINLOG FOR BACKUP），這會阻塞任何會更改 binlog 位點的操作。

備份完數據庫的所有文件和binlog等相關信息，備份工作就基本完成了，之後主要執行的操作如下：

1）執行”FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS”,將所有的redo log刷盤；

2）停止redo log複製線程；

3）釋放全局讀鎖（備份鎖），binlog鎖；

4）開啟SQL_THREAD；

5）拷貝ib_buffer_pool和ib_lru_dump文件；

6）生成配置文件backup-my.cnf；

7）打印備份信息到xtrabackup_info文件，這些信息主要包含備份時使用的參數信息，備份起止時間，binlog位點信息，以及將會回到的lsn點。

下面是xtrabackup_info記錄的部分內容：

加鎖對應的函數是 mdl_lock_tables ，釋放鎖對應的函數是 mdl_unlock_all，主要是執行COMMIT，結束 mdl_lock_tables 中開啟的顯式事務，來釋放MDL鎖。mdl_lock_tables 流程如下：

上面參數–lock-ddl和–lock-ddl-per-table是在 Percona XtraBackup 2.4.8 之後添加的，因為 MySQL 5.7 新增了一個叫做 Sorted Index Builds 的功能，這會導致某些 DDL 操作不記錄重做日誌而導致備份失敗。使用–lock-ddl或–lock-ddl-per-table 就會在備份開始時施加鎖，阻止 DDL 操作。

另外，若備份時指定了–lock-ddl或–lock-ddl-per-table，則在備份非 ibd 文件時就不是再有加鎖操作。

注意：LOCK TABLES FOR BACKUP和LOCK BINLOG FOR BACKUP 語句只有在支持備份鎖的實例上才會執行，Percona Server for MySQL已經在 5.6.16-64.0 版本開始支持這種更加輕量的備份鎖。

Q1: 使用 XtraBackup 備份的文件進行恢復時，恢復到哪個時間點？ A1：恢復到執行 LOCK BINLOG FOR BACKUP 或 FLUSH TABLES WITH READ LOCK 的時間點，因為這時任何改變 binlog 位點的操作都會被阻塞，redo log和binlog 是一致的。

Q2: 在開啟 binlog 的情況下，MySQL 的奔潰恢復是同時依賴 binlog 和 redo log 這兩種日誌的，為什麼XtraBackup 不用備份binlog？

A2：因為在備份中有執行LOCK BINLOG FOR BACKUP/FLUSH TABLES WITH READ LOCK，阻止了任何改變binlog位點的操作，這樣只需要根據redo log將有commit log 的事務提交，沒有commit log的事務進行回滾即可。

Q3: 使用Percona XtraBackup備份完成後redo的位點是和binlog是一樣還是比binlog多一些？

A3：通過分析備份流程可以發現備份 binlog 位點信息（加binlog鎖）是發生在停止 redo 拷貝線程前，而釋放鎖是在停止 redo 拷貝線之後，所以 redo log 會多一些。鎖住了 binlog 保證了在該 binlog 位點前已經提交的事務的 redo log 都有 commit log 的信息，未提交的事物也就沒有對應的 commit log 的信息，即便在鎖住 binlog 後有 Innodb 表新的 DML 產生的 redo log ，但是事務無法提交，也就沒有 commit log 的信息的，最後在回放的過程中對沒有 commit log 的事務進行回滾就可以了。

Q4：Percona XtraBackup什麼時候會加鎖，以及影響加鎖時間長度的因素有哪些？

A4：上面進行了分析，加鎖操作只在備份非 ibd 文件時執行，加鎖時長主要和非事務表的數量和大小有關，非事務表的數量越多，體積越大，拷貝文件所用的時間越長，那麼加鎖時間也就越長。也會和 redo log 生成的速度有關，只是 redo log 刷盤受到多個因素的影響，未及時刷盤的 redo log 一般很小。

Q5：Percona XtraBackup 和mysqldump選擇哪個更好？

A5：通過上面的的解析，若是整個實例備份，首先選擇 Percona XtraBackup ，因為對數據庫的影響最小。若只是備份某個庫表，這個就要視數據量而定，若數據量不大可以使用 mysqldump 。注意，對數據庫做備份時最好選擇業務連接最少的從庫，因為備份也會消耗一定的資源，避免影響業務。

用ssh遠程執行mysql備份，我怎麼得到dump文件的名字

　1. 打開BE控制台，點擊左上角BE圖標，在下拉菜單中點擊安裝和授權許可 – 在其他服務器上安裝代理和Backup Exec服務器。

2. 在打開的安裝嚮導中，點擊添加 – 添加單個計算機。

3. 選擇要安裝的產品 Agent for Windows。

4. 安裝屏幕提示，輸入目標服務器的名稱，以及具有其本地管理員權限的用戶名、密碼和域。

5. 屏幕顯示默認的安裝路徑（C:\Program Files\Symantec\Backup Exec\RAWS），可以根據需要更改安裝路徑。

注意：該路徑不可以是可移動驅動器和網絡驅動器。

6. 勾選“允許Agent for Windows向Backup Exec 服務器發布遠程計算機的IP地址和名稱以及Agent for Windows的版本”，並添加介質服務器的名稱和IP地址，然後點擊“下一步”。

7. 安裝嚮導執行安裝前的驗證。

8. 驗證通過後，點擊下一步。

9.顯示安裝摘要，確認無誤後，點擊安裝。

10. 屏幕顯示安裝過程及進度。

11.安裝成功後，提示需要重新啟動計算機，安裝過程才全部完成，點擊下一步。

12.點擊完成，結束安裝嚮導。

13.安排合適時間，重新啟動目標服務器，完成全部安裝過程。

mysql數據庫如何導入.dump文件

如果是，可以用 mysqldump _u用戶名 _P 密碼 數據庫名 [表名1 表名2..]存放路徑(d:/bk.dump) 比如: 把temp數據庫備份到 d:\temp.dump 就可以使用下面的命令：mysqldump –u root –proot temp d:\temp.dump

MySQL dump 備份出的SQL文件中文全部顯示亂碼，請教如何導入新數據庫中

你可以直接把mysql安裝包下面的data目錄下所要到出的數據複製份，然後把mysql服務關了，把data數據放在新數據庫中的data目錄下，開啟服務就OK！（這種方式mysql版本的一致，如果不一致可以吧data目錄下的ibdata1文件一併拷貝過去）