1.運維在數據庫備份恢復方面的職責

1.設計備份策略

全備 、增量、時間、自動

2.日常備份檢查

備份存在性

備份空間夠用否

3.定期恢復演練(測試庫)

一季度 或者 半年

4.故障恢復

通過現有備份,能夠將數據庫恢復到故障之前的時間點.

5.遷移

1. 停機時間

2. 回退方案

2.Mysql數據損壞類型

1.物理損壞

磁盤損壞：硬件，磁道壞，dd，格式化

文件損壞：數據文件損壞，redo損壞

2.邏輯損壞

drop

delete

truncate

update

3. 備份類型

1.熱備

在數據庫正常業務時,備份數據,并且能夠一致性恢復(只能是innodb)

對業務影響非常小

2.溫備

鎖表備份,只能查詢不能修改(myisam)

影響到寫入操作

3.冷備

關閉數據庫業務,數據庫沒有任何變更的情況下,進行備份數據.

業務停止

4. 備份方式及工具介紹

1.邏輯備份工具

基于SQL語句進行備份

mysqldump *****

mysqlbinlog *****

2. 物理備份工具

基于磁盤數據文件備份

xtrabackup(XBK) ：percona 第三方 *****

MySQL Enterprise Backup(MEB)

5. 邏輯備份和物理備份的比較

mysqldump (MDP)

優點：

1.不需要下載安裝

2.備份出來的是SQL，文本格式，可讀性高,便于備份處理

3.壓縮比較高，節省備份的磁盤空間

缺點：

依賴于數據庫引擎，需要從磁盤把數據讀出，然后轉換成SQL進行轉儲，比較耗費資源，數據量大的話效率較低

建議：

100G以內的數據量級，可以使用mysqldump

超過TB以上，我們也可能選擇的是mysqldump，配合分布式的系統

1EB ?=1024 PB =1000000 TB

6.備份策略

備份方式：

全備:全庫備份，備份所有數據

增量:備份變化的數據

備份工具

邏輯備份=mysqldump+mysqlbinlog

物理備份=xtrabackup_full+xtrabackup_incr+binlog或者xtrabackup_full+binlog

備份周期:

根據數據量設計備份周期

比如：周日全備，周1-周6增量

備份監控

備份空間

備份日志

7.容災策略

備份

架構

高可用? ? ? ? ?負載均衡

演示從庫? ? ? ? ?主從同步

災備庫? ? ? ? ? ?異地備份

定期的故障恢復演練

8.mysqldump應用

介紹：邏輯備份工具。備份的是sql語句

備份方式

InnoDB

可以采取快照備份的方式

開啟一個獨立的事務，獲取當前最新的一致性快照，將快照數據，放在臨時表中，轉換成SQL(Create database，create table，insert)，保存到sql文件中

非InnoDB

需要鎖表備份。觸發FTWRL，全局鎖表。

將快照數據，放在臨時表中，轉換成SQL(Create database，create table，insert)，保存到sql文件中

核心參數

建立備份目錄，授權

mkdir -p /data/backup

chown -R mysql.mysql /data/backup/

連接參數

-u ?#用戶

-p ?#密碼

-h ? #主機地址

-P ? #端口

-S ?#socket連接

-A ? #全備mysqldump -uroot -proot -A >/data/backup/full.sql

-B ?#備份一個或者多個mysqldump -uroot -proot -B gtid gtid2 >/data/backup/db.sql

備份庫下的多個表(不加參數)

mysqldump -uroot -proot gtid t1 t1 >/data/backup/db.sql

備份高級參數

--master-data=2

以注釋的形式,保存備份開始時間點的binlog的狀態信息

mysqldump -uroot -p -A -R --triggers --master-data=2 >/back/world.sql

[root@db01 ~]# grep 'CHANGE' /backup/world.sql

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000035', MASTER_LOG_POS=194;

功能：

(1)在備份時，會自動記錄，二進制日志文件名和位置號

0 默認值

1 以change master to命令形式，可以用作主從復制

2 以注釋的形式記錄，備份時刻的文件名+postion號

