MySQL數(shù)據(jù)備份

MySQL的兩種備份策略

兩種備份策略指的是邏輯備份：分別是全量備份和增量備份。除了邏輯備份外，我們還可以選擇通過(guò)復(fù)制原始數(shù)據(jù)來(lái)備份數(shù)據(jù)，這種方式也稱為物理備份。關(guān)于邏輯備份和物理備份，它們有如下的特點(diǎn)：

物理備份的特點(diǎn)

  1. 因?yàn)槭侵苯訌?fù)制文件系統(tǒng)上面的數(shù)據(jù)，因此備份文件和數(shù)據(jù)文件完全一致

  2. 物理備份比邏輯備份速度要快

  3. 數(shù)據(jù)還原后不保證table層面的一致性（不同存儲(chǔ)引擎行為不一樣）

  4. 除了數(shù)據(jù)外，物理備份也同時(shí)備份了一些其他文件，例如日志文件

  5. 物理備份最好在服務(wù)器停止的情況下備份，確保數(shù)據(jù)一致性，否則需要在鎖表的前提下進(jìn)行備份

  6. 無(wú)法備份內(nèi)存表，因?yàn)閮?nèi)存表沒(méi)有寫入硬盤

  7. 物理備份可移植性較低，例如在Linux下備份的文件還原到windows下是就可能會(huì)有問(wèn)題

邏輯備份的特點(diǎn)

  1. 邏輯備份只能在服務(wù)器online的情況下備份，因?yàn)樾枰\(yùn)行sql語(yǔ)句
  2. 邏輯備份速度比物理備份慢
  3. 不管數(shù)據(jù)庫(kù)是什么引擎，保證數(shù)據(jù)在database層面和table層面的一致性
  4. 只能備份數(shù)據(jù)，不能備份配置文件或者日志文件
  5. 備份文件可能會(huì)比物理備份的大
  6. 邏輯備份可以在不鎖表的情況下進(jìn)行備份（針對(duì)innoDB引擎）
  7. 邏輯備份可移植性比物理備份高

邏輯備份的兩種方式

可以對(duì)數(shù)據(jù)進(jìn)行全量備份和增量備份，它們各有長(zhǎng)短，最好的方式是兩者結(jié)合使用，以確保數(shù)據(jù)的安全性和一致性。

全量備份的好處是數(shù)據(jù)完整，還原方便，但壞處是備份時(shí)間長(zhǎng)；而增量備份的好處是備份時(shí)間短，缺點(diǎn)是數(shù)據(jù)不完整，還原比全量備份麻煩。在實(shí)際情況中，我們應(yīng)該定期為數(shù)據(jù)庫(kù)做全量備份，然后分時(shí)段做增量備份

使用mysqldump程序備份數(shù)據(jù)

一般來(lái)說(shuō)，我們都是使用mysqldump來(lái)進(jìn)行數(shù)據(jù)備份；除非你使用的是企業(yè)版的數(shù)據(jù)庫(kù)，那么，你有更好的選擇（mysqlbackup）。

