第二十三天：mysql數據備份及還原

一、備份類型

1、mysqldump 說明

mysqldump [OPTIONS] database [tables]   #支持指定資料庫和指定多表的備份，但資料庫本身定義不備份
mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定資料庫備份，包含資料庫本身定義也會備份
mysqldump [OPTIONS] -A [OPTIONS]        #備份所有資料庫，包含資料庫本身定義也會備份

2、mysqldump 常見通用選項：

-u, --user=name     User for login if not current user
-p, --password[=name]  Password to use when connecting to server
-A, --all-databases #備份所有資料庫，含create database
-B, --databases db_name…  #指定備份的資料庫，包括create database語句
-E, --events：#備份相關的所有event scheduler
-R, --routines：#備份所有存儲過程和自定義函數
--triggers：#備份表相關觸發器，預設啟用,用--skip-triggers，不備份觸發器
--default-character-set=utf8 #指定字元集
--master-data[=#]：#註意：MySQL8.0.26版以後，此選項變為--source-data
#此選項須啟用二進位日誌
#1：所備份的數據之前加一條記錄為CHANGE MASTER TO語句，非註釋，不指定#，預設為1，適合於主從復
制多機使用
#2：記錄為被註釋的#CHANGE MASTER TO語句，適合於單機使用,適用於備份還原
#此選項會自動關閉--lock-tables功能，自動打開-x | --lock-all-tables功能（除非開啟--
single-transaction）
-F, --flush-logs #備份前滾動日誌，鎖定表完成後，執行flush logs命令,生成新的二進位日誌文件，配合-A 或 -B 選項時，會導致刷新多次資料庫。建議在同一時刻執行轉儲和日誌刷新，可通過和--
single-transaction或-x，--master-data 一起使用實現，此時只刷新一次二進位日誌
--compact        #去掉註釋，適合調試，節約備份占用的空間,生產不使用
-d, --no-data    #只備份表結構,不備份數據,即只備份create table 
-t, --no-create-info #只備份數據,不備份表結構,即不備份create table 
-n,--no-create-db #不備份create database，可被-A或-B覆蓋
--flush-privileges #備份mysql或相關時需要使用
-f, --force       #忽略SQL錯誤，繼續執行
--hex-blob        #使用十六進位符號轉儲二進位列，當有包括BINARY,VARBINARY,BLOB，BIT的數據類型的列時使用，避免亂碼
-q, --quick     #不緩存查詢，直接輸出，加快備份速度

mysqldump的MyISAM存儲引擎相關的備份選項：

-x,--lock-all-tables #加全局讀鎖，鎖定所有庫的所有表，同時加--single-transaction或--lock-tables選項會關閉此選項功能，註意：數據量大時，可能會導致長時間無法併發訪問資料庫
-l,--lock-tables #對於需要備份的每個資料庫，在啟動備份之前分別鎖定其所有表，預設為on,--skip-lock-tables選項可禁用,對備份MyISAM的多個庫,可能會造成數據不一致
#註：以上選項對InnoDB表一樣生效，實現溫備，但不推薦使用

--single-transaction
#此選項Innodb中推薦使用，不適用MyISAM，此選項會開始備份前，先執行START TRANSACTION指令開啟事務
#此選項通過在單個事務中轉儲所有表來創建一致的快照。 僅適用於存儲在支持多版本控制的存儲引擎中的表（目前只有InnoDB可以）; 轉儲不保證與其他存儲引擎保持一致。 在進行單事務轉儲時，要確保有效的轉儲
文件（正確的表內容和二進位日誌位置），沒有其他連接應該使用以下語句：ALTER TABLE，DROP TABLE，RENAME TABLE，TRUNCATE TABLE,此選項和--lock-tables（此選項隱含提交掛起的事務）選
項是相互排斥,備份大型表時，建議將--single-transaction選項和--quick結合一起使用

3、生產環境實戰備份策略

mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --masterdata=2 --flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sql
#新版8.0.26以上
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --sourcedata=2 --flush-privileges --default-character-set=utf8 --hex-blob
>${BACKUP}/fullbak_${BACKUP_TIME}.sq

4、特定資料庫的備份腳本

[root@centos8 ~]#cat mysql_backup.sh 
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
DB=hellodb
PASS=magedu
[ -d $DIR ] || mkdir $DIR
mysqldump -uroot -p "$PASS" -F -E -R --triggers  --single-transaction --masterdata=2 --default-character-set=utf8 -q  -B $DB | gzip > 
${DIR}/${DB}_${TIME}.sql.gz

5、分庫備份並壓縮

 6、分庫備份的實戰腳本

[root@centos8 ~]#cat backup_db.sh 
#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=magedu
[ -d "$DIR" ] || mkdir $DIR
for DB in `mysql -uroot -p "$PASS" -e 'show databases' | grep -Ewv 
"^Database|.*schema$"`;do
 mysqldump -F --single-transaction --master-data=2 --default-characterset=utf8 -q -B $DB | gzip >  ${DIR}/${DB}_${TIME}.sql.gz
done

7、利用二進位日誌，還原資料庫最新狀態

#二進位日誌獨立存放
[mysqld]
log-bin=/data/mysql/mysql-bin 
#完全備份，並記錄備份的二進位位置
mysqldump  -uroot -pmagedu -A -F --default-character-set=utf8  --singletransaction --master-data=2 | gzip > /backup/all_`date +%F`.sql.gz
#修改資料庫
insert students (name,age,gender)value('mage',20,'M');
insert students (name,age,gender)value('wang',22,'M');
#損壞資料庫
rm -rf /var/lib/mysql/*
#還原
cd /backup
gzip -d all_2019-11-25.sql.gz
#CentOS 8 需要事先生成資料庫相關文件，CentOS7 不需要執行此步
mysql_install_db  --user=mysql

8、mysqldump 和二進位日誌結合實現差異(增量)備份 

[root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2 
|gzip > /backup/all-`date +%F`.sql.gz
#觀察上面備份文件中記錄的二進位文件和位置，定期將其之後生成的所有二進位日誌進行複製備份
[root@centos8 ~]#cp /var/lib/mysql/mariadb-bin.000003 /backup #假設mariadbbin.000003是後續生成的二進位日誌
[root@centos8 ~]#mysqlbinlog backup/mariadb-bin.000003 > /backup/inc.sql

三、xtrabackup 備份工具

1、xtrabackup 工具介紹

yum install percona-xtrabackup

3、xtrabackup 用法

innobackupex [option] BACKUP-ROOT-DIR

 Prepare預準備：

innobackupex --apply-log [option] BACKUP-DIR

--apply-log：#一般情況下,在備份完成後，數據尚且不能用於恢復操作，因為備份的數據中可能會包含尚
未提交的事務或已經提交但尚未同步至數據文件中的事務。因此，此時數據文件仍處理不一致狀態。此選項作
用是通過回滾未提交的事務及同步已經提交的事務至數據文件使數據文件處於一致性狀態
--use-memory：#和--apply-log選項一起使用，當prepare 備份時，做crash recovery分配的記憶體
大小，單位位元組，也可1MB,1M,1G,1GB等，推薦1G
--export：#表示開啟可導出單獨的表之後再導入其他Mysql中
--redo-only：#此選項在prepare base full backup，往其中合併增量備份時候使用，但不包括對最
後一個增量備份的合併

innobackupex --copy-back [選項] BACKUP-DIR
innobackupex --move-back [選項] [--defaults-group=GROUP-NAME] BACKUP-DIR

 還原註意事項：

4、利用 xtrabackup 實現完全備份及還原

 5、利用xtrabackup8.0 完全，增量備份及還原MySQL8.0

 6、xtrabackup單表導出和導入