跳到主要内容

第8章 MySQL备份恢复

MySQL备份策略​

1. DBA备份与恢复职责​

https://www.processon.com/view/link/659e09125db284378ce675a5

  1. 备份、恢复策略的设计：备份周期、备份工具、备份方式、恢复方式全部流程化
  2. 日常备份检查：日志、备份内容
  3. 定期的恢复演练
  4. 数据故障时，利用现有的资源，快速恢复
  5. 数据迁移、升级

2. MySQL备份工具分类​

逻辑备份（100G以内，30分钟）

  • mysqldump
  • mysqlbinlog

物理备份（100G以上）

  • Xtrabackup

3. mysqldump逻辑备份​

介绍​

mysqldump是MySQL自带的数据逻辑备份工具，可以实现远程和本地备份。

重要参数​

连接参数

  • -u 用户名
  • -p 密码
  • -S 本地socket文件路径

备份参数

  • -A 导出所有的库
  • -B 导出单库或多库
  • --master-data=2 标记全备的时候位于binlog哪个位置，=2这一行是个注释，固定为22行
  • --single-transaction 对于InnoDB表，通过快照备份表数据，不锁表备份，可以理解为热备
  • -R -E --triggers 备份特殊对象使用

备份命令​

全备命令

mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers > /backup/full_$(date +%F).sql

备份单个库或多个库

mysqldump -uroot -p123 -B 库名 --master-data=2 --single-transaction -R -E --triggers > /backup/luffy_$(date +%F).sql
mysqldump -uroot -p123 -B 库名 库名 --master-data=2 --single-transaction -R -E --triggers > /backup/luffy_$(date +%F).sql

备份单个表或多个表

mysqldump -uroot -p123 库名 表名 --master-data=2 --single-transaction -R -E --triggers > /backup/linux7_$(date +%F).sql
mysqldump -uroot -p123 库名 表名 表名 --master-data=2 --single-transaction -R -E --triggers > /backup/linux7_$(date +%F).sql

远程备份

mysqldump -uroot -p123 -h10.0.0.51 -A --master-data=2 --single-transaction -R -E --triggers > /backup/full_$(date +%F).sql

分库分表备份​

备份思路

  • 双层for循环
  • 第一层for循环所有的库
  • 第二层for循环库下所有的表

备份脚本

#!/bin/bash
for ku in $(mysql -N -e 'show databases;'|egrep -v 'mysql|schema|sys')
do
  mkdir /backup/$(date +%F)/$ku/ -p
  for biao in $(mysql -N -e "show tables from $ku;")
  do
      mysqldump -h127.0.0.1 $ku $biao --master-data=2 --single-transaction -R -E --triggers > /backup/$(date +%F)/$ku/$biao.sql
  done
done
技巧

可以把账号密码写进配置文件里，这样就不会出现命令警告了：

vim /etc/my.cnf
[client]
user=root
password=123

故障恢复演练​

模拟环境

create database luffy charset utf8mb4;
use luffy
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;

模拟周一23:00全备

mysqldump -uroot -p  -A  --master-data=2  --single-transaction  --max_allowed_packet=64M  -R -E  --triggers >/backup/full_$(date +%F).sql

查看GTID相关信息，GTID截取起点：

SET @@GLOBAL.GTID_PURGED='9b52b744-eb82-11ea-986c-000c294983f8:1-6';

查看pos号，备份开始时binlog位置点信息：

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=1028;

模拟周二白天数据变化

use luffy;
create table t2 (id int);
insert into t2 values(1),(2),(3);
commit;

模拟周二下午2点，误删除了核心库

mysql> drop database luffy;

恢复数据

第一步：查看全备时候的位置点在哪里

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=2532

第二步：查看binlog定位终止点

# at 3119
#240110 15:24:15 server id 51  end_log_pos 3237 CRC32 0xd7f3dc06        Query   thread_id=2
     exec_time=0     error_code=0
SET TIMESTAMP=1641972255/*!*/;
DROP TABLE `t1` /* generated by server */

第三步：截取binlog

mysqlbinlog -d luffy --start-position=2532 --stop-position=3119 /data/mysql_3306/logs/mysql-bin.000007 > /tmp/backup.sql

第四步：将全备和binlog导入到测试服务器里

scp /tmp/full_2024-01-10.sql 10.0.0.41:/opt/
scp /tmp/backup.sql  10.0.0.41:/opt/

第五步：测试服务器导入数据

mysql -uroot -p123 < full_2024-01-10.sql
mysql -uroot -p123 < backup.sql

第六步：只导出被删除的表

mysqldump -uroot -p123 luffy t1 > luffy_t1.sql

第七步：将备份数据发送给生产服务器

scp luffy_t1.sql 10.0.0.51:/opt/

第八步：生产服务器导入恢复的数据

