一背景

在我們運維數據庫的過程中.為了保證數據的安全性及PTIR的恢復.都會對數據庫進行備份.mysql中我們一般使用的備份方式就兩種.一種是邏輯備份.另一種是物理備份.接下來我們一起了解一下兩種備份方式的實現及原理.

二 mysql邏輯備份

mysql中我們經常使用的邏輯備份方式是mysql官方的mysqldump.當然也有開源的mydumper 但是不建議使用.為了數據的安全性.接下來我們了解一下mysqldump的使用及原理

2.1 mysqldump介紹:

基於SQL（create database ,create table , insert into）語句的備份。

使用場景： 100G以內，比較常用的就是邏輯備份。

針對InnoDB表可以實現非鎖定備份。原理上是通過MVCC中的快照技術進行備份。

針對非InnoDB表，是啟用了鎖表備份，FTWRL（global read lock）。

優點：自帶工具，不需要單獨安裝；文本形式存儲，便於查看處理；壓縮比較高，節省空間。

缺點：備份時間較長。恢復時間更長（4-6倍）。

2.2 mysqldump的執行原理

首先看一下我們執行mysqldump發生了什麼
2021-07-20T02:48:26.478282Z	   57 Connect	root@localhost on  using Socket
2021-07-20T02:48:26.478440Z	   57 Query	/*!40100 SET @@SQL_MODE='' */
2021-07-20T02:48:26.478531Z	   57 Query	/*!40103 SET TIME_ZONE='+00:00' */
2021-07-20T02:48:26.478601Z	   57 Query	/*!80000 SET SESSION information_schema_stats_expiry=0 */
2021-07-20T02:48:26.478654Z	   57 Query	SET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 86400
2021-07-20T02:48:26.478719Z	   57 Query	FLUSH /*!40101 LOCAL */ TABLES
2021-07-20T02:48:26.480218Z	   57 Query	FLUSH TABLES WITH READ LOCK
2021-07-20T02:48:26.480292Z	   57 Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2021-07-20T02:48:26.480341Z	   57 Query	START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */
2021-07-20T02:48:26.480422Z	   57 Query	SHOW VARIABLES LIKE 'gtid\_mode'
2021-07-20T02:48:26.482852Z	   57 Query	SHOW MASTER STATUS
2021-07-20T02:48:26.482920Z	   57 Query	UNLOCK TABLES
可以看到mysqldump首先執行了FTWL全局只讀鎖.然後獲取了數據庫的一致性快照.開始備份

2.3 mysqldump使用方法介紹

大家可以通過命令查看幫助:如下

[root@db02 ~]# mysqldump --help

基本連接參數:

-u   備份用戶名
-p   用戶密碼
-S   socket位置
-h   IP地址
-P   mysql端口

下面重點參數介紹一下.

–tab 將表數據和表結構分別導出.表數據為csv格式

mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock --single-transaction  --tab="/tmp" world
將world數據庫下的所有表結構及表數據備份到/tmp目錄下且表數據為csv格式
查看:
[root@db02 tmp]# ll
總用量 4760
-rw-r--r-- 1 root  root    1637 7月  20 13:51 city.sql
-rw-r----- 1 mysql mysql 143565 7月  20 13:51 city.txt
-rw-r--r-- 1 root  root    1666 7月  20 13:51 countrylanguage.sql
-rw-r----- 1 mysql mysql  18234 7月  20 13:51 countrylanguage.txt
-rw-r--r-- 1 root  root    2036 7月  20 13:51 country.sql
-rw-r----- 1 mysql mysql  31755 7月  20 13:51 country.txt
-rw-r--r-- 1 root  root    1763 7月  20 13:51 employees.sql
-rw-r----- 1 mysql mysql    335 7月  20 13:51 employees.txt
其中sql為表結構語句.txt為表數據

–no-data, -d 只導出表結構.不導出表數據

[root@db02 tmp]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock --single-transaction  --no-data world >world.sql
[root@db02 tmp]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock --single-transaction  -d world >world.sql
只導出world庫下所有表的表結構

只導出表數據.不導出表結構:

