MySQL的备份和恢复

Yaoxuan Wei

2020-03-22 (Updated: 2020-11-29)

Database, MySQL

接下来我们来说说MySQL的备份和恢复相关. 关于备份和恢复, 小到可以仅仅涉及一条命令和一个SQL文件, 大到可以说成一个学科. ( 然后发现, 每件事情不都是这样的. 简直就是废话23333. )

说说备份

首先我们来说说, 为什么要备份? 备份的目的主要是应对应急情况下的不时之需. 也就是所谓的灾难恢复, 包括不限于人为操作, 硬件故障, 软件故障, 自然灾害, 外部攻击等等. 除了灾难恢复, 有时候我们进行测试也是需要进行备份的. 这里可能会有人说, 我们可以使用类似RAID10的手段来进行硬件上的数据备份. 没错, 这个做法是为了保证数据不会因为硬件的故障丢失和损坏, 但是对于数据的删除操作, 任何情况下也无法进行冗余, 说白了, 无论使用哪种数据存储设备, 备份都是必不可少的.

备份, 一定不是镜像. 因为镜像会同步主体的操作. 所以一般情况下, 我们的备份更多有可能是离线的.

不同的业务模型需要的备份的力度也不同, 我们可以看:

能容忍最多丢失多少的数据
恢复数据需要在多长时间内完成
需要恢复那些数据

另外, 有的时候, 我们的备份的数据集都没问题, 但是有可能因为还原出问题导致备份的数据集全部失效, 因此我们需要经常做做还原测试, 用于测试备份的可用性, 以及做还原演练.

说回备份本身, 我们的备份类型分为: 完全备份, 这会整个数据集, 部分备份, 只备份数据子集. 从数据变化的时间轴出发, 我们也可以划分成: 完全备份, 增量备份, 差异备份. 看名字挺好理解了, 就不展开了.

另外, 我们的备份还分成:

热备份: 读写操作均可执行
温备份: 读操作可进行, 写操作不可执行
冷备份: 读写操作均不可进行

从上到下, 需要的技术要求也越低.

我们前面在说InnoDB存储引擎的时候, 引入过一个概念叫做MVCC, 即多版本并发控制. 我们可以对内部的数据做一个快照然后备份, 因此InnoDB存储引擎就是支持热备的, 而MyISAM, 一般情况下, 是不能够进行热备的.

而温备, 就相对来说简单一点, 直接对服务器层面上施加一个写锁就可以了. 虽然听起来很不错, 但是实际上, 真正实施起来还是有很多问题, 例如在我们进行备份的时候, 有一个事务正在执行, 这个施加写锁的请求就会被挂起等待事务完成, 如果事务很大, 需要数小时才能完成, 那也就意味着我们的备份也需要数小时之后才能进行.

最后, 做冷备, 就意味着服务会被关闭, 这就不用多说了. 所以说到底, 最后还是需要热备.

另外, 我们可以把备份的方式分成:

物理备份: 直接复制数据文件进行备份
逻辑备份: 从数据库中导出数据另存来进行备份 (数据库协议)

我们来简单分析一下这两种备份的优与劣.

首先物理备份只需要文件层面的复制就好了, 而逻辑备份需要使用遵循数据库服务端协议的客户端来操作. 但是, 物理备份需要和存储引擎相关联, 数据文件所使用的存储引擎回复时也得用一样的, 而逻辑备份就和存储引擎无关.

除了需要备份数据, 我们还需要备份二进制文件, InnoDB的事务日志, 代码(存储过程, 存储函数, 触发器, 事件调度器), 服务器的配置文件等等, 这些都是我们需要考虑的.

接下来我们就来说说备份工具吧!

首当其冲, 大家都听说过的, 就是mysqldump了. 这是一个逻辑备份工具, 适用所有存储引擎, 支持温备, 能够对InnoDB存储引擎进行热备, 支持完全备份, 部分备份.

xtrabackup, 是一个由Percona提供的支持对InnoDB进行热备的工具, 支持完全备份和增量备份.

