#yyds幹貨盤點#--mysql備份恢複兩款常用工具

一、備份的類型

冷備份：關閉資料、停止業務
溫備份：加鎖備份
熱備份：在先備份，不會影響到也正常運作

二、備份方式

2.1 邏輯備份：基于SQL語句的備份

mysqldump建庫、建表、資料插入
基于二進制日志：資料庫的所有變化類的操作
基于複制的備份：将二進制日志實時傳送到另一台機器并且恢複

2.2 實體備份

xtrabackup進行實體備份
拷貝資料檔案（冷備）

三、備份工具

mysqldump （mysql原生自帶很好用的邏輯備份工具）
mysqlbinlog （實作binlog備份的原生态指令）
xtrabackup （precona公司開發的性能很高的實體備份工具）

四、mysqldump備份工具使用

優點：邏輯備份工具，都是SQL語句，都是文本格式，便于檢視和編輯，更便于壓縮

缺點：備份效率較慢

mysqldump常用參數：

-u -p -h -S -P

五、備份案例

5.1 全庫備份

-A --->全庫備份     
例子：
mysqldump -uroot -p123 -A >/backup/full.sql

5.2 單庫備份

例子：
 mysqldump -uroot -p123 -B lufei > /backup/lufei.sql  ---> -B 表示增加建庫和use的語句
 mysqldump -uroot -p123  lufei > /backup/lufei1.sql

說明：-B,增加建庫（create）及“use庫”的語句，在将來恢複時，不需要手工進行建庫和use 不加-B，需要恢複時，先建立庫，use庫下再進行恢複另外，-B選項還可以實作，同時備份多個庫，備份到同一個檔案中 mysqldump -uroot -p123 -B lufei oldboy>/backup/lufei_oldboy.sql 以下例子，如果不加-B,去備份，他的功能是備份路飛資料庫下的oldboy表 mysqldump -uroot -p123 lufei oldboy>/backup/lufei_oldboy.sql 文法：mysqldump 庫1 表1 表2 表3 >庫1.sql

注意：生産環境下，也要加的額外參數：

-R,       備份存儲過程和函數資料
--triggers, 備份觸發器資料
例如：
mysqldump -uroot -p123 -A  -R --triggers >/backup/full.sql

5.3 記錄備份時刻binlog的位置

-F, --flush-logs   重新整理binlog日志，為了友善将來二進制日志截取時的起點
mysqldump -uroot -p123 -A -F >/backup/full.sql

--master-data={1|2}     告訴你備份時刻的binlog位置，一般我們選擇使用2，以注釋的方式記錄二進制日志位置

5.4 鎖表備份和熱備

鎖表:适合所有引擎(myisam,innodb)
  -x, --lock-all-tables 
  -l, --lock-tables

--single-transaction  對innodb引擎進行熱備
通過快照的方式實作熱備

5.5 一個生産例子壓縮備份:(全庫備份)

mysqldump -uroot -p123 -A  -R --triggers --master-data=2 --single-transaction |gzip >/backup/all_$(date +%F).sql.gz

六、mysqldump+binlog恢複案例

6.1 背景環境：

正在運作的網站系統，mysql資料庫，資料量25G，日業務增量10-15M。
備份方式：
  每天23:00點，計劃任務調用mysqldump執行全備腳本
故障時間點：
  上午10點，誤删除了一個表
如何恢複？

6.2 思路

1、斷開業務，防止對資料庫二次傷害，挂出維護頁面
  2、搭建備用庫，恢複全備
  3、截取昨天晚上23:00之後到上午10點誤删除操作之前的二進制日志
  4、恢複到備用庫，驗證資料可用性和完整性
  5、兩種方案恢複前端應用
    5.1 備用庫導出誤删除的表，導入到生産庫，開啟業務
    5.2 直接将應用切割到備用庫，替代生産庫，開啟業務

6.3 模拟故障并恢複：

