Mysql-08-备份与恢复

MySql Comments

概述

根据备份方法的不同，备份可以分为：

  1. 热备 Hot Backup：指数据库运行中直接备份，对正在运行的数据库没有任何影响，也称在线备份(Online Backup)
  2. 冷备 Code Backup: 指备份操作是在数据库停止的情况下，也称离线备份(Offline Backup)
  3. 温备份 Warm Backup: 指数据库运行中进行的备份操作，但是对当前数据库操作有影响。如加一个全局读锁保证数据一致性

按照备份后的文件的内容可以分为：

  1. 逻辑备份： 指备份出的文件内容是可读的，内容一般是由一条条SQL语句或表内容实际数据组成
    • 一般用于数据库升级与迁移
    • 缺点是 恢复所需要的时间往往比较长
  2. 裸文件备份: 复制数据库的物理文件，即可以是在数据库运行中的复制，也可以是数据库停止运行时直接的数据文件复制。
    • 恢复时间往往比逻辑备份短的多

按照备份数据库的内容可分为：

  1. 完全备份：对数据库进行一个完全的备份
  2. 增量备份：在上次完全备份的基础上，对于更改的数据进行备份
  3. 日志备份：主要指对MySQL数据库二进制日志的备份

MySQL数据库复制(replication)的原理就是异步实时的将二进制日志重做传送并应用到从(slave/standby)数据库

冷备

对于InnoDB存储引擎的冷备，只需要备份MySQL数据库的frm文件，共享表空间文件，独立表空间文件(*.idb)，重做日志。

另外建议定期备份MySQL数据库的配置文件my.cnf

最好将本地产生的备份存放到一台远程服务器中，却被不会应为本地数据库的宕机而影响备份文件的使用

优点：

  1. 备份简单，只需要复制相关文件
  2. 恢复简单，只需要把文件恢复到指定位置即可
  3. 备份文件可在不同操作系统，不同MySQL版本上恢复
  4. 恢复速度快，不需要执行任何SQL语句，也不需要重建索引

缺点:

  1. InnoDB存储引擎冷备的文件通常比逻辑文件大很多，因为表空间存放着很多其他数据，如undo段，插入缓冲等信息
  2. 冷备跨平台可能存在操作系统，MySQL的版本，文件大小写敏感和浮点数格式都可能称为问题

热备

ibbackup是InnoDB存储引擎官方提供的热备工具，可以同时备份MyISAM存储引擎。原理是：

  1. 记录备份开始时，InnoDB存储引擎重做日志文件检查点的LSN
  2. 复制共享表空间文件以及独立表空间文件
  3. 记录复制完表空间文件后，InnoDB存储引擎重做日志文件检查点LSN
  4. 复制在备份时产生的重做日志

优点：

  1. 在线备份，不阻塞任何的SQL语句
  2. 备份性能好，备份的实质是复制数据库文件和重做日志文件
  3. 支持压缩备份，通过选项，支持不同级别的压缩
  4. 跨平台

逻辑备份

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
#1. 备份所有数据库
mysqldump --all-databases > dump.sql
#2. 备份指定数据库
mysqldump --databases db1 db2 db3 >dump.sql
#3. 导出指定数据库指定条件的数据(test数据库，表a)
mysqldump --single-transaction --where='b>2' test a > a.sql
#4. 备份恢复(直接执行mysql语句)
mysql -uroot -p < test_backup.sql
#5. 执行使用source导出逻辑备份文件
mysql> source /home/mysql/test_bakcup.sql

# 二进制日志备份与恢复
#1. 推荐二进制日志的服务器配置
log-bin = mysql-bin
sync_binlog = 1
innodb_suuport_xa = 1
#2. 使用mysqlbinlog恢复日志
shell> mysqlbinlog binlog.00001 > /tmp/statements.sql

注意：

  1. mysqldump 无法导出视图，需要独立导出视图的定义或者备份视图定义 frm文件

  2. 备份二进制日志文件前，可以通过FLUSH LOGS 命令来生成一个新的二进制日志文件，然后备份

快照备份

指通过文件系统的快照功能进行数据库备份，MySQL本身不支持快照功能

复制(replication)

replication的工作原理可以分为3个步骤：

  1. 主服务器(master)把数据更改记录到二进制日志(binlog)中
  2. 从服务器(slave)把主服务器的二进制日志复制到自己的中继日志(relay log)中
  3. 从服务器重做中继日志中的日志，把更改应用到自己的数据库

复制不是完全实时地进行同步，而是异步实时

image-20210718141818349

主服务器上有一个线程负责发送二进制日志

从服务器有2个线程

  • I/O线程，负责读取主服务器的二进制日志，并将其保存为中继日志

  • SQL线程负责复制执行中继日志

在主从架构下，当主服务器误操作发生，从库也会跟着执行，这该怎样恢复？

可对从服务器上的数据库所在分区做快照，避免误操作对复制造成的影响，然后根据二进制日志进行point-in-time的恢复，因此快照+复制的备份架构如下：

image-20210718142636128

参考文献

  1. 《MySql技术内幕》