十一、mysql 備份恢復
課程大綱
1、備份的原因
2、備份的方式
3、備份的工具
4、mysqldump備份工具的詳解
5、mysqldump+mysqlbinlog實作增量備份
6、企業級備份策略及恢復案例
7、xtrabackup備份恢復實戰
運維作業的核心簡單概括就兩件事：
第一個是保護公司的資料，
第二個是讓網站能7*24小時提供服務(用戶體驗)，
1、備份的型別
冷備份：關閉資料、停止業務
溫備份：枷鎖備份
熱備份：在線備份，不會影響業務，
2、備份方式
邏輯備份：
基于sql陳述句的備份：
①mysqldump--》建庫，建表，資料插入
②基于二進制日志：資料庫的所有變化類的操作，
③基于復制的備份：將二進制日志實時傳遞到另一臺機器并且恢復，
物理備份：
①xtrabackup 進行物理備份
②拷貝資料檔案（冷備）
3、備份工具：
①mysqldump
mysql原生自帶很好用的邏輯備份工具
②mysqlbinlog（根據始末position位置進行截取備份）
實作binlog備份的原生態命令
③xtrabackup
percona公司開發的性能很高的物理備份工具
mysqldump備份工具優缺點：
優點：邏輯備份工具，都是sql陳述句，都是文本格式，便于查看和編輯，便于壓縮，
缺點：備份較慢，效率低，
mysqldump引數介紹：
-u -p -S -h -P
-A, 全庫備份
例子：
[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -A >/backup/mysqlfull.sql
單庫備份：
-B,增加建庫（create）及’use庫‘的陳述句，在將來恢復時，不需要手工建庫和use庫，
-B 選項還可以實作，同時備份多個庫，備份到同一個檔案中，（空格隔開）
[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -B lufei >/backup/mysqllufei.sql
[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 lufei >/backup/mysqllufei1.sql
不加-B，恢復時候需要先創建庫，use庫下再進行恢復，
不加-B ,去備份他庫下的一個單表的意思，
生成環境下，也要加的額外引數：
-R:備份存盤程序和函式資料，
--triggers :備份觸發器資料，
-F，--flush-logs ：重繪binlog日志，為了方便將來二進制日志截取時的起點，
mysqldump -uroot -poldboy -A -F >/backup/mysqlfull.sql
--master-data=https://www.cnblogs.com/cuiyongchao007/p/{1|2} :告訴你備份時刻的binlog位置，一般我們選擇2，以注釋的方式記錄二進制日志的位置，
mysql> show master status;
鎖表：適合所有引擎（myisam,innodb）
-x,--lock-all-tables
-l,--lock-tables
--single-transaction 對innodb引擎進行熱備
[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -A --master-data=https://www.cnblogs.com/cuiyongchao007/p/2 --single-transaction >/backup/mysqlfull.sql
通過快照的方式實作熱備，
壓縮備份：
[root@centos6-kvm3 ~]# mysqldump -uroot -poldboy123 -A -R --triggers --master-data=https://www.cnblogs.com/cuiyongchao007/p/2 --single-transaction | gzip >/backup/mysqlfull_$(date +%F).sql
4、mysqldump+binlog企業恢復實戰
使用source 命令進行恢復
set sql_log_bin=0;（臨時關閉二進制日志，防止恢復操作記錄到二進制日志中）
source /opt/xxx.sql;
企業實體：
背景環境：
正在運行的網站系統，mysql資料庫，資料量25G，日業務增量10-15M，
備份方式：
每天23:00點，計劃任務呼叫mysqldump執行全備腳本，
故障時間點：
上午10點，誤洗掉了一個表，
如何恢復？
思路：
①斷開業務，防止對資料庫二次傷害，掛出維護頁面，
②搭建備用庫，恢復全備，
③截取昨天晚上23:00之后到上午10點誤洗掉操作之前的二進制日志，
④恢復到備用庫，驗證資料可用性和完整性，
⑤兩種恢復前端應用，
5.1備用庫匯出誤洗掉的表，匯入到生產庫，開啟業務，
5.2直接將應用切割刀備用庫，替代生產庫，開啟業務，
模擬故障并恢復：
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=https://www.cnblogs.com/cuiyongchao007/p/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
5、xtrabackup 介紹
Xtrabackup物理備份工具
percona公司的備份工具，性能比較高，物理備份工具，
特點：
物理備份工具，在同級資料量基礎上，都要比邏輯備份性能要好的多，
特別是在資料量比較大的時候，體現的更加明顯，
備份方式：
1、拷貝資料檔案
2、拷貝資料頁
備份原理（innodb）：
1、對于innodb表，可以實作熱備
（1）在資料還有修改操作的時刻，直接將資料檔案中的資料頁備份
此時，備份走的資料對于當前mysql來講是不一致，
（2） 將備份程序中的redo和undo一并備走，
（3）為了恢復的時候，只要保證備份出來的資料頁LSN能和redo LSN匹配，
將來恢復的就是一致的資料，redo應用和undo的應用，
2、對與myisam表，實作自動鎖表拷貝檔案，
Xtrabackup軟體安裝：
1、安裝
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-6.repo
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
2、全備備份并恢復
mkdir /server/backup -p
--user=
--password=
--socket=
--no-timestamp
全備例子：
innobackupex /server/backup/
innobackupex --no-timestamp /server/backup/full
全備恢復例子：
1、恢復資料前的準備(合并xtabackup_log_file和備份的物理檔案)
innobackupex --apply-log --use-memory=32M /server/backup/full/
2、模擬故障
停庫:
pkill mysqld
破壞資料：
cd /application/mysql/data
\rm -rf *
3、恢復
cp -a /server/backup/full/* /application/mysql/data
或者
innobackupex --copy-back /server/backup/full/
注意：恢復時，要確認資料路徑是空的，并且資料庫是停掉的
chown -R mysql.mysql /application/mysql/data
啟動：
/etc/init.d/mysqld start
mysql -e "select * from oldboy.t1"
6、xtrabackup 實作增量備份及故障恢復
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
7、xtrabackup生產恢復案例：
背景：
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恢復到備用庫
（ 根據備份日志最后的position，再根據show binlog events in 'my-bin.000004';獲取最后的position）
6、驗證資料可用性和完整性
7、使用備用庫替代生產庫使用或者將t1表匯出并匯入回生產庫
8、業務恢復
-----------------
-----------------
思考：以上恢復策略是否可以優化？
為了恢復1G表，需要將整個全備恢復，有必要嗎？有什么好的解決辦法？
單獨恢復一個表：
drop table t1;
create table t1 (id int,name varchar(20));
alter table t1 discard tablespace;
cd /application/mysql/data/oldboy
cp /backup/full/oldboy/t1.ibd ./
chown -R mysql.mysql *
alter table t1 import tablespace;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/44462.html
標籤：MySQL
下一篇：第五天MYSQL