MYSQL备份与恢复

备份恢复概述

为什么要备份

灾难恢复：硬件故障、软件故障、自然灾害、黑客攻击、误操作测试等数据丢失场景。

备份类型

• 完全备份 ：备份整个数据集
• 部分备份：只备份数据子集，如部分库或表
• 增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）依赖的数据，备份较快，还原复杂
• 差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单；注：二进制日志文件不应该与数据文件放在同一磁盘
• 冷备：读、写操作均不可进行，数据库停止服务
• 热备：读操作可以执行；写操作不可执行
• 温备：读、写操作均可执行
  • MyISAM：温备，不支持热备
  • InnoDB：都支持
• 物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快
• 逻辑备份：从数据库中导出数据另存而进行的备份，与存储引擎无关，占用空间较少，速度慢，可能丢失精度

备份内容

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

备份注意要点

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

还原要点

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

备份工具

• cp，tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份
• LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份
• mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份，对InnoDB存储引擎支持热备，结合binlog的增量备份
• xtrabackup：由Percona提供支持对InnoDB做热本（物理备份）的工具，支持完全备份、增量备份
• MariaDB Backup：从MariaDB 10.1.26开始集成，基于Percona XtraBackup2.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)制定好策略，通过原卷备份二进制日志

mysqldump备份工具

mysqldump说明

逻辑备份工具：

mysqldump，mydumper，phpMyAdmin

Schema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件

mysqldump：是MySQL的客户端命令，通过mysql协议连接至mysql服务器进行备份

命令格式：

# 支持指定数据库和指定多表的备份，但数据库本身定义不备份
mysqldump [OPTION] database [tables] 

# 支持指定数据库备份，包含数据库本身定义也会备份
mysqldump [OPTION] -B DB1 [DB2 DB3 ...]

# 备份所有数据库，包含数据库本身定义也会备份
mysqldump [OPYION] -A [OPYIONS]

mysqldump参考：

https://dev.mysql.com/doc/refman/5.7/en/mysqldump.html

mysqldump常见通用选项：

# 备份所有数据库，含create database
-A,--all-databases

# 指定备份的数据库，包括create database语句
-B,--databases db_name

# 备份相关的所有event scheduler
-E,--events

# 备份所有存储过程和自定义函数
-R,--routines

# 备份表相关触发器，默认启用，用--skip-triggers，不备份触发器
--trigge

# 指定字符集
--default-character-set=utf8

