本課程局冰，適合具備一定Linux運維或者開發(fā)基礎的朋友吊洼，課程定級中训貌、高級DBA。
只要掌握80%冒窍，輕松助力薪資15k-25K递沪。
課程內(nèi)容均來自與MySQL官網(wǎng)+MySQL源碼。
配套精品視頻(2021 5月全新錄制综液，版權所有：郭加磊 oldguo款慨。)，獲取方法私聊谬莹。

1. 備份的作用

處理數(shù)據(jù)庫損壞檩奠。
損壞桩了？
物理 ：磁盤、文件系統(tǒng)埠戳、數(shù)據(jù)文件井誉。處理方案：主從、高可用整胃、備份+日志颗圣。
邏輯 ：drop truncate delete update 。 處理方案： 備份+日志屁使、延時從在岂。

2. 備份工具

邏輯備份 ： mysqldump (MDP)、binlog 蛮寂、主從 .....
物理備份 : Percona Xtrabackup (PXB\XBK\Xbakcup)
擴展: 8.0 Clone plugin

3. 作為DBA在備份恢復工作職責

a. 設計備份策略 ： 備份周期蔽午、備份工具、備份方式（全備酬蹋、增量..）
b. 定期備份檢查祠丝。
c. 定期恢復演練。
d. 數(shù)據(jù)恢復。
e. 數(shù)據(jù)的遷移升級。

4. mysqldump 應用

4.1 介紹

邏輯備份工具畅卓。文本形式保存?zhèn)浞荩勺x性較強叠蝇。
備份邏輯： 將建庫、建表年缎、數(shù)據(jù)插入語句導出悔捶，包存至一個sql文件中。
比較適合于：數(shù)據(jù)量較小的場景单芜，單表數(shù)據(jù)行千萬級別以內(nèi)蜕该。百G以內(nèi)的小型數(shù)據(jù)庫.跨版本、跨平臺遷移洲鸠。
可以本地堂淡、可以遠程備份。
注意： 一般情況下扒腕，恢復需要耗費的時間是備份耗費時間的3-5倍绢淀。

4.2 使用

4.2.1 連接參數(shù)

mysqldump -u -p -S -h -P 

4.2.2 備份基礎參數(shù)

-A 全備

[root@db01 ~]# mysqldump -uroot -p123 -A >/data/backup/full.sql

-B 單庫或多庫備份

[root@db01 ~]# mysqldump -uroot -p123 -B test world >/data/backup/db.sql

單表或多表備份

[root@db01 ~]# mysqldump -uroot -p123  world city country >/data/backup/tb.sql

注意：
-A 和 -B 都帶有了 create database 和use 語句，直接恢復即可
單表或多表備份方式, 沒有 create database 和use 語句瘾腰，所以要手工進行建庫和use皆的，再恢復數(shù)據(jù)。

4.2.3 高級功能參數(shù)

參數(shù)一： --master-data=2

a. 自動記錄binlog位置點 b. 自動加GRL鎖(FTWRL ,flush tables with read lock) c. 配合--single-transaction ,減少鎖的時間蹋盆。

參數(shù)二： single-transaction
a. 對于InnoDB表费薄，利用MVCC中一致性快照進行備份硝全。備份數(shù)據(jù)時,不加鎖 b. 備份期間如果出現(xiàn)DDL操作，導致備份數(shù)據(jù)不一致 問題： mysqldump是嚴格意義上的熱備嗎楞抡？ 8.0 之后 master-data和single-transaction,對于InnoDB數(shù)據(jù)備份時是快照備份的. 備份表結構等數(shù)據(jù)時,還是FTWRL過程備份. --single-transaction 只是針對InnoDB表數(shù)據(jù)進行一致性快照備份伟众。 問題： mysqldump備份需要鎖表嗎？ 是有的拌倍。global read lock

參數(shù)三： -R -E --triggers 備份特殊對象
存儲過程 函數(shù) 觸發(fā)器 事件

參數(shù)四： --max_allowed_packet=64M
最大允許數(shù)據(jù)包的大小。

4.2.4 標準化備份

[root@db01 backup]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/data/backup/full_`date +%F`.sql
Enter password: 

4.2.5 案例：通過mysqldump全備+binlog實現(xiàn)PIT數(shù)據(jù)恢復

