背景

在数据库运行和维护过程中，为了保证数据的安全和PTIR的恢复，会对数据库进行备份。mysql中常用的备份方式有两种。一个是逻辑备份，一个是物理备份。接下来，我们来了解一下两种备份方式的实现和原理。

两个mysql逻辑备份

我们在mysql中经常使用的逻辑备份方式是mysql官方MyqlDump。当然也有开源的mydumper但是不推荐。为了数据安全，我们先来了解一下MyqlDump的用法和原理。

2.1 MySQL dump简介:

基于SQL (create database，create table，insert into)语句的备份。

使用场景:100G以内，常用逻辑备份。

可以为InnoDB表实现解锁备份。原则上，它由MVCC的快照技术支持。

对于非InnoDB表，启用带锁的表备份，FTWRL(全局读锁)。

优点:自带工具，不需要单独安装；以文本形式存储，便于查看和处理；高压缩，节省空。

缺点:备份时间长。恢复时间更长(4-6倍)。

2.2 MySQL dump的实现原理

首先看一下我们执行mysqldump发生了什么2021-07-20T02:48:26.478282Z 57 Connectroot@localhost on using Socket2021-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 infor***tion_sche***_stats_expiry=0 */2021-07-20T02:48:26.478654Z 57 QuerySET SESSION NET_READ_TIMEOUT= 8***00, SESSION NET_WRITE_TIMEOUT= 8***002021-07-20T02:48:26.478719Z 57 QueryFLUSH /*!40101 LOCAL */ TABLES2021-07-20T02:48:26.480218Z 57 QueryFLUSH TABLES WITH READ LOCK2021-07-20T02:48:26.480292Z 57 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2021-07-20T02:48:26.480341Z 57 QuerySTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */2021-07-20T02:48:26.480422Z 57 QuerySHOW VARIABLES LIKE 'gtid\_mode'2021-07-20T02:48:26.482852Z 57 QuerySHOW MASTER STATUS2021-07-20T02:48:26.482920Z 57 QueryUNLOCK TABLES可以看到mysqldump首先执行了FTWL全局只读锁.然后获取了数据库的一致性快照.开始备份

2.3 MySQL dump用法介绍

您可以通过以下命令查看帮助

[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位置信息会自动开启锁表备份功能。如果它打开了–单事务可以减少全局读锁。

–单事务函数:InnoDB“热备用”。对于InnoDB表，在不锁定表的情况下打开一致快照备份。使用此参数会将隔离级别调整为RR，同时打开快照备份。

2021-07-20T02:48:26.478282Z 57 Connectroot@localhost on using Socket2021-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 infor***tion_sche***_stats_expiry=0 */2021-07-20T02:48:26.478654Z 57 QuerySET SESSION NET_READ_TIMEOUT= 8***00, SESSION NET_WRITE_TIMEOUT= 8***002021-07-20T02:48:26.478719Z 57 QueryFLUSH /*!40101 LOCAL */ TABLES2021-07-20T02:48:26.480218Z 57 QueryFLUSH TABLES WITH READ LOCK2021-07-20T02:48:26.480292Z 57 QuerySET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ2021-07-20T02:48:26.480341Z 57 QuerySTART TRANSACTION /*!40100 WITH CONSISTENT SNAPSHOT */

–Flush-logs在备份开始时刷新新的二进制日志文件。

特殊对象备份-R-E –触发器备份存储过程，函数。事件

–Max-allowed-packet=128M默认值为24M。最大值可以设置为1G。

–将指定条件的数据导出到哪里

[root@db02 ~]# mysqldump -uroot -p123 -P3307 -S /data/3307/mysql.sock scsales_prd T_HM_ROOMNO_VIEW_LOG_bak1 --where="pkid<***">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 --***ster-data=2 --single-transaction -R -E --triggers --***x-allowed-packet=128M --flush-logs>/opt/full.sql

以上是mysql的逻辑备份方式mysqldump的总结。

三种物理备份方法介绍

说到物理备份方式，MySQL8.0之前我们可以选择的物理备份工具都是PXB。MySQL8017之后，我们又多了一个选择，那就是MySQL自带的克隆plguin。

3.1简介

PXB是由percona开发的物理备份工具。可以实现innodb的热备。实现原理是利用mysql的崩溃恢复原理。

首先介绍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_Set4.此时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_checkpoint***ackup_type = full-backupedfrom_lsn = 0to_lsn = 2628274last_lsn = 2628283compact = 0recover_binlog_info = 0模拟增量数据mysql> create database increment;Query OK, 1 row affected (0.00 sec)mysql> use increment;Database changedmysql> 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 = incrementalfrom_lsn = 2628274to_lsn = 2633017last_lsn = 2633026compact = 0recover_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 mysqldrm -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 |+--------------------+| infor***tion_sche*** || inc1 || increment || mysql || perfor***nce_sche*** || sys |+--------------------+6 rows in set (0.00 sec)mysql> use increment;Reading table infor***tion for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> 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之后增加的克隆plguin的备份方法，可以参考我之前的文章克隆插件，MySQL 8.0的新特性。

四。总结:

本文分享了MySQL的逻辑备份和物理备份方法，以及详细的使用方法！