MySQL增量備份與恢復的示例

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

文章目錄

• 一、MySQL 增量備份

• 
  

• 1.1 為什么使用增量備份

• 1.2 增量備份的特點

• 1.增量備份的概念

• 
  

• 2.增量備份示例

• 二、MySQL 增量恢復

• 
  

• 4.1 基于時間點的恢復

• 4.1 基于位置的操作

• 1.增量恢復的場景

• 2.丟失完全備份之后更改的數據的恢復步驟

• 3.完全備份之后丟失所有數據的恢復步驟

• 4. 基于時間點與位置的恢復

• 
  

• 5. 指定企業備份策略的思路

一、MySQL 增量備份

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

1.增量備份的概念

1.1 為什么使用增量備份

• 前面章節講到了完全備份有兩種方式，一種是使用 tar 打包數據文件，另一種是
  使用 mysqldump 進行完全備份

• 完全備份存在的問題很容易看到，每次都是把所有的數據內容進行備份，備份數據中有大量的重復數據，并且完全備份的時間與恢復的時間很長

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

1.2 增量備份的特點

• 增量備份的優點是沒有重復數據，備份量不大，時間短

• 缺點也很明顯，需要上次完全備份及完全備份之后所有的增量備份才能恢復，而且對所有增量備份進行逐個反推恢復，操作較為繁鎖

• MySQL 沒有提供直接的增量備份方法，但是可以通過 MySQL 的二進制日志（binary
  logs)間接實現增量備份

二進制日志對備份的意義如下：

1. 二進制日志保存了所有更新或者可能更新數據庫的操作

2. 二進制日志在啟動 MySQL 服務器后開始記錄，并在文件達到 max_binlog_size 所設置的大小或者接收到 flush logs 命令后重新創建新的日志文件

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

2.增量備份示例

1. 開啟二進制日志功能

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

2. 每周選擇服務器負載較輕的時間段，或者用戶訪問較少的時間段進行備份

mysqldump -uroot -p123123 SCHOOL CLASS01 > /opt/SCHOOL_CLASS01_$(date +%F).sql#對表進行完全備份mysqldump -uroot -p123123 --all-databases SCHOOL > /opt/SCHOOL_$(date +%F).sql#對庫進行完全備份crontab -e#也可以使用計劃性任務來執行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 對數據庫和表進行完全備份

3. 可每天進行增量備份操作，生成新的二進制日志文件，這樣在插入新的數據后，新的二進制文件對應的就是數據庫的變化的內容

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

4. 插入新的數據，以模擬數據的增加或變更

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

5. 生成新的二進制文件并查看其內容

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

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

二、MySQL 增量恢復

• 增量恢復比完全恢復操作更為繁瑣

• 每個增量備份都是單獨的個體，數據不重復，需要控制得更加精確

1.增量恢復的場景

• 當數據發送錯誤時，應根據實際情況選擇使用完全備份恢復，還是增量備份

• 增量備份的場景是：

• 人為的 SQL 語句破壞了數據庫

• 在進行下一次全備之前發送系統故障導致數據庫數據丟失

• 在主從架構中，主庫數據發送了故障

• 根據數據丟失的情況可以分為兩類：

• 只丟失了完全備份之后更改的數據

• 完全備份之后丟失所有的數據

2.丟失完全備份之后更改的數據的恢復步驟

• 當完全備份之后更改的數據丟失，需要把完全備份之后的所有增量備份文件逐個恢復

• 步驟如下：

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

3.完全備份之后丟失所有數據的恢復步驟

• 當完全備份和增量備份之后，所有的數據丟失，需要把完全備份和所有增量備份文件逐個恢復

• 步驟如下：

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

4. 基于時間點與位置的恢復

• 利用二進制日志可實現基于時間點與位置的恢復，例如由于誤操作刪除了一張表，這時完全恢復是沒有用的

• 因為日志里還有誤操作的語句，我們需要的是恢復到誤操作之前的狀態，然后跳過誤操作的語句，再恢復后面操作的語句

4.1 基于時間點的恢復

• 基于時間點的恢復，就是將某個起始時間的二進制文件導入數據庫中，從而跳過某個發生錯誤的時間點實現數據的恢復

• 使用 mysqlbinlog 加上 --stop-datetime 選項，表示在哪個時間點結束，后面誤操作的語句不執行

• –start-datetime 選項表示執行后面的語句

• 結合使用它們就可以跳過誤操作的語句，完成恢復工作

• 需要注意的是，二進制文件中保存的日期格式需要調整為用“-”分割

#恢復用戶“wangsan”的數據，而不恢復“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;"

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

4.1 基于位置的操作

• 基于位置的恢復，就是使用基于時間點的恢復

• 可能會出現在一個時間點里既同時存在正確的操作又存在錯誤的操作，基于位置是一種更為精確的恢復方式

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

#僅恢復“1810”之前的數據，即不恢復“wangsi”的數據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;"

#僅恢復“wangsi”的數據，跳過“wangsan”的數據恢復，即僅有第四條記錄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. 指定企業備份策略的思路

• 指定企業備份策略要根據企業數據庫的實際讀寫的頻繁性與數據的重要性進行

• 數據更新頻繁，則應該進行較為頻繁的備份

• 數據較為重要，則在有適當更新時進行備份

• 在數據庫壓力小的時段進行全備，如一周一次，然后每天增備

• 根據公司的規模，中小公司可一天一次全備，大公司可每周一次全備，每天進行一次增備，并且盡量為企業實現主從復制架構

以上是“MySQL增量備份與恢復的示例”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！