set sql_log_bin=0;
source /opt/luffy_t1.sql
set sql_log_bin=1;

mysqldump多种备份策略​

场景 100G全库数据，全库备份30分钟-40分钟，恢复整库需要5倍时间2.5-3小时之间。一张表1G被误删除了。

第一种：full + binlog增量备份思路

第一步：提取full全备中的故障表数据，恢复数据

sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `t1`/!d;q'  full_2021-06-28.sql > create_table.sql
sed -n '/CREATE TABLE `t1` /,/\;/p' /data/backup/full.sql

grep -i 'INSERT INTO `t1`' full_2021-06-28.sql > data.sql

第二步：binlog中截取全备到误删除t1之间对于这张表的修改

第二种：单库单表备份+binlog增量思路

第一步：恢复单表的备份 第二步：binlog中截取备份到误删除t1之间对于这张表的修改

4. Xtrabackup物理备份​

介绍​

Xtrabackup是percona公司研发的物理备份工具，类似于cp文件，支持全备和增量备份。

  • xtrabackup → C/C++
  • innobackupex → perl语言
  • 8.0之前使用2.4.x版本
  • 8.0之后使用8.0版本
  • 无法通过增量单独恢复数据，依赖于全备

安装​

yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.12/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm
yum -y localinstall percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

全量备份数据​

全备命令

innobackupex -uroot -p123 /backup/

自定义目录名备份

innobackupex -uroot -p123 --no-timestamp /backup/full_$(date +%F)

备份完成目录结构

[root@db-51 ~]# ll /data/backup/test/2020-09-14_22-06-11/
总用量 12348
-rw-r----- 1 root root      487 9月  14 22:06 backup-my.cnf
drwxr-x--- 2 root root       48 9月  14 22:06 gtdb
-rw-r----- 1 root root    10056 9月  14 22:06 ib_buffer_pool
-rw-r----- 1 root root 12582912 9月  14 22:06 ibdata1
-rw-r----- 1 root root       63 9月  14 22:06 xtrabackup_binlog_info
-rw-r----- 1 root root      117 9月  14 22:06 xtrabackup_checkpoints
-rw-r----- 1 root root      546 9月  14 22:06 xtrabackup_info
-rw-r----- 1 root root     2560 9月  14 22:06 xtrabackup_logfile

相关文件说明

  1. xtrabackup_binlog_info：记录binlog位置点，截取binlog起点位置
  2. xtrabackup_checkpoints：
    • from_lsn = 0（一般增量备份会关注，一般上次备份的to_lsn的位置）
    • to_lsn = 180881595（CKPT-LSN最近的内存数据落地到磁盘上的LSN号）
    • last_lsn = 180881604（xtrabackup_logfile LSN）
  3. xtrabackup_info：备份总览信息
  4. xtrabackup_logfile：备份期间产生的redo变化

增量备份原理​

增量备份特点

  • 自带的功能，每次增量一般是将最近一次备份作为参照物
  • 自动读取参照物cat xtrabackup_checkpoints中to_lsn值，与当前CKPT的LSN对比，备份变化过page
  • 备份期间新的数据变化，通过redo自动备份
  • 恢复数据时，需要把所有需要的增量合并到FULL中，无法通过增量单独恢复数据，依赖于全备

MySQL恢复实战​

1. 全量恢复流程​

Xtrabackup全量恢复​

模拟删除