mysqldump雖然使用簡(jiǎn)單，但是還是有很多地方需要注意的，尤其有些參數(shù)可能會(huì)造成理解上的錯(cuò)誤。

  • 基本備份命令

    例如要備份名稱為wordpress的數(shù)據(jù)庫(kù)，可以使用下面的命令

    root@ubuntu-server:~# mysqldump -uUser -pPassword wordpress > wordpress.sql

    mysqldump數(shù)據(jù)默認(rèn)輸出到標(biāo)準(zhǔn)輸出，因此要把輸出重定向到指定文件中。執(zhí)行完命令后，wordpress數(shù)據(jù)庫(kù)的內(nèi)容就會(huì)備份到wordpress.sql文件中。

  • 備份某個(gè)指定表

    mysqldump還可以單獨(dú)備份某個(gè)指定的表，例如要備份wordpress下的article表

    root@ubuntu-server:~# mysqldump -uUser -pPassword wordpress article > article.sql

    除了用戶名和密碼外，mysqldump會(huì)把第一個(gè)給出的參數(shù)作為數(shù)據(jù)庫(kù)，其他的則作為表來(lái)備份，因此，上面語(yǔ)句的意思是：備份wordpress數(shù)據(jù)庫(kù)中的article表到article.sql文件。如果需要備份多個(gè)表（article、user、comment），只需要把表的名稱添加上即可，例如：

    root@ubuntu-server:~# mysqldump -uUser -pPassword wordpress article user comment > backup.sql

  • 備份多個(gè)數(shù)據(jù)庫(kù)

    通過(guò)--databases選項(xiàng)（短形式為-B），可以同時(shí)備份多個(gè)數(shù)據(jù)庫(kù)，例如備份wordpress數(shù)據(jù)庫(kù)和mysql數(shù)據(jù)庫(kù)

    root@ubuntu-server:~# mysqldump -uUser -pPassword --databases wordpress mysql > backup.sql

    --databases后面跟著的參數(shù)都被當(dāng)做數(shù)據(jù)庫(kù)來(lái)處理，除此之外，這個(gè)選項(xiàng)還會(huì)在備份文件中添加上create database和use database命令。因此，在還原數(shù)據(jù)庫(kù)的時(shí)候就不需要手動(dòng)處理這些工作。

  • 備份所有數(shù)據(jù)庫(kù)

    使用的選項(xiàng)是--all-databases（短形式為-A）

    root@ubuntu-server:~# mysqldump -uUser -pPassword --all-databases > all.sql

    這個(gè)選項(xiàng)和--databases一樣，也會(huì)在備份文件中添加create database和use database命令。

  • 備份innoDB數(shù)據(jù)庫(kù)

    innoDB數(shù)據(jù)庫(kù)的特點(diǎn)是支持事務(wù)，通過(guò)--single-transaction選項(xiàng)，可以在不鎖表的情況下備份innoDB數(shù)據(jù)庫(kù)，確保數(shù)據(jù)一致性。另外需要注意的是，在備份過(guò)程中，任何ALTER TABLE、CREATE TABLE、DROP TABLE、RENAME TABLE、TRUNCATE TABLE命令都不允許被運(yùn)行。--single-transaction選項(xiàng)會(huì)通過(guò)start transaction新建一個(gè)事務(wù)，然后把數(shù)據(jù)庫(kù)隔離級(jí)別設(shè)置為REPEATABLE READ。

    root@ubuntu-server:~# mysqldump --single-transaction -uUser -pPassword --databases wordpress > wordpress.sql

  • 備份myisam數(shù)據(jù)庫(kù)
    --single-transaction選項(xiàng)不支持myisam數(shù)據(jù)庫(kù)，因此，如果要確保myisam數(shù)據(jù)庫(kù)備份時(shí)候的一致性，需要對(duì)數(shù)據(jù)庫(kù)進(jìn)行鎖表操作。鎖表選項(xiàng)是--lock-tables。

    root@ubuntu-server:~# mysqldump --lock-tables -uUser -pPassword --databases tempdb > tempdb.sql

  • 備份數(shù)據(jù)庫(kù)以建立slave服務(wù)器

    建立主從服務(wù)器的一個(gè)關(guān)鍵點(diǎn)是binary log，因此，如果希望通過(guò)備份數(shù)據(jù)庫(kù)來(lái)建立主從關(guān)系，那么如何處理binary log是關(guān)鍵。

    通過(guò)--flush-logs（-F）選項(xiàng)，可以在備份的時(shí)候同時(shí)刷新bniary log。另外，--flush-logs選項(xiàng)通常會(huì)結(jié)合-lock-all-tables、--master-data或--single-transaction選項(xiàng)使用，針對(duì)不同的數(shù)據(jù)庫(kù)引擎，使用不同的選項(xiàng)。

    --master-data選項(xiàng)的作用是在備份文件中添加change master to命令，當(dāng)該選項(xiàng)的值為2，則change master to命令被注釋，1則沒(méi)有注釋。

    root@ubuntu-server:~# mysqldump --all-databases --lock-all-tables --master-data=1 --flush-logs -uUser -pPassword > all.sql

    備份好后，需要到從服務(wù)器中還原all.sql，以及flush-logs選項(xiàng)生成的binary log文件。以確保數(shù)據(jù)的一致性。

通過(guò)mysqladmin增量備份數(shù)據(jù)

增量備份數(shù)據(jù)需要mysql開(kāi)啟binary log模式，增量數(shù)據(jù)都被儲(chǔ)存到binary log中。做增量備份之前請(qǐng)確保已經(jīng)做好全量備份。具體可以通過(guò)上面介紹的方法。