(2) 自動鎖表

(3)如果配合--single-transaction，只對非InnoDB表進行鎖表備份，InnoDB表進行“熱“”備，實際上是實現快照備份。

--single-transaction

innodb 存儲引擎開啟熱備(快照備份)功能

master-data可以自動加鎖

(1)在不加--single-transaction ，啟動所有表的溫備份，所有表都鎖定

(1)加上--single-transaction ,對innodb進行快照備份,對非innodb表可以實現自動鎖表功能

例子6: 備份必加參數

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

--set-gtid-purged=auto

auto , on

off

使用場景:

1. --set-gtid-purged=OFF,可以使用在日常備份參數中.

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql

2. auto , on:在構建主從復制環境時需要的參數配置

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=ON >/data/backup/full.sql

--max-allowed-packet=#

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql

--max-allowed-packet=#

The maximum packet length to send to or receive from server.

-R ? ? ? ? ? ?備份存儲過程及函數

--triggers ?備份觸發器

-E ? ? ? ? ? ? 備份事件

生產備份語句(只是建議，根據自己需求來改)

mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql

練習? ?實現所有表的單獨備份

提示：

information_schema.tables

mysqldump -uroot -p123 world city >/backup/world_city.sql

select concat("mysqldump -uroot -p123 ",table_schema," ",table_name," --master-data=2 --single-transaction --set-gtid-purged=0 -R -E --triggers>/backup/",table_schema,"_",table_name,".sql") from information_schema.tables where table_schema not in ('sys','information_schema','performance_schema');

9. 備份時優化參數:

(1) max_allowed_packet 最大的數據包大小

mysqldump -uroot -p123 -A -R --triggers --set-gtid-purged=OFF --master-data=2 max_allowed_packet=128M --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

(2) 增加key_buffer_size (臨時表有關)

(3) 分庫分表并發備份 (作業)

(4) 架構分離,分別備份 (架構拆分,分布式備份)

10. MySQL物理備份工具-xtrabackup(XBK、Xbackup)

安裝并安裝

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

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.el7.x86_64.rpm

備份命令介紹:

xtrabackup

innobackupex ******

備份方式——物理備份

(1)對于非Innodb表(比如 myisam)是，鎖表cp數據文件，屬于一種溫備份。

(2)對于Innodb的表(支持事務的)，不鎖表，拷貝數據頁，最終以數據文件的方式保存下來，把一部分redo和undo一并備走，屬于熱備方式。

xbk 在innodb表備份恢復的流程

0、xbk備份執行的瞬間,立即觸發ckpt,已提交的數據臟頁,從內存刷寫到磁盤,并記錄此時的LSN號

1、備份時，拷貝磁盤數據頁，并且記錄備份過程中產生的redo和undo一起拷貝走,也就是checkpoint LSN之后的日志

2、在恢復之前，模擬Innodb“自動故障恢復”的過程，將redo(前滾)與undo(回滾)進行應用

3、恢復過程是cp 備份到原來數據目錄下

innobackupex使用

全備

innobackupex --user=root --password=123 /data/backup

自主定制備份路徑名

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

備份集中多出來的文件：

-rw-r----- 1 root root 24 Jun 29 09:59 xtrabackup_binlog_info

-rw-r----- 1 root root 119 Jun 29 09:59 xtrabackup_checkpoints

-rw-r----- 1 root root 489 Jun 29 09:59 xtrabackup_info

-rw-r----- 1 root root 2560 Jun 29 09:59 xtrabackup_logfile

xtrabackup_binlog_info ：(備份時刻的binlog位置)

[root@db01 full]# cat xtrabackup_binlog_info

mysql-bin.000003 536749

79de40d3-5ff3-11e9-804a-000c2928f5dd:1-7

記錄的是備份時刻，binlog的文件名字和當時的結束的position，可以用來作為截取binlog時的起點。

xtrabackup_checkpoints ：

backup_type = full-backuped

from_lsn = 0 上次所到達的LSN號(對于全備就是從0開始,對于增量有別的顯示方法)

