MySQL備份的幾種常用方法與恢復(fù)步驟

1、 MySQL備份的幾種常用方法與恢復(fù)步驟 MySQL備份（Backup）與 恢復(fù)（Restore）匯總：1.mysqldump2.mysqlbackup3.mysqlhotcopy4.xtrabackup/innobackupex5.cp備份備于一切，今天匯總一下常用的幾種備份方法，以及恢復(fù)的步驟。1.mysqldump在日常工作中，我們會使用mysqldump命令創(chuàng)建sql格式的轉(zhuǎn)儲文件來備份數(shù)據(jù)庫?；蛘呶覀儼褦?shù)據(jù)導(dǎo)出后做數(shù)據(jù)遷移，主備搭建等操作。mysqldump是一個邏輯備份工具,復(fù)制原始的數(shù)據(jù)庫對象定義和表數(shù)據(jù)產(chǎn)生一組可執(zhí)行的SQL語句。 默認(rèn)情況下,生成insert語句，也能生成其它

2、分隔符的輸出或XML格式的文件。shell mysqldump arguments file_name我們簡單的來看一下日常的用法：備份所有的數(shù)據(jù)庫：shell mysqldump -all-databases dump.sql (不包含INFORMATION_SCHEMA,performance_schema,sys，如果想要導(dǎo)出的話還要結(jié)合-skip-lock-tables和-database一起用)備份指定的數(shù)據(jù)庫：shell mysqldump -databases db1 db2 db3 dump.sql當(dāng)我們只備份一個數(shù)據(jù)的時候可以省去 -databases 直接寫成：mysqld

3、ump test dump.sql 不過有一些細(xì)微的差別，如果不加的話，數(shù)據(jù)庫轉(zhuǎn)儲輸出不包含創(chuàng)建數(shù)據(jù)庫和use語句，所以可以不加這個參數(shù)直接導(dǎo)入到其它名字的數(shù)據(jù)庫里當(dāng)然我們也可以只備份某個表 ：mysqldump -user username -password=password database name table name table_name.sql了解了簡單的一些用法后我們再著重的看一下幾個參數(shù)：-master-data 獲取備份數(shù)據(jù)的Binlog位置和Binlog文件名，用于通過備份恢復(fù)的實例之間建立復(fù)制關(guān)系時使用，該參數(shù)會默認(rèn)開啟。-dump-slave 用于在slave上dum

4、p數(shù)據(jù)，建立新的slave。因為我們在使用mysqldump時會鎖表，所以大多數(shù)情況下，我們的導(dǎo)出操作一般會在只讀備庫上做，為了獲取主庫的Relay_Master_Log_File和Exec_Master_Log_Pos，需要用到這個參數(shù)，不過這個參數(shù)只有在5.7以后的才會有no-data, -d 不導(dǎo)出任何數(shù)據(jù)，只導(dǎo)出數(shù)據(jù)庫表結(jié)構(gòu)剛剛我們說過在使用mysqldump的時候會鎖表，我們來詳細(xì)的看一下它的鎖機制。我們開兩個窗口，在第一個里面執(zhí)行mysqldump -uroot -pxxxxx -master-data=2 -databases dbname /tmp/dbnamedate +%F

5、.sql然后第二個窗口登陸進(jìn)去，使用show process的命令可以看到目前dump的session正在執(zhí)行SELECT /*!40001 SQL_NO_CACHE */ * FROM table_name; 可以看到這條sql正在以no_cache的模式查詢數(shù)據(jù)。然后我們在同樣的表上執(zhí)行一下select，發(fā)現(xiàn)被阻塞了。光標(biāo)一直不返回。一般遇到這種文件，我們會想是不是有鎖呢？為了驗證我們查看一下鎖的信息，可以發(fā)現(xiàn)dump的進(jìn)程實際上是加了鎖的。我們把具體的general_log打開，然后看一下當(dāng)時的操作：4101044 Query FLUSH /*!40101 LOCAL */ TABLES