通過(guò)mysqladmin生成增量備份其實(shí)非常簡(jiǎn)單，通過(guò)flush-logs命令就可以創(chuàng)建增量備份，假設(shè)在全量備份之前，mysql數(shù)據(jù)目錄內(nèi)容如下：

root@ubuntu-server:/var/lib/mysql# ls -l
total 244736
   ......
drwxr-x--- 2 mysql mysql     4096 4月  26 06:06 mysql
-rw-r----- 1 mysql mysql    91332 6月  20 11:03 mysql-bin.000001
-rw-r----- 1 mysql mysql  6233676 6月  20 13:39 mysql-bin.000002
-rw-r----- 1 mysql mysql  1843408 6月  20 14:35 mysql-bin.000003
-rw-r----- 1 mysql mysql 39375931 6月  21 07:35 mysql-bin.000004
-rw-r----- 1 mysql mysql 10037135 6月  21 11:44 mysql-bin.000005
-rw-r----- 1 mysql mysql       95 6月  21 07:35 mysql-bin.index
-rw-r--r-- 1 root  root         6 4月  26 06:06 mysql_upgrade_info
drwxr-x--- 2 mysql mysql     4096 4月  26 06:06 performance_schema
drwxr-x--- 2 mysql mysql    12288 3月  26 10:53 sys
drwxr-x--- 2 mysql mysql     4096 6月  21 10:50 wordpress
drwxr-x--- 2 mysql mysql    12288 5月  28 13:44 zabbix

一共有5個(gè)binary log文件，那么，在使用下面的備份命令之后，結(jié)果會(huì)怎樣

root@ubuntu-server:~# mysqldump --all-databases --lock-all-tables --master-data=1 --flush-logs -uUser -pPassword > all.sql

然后再ls一下該目錄

root@nas-share:/var/lib/mysql# ls -l
total 322452
   ......
drwxr-x--- 2 mysql mysql     4096 4月  26 06:06 mysql
-rw-r----- 1 mysql mysql    91332 6月  20 11:03 mysql-bin.000001
-rw-r----- 1 mysql mysql  6233676 6月  20 13:39 mysql-bin.000002
-rw-r----- 1 mysql mysql  1843408 6月  20 14:35 mysql-bin.000003
-rw-r----- 1 mysql mysql 39375931 6月  21 07:35 mysql-bin.000004
-rw-r----- 1 mysql mysql 10095733 6月  21 11:45 mysql-bin.000005
-rw-r----- 1 mysql mysql     6013 6月  21 11:46 mysql-bin.000006
-rw-r----- 1 mysql mysql      114 6月  21 11:45 mysql-bin.index
-rw-r--r-- 1 root  root         6 4月  26 06:06 mysql_upgrade_info
drwxr-x--- 2 mysql mysql     4096 4月  26 06:06 performance_schema
drwxr-x--- 2 mysql mysql    12288 3月  26 10:53 sys
drwxr-x--- 2 mysql mysql     4096 6月  21 10:50 wordpress
drwxr-x--- 2 mysql mysql    12288 5月  28 13:44 zabbix

發(fā)現(xiàn)多了一個(gè)日志mysql-bin.000006，這個(gè)日志其實(shí)就是第一個(gè)增量備份，mysqladmin的flush-logs命令和mysqldump的差不多，也就是說(shuō)，如果你希望在特定時(shí)候創(chuàng)建增量備份，就可以使用下面的命令：

root@ubuntu-server:~# mysqladmin -uUser -pPassword flush-logs

結(jié)果是目錄下面增加了mysql-bin.000007日志，這也是最新的增量備份數(shù)據(jù)。

通過(guò)mysqlbinlog還原增量備份

假如前面的完全備份（all.sql）是在mysql-bin.000006之前創(chuàng)建的，如果有一天數(shù)據(jù)意外丟失，那么你可以先把完全備份還原，然后再分別還原兩個(gè)增量備份，分別是mysql-bin.000006和mysql-bin.000007。具體步驟如下：

  1. 還原完全備份

    root@ubuntu-server:~# mysql -uUser -pPassword < all.sql

  2. 還原增量備份

    先查看一下增量備份有哪些，6和7就是我們還原的目標(biāo)

    mysqlbinlog有一個(gè)選項(xiàng)比較重要，它就是--disable-log-bin，它可以防止還原過(guò)程產(chǎn)生額外的日志記錄，從而避免了數(shù)據(jù)循環(huán)寫入。

    root@ubuntu-server:/var/lib/mysql# ls
