mysql增量備份及恢復的操作方法

更新時間：2024年10月10日 16:32:21 作者：goser

增量備份是在全備或上次增量備份基礎上,只備份新增或修改的文件,減少數(shù)據(jù)量和時間,binlog記錄數(shù)據(jù)庫變更,重啟時創(chuàng)建新日志文件,增量備份復雜但減輕服務器負擔,而binlog幫助精確恢復數(shù)據(jù)

1 簡介

1.1 增量備份簡介

增量備份是指在一次全備份或上一次增量備份后，以后每次的備份只需備份與前一次相比增加或者被修改的文件。這就意味著，第一次增量備份的對象是進行全備后所產(chǎn)生的增加和修改的文件；第二次增量備份的對象是進行第一次增量備份后所產(chǎn)生的增加和修改的文件，如此類推。這種備份方式最顯著的優(yōu)點就是：沒有重復的備份數(shù)據(jù)，因此備份的數(shù)據(jù)量不大，備份所需的時間很短。但增量備份的數(shù)據(jù)恢復是比較麻煩的。您必須具有上一次全備份和所有增量備份磁帶（一旦丟失或損壞其中的一個增量，就會造成恢復的失敗），并且它們必須沿著從全備份到依次增量備份的時間順序逐個反推恢復，因此這就極大地延長了恢復時間。

假如我們有一個數(shù)據(jù)庫，有20G的數(shù)據(jù)，每天會增加10M的數(shù)據(jù)，數(shù)據(jù)庫每天都要全量備份一次，這樣的話服務器的壓力比較大，因此我們只需要備份增加的這部分數(shù)據(jù)，這樣減少服務器的負擔。

1.2 binlog簡介

binlog日志由配置文件的log-bin選項負責啟用，Mysql服務器將在數(shù)據(jù)根目錄創(chuàng)建兩個新文件XXX-bin.001和xxx-bin.index，若配置選項沒有給出文件名，Mysql將使用主機名稱命名這兩個文件，其中.index文件包含一份全體日志文件的清單。 Mysql會把用戶對所有數(shù)據(jù)庫的內容和結構的修改情況記入XXX-bin.n文件，而不會記錄 SELECT和沒有實際更新的UPDATE語句。
當MySQL數(shù)據(jù)庫停止或重啟時，服務器會把日志文件記入下一個日志文件，Mysql會在重啟時生成一個新的binlog日志文件，文件序號遞增，此外，如果日志文件超過max_binlog_size系統(tǒng)變量配置的上限時，也會生成新的日志文件。

2 binlog日志操作

2.1 開啟binlog日志

在my.cnf或my.ini中增加

[mysqld] 
log-bin=/data/3306/mysql-bin

　其中：log-bin若不顯示指定存儲目錄，則默認存儲在mysql的data目錄下

binlog_format的幾種格式：（STATEMENT，ROW和MIXED）

STATEMENT：基于SQL語句的復制(statement-based replication, SBR) 默認的格式
ROW：基于行的復制(row-based replication, RBR)
MIXED：混合模式復制(mixed-based replication, MBR)

啟動后會產(chǎn)生mysql-bin.*這樣的文件，每啟動一次，就會增加一個或者多個。如果全備的時候加-F參數(shù)的話也會增加一個binlog二進制文件或者mysqladmin flush-log也會增加

[root@moremysql ~]# ll /data/3306/
-rw-rw---- 1 mysql mysql    264 Sep 16 15:31 mysql-bin.000001
-rw-rw---- 1 mysql mysql    616 Sep 17 11:38 mysql-bin.index
-rw-rw---- 1 mysql mysql      6 Sep 17 11:29 mysqld.pid
-rw-r----- 1 mysql root   23377 Sep 17 11:29 mysql_oldboy3306.err
srwxrwxrwx 1 mysql mysql      0 Sep 17 11:29 mysql.sock

