MySQL备份和恢复

发表于 更新于 分类于 ，

概述

备份类型

  • 完全备份、部分备份、增量备份、差异备份

    • 完全备份：整个数据集
    • 部分备份：只备份数据子集，如部分库或表
    • 增量备份：仅备份最近一次完全备份或者增量备份（如果有增量备份）以来变化的数据，备份较快，还原复杂
    • 差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单

    注意：二进制日志文件不应该与数据文件放在同一磁盘

  • 冷备份、温备份、热备份

    • 冷备：读写操作均不可进行，数据库停止服务
    • 温备：读操作可执行，但写操作不可执行
    • 热备：读写操作均可执行
    1
    2
    3
    MyISAM：支持温备，不支持热备

    InnoDB：都支持

  • 物理备份、逻辑备份

    • 物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
    • 逻辑备份：从数据库中“到处”数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度

增量备份 图示：

增量备份

差异备份 图示：

差异备份

推荐：增量备份 + 热备 + 物理备份

备份什么

  • 数据
  • 二进制日志、InnoDB 的事务日志
  • 用户帐号，权限设置，程序代码（存储过程、函数、触发器、事件调度器）
  • 服务器的配置文件

备份注意要点

  • 能容忍最多丢失多少数据
  • 备份产生的负载
  • 备份过程的时长
  • 温备的持锁多久
  • 恢复数据需要在多长时间内完成
  • 需要备份和恢复哪些数据

还原要点

  • 做还原测试，用于测试备份的可用性
  • 还原演练，写成规范的技术文档

备份工具

  • cp, ta r 等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备份
  • LVM 的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
  • mysqldump：逻辑备份工具，适用所有存储引擎，对 MyISAM 存储引擎进行温备；支持完全或部分备份；对 InnoDB 存储引擎支持热备，结合 binlog 的增量备份
  • xtrabackup：由 Percona 提供支持对 InnoDB 做热备(物理备份)的工具，支持完全备份、增量备份
  • MariaDB Backup： 从 MariaDB 10.1.26 开始集成，基于 Percona XtraBackup 2.3.8 实现
  • mysqlbackup：热备份， MySQL Enterprise Edition 组件
  • mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于 MyISAM 存储引擎，使用 LOCK TABLES、
  • FLUSH TABLES 和 cp 或 scp 来快速备份数据库

基于 LVM 的快照备份

1
2
3
4
5
6
7
8
9
10
11
12
13
# 1 请求锁定所有表
mysql> FLUSH TABLES WITH READ LOCK;
# 2 记录二进制日志文件及事件位置
mysql> FLUSH LOGS;
mysql> SHOW MASTER STATUS;
mysql -e 'SHOW MASTER STATUS' > /PATH/TO/SOMEFILE
# 3 创建快照
lvcreate -L # -s -p r -n NAME /DEV/VG_NAME/LV_NAME
# 4 释放锁
mysql> UNLOCK TABLES;
# 5 挂载快照卷，执行数据备份
# 6 备份完成后，删除快照卷
# 7 制定好策略，通过原卷备份二进制日志

冷备份和还原

冷备份和还原需要停止数据库服务

1
2
3
4
5
6
7
# 1 关闭数据库
# 2 `scp -r` 或 `rsync -av` 导出数据和二进制日志文件
# 3 启动数据库
# 4 发送故障，关闭数据库
# 5 `scp -r` 或 `rsync -av`还原数据
# 6 对需要权限的文件执行 `chown -R mysql:mysql`
# 7 启动数据库

mysqldump

mysqldump 是逻辑备份工具，是 MySQL 的客户端工具，通过 mysql 协议连接至 mysql 服务器进行备份

1
2
3
4
5
6
# 支持指定数据库和指定多表的备份，默认创建数据库语句不备份，创建表语句备份
mysqldump [OPTIONS] database [tables]
# 支持指定数据库备份，创建数据库语句也会备份
mysqldump [OPTIONS] -B DB1 [DB2 DB3...]
# 备份所有数据库，创建数据库语句也会备份
mysqldump [OPTIONS] -A [OPTIONS]