to_lsn = 160683027 備份開始時間(ckpt)點數據頁的LSN

last_lsn = 160683036 備份結束后，redo日志最終的LSN

compact = 0

recover_binlog_info = 0

(1)備份時刻，立即將已經commit過的，內存中的數據頁刷新到磁盤(CKPT).開始備份數據，數據文件的LSN會停留在to_lsn位置。

(2)備份時刻有可能會有其他的數據寫入，已備走的數據文件就不會再發生變化了。

(3)在備份過程中，備份軟件會一直監控著redo的undo，如果一旦有變化會將日志也一并備走，并記錄LSN到last_lsn。

從to_lsn ----》last_lsn 就是，備份過程中產生的數據變化.

innobackupex 增量備份(incremental)

(1)增量備份的方式，是基于上一次備份進行增量。

(2)增量備份無法單獨恢復。必須基于全備進行恢復。

(3)所有增量必須要按順序合并到全備中。

增量備份命令

(1)刪掉原來備份

略.

(2)全備(周日)

[root@db01 backup]# innobackupex --user=root --password --no-timestamp /backup/full >&/tmp/xbk_full.log

(3)模擬周一數據變化

db01 [(none)]>create database cs charset utf8;

db01 [(none)]>use cs

db01 [cs]>create table t1 (id int);

db01 [cs]>insert into t1 values(1),(2),(3);

db01 [cs]>commit;

(4)第一次增量備份(周一)

innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1 &>/tmp/inc1.log

(5)模擬周二數據

db01 [cs]>create table t2 (id int);

db01 [cs]>insert into t2 values(1),(2),(3);

db01 [cs]>commit;

(6)周二增量

innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/inc1 /backup/inc2 &>/tmp/inc2.log

(7)模擬周三數據變化

db01 [cs]>create table t3 (id int);

db01 [cs]>insert into t3 values(1),(2),(3);

db01 [cs]>commit;

db01 [cs]>drop database cs;

恢復到周三誤drop之前的數據狀態

恢復思路：

1. 掛出維護頁，停止當天的自動備份腳本

2. 檢查備份：周日full+周一inc1+周二inc2，周三的完整二進制日志

3. 進行備份整理(細節)，截取關鍵的二進制日志(從備份——誤刪除之前)

4. 測試庫進行備份恢復及日志恢復

5. 應用進行測試無誤，開啟業務

6. 此次工作的總結

恢復過程

1. 檢查備份

1afe8136-601d-11e9-9022-000c2928f5dd:7-9

2. 備份整理(apply-log)+合并備份(full+inc1+inc2)

(1) 全備的整理

[root@db01 one]# innobackupex --apply-log --redo-only /data/backup/full

(2) 合并inc1到full中

[root@db01 one]# innobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1 /data/backup/full

(3) 合并inc2到full中

[root@db01 one]# innobackupex --apply-log --incremental-dir=/data/backup/inc2 /data/backup/full

(4) 最后一次整理全備

[root@db01 backup]# innobackupex --apply-log /data/backup/full

3. 截取周二 23:00 到drop 之前的 binlog

[root@db01 inc2]# mysqlbinlog --skip-gtids --include-gtids='1afe8136-601d-11e9-9022-000c2928f5dd:7-9' /data/binlog/mysql-bin.000009 >/data/backup/binlog.sql

4. 進行恢復

[root@db01 backup]# mkdir /data/mysql/data2 -p

[root@db01 full]# cp -a * /data/mysql/data2

[root@db01 backup]# chown -R mysql. /data/*

[root@db01 backup]# systemctl stop mysqld

vim /etc/my.cnf

datadir=/data/mysql/data2

systemctl start mysqld

Master [(none)]>set sql_log_bin=0;

Master [(none)]>source /data/backup/binlog.sql

下面是我畫的思維導圖，可以很快加強記憶。

總結

以上是生活随笔為你收集整理的mysql dump xtrabackup_MySQL--备份恢复【Mysqdump+xtrabackup（XBK）】的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。