一 背景

在我们运维数据库的过程中.为了保证数据的安全性及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

四 总结:

这篇文章为大家分享了MySQL的逻辑备份及物理备份方式.以及详细的使用方法!