MySQL資料庫備份還原（基于binlog的增量備份）

1 簡介

1.1 增量備份簡介

增量備份是指在一次全備份或上一次增量備份後，以後每次的備份隻需備份與前一次相比增加或者被修改的檔案。這就意味着，第一次增量備份的對象是進行全備後所産生的增加和修改的檔案；第二次增量備份的對象是進行第一次增量備份後所産生的增加和修改的檔案，如此類推。這種備份方式最顯著的優點就是：沒有重複的備份資料，是以備份的資料量不大，備份所需的時間很短。但增量備份的資料恢複是比較麻煩的。您必須具有上一次全備份和所有增量備份錄音帶（一旦丢失或損壞其中的一個增量，就會造成恢複的失敗），并且它們必須沿着從全備份到依次增量備份的時間順序逐個反推恢複，是以這就極大地延長了恢複時間。

假如我們有一個資料庫，有20G的資料，每天會增加10M的資料，資料庫每天都要全量備份一次，這樣的話伺服器的壓力比較大，是以我們隻需要備份增加的這部分資料，這樣減少伺服器的負擔。

1.2 binlog簡介

binlog日志由配置檔案的log-bin選項負責啟用，Mysql伺服器将在資料根目錄建立兩個新文件XXX-bin.001和xxx-bin.index，若配置選項沒有給出檔案名，Mysql将使用主機名稱命名這兩個檔案，其中.index檔案包含一份全體日志檔案的清單。 Mysql會把使用者對所有資料庫的内容和結構的修改情況記入XXX-bin.n檔案，而不會記錄 SELECT和沒有實際更新的UPDATE語句。

當MySQL資料庫停止或重新開機時，伺服器會把日志檔案記入下一個日志檔案，Mysql會在重新開機時生成一個新的binlog日志檔案，檔案序号遞增，此外，如果日志檔案超過max_binlog_size系統變量配置的上限時，也會生成新的日志檔案。

2 binlog日志操作

2.1 開啟binlog日志

在my.cnf或my.ini中增加

[mysqld]
log-bin=mysql-bin
binlog_format=row

其中：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)

啟動後會産生mysql-bin.*這樣的檔案，每啟動一次，就會增加一個或者多個。

[[email protected] data]# ls -l| grep mysql-bin
-rw-rw----. 1 mysql mysql       107 Jul  5 11:19 mysql-bin.000001
-rw-rw----. 1 mysql mysql        19 Jul  5 11:19 mysql-bin.index
[[email protected] data]#

檢視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                       | 32768 |
| binlog_direct_non_transactional_updates | OFF   |
| binlog_format                           | ROW |
| binlog_stmt_cache_size                  | 32768 |
+-----------------------------------------+-------+

2.2 檢視binlog日志内容

[[email protected] data]# mysqlbinlog /usr/local/mysql/data/mysql-bin.000001 ;
mysqlbinlog: unknown variable 'default-character-set=utf8'

這裡我們碰到了mysqlbinlog的一個bug，解決方法有兩個：

方法一：使用--no-defaults選項

[[email protected] data]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000001

方法二：将my.cnf中[client]選項組中default-character-set=utf8選項臨時屏蔽掉（該選項即時生效，不用重新開機資料庫）,使用完mysqlbinlog指令時在恢複。因為使用mysqlbinlog工具檢視二進制日志時會重新讀取的mysql的配置檔案my.cnf(windows下是my.ini),而不是伺服器已經加載進記憶體的配置檔案。

輸出格式如下：

# at 188
#140705 11:23:55 server id 1  end_log_pos 271 	Query	thread_id=1	exec_time=0	error_code=0
SET TIMESTAMP=1404573835/*!*/;
create database test
/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET [email protected]_COMPLETION_TYPE*/;

包含以下要素：

Position:位于檔案中的位置，即第一行的（#at 4）和第二行的（log_pos 4），說明該事件記錄從檔案第4位元組開始。

Timestamp:事件發生的時間戳，即第二行的（#070813 14:16:36）

Exec_time:事件的執行花費時間

Error_code:錯誤碼

Type 事件類型：

Master ID:建立二進制事件的主機伺服器ID

Master Pos:事件在原始二進制檔案中的位置

Flags:标志資訊

2.3 一些常用操作

mysql> show master logs;  #檢視資料庫所有日志檔案。
mysql> show binlog events \g;  #檢視目前使用的binlog檔案資訊。
mysql> show binlog events in 'mysql-bin.000016';  #檢視指定的binlog檔案資訊。
mysql> flush logs;  #将記憶體中log日志寫磁盤，儲存在目前binlog檔案中，并産生一個新的binlog日志檔案。
mysql> flush logs; reset master;  #删除所有二進制日志，并重新（mysql-bin.000001）開始記錄。

