MySQL備份與恢復

1、資料備份概述

備份是資料安全的最後一道防線，對於任何資料丟失的場景，備份雖然不一定能恢復百分之百的資料(取決於備份週期)，但至少能將損失降到最低。

資料丟失的場景舉例：

人為操作失誤造成某些資料被誤操作
軟體 BUG 造成部分資料或全部資料丟失
硬體故障造成資料庫部分資料或全部資料丟失
安全漏洞被入侵資料惡意破壞

1.1 衡量備份恢復重要指標

衡量備份恢復有兩個重要的指標：

恢復點目標(RPO)
- 恢復點目標是指資料能恢復到什麼程度
恢復時間目標(RTO)
- 恢復時間目標是指資料恢復需要多長時間

1.2 資料庫備份分類

資料庫備份方式分很多種，從物理與邏輯的角度來看，備份可分為：

物理備份：指對資料庫作業系統的物理檔案（如資料檔案、紀錄檔檔案等）的備份。物理備份又可以分為離線備份(冷備份)和聯機備份(熱備份)。
- 冷備份:在關閉資料庫時進行的備份操作，能夠較好地保證資料庫的完整性。
- 熱備份:在資料庫執行狀態中進行操作，這種備份方法依賴於資料庫的紀錄檔檔案。
邏輯備份：指對資料庫邏輯元件(如"表"等資料庫物件)的備份。

資料庫的備份從策略角度來看，備份可分為以下幾類：

完全備份：每次對資料進行完整的備份。可以備份整個資料庫，包含使用者表、系統表、索引、檢視和儲存過程等所有資料庫物件。但它需要花費更多的時間和空間，所以，做一次完全備份的週期要長些。
差異備份：備份那些自從上次完全備份之後被修改過的檔案，只備份資料庫部分的內容。它比最初的完全備份小，因為只包含自上次完全備份以來所改變的資料庫。它的優點是儲存和恢復速度快。
增量備份：只有那些在上次完全備份或者增量備份後被修改的檔案才會被備份。

2、MySQL備份工具

2.1 mysqldump備份工具

MySQL官方提供了Mysqldump邏輯備份工具，它的備份原理是通過協定連線到 MySQL 資料庫，將需要備份的資料查詢出來，將查詢出的資料轉換成對應的insert語句，當我們需要還原這些資料時，只要執行這些insert語句，即可將對應的資料還原。

# mysqldump命令語法：
mysqldump [選項] 資料庫名 [表名] > 備份檔名

# 資料恢復
mysql [選項] 資料庫名 [表名] > 備份檔名
source 備份檔名

選項列表：

選項名	含義
--host	伺服器IP地址
--port	伺服器埠號
--user	MySQL 使用者名稱
--password	MySQL 密碼
--databases	指定要備份的資料庫
--all-databases	備份mysql伺服器上的所有資料庫
--compact	壓縮模式，產生更少的輸出
--comments	新增註釋資訊
--complete-insert	輸出完成的插入語句
--lock-tables	備份前，鎖定所有資料庫表
–no-create-db / --no-create-info	禁止生成建立資料庫語句
--force	當出現錯誤時仍然繼續備份操作
--default-character-set	指定預設字元集
--add-locks	備份資料庫表時鎖定資料庫表

2.2 Xtrabackup備份工具

XtraBackup(PXB)工具是Percona公司用perl語言開發的一個用於 MySQL資料庫物理熱備的備份工具，能夠非常快速地備份與恢復mysql資料庫，且支援線上熱備份（備份時不影響資料讀寫）。

Xtrabackup中包含兩個工具：

xtrabackup ：用於熱備份innodb,xtradb引擎表的工具，不能備份其他表。
innobackupex ：提供了用於myisam(會鎖表)和innodb引擎，及混合使用引擎備份的能力。

Xtrabackup的優點：

備份速度快，物理備份可靠
備份過程不會打斷正在執行的事務（無需鎖表）
能夠基於壓縮等功能節約磁碟空間和流量
自動備份校驗
還原速度快
可以流傳，將備份傳輸到另外一臺機器上
在不增加伺服器負載的情況備份資料
支援增量備份