　查看binlog開啟情況和binlog的設置大小

mysql> show variables like 'log_bin%';
+---------------------------------+-------+
| Variable_name                   | Value |
+---------------------------------+-------+
| log_bin                         | ON    |
| log_bin_trust_function_creators | OFF   |
+---------------------------------+-------+

mysql> show variables like 'binlog%';
+-----------------------------------------+-----------+
| Variable_name                           | Value     |
+-----------------------------------------+-----------+
| binlog_cache_size                       | 1048576   |
| binlog_direct_non_transactional_updates | OFF       |
| binlog_format                           | STATEMENT |
| binlog_stmt_cache_size                  | 32768     |
+-----------------------------------------+-----------+

2.2 查看binlog日志內容

[root@moremysql ~]# mysqlbinlog  /data/3306/mysql-bin.000009
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#170917 11:19:55 server id 1  end_log_pos 107   Start: binlog v 4, server v 5.5.32-log created 170917 11:19:55
BINLOG '
2+m9WQ8BAAAAZwAAAGsAAAAAAAQANS41LjMyLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAVAAEGggAAAAICAgCAA==
'/*!*/;
# at 107
#170917 11:21:29 server id 1  end_log_pos 150   Rotate to mysql-bin.000010  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

　　日志內容部分是不可查看的，這是因為binlog二進制日志格式默認是STATEMENT，如果是行級模式ROW就可以查看了，可以用--base64-output=decode-rows -v參數(shù)臨時用ROW模式來查看

[root@moremysql ~]# mysqlbinlog --base64-output=decode-rows -v /data/3306/data/mysql-bin.000009
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File '/data/3306/data/mysql-bin.000009' not found (Errcode: 2)
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

mysqlbinlog命令參數(shù)：
　　1、作用：解析binlog日志為sql語句
　　2、-d 參數(shù)根據(jù)指定庫拆分binlog(單表通過關鍵字過濾)
　　3、位置參數(shù)：--start-position=365 --stop-position=456,精確定位去內容
　　4、時間參數(shù) --start-datetime='2017-10-16 17:16:15' --stop-datetime='2017-10-16 17:30:15' 模糊取內容，不太精確
　　5、-r 將語句導入到指定文件名中，相當于重定向>
　　6、解析ROW 級別binlog日志的方法： mysqlbinlog --base64-output=decode-rows -v mysql-bin.000004

　刪除binlog日志文件的方式有：
　　mysql>reset master;清空bin-log
　　mysql>PURGE MASTERLOGS TO 'mysql-bim.000005' 刪除mysql-bim.000005之前的bin-log mysql-bim.000005本身保留

3、mysql數(shù)據(jù)庫備份與恢復

3.1、備份數(shù)據(jù)庫

　　全備之前的binlog文件，只有一個binlog文件mysql-bin.000001

[root@moremysql ~]# ll /data/3306/
total 72
drwxr-xr-x 5 mysql mysql  4096 Sep 17 11:29 data
-rw-r--r-- 1 mysql mysql  1982 Sep 17 11:29 my.cnf
-rw-r--r-- 1 root  root   1899 Sep 17 00:22 my.cnf.ori
-rwx------ 1 mysql mysql  1307 Sep 16 15:29 mysql
-rw-rw---- 1 mysql mysql   107 Sep 17 13:01 mysql-bin.000001
-rw-rw---- 1 mysql mysql    28 Sep 17 13:01 mysql-bin.index

　　全備

[root@moremysql ~]# mysqldump -uroot -p123456 -S /data/3306/mysql.sock -B -x -F -R --master-data=2 class |gzip >class_$(date +%F).sql.gz

　　全備參數(shù)說明：

-B指定數(shù)據(jù)庫
-F刷新日志
-R備份存儲過程等
-x鎖表
--master-data在備份語句里添加CHANGEMASTER語句以及binlog文件及位置點信息