3 mysql備份執行個體分析（全備+基于binlog的增備）

執行個體采用小資料量進行模拟，包含一份全備及兩份增備，主要示範下備份還原過程，工程中可根據資料實際情況進行備份還原政策調整。

3.1 檢視目前資料庫binlog檔案

通過mysql用戶端檢視

mysql> show master logs;
+------------------+-----------+
| Log_name         | File_size |
+------------------+-----------+
| mysql-bin.000001 |       107 |
+------------------+-----------+

通過linux指令行直接檢視（mysql資料目錄data）

[[email protected] data]# ll -h
total 5.1G
-rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ibdata1
-rw-rw---- 1 mysql mysql 2.0G Jul 18 14:12 ibdata2
-rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ib_logfile0
-rw-rw---- 1 mysql mysql 1.0G Jul 23 13:29 ib_logfile1
drwxr-xr-x 2 mysql mysql 4.0K Jul 18 13:52 mysql
-rw-rw---- 1 mysql mysql  107 Jul 23 13:29 mysql-bin.000001
-rw-rw---- 1 mysql mysql   19 Jul 23 13:29 mysql-bin.index
srwxrwxrwx 1 mysql mysql    0 Jul 18 14:14 mysql.sock
drwx------ 2 mysql mysql 4.0K Jul 18 14:01 performance_schema
-rw-rw---- 1 mysql mysql  483 Jul 23 13:29 R820-08.err
-rw-rw---- 1 mysql mysql    5 Jul 18 14:14 R820-08.pid
drwx------ 2 mysql mysql   19 Jul 22 23:15 test

目前隻有一個binlog檔案mysql-bin.000001。

3.2 準備全量資料

mysql> create database backup_full;
mysql> create table full (c1 int(10), c2 varchar(20)) engine=innodb;
mysql> insert into full values (1, 'full1'),(2, 'full2'),(3, 'full3'),(4, 'full4'),
(5, 'full5'),(6, 'full6'),(7, 'full7'),(8, 'full8'),(9, 'full9'),(10, 'full10');

3.3 将全量資料進行備份

步驟如下：

a.備份前需要将資料庫加讀鎖，防止資料在備份時寫入。

mysql> flush tables with read lock;

b.通過指令flush logs;将log日志刷盤，寫入目前binlog（mysql-bin.000001），在生成一個新的binlog（mysql-bin.000002）為增備做準備。

mysql> flush logs;

c.進行資料備份。在linux指令行下執行：

mysqldump  -u使用者名 -p密碼 -hIP位址 -P端口 資料庫名 > /tmp/backup_full.sql
mysqldump  -uroot -pjesse -h127.0.0.1 -P3355 buckup_full > /tmp/backup_full.sql

d.解除表鎖。

mysql> unlock tables;

至此全量備份全部結束，将全量資料檔案buckup_full.sql儲存即可。資料庫再有新的資料更新會記錄在新的binlog（mysql-bin.000002）裡面。

3.4 準備第一份增量資料

mysql> create database backup_increment;
mysql> use backup_increment;
mysql> create table increment (c1 int(10), c2 varchar(20)) engine=innodb;
mysql> insert into increment values (11, 'increment1'),(12, 'increment2'),(13, 'increment3'),(14, 'increment4'),(15, 'increment5');

3.5 将第一份增量資料進行備份

步驟如下：

a.備份前需要将資料庫加讀鎖，防止資料在備份時寫入。

mysql> flush tables with read lock;

b.通過指令flush logs;将log日志刷盤，寫入目前binlog（mysql-bin.000002），在生成一個新的binlog（mysql-bin.000003）為下次增備做準備。

mysql> flush logs;

c.将binlog第一個增備檔案mysql-bin.000002直接複制儲存即可。

也可以将二進制檔案導出到文本檔案儲存，在linux指令行下執行

mysqlbinlog  mysql-bin.000002 > /tmp/increment1.txt

d.解除表鎖。

mysql> unlock tables;

至此第一個增量備份全部結束，将增量binlog檔案mysql-bin.000002或者有binlog檔案導出的文本檔案/tmp/increment1.txt儲存即可。資料庫再有新的資料更新會記錄在新的binlog（mysql-bin.000003）裡面。

3.6 準備第二份增量資料

mysql> use backup_increment;
mysql> insert into increment values (16, 'increment16'),(17, 'increment17'),(18, 'increment18'),(19, 'increment19'),(20, 'increment20');