6、4101044 Query FLUSH TABLES WITH READ LOCK （關(guān)閉所有打開的表，同時對于所有數(shù)據(jù)庫中的表都加一個讀鎖，直到顯示地執(zhí)行unlock tables，該操作常常用于數(shù)據(jù)備份的時候。）4101044 Query SHOW MASTER STATUS（這是因為我用了-master-data=2）所以這個時候表就會被鎖住。如果我不加-master-data參數(shù)(mysqldump -uroot -pxx -databases db /tmp/dbnamedate +%F.sql) mysql會顯示的對每一張要備份的表執(zhí)行LOCK TABLEStable_name1R

7、EAD,LOCK TABLEStable_name2READ并且也不會有讀的阻塞。那有沒有不鎖的方法？其實也是有的，就是使用-single-transaction把備份的操作放在一個事務(wù)里去進(jìn)行帶上-single-transaction參數(shù)的mysqldump備份過程：如果是5.6版本的mysql在備份之間同樣的先FLUSH TABLES WITH READ LOCK，然后設(shè)置事務(wù)級別SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ，然后開啟一個事務(wù)START TRANSACTION進(jìn)行備份，這個時候備份的過程就很意思，它先創(chuàng)建了一

8、個savepoint，然后把數(shù)據(jù)庫里的表依次的進(jìn)行備份，備份完成了之后又回滾到了之前的savepoint，來保證數(shù)據(jù)的一致性如果是5.7版本的mysql備份前的操作相同，只是沒有了savepoint不過不管是哪個版本，只有InnoDB表是在一個一致性的狀態(tài)。其它的任何MyISAM表或內(nèi)存表是沒有用的。mysqldump的優(yōu)勢是可以查看或者編輯十分方便，它也可以靈活性的恢復(fù)之前的數(shù)據(jù)。它也不關(guān)心底層的存儲引擎，既適用于支持事務(wù)的，也適用于不支持事務(wù)的表。不過它不能作為一個快速備份大量的數(shù)據(jù)或可伸縮的解決方案。如果數(shù)據(jù)庫過大,即使備份步驟需要的時間不算太久,但有可能恢復(fù)數(shù)據(jù)的速度也會非常慢,因為它

9、涉及的SQL語句插入磁盤I/O,創(chuàng)建索引等等。對于大規(guī)模的備份和恢復(fù),更合適的做法是物理備份,復(fù)制其原始格式的數(shù)據(jù)文件,可以快速恢復(fù):如果你的表主要是InnoDB表,或者如果你有一個InnoDB和MyISAM表,可以考慮使用MySQL的mysqlbackup命令備份恢復(fù)操作：先看一下當(dāng)前的數(shù)據(jù)：dbadmintest 11:10:34select * from t;+-+| id |+-+| 1 |+-+1 row in set (0.00 sec)備份；mysqldump -uroot -proot1234 -master-data=1 test test.sql模擬增量操作：dbadmin

10、test 11:15:17insert into t values (2);Query OK, 1 row affected (0.00 sec)dbadmintest 11:15:36select * from t;+-+| id |+-+| 1 | 2 |+-+2 rows in set (0.00 sec)模擬誤操作：dbadmintest 11:15:41truncate table t;Query OK, 0 rows affected (0.01 sec)dbadmintest 11:16:14select * from t;Empty set (0.00 sec)摸擬恢復(fù)操作：s

11、tep 1:找到誤操作的log positiondbadmintest 11:20:57show master logs;dbadmin(none) 11:21:37show binlog events in mysql-bin.000004;查看可以看到是444step 2:恢復(fù)到備份dbadmintest 11:16:25source test.sqldbadmintest 11:17:26select * from t;+-+| id |+-+| 1 |+-+1 row in set (0.00 sec)step 3:因為我們在備份的時候使用了master-data的參數(shù)，所以可以直接看

12、到備份時候的最后位置，然后應(yīng)用中間的log。查看可以看到是187我們使用mysqlbinlog得到這一段時間的操作，其實我們也可以用這個工具得到操作后使用sed進(jìn)行undo的操作。mysqlbinlog -start-position=187 -stop-position=444 mysql-bin.000004 increment.sqldbadmintest 11:44:37source /u01/my3307/log/increment.sqldbadmintest 11:44:50select * from t;+-+| id |+-+| 1 | 2 |+-+至此數(shù)據(jù)恢復(fù)。2.mysq

