1 MySQL備份和恢復

1.1 備份類型

• 完全備份, 部分備份

          完全備份: 整個數據集, 備份一整個數據庫, 或者備份所有的數據庫, 看部署情況
          部分備份: 只備份數據子集, 如部分庫或表
  

• 完全備份, 增量備份, 差異備份

          增量備份: 僅備份最近一次完全備份或增量備份(如果存在增量)以來變化的數據, 備份較快, 還原復雜
          差異備份: 僅備份最近一次完全備份以來變化的數據, 備份較慢, 還原簡單
  

增量備份:

圖片.png

需要有一次完全備份
之后每次都是備份基于上一次完全備份或者上一次增量備份以來, 變化的那一部分數據
對于增量備份的數據恢復, 一定要按照順序進行恢復, 先恢復最近一次的完全備份, 然后陸續(xù)恢復每一次增量備份
因為, 每次增量備份, 備份數據不確實是否有交集, 因此, 一定要按照備份順序恢復

圖片.png

差異備份:

圖片.png

需要有一次完全備份
每次差異備份, 都是基于上一次完全備份來備份, 備份的就是上一次完全備份, 到此次備份時, 變化的數據
對于完全備份的數據, 恢復時, 需要先恢復上一次的完全備份, 然后再用最近一次的差異備份來恢復
雖說還原只需要最近一次完全備份和差異備份, 但是此前的差異備份不要刪除, 避免期間有誤操作, 之后恢復數據還需要之前的差異備份v

圖片.png

注意: 二進制文件不應該與數據文件放在同一個磁盤

• 冷, 溫, 熱備份

冷備: 讀, 寫操作均不可進行, 數據庫停止服務
溫備: 讀操作可以執(zhí)行, 但是寫操作不可執(zhí)行, 加全局讀鎖
熱備: 讀, 寫操作做均可執(zhí)行

      MyISAM: 溫備, 不支持熱備
      InnoDB: 都支持. InnoDB支持熱備, 因為支持事務, 事務的可重復讀隔離級別, 保證在備份的時間段內, 備份的數據是基于同一個時間點的

• 物理備份和邏輯備份

物理備份: 直接復制數據文件進行備份, 與存儲引擎有關, 占用較多的空間, 速度快. 直接復制數據目錄的文件
邏輯備份: 從數據庫中'導出'數據另存到文件而進行備份, 與存儲引擎無關, 占用空間少, 速度慢, 可能丟失精度, 利用mysqldump, xtrabackup等其他工具

1.2 備份內容

• 數據, 業(yè)務數據庫
• mysql數據庫, 存放用戶信息
• 二進制日志, InnoDB的事務日志
• 用戶賬戶, 權限設置, 程序代碼(存儲過程, 函數, 觸發(fā), 事件調度器)
• 服務器的配置文件

1.3 備份注意要點

• 能容忍最多丟失多少數據
• 備份產生的負載
• 備份過程的時長
• 溫備的鎖要持續(xù)多久
• 恢復數據需要在多長時間內完成
• 需要備份和恢復哪些數據
• 多個數據庫進行備份還原時,數據庫版本要一致, 避免出現問題

1.4 還原注意

• 做還原測試, 用于測試備份的可用性, 避免由于備份過程出錯, 比如斷網, 與mysql連接端斷開等原因導致備份不完全, 不可用
• 還原演練, 把還原過程寫成規(guī)范的技術文檔

1.5 備份工具

• cp, tar等復制歸檔工具: 用于物理備份, 適用所有的存儲引擎, 只支持冷備, 完全和部分備份
• LVM的快照: 先加讀鎖, 做完快照后解鎖, 幾乎熱備, 借助文件系統(tǒng)工具進行備份
• mysqldump: 邏輯備份工具, 適用所有存儲引擎, 對MyISAM存儲引擎進行溫備, 支持完全或部分備份; 對InnoDB存儲引擎支持熱備, 結合binlog達到增量備份, 還未來得及備份的數據, 適用二進制日志做還原, 已經備份好的數據, 用增量備份還原.
• xtrabackup; 由Percona提供支持對InnoDB做熱備(物理備份)的工具, 支持完全備份, 增量備份
• MariaDB Backup: 從MariaDB 10.1.26開始集成, 基于Percona XtraBackup 2.3.8實現
• mysqlbackup: 熱備份, MySQL Enterprise Edition組件
• mysqlhotcopy: perl語言實現, 幾乎冷備, 僅適用于MyISAM存儲引擎, 使用lock tables, flush tables 和 cp 或 scp來快速備份數據庫

1.6 備份案例

1.6.1 基于LVM的快照備份

