1.運維人員在備份恢復方面的職責
1.1 設計備份策略
1）備份內容 ：數據，二進制日志
2）數據容量：大小
3）備份周期：1.每天全備
2.周期全備+增量備份
3.周期全備+日志備份
4)備份時間：晚上，
5）備份目標位置：
1.2日常備份檢查
日志檢查、備份的內容、備份大小
1.3定期恢復演練
每半年做一次
1.4故障時的恢復
快速準確恢復數據
1.5平臺數據遷移
同構平臺
異構平臺
2.備份工具介紹
2.1介紹
mysqldump (MDP) 邏輯備份工具
Percona Xtrabackup (xbk, pbk, pxb)物理備份
2.2區(qū)別
MDP:邏輯備份，SQL文件，文本形式，可讀性高，便于處理，壓縮比高、備份相對較慢，適用于100G以內的備份。
100G數據，備份30-40分鐘
xbk: 物理備份，數據文件、可讀性低，壓縮比低、備份相對較快，適用于100G以內的備份。
100G 10分鐘
3.mysqldump
3.1 備份邏輯
將磁盤數據，加載到臨時表，轉換為SQL語句(建庫、建表、插入語句)
3.2 核心參數
1）連接參數
本地：-u - p -S
遠程：-u -p -H -P
2)備份參數
1.全備參數
-A全備
-B 指定多個庫名
[root@db01 ~]# mysqldump -uroot -p123 -A >/data/backup/full.sql
2.單表或多庫備份
[root@db01 ~]# mysqldump -uroot -p123 -B bgx oldboy test world mysql > /data/backup/db.sql
3.單標或多表備份
[root@db01 ~]# mysqldump -uroot -p123 world city country >/data/backup/tab.sql
4.只導出建表語句
[root@db01 ~]# mysqldump -uroot -p123 world city country --no-data >/data/backup/tab1.sql
5.--master-data=2
(1) 自動記錄備份時的binlog filename 及pos
(2) 自動加鎖和解鎖
(3) 加了--single-transaction , 會有不一樣的效果( global read lock is only taken a short time )
mysqldump -uroot -p123 -A --master-data=2 >/data/backup/full.sql
6. --single-transaction
開啟獨立事務,備份InnoDB表的一致性快照.實現InnoDB表的熱備功能.
補充:
對于非InnoDB表(FTWRL Flush tables with read lock 全局鎖表) ,對于mysq1庫下的系統(tǒng)表備份的時候,會加global read lock
Flush tables with read lock 全局鎖表
unlock tables關閉鎖表
7. 特殊的數據庫對象
-R 存儲過程和函數
--triggers 觸發(fā)器
-E 事件
mysqldump -uroot -p -A --master-data=2 --single-transaction -R --triggers -E >/data/backup/full.sql
8. --max_allowed_packet=128M
1153 - Got a packet bigger than 'max_allowed_packet' bytes
出現場景: 在數據庫有超大比表時,需要加此的參數.
9. 終極備份語句
mysqldump -uroot -p -A --master-data=2 --single-transaction -R --triggers -E --max_allowed_packet=128M |gzip >/data/backup/full_date +%F.sql.gz
3.3 模擬故障和恢復(mysqldump 全備+ binlog)
(1) 模擬原始數據
create database mdp charset utf8mb4;
use mdp
create table t1 (id int);
create table t2 (id int);
create table t3 (id int);
insert into t1 values(1);
commit;
insert into t2 values(1);
commit;
insert into t3 values(1);
commit;
(2) 模擬全備
mysqldump -uroot -p -A --master-data=2 --single-transaction -R --triggers -E --max_allowed_packet=128M |gzip >/data/backup/full_date +%F.sql.gz
(3) 模擬新的數據
use mdp
insert into t1 values(2);
commit;
insert into t2 values(2);
commit;
insert into t3 values(2);
commit;
(4) 搞破壞
drop database mdp;
(5) 恢復思路
-- 1. 掛維護頁
-- 2. 找測試庫
-- 3. 準備備份
full
截取binlog部分
-- 4. 恢復全備+binlog到測試庫 , 業(yè)務測試.
-- 5. 導出故障庫數據,恢復到生產 .
-- 6. 撤維護頁.
恢復過程:
1. 準備備份
vim /data/backup/full_2019-12-26.sql
第30行:
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2570;
2. 截取二進制日志
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=2570;
起點 : 2570
mysql> show binlog events in 'mysql-bin.000003';
| mysql-bin.000003 | 3388 | Query | 6 | 3477 | drop database mdp
終點 : 3388
mysqlbinlog --skip-gtids --start-position=2570 --stop-position=3388 /data/mysql/binlog_3306/mysql-bin.000003 >/data/backup/bin.sql
3. 恢復備份
set sql_log_bin=0;
source /data/backup/full_2019-12-26.sql
source /data/backup/bin.sql
set sql_log_bin=1;
4. 檢查數據
mysql> show tables;
mysql> select * from t1;
mysql> select * from t2;
mysql> select * from t3;
5. 擴展: 從全備中提取 單庫 單表的數據進行恢復
1、獲得表結構
sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE city/!d;q' full.sql>createtable.sql
2、獲得INSERT INTO 語句，用于數據的恢復
grep -i 'INSERT INTO city' full.sqll >data.sql &
3.獲取單庫的備份
