MySQL备份和恢复

本文最后更新于：2023年12月5日晚上

概述

备份类型

完全备份、部分备份、增量备份、差异备份
- 完全备份：整个数据集
- 部分备份：只备份数据子集，如部分库或表
- 增量备份：仅备份最近一次完全备份或者增量备份（如果有增量备份）以来变化的数据，备份较快，还原复杂
- 差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单
注意：二进制日志文件不应该与数据文件放在同一磁盘
冷备份、温备份、热备份
- 冷备：读写操作均不可进行，数据库停止服务
- 温备：读操作可执行，但写操作不可执行
- 热备：读写操作均可执行
```
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 请求锁定所有表
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 `scp -r` 或 `rsync -av` 导出数据和二进制日志文件
# 3 启动数据库
# 4 发送故障，关闭数据库
# 5 `scp -r` 或 `rsync -av`还原数据
# 6 对需要权限的文件执行 `chown -R mysql:mysql`
# 7 启动数据库

mysqldump

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

# 支持指定数据库和指定多表的备份，默认创建数据库语句不备份，创建表语句备份
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

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

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 从完全备份(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 差不多，和旧版本有不少出入

# 去官网下载最新的版本，然后安装
[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 工具备份和还原，需要三步实现：

备份，对数据库做完全或者增量备份
预准备：还原前，先对备份的数据，整理至一个临时目录
还原：将整理好的数据，复制回数据库目录中

# 备份
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：该选项表示还原时增量备份的目录

还原注意事项：

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

范例 1：完全备份及还原

# 全量备份
[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：完全、增量备份及还原

# 全量备份
[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

运维 > MySQL

#MySQL

MySQL备份和恢复

http://blog.lujinkai.cn/运维/MySQL/MySQL备份和恢复/

作者

像方便面一样的男子

发布于

2020年12月31日

更新于

2023年12月5日

许可协议

MySQL集群Cluster 上一篇

MySQL架构和性能优化下一篇