　　全備后的binlog文件，增加了一個mysql-bin.000002，這個增加的binlog二進制文件就是全備的時候-F參數(shù)的作用。相當于mysqladmin的參數(shù)flush-log或mysql> flush logs;

[root@moremysql ~]# ll /data/3306/
total 76
drwxr-xr-x 5 mysql mysql  4096 Sep 17 11:29 data
-rw-r--r-- 1 mysql mysql  1982 Sep 17 11:29 my.cnf
-rw-r--r-- 1 root  root   1899 Sep 17 00:22 my.cnf.ori
-rwx------ 1 mysql mysql  1307 Sep 16 15:29 mysql
-rw-rw---- 1 mysql mysql   150 Sep 17 13:04 mysql-bin.000001
-rw-rw---- 1 mysql mysql   107 Sep 17 13:04 mysql-bin.000002

3.2、準備第一份增量數(shù)據(jù)，向庫class的userinfo表中刪除、插入、更新數(shù)據(jù)

mysql> delete from userinfo  where name='hello';
mysql> delete from userinfo  where name='world';
mysql> insert into userinfo  values(12,'test');
mysql> update  userinfo set name='tiantian' where id=6;

　　這時候對數(shù)據(jù)庫文件的更改都會記錄到mysql-bin.000002中，這是全備后的增量文件，在增量恢復的時候用得到。

3.3、數(shù)據(jù)恢復

　　在數(shù)據(jù)恢復之前，停止數(shù)據(jù)庫對外服務。如果是在從庫上恢復(這個是事先做好的備份主庫，開啟了binlog)，還要關閉slave（因為開啟了slave的話，會從主庫復制，那么數(shù)據(jù)恢復就沒效果了）

　　同時應將增量備份文件復制一份到其他目錄或關閉sql_log_bin=off，因為恢復過程中，會繼續(xù)寫入語句到binlog，最終導致增量恢復數(shù)據(jù)部分變得比較混亂。

　　查看sql_log_bin狀態(tài)

mysql> show variables like 'sql_log%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | ON    |
| sql_log_off   | OFF   |
+---------------+-------+

　　臨時關閉sql_log_bin，數(shù)據(jù)恢復的時候就不會將恢復的數(shù)據(jù)寫入到binlog中，這樣binlog文件就會保持很清潔。待完全恢復完成后，再開啟sql_log_bin。

mysql> set sql_log_bin=off;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like 'sql_log%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_log_bin   | OFF   |
| sql_log_off   | OFF   |
+---------------+-------+

　　如果是從庫(備主庫)，要關掉slave

mysql> stop slave;
mysql> show slave status\G
             Slave_IO_Running: No
            Slave_SQL_Running: No
        Seconds_Behind_Master: NULL

　　現(xiàn)在開始還原全量數(shù)據(jù)：

[root@moremysql ~]# mysql -uroot -p123456  -S /data/3306/mysql.sock <class_2017-09-17.sql

　　全量恢復好后檢查是否恢復了全量備份時的數(shù)據(jù)：

[root@moremysql ~]# mysql -uroot -p123456  -S /data/3306/mysql.sock -e "use class;select * from userinfo;"
+----+----------+
| id | name     |
+----+----------+
|  1 | goser    |
|  3 | hello    |
|  5 | world    |
|  6 | zhangsan |
|  8 | lisi     |
| 10 | wangwu   |
+----+----------+

　　全量恢復沒問題后再進行增量恢復，恢復被刪除的數(shù)據(jù)，更改的保留

[root@moremysql ~]# cp /data/3306/mysql-bin.000002  /opt/

　　將mysqlbinlog -r 生成的文件中的delete語句刪掉即可

[root@moremysql ~]# mysqlbinlog /opt/mysql-bin.000002  -r userinfo.sql

[root@moremysql ~]# vim  userinfo.sql
delete from userinfo  where name='hello' ---刪掉這行
delete from userinfo  where name='world'---刪掉這行