13、lbackup是ORACLE公司提供的針對企業(yè)的備份軟件，全名叫做MySQL Enterprise Backup，是一個收費的軟件。 下載地址：/products/enterprise/backup.html 可以試用下載。我們簡單的來看一下這個工具的使用。查看所有的幫助：我這里只是截取了一小部分，這個幫助很長，參數(shù)很多，功能很全，是oracle官方主推的備份方式。全量備份：mysqlbackup -user=root -password=ucjmh -databases=t1 -encrypt-password=1 -with-timestamp -backup-dir=/u01/backu

14、p/ backup解釋一下參數(shù)：-databases 要備份的數(shù)據(jù)庫-with-timestamp 產(chǎn)生一個當(dāng)前時間的備份目錄。mysqlbackup這個工具要求一個空目錄才能做備份。所以這個會常用-backup-dir 備份的目錄-compress：壓縮備份 這個提供了多種壓縮方法和壓縮級別。1-9,壓縮比依次遞增backup 是備份的方式， 一共有如下幾種方式，我會在一個恢復(fù)案例里把常用的幾個都用到Backup operations: backup, backup-and-apply-log, backup-to-imageUpdate operations: apply-log, app

15、ly-incremental-backupRestore operations: copy-back, copy-back-and-apply-logValidation operation: validateSingle-file backup operations: image-to-backup-dir, backup-dir-to-image, list-image, extract其實，在大多數(shù)情況下,單個文件備份,使用backup-to-image命令創(chuàng)建,性能優(yōu)于backup。buckup這個命令只執(zhí)行一個完整的備份過程的初始階段。需要通過再次運行mysqlbackup運用app

16、ly-log 命令,使備份一致。mysqlbackup -user=root -password=ucjmh -databases=t1 -encrypt-password=1 -with-timestamp -backup-dir=/u01/backup/2017-04-28_12-49-35/ apply-log當(dāng)然你可以直接用backup-and-apply-log 不過這個時候的備份將不能用于增量了。增量備份：mysqlbackup -user=root -password=ucjmh -databases=t1 -encrypt-password=1 -with-timestamp

17、-backup-dir=/u01/backup/ -incremental -incremental-base=dir:/u01/backup/2017-04-28_12-49-35 -incremental-backup-dir=/u01/backup/incremental backup這個是基于上次的備份做的備份，當(dāng)然也可以基于某一個log position之后做。-incremental：代表增量備份；-incremental-base：上次全備的目錄；-incremental-backup-dir：增量備份的保存的目錄再多說一點關(guān)于image的備份:使用如下命令可以進(jìn)行備份mysql

18、backup -user=root -password=ucjmh -databases=t1 -encrypt-password=1 -with-timestamp -backup-dir=/u01/backup/ -backup-image=all.mbi backup-to-image備份之后可以很清楚的發(fā)現(xiàn)這個比backup要節(jié)省很多空間，把所有的文件都以二進(jìn)制的方式放在了all.mbi這個文件里，可以使用list-image來查看具體內(nèi)容。mysqlbackup -backup-image=/u01/backup/2017-04-28_14-50-17/all.mbi list-im

19、age同樣的也可以使用mysqlbackup -backup-image=/u01/backup/2017-04-28_14-50-17/all.mbi extract來解壓出來具體的內(nèi)容。因為這是一個oracle出的工具，有很深的rman的影子在，0級，1級備份，加密，異構(gòu)機器還原等特性。更多的參數(shù)可以參看online help：/doc/mysql-enterprise-backup/4.1/en/backup-commands-single-file.html恢復(fù)操作：查看當(dāng)前數(shù)據(jù)dbadmintest 11:51:32select * from t;+-+| id |+-+| 1 |+

20、-+1 row in set (0.01 sec)全量備份mysqlbackup -user=root -password=root1234 -databases=test -with-timestamp -backup-dir=/data/backup/ backup模擬增量操作：dbadmintest 11:54:04select * from t;+-+| id |+-+| 1 | 2 |+-+2 rows in set (0.00 sec)增量備份：mysqlbackup -user=root -password=root1234 -databases=test -with-times

21、tamp -backup-dir=/data/backup/ -incremental -incremental-base=dir:/data/backup/2017-04-29_11-53-20 -incremental-backup-dir=/data/backup/incremental backup模擬無備份操作：dbadmintest 11:57:10select * from t;+-+| id |+-+| 1 | 2 | 3 |+-+3 rows in set (0.00 sec)模擬誤操作：dbadmintest 11:57:17truncate table t;Query O