[root@db02 ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock -t world city --single-transaction>all.sql

–master-data=2 備份時自動生成，當前的binlog位置信息.自動開啟鎖表備份功能，如果開了–single-transaction，可以減少global read lock.

–single-transaction 功能：InnoDB “熱備”。對於InnoDB表不鎖表，開啟一致性快照備份.使用這個參數會在備份時調整隔離級別為RR同時開啟快照備份

2021-07-20T02:48:26.478282Z	   57 Connect	root@localhost on  using Socket
2021-07-20T02:48:26.478440Z	   57 Query	/*!40100 SET @@SQL_MODE='' */
2021-07-20T02:48:26.478531Z	   57 Query	/*!40103 SET TIME_ZONE='+00:00' */
2021-07-20T02:48:26.478601Z	   57 Query	/*!80000 SET SESSION information_schema_stats_expiry=0 */
2021-07-20T02:48:26.478654Z	   57 Query	SET SESSION NET_READ_TIMEOUT= 86400, SESSION NET_WRITE_TIMEOUT= 86400
2021-07-20T02:48:26.478719Z	   57 Query	FLUSH /*!40101 LOCAL */ TABLES
2021-07-20T02:48:26.480218Z	   57 Query	FLUSH TABLES WITH READ LOCK
2021-07-20T02:48:26.480292Z	   57 Query	SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ
2021-07-20T02:48:26.480341Z	   57 Query	START TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

–flush-logs 在備份開始時刷新一個新的二進制日誌文件

特殊對象備份-R -E –triggers 備份存儲過程,函數.事件

–max-allowed-packet=128M 默認是24M.最大可以設置1G

–where 導出指定條件的數據

[root@db02 ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock scsales_prd T_HM_ROOMNO_VIEW_LOG_bak1 --where="pkid<10010">all.sql

單庫備份:

[root@db02 ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock world --single-transaction>all.sql

單表備份:

[root@db02 ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock world city --single-transaction>all.sql

最後提供一個生產經常使用的mysqldump完整版

mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers --max-allowed-packet=128M --flush-logs>/opt/full.sql

以上就是對mysql的邏輯備份方式mysqldump的總結.

三物理備份方式介紹

說到物理備份方式.在MySQL8.0之前我們可以選擇的物理備份工具都是PXB 從MySQL8017版本之後我們多了一種選擇.那就是MySQL自帶的clone plguin

3.1 PXB介紹

PXB是percona開源出來的一個物理備份工具.可以實現innodb的熱備.實現原理是利用了mysql的crash recovery原理.

首先介紹xtrabackup的實現原理:

1.執行備份命令之後pxb先獲取到當前的lsn.然後開始拷貝redo文件.同時開啟一個後台線程監控redo的實時變化同步到拷貝目錄.
2.開始先拷貝innodb表數據.然後執行FLUSH TABLES WITH READ LOCK 和LOCK TABLES FOR BACKUP拷貝非innodb表數據.當這些拷貝完成之後.再拷貝其他數據文件
3.然後執行LOCK BINLOG FOR BACKUP開始獲取二進制日誌的位置點及Exec_Gtid_Set
4.此時xtrabackup完成對redo的拷貝並釋放二進制日誌的鎖

使用xtrabackup備份數據庫:

root@slowquery ~]# xtrabackup --backup --user=root --password=123 --target-dir=/data/backups/
xtrabackup: recognized server arguments: --datadir=/data/3306/data --server-id=7 --log_bin=/data/3306/binlog/mysql-bin --open_files_limit=65535 --innodb_buffer_pool_size=4096M --innodb_flush_log_at_trx_commit=1 --innodb_log_buffer_size=32M --innodb_log_file_size=128M --innodb_log_files_in_group=3 --innodb_flush_log_at_trx_commit=1

使用xtrabackup進行增量備份

增量備份的原理:

xtrabackup會對比全備下的LSN號碼.然後進行增量拷貝

查看全備目錄下記錄的lsn.後邊我們查看增量的LSN

[root@slowquery ~]# cat /data/backups/xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 2628274
last_lsn = 2628283
compact = 0
recover_binlog_info = 0


模擬增量數據
mysql> create database increment;
Query OK, 1 row affected (0.00 sec)

mysql> use increment;
Database changed
mysql> create table increment(id int,name varchar(30));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into increment values(1,'lzm');
Query OK, 1 row affected (0.01 sec)

mysql>

開始增量備份:

[root@slowquery ~]# xtrabackup --backup --user=root --password=123 --target-dir=/data/backups/inc1 \
> --incremental-basedir=/data/backups

查看增量目錄下的LSN號碼對比全量目錄下的LSN號碼

[root@slowquery ~]# cat /data/backups/inc1/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 2628274
to_lsn = 2633017
last_lsn = 2633026
compact = 0
recover_binlog_info = 0
[root@slowquery ~]#

此時增量備份也已經完成.接下來我們進行數據恢復

首先對全備備份目錄操作:

xtrabackup --prepare --apply-log-only --target-dir=/data/backups/

然後應用增量日誌

xtrabackup --prepare --apply-log-only --target-dir=/data/backups --incremental-dir=/data/backups/inc1
這裡注意.如果增量不止一個的話.後邊的增量和全備合併的時候不需要加--apply-log-only

停止現在的數據庫並把數據目錄清除:

pkill mysqld
rm -rf /data/3306/data/*

將全備目錄下的文件複製到數據目錄:

xtrabackup --copy-back --target-dir=/data/backups/
  修改權限
chown -R mysql:mysql /data/
 啟動mysql
/etc/init.d/mysqld start

連接進入數據庫查看數據是否恢復

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

mysql> use increment;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;
+---------------------+
| Tables_in_increment |
+---------------------+
| increment           |
+---------------------+
1 row in set (0.00 sec)

mysql> select * from increment;
+------+------+
| id   | name |
+------+------+
|    1 | lzm  |
+------+------+
1 row in set (0.00 sec)

可以看到我們的增量數據也已經恢復了

關於8017之後加入的clone plguin的備份方式請查看我之前的文章MySQL 8.0新特性之clone plugin