常见选项：

  • -A：备份所有数据库，包含 create database 语句

    • -A 包括 mysql 数据库，但不包括 information_schema 和 performance_schema

  • -B db_name…：备份指定的数据库，包含 create database 语句

  • –flush-privileges：-A 包含了 mysql 数据库，其中包含了权限的数据库，需要加上–flush-privileges，将来恢复的时候权限才会生效

  • -n：不备份 create database 语句，会被-A 或-B 覆盖

  • -t：只备份数据，不备份表结构，即不备份 create table 语句

  • -d：只备份表结构，不备份数据，即只备份 create table 语句

  • -E：备份相关的所有 event scheduler

  • -R：备份所有存储过程和自定义函数

  • –triggers：备份表相关触发器，默认启用，用 –skip-triggers 不备份触发器

  • –default-character-set=utf8mb4：指定字符集

  • –single-transaction：置事务的隔离级别为可重复读，即 REPEATABLE READ，这样能保证在一个事务中所有相同的查询读取到同样的数据，也就大概保证了在 dump 期间，如果其他 innodb 引擎的线程修改了表的数据并提交，对该 dump 线程的数据并无影响，在这期间不会锁表

  • –master-data=1|2

    此选项会自动关闭–lock-tables 功能，自动打开-x | –lock-all-tables 功能（除非开启–single-transaction）

    • 1：默认值，所备份的数据之前加一条记录为 CHANGE MASTER TO 语句，适合于主从复制多机使用
    • 2：所备份的数据之前加一条记录为 CHANGE MASTER TO 语句，但是被注释，适合于单机使用

  • -F：在 dump 之前会执行--flush-logs刷新日志。使用-A 或-B 时一次性 dump 多个库，每个库都会刷新一次。建议搭配–master-data 或–lock-all-tables，就只会刷新一次

  • –compact：去掉注释，节约备份占用的空间，适合调试，生产不使用

  • -f：忽略 SQL 错误，继续执行

  • –hex-blob：使用十六进制符号转储二进制列，当有包括 BINARY， VARBINARY，BLOB，BIT 的数据类型的列时使用，避免乱码

  • -q：不缓存查询，直接输出，加快备份速度

mysqldump 备份的最佳姿势

Innodb

