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ù)庫的內(nèi)容和結(jié)構(gòu)的修改情況記入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日志內(nèi)容

[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*/;

　　日志內(nèi)容部分是不可查看的，這是因為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,精確定位去內(nèi)容
　　4、時間參數(shù) --start-datetime='2017-10-16 17:16:15' --stop-datetime='2017-10-16 17:30:15' 模糊取內(nèi)容，不太精確
　　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;

　　這時候?qū)?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增量備份內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

Ubuntu系統(tǒng)安裝mysql超詳細步驟
這篇文章主要給大家介紹了關于Ubuntu系統(tǒng)安裝mysql的相關資料,現(xiàn)在的軟件越來越好安裝,尤其是在ubuntu下安裝軟件,文中通過圖文介紹的非常詳細,需要的朋友可以參考下
2023-09-09
Mysql經(jīng)典的“8小時問題”
MySQL 的默認設置下,當一個連接的空閑時間超過8小時后,MySQL 就會斷開該連接,而 c3p0 連接池則以為該被斷開的連接依然有效。
2015-04-04
Mysql提權(quán)的多種姿勢匯總
這篇文章主要給大家介紹了關于Mysql提權(quán)的多種姿勢,姿勢包括寫入Webshell、UDF提權(quán)以及MOF提權(quán),文中通過示例代碼介紹的非常詳細,需要的朋友可以參考下
2021-08-08
Linux下MYSQL 5.7 找回root密碼的問題(親測可用)
這篇文章主要介紹了Linux下MYSQL 5.7 找回root密碼的問題(親測可用),通過 --skip-grant-tables 找回,新增完成后，:wq 保存退出，重啟mysqld服務,具體內(nèi)容詳情跟隨小編一起看看吧
2021-10-10
MySQL日期格式化yyyy-mm-dd詳解(DATE_FORMAT()函數(shù))
MySQL提供了很多功能強大、方便易用的函數(shù),在進行數(shù)據(jù)庫管理以及數(shù)據(jù)的查詢和操作時,幫助我們提高對數(shù)據(jù)庫的管理效率,下面這篇文章主要給大家介紹了關于MySQL日期格式化yyyy-mm-dd(DATE_FORMAT()函數(shù))的相關資料,需要的朋友可以參考下
2023-01-01
詳解mysql 獲取當前日期及格式化
本篇文章主要介紹了mysql 獲取當前日期及格式化，具有一定的參考價值，感興趣的小伙伴們可以參考一下。
2016-12-12
mysqldump你可能不知道的參數(shù)
這篇文章主要介紹了mysqldump你可能不知道的參數(shù)，幫助大家更好的理解和使用MySQL，感興趣的朋友可以了解下
2020-11-11
Mysql存儲過程和函數(shù)區(qū)別介紹
這篇文章主要介紹了Mysql存儲過程和函數(shù)的區(qū)別,需要的朋友可以參考下
2014-03-03
MySQL修改字符集的實戰(zhàn)教程
這篇文章主要介紹了MySQL修改字符集的方法，幫助大家更好的理解和使用MySQL數(shù)據(jù)庫，感興趣的朋友可以了解下
2021-01-01
云服務器Ubuntu_Server_16.04.1安裝MySQL并開啟遠程連接的方法
這篇文章主要介紹了云服務器Ubuntu_Server_16.04.1安裝MySQL并開啟遠程連接的方法，非常不錯，具有參考借鑒價值,需要的朋友可以參考下
2018-02-02