pkill mysqld
rm -rf /data/mysql_3306/*

第1步：prepare准备备份阶段

innobackupex --apply-log /backup/2024-01-10_14-51-59
命令解释

--apply-log是innobackupex工具的一个选项，用于准备（也称为"重放"）备份。

过程详解：

  1. 事务日志重放：innobackupex会读取日志文件，并将所有从备份开始到备份结束期间的数据库变更应用到备份数据文件中
  2. 数据一致性：此步骤确保备份数据在恢复时的一致性，没有执行这个步骤，备份数据可能处于不一致的状态
  3. 备份准备：只有在执行了--apply-log后，备份才被视为"准备好"状态，可以用于恢复操作

使用场景：

  • 在恢复之前，应总是对备份执行--apply-log操作，以确保数据的一致性和完整性
  • 在执行增量备份的情况下，--apply-log还可以用来合并基础备份和一系列增量备份

第2步：恢复数据（任选一个方法即可）

方法1）使用命令复制全备文件到数据目录

innobackupex --copy-back /backup/2024-01-10_14-51-59/
innobackupex --move-back /backup/2024-01-10_14-51-59/

方法2）修改配置文件，数据目录指向备份文件目录

[root@db-52 ~]# cat /etc/my.cnf
[mysqld]
port=3306
user=mysql
basedir=/opt/mysql
#直接修改为备份文件的目录
datadir=/backup/2024-01-10_14-51-59/

#主从复制参数
server_id=51
log_bin=/data/mysql_3306/mysql-bin

方法3）创建备份目录的软链接到数据目录

mkdir /backup/2024-01-10_14-51-59/logs
touch /backup/2024-01-10_14-51-59/logs/mysql.err
chown -R mysql:mysql /backup/2024-01-10_14-51-59
ln -s /backup/2024-01-10_14-51-59/ /data/mysql_3306

第3步：恢复数据后记得更改权限

重要注意事项
  1. 备份恢复的时候不会把日志目录一起备份，比如错误日志和Binlog日志，恢复完成后需要手动创建
  2. 恢复后数据目录的用户权限都是root，需要手动更改权限
mkdir /data/mysql_3306/logs/
touch /data/mysql_3306/logs/mysql.err
chown -R mysql:mysql /data/mysql_3306
systemctl start mysqld

2. 增量恢复演练​

FULL(周日)+inc1(周一)+inc2(周二)+inc3(周三)​

第1步：实验数据准备

create database xbk charset utf8mb4;
use xbk
create table full (id int);
insert into full values(1),(2),(3);

第2步：模拟周日23:00全备

innobackupex -uroot -p123 --no-timestamp /backup/full_$(date +%F)

第3步：模拟周一白天数据变化

use xbk
create table inc1 (id int);
insert into  inc1 values(1),(2),(3);

第4步：第一次增量备份

innobackupex -uroot -p123 --no-timestamp --incremental --incremental-basedir=/backup/full_2022-01-13  /backup/inc1_$(date +%F)

第5步：模拟周二白天数据变化

use xbk
create table inc2 (id int);
insert into  inc2 values(1),(2),(3);

第6步：第二次增量备份

innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc1_2022-01-13  /backup/inc2_$(date +%F)

第7步：模拟周三白天数据变化

use xbk
create table inc3 (id int);
insert into  inc3 values(1),(2),(3);

第8步：第三次增量备份

innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp --incremental --incremental-basedir=/backup/inc2_2022-01-13  /backup/inc3_$(date +%F)

第9步：模拟周四白天数据变化

use xbk
create table inc4(id int);
insert into  inc4 values(1),(2),(3);
commit;

第10步：周四下午出现数据损坏，如何恢复到误删除之前

pkill mysqld
rm -rf /data/mysql_3306/*

3. 增量备份恢复完整流程​

第11步：截取binlog位置点全备-增量1-增量2-增量3-binlog

备份的数据：全备-增量1-增量2-增量3

binlog起始和终止点

起始点：

cat /backup/inc3_2022-01-13/xtrabackup_binlog_info
mysql-bin.000001  21105729

终止点：at 21106154

截取命令：

mysqlbinlog --start-position=21105729 --stop-position=21106154 /opt/logs/mysql-bin.000001 > /tmp/backup.sql

第12步：处理全备，将临时文件整合到磁盘里

innobackupex --apply-log --redo-only /backup/full_2022-01-13/

第13步：inc1合并到full中，并且prepare

innobackupex --apply-log --redo-only --incremental-dir=inc1_2022-01-13 full_2022-01-13

检验合并结果：

cat /backup/full_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
cat /backup/inc1_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"

第14步：inc2合并到full中，并且prepare

innobackupex --apply-log --redo-only --incremental-dir=inc2_2022-01-13 full_2022-01-13

检验合并结果：

cat /backup/full_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
cat /backup/inc2_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"

第15步：inc3合并到full中，并且prepare

innobackupex --apply-log --redo-only --incremental-dir=inc3_2022-01-13 full_2022-01-13

检验合并结果：

cat /backup/full_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"
cat /backup/inc3_2022-01-13/xtrabackup_checkpoints |grep "to_lsn"

第16步：将合并后全备再次prepare

innobackupex --apply-log  /backup/full_2022-01-13

第17步：恢复数据

innobackupex --copy-back /backup/full_2022-01-13
mkdir /data/mysql_3306/logs/
touch /data/mysql_3306/logs/mysql.err
chown -R mysql:mysql /data/mysql_3306
systemctl start mysqld
mysql -uroot -p123 < /backup/backup.sql

第18步：检查数据 检查数据：xbk、inc1、inc2、inc3、inc4

第19步：立刻做一次全备

innobackupex -uroot -p123 -S /tmp/mysql.sock --no-timestamp /backup/full_OK_$(date +%F)

4. 备份恢复最佳实践​

备份策略建议
  1. 备份周期：根据数据重要性和容忍的数据丢失时间制定备份策略
  2. 工具选择：100G以内使用mysqldump，100G以上使用Xtrabackup
  3. 定期演练：定期进行恢复演练，验证备份有效性
  4. 异地备份：重要数据应进行异地备份
  5. 权限管理：恢复后及时修正文件权限和目录结构

更新: 2025-01-04 10:13:44

最后于 更新