小編給大家分享一下MySQL增量備份與恢復(fù)的示例，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

創(chuàng)新互聯(lián)-專(zhuān)業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性?xún)r(jià)比塔河網(wǎng)站開(kāi)發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫(kù),直接使用。一站式塔河網(wǎng)站制作公司更省心,省錢(qián),快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋塔河地區(qū)。費(fèi)用合理售后完善，10多年實(shí)體公司更值得信賴(lài)。

文章目錄

• 一、MySQL 增量備份

• 1.1 為什么使用增量備份

• 1.2 增量備份的特點(diǎn)

• 1.增量備份的概念

• 2.增量備份示例

• 二、MySQL 增量恢復(fù)

• 4.1 基于時(shí)間點(diǎn)的恢復(fù)

• 4.1 基于位置的操作

• 1.增量恢復(fù)的場(chǎng)景

• 2.丟失完全備份之后更改的數(shù)據(jù)的恢復(fù)步驟

• 3.完全備份之后丟失所有數(shù)據(jù)的恢復(fù)步驟

• 4. 基于時(shí)間點(diǎn)與位置的恢復(fù)

• 5. 指定企業(yè)備份策略的思路

一、MySQL 增量備份

增量備份可以在完全備份的基礎(chǔ)上，減少備份文件的大小，從而加快備份和恢復(fù)的速度

1.增量備份的概念

1.1 為什么使用增量備份

• 前面章節(jié)講到了完全備份有兩種方式，一種是使用 tar 打包數(shù)據(jù)文件，另一種是
  使用 mysqldump 進(jìn)行完全備份

• 完全備份存在的問(wèn)題很容易看到，每次都是把所有的數(shù)據(jù)內(nèi)容進(jìn)行備份，備份數(shù)據(jù)中有大量的重復(fù)數(shù)據(jù)，并且完全備份的時(shí)間與恢復(fù)的時(shí)間很長(zhǎng)

• 解決完全備份存在的問(wèn)題就是使用增量備份的方式，增量備份就是備份自上一次備份之后增加或改變的文件或者內(nèi)容

1.2 增量備份的特點(diǎn)

• 增量備份的優(yōu)點(diǎn)是沒(méi)有重復(fù)數(shù)據(jù)，備份量不大，時(shí)間短

• 缺點(diǎn)也很明顯，需要上次完全備份及完全備份之后所有的增量備份才能恢復(fù)，而且對(duì)所有增量備份進(jìn)行逐個(gè)反推恢復(fù)，操作較為繁鎖

• MySQL 沒(méi)有提供直接的增量備份方法，但是可以通過(guò) MySQL 的二進(jìn)制日志（binary
  logs)間接實(shí)現(xiàn)增量備份

二進(jìn)制日志對(duì)備份的意義如下：

1. 二進(jìn)制日志保存了所有更新或者可能更新數(shù)據(jù)庫(kù)的操作

2. 二進(jìn)制日志在啟動(dòng) MySQL 服務(wù)器后開(kāi)始記錄，并在文件達(dá)到 max_binlog_size 所設(shè)置的大小或者接收到 flush logs 命令后重新創(chuàng)建新的日志文件

3. 只需要定時(shí)執(zhí)行 flush logs 方法重新創(chuàng)建新的日志，生成二進(jìn)制文件序列，并及時(shí)把這些日志保存到安全的地方就完成了一個(gè)時(shí)間段的增量備份

2.增量備份示例

1. 開(kāi)啟二進(jìn)制日志功能

vim /etc/my.cnf...[mysqld]log-bin=mysql-binbinlog_format = MIXED#指定二進(jìn)制日志(binlog)的記錄格式為 MIXEDsystemctl restart mysqld.service#重啟服務(wù)cd /usr/local/mysql/datals -l /usr/local/mysql/data/mysql-bin.*#查看二進(jìn)制文件#二進(jìn)制日志(binlog)有3種不同的記錄格式：STATEMENT(基于SQL語(yǔ)句)、ROW(基于行)、MIXED(混合模式)#默認(rèn)格式是 STATEMENT

2. 每周選擇服務(wù)器負(fù)載較輕的時(shí)間段，或者用戶(hù)訪問(wèn)較少的時(shí)間段進(jìn)行備份