1. 請求鎖定所有表
mysql> flush tables with read lock;
2. 記錄二進制日志文件及事件位置
mysql> flush logs;
mysql> show master status;
mysql -e 'show master status' > /PATH/TO/LOGFILE
3. 創(chuàng)建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
4. 釋放鎖
mysql> unlock tables;
5. 掛載快照卷, 執(zhí)行數據備份
6. 備份完成后, 刪除快照卷
7. 制定好策略, 通過原卷備份二進制日志

1.6.2 實戰(zhàn)案例: 數據庫冷備份和還原

準備環(huán)境:

兩臺MySQL服務器, CentOS 8, 安裝最新版MySQL 8.0

1. 10.0.0.51: 作為源數據庫, 導入hellodb.sql
2. 10.0.0.52: 作為備份數據庫, 關掉源數據庫服務, 將源數據庫的數據文件拷貝到備份數據庫數據目錄下, 開啟mysql后, 會讀取源數據庫的數據文件

step1: 開啟源數據庫的二進制日志

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
log-bin=/data/log/binlog/mysql-binlog   
server-id=1 #如果是MySQL5.7版本, 開啟二進制必須指定server-id, 否則無法啟動, MySQL8.0不用

[root@mysql-1 ~]#mkdir -pv /data/log/binlog/
[root@mysql-1 ~]#chown -R mysql.mysql /data/log  #必須保證mysql用戶對于存放二進制日志的目錄有寫權限

step2: 開啟服務, 導入數據庫文件

[root@mysql-1 ~]#systemctl start mysqld
[root@mysql-1 ~]#mysql < hellodb_innodb.sql

step3: 在備份數據庫創(chuàng)建好保存二進制日志的目錄. 開啟二進制.

[root@mysql-2 ~]#mkdir -pv /data/log/binlog 
[root@mysql-2 ~]#chown -R mysql.mysql /data/log/binlog

[root@mysql-2 ~]#vim /etc/my.cnf.d/mysql-server.cnf 
log-bin=/data/log/binlog/mysql-binlog

step4: 源數據庫關閉服務, 將配置文件, 二進制文件, 數據目錄分別拷貝到備份服務器

# 兩臺主機需要安裝rsync, yum -y install rsync
[root@mysql-1 ~]#systemctl stop mysqld
[root@mysql-1 ~]#rsync -av /etc/my.cnf.d/mysql-server.cnf 10.0.0.52:/etc/my.cnf.d
[root@mysql-1 ~]#rsync -av /var/lib/mysql/ 10.0.0.52:/var/lib/mysql
[root@mysql-1 ~]#rsync -av /data/log/binlog/ 10.0.0.52:/data/log/binlog/

step5: 備份服務器開啟mysql服務, 驗證數據導入成功

[root@mysql-2 ~]#systemctl start mysqld
[root@mysql-2 ~]#mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| hellodb            |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

補充:

MyISAM不支持熱備份, InnoDB支持熱備份, 因為InnoDB支持事務, 靠的是事務的第三個隔離性, 可重復讀,保證了即使數據正在被修改, 備份時讀取的數據還是開始備份時的狀態(tài). 因此, 備份要以事務方式進行

如果通過物理備份, 拷文件, 需要冷備份, 確保沒有數據的變化. 采用冷備份, 備份數據庫無需關閉二進制. 因為數據是直接通過文件拷貝過去的, 不是在備份服務器執(zhí)行的命令

數據庫的數據文件, 如果啟動時,本身沒有, 那么會創(chuàng)建, 如果目的路徑有相應文件, 會讀取, 不會創(chuàng)建新的. 只會讀取數據文件, 二進制日志不會讀取

2 mysqldump 備份工具

2.1 mysqldump說明

邏輯備份工具

mysqldump是MySQL的客戶端命令, 通過mysql協(xié)議連接至MySQL服務器進行備份
mysqldump需要在mysql服務啟動時使用

三種命令格式

Usage: mysqldump [OPTIONS] database [tables] #支持指定數據庫和指定多表的備份, 但是數據庫本身的屬性定義不會備份
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...] #支持指定數據庫備份, 也支持備份多個數據庫, 包含數據庫本身定義也會備份
OR     mysqldump [OPTIONS] --all-databases [OPTIONS] #備份所有數據庫, 包含數據庫本身定義也會備份

mysqldump常用選項