然后就是我们的复制归档工具: cp, tar等等, 这些就是物理备份工具了, 只能进行冷备.

以及, lvm2的快照, 可以实现一个几乎的热备, 为什么说是几乎呢? 因为lvm并不能备份, 他只是一个快照功能, 说到底, 还是需要借助于文件系统管理工具进行备份.

还有一个仅适用于MyISAM存储引擎的冷备工具: mysqlhotcopy, 不过很少有人用就是了.

如何选择这些工具呢? 我们可以像这样:

mysqldump+复制binlog
- 使用mysqldump进行完全备份, 复制binlog指定时间范围的event进行增量备份
lvm2快照+复制binlog
直接使用xtrabackup

mysqldump

接下来我们来说说其中一个工具, mysqldump. 在我们的数据量较小的情况下, mysqldump就是一个很不错的选择. 这个程序是mysql自带的, 而xtrabackup是需要另外安装使用的.

mysqldump是从文件系统层面上的备份, 上面也说过了, mysqldump是一个逻辑备份工具, 所以, schema和数据是存储在一起的. dump出来的东西, 是一个单个巨大的SQL语句, output的东西也是一个单个巨大的备份文件.

mysqldump说白了其实就是通过mysql协议连接到服务器然后进行一个全量的查询.

1	mysqldump [options] [db_name [tbl_name ...]]

我们可以直接指定选择什么数据库, 或者通过传递选项来备份所有的数据库. 当然了, 我们也可以进行表级别的备份.

不过, 需要提到的是, 如果在备份的时候, 下面的三种方式, 第一种略有不同:

1
2
3

Usage: mysqldump [OPTIONS] database [tables]
OR     mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
OR     mysqldump [OPTIONS] --all-databases [OPTIONS]

如果我们没有传递--databases或者--all-databases的话, 备份的结果中是不包含创建数据库的语句的, 而另外两个会自带创建数据库语句.

接下来还是回到我们之前说的问题, 显然我们不能直接就从一个正在运行的线上数据库dump数据, 因为这样获取的数据备份会出现新旧数据夹杂在一起. 因此我们需要在备份时候做锁定表的操作.

mysqldump就有提供类似的选项:

--lock-all-tables, -x
Lock all tables across all databases. This is achieved by acquiring a global read lock for the duration of the whole dump. This option automatically turns off --single-transaction and --lock-tables.

--lock-tables, -l
For each dumped database, lock all tables to be dumped before dumping them. The tables are locked with READ LOCAL to allow concurrent inserts in the case of MyISAM tables. For transactional tables such as InnoDB, --single-transaction is a much better option than --lock-tables because it does not need to lock the tables at all.

Because --lock-tables locks tables for each database separately, this option does not guarantee that the tables in the dump file arelogically consistent between databases. Tables in different databases may be dumped in completely different states.

其中, 我们可以直接给所有的表都施加一个全局的读锁, 不过在我们只备份其中的几个表, 这个选项就有点太过了. 因此可以选择只锁定我们需要备份的表. 注意这里是读锁, 因此还是可以进行读的操作的.

首先我们说MyISAM存储引擎. 我们前面也说过了, 它是不能热备的, 因为我们就锁定备份库然后进行备份操作就行了. 而对于InnoDB而言, 我们可以实现热备, 为了实现热备, 我们会使用到InnoDB的事务功能.

mysqldump可以启动一个巨大的事务操作, 在备份结束之后才commit. 这个选项叫做--single-transaction. 这样我们备份的数据就是一个时间点的.

--single-transaction
           
This option sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when BEGIN was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

This option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the NDBCLUSTERstorage engine supports only the READ_COMMITTED transaction isolation level. You should always use NDB backup and restore instead.

To dump large tables, you should combine the --single-transaction option with --quick.

mysqldump还可以备份数据库相关的event scheduler, 通过-E或者--events选项来指定.

存储过程和存储函数也是可以备份的, 通过-R来指明备份这些.

默认情况下, 触发器也是被备份的, 如果想要避免备份触发器, 传递--skip-triggers.