auto.cnf         ibdata1      mysql             mysql-bin.000004  mysql-bin.index     restore.sh   zabbix
backall.sql      ib_logfile0  mysql-bin.000001  mysql-bin.000005  mysql_upgrade_info  restore.sql
debian-5.7.flag  ib_logfile1  mysql-bin.000002  mysql-bin.000006  performance_schema  sys
ib_buffer_pool   ibtmp1       mysql-bin.000003  mysql-bin.000007  restore2.sql        wordpress

    接下來(lái)使用mysqlbinlog進(jìn)行還原，當(dāng)需要還原被意外刪除的數(shù)據(jù)之前，需要先確保日志中的drop語(yǔ)句被移除，可以先把日志文件中的內(nèi)容導(dǎo)出到普通的文件當(dāng)中，然后把相關(guān)的drop語(yǔ)句刪除，這樣才能保證數(shù)據(jù)可以正確還原。

    root@ubuntu-server:/var/lib/mysql# mysqlbinlog mysql-bin.000006 >> bak.sql  #導(dǎo)出后刪除drop語(yǔ)句（如果有的話）
root@ubuntu-server:/var/lib/mysql# mysqlbinlog mysql-bin.000007 >> bak.sql  #導(dǎo)出后刪除drop語(yǔ)句（如果有的話）
root@ubuntu-server:/var/lib/mysql# cat bak.sql | mysql -u root -p   #最后進(jìn)行數(shù)據(jù)還原

  3. 其他選項(xiàng)

    mysqlbinlog有些選項(xiàng)在還原數(shù)據(jù)的時(shí)候非常有用，分別是

    • --start-position和--stop-position
    • --start-datetime和--stop-datetime

    第一對(duì)選項(xiàng)用于指定開(kāi)始還原的位置和結(jié)束還原的位置。如果只指定start-position，則數(shù)據(jù)從start-position一直還原到日志文件的末尾，相反如果只指定stop-position，那么數(shù)據(jù)從日志開(kāi)頭一直還原到stop-position位置。

    第二對(duì)選項(xiàng)則用來(lái)指定還原的起始時(shí)間，其作用和start-position類似。

  4. 使用mysqlbinlog查看日志內(nèi)容

    由于binary log日志是二進(jìn)制形式的，我們無(wú)法像普通文件一樣查看它里面的內(nèi)容，不過(guò)MySQL提供了mysqlbinlog工具來(lái)操作binary log。

    root@ubuntu-server:/var/lib/mysql# mysqlbinlog mysql-bin.000007
BINLOG '
QSIrWxMBAAAAPAAAAP0BAAAAAAQCAAAAAAEABnphYmJpeAAMaGlzdG9yeV91aW50AAQIAwgDAAAD
ESAf
QSIrWx4BAAAAVQAAAFICAAAAAAQCAAAAAAEAAgAE//B8XQAAAAAAAEAiK1sAAAAAAAAAAGNL/hvw
6FoAAAAAAABAIitbAAAAAAAAAABZegEcDUyQpA==
'/*!*/;
# at 594
#180621 11:57:53 server id 1  end_log_pos 625 CRC32 0x7a2c64d6  Xid = 539263
COMMIT/*!*/;

    默認(rèn)情況下，日志會(huì)輸出到標(biāo)準(zhǔn)輸出，且數(shù)據(jù)以base64方式加密，如果希望查看具體內(nèi)容，可以使用--base64-output和-v選項(xiàng)解密。--base64-output選項(xiàng)可以抑制加密信息的輸出，而-v選項(xiàng)可重構(gòu)日志中的sql語(yǔ)句

    root@ubuntu-server:/var/lib/mysql# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000007 > decode.sql

    解密后的內(nèi)容部分顯示如下，已經(jīng)可以看到具體的sql語(yǔ)句操作：

    # at 389
#180621  7:35:22 server id 1  end_log_pos 449 CRC32 0x43f149b1  Write_rows: table id 272 flags: STMT_END_F
### INSERT INTO `zabbix`.`history`
### SET
###   @1=23301
###   @2=1529537721
###   @3=26.818599999999999994
###   @4=995065421

    當(dāng)然，通過(guò)-v選項(xiàng)輸出的文件不能用來(lái)進(jìn)行數(shù)據(jù)的還原，因?yàn)樵撨x項(xiàng)會(huì)把任何insert、update語(yǔ)句都注釋掉。

最后編輯于 ：
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容