-A, --all-databases #備份所有數據庫, 內容包含CREATE DATABASE語句, 也就是數據庫定義
-B, --database DB_NAME... #指定備份的數據庫, 內容包含CREATE DATABASE語句, 也就是數據庫定義
-R, --routines #備份所有存儲過程和函數
-E, --events #備份相關的所有event scheduler
--triggers #備份表相關觸發(fā)器, 默認啟用, 用--skip-triggers, 不備份觸發(fā)器
# -R, -E, --triggers需要看數據庫是否有存儲過程, 函數, 時間和觸發(fā)器, 如果沒有, 那么無需加這些選項
--default-character-set=utf8 #指定字符集, 需要和數據庫服務器的字符集一致
--master-data[=#] #此選項需啟用二進制日志
#1. 所備份的數據前加一條記錄為CHANGE MASTER TO語句, 非注釋, 不指定#, 默認是為1, 適合于主從復制多機使用
#2. 記錄為被注釋的 -- CHANGE MASTER TO語句, 適合于單機使用
#此選項會自動關閉--lock-tables功能, 自動打開-x | --lock-all-tables功能(除非開啟--single-transaction)
-F, --flush-logs #備份前滾動日志, 鎖定表完成后, 執(zhí)行flush logs命令, 生成新的二進制日志文件, 配合-A或-B選項時, 會導致刷新多次數據庫. 建議在同一時刻執(zhí)行轉儲和日志刷新, 可通過和--single-transaction或-x, --master--data一起使用實現, 此時只刷新一次二進制日志, 這樣備份之前的二進制日志在一個文件里, 備份開始后新產生的二進制日志會在新的文件里, 這樣可以輕松的實現二進制文件拷貝
--compact #去掉注釋, 適合調試, 生產不使用
-d, --no-data #只備份表結構, 不備份數據
-t, --no-create-info #只備份數據, 不備份表結構, 即不備份CREATE TABLES語句. 當需要重新建表, 只保留數據, 而表屬性, 字段屬性需要重新設定時, 可以使用. 因為備份出來的的
sql語句, 不會包含表創(chuàng)建語句, 可以先重新建表, 然后把sql語句導進去即可
-n, --no-create-db #不備份CREATE DATABASE語句, 可被-A或-B覆蓋
--flush-privileges #備份mysql數據庫或相關時需要使用, 如果涉及到了權限變化, 那么可以加該選項
-f, --force #忽略SQL錯誤, 繼續(xù)執(zhí)行
--hex-blob #使用十六進制符號轉儲二進制列, 當有包括binary, varbinary, blob, bit的數據類型的列時使用, 避免亂碼
-q, --quick #不緩存查詢, 直接輸出, 加快備份速度

mysqldump的MyISAM存儲引擎相關的備份選項:
MyISAM不支持事務, 只能支持溫備, 不支持熱備, 所以必須先鎖定要備份的庫, 而后啟動備份操作

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

mysqldump的InnoDB存儲引擎相關的備份選項:
InnoDB存儲引擎支持事務, 可以利用事務的相應隔離級別, 實現熱備, 也可以實現溫備,但不建議使用

--single-transaction
#此選項InnoDB中推薦使用, 不適用MyISAM, 此選項會開始備份前, 先執(zhí)行start transaction指令開啟事務
#此選項通過在單個事務中轉儲所有表來創(chuàng)建一致的快照. 僅適用于存儲在支持多版本控制的存儲引擎中的表(目前只有InnoDB可以); 轉儲不保證和其他存儲引擎保持一致. 在進行單事務轉儲時, 要確保有效的轉儲文件(正確的表內容和二進制日志位置), 沒有其他連接應該使用以下語句: alter table, drop table, rename table, truncate tables, 此選項和--lock-tables(此選項隱含提交掛起的事務)選項是相互排斥, 備份大型表時, 建議將--single-transaction和--quick結合一起使用

2.2 生產環(huán)境實戰(zhàn)備份策略

InnoDB完全備份建議

mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob > ${BACKUP}/fullback_${BACKUP_TIME}.sql

MyISAM完全備份建議

# myisam不支持事務, 因此只能熱備, -x加全局讀鎖, 只讀不寫
mysqldump -uroot -p -A -F -E -R --triggers -x --master-data=1 --flush-privileges --default-character-set=utf8 --hex-blob > ${BACKUP}/fullback_${BACKUP_TIME}.sql

2.3 mysqldump完全備份簡單案例

2.3.1 不加任何選項只備份單個庫, 或者單個庫的多個表

• mysqldump不加任何選項, 備份時只是把庫或表的內容顯示出來到屏幕. 需要重定向到文件里才行

[15:21:43 root@mysql-1 ~]#mysqldump hellodb
-- MySQL dump 10.13  Distrib 5.7.31, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: hellodb
-- ------------------------------------------------------
-- Server version   5.7.31

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;

[15:23:29 root@mysql-1 ~]#mysqldump hellodb teachers
-- MySQL dump 10.13  Distrib 5.7.31, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database: hellodb
-- ------------------------------------------------------
-- Server version   5.7.31

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;

將備份內容重定向到文件里

[15:25:19 root@mysql-1 ~]#mysqldump hellodb teachers > /data/hellodb_teachers.sql

• 測試備份恢復

刪除hellodb中的teachers表

[15:26:21 root@mysql-1 ~]#mysql  hellodb -e 'drop table teachers'

還原teachers表

[15:32:42 root@mysql-1 ~]#mysql hellodb  < /data/hellodb_teachers.sql 

mysql> show tables from hellodb;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.00 sec)