# 語法：
xtrabackup | innobackupex  [--defaults-file=#] [--backup | --prepare | --copy-back | --move-back] [OPTIONS]

Xtrabackup工具備份常用選項：

選項類別	命令全名	含義
通用選項	--user=name	資料庫賬號名
通用選項	--password	資料庫密碼
通用選項	--host=name	資料庫主機IP地址
通用選項	--port=name	資料庫主機埠號
通用選項	--defaults-file	定義包含預設設定的檔案的路徑。
通用選項	--socket=name	資料庫socket檔案地址
備份選項	--backup	建立備份並且放入--target-dir目錄中
備份選項	--target-dir	備份檔案的存放目錄路徑，如果目錄不存在，xtrabakcup會建立。如果目錄存在且為空則成功。不會覆蓋已存在的檔案。
備份選項	--databases=name	指定要備份的資料庫
增量選項	--incremental-basedir	使用增量備份
壓縮/解壓選項	--compress	compress壓縮
壓縮/解壓選項	--compress-threads=n	啟用n個執行緒進行壓縮
壓縮/解壓選項	--decompress	準備資料之前先解壓
準備選項	--prepare	實現同步回滾未提交的事務及同步已經提交的事務至資料檔案使資料檔案處於一致性狀態
準備選項	--apply-log-only	阻止回滾未提完成的事務（最後一次增量備份的準備不需要此選項）
準備選項	--incremental-dir	指定增量備份，與全備合併
還原選項	--copy-back	做資料恢復時將備份資料檔案拷貝到MySQL伺服器的datadir。
還原選項	--move-back	這個選項與–copy-back相似，唯一的區別是它不拷貝檔案，而是移動檔案到目的地。這個選項會移除backup檔案，用時候必須小心。

# 下載安裝Xtrabackup備份工具
[root@localhost ~]# wget https://downloads.percona.com/downloads/Percona-XtraBackup-LATEST/Percona-XtraBackup-8.0.22-15/binary/redhat/8/x86_64/percona-xtrabackup-80-8.0.22-15.1.el8.x86_64.rpm


[root@localhost ~]#dnf -y localinstall percona-xtrabackup-80-8.0.22-15.1.el8.x86_64.rpm 

# 完全備份
[root@localhost ~]#trabackup --backup --databases=zsl --target-dir=/backup/xtrabackup/ -uroot -pPasswd123!

# 恢復階段：準備備份，恢復之前需要準備備份
[root@localhost ~]#xtrabackup --prepare --target-dir=/backup/xtrabackup/

# 恢復資料（保證要還原的資料庫伺服器的data目錄為空）
[root@localhost ~]#rm -rf /var/lib/mysql/*		#模擬資料丟失

[root@localhost ~]#xtrabackup --copy-back --target-dir=/backup/xtrabackup/

[root@localhost ~]#chown -R mysql:mysql /var/lib/mysql    

[root@localhost ~]#systemctl restart mysqld

增量備份：

# 先建立完全備份
[root@localhost ~]#xtrabackup --backup --databases=test --target-dir=/backup/xtrabackup/ -uroot -p123456

# 建立第一次增量備份
[root@localhost ~]#xtrabackup --backup --databases=test --target-dir=/backup/inc1/ --incremental-basedir=/backup/xtrabackup/  -uroot -p123456

# 建立第二次增量備份
[root@localhost ~]#xtrabackup --backup --databases=test --target-dir=/backup/inc2/ --incremental-basedir=/backup/inc1/ -uroot -p123456

# 恢復階段：準備全量備份
[root@localhost ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/xtrabackup/

# 準備第一次增量備份，將第一次增量備份與全備合併
[root@localhost ~]#xtrabackup --prepare --apply-log-only --target-dir=/backup/xtrabackup/ --incremental-dir=/backup/inc1

# 準備第二次增量備份，將第二次增量備份與全備合併
[root@localhost ~]#xtrabackup --prepare --target-dir=/backup/xtrabackup/ --incremental-dir=/backup/inc2/

# 恢復資料（保證要還原的資料庫伺服器的data目錄為空）
[root@localhost ~]#rm -rf /var/lib/mysql/*		# 模擬資料丟失

[root@localhost ~]#xtrabackup --copy-back --target-dir=/backup/xtrabackup/

[root@localhost ~]#chown -R mysql:mysql /var/lib/mysql    

[root@localhost ~]#systemctl restart mysqld

2.3 Mariabackup備份工具

Mariabackup是MariaDB提供的一個開源工具，用於對InnoDB，Aria和MyISAM表進行物理線上備份。這個工具基於Percona的XtraBackup解決方案。

# 語法：
mariabackup [--defaults-file=#] [--backup | --prepare | --copy-back | --move-back] [OPTIONS]

# 常用選項
- -backup									#備份資料庫
- -copy-back							 #將備份還原到資料目錄
- -defaults-file					 #定義包含預設設定的檔案的路徑
-H, --host								 #定義要備份的MariaDB伺服器的主機
- -incremental-basedir			 #定義是否要增加備份
- -incremental-dir					#定義是否要增加準備好的備份
- -move-back								#將備份還原到資料目錄
-p, --password							#定義用於連線MariaDB Server的密碼
-P, --port									#定義要連線的伺服器埠
- -prepare									#準備現有備份以還原到MariaDB伺服器
-S, --socket								#定義用於連線本地資料庫的通訊端
- -user											#定義用於連線MariaDB伺服器的使用者名稱
- -version-check						 #啟用版本檢查
- -version									#列印版本資訊

完全備份與恢復：

# 全量備份
mariabackup --backup --target-dir /root/mariadb-backup-`date +%F` --user root --password "1"


# 恢復階段：準備全備資料，恢復之前必須準備
mariabackup --prepare --target-dir /root/mariadb-backup-2022-07-28/ --user root --password "1"

# 恢復資料（保證要還原的資料庫伺服器的data目錄為空）
rm -rf /var/lib/mysql/*		#模擬資料丟失

mariabackup --copy-back --target-dir /root/mariadb-backup-2022-07-28/ --user root --password "1"

chown -R mysql.mysql /var/lib/mysql/

systemctl restart mariadb.service

增量備份與恢復：

# 全量備份，增量備份前需要先進行一次全量備份
mariabackup --backup --target-dir /root/mariadb-backup-`date +%F` --user root --password "1"

# 基於全量備份，進行第一次增量備份
mariabackup --backup --target-dir /root/mariadb-backup-`date +%F`-inc1 --incremental-basedir /root/mariadb-backup-2022-07-28/ --user root --password "1"

# 恢復階段：現在有2個備份，一個是全備一個是增量備份
# 準備全備資料
mariabackup --prepare --apply-log-only --target-dir /root/mariadb-backup-2022-07-28/ --user root --password "1"

# 將增量備份與全備合併
mariabackup --prepare --target-dir /root/mariadb-backup-2022-07-28/ --incremental-dir /root/mariadb-backup-2022-07-28-inc1/ --user root --password "1"

# 如果有多次增量備份，按照增量備份順序依次將增量備份與全備合併，記得加--apply-log-only選項，最後一次增量備份不需要新增該選項

# 恢復資料（保證要還原的資料庫伺服器的data目錄為空）
rm -rf /var/lib/mysql/*		#模擬資料丟失

mariabackup --copy-back --target-dir /root/mariadb-backup-2022-07-28/ --user root --password "1"

chown -R mysql.mysql /var/lib/mysql/

systemctl restart mariadb.service

3、mysql全量備份與恢復範例演示

3.1 mysql全量備份

# 備份整個資料庫(全備)
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zsl                |
+--------------------+
5 rows in set (0.00 sec)

mysql> use zsl;
Database changed
mysql> show tables;
+---------------+
| Tables_in_zsl |
+---------------+
| course        |
| students      |
| teacher       |
+---------------+
3 rows in set (0.00 sec)

[root@localhost ~]# mysqldump -uroot -pPasswd123! --all-databases > all-database-$(date '+%F-%H-%M-%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-database-2022-07-29-10-45-04.sql  anaconda-ks.cfg


# 備份zsl庫的course表、students表和teacher表
[root@localhost ~]# mysqldump -uroot -pPasswd123! zsl course students teacher > table-$(date '+%F-%H-%M-%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-database-2022-07-29-10-45-04.sql  anaconda-ks.cfg  table-2022-07-29-10-45-27.sql


# 備份zsl庫
[root@localhost ~]# mysqldump -uroot -pPasswd123! --databases zsl  > zsl-database-$(date '+%F-%H-%M-%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ls
all-database-2022-07-29-10-45-04.sql  table-2022-07-29-10-45-27.sql
anaconda-ks.cfg                       zsl-database-2022-07-29-10-47-47.sql

3.2 mysql資料恢復

# 模擬誤刪zsl資料庫
mysql> drop database zsl;
Query OK, 3 rows affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)


# 恢復zsl資料庫
##方法一：系統行命令資料恢復
[root@localhost ~]# mysql -uroot -pPasswd123! < zsl-database-2022-07-29-10-47-47.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zsl                |
+--------------------+
5 rows in set (0.00 sec)

方法二：source資料恢復
mysql> source zsl-database-2022-07-29-10-47-47.sql;
......
Query OK, 5 rows affected (0.01 sec)
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
......
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zsl                |
+--------------------+
5 rows in set (0.00 sec)


# 模擬刪除zsl庫的course表、students表
mysql> drop table course;
Query OK, 0 rows affected (0.00 sec)

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

mysql> show tables;
+---------------+
| Tables_in_zsl |
+---------------+
| teacher       |
+---------------+
1 row in set (0.00 sec)


# 恢復zsl庫的course表、students表
mysql> source table-2022-07-29-10-45-27.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
......
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+---------------+
| Tables_in_zsl |
+---------------+
| course        |
| students      |
| teacher       |
+---------------+
3 rows in set (0.00 sec)


# 模擬刪除整個資料庫
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zsl                |
+--------------------+
5 rows in set (0.00 sec)

mysql> drop database zsl;
Query OK, 3 rows affected (0.00 sec)

mysql> drop database sys;
Query OK, 101 rows affected (0.02 sec)

mysql> drop database mysql;
Query OK, 31 rows affected, 2 warnings (0.03 sec)

mysql> drop database performance_schema;
Query OK, 87 rows affected, 2 warnings (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
+--------------------+
1 row in set (0.01 sec)

# 恢復整個資料庫
[root@localhost ~]# mysql -uroot -pPasswd123! < all-database-2022-07-29-10-45-04.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| zsl                |
+--------------------+
3 rows in set (0.00 sec)

4、mysql差異備份與恢復範例演示

4.1 mysql差異備份

開啟MySQL伺服器的二進位制紀錄檔功能

[root@localhost ~]# vim /etc/my.cnf 
[root@localhost ~]# cat /etc/my.cnf 
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
server-id=1					      #設定伺服器識別符號
log-bin=mysql_bin 				  #開啟二進位制紀錄檔功能

[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS!

對資料庫進行完全備份

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zsl                |
+--------------------+
5 rows in set (0.00 sec)

mysql> show tables from zsl;
+---------------+
| Tables_in_zsl |
+---------------+
| course        |
| students      |
| teacher       |
+---------------+
3 rows in set (0.00 sec)

mysql> select * from zsl.course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | HTML        |
|  2 | JAVA        |
|  3 | MySQL       |
|  4 | Python      |
|  5 | C++         |
+----+-------------+
5 rows in set (0.01 sec)

mysql> select * from zsl.students;
+----+-------+------+------+--------+-----------+
| id | name  | age  | sex  | height | course_id |
+----+-------+------+------+--------+-----------+
|  1 | meng  |   25 | 女   |    160 |         1 |
|  2 | ke    |   22 | 男   |    180 |         3 |
|  3 | yang  |   18 | 男   |    175 |         2 |
|  4 | ding  |   19 | 女   |    165 |         4 |
|  5 | zhong |   20 | 男   |    165 |         5 |
+----+-------+------+------+--------+-----------+
5 rows in set (0.00 sec)

mysql> select * from zsl.teacher;
+----+------+------+------+--------+-----------+
| id | name | age  | sex  | height | course_id |
+----+------+------+------+--------+-----------+
|  1 | aa   |   25 | 女   |    160 |         1 |
|  2 | bb   |   22 | 男   |    180 |         3 |
|  3 | cc   |   18 | 男   |    175 |         2 |
|  4 | dd   |   19 | 女   |    165 |         4 |
|  5 | ee   |   20 | 男   |    165 |         5 |
+----+------+------+------+--------+-----------+
5 rows in set (0.00 sec)


# 開始完全備份
[root@localhost ~]# mysqldump -uroot -pPasswd123! --single-transaction --flush-logs --master-data=2 --all-databases --delete-master-logs > all-database-$(date '+%F-%H-%M-%S').sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll
total 1732
-rw-r--r--. 1 root root 878539 Jul 29 10:45 all-database-2022-07-29-10-45-04.sql
-rw-r--r--. 1 root root 878710 Jul 29 11:21 all-database-2022-07-29-11-21-10.sql
-rw-------. 1 root root   1097 Jul 21 18:41 anaconda-ks.cfg
-rw-r--r--. 1 root root   3726 Jul 29 10:45 table-2022-07-29-10-45-27.sql
-rw-r--r--. 1 root root   3862 Jul 29 10:47 zsl-database-2022-07-29-10-47-47.sql


# 新增或刪減內容
mysql> select * from course;
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | HTML        |
|  2 | JAVA        |
|  3 | MySQL       |
|  5 | C++         |
|  6 | wuli        |
|  7 | huaxue      |
|  8 | shuxue      |
|  9 | yuwen       |
+----+-------------+
8 rows in set (0.00 sec)

mysql> select * from students;
+----+-------+------+------+--------+-----------+
| id | name  | age  | sex  | height | course_id |
+----+-------+------+------+--------+-----------+
|  1 | meng  |   25 | 女   |    160 |         1 |
|  2 | ke    |   22 | 男   |    180 |         3 |
|  3 | yang  |   18 | 男   |    175 |         2 |
|  4 | ding  |   19 | 女   |    165 |         4 |
|  5 | zhong |   20 | 男   |    165 |         5 |
+----+-------+------+------+--------+-----------+
5 rows in set (0.00 sec)

mysql> select * from teacher;
+----+------+------+------+--------+-----------+
| id | name | age  | sex  | height | course_id |
+----+------+------+------+--------+-----------+
|  1 | aa   |   25 | 女   |    160 |         1 |
|  2 | bb   |   22 | 男   |    180 |         3 |
|  3 | cc   |   18 | 男   |    175 |         2 |
|  4 | dd   |   19 | 女   |    165 |         4 |
|  5 | ee   |   20 | 男   |    165 |         5 |
|  6 | qq   |   20 | 男   |    156 |         2 |
|  7 | gg   |   60 | 男   |    186 |         3 |
|  8 | ff   |   20 | 女   |    185 |         2 |
|  9 | rr   |   15 | 男   |    189 |         9 |
+----+------+------+------+--------+-----------+
9 rows in set (0.00 sec)

4.2 mysql差異備份資料恢復

# 模擬誤刪資料
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'drop database zsl;'
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
# 由上可以看到zsl這個資料庫已被刪除


# 重新整理建立新的二進位制紀錄檔
[root@localhost ~]# ll /opt/data/
total 122984
-rw-r-----. 1 mysql mysql       56 Jul 29 02:43 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 client-key.pem
-rw-r-----. 1 mysql mysql      669 Jul 29 11:12 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:39 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:39 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 29 02:43 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:21 ibtmp1
-rw-r-----. 1 mysql mysql    20346 Jul 29 11:12 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jul 29 11:10 mysql
-rw-r-----. 1 mysql mysql     3913 Jul 29 11:39 mysql_bin.000003
-rw-r-----. 1 mysql mysql       19 Jul 29 11:21 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 29 11:12 mysql.pid
-rw-r--r--. 1 root  root         6 Jul 29 11:10 mysql_upgrade_info
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 29 02:43 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 29 02:43 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 sys
[root@localhost ~]#  mysqladmin -uroot -pPasswd123! flush-logs
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# ll /opt/data/
total 122988
-rw-r-----. 1 mysql mysql       56 Jul 29 02:43 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 client-key.pem
-rw-r-----. 1 mysql mysql      669 Jul 29 11:12 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:41 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:41 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 29 02:43 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:21 ibtmp1
-rw-r-----. 1 mysql mysql    20346 Jul 29 11:12 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jul 29 11:10 mysql
-rw-r-----. 1 mysql mysql     3960 Jul 29 11:41 mysql_bin.000003
-rw-r-----. 1 mysql mysql      154 Jul 29 11:41 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 Jul 29 11:41 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 29 11:12 mysql.pid
-rw-r--r--. 1 root  root         6 Jul 29 11:10 mysql_upgrade_info
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 29 02:43 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 29 02:43 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 sys


# 恢復完全備份
[root@localhost ~]# mysql -uroot -pPasswd123! < all-database-2022-07-29-11-21-10.sql 
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'show databases;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| zsl                |
+--------------------+
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'show tables from zsl;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+---------------+
| Tables_in_zsl |
+---------------+
| course        |
| students      |
| teacher       |
+---------------+
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'select * from zsl.course;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | HTML        |
|  2 | JAVA        |
|  3 | MySQL       |
|  4 | Python      |
|  5 | C++         |
+----+-------------+
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'select * from zsl.students;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------+------+------+--------+-----------+
| id | name  | age  | sex  | height | course_id |
+----+-------+------+------+--------+-----------+
|  1 | meng  |   25 | 女   |    160 |         1 |
|  2 | ke    |   22 | 男   |    180 |         3 |
|  3 | yang  |   18 | 男   |    175 |         2 |
|  4 | ding  |   19 | 女   |    165 |         4 |
|  5 | zhong |   20 | 男   |    165 |         5 |
+----+-------+------+------+--------+-----------+
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'select * from zsl.teacher;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+------+------+------+--------+-----------+
| id | name | age  | sex  | height | course_id |
+----+------+------+------+--------+-----------+
|  1 | aa   |   25 | 女   |    160 |         1 |
|  2 | bb   |   22 | 男   |    180 |         3 |
|  3 | cc   |   18 | 男   |    175 |         2 |
|  4 | dd   |   19 | 女   |    165 |         4 |
|  5 | ee   |   20 | 男   |    165 |         5 |
+----+------+------+------+--------+-----------+


# 恢復差異備份
[root@localhost ~]# ll /opt/data/
total 189544
-rw-r-----. 1 mysql mysql       56 Jul 29 02:43 auto.cnf
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 ca-key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 ca.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 client-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 client-key.pem
-rw-r-----. 1 mysql mysql      669 Jul 29 11:12 ib_buffer_pool
-rw-r-----. 1 mysql mysql 79691776 Jul 29 11:47 ibdata1
-rw-r-----. 1 mysql mysql 50331648 Jul 29 11:47 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 Jul 29 02:43 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 Jul 29 11:21 ibtmp1
-rw-r-----. 1 mysql mysql    20346 Jul 29 11:12 localhost.localdomain.err
drwxr-x---. 2 mysql mysql     4096 Jul 29 11:45 mysql
-rw-r-----. 1 mysql mysql     3960 Jul 29 11:41 mysql_bin.000003
-rw-r-----. 1 mysql mysql   859354 Jul 29 11:45 mysql_bin.000004
-rw-r-----. 1 mysql mysql       38 Jul 29 11:41 mysql_bin.index
-rw-r-----. 1 mysql mysql        5 Jul 29 11:12 mysql.pid
-rw-r--r--. 1 root  root         6 Jul 29 11:10 mysql_upgrade_info
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 performance_schema
-rw-------. 1 mysql mysql     1676 Jul 29 02:43 private_key.pem
-rw-r--r--. 1 mysql mysql      452 Jul 29 02:43 public_key.pem
-rw-r--r--. 1 mysql mysql     1112 Jul 29 02:43 server-cert.pem
-rw-------. 1 mysql mysql     1680 Jul 29 02:43 server-key.pem
drwxr-x---. 2 mysql mysql     8192 Jul 29 11:10 sys
drwxr-x---. 2 mysql mysql      134 Jul 29 11:45 zsl

# 檢查誤刪資料庫的位置在什麼地方
mysql> show binlog events in 'mysql_bin.000003';
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| Log_name         | Pos  | Event_type     | Server_id | End_log_pos | Info                                  |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
| mysql_bin.000003 |    4 | Format_desc    |         1 |         123 | Server ver: 5.7.37-log, Binlog ver: 4 |
| mysql_bin.000003 |  123 | Previous_gtids |         1 |         154 |                                       |
| mysql_bin.000003 |  154 | Anonymous_Gtid |         1 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 |  219 | Query          |         1 |         290 | BEGIN                                 |
| mysql_bin.000003 |  290 | Table_map      |         1 |         349 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 |  349 | Write_rows     |         1 |         510 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 |  510 | Xid            |         1 |         541 | COMMIT /* xid=988 */                  |
| mysql_bin.000003 |  541 | Anonymous_Gtid |         1 |         606 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 |  606 | Query          |         1 |         677 | BEGIN                                 |
| mysql_bin.000003 |  677 | Table_map      |         1 |         728 | table_id: 178 (zsl.course)            |
| mysql_bin.000003 |  728 | Write_rows     |         1 |         808 | table_id: 178 flags: STMT_END_F       |
| mysql_bin.000003 |  808 | Xid            |         1 |         839 | COMMIT /* xid=989 */                  |
| mysql_bin.000003 |  839 | Anonymous_Gtid |         1 |         904 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 |  904 | Query          |         1 |         975 | BEGIN                                 |
| mysql_bin.000003 |  975 | Table_map      |         1 |        1034 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 1034 | Write_rows     |         1 |        1201 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 1201 | Xid            |         1 |        1232 | COMMIT /* xid=990 */                  |
| mysql_bin.000003 | 1232 | Anonymous_Gtid |         1 |        1297 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 1297 | Query          |         1 |        1368 | BEGIN                                 |
| mysql_bin.000003 | 1368 | Table_map      |         1 |        1427 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 1427 | Delete_rows    |         1 |        1483 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 1483 | Xid            |         1 |        1514 | COMMIT /* xid=994 */                  |
| mysql_bin.000003 | 1514 | Anonymous_Gtid |         1 |        1579 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 1579 | Query          |         1 |        1650 | BEGIN                                 |
| mysql_bin.000003 | 1650 | Table_map      |         1 |        1709 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 1709 | Delete_rows    |         1 |        1765 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 1765 | Xid            |         1 |        1796 | COMMIT /* xid=995 */                  |
| mysql_bin.000003 | 1796 | Anonymous_Gtid |         1 |        1861 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 1861 | Query          |         1 |        1932 | BEGIN                                 |
| mysql_bin.000003 | 1932 | Table_map      |         1 |        1991 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 1991 | Delete_rows    |         1 |        2048 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 2048 | Xid            |         1 |        2079 | COMMIT /* xid=996 */                  |
| mysql_bin.000003 | 2079 | Anonymous_Gtid |         1 |        2144 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 2144 | Query          |         1 |        2215 | BEGIN                                 |
| mysql_bin.000003 | 2215 | Table_map      |         1 |        2274 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 2274 | Delete_rows    |         1 |        2331 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 2331 | Xid            |         1 |        2362 | COMMIT /* xid=997 */                  |
| mysql_bin.000003 | 2362 | Anonymous_Gtid |         1 |        2427 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 2427 | Query          |         1 |        2498 | BEGIN                                 |
| mysql_bin.000003 | 2498 | Table_map      |         1 |        2557 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 2557 | Delete_rows    |         1 |        2614 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 2614 | Xid            |         1 |        2645 | COMMIT /* xid=998 */                  |
| mysql_bin.000003 | 2645 | Anonymous_Gtid |         1 |        2710 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 2710 | Query          |         1 |        2781 | BEGIN                                 |
| mysql_bin.000003 | 2781 | Table_map      |         1 |        2840 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 2840 | Delete_rows    |         1 |        2897 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 2897 | Xid            |         1 |        2928 | COMMIT /* xid=999 */                  |
| mysql_bin.000003 | 2928 | Anonymous_Gtid |         1 |        2993 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 2993 | Query          |         1 |        3064 | BEGIN                                 |
| mysql_bin.000003 | 3064 | Table_map      |         1 |        3123 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 3123 | Delete_rows    |         1 |        3180 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 3180 | Xid            |         1 |        3211 | COMMIT /* xid=1000 */                 |
| mysql_bin.000003 | 3211 | Anonymous_Gtid |         1 |        3276 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 3276 | Query          |         1 |        3347 | BEGIN                                 |
| mysql_bin.000003 | 3347 | Table_map      |         1 |        3406 | table_id: 180 (zsl.teacher)           |
| mysql_bin.000003 | 3406 | Delete_rows    |         1 |        3463 | table_id: 180 flags: STMT_END_F       |
| mysql_bin.000003 | 3463 | Xid            |         1 |        3494 | COMMIT /* xid=1001 */                 |
| mysql_bin.000003 | 3494 | Anonymous_Gtid |         1 |        3559 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 3559 | Query          |         1 |        3630 | BEGIN                                 |
| mysql_bin.000003 | 3630 | Table_map      |         1 |        3681 | table_id: 178 (zsl.course)            |
| mysql_bin.000003 | 3681 | Delete_rows    |         1 |        3728 | table_id: 178 flags: STMT_END_F       |
| mysql_bin.000003 | 3728 | Xid            |         1 |        3759 | COMMIT /* xid=1002 */                 |
| mysql_bin.000003 | 3759 | Anonymous_Gtid |         1 |        3824 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'  |
| mysql_bin.000003 | 3824 | Query          |         1 |        3913 | drop database zsl                     |
| mysql_bin.000003 | 3913 | Rotate         |         1 |        3960 | mysql_bin.000004;pos=4                |
+------------------+------+----------------+-----------+-------------+---------------------------------------+
65 rows in set (0.00 sec)

# 使用mysqlbinlog恢復差異備份
[root@localhost ~]# mysqlbinlog --stop-position=3824 /opt/data/mysql_bin.000003 |mysql -uroot -pPasswd123!
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# mysql -uroot -pPasswd123! -e 'select * from zsl.course;'
mysql: [Warning] Using a password on the command line interface can be insecure.
+----+-------------+
| id | course_name |
+----+-------------+
|  1 | HTML        |
|  2 | JAVA        |
|  3 | MySQL       |
|  5 | C++         |
|  6 | wuli        |
|  7 | huaxue      |
|  8 | shuxue      |
|  9 | yuwen       |
+----+-------------+

擴充套件

二進位制紀錄檔轉換文字檔案

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql_bin.000003 > /opt/mysql_bin003.txt

根據時間點恢復資料

mysqlbinlog --no-defaults --stop-datetime='2022-07-29 12:06:12' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p //基於時間恢復從開頭到指定的時間停止之前的都會執行操作
mysqlbinlog --no-defaults --start-datetime='2022-07-29 12:06:16' /usr/local/mysql/data/mysql-bin.000002 | mysql -u root -p //基於正確的時間恢復從指定的時間點到結尾都會執行操作