1、原始資料：
mysql> create database oldboy;
mysql> use oldboy
mysql> create table t1 (id int,name varchar(20));
mysql> insert into t1 values (1,'zhang3');
mysql> insert into t1 values (2,'li4');
mysql> insert into t1 values (3,'wang5');
mysql> commit;
2、模拟前一天晚上23:00全備
mysqldump  -A  -R --triggers --master-data=2 --single-transaction |gzip >/backup/all_$(date +%F).sql.gz
3、模拟白天（23:00-10:00）業務對資料的修改
mysql> insert into t1 values (4,'zhang33');
mysql> insert into t1 values (5,'li44');
mysql> insert into t1 values (6，'wang54');
mysql> commit;
4、模拟故障
drop table t1;
5、恢複
（1）準備全備，并擷取到備份檔案中的binlog的截取起點
gunzip all_2018-04-04.sql.gz
  -- CHANGE MASTER TO MASTER_LOG_FILE='my-bin.000004', MASTER_LOG_POS=731;
（2）截取二進制日志
mysqlbinlog --start-position=731  --stop-position=1126 /data/binlog/my-bin.000004 >/backup/binlog.sql
-----
 show binlog events in 'my-bin.000004';  ----》drop之前的position為1126 
-----
(3)恢複全備+binlog
set sql_log_Bin=0;
source /backup/all_2018-04-04.sql;
source /backup/binlog.sql

七、 Xtrabackup實體備份工具

7.1 Xtrabackup介紹

percona公司的備份工具，性能比較高。實體備份工具。

Xtrabackup的特點：

實體備份工具，在同級資料量基礎上，都要比邏輯備份性能要好的多。
特别是在資料量比較大的時候，展現的更加明顯。
備份方式：
  1、拷貝資料檔案
  2、拷貝資料頁
備份原理（innodb）：
  1、對于innodb表，可以實作熱備
    （1）在資料還有修改操作的時刻，直接将資料檔案中的資料頁備份
    此時，備份走的資料對于目前mysql來講是不一緻。
    （2） 将備份過程中的redo和undo一并備走。
    （3）為了恢複的時候，隻要保證備份出來的資料頁LSN能和redo LSN比對，
      将來恢複的就是一緻的資料。redo應用和undo的應用。
  2、對與myisam表，實作自動鎖表拷貝檔案。

7.2 Xtrabackup軟體安裝：

1、安裝

wget -O /etc/yum.repos.d/epel.repo  http://mirrors.aliyun.com/repo/epel-6.repo   //--->下載下傳依賴包的yum源
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL    //---> 安裝依賴包
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm  
yum -y install percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm     //--->安裝Xtraback

2、全備備份并恢複

mkdir /server/backup -p

常用參數

--user=
--password=
--socket=
--no-timestamp --不使用時間挫

全備例子：

innobackupex    --user=root --password=123   /server/backup/
innobackupex    --user=root --password=123   --no-timestamp  /server/backup/

全備恢複例子：

1、恢複資料前的準備(合并xtabackup_log_file和備份的實體檔案)

-apply-log：此選項作用是通過復原未送出的事務及同步已經送出的事務至資料檔案使資料檔案處于一緻性狀态
use-memory：該選項表示和--apply-log選項一起使用，prepare 備份的時候，xtrabackup做crash recovery配置設定的記憶體大小，機關位元組。也可(1MB,1M,1G,1GB)，推薦1G

innobackupex --apply-log --use-memory=32M /server/backup/  # 必須做的準備操作

2、模拟故障

停庫:
pkill mysqld
破壞資料：
cd /application/mysql/data
\rm -rf *

3、恢複