如果數據庫量比較大時, 可以壓縮, 比如導出整個數據庫

[15:32:48 root@mysql-1 ~]#mysqldump hellodb | gzip > /data/hellodb.sql.gz
[15:35:21 root@mysql-1 ~]#ll /data/hellodb.sql.gz 
-rw-r--r-- 1 root root 1837 Nov 24 15:35 /data/hellodb.sql.gz

[15:38:25 root@mysql-1 ~]#gzip -d /data/hellodb.sql.gz 
[15:39:38 root@mysql-1 ~]#ll /data/
total 16
-rw-r--r--  1 root  root  7646 Nov 24 15:35 hellodb.sql

但是直接用mysqldump hellodb 去備份, 這時導出來的數據是沒有指定數據庫信息的, 無法對整個數據庫進行還原, 只能還原數據庫里的表

[15:41:36 root@mysql-1 ~]#mysql < /data/hellodb.sql  
ERROR 1046 (3D000) at line 22: No database selected

需要先手動把數據庫創(chuàng)建起來, 再進行還原, 而且新創(chuàng)建的數據可以和源數據庫不同名

[15:43:08 root@mysql-1 ~]#mysql -e 'create database hello'
[15:43:20 root@mysql-1 ~]#mysql hello < /data/hellodb.sql 

但是新創(chuàng)建的庫, 和源庫的屬性不一定相等, 即使數據導進去也不一定能用, 因為源數據庫的庫信息沒有保留下來

mysqldump 不加選項的缺點總結:

1. mysqldump不加任何選項, 備份時只是把庫或表的內容顯示出來到屏幕. 需要重定向到文件里才行

2. 備份時候, mysqldump是陸續(xù)給每個表,先加寫鎖, 保證備份表的時候,沒人能修改數據, 表備份完事,再把鎖去掉,然后給下一張表加寫鎖,備份,再解鎖,這樣就會造成如果表之間有聯(lián)系, 一個表去掉寫鎖后發(fā)生了修改,另一個表正在被備份, 沒有寫入新的數據, 這樣數據就會有差錯, 因此mysqldump必須配合相應選項使用.

mysql> select * from course;
ERROR 1100 (HY000): Table 'course' was not locked with LOCK TABLES

3. 另外備份的時候, 即使指定了備份哪個數據庫,內容里也不會有庫的信息, 只有表的信息, 因此, 在還原時, 要先把庫創(chuàng)建出來, 新的庫名可以不一樣

4. 然而,  雖然表和表內容, 表屬性都記錄下來并且能被還原, 庫的屬性是沒記錄的

不建議用mysqldump [OPTIONS] database [tables]方式備份數據庫, 因為不會記錄庫信息, 和其他以上原因

2.3.2 --databases|-B選項, 備份單個或者多個數據庫

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

mysqldump -B 選項, 會把數據庫的屬性信息備份下來, 還原時會先根據數據庫原屬性創(chuàng)建數據庫

會記錄庫的屬性,信息, 還原時不需要手動創(chuàng)建數據庫, 直接導入文件即可

# 查看源數據庫屬性