22、K, 0 rows affected (0.01 sec)摸擬恢復(fù)操作：step 1:找到誤操作的log positiondbadmintest 11:58:06show master logs;dbadmintest 11:58:18show binlog events in mysql-bin.000001;1333step 2:恢復(fù)全量檢測并應(yīng)用日志：mysqlbackup -backup-dir=/data/backup/2017-04-29_11-53-20 apply-logstep 3:應(yīng)用增量mysqlbackup -backup-dir=/data/backup/2017-0

23、4-29_11-53-20 -incremental-backup-dir=/data/backup/incremental/2017-04-29_11-55-54 apply-incremental-backupstep 4:物理文件復(fù)制還原mysqlbackup -backup-dir=/data/backup/2017-04-29_11-53-20 copy-back數(shù)據(jù)恢復(fù)到備份的時候：dbadmintest 12:09:49select * from t;+-+| id |+-+| 1 | 2 |+-+2 rows in set (0.00 sec)恢復(fù)完成之后，data目錄下會生成

24、backup_variables.txt的文件（其實在備份的時候就已經(jīng)有這些文件的），找到備份的時候的log position,然后從binlog恢復(fù)無備份的數(shù)據(jù)binlog_position=mysql-bin.000001:1076mysqlbinlog mysql-bin.000001 -start-position=1076 -stop-position=1333 -vv increment.sqldbadmintest 12:14:07source /u01/my3307/log/increment.sqldbadmintest 12:14:16select * from t;+-+

25、| id |+-+| 1 | 2 | 3 |+-+3 rows in set (0.00 sec)至此數(shù)據(jù)恢復(fù)。大致梳理一下操作步驟，來了解一下恢復(fù)的原理：首先檢測并應(yīng)用全備事務(wù)日志文件（這里是因為我備份的時候用的是backup而不是backup-and-apply-log），然后基于全備去應(yīng)用增量的log。這個時候如果有多次增量備份也可以（基于LSN點向后應(yīng)用）。 所有的都應(yīng)用完成之后就是一個可以直接cp的數(shù)據(jù)庫了。個人感覺這個工具比xtrabackup好用，但是xtrabackup是開源的，所以市場占有量才會大，才會更有名，更多人用吧。3.mysqlhotcopymysqlhotcopy使

26、用lock tables、flush tables和cp或scp來快速備份數(shù)據(jù)庫.它是備份數(shù)據(jù)庫或單個表最快的途徑,完全屬于物理備份,但只能用于備份MyISAM存儲引擎和ARCHIVE引擎，并且是一個服務(wù)器命令，只能運行在數(shù)據(jù)庫目錄所在的機器上.與mysqldump備份不同,mysqldump屬于邏輯備份,備份時是執(zhí)行的sql語句.使用mysqlhotcopy命令前需要要安裝相應(yīng)的軟件依賴包.因為這個功能很弱，我們只簡單的介紹一個怎么用：備份一個庫mysqlhotcopy db_name /path/to/new_directory備份一張表mysqlhotcopy db_name./tabl

27、e_name/ /path/to/new_directory更詳細(xì)的使用可以使用perldoc mysqlhotcopy查看4.xtrabackup/innobackupexPercona XtraBackup是一款基于MySQL的熱備份的開源實用程序，它可以備份5.1到5.7版本上InnoDB,XtraDB,MyISAM存儲引擎的表。Xtrabackup有兩個主要的工具：xtrabackup、innobackupex（1）xtrabackup只能備份InnoDB和XtraDB兩種數(shù)據(jù)表，而不能備份MyISAM數(shù)據(jù)表（2）innobackupex則封裝了xtrabackup，是一個腳本封裝，所

28、以能同時備份處理innodb和myisam，但在處理myisam時需要加一個讀鎖首先我們先來簡單的了解一下xtrabackup是怎么工作的。xtrabackup基于innodb的crash-recovery（實例恢復(fù)）功能，先copy innodb的物理文件（這個時候數(shù)據(jù)的一致性是無法滿足的），然后進(jìn)行基于redo log進(jìn)行恢復(fù)，達(dá)到數(shù)據(jù)的一致性。詳細(xì)的信息可以參數(shù)/doc/percona-xtrabackup/LATEST/how_xtrabackup_works.html 我就不翻譯了。我們還是簡單的來看一下日常工作中具體的使用：全量備份：xtrabackup -backup -targ