在锁定表完成后, 进行日志滚动: --flush-logs. 为什么要做这个操作呢? 这样当我们一次备份结束, 在下一次备份的时候就可以直接把新的binlog拿来用了, 而不需要看上一次备份的master-log的位置在哪里.

总的来说, 在中小规模的数据库下, mysqldump可以说是一个廉价可靠易用的解决方案.

xtrabackup

接下来我们再来说说xtrabackup这个工具.

在我们的epel源中收录了他的2.3.6版本, 可以直接通过yum下载安装.

[root@node1 ~]# rpm -ql percona-xtrabackup
/usr/bin/innobackupex
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/share/doc/percona-xtrabackup-2.3.6
/usr/share/doc/percona-xtrabackup-2.3.6/COPYING
/usr/share/doc/percona-xtrabackup-2.3.6/README
/usr/share/doc/percona-xtrabackup-2.3.6/VERSION
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz

可以看到下载生成的文件很少, 基本就是几个应用程序然后就是帮助文档了.

这里有一个innobackupex的程序, man文档中说他是一个perl脚本, 用作xtrabackup的wrapper, 但是实际上这个东西就是一个软连接, 指向xtrabackup.

在我们使用xtrabackup进行备份的时候, 应用程序会在我们的备份目录中创建这些文件:

xtrabackup_checkpoints – 备份类型(完全或增量), 备份状态(如是否是prepared) 以及 LSN (日志序列号)

对于每个InnoDB页都会有一个LSN.

xtrabackup_binlog_info – mysql服务器当前正在使用的二进制日志文件以及到备份这一刻位置的二进制日志时间的位置
xtrabackup_binlog_pos_innodb – 二进制日志文件及用于InnoDB或者XtraDB的表的二进制日志文件的位置

我们来执行一次备份操作, 就会好理解很多.

首先还是看一下我们当前的表:

hellodb_table_innodb

使用的都是InnoDB引擎. 接着我们要来确保一下innodb_file_per_table这个选项是要打开的.

我们直接运行一次全库的备份: innobackupex --user=???--password=??? /backups/, 这里其实默认就带上了百分的参数--backup因此我们可以忽略不写.

执行的结果最后告诉我们备份的目标地址以及一些信息:

200507 12:58:29 Backup created in directory '/backups//2020-05-07_12-58-28'
MySQL binlog position: filename 'mariadb-bin.000003', position '245'
200507 12:58:29 [00] Writing backup-my.cnf
200507 12:58:29 [00]        ...done
200507 12:58:29 [00] Writing xtrabackup_info
200507 12:58:29 [00]        ...done
xtrabackup: Transaction log of lsn (1646953) to (1646953) was copied.
200507 12:58:29 completed OK!

在这个备份的目录下, 我们可以看到刚刚提到的那些文件:

[root@node1 ~]# ls -l /backups/2020-05-07_12-58-28/
total 18460
-rw-r-----. 1 root root      386 May  7 12:58 backup-my.cnf
drwx------. 2 root root      272 May  7 12:58 hellodb
-rw-r-----. 1 root root 18874368 May  7 12:58 ibdata1
drwx------. 2 root root       48 May  7 12:58 mydb
drwx------. 2 root root     4096 May  7 12:58 mysql
drwx------. 2 root root     4096 May  7 12:58 performance_schema
-rw-r-----. 1 root root       23 May  7 12:58 xtrabackup_binlog_info
-rw-r-----. 1 root root      113 May  7 12:58 xtrabackup_checkpoints
-rw-r-----. 1 root root      471 May  7 12:58 xtrabackup_info
-rw-r-----. 1 root root     2560 May  7 12:58 xtrabackup_logfile

如果查看checkpoints的内容, 我们也可以获取这一次备份的信息:

[root@node1 2020-05-07_12-58-28]# cat xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1646953
last_lsn = 1646953
compact = 0
recover_binlog_info = 0

备份完成了之后, 在进行数据恢复之前, 我们现需要对备份进行整理, 以确保不会出现事务相关的问题. 整理的选项就加上apply-log就可以了.

