1. 上節(jié)回顧:
1. binlog
show master status;
oldguo[(none)]>show master status;
+------------------+----------+--------------+------------------+----------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+----------------------------------------+
| mysql-bin.000013 | 194 | | | 545fd699-be48-11e9-8f0a-000c2980e248:1 |
+------------------+----------+--------------+------------------+----------------------------------------+
oldguo[(none)]>show binlog events in 'mysql-bin.000012';
mysqlbinlog --start-position= --stop-position /data/binlog/mysql-bin.000004 >/backup/binlog.sql
set sql_log_bin=0;
source /backup/binlog.sql;
set sql_log_bin=1;
exit
mysqlbinlog --skip-gtids --include-gtids='545fd699-be48-11e9-8f0a-000c2980e248:1-10' --exclude-gtids=''
2. slowlog
3. mysqldump
-u -p -h -P -S
-A -B
-R -E --triggers
--master-data=2
--single-transaction
--set-gtid-purged=OFF
--max-allowed-packet=256M
-------- __@ __@ __@ __@ __~@ __~@
----- _`\<,_ _`\<,_ _`\<,_ _`\<,_ _`\<,_ _`\<,_
---- (*)/ (*) (*)/ (*) (*)/ (*) (*)/ (*) (*)/ (*) (*)/ (*) 下一知識(shí)點(diǎn)
2. 企業(yè)故障恢復(fù)案例(MDP)
2.1 背景環(huán)境：
正在運(yùn)行的小型網(wǎng)站系統(tǒng)，mysql-5.7.20 數(shù)據(jù)庫(kù)囱修，數(shù)據(jù)量 50G.
每天23:00點(diǎn)第队，計(jì)劃任務(wù)調(diào)用mysqldump執(zhí)行全備腳本
2.2 故障時(shí)間點(diǎn)：
o(*￣︶￣*)o 面試保持專業(yè)性，此案例面試可以說(shuō)為 故障演練
年底故障演練:模擬周三上午10點(diǎn)誤刪除數(shù)據(jù)庫(kù)，并進(jìn)行恢復(fù).
2.3 恢復(fù)思路
1. 停止故障業(yè)務(wù)陈瘦，掛維護(hù)頁(yè)
2. 準(zhǔn)備測(cè)試庫(kù)癣诱，進(jìn)行全庫(kù)恢復(fù)
3. 截取從昨晚23:00的全備開(kāi)始一直到故障時(shí)刻的 binlog日志
4. 測(cè)試數(shù)據(jù)的可用性和完整性
5. 將故障數(shù)據(jù)導(dǎo)出，導(dǎo)入到生產(chǎn)環(huán)境中
6. 撤掉維護(hù)頁(yè)扫责，開(kāi)啟業(yè)務(wù)
2.4 演練過(guò)程
2.4.1 模擬數(shù)據(jù)
mysql [none]>create database mdp charset utf8mb4;
mysql [none]>use mdp;
mysql [mdp]>create table t1(id int) engine=innodb charset=utf8mb4;
mysql [mdp]>insert into t1 values(1),(2),(3);
2.4.2 模擬昨天晚上23:00的全備(命令行執(zhí)行)
mysqldump -uroot -p123456 -A -E -R --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M |gzip > /backup/full_$(date +%F).sql.gz
2.4.3 模擬備份后數(shù)據(jù)變化
mysql [none]>use mdp;
mysql [mdp]>insert into t1 values(11),(12),(13);
mysql [mdp]>commit;
mysql [mdp]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 12 |
| 13 |
+------+
2.4.5 模擬數(shù)據(jù)損壞(可以直接刪庫(kù))
mysql [mdp]>drop database mdp;
2.4.6 恢復(fù)故障
(1) 停止故障業(yè)務(wù)，掛維護(hù)頁(yè)
(此步驟略)
(2) 準(zhǔn)備備份
全備準(zhǔn)備：
[root@db01 ~]# cd /backup/
[root@db01 /backup]# gunzip full_2019-08-16.sql.gz
binlog準(zhǔn)備：
vim full_2019-08-16.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000009', MASTER_LOG_POS=1425;
(3) 確認(rèn)日志的終點(diǎn)
mysql [mdp]>show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000009 | 1840 | | | ae5de4c2-bf1e-11e9-8109-000c29c6fa61:1-21 |
+------------------+----------+--------------+------------------+-------------------------------------------+
mysql [mdp]>show binlog events in 'mysql-bin.000009';
...
| mysql-bin.000009 | 1686 | Gtid | 6 | 1751 | SET @@SESSION.GTID_NEXT= 'ae5de4c2-bf1e-11e9-8109-000c29c6fa61:21' |
| mysql-bin.000009 | 1751 | Query | 6 | 1840 | drop database mdp |
mysqlbinlog --skip-gtids --start-position=1425 --stop-position=1751 /data/binlog/mysql-bin.000009 >/backup/binlog.sql
2.4.7 進(jìn)行恢復(fù)
set sql_log_bin=0;
source /backup/full_2019-08-16.sql
source /backup/binlog.sql
set sql_log_bin=1;
mysql [mdp]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 12 |
| 13 |
+------+
3. XBK(Percona-Xtrabackup)-物理備份工具
3.1安裝
安裝依賴包：
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
下載軟件并安裝
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
將下載好的 Percona-Xtrabackup 安裝:
yum -y install percona-xtrabackup-24-2.4.4-1.el7.x86_64.rpm
[root@db01 ~]# rpm -qa |grep percona
percona-xtrabackup-24-2.4.12-1.el7.x86_64
3.2 介紹
物理備份工具, 類似于cp數(shù)據(jù).
3.3 備份細(xì)節(jié)
非InnoDB：例如 MyISAM逃呼，自動(dòng)鎖表備份鳖孤，會(huì)有短暫的全局鎖.
InnoDB：
1. 立即進(jìn)行CKPT,將當(dāng)前所有的已提交事務(wù)的臟頁(yè),立即刷寫到磁盤上(to_lsn = 425544571)
2. 拷貝所有InnoDB的數(shù)據(jù)文件,系統(tǒng)數(shù)據(jù)文件(ibdata1)也進(jìn)行拷貝.
3. 將備份過(guò)程中產(chǎn)生的redo截取并備份走(last_lsn = 425544580).
3.4 innobackupex
3.4.1 配置操作
[root@db01 ~]# cd /backup/
[root@db01 /backup]# mkdir xbk
配置文件中添加client客戶端:
[root@db01 /backup]# vim /etc/my.cnf
...
[client]
socket=/tmp/mysql.sock
3.4.2 全備
innobackupex --user=root --password=123456 /backup/xbk
innobackupex --user=root --password=123456 --no-timestamp /backup/xbk/full
3.4.5 搞破壞
[root@db01 ~]# pkill mysqld
[root@db01 ~]# \rm -rf /data/3306/data/*
3.4.5 數(shù)據(jù)恢復(fù)準(zhǔn)備(備份處理)
--apply-log
[root@db01 /backup/xbk]# innobackupex --apply-log /backup/xbk/full/
3.4.6 恢復(fù)數(shù)據(jù)
--copy-back
[root@db01 ~]# innobackupex --copy-back /backup/xbk/full/
[root@db01 /data/3306/data]# chown -R mysql.mysql /data/*
3.4.7 數(shù)據(jù)恢復(fù)成功
[root@db01 /data/3306/data]# ls
auto.cnf gtid ibtmp1 performance_schema xtrabackup_binlog_pos_innodb
binlog ib_buffer_pool mdp school xtrabackup_info
db01.pid ibdata1 mysql sys xtrabackup_master_key_id
db01-slow.log ib_logfile0 mysql.log test
db1 ib_logfile1 oldgirl world
3.4.8 --apply-log 參數(shù)說(shuō)明 (面試重點(diǎn)) ☆☆☆☆☆
- 模仿了InnoDB引擎的ACSR的過(guò)程
- 將備份集中地?cái)?shù)據(jù)和日志的LSN追平
- 利用redo進(jìn)行前滾
- 利用undo進(jìn)行回滾
3.5 備份集的認(rèn)識(shí)
3.5.1 備份集文件
[root@db01 /backup/xbk/full]# ll xtrabackup_*
-rw-r----- 1 root root 64 Aug 16 10:43 xtrabackup_binlog_info
-rw-r--r-- 1 root root 22 Aug 16 10:50 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 117 Aug 16 10:50 xtrabackup_checkpoints
-rw-r----- 1 root root 561 Aug 16 10:43 xtrabackup_info
-rw-r----- 1 root root 8388608 Aug 16 10:45 xtrabackup_logfile
-rw-r--r-- 1 root root 1 Aug 16 10:50 xtrabackup_master_key_id
3.5.2 備份集文件認(rèn)識(shí)
[root@db01 /backup/xbk/full]# cat xtrabackup_binlog_info #存儲(chǔ)的是binlog截取的起始點(diǎn)信息(position,gtid)
mysql-bin.000009 1840 ae5de4c2-bf1e-11e9-8109-000c29c6fa61:1-21
[root@db01 /backup/xbk/full]# cat xtrabackup_binlog_pos_innodb
mysql-bin.000009 1686
[root@db01 /backup/xbk/full]# cat xtrabackup_checkpoints
backup_type = full-prepared #備份類型，默認(rèn)全備
from_lsn = 0 #全備中是0抡笼，增量備份時(shí)是最起始的LSN號(hào)碼
to_lsn = 425544571 #ckpt后數(shù)據(jù)頁(yè)(ibd)的LSN號(hào)碼
last_lsn = 425544580 #備份結(jié)束時(shí),redo的LSN,(在當(dāng)前5.7版本,會(huì)有9個(gè)LSN可以忽略)
# to_lsn和last_lsn 差9位數(shù)字
[root@db01 /backup/xbk/full]# cat xtrabackup_info 3備份的具體參數(shù) 時(shí)間 （不敘述）
[root@db01 /backup/xbk/full]# file xtrabackup_logfile #備份過(guò)程中的信息苏揣，不能打開(kāi)
xtrabackup_logfile: data
4.XBK增量（incremental）備份
Xtrabackup企業(yè)級(jí)增量恢復(fù)實(shí)戰(zhàn)
背景：
某大型網(wǎng)站，mysql數(shù)據(jù)庫(kù)蔫缸，數(shù)據(jù)量500G腿准，每日更新量20M-30M
備份策略：
xtrabackup，每周日23:00進(jìn)行全備，周一到周六23:00進(jìn)行增量備份吐葱。
故障場(chǎng)景：
周三下午2點(diǎn)出現(xiàn)數(shù)據(jù)庫(kù)意外刪除表操作街望。
如何恢復(fù)？
4.1 清除以往備份
[root@db01 /backup/xbk]# \rm -rf /backup/xbk/*
4.2 建庫(kù)建表
mysql [(none)]>create database xbk charset utf8mb4;
mysql [(none)]>use xbk
mysql [xbk]>create table t1(id int) engine=innodb charset=utf8mb4;
4.3 插入數(shù)據(jù)
mysql [xbk]>insert into t1 values(1),(2),(3);
mysql [xbk]>commit;
4.4 模擬周日全備
[root@db01 /backup/xbk]# innobackupex --user=root --password=123456 --no-timestamp /backup/xbk/full
[root@db01 /backup/xbk]# ll
total 0
drwxr-x--- 14 root root 336 Aug 16 13:35 full
4.5 模擬周一晚上數(shù)據(jù)變化
insert into t1 values(11),(22),(33);
4.6 模擬周一晚上增量備份
[root@db01 ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/xbk/full /backup/xbk/inc1
--incremental # 打開(kāi)增量備份開(kāi)關(guān)弟跑，(每一次增量都是基于上一次備份)
--incremental-basedir=/backup/xbk/full #設(shè)定增量備份的基備份(一般是上一天)
查看備份集的關(guān)系
工作中灾前，每天檢查備份，LSN號(hào)是一個(gè)標(biāo)志點(diǎn)
4.7 模擬周二白天的數(shù)據(jù)變化
mysql [xbk]>use xbk;
mysql [xbk]>insert into t1 values(111),(222),(333);
mysql [xbk]>commit;
4.8 模擬周二晚上增量
[root@db01 ~]# innobackupex --user=root --password=123456 --no-timestamp --incremental --incremental-basedir=/backup/xbk/inc1 /backup/xbk/inc2
4.9 模擬周三白天數(shù)據(jù)庫(kù)變化
mysql [xbk]>use xbk;
mysql [xbk]>insert into t1 values(1111),(2222),(3333);
mysql [xbk]>commit;
4.10 搞破壞
[root@db01 /backup/xbk]# pkill mysqld
[root@db01 /backup/xbk]# \rm -rf /data/3306/data/*
4.11 恢復(fù)思路
(1) 測(cè)試庫(kù),維護(hù)頁(yè)
(2) 處理備份
合并
準(zhǔn)備
(3) 截取二進(jìn)制日志
(4) 數(shù)據(jù)恢復(fù)
4.12 開(kāi)始恢復(fù)演練
1. 處理備份:
(1) 處理原始全備
[root@db01 ~]# innobackupex --apply-log --redo-only /backup/xbk/full/
(2) 合并周1并處理
[root@db01 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/xbk/inc1 /backup/xbk/full
(3) 合并周2并處理
[root@db01 ~]# innobackupex --apply-log --incremental-dir=/backup/xbk/inc2 /backup/xbk/full
(4)處理合并后的全備數(shù)據(jù)
[root@db01 ~]# innobackupex --apply-log /backup/xbk/full/
2. 恢復(fù)備份啟動(dòng)數(shù)據(jù)庫(kù)
[root@db01 /backup/xbk/full]# cp -a /backup/xbk/full/* /data/3306/data/
[root@db01 /backup/xbk/full]# chown -R mysql. /data/3306/data/
[root@db01 /backup/xbk/full]# /etc/init.d/mysqld start
3 截取binlog并恢復(fù)
[root@db01 /backup/xbk/inc2]# cat /backup/xbk/inc2/xtrabackup_binlog_info
mysql-bin.000010 2490 ae5de4c2-bf1e-11e9-8109-000c29c6fa61:1-21,
e21f04e4-bfd0-11e9-b8ae-000c29c6fa61:1-10
# 進(jìn)入數(shù)據(jù)庫(kù)查看
oldguo[(none)]>show binlog events in 'mysql-bin.000010';
...
| mysql-bin.000010 | 2720 | Xid | 6 | 2751 | COMMIT /* xid=188 */
#截取binlog并恢復(fù)
[root@db01 ~]# mysqlbinlog --skip-gtids --start-position=2490 --stop-position=2720 /data/binlog/mysql-bin.000010 >/backup/bin.sql
set sql_log_bin=0;
source /backup/bin.sql
set sql_log_bin=1;
oldguo[xbk]>select * from t1;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 11 |
| 22 |
| 33 |
| 111 |
| 222 |
| 333 |
| 1111 |
| 2222 |
| 3333 |
+------+
5. 恢復(fù)數(shù)據(jù)的效率（小擴(kuò)展）
整庫(kù)數(shù)據(jù)量較大 ,但是損壞的數(shù)據(jù)很少
例如 500G數(shù)據(jù)總量, 損壞的數(shù)據(jù)是10M
- XBK ：表空間遷移
- MDP ：手工分析
1孟辑、獲得表結(jié)構(gòu)
# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `city`/!d;q' /tmp/full.sql>createtable.sql
2哎甲、獲得INSERT INTO 語(yǔ)句，用于數(shù)據(jù)的恢復(fù)
# grep -i 'INSERT INTO `city`' /tmp/full.sqll >data.sql
3.獲取單庫(kù)的備份
# sed -n '/^-- Current Database: `world`/,/^-- Current Database: `/p' all.sql >world.sql
6. 小擴(kuò)展: 閃回表數(shù)據(jù)(binlog閃回)
數(shù)據(jù)損壞:
- 物理：磁盤饲嗽，raid炭玫，F(xiàn)S，ibd
- 邏輯：drop alter delete updatae
說(shuō)明: 根據(jù) binlog row的記錄,自動(dòng)轉(zhuǎn)化日志為逆操作,實(shí)現(xiàn)閃回的功能.
mariadb 默認(rèn)支持
binlogsql
Github上的 binlogsql 開(kāi)源工具
7. 小擴(kuò)展：遷移
Oracle ,SQL Server ---> MySQL
Oracle ----OGG ---> MySQL
上云遷移,DTS
停機(jī)時(shí)間:
15分鐘
基于Oracle GoldenGate （OGG）進(jìn)行MySQL->MySQL數(shù)據(jù)庫(kù)同步配置