mysqldump -uroot -p123123 SCHOOL CLASS01 > /opt/SCHOOL_CLASS01_$(date +%F).sql#對(duì)表進(jìn)行完全備份mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql#對(duì)庫(kù)進(jìn)行完全備份crontab -e#也可以使用計(jì)劃性任務(wù)來(lái)執(zhí)行30 3 * * 3 mysqldump -uroot -p123123 SCHOOL CLASS01 > /opt/SCHOOL_CLASS01_$(date +%F).sql30 3 * * 3 mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql每周三的凌晨 3:00 對(duì)數(shù)據(jù)庫(kù)和表進(jìn)行完全備份

3. 可每天進(jìn)行增量備份操作，生成新的二進(jìn)制日志文件，這樣在插入新的數(shù)據(jù)后，新的二進(jìn)制文件對(duì)應(yīng)的就是數(shù)據(jù)庫(kù)的變化的內(nèi)容

ls /usr/local/mysql/datamysqladmin -uroot -p123123 flush-logs

4. 插入新的數(shù)據(jù)，以模擬數(shù)據(jù)的增加或變更

use SCHOOL;insert into CLASS01 values(3,'wangsan','woman','games');insert into CLASS01 values(4,'wangsi','man','runing');select * from CLASS01;

5. 生成新的二進(jìn)制文件并查看其內(nèi)容

cd /usr/local/mysql/data/lsmysqladmin -uroot -p123123 flush-logs

cp mysql-bin.000002 /opt/#將記錄變更的二進(jìn)制文件02復(fù)制至/opt目錄下cd /opt/lsmysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002#使用64位編碼機(jī)制去解碼，按行讀取詳細(xì)內(nèi)容

二、MySQL 增量恢復(fù)

• 增量恢復(fù)比完全恢復(fù)操作更為繁瑣

• 每個(gè)增量備份都是單獨(dú)的個(gè)體，數(shù)據(jù)不重復(fù)，需要控制得更加精確

1.增量恢復(fù)的場(chǎng)景

• 當(dāng)數(shù)據(jù)發(fā)送錯(cuò)誤時(shí)，應(yīng)根據(jù)實(shí)際情況選擇使用完全備份恢復(fù)，還是增量備份

• 增量備份的場(chǎng)景是：

• 人為的 SQL 語(yǔ)句破壞了數(shù)據(jù)庫(kù)

• 在進(jìn)行下一次全備之前發(fā)送系統(tǒng)故障導(dǎo)致數(shù)據(jù)庫(kù)數(shù)據(jù)丟失

• 在主從架構(gòu)中，主庫(kù)數(shù)據(jù)發(fā)送了故障

• 根據(jù)數(shù)據(jù)丟失的情況可以分為兩類(lèi)：

• 只丟失了完全備份之后更改的數(shù)據(jù)

• 完全備份之后丟失所有的數(shù)據(jù)

2.丟失完全備份之后更改的數(shù)據(jù)的恢復(fù)步驟

• 當(dāng)完全備份之后更改的數(shù)據(jù)丟失，需要把完全備份之后的所有增量備份文件逐個(gè)恢復(fù)

• 步驟如下：

mysql -uroot -p123123use SCHOOL;delete from CLASS1 where id=3;delete from CLASS1 where id=4;#刪除插入的兩條數(shù)據(jù)，模擬完全備份后數(shù)據(jù)丟失的故障select * from CLASS01;#檢查quitmysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123#使用二進(jìn)制文件進(jìn)行恢復(fù)操作mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"#檢查表內(nèi)容是否恢復(fù)

3.完全備份之后丟失所有數(shù)據(jù)的恢復(fù)步驟

• 當(dāng)完全備份和增量備份之后，所有的數(shù)據(jù)丟失，需要把完全備份和所有增量備份文件逐個(gè)恢復(fù)

• 步驟如下：

mysql -uroot -p123123use SCHOOL;drop table CLASS01;#直接刪除整個(gè)表，假設(shè)完全備份后所有數(shù)據(jù)都丟失了quitmysql -uroot -p123123 SCHOOL < /opt/SCHOOL_CLASS01_2021-02-06.sqlmysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"#進(jìn)行完全備份后查看一下mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -uroot -p123123#增量備份mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"

4. 基于時(shí)間點(diǎn)與位置的恢復(fù)

• 利用二進(jìn)制日志可實(shí)現(xiàn)基于時(shí)間點(diǎn)與位置的恢復(fù)，例如由于誤操作刪除了一張表，這時(shí)完全恢復(fù)是沒(méi)有用的