環(huán)境背景： 小型的業(yè)務數(shù)據(jù)庫噪径，50G柱恤，每天23:00全備，定期binlog異地備份找爱。
故障場景： 周三下午2點梗顺，開發(fā)Navicat連接數(shù)據(jù)庫實例錯誤，導致生產(chǎn)數(shù)據(jù)被誤刪除（DROP）
恢復思路：
1. 掛維護頁车摄。
2. 檢查備份寺谤、日志可用。
3. 如果只是部分損壞吮播，建議找一個應急庫進行恢復
a. 全備恢復
b. 日志截取并恢復
4. 恢復后數(shù)據(jù)校驗 （業(yè)務測試部門驗證）
5. 立即備份（停機冷備）
6. 恢復架構系統(tǒng)
7. 撤維護頁变屁，恢復業(yè)務

> 模擬環(huán)境：
mysql> create database mdb;
Query OK, 1 row affected (0.01 sec)

mysql> use mdb
Database changed
mysql> create table t1 (id int);
Query OK, 0 rows affected (0.03 sec)

mysql> create table t2 (id int);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into t1 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t2 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.00 sec)


[root@db01 backup]# mysqldump -uroot -p -A --master-data=2 --single-transaction -R -E --triggers --max_allowed_packet=64M >/data/backup/full_`date +%F`.sql

mysql> create table t3 (id int);
Query OK, 0 rows affected (0.02 sec)

mysql> insert into t3 values(1),(2),(3);
Query OK, 3 rows affected (0.00 sec)
Records: 3  Duplicates: 0  Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> drop database mdb;

> 恢復過程：  
a. 查看備份，獲取二進制日志位置點

-- CHANGE MASTER TO MASTER_LOG_FILE='binlog.000001', MASTER_LOG_POS=1274;

思考一個問題： binlog位置點是備份開始時意狠，還是備份結束時的位置點粟关？

b.  恢復全備
mysql> source /data/backup/full_2020-09-18.sql

c.  binlog 截取并恢復 
| binlog.000001 | 1711 | Xid            |         1 |        1742 | COMMIT /* xid=2278 */    

[root@db01 backup]# mysqlbinlog --skip-gtids --start-position=1274 --stop-position=1742 /data/3306/data/binlog.000001 >/tmp/bin.sql


mysql> set sql_log_bin=0;
mysql> source /tmp/bin.sql

4.2.6 擴展 (課后作業(yè))

100G mysqldump全備恢復時間很長，誤刪除的表10M大小 环戈，有什么思路可以快速恢復闷板？

思路：  
a. 從全備中，將單表 建表語句和insert語句提取出來 院塞，進行恢復

# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `oldguo`/!d;q'  /data/backup/mdp/full.sql>/data/createtable.sql
# grep -i 'INSERT INTO `oldguo`'   /data/backup/mdp/full.sql >/data/data.sql 

b. 從binlog中單獨截取單表的所有binlog遮晚，進行恢復。 
binlog2sql 截取單表binlog拦止，恢復县遣。

5. 物理備份工具使用-Percona Xtrabackup（PXB）

5.0 介紹

物理備份工具，支持全備和增量備份汹族。
備份邏輯：
a. 數(shù)據(jù)庫運行期間艺玲，拷貝數(shù)據(jù)表空間.
b. 拷貝的同時，會將備份期間的redo進行備份
恢復邏輯 ：
模擬了InnoDB Crash Recovery 功能鞠抑，需要要將備份進行處理（前滾和回滾）后才能恢復

5.1 安裝

yum install percona-xtrabackup*.rpm

注意：
對于MySQL 8.0.20版本饭聚，需要使用PXB 8.0.12+以上版本，MysQL：8.0.11 ~ 8.0.19 使用PXB 8.0正式版本搁拙。
MySQL 8.0 之前（5.6秒梳，5.7 ）的版本： PXB 2.4

5.2 全量備份

1.全量備份

xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123   --backup --target-dir=/data/backup/full

2.數(shù)據(jù)恢復：