尽管我们备份的时候是不存在未完成的事务的, 我们还是可以执行一下整理命令来看一下效果:

xtrabackup: cd to /backups/2020-05-07_12-58-28/
xtrabackup: This target seems to be not prepared yet.
xtrabackup: xtrabackup_logfile detected: size=2097152, start_lsn=(1646953)
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 1
xtrabackup:   innodb_log_file_size = 2097152
xtrabackup: Starting InnoDB instance for recovery.

首先是获取我们备份目录下的内容然后读取InnoDB存储引擎的配置.

InnoDB: 5.6.24 started; log sequence number 1646953
xtrabackup: Last MySQL binlog file position 436, file name ./mariadb-bin.000001

xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1646963
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = ./
xtrabackup:   innodb_data_file_path = ibdata1:10M:autoextend
xtrabackup:   innodb_log_group_home_dir = ./
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 5242880
InnoDB: Using atomics to ref count buffer pool pages
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Memory barrier is not used
InnoDB: Compressed tables use zlib 1.2.7
InnoDB: Using CPU crc32 instructions
InnoDB: Initializing buffer pool, size = 100.0M
InnoDB: Completed initialization of buffer pool
InnoDB: Setting log file ./ib_logfile101 size to 5 MB
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0
InnoDB: New log files created, LSN=1646963
InnoDB: Highest supported file format is Barracuda.
InnoDB: 128 rollback segment(s) are active.
InnoDB: Waiting for purge to start
InnoDB: 5.6.24 started; log sequence number 1647116
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
InnoDB: FTS optimize thread exiting.
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1647126
200507 13:06:18 completed OK!

接着会读取最后一个二进制日志文件, 然后创建一个新的日志文件, 标记序列号.

接下来我们就可以进行恢复操作了. 注意在恢复的时候, mysql服务可以为关闭状态.

恢复的参数是--copy-back.

在执行完毕之后, 你可能会遇到文件权限的问题. 这是因为在执行完操作之后, data的属主属组会变成mysql没有权限访问的对象, 因此, 我们需要在启动之前更改数据文件的属主属组.

这里我们做的是一个全库的备份. 如果是增量备份呢?

想要实现增量备份, 首先我们需要一个已有的备份. 我们之前说过每个InnoDB的页面都会包含一个LSN信息, 增量备份就是通过这个信息来实现的. 我们可以用下面的命令进行第一个增量备份: (省略user/password)

innobackupex --incremental /backup --incremental-basedir=BASEDIR

这里BASEDIR指的就是完全备份所在的目录. 这个备份倒没什么, 但是此刻聪明的你一定想到了, 在进行准备恢复操作的时候, 就会有一点不同了.

首先我们需要在每个(完全+增量)备份中, 进行事务的重放操作. 重放之后, 所有的备份数据就会合并到我们的完全备份上. 接着我们就对所有的备份将未提交的事务进行回滚就行了. 反映到实际操作, 就像这样:

innobackupex --apply-log --redo-only BASE-DIR
innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-1
innobackupex --apply-log --redo-only BASE-DIR --incremental-dir=INCREMENTAL-DIR-2
...

这样, 将每个增量备份都执行一边, 就ok了.

我新建了mydb.t2并且插入了两行数据, 接着我们执行增量备份的命令.

执行一遍之后我们再来查看一下checkpoint:

[root@node1 2020-05-10_03-10-25]# cat xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1646953
to_lsn = 1650558
last_lsn = 1650558
compact = 0
recover_binlog_info = 0

这一次的备份类型就变成增量了. 而且同时LSN也不再从0开始了.

接下来我们来尝试进行数据恢复.

在准备工作结束后, 再次查看全量备份的checkpoint, 我们可以看到:

[root@node1 ~]# cat /backups/2020-05-07_12-58-28/xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 1650558
last_lsn = 1650558
compact = 0
recover_binlog_info = 0

接着就copy-back即可, 别忘记改变属主属组.