3.7 将第二份增量資料進行備份

步驟如下：

a.備份前需要将資料庫加讀鎖，防止資料在備份時寫入。

mysql> flush tables with read lock;

b.通過指令flush logs;将log日志刷盤，寫入目前binlog（mysql-bin.000003），在生成一個新的binlog（mysql-bin.000004）為下次增備做準備。

mysql> flush logs;

c.将binlog第一個增備檔案mysql-bin.000003直接複制儲存即可。

也可以将二進制檔案導出到文本檔案儲存，在linux指令行下執行

mysqlbinlog  mysql-bin.000003 > /tmp/increment2.txt

d.解除表鎖。

mysql> unlock tables;

至此第二個增量備份全部結束，将增量binlog檔案mysql-bin.000003或者有binlog檔案導出的文本檔案/tmp/increment2.txt儲存即可。資料庫再有新的資料更新會記錄在新的binlog（mysql-bin.000004）裡面。

4 mysql還原執行個體分析（全備還原+基于binlog的增備還原）

模拟資料庫故障，即删除全備資料及增備資料庫。

mysql> drop table backup_full.full;
mysql> drop database backup_increment;

此時資料庫資料被清空。

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| backup_full        |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> select * from backup_full.full;
ERROR 1146 (42S02): Table 'backup_full.full' doesn't exist
mysql> select * from backup_increment.increment;
ERROR 1146 (42S02): Table 'backup_increment.increment' doesn't exist

4.1 還原全備資料

方法1：進入資料庫，通過source

mysql> use backup_full;
mysql> source /tmp/backup_full.sql;
mysql> select * from backup_full.full;
+------+--------+
| c1   | c2     |
+------+--------+
|    1 | full1  |
|    2 | full2  |
|    3 | full3  |
|    4 | full4  |
|    5 | full5  |
|    6 | full6  |
|    7 | full7  |
|    8 | full8  |
|    9 | full9  |
|   10 | full10 |
+------+--------+

全量資料還原成功

方法2：直接還原資料檔案。

mysql -u使用者名 -p密碼  -hIP位址    -P端口 資料庫名     < /tmp/buckup_full.sql
mysql -uroot   -pjesse -h127.0.0.1 -P3355 backup_full  < /tmp/buckup_full.sql
mysql> select * from backup_full.full;
+------+--------+
| c1   | c2     |
+------+--------+
|    1 | full1  |
|    2 | full2  |
|    3 | full3  |
|    4 | full4  |
|    5 | full5  |
|    6 | full6  |
|    7 | full7  |
|    8 | full8  |
|    9 | full9  |
|   10 | full10 |
+------+--------+

4.2 還原第一個增備檔案

方法一：通過文本檔案還原

mysql> source /tmp/increment1.txt;
mysql> select * from backup_increment.increment;
+------+------------+
| c1   | c2         |
+------+------------+
|   11 | increment1 |
|   12 | increment2 |
|   13 | increment3 |
|   14 | increment4 |
|   15 | increment5 |
+------+------------+

方法二：通過binlog直接還原

在linux指令行下執行：

mysqlbinlog  binlog檔案名 | mysql -u使用者名 -p密碼 -hIP位址 -P端口
mysqlbinlog  mysql-bin.000002 | mysql -uroot -pjesse -h127.0.0.1 -P3355

檢視資料：

mysql> select * from backup_increment.increment;
+------+------------+
| c1   | c2         |
+------+------------+
|   11 | increment1 |
|   12 | increment2 |
|   13 | increment3 |
|   14 | increment4 |
|   15 | increment5 |
+------+------------+

第一份增量資料還原成功！

4.3 還原第二個增備檔案（方法同上）

在linux指令行下執行：

mysqlbinlog  binlog檔案名 | mysql -u使用者名 -p密碼 -hIP位址 -P端口
mysqlbinlog  mysql-bin.000003 | mysql -uroot -pjesse -h127.0.0.1 -P3355

檢視資料：

mysql> select * from backup_increment.increment;
+------+-------------+
| c1   | c2          |
+------+-------------+
|   11 | increment1  |
|   12 | increment2  |
|   13 | increment3  |
|   14 | increment4  |
|   15 | increment5  |
|   16 | increment16 |
|   17 | increment17 |
|   18 | increment18 |
|   19 | increment19 |
|   20 | increment20 |
+------+-------------+

至此資料全部還原成功！

****************************************************************************************

原文位址： http://blog.csdn.net/jesseyoung/article/details/37106035

部落格首頁： http://blog.csdn.net/jesseyoung

****************************************************************************************

MySQL資料庫備份還原（基于binlog的增量備份）

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案