cp -a /server/backup/*  /application/mysql/data
或者
innobackupex --copy-back  /server/backup/full/

注意：恢複時，要确認資料路徑是空的，并且資料庫是停掉的

4、重新賦予權限

chown -R mysql.mysql /application/mysql/data

5、啟動并驗證

/etc/init.d/mysqld start
mysql -e "select * from test.t1"

7.3 xtrabackup 實作增量備份及故障恢複案例

周日全備，周一到周六做增量

1、周日全備：

mkdir /backup/full
innobackupex --user=root --password=123 --no-timestamp /backup/full/

2、模拟資料變化（周一資料變化）。。。

3、第一增量（周一晚上增量）：

innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/full/ /backup/inc1

4、模拟資料變化（周二資料變化）。。。

5、第二次增量（周二晚上增量）：

innobackupex --user=root --password=123 --incremental --no-timestamp --incremental-basedir=/backup/inc1 /backup/inc2

6、周三上午做了n多的操作，10:00時刻,誤删除t1表

7、在準備的備庫中恢複資料：

innobackupex --apply-log  --redo-only /backup/full 
innobackupex --apply-log  --redo-only --incremental-dir=/backup/inc1 /backup/full
innobackupex --apply-log  --incremental-dir=/backup/inc2 /backup/full 
innobackupex --apply-log  /backup/full
----準備工作，将日志進行合并

innobackupex --copy-back  /backup/full/   ---> cop備份到MySQL資料目錄下

8、截取第二次備份結束的position号誤删除之前的position号

[root@test01 inc2]# cat xtrabackup_binlog_info 
my-bin.000042 1253
mysqlbinlog --start-position=1253  --stop-position=1881 /data/binlog/my-bin.000042 >/backup/binlog.sql
mysql> source /backup/binlog.sql

生産恢複案例：
背景：
1、xtrabackup備份政策每周日，full全備
2、xtrabackup周一到周六，inc1-inic6
3、總資料量200G
4、周三上午10點誤删除表t1，資料量1G左右
5、周二晚上inc2備份完成之後到周三上午10點又做了很多操作
如何将資料庫恢複到t1表誤删除之前狀态？
思路：
  1、停業務，挂維護頁
  2、找備用庫
  3、合并full+inc1+inc2
  4、截取周二晚上inc2備份後到周三上午10點，t1表删除之前的binlog日志
  5、将合并後的full+截取的binlog恢複到備用庫
  6、驗證資料可用性和完整性
  7、使用備用庫替代生産庫使用或者将t1表導出并導入回生産庫
  8、業務恢複
-----------------

以上恢複政策可以做如下優化

建立一個和删前表結構一模一樣的表
discard 掉他的表空間檔案
将合并後的資料庫對應的ibd檔案cp到mysql資料目錄下并修改相應的權限
最後導入表空間即可
通過二進制截取的方式截取誤删除前最後一次備份後的position号，并恢複

drop table t1;
create table t1 (id int,name varchar(20));
alter table t1 discard tablespace;
cd /application/mysql/data/test
cp /backup/full/test/t1.ibd  ./
chown  -R mysql.mysql *
alter table t1 import tablespace;
select * from t1

#yyds幹貨盤點#--mysql備份恢複兩款常用工具

一、備份的類型

二、備份方式

2.1 邏輯備份：基于SQL語句的備份

2.2 實體備份

三、備份工具

四、mysqldump備份工具使用

五、備份案例

5.1 全庫備份

5.2 單庫備份

5.3 記錄備份時刻binlog的位置

5.4 鎖表備份和熱備

5.5 一個生産例子壓縮備份:(全庫備份)

六、mysqldump+binlog恢複案例

6.1 背景環境：

6.2 思路

6.3 模拟故障并恢複：

七、 Xtrabackup實體備份工具

7.1 Xtrabackup介紹

Xtrabackup的特點：

7.2 Xtrabackup軟體安裝：

7.3 xtrabackup 實作增量備份及故障恢複案例

繼續閱讀

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檔案

#yyds幹貨盤點#--mysql備份恢複兩款常用工具

一、備份的類型

二、備份方式

2.1 邏輯備份：基于SQL語句的備份

2.2 實體備份

三、 備份工具

四、mysqldump備份工具使用

五、備份案例

5.1 全庫備份

5.2 單庫備份

5.3 記錄備份時刻binlog的位置

5.4 鎖表備份和熱備

5.5 一個生産例子壓縮備份:(全庫備份)

六、mysqldump+binlog恢複案例

6.1 背景環境：

6.2 思路

6.3 模拟故障并恢複：

七、 Xtrabackup實體備份工具

7.1 Xtrabackup介紹

Xtrabackup的特點：

7.2 Xtrabackup軟體安裝：

7.3 xtrabackup 實作增量備份及故障恢複案例

繼續閱讀

三、備份工具