1
2
3
4
5
6
7
8
9
10
11
12
mysqldump -uroot -p \
-A \
-F \
-E \
-R \
--triggers \
--single-transaction \
--master-data=1 \
--flush-privileges \
--default-character-set=utf8mb4 \
--hex-blob \
> ${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM

1
2
3
4
5
6
7
8
9
10
11
12
mysqldump -uroot -p \
-A \
-F \
-E \
-R \
-x \
--master-data=1 \
--flush-privileges \
--triggers \
--default-character-set=utf8mb4 \
--hex-blob \
> ${BACKUP}/fullbak_${BACKUP_TIME}.sql

实战案例

  • mysqldump 结合二进制日志文件实现增量备份

数据库接手的时候，早期的二进制日志文件已经没有了，此时可以先用 msyqldump 做全量备份，然后再定期备份二进制日志文件

  • 恢复误删除的表

每天都全量备份，误删数据表，该如何恢复？

1
2
3
4
5
6
7
8
9
10
11
12
13
# 1 关闭数据库服务
# 2 从完全备份(allbackup_2019-11-27_10:20:08.sql)中，找到二进制日志位置
# 3 备份完全备份后的二进制日志(inc.sql)
# 4 找到误删除的语句，从二进制日志备份中删除此语句
# 5 关闭端口或者其他方式，禁止用户访问数据库
# 6 启动数据库
# 7 开始恢复数据
    [root@centos8 ~]#mysql -uroot -p
    MariaDB [hellodb]> set sql_log_bin=0;
    MariaDB [hellodb]> source /backup/allbackup_2019-11-27_10:20:08.sql;
    MariaDB [hellodb]> source /backup/inc.sql
    MariaDB [hellodb]> set sql_log_bin=1;
# 7 打开端口或者其他方式，恢复正常服务

xtrabackup

xtrabackup 是一个开源的 mysql 数据库备份工具，支持 InnoD 和 XtraDB 引擎

mysqldump 基于逻辑备份，备份和还原比较慢，增量备份（配合二进制日志）需要自己写脚本

xtrabackup 基于物理备份，备份和还原很快，自动化增量备份

mysqldump 的优点是相较于 xtrabackup，备份文件更小，节省磁盘空间

当数据量比较大的时候，推荐使用 xtrabackup 备份

xtrabackup 特点：

  • 备份还原过程快速、可靠
  • 备份过程不会打断正在执行的事务
  • 能够基于压缩等功能节约磁盘空间和流量
  • 自动实现备份检验
  • 开源，免费

xtrabackup 备份过程：

安装：

xtrabackup2.4 支持 mysql5.7，xtrabackup8.0 支持 mysql8.0，这里以 2.4 版本为例，2.4 版本和 8.0 差不多，和旧版本有不少出入

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
# 去官网下载最新的版本，然后安装
[root@c71 src]# yum -y install ./percona-xtrabackup-24-2.4.20-1.el7.x86_64.rpm
[root@c72 src]$rpm -qi percona-xtrabackup-24-2.4.20-1.el7
Name        : percona-xtrabackup-24
Version     : 2.4.20
Release     : 1.el7
Architecture: x86_64
Install Date: Fri 23 Oct 2020 09:40:03 AM CST
Group       : Applications/Databases
Size        : 33240760
License     : GPLv2
Signature   : RSA/SHA256, Tue 21 Apr 2020 04:45:08 AM CST, Key ID 9334a25f8507efa5
Source RPM  : percona-xtrabackup-24-2.4.20-1.el7.src.rpm
Build Date  : Tue 21 Apr 2020 04:44:27 AM CST
Build Host  : minimal-centos-7-x64-623.ci.percona.com
Relocations : (not relocatable)
URL         : http://www.percona.com/software/percona-xtrabackup
Summary     : XtraBackup online backup for MySQL / InnoDB
Description :
Percona XtraBackup is OpenSource online (non-blockable) backup solution for InnoDB and XtraDB engines
[root@c72 src]$rpm -ql percona-xtrabackup-24-2.4.20-1.el7
/usr/bin/innobackupex
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.20
/usr/share/doc/percona-xtrabackup-24-2.4.20/COPYING
/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

用法：

xtrabackup 工具备份和还原，需要三步实现：

  1. 备份，对数据库做完全或者增量备份
  2. 预准备：还原前，先对备份的数据，整理至一个临时目录
  3. 还原：将整理好的数据，复制回数据库目录中
1
2
3
4
5
6
# 备份
xtrabackup [--defaults-file=/etc/my.cnf] --backup [OPTIONS]
# 预准备
xtrabackup [--defaults-file=/etc/my.cnf] --prepare [OPTIONS]
# 还原
xtrabackup [--defaults-file=/etc/my.cnf] --copy-back [OPTIONS]

OPTIONS：

  • –defaults-file：该选项指定从哪个文件读取 MySQL 配置，必须放在命令行第一个选项位置
  • -u：user 该选项表示备份账号
  • -p：password 该选项表示备份的密码
  • -h：host 该选项表示备份数据库的地址
  • –backup：备份
  • –databases：该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开；
    如：”xtra_test dba_test”，同时，在指定某数据库时，也可以只指定其中的某张表。
    如：”mydatabase.mytable”。该选项对 innodb 引擎表无效，还是会备份所有 innodb 表
  • –incremental-basedir：该选项指定为前一次全备份或增量备份的目录，与–incremental 同时使用
  • –prepare：预准备
  • –apply-log-only：预准备的过程中，默认回滚未完成的事务，–apply-log 选项能阻止回滚
  • –copy-back：还原，拷贝整理好的备份数据到 datadir
  • –incremental-dir：该选项表示还原时增量备份的目录

还原注意事项：

  1. 保证 datadir 目录为空
  2. 还原期间关闭 mysql 服务
  3. 重新启动之前，需要chown -R mysql:mysql datadir

范例 1：完全备份及还原

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
# 全量备份
[root@4710419222 backup]$mkdir /data/backup
[root@4710419222 backup]$xtrabackup -uroot -pLujinkai0? --backup --target-dir=/data/backup/base

# 切换到10.0.0.71
# 将全量备份拷贝过来
[root@c71 data]$scp -r root@47.104.192.22:/data/backup ./
# 关闭数据库
[root@c71 data]$systemctl stop mysql.service
# 清除数据库，确保datadir目录为空
[root@c71 data]$rm -rf /data/mysql/*
# 预准备，因为只有一次预准备，所以不加--apply-log-only，提交完成的事务，回滚未完成事务，确保数据一致
[root@c71 data]$xtrabackup --prepare --target-dir=/data/backup/base
# 复制预准备完的数据到数据库目录
[root@c71 data]$xtrabackup --copy-back --target-dir=/data/backup/base
[root@c71 data]$chown -R mysql:mysql /data/mysql/
# 启动数据库
[root@c71 data]$systemctl start mysqld.service

范例 2：完全、增量备份及还原

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
# 全量备份
[root@c71 ~]$xtrabackup -uroot -pLujinkai0? --backup --target-dir=/data/backup/base
# 第一次增量备份
[root@c71 base]$xtrabackup -uroot -pLujinkai0? --backup --target-dir=/data/backup/inc1 --incremental-basedir=/data/backup/base
# 第二次增量备份
[root@c71 base]$xtrabackup -uroot -pLujinkai0? --backup --target-dir=/data/backup/inc2 --incremental-basedir=/data/backup/inc1
[root@c71 base]$ll /data/backup/
total 12
drwxr-x--- 10 root root 4096 Oct 23 10:56 base
drwxr-x--- 10 root root 4096 Oct 23 10:54 inc1
drwxr-x--- 10 root root 4096 Oct 23 10:58 inc2

# 预准备，除了最后一次预准备，其余的都需要加--apply-log-only参数
[root@c71 base]$xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base
[root@c71 base]$xtrabackup --prepare --apply-log-only --target-dir=/data/backup/base --incremental-dir=/data/backup/inc1
[root@c71 base]$xtrabackup --prepare --target-dir=/data/backup/base --incremental-dir=/data/backup/inc2

# 还原
[root@c71 base]$systemctl stop mysqld.service
[root@c71 base]$rm -rf /data/mysql/*
[root@c71 base]$ll /data/mysql/
total 0
[root@c71 base]$xtrabackup --copy-back --target-dir=/data/backup/base
[root@c71 base]$chown -R mysql:mysql /data/mysql/
[root@c71 base]$systemctl start mysqld.service