# 此选项需要启用二进制日志
# 1:所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合主从复制多机使用
# 2:记录为被注释的#CHANGE MASTER TO语句，适合单机使用
# 此选项会自动关闭--lock-tables功能，自动打开-x|--lock-all-tables功能（除非开启--single-transaction）
--master-data[=#]

# 备份前滚动日志，锁定表完成后，执行flush logs命令，生成新的二进制日志文件，配合-A或-B选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可以通过和--single-transaction或-x,--master-data 一起使用实现，此时只刷一次二进制日志
-F,--flush-logs

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

# 只备份表结构，不备份数据，即只备份create table
-d,--no-data

# 只备份数据，不备份表结构，即不备份create table
-t,--no-create-info

# 不备份create database，可被-A 或 -B覆盖
-n,--no-create-db

# 备份mysql或相关时需要使用
--flush-privileges

# 忽略SQL错误，继续执行
-f,--force

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

# 不缓存查询，直接输出，加快备份速度
-q,--quick

mysqldump的MyISAM存储引擎相关备份选项：

MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作

# 加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能；注意：数据量大时，可能会导致长时间无法并发访问数据库
-x,--lock-all-tables

# 对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on，--skip-lock-tables选项可禁用，对备份MyISAM的多个库，可能会造成数据不一致
-l,--lock-tables

# 注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

mysqldump的InnoDB存储引擎先关备份选项：

InnoDB存储引擎支持事务，可以利用事务的相应的隔离级别，实现热备，也可以实现温备但不建议使用

# 此选项InnoDB中推荐使用，不适用MyISAM，此选项会开始备份前，先执行START TRANSACTION指令开启事务
# 此选项通过在单个事务中转储所有表来创建一致的快照。仅适用于存储在支持多版本控制的存储引擎中的表（目标只有InnoDB可以）；转储不保证与其他存储引擎保持一致。在进行单事务转储时，要确保有效的转储文件（正确的表内容和二进制日志位置），没有其他连接应该使用以下语句：ALTER TABLE,DROP TABLE,RENAME TABLE,TRUNCATE TABLE,此选项和--lock-tables（此选项隐含提交挂起的事务）选项是相互排斥，备份大型表时，建议将--single-transaction选项和--quick结合一起使用
--single-transcation

生产环境实战备份策略

InnoDB建议备份策略

mysqldump -uroot -p -A -F -E -R --triggers --single-transaction --master-data=1 --flush-privileges --default-character-set=utf8 --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=utf8 --hex-blob > ${BACKUP}/fullbak_${BACKUP_TIME}.sql

实战：mysqldump备份还原

特定数据库的备份脚本

#!/bin/bash
TIME=`date +%F_%H-%M-%S`
# 备份文件存放路径
DIR=/backup
# 数据库名称
DB=erpdb
# 数据库密码
PASS=123456
mysqldump -uroot -p"$PASS" -F -E -R --triggers --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB |gzip >${DIR}/${DB}_${TIME}.sql.gz

分库备份脚本

#!/bin/bash
TIME=`date +%F_%H-%M-%S`
# 备份文件存放路径
DIR=/backup
# 数据库密码
PASS=123456
[ ! -d "$DIR" ]  || mkdir $DIR
for DB in `mysql -uroot -p123456 -e 'show databases'|grep -Ev "^Database|.*schema$"`;do mysqldump -uroot -p123456 -F --single-transaction --master-data=2 --default-character-set=utf8 -q -B $DB |gzip > ${DIR}/${DB}_${TIME}.sql.gz
done

完全备份与还原

# 前提开启二进制日志
[mysqld]
log-bin=/usr/local/mysql/log/mysql-bin

# 完全备份
mysqldump -uroot -p123456 -A -F --single-transaction --master-data=2 |gzip > /backup/all-`date +%F`.sql.gz

# 还原
# 登录新安装的数据库
mysql -uroot -p

# 解压备份压缩包
gunzip /backup/all-2021-08-09.sql.gz
# 依次执行
set sql_log_bin=off;
source /backup/all-2021-08-09.sql;
set sql_log_bin=on;

xtrabackup备份工具

XtraBackup工具介绍

Percona公司 http://www.percona.com

Percona-server

InnoDB—>XtraDB

Xtrabackup 备份工具：Percona提供的MySQL数据库备份工具，唯一开源的能够对InnoDB和Xtradb数据库进行热备的工具

手册：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html

下载：https://www.percona.com/downloads/

Xtrabackup特点：

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

XtraBackup工具文件组成

XtraBackup2.2版之前包括4个可执行文件：

• innobackupex：Perl脚本
• XtraBackup：C/C++,编译的二进制程序
• xbcrypt：加解密
• xbstream：支持并发写的流文件格式

说明：XtraBackup是用来备份InnoDB表的，不能备份非InnoDB表，和MySQL Server没有交互；innobackupex脚本用来备份非InnoDB表，同时会调用XtraBackup命令来备份InnoDB表，还会和MySQL Server发送命令进行交互，如加全局锁（FTWRL）、获取位点（SHOW SLAVE STATUS）等。即innobackupex是在XtraBackup至上做了一层封装实现的

Xtrabackup的新版本变化

xtrabackup版本升级到2.4后，相比之前的2.1有了比较大的变化:innobackupex 功能全部集成到 xtrabackup 里面，只有一个 binary程序，另外为了兼容考虑，innobackupex作为 xtrabackup 的软链接，即xtrabackup现在支持非Innodb表备份，并且 Innobackupex 在下一版本中移除，建议通过 xtrabackup替换innobackupex

备份生成的相关文件

使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相 关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配 置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备 份目录中创建如下文件

• xtrabackup_info:文本文件，innobackupex工具执行时的相关信息，包括版本，备份选项，备份 时长，备份LSN(log sequence number日志序列号)，BINLOG的位置
• xtrabackup_checkpoints:文本文件，备份类型(如完全或增量)、备份状态(如是否已经为 prepared状态)和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。 LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的
• xtrabackup_binlog_info:文本文件，MySQL服务器当前正在使用的二进制日志文件及至备份这一 刻为止二进制日志事件的位置，可利用实现基于binlog的恢复
• backup-my.cnf:文本文件，备份命令用到的配置选项信息
• xtrabackup_logfile:备份生成的二进制日志文件

XtraBackup安装

在EPEL源中

yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm

percona-release setup ps80

yum install -y percona-xtrabackup-24

XtraBackup用法

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

1.备份：对数据库做完全备份或增量备份

2.预准备：还原前，先对备份的数据，整理至一个临时目录

3.还原：将整理好的数据，复制回数据库目录中

XtraBackup选项参考：

https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html

备份

innobackupex [option] BACKUP-ROOT-DIR

选项说明：

# 该选项表示备份账号
--user

# 该选项表示备份的密码
--password

# 该选项表示备份数据库的地址
--host

# 该选项接受的参数为数据库名，如果要指定多个数据库，彼此间需要以空格隔开; 
# 如:"xtra_test dba_test"，同时，在指定某数据库时，也可以只指定其中的某张表。如:"mydatabase.mytable"。该选项对innodb引擎表无效，还是会备份所有innodb表
--databases

#该选项指定从哪个文件读取MySQL配置，必须放在命令行第一个选项位置
--defaults-file

#该选项表示创建一个增量备份，需要指定--incremental-basedir
--incremental

#该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用
--incremental-basedir

#该选项表示还原时增量备份的目录
--incremental-dir

#指定表名，格式:databasename.tablename
--include=name

Prepare预准备

innobackupex --apply-log [option]  BACKUP-DIR

选项说明：

# 一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚 未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作 用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
--apply-log

# 和--apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存大 小，单位字节，也可1MB,1M,1G,1GB等，推荐1G
--use-memory

# 表示开启可导出单独的表之后再导入其他Mysql中
--export

# 此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最后 一个增量备份的合并
--redo-only

还原

innobackupex --copy-back [选项] BACKUP-DIR
innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR

选项说明：

# 做数据恢复时将备份数据文件拷贝到MySQL服务器的datadir
--copy-back

# 这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这 个选项移除backup文件，用时候必须小心。使用场景:没有足够的磁盘空间同事保留数据文件和Backup副本
--move-back

# 指定该参数时候，使得innobackupex --copy-back或--move- back选项转移文件到非空目录，已存在的文件不会被覆盖。如果--copy-back和--move-back文件需要从备 份目录拷贝一个在datadir已经存在的文件，会报错失败
--force-non-empty-directories

还原注意事项

1.datadir 目录必须为空。除非指定innobackupex –force-non-empty-directorires选项指定，否则– copy-back选项不会覆盖

2.在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中

3.由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户, 执行chown -R mysql:mysql /data/mysql,以上需要在用户调用 innobackupex之前完成

Xtrabackup 完全备份及还原

基于Centos7的MySQL5.7实现

# 1.安装XtraBackup,依次执行
yum install -y https://repo.percona.com/yum/percona-release-latest.noarch.rpm
percona-release setup ps80
yum install -y percona-xtrabackup-24

# 2.在原主机完全备份到/backup
xtrabackup -uroot -p123456 --backup --host=127.0.0.1 --target-dir=/backup/base 

# 目标主机无需创建/backup目录，直接复制目录本身
scp -r /backup/ 目标主机:/

# 3.在目标主机上还原
# 1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
xtrabackup --prepare --target-dir=/backup/base

# 2）复制到数据库
# 注意：数据库目录必须为空，mysql服务不能启动
xtrabackup --copy-back --target-dir=/backup/base

# 3）还原属性
chown -R mysql:mysql /usr/local/mysql/

# 4）启动服务
service mysqld start

Xtrabackup 完全备份,增量备份及还原

# 1.完全备份
mkdir backup
xtrabackup -uroot -p123456 --backup --host=127.0.0.1 --target-dir=/backup/base

# 2.第一次修改数据
# 3.第一次增量备份
xtrabackup -uroot -p123456 --host=127.0.0.1 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base

# 查看xtrabackup相关文件
cat /backup/inc1/xtrabackup_info

uuid = 78811189-f96e-11eb-a6f8-000c2930bb8a
name =
tool_name = xtrabackup
tool_command = -uroot -p123456 --host=127.0.0.1 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
tool_version = 2.4.23
ibbackup_version = 2.4.23
server_version = 5.7.25-log
start_time = 2021-08-10 08:03:52
end_time = 2021-08-10 08:04:02
lock_time = 1
binlog_pos = filename 'mysql-bin.000022', position '154'
innodb_from_lsn = 1047831622
innodb_to_lsn = 1047831622
partial = N
incremental = Y
format = file
compact = N
compressed = N
encrypted = N

# 4.第二次修改数据
# 5.第二次增量
xtrabackup -uroot -p123456 --host=127.0.0.1 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1

# 查看xtrabackup相关文件
cat /backup/inc2/xtrabackup_info

uuid = f230df9a-f96e-11eb-a6f8-000c2930bb8a
name =
tool_name = xtrabackup
tool_command = -uroot -p123456 --host=127.0.0.1 --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1
tool_version = 2.4.23
ibbackup_version = 2.4.23
server_version = 5.7.25-log
start_time = 2021-08-10 08:07:16
end_time = 2021-08-10 08:07:26
lock_time = 0
binlog_pos = filename 'mysql-bin.000022', position '589'
innodb_from_lsn = 1047831622
innodb_to_lsn = 1047832133
partial = N
incremental = Y
format = file
compact = N
compressed = N
encrypted = N


cat /backup/inc2/xtrabackup_checkpoints

backup_type = incremental
from_lsn = 1047831622
to_lsn = 1047832133
last_lsn = 1047832142
compact = 0
recover_binlog_info = 0
flushed_lsn = 1047832142

cat /backup/inc2/xtrabackup_binlog_info

mysql-bin.000022	589

# 6.还原数据
scp -r /backup/* 目标主机:/backup/

# 还原过程
# 1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
xtrabackup --prepare --apply-log-only --target-dir=/backup/base

# 2）合并第1次增量备份到完全备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1

# 3）合并第2次增量备份到完全备份:最后一次还原不需要加选项--apply-log-only
xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2

# 4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
xtrabackup --copy-back --target-dir=/backup/base

# 5）还原属性
chown -R mysql:mysql /usr/local/mysql/

# 6）启动服务
service mysqld start