• 因?yàn)槿罩纠镞€有誤操作的語(yǔ)句，我們需要的是恢復(fù)到誤操作之前的狀態(tài)，然后跳過(guò)誤操作的語(yǔ)句，再恢復(fù)后面操作的語(yǔ)句

4.1 基于時(shí)間點(diǎn)的恢復(fù)

• 基于時(shí)間點(diǎn)的恢復(fù)，就是將某個(gè)起始時(shí)間的二進(jìn)制文件導(dǎo)入數(shù)據(jù)庫(kù)中，從而跳過(guò)某個(gè)發(fā)生錯(cuò)誤的時(shí)間點(diǎn)實(shí)現(xiàn)數(shù)據(jù)的恢復(fù)

• 使用 mysqlbinlog 加上 --stop-datetime 選項(xiàng)，表示在哪個(gè)時(shí)間點(diǎn)結(jié)束，后面誤操作的語(yǔ)句不執(zhí)行

• –start-datetime 選項(xiàng)表示執(zhí)行后面的語(yǔ)句

• 結(jié)合使用它們就可以跳過(guò)誤操作的語(yǔ)句，完成恢復(fù)工作

• 需要注意的是，二進(jìn)制文件中保存的日期格式需要調(diào)整為用“-”分割

#恢復(fù)用戶(hù)“wangsan”的數(shù)據(jù)，而不恢復(fù)“wangsi”mysql -uroot -p123123 -e "truncate table SCHOOL.CLASS01;"mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"mysqlbinlog --no-defaults --stop-datetime='2021-02-06 15:58:39' /opt/mysql-bin.000002 |mysql -uroot -p123123mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"

#恢復(fù)“wangsi”的數(shù)據(jù)mysqlbinlog --no-defaults --start-datetime='2021-02-06 15:58:39' /opt/mysql-bin.000002 |mysql -uroot -p

4.1 基于位置的操作

• 基于位置的恢復(fù)，就是使用基于時(shí)間點(diǎn)的恢復(fù)

• 可能會(huì)出現(xiàn)在一個(gè)時(shí)間點(diǎn)里既同時(shí)存在正確的操作又存在錯(cuò)誤的操作，基于位置是一種更為精確的恢復(fù)方式

mysqlbinlog --no-defaults --stop-position='609' /opt/mysql-bin.000002 | mysql -uroot -p#使用64位編碼機(jī)制去解碼并按行讀取二進(jìn)制文件02（增量備份）的詳細(xì)內(nèi)容......略

#僅恢復(fù)“1810”之前的數(shù)據(jù)，即不恢復(fù)“wangsi”的數(shù)據(jù)mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"mysql -uroot -p123123 -e "truncate table SCHOOL.CLASS01;"mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"mysqlbinlog --no-defaults --stop-position='1810' /opt/mysql-bin.000002 | mysql -uroot -pmysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"

#僅恢復(fù)“wangsi”的數(shù)據(jù)，跳過(guò)“wangsan”的數(shù)據(jù)恢復(fù)，即僅有第四條記錄mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"mysqlbinlog --no-defaults --start-position='1810' /opt/mysql-bin.000002 | mysql -uroot -p123123mysql -uroot -p123123 -e "select * from SCHOOL.CLASS01;"

5. 指定企業(yè)備份策略的思路

• 指定企業(yè)備份策略要根據(jù)企業(yè)數(shù)據(jù)庫(kù)的實(shí)際讀寫(xiě)的頻繁性與數(shù)據(jù)的重要性進(jìn)行

• 數(shù)據(jù)更新頻繁，則應(yīng)該進(jìn)行較為頻繁的備份

• 數(shù)據(jù)較為重要，則在有適當(dāng)更新時(shí)進(jìn)行備份

• 在數(shù)據(jù)庫(kù)壓力小的時(shí)段進(jìn)行全備，如一周一次，然后每天增備

• 根據(jù)公司的規(guī)模，中小公司可一天一次全備，大公司可每周一次全備，每天進(jìn)行一次增備，并且盡量為企業(yè)實(shí)現(xiàn)主從復(fù)制架構(gòu)

以上是“MySQL增量備份與恢復(fù)的示例”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對(duì)大家有所幫助，如果還想學(xué)習(xí)更多知識(shí)，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！