　　將修改好的binlog增量文件導入到數(shù)據(jù)庫class中

[root@moremysql ~]# mysql -uroot -p123456  -S /data/3306/mysql.sock class < userinfo.sql

　　最后別忘了開啟sql_log_bin

mysql> set sql_log_bin=on;

　　到此數(shù)據(jù)恢復全部完成。。。。。

到此這篇關于mysql增量備份及恢復的文章就介紹到這了,更多相關mysql增量備份內容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

MySQL max_allowed_packet的坑
max_allowed_packet是 MySQL 中的一個設定參數(shù),用于設定所接受的包的大小,根據(jù)情形不同,其缺省值可能是 1M 或者 4M,本文主要介紹了MySQL max_allowed_packet的坑,感興趣的可以了解一下
2024-01-01
MySQL防止delete命令刪除數(shù)據(jù)的兩種方法
在sql中刪除數(shù)據(jù)庫中記錄我們會使用到delete命令，這樣如果不小心給刪除了很難恢復了，下面我來總結一些刪除數(shù)據(jù)但是不在數(shù)據(jù)庫刪除的方法，有需要的朋友可以參考一下
2013-08-08
MySQL數(shù)據(jù)庫怎么正確查詢字符串長度
MySQL中字符串長度一般指數(shù)據(jù)庫表中一個字段或列中存儲的字符串的最大長度,有時我們需要測量字符串長度,來保證表結構及數(shù)據(jù)庫性能穩(wěn)定,下面這篇文章主要給大家介紹了關于MySQL數(shù)據(jù)庫怎么正確查詢字符串長度的相關資料,需要的朋友可以參考下
2023-06-06
MySQL性能分析及explain的使用說明
本文我們主要介紹了MySQL性能分析以及explain的使用，包括：組合索引、慢查詢分析、MYISAM和INNODB的鎖定、MYSQL的事務配置項等，希望能夠對您有所幫助。
2011-08-08
MySQL安裝三種方法總結(yum安裝、編譯安裝、二進制安裝)
MySQL安裝網(wǎng)上的教程有很多,基本上大同小異,但是安裝軟件有時就可能因為一個細節(jié)安裝失敗,這篇文章主要介紹了MySQL安裝三種方法的相關資料,三種方法分別是yum安裝、編譯安裝以及二進制安裝,需要的朋友可以參考下
2023-12-12
Windows7下Python3.4使用MySQL數(shù)據(jù)庫
這篇文章主要為大家詳細介紹了Windows7下Python3.4使用MySQL數(shù)據(jù)庫，MySQL Community Server的安裝步驟，具有一定的參考價值，感興趣的小伙伴們可以參考一下
2018-07-07
MySQL多實例管理如何在一臺主機上運行多個mysql
文章詳解了在Linux主機上通過二進制方式安裝MySQL多實例的步驟,涵蓋端口配置、數(shù)據(jù)目錄準備、初始化與啟動流程,以及排錯方法,適用于構建讀寫分離架構,感興趣的朋友一起看看吧
2025-07-07
MySQL別名規(guī)則與使用應用場景
MySQL 起別名是一項功能強大且靈活的特性,通過合理運用列別名和表別名,可以極大地提升查詢語句的質量,下面就來介紹一下MySQL別名規(guī)則與使用應用場景,感興趣的可以了解一下
2025-05-05
centos 7安裝mysql5.5的方法
這篇文章主要介紹了centos 7安裝mysql5.5的方法,需要的朋友可以參考下
2015-09-09
MySQL數(shù)據(jù)庫自動補全命令的三種方法
這篇文章主要介紹了MySQL數(shù)據(jù)庫自動補全命令的三種方法，文中通過示例代碼介紹的非常詳細，對大家的學習或者工作具有一定的參考學習價值，需要的朋友們下面隨著小編來一起學習學習吧
2021-02-02