29、et-dir=/data/backup/base可以先看到：在備份過程中,可以看到很多輸出顯示數(shù)據(jù)文件被復(fù)制,以及日志文件線程反復(fù)掃描日志文件和復(fù)制。同樣的它也輸出了當(dāng)前的binlog filename和position，如果有g(shù)tid(同樣也會輸出) 可以用于搭建主從。最后一行一定會是你的lsn被copy的信息。這是因為每次啟動備份，都會記錄170429 12:54:10 log scanned up to (1676085)），然后開始拷貝文件，一般來講數(shù)據(jù)庫越大拷貝文件是要花費越長的時間，所以說這期間一般情況都會有新的操作，所以說所有文件也可能記錄的并不是一個時間點的數(shù)據(jù)。為了解決數(shù)據(jù)這

30、個問題，XtraBackup 就會啟動一個后臺進(jìn)程來每秒1次的觀測mysql的事務(wù)日志，直到備份結(jié)束。而且把事務(wù)日志中的改變記錄下來。我們知道事物日志是會重用的（redo log）,所以這個進(jìn)程會把redolog寫到自己的日志文件xtrabackup_log，這個后臺監(jiān)控進(jìn)程會記錄所有的事務(wù)日志的改變，用于保證數(shù)據(jù)一致性所。增量備份：當(dāng)我們做過全量備份以后會在目錄下產(chǎn)生xtrabackup_checkpoints的文件 這里面記錄了lsn和備份方式，我們可以基于這次的全量做增量的備份。$cat xtrabackup_checkpointsbackup_type = full-backupedf

31、rom_lsn = 0to_lsn = 1676085last_lsn = 1676085compact = 0recover_binlog_info = 0 xtrabackup -backup -target-dir=/data/backup/inc1 -incremental-basedir=/data/backup/base這個時候xtrabackup也是去打開了xtrabackup_checkpoints文件進(jìn)行上一次備份的信息查看。這個時候去查看增量備份的xtrabackup_checkpoints也記錄了這些信息$cat xtrabackup_checkpointsbackup_

32、type = incrementalfrom_lsn = 1676085to_lsn = 1676085last_lsn = 1676085compact = 0recover_binlog_info = 0這也意味著你可以在增量的備份上繼續(xù)增量的備份。同樣的xtrabackup也支持壓縮（-compress）、加密（-encrypt）、并行(-parallel)等操作，但是和mysqlbackup不同的是這個沒有同時的備份binlog,而mysqlbackup是備份了binlog的。我們來模擬一個恢復(fù)的過程深入的了解一下原理查看當(dāng)前數(shù)據(jù):dbadmintest 03:04:33select

33、* from t;+-+| id |+-+| 1 |+-+1 row in set (0.00 sec)全量備份$xtrabackup -backup -target-dir=/data/backup/base模擬增量數(shù)據(jù)dbadmintest 03:07:16select * from t;+-+| id |+-+| 1 | 2 |+-+2 rows in set (0.00 sec)進(jìn)行增量備份：$xtrabackup -backup -target-dir=/data/backup/inc1 -incremental-basedir=/data/backup/base模擬無備份操作：db

34、admintest 03:09:42select * from t;+-+| id |+-+| 1 | 2 | 3 |+-+3 rows in set (0.00 sec)模擬誤操作：dbadmintest 03:09:45truncate table t;Query OK, 0 rows affected (0.00 sec)摸擬恢復(fù)操作：找到誤操作的log positiondbadmintest 03:10:19show master logs;dbadmintest 03:10:47show binlog events in mysql-bin.000001;1333我們需要分別對全量、

35、增量備份各做一次prepare操作。xtrabackup -prepare -apply-log-only -target-dir=/data/backup/base增量xtrabackup -prepare -apply-log-only -target-dir=/data/backup/base -incremental-dir=/data/backup/inc1如果我們使用它自帶的還原命令的時候就要先把data目錄給清空。不然就會報如下的錯誤$innobackupex -copy-back /data/backup/base/170429 15:37:19 innobackupex: Starting the copy-back operationIMPORTANT: Please check that the copy-back run completes successfully.At the end