mysql> show create database hellodb;
+----------+-----------------------------------------------------------------------------------------------------+
| Database | Create Database                                                                                     |
+----------+-----------------------------------------------------------------------------------------------------+
| hellodb  | CREATE DATABASE `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+-----------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

[15:48:34 root@mysql-1 ~]#mysqldump -B hellodb > /data/hello_B.sql

-- Current Database: `hellodb`
--

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET latin1 */;

[21:42:24 root@mysql-1 ~]#mysql -e 'drop database hellodb'
[21:42:43 root@mysql-1 ~]#mysql -e 'show databases'
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

還原h(huán)ellodb數據庫

[15:50:34 root@mysql-1 ~]#mysql < /data/hello_B.sql

mysql> show tables from hellodb;
+-----------------+
| Tables_in_hello |
+-----------------+
| classes         |
| coc             |
| courses         |
| scores          |
| students        |
| teachers        |
| toc             |
+-----------------+
7 rows in set (0.00 sec)

2.3.3 --all-databases|-A選項備份所有數據庫

mysqldump -A 備份所有數據庫, 包括除了information_schema,performance_schema和sys外所有的數據庫. 也就是mysql和其余業(yè)務數據庫都會備份

[21:47:59 root@mysql-1 ~]#mysqldump -A > /data/hellodb_A.sql
[15:56:05 root@mysql-1 ~]#grep -i '^create database' /data/hellodb_A.sql 

CREATE DATABASE /*!32312 IF NOT EXISTS*/ `hellodb` /*!40100 DEFAULT CHARACTER SET utf8 */ /*!80016 DEFAULT ENCRYPTION='N' */;
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `mysql` /*!40100 DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci */ /*!80016 DEFAULT ENCRYPTION='N' */;

不建議用-A, 因為會備份所有數據庫, 不能選擇性備份, 要做到按照不同業(yè)務,制定不同的備份計劃, 每個數據庫備份到獨立的文件里

2.3.4 分庫完全備份并壓縮, 將不同的數據庫備份到不同的目錄和文件中

注意: 還原數據庫時要把二進制日志關掉

方法1:

#!/bin/bash
DB=`mysql -e 'show databases' | grep -Ev 'Database|.*schema|sys'` #這里根據不同的數據庫版本, 需要排除除了mysql庫和業(yè)務數據庫意外的其他系統(tǒng)數據庫

for db in $DB; do
mysqldump -B $db | gzip > /data/$db.sql.gz
done

[19:30:59 root@mysql-2 ~]#bash /data/scripts/mysql_back1.sh
[19:31:05 root@mysql-2 ~]#ll /data/
total 232
-rw-r--r--  1 root  root    1903 Nov 24 19:31 hellodb.sql.gz
-rw-r--r--  1 root  root  230803 Nov 24 19:31 mysql.sql.gz

方法2:

mysql -e 'show databases' | grep -Ev 'Database|.*schema|sys' | while read db; do mysqldump -B $db | gzip > /data/$db.sql2.gz;done

方法3:

mysql -e 'show databases' | grep -Ev 'Database|.*schema|sys' | sed -nr 's#(.*)#mysqldump -B \1 | gzip > /data/\1.sql3.gz#p' | bash

#利用sed查找替換, 把mysqldump命令整個替換進去, 再把結果傳給bash執(zhí)行
[19:46:01 root@mysql-2 ~]#mysql -e 'show databases' | grep -Ev 'Database|.*schema|sys' | sed -nr 's#(.*)#mysqldump -B \1 | gzip > /data/\1.sql3.gz#p'
mysqldump -B hellodb | gzip > /data/hellodb.sql3.gz
mysqldump -B mysql | gzip > /data/mysql.sql3.gz

方法4: 用sed地址定位, 排除包含Database和sys的行

[19:46:18 root@mysql-2 ~]#mysql -e 'show databases' | sed -nr '/Database|sys|.*schema/!s#(.*)#mysqldump -B \1 | gzip > /data/\1.sql4.gz#p' 
mysqldump -B hellodb | gzip > /data/hellodb.sql4.gz
mysqldump -B mysql | gzip > /data/mysql.sql4.gz
[19:51:45 root@mysql-2 ~]#mysql -e 'show databases' | sed -nr '/Database|sys|.*schema/!s#(.*)#mysqldump -B \1 | gzip > /data/\1.sql4.gz#p' | bash

2.3.5 對二進制日志備份是增量備份還是差異備份?

二進制日志記錄的是數據庫的每個DML操作, 并且達到一定的條件或者手動觸發(fā), 就會生成新的二進制日志文件, 還原過程相當于把數據庫之前做的操作, 再重新做一遍, 不過需要把誤操作給刪除

• 假設, 每天都生成一個二進制日志文件, 那么一周就有7個二進制日志

如果每次備份都是備份當天產生的二進制日志, 那么就是增量備份
如果每次備份都是備份從第一天到當前時間點的所有二進制日志, 那就是差異備份

• 然而, 二進制并不是按照時間去觸發(fā)生成新的新的二進制日志, 即使用flush logs, 也難保證按照時間去生成新的二進制日志, 除非用mysqlbiglog, 按照每一天的時間點, 導出二進制, 那么這就是增量備份. 如果每次都是把從完全備份時間點開始, 到當前時間點的二進制日志備份出來, 那就是差異備份

如果每周都做一次mysqldump完全備份, 那么之前的二進制日志就可以備份到其他的位置, 保留一段時間后, 就可以刪除了

2.4 mysqldump重要選項

• --master-data[=#]

--master-data[=#] #此選項需啟用二進制日志
#1. 所備份的數據前加一條記錄為CHANGE MASTER TO語句, 非注釋, 不指定#, 默認是為1, 適合于主從復制多機使用
#2. 記錄為被注釋的 -- CHANGE MASTER TO語句, 適合于單機使用
# --master-data 此選項會自動關閉--lock-tables(加鎖)功能, 自動打開-x | --lock-all-tables功能(除非開啟--single-transaction)
# 因此, 備份過程中, 會對所有表加鎖, 導致所有表都無法寫入新的數據, 業(yè)務會受到影響

• --master-data[=#]選項的作用

記錄二進制日志, 和當前時間點的對應關系, 也就是在做完全備份時, 是從二進制日志哪個Pos位置開始備份的. 只有把做完全備份這個時間點的位置記錄下來, 才知道從二進制日志哪個Pos位置開始, 是沒有做完全備份的數據
CHANGE MASTER TO會記錄當前mysqldump執(zhí)行時, 二進制日志的位置, 那么從該位置往后的就是數據庫沒備份下來的, 之前的是備份下來的

比如: 當前執(zhí)行mysqldump時, --master-data記錄了二進制位置位Pos=100, 那么Pos=100之后的二進制日志, 就是沒有做完全備份的.

• --single-transaction

由于--master-data會在執(zhí)行備份過程中, 對所有表加鎖, 導致無法寫入, 影響業(yè)務, 因此, 可以使用--single-transaction選項, 表示以事務方式進行備份
事物的可重復讀隔離級別和MVCC保證了即使在備份過程中, 有新的數據寫入, 整個備份的過程中, 看到的數據也是同一個時間點的, 數據一致, 不會受新寫入數據的影響, 即使不加鎖, 也沒影響

• -F | --flush-logs

-F, --flush-logs #備份前生成新的二進制日志, 鎖定表完成后, 執(zhí)行flush logs命令, 生成新的二進制日志文件, 配合-A或-B選項時, 會導致刷新多次二進制日志, 備份了幾個數據庫就生成幾個二進制日志. 
建議在同一時刻執(zhí)行轉儲和日志刷新, 可通過和--single-transaction或-x, --master--data一起使用實現, 此時只刷新一次二進制日志, 這樣備份之前的二進制日志在一個文件里, 備份開始后新產生的二進制日志會在新的文件里, 這樣可以輕松的實現二進制文件拷貝, 可以把就舊二進制日志直接拷貝走, 因為這部分內容都存在了完全備份里了, 新的內容保留在了新的二進制日志

備份案例: InnoDB完全備份指定數據庫到指定目錄

#!/bin/bash
TIME=`date +%F_%H-%M-%S`
DIR=/backup/hellodb
DB=hellodb
PASS="centos"

mysqldump -uroot -p"$PASS" -F -E -R --triggers --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB |  gzip > ${DIR}/${DB}_${TIME}.sql.gz  

# 完全備份文件中的-- CHANGE MASTER TO, 表示, 開啟備份時間點, 二進制的日志位置位POS=156, 那么從mysql-binlog.000004文件的156開始, 都是沒有做完全備份的
[00:44:53 root@mysql-1 /backup/hellodb]#grep 'CHANGE MASTER TO'  hellodb_2021-06-13_00-31-39.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-binlog.000004', MASTER_LOG_POS=156;

備份后, 生成的sql文件就是該數據庫知道change master to位置的完全備份
-F --single-transaction會刷新日志, 生成新的日志文件, 新的二進制日志就是從備份時間點開始新增的數據
因為, 除了新的二進制日志文件, 其他的二進制日志內容都已經包含在了完全備份的文件里, 可以拷貝走, 單獨保存
可以每天做一次完全備份, 生成新的二進制日志, 一旦某一時間出現錯誤, 那么先停止服務, 比如只允許本地登錄, 或者添加防火墻策略. 恢復上一次的完全備份, 然后修改二進制日志文件, 刪除錯誤的語句, 再導入完全備份后新的二進制日志, 即可恢復數據

備份案例:InnoDB完全備份所有數據庫到指定的目錄

#!/bin/bash
  
TIME=`date +%F_%H-%M-%S`
DIR=/backup
PASS=centos
DB=`mysql -uroot -p"$PASS" -e 'show databases' | grep -Ev "^Database|.*schema|sys"`                                                                              


[ -d "$DIR" ] || mkdir $DIR

for db in $DB; do
    [ -d ${DIR}/${db} ] || mkdir ${DIR}/${db}
    mysqldump -uroot -p"$PASS" -F -B $db --single-transaction --master-data=2 --default-character-set=utf8 -q &> /dev/null | gzip > ${DIR}/${db}/${db}_${TIME}.sql.gz
done

2.5 利用二進制日志恢復誤刪的表

部署環(huán)境

yum -y install mysql-server

開啟二進制

[01:13:16 root@mysql-1 ~]#vim /etc/my.cnf.d/mysql-server.cnf
log-bin=/data/mysql/log/mysql-bin

創(chuàng)建二進制存放目錄

[01:13:29 root@mysql-1 ~]#mkdir -pv /data/mysql/log
mkdir: created directory '/data/mysql'
mkdir: created directory '/data/mysql/log'

[01:14:05 root@mysql-1 ~]#chown -R mysql.mysql /data/mysql/

[01:14:35 root@mysql-1 ~]#systemctl enable --now mysqld

導入hellodb數據庫

[01:28:21 root@mysql-1 ~]#mysql < hellodb_innodb.sql
[01:28:17 root@mysql-1 ~]#ll /data/mysql/log/
total 20
-rw-r----- 1 mysql mysql   179 Jun 13 01:14 mysql-bin.000001
-rw-r----- 1 mysql mysql 10752 Jun 13 01:26 mysql-bin.000002 # yum安裝的8.0版本, 開啟二進制后會生成兩個文件
-rw-r----- 1 mysql mysql    66 Jun 13 01:14 mysql-bin.index

#可以看到二進制日志存放到了mysql-bin.000002里, 并且記錄到了10752

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |    10752 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

實驗背景

image.png

案例說明: 每天凌晨00:00做完全備份, 周一白天正常修改數據, 晚上17:30, students表被誤刪除, 之后teachers表做了修改, 18:00時發(fā)現了students表被刪除了

恢復過程

1. 先利用前一天的完全備份, 恢復到周一凌晨00:00點的狀態(tài)
2. 把二進制日志中,刪除表的操作刪除
3. 用二進制恢復到18:00

step1: 模擬完全備份

[22:00:30 root@mysql-1 ~]#mkdir /backup
[22:00:40 root@mysql-1 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2 > /backup/allbackup_`date +%F_%T`.sql

#由于加了-F刷新日志, 因此會生成新的00003二進制文件, 意味著00003文件保存著這次完全備份后的二進制日志, 剛剛導入hellodb的二進制日志全部存放在000002文件里
[22:01:09 root@mysql-1 ~]#ll /data/mysql/log
total 24
-rw-r----- 1 mysql mysql   179 Nov 24 21:31 mysql-bin.000001
-rw-r----- 1 mysql mysql 10799 Nov 24 22:01 mysql-bin.000002
-rw-r----- 1 mysql mysql   156 Nov 24 22:01 mysql-bin.000003 #新的二進制日志文件
-rw-r----- 1 mysql mysql    87 Nov 24 22:01 mysql-bin.index

查看完全備份文件

[22:01:25 root@mysql-1 ~]#ll /backup/
total 1056
-rw-r--r-- 1 root root 1078502 Nov 24 22:01 allbackup_2020-11-24_22:01:06.sql

step2: 模擬白天17:30之前的數據更新

mysql> use hellodb;
Database changed


mysql> insert students (name,age,gender) value ("haha",18,'M');
Query OK, 1 row affected (0.00 sec)

mysql> insert students (name,age,gender) value ("lala",20,'F');
Query OK, 1 row affected (0.00 sec)

step3: 模擬17:30, 刪除student表

mysql> drop table students;
Query OK, 0 rows affected (0.06 sec)

mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| teachers          |
| toc               |
+-------------------+
6 rows in set (0.00 sec)

step4: 17:30后, 繼續(xù)修改teachers表

mysql> insert teachers (name,age,gender) values ('zhao',19,'M');
Query OK, 1 row affected (0.01 sec)

mysql> insert teachers (name,age,gender) values ('qian',18,'F');
Query OK, 1 row affected (0.00 sec)

step5: 發(fā)現故障后, 需要暫停業(yè)務, 通過防火墻或者只允許本地socket登錄

下面恢復數據

step1: 找到二進制日志的位置

先查看二進制日志, 可以看到00003文件數據增加了, 但是實際情況是不知道二進制具體位置的, 需要根據完全備份來判斷完全備份備份到的二進制位置

[22:10:59 root@mysql-1 ~]#ll /data/mysql/log
total 24
-rw-r----- 1 mysql mysql   179 Nov 24 21:31 mysql-bin.000001
-rw-r----- 1 mysql mysql 10799 Nov 24 22:01 mysql-bin.000002
-rw-r----- 1 mysql mysql  1582 Nov 24 22:08 mysql-bin.000003
-rw-r----- 1 mysql mysql    87 Nov 24 22:01 mysql-bin.index

#查看完全備份文件
[22:11:19 root@mysql-1 ~]#grep '^-- CHANGE MASTER' /backup/allbackup_2020-11-24_22\:01\:06.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=156;
#這里可以看到上一次完全備份, 備份到了00003文件的位置ID156, 也就是說該文件156以后的都是新增的二進制日志, 如果00003以后還有其他文件, 那么要把這些都合并在一起, 一起重新導入

step2: 將新增的二進制日志內容導出來

[22:11:22 root@mysql-1 ~]#mysqlbinlog --start-position=156 /data/mysql/log/mysql-bin.000003 > /backup/inc.sql
#此時導出來的數據, 就是完全備份后的增量備份
#如果除了00003還有其他的二進制文件, 那么要一并導出, 假如還有00004文件, 那么需要追加到/backup/inc.sql文件里
#mysqlbinlog  /data/mysql/log/mysql-bin.000004 >> /backup/inc.sql
#除了00003外, 其余文件的所有內容都是新增的二進制日志內容, 因此無需判斷位置

step3: 將此前刪除students表的語句從二進制備份中刪除

#如果導出來的增量備份過大, 可以使用grep先把SQL語句過濾出來, 然后用sed去刪除, 一定要確保刪的是對的SQL語句
[22:19:02 root@mysql-1 ~]#grep -i 'drop' /backup/inc.sql 
DROP TABLE `students` /* generated by server */

step4: 定位到正確的需要刪除的語句后, 用sed刪除

[22:19:13 root@mysql-1 ~]#sed -i.bak '/DROP TABLE `students`/d' /backup/inc.sql #sed -i.bak把增量備份文件先做個備份
[22:20:31 root@mysql-1 ~]#grep -i 'drop' /backup/inc.sql  #確保錯誤的語句已經被刪除

step5: 關閉數據庫二進制

mysql> set sql_log_bin=off;

step6: 導入完全備份

mysql> source /backup/allbackup_2020-11-24_22:01:06.sql

step7: 導入修改后的增量備份

mysql> source /backup/inc.sql

step8: 開啟二進制

mysql> set sql_log_bin=on;

還原完成, 檢查數據庫

mysql> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes           |
| coc               |
| courses           |
| scores            |
| students          |  #students表還原成功
| teachers          |
| toc               |
+-------------------+
7 rows in set (0.01 sec)

|    26 | haha          |  18 | M      |    NULL |      NULL |
|    27 | lala          |  20 | F      |    NULL |      NULL |
+-------+---------------+-----+--------+---------+-----------+
|   5 | zhao          |  19 | M      |
|   6 | qian          |  18 | F      |
+-----+---------------+-----+--------+
#新增的數據都在

如果刪除的是一個數據庫, 那么在mysqlbinlog導出的文件中, 要把從刪庫命令到最后的所有數據都刪除
總結: 還原的過程, 就是利用上一次的完全備份, 先還原到完全備份那個時間點, 這樣所有在完全備份之后的操作, 就相當于沒執(zhí)行, 因為完全備份還原, 會把所有表重新創(chuàng)建, 插入完全備份那個時間點的數據, 之后再根據完全備份的文件里的CHANGE MASTER TO定位到此次完全備份備份到了哪個二進制文件的哪個位置id, 之后把從這個位置id開始的所有二進制文件都導出來到同一個文件里, 那么這個文件就包含了從完全備份到發(fā)現故障時的所有操作,之后把錯誤的執(zhí)行命令刪除, 然后先關閉二進制日志, 先導入完全備份, 再倒入二進制的增量備份,再開啟二進制即可還原數據

這種還原方式有一定的風險, 按照上面的案例, students表是在17:30被刪除的, 18:00發(fā)現了故障, 假如18:10把表恢復了,那么恢復的students表利用完全備份和二進制恢復后也是處于刪除時17:30的狀態(tài), 而其余表是18:00時的狀態(tài), 因為其余表是沒有被刪除的, 它們的狀態(tài)是18:00發(fā)現故障暫停服務前的狀態(tài), 這就造成了students表的時間點和其他表不一樣,如果數據之間有關聯(lián)就會產生問題, 造成數據不同步

注意要點: 導入完全備份和增量備份前, 需要停止二進制服務, 同時整個業(yè)務都要暫停訪問, 避免有用戶還在連接數據庫

建議: 每周做一次完全備份, 把整個數據庫的內容都拷出來, 備份時用-F每次備份完都刷一下二進制日志, 這樣新生成的二進制日志就是新的內容, 另外每天做一次差異備份, 把從上次完全備份后生成的新的所有的二進制日志都備份,這樣每天都備份了最新的二進制信息, 這樣一旦出現問題, 可以利用上一次完全備份, 和最新的所有的二進制差異備份, 去進行數據恢復. 這里備份的二進制信息指的是, 把二進制文件通過mysqlbinlog導出到一個文件里.

如果做增量備份, 那么需要每次把之前備份過的內容剔除, 把剩下的做備份, 計算比較麻煩, 不如每次都做差異備份, 把上一次完全備份后生成的二進制文件都拷出來, 利用增量復制工具比如rsync, 只去復制發(fā)生變化的文件, 這樣就不用每次都把之前復制過的二進制文件再復制一次, 可以加快復制速度. 通過增量備份工具, 最終實現還是增量備份的效果