MySQL备份和恢复

概述

备份类型

  • 完全备份、增量备份、差异备份
  • 冷备份、温备份、热备份
    • 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工具备份和还原,需要三步实现:

  1. 备份,对数据库做完全或者增量备份
  2. 预准备:还原前,先对备份的数据,整理至一个临时目录
  3. 还原:将整理好的数据,复制回数据库目录中
# 备份
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:完全备份及还原

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