[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/3306/data/*
[root@db01 ~]# rm -rf /data/3306/logs/*
[root@db01 ~]# rm -rf /data/3306/binlog/*

2.1 準備：(CR)

xtrabackup   --prepare --target-dir=/data/backup/full

說明： 模擬CR過程法绵，將redo前滾，undo回滾酪碘，讓備份數(shù)據(jù)是一致狀態(tài)

2.2 拷回數(shù)據(jù)：

xtrabackup  --copy-back --target-dir=/data/backup/full

2.3 修改權限并啟動數(shù)據(jù)庫

[root@db01 data]# chown -R mysql.mysql /data/*
[root@db01 data]# /etc/init.d/mysqld start

5.3 增量備份

5.3.1 介紹

增量備份朋譬，是基于上一次備份LSN變化過的數(shù)據(jù)頁進行備份，在備份同時產(chǎn)生的新變更兴垦，會將redo備份徙赢。
第一次增量是依賴于全備的。將來的恢復也要合并到全備中探越，再進行統(tǒng)一恢復狡赐。

5.3.2 增量備份演練

全量備份的目錄為： mkdir -p /data/backup/full
增量備份的目錄為： mkdir -p /data/backup/inc

1.備份過程
1. 全量備份：
xtrabackup --defaults-file=/etc/my.cnf  --user=root --password=123  --backup --parallel=4 --target-dir=/data/backup/full

mysql> create database pxb;
mysql> use pxb
mysql> create table t1 (id int);
mysql> insert into t1 values(1),(2),(3);
mysql> commit;

1.2.增量備份：
xtrabackup --defaults-file=/etc/my.cnf --user=root --password=123  --backup --parallel=4 --target-dir=/data/backup/inc  --incremental-basedir=/data/backup/full
 
1.3. 模擬損壞  
[root@db01 ~]# pkill mysqld
[root@db01 ~]# rm -rf /data/3306/data/*

2. 恢復操作：
2.1 準備全備份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full


2.2 準備增量備份的日志：
xtrabackup --prepare --apply-log-only --target-dir=/data/backup/full  --incremental-dir=/data/backup/inc


2.3 全備份準備：
# xtrabackup --prepare --target-dir=/data/backup/full


2.4 拷回數(shù)據(jù)：
xtrabackup    --copy-back --target-dir=/data/backup/full
 
2.5 修改數(shù)據(jù)目錄的權限和屬性：
chown -R mysql:mysql /data/*

課后思考: 
500G數(shù)據(jù)， 每周日全備钦幔，其他時間段增量枕屉，周三下午誤刪除了一個表（10M）數(shù)據(jù)。
如何快速恢復鲤氢。

2.8 MySQL 8.0（8.0.17+） Clone-plugin

2.8.1 Clone Plugin介紹

本地克隆
啟動克隆操作的MySQL服務器實例中的數(shù)據(jù)搀擂，克隆到同服務器或同節(jié)點上的一個目錄里
遠程克隆
默認情況下，遠程克隆操作會刪除接受者(recipient)數(shù)據(jù)目錄中的數(shù)據(jù)卷玉，并將其替換為捐贈者(donor)的克隆數(shù)據(jù)哨颂。您也可以將數(shù)據(jù)克隆到接受者的其他目錄，以避免刪除現(xiàn)有數(shù)據(jù)相种。(可選)

2.8.2 原理

PAGE COPY
開啟redo archiving功能咆蒿，從當前點開始存儲新增的redo log，這樣從當前點開始所有的增量修改都不會丟失蚂子。同時上一步在page track的page被發(fā)送到目標端沃测。確保當前點之前所做的變更一定發(fā)送到目標端。
關于redo archiving食茎，實際上這是官方早就存在的功能蒂破，主要用于官方的企業(yè)級備份工具，但這里clone利用了該特性來維持增量修改產(chǎn)生的redo别渔。
在開始前會做一次checkpoint附迷， 開啟一個后臺線程log_archiver_thread()來做日志歸檔。當有新的寫入時(notify_about_advanced_write_lsn)也會通知他去archive哎媚。當arch_log_sys處于活躍狀態(tài)時喇伯，他會控制日志寫入以避免未歸檔的日志被覆蓋(log_writer_wait_on_archiver), 注意如果log_writer等待時間過長的話， archive任務會被中斷掉.

Redo Copy
停止Redo Archiving,所有歸檔的日志被發(fā)送到目標端拨与，這些日志包含了從page copy階段開始到現(xiàn)在的所有日志稻据，另外可能還需要記下當前的復制點，例如最后一個事務提交時的binlog位點或者gtid信息买喧，在系統(tǒng)頁中可以找到捻悯。

Done
目標端重啟實例匆赃，通過crash recovery將redo log應用上去。

2.8.3 限制

官方文檔列出的一些限制：
The clone plugin is subject to these limitations:
* DDL, is not permitted during a cloning operation. This limitation should be considered when selecting data sources. A workaround is to use dedicated donor instances, which can accommodate DDL operations being blocked while data is cloned. Concurrent DML is permitted.
* An instance cannot be cloned from a different MySQL server version. The donor and recipient must have the same MySQL server version. For example, you cannot clone between MySQL 5.7 and MySQL 8.0\. The clone plugin is only supported in MySQL 8.0.17 and higher.
* Only a single MySQL instance can be cloned at a time. Cloning multiple MySQL instances in a single cloning operation is not supported.
* The X Protocol port specified byis not supported for remote cloning operations
* The clone plugin does not support cloning of MySQL server configurations.
* The clone plugin does not support cloning of binary logs.
* The clone plugin only clones data stored inInnoDB. Other storage engine data is not cloned.
* Connecting to the donor MySQL server instance through MySQL Router is not supported.
* Local cloning operations do not support cloning of general tablespaces that were created with an absolute path. A cloned tablespace file with the same path as the source tablespace file would cause a conflict.

2.8.4. 應用

a. 本地

# 1. 加載插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
或
[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'clone';

# 2 創(chuàng)建克隆專用用戶
CREATE USER clone_user@'%' IDENTIFIED by 'password'; 
GRANT BACKUP_ADMIN ON *.* TO 'clone_user'; 

注意：BACKUP_ADMIN是MySQL8.0 才有的備份鎖的權限

# 3 本地克隆
[root@db01 3306]# mkdir -p /data/test/
[root@db01 3306]# chown -R mysql.mysql /data/
mysql -uclone_user -ppassword
CLONE LOCAL DATA DIRECTORY = '/data/test/clonedir';

# 4 觀測狀態(tài)
db01 [(none)]> SELECT STAGE, STATE, END_TIME FROM performance_schema.clone_progress;
+-----------+-------------+----------------------------+
| STAGE     | STATE       | END_TIME                   |
+-----------+-------------+----------------------------+
| DROP DATA | Completed   | 2020-04-20 21:13:19.264003 |
| FILE COPY | Completed   | 2020-04-20 21:13:20.025444 |
| PAGE COPY | Completed   | 2020-04-20 21:13:20.028552 |
| REDO COPY | Completed   | 2020-04-20 21:13:20.030042 |
| FILE SYNC | Completed   | 2020-04-20 21:13:20.439444 |
| RESTART   | Not Started | NULL                       |
| RECOVERY  | Not Started | NULL                       |
+-----------+-------------+----------------------------+
7 rows in set (0.00 sec)

# 日志觀測： 
set global log_error_verbosity=3;
tail -f db01.err
CLONE LOCAL DATA DIRECTORY = '/data/test/3308';

# 啟動新實例
[root@db01 clonedir]# mysqld_safe  --datadir=/data/test/clonedir --port=3333 --socket=/tmp/mysql3333.sock --user=mysql --mysqlx=OFF &

2.8.5 遠程clone

# 各個節(jié)點加載插件
INSTALL PLUGIN clone SONAME 'mysql_clone.so';
或
[mysqld]
plugin-load-add=mysql_clone.so
clone=FORCE_PLUS_PERMANENT

SELECT PLUGIN_NAME, PLUGIN_STATUS
FROM INFORMATION_SCHEMA.PLUGINS
WHERE PLUGIN_NAME LIKE 'clone';

# 創(chuàng)建遠程clone用戶
# 捐贈者(source)授權
create user test_s@'%' identified by '123';
grant backup_admin on *.* to test_s@'%';

# 接受者(target)授權
create user test_t@'%' identified by '123';
grant clone_admin on *.* to test_t@'%';

# 遠程clone(目標端)
# 開始克隆
SET GLOBAL clone_valid_donor_list='10.0.0.51:3306';
mysql -utest_t -p123 -h10.0.0.52  -P3306
CLONE INSTANCE FROM test_s@'10.0.0.51':3306 IDENTIFIED BY '123';