文章目录

展开

MySQL 的备份和恢复

为什么要备份

1)备份就是为了恢复。
2)尽量减少数据的丢失(公司的损失)

备份类型

rsync:

  • 1)冷备

    停机、停服务进行备份

  • 2)热备

    不停机、不停服务,直接备份

MySQL

  • 1)冷备

    停库,维护页,进行备份

  • 2)温备

    不停库,但是备份时,会锁表

  • 3)热备

    不停库,不锁表,直接备份数据

备份方式

  • 1)逻辑备份

    基于 SQL 语句进行备份

    • binlog
    • mysqldump
    • replication(主从复制)
    • into outfile(狗都不用)
    mysql> select * from world.city into outfile '/tmp/world_city.data';
    
  • 2)物理备份

    基于数据文件进行备份

    • 直接打包 datadir
    • Xtrabackup(percona)

备份策略(每天一次全备,每小时一次增备)

全量备份

增量备份

差异备份

MySQL 逻辑备份工具 mysqldump

-u 指定用户
-p 指定密码
-h 指定主机域
-S 指定socket文件
-P 指定端口

所有库备份

-A:备份所有库
--all-database:备份所有库

例子:
mysqldump -uroot -p123 -A > /tmp/all.sql

## 备份文件中注释
-- 注释内容
/* 注释内容 */

## MySQL恢复数据,是覆盖形式的
drop table
create table
insert

备份单个库

# -B:备份单个库,指定库名(备份库和表)
mysqldump -uroot -p123 -B world > /tmp/world.sql
mysql -uroot -p123 < /tmp/world.sql

# -B:备份多个库
mysqldump -uroot -p123 -B 库名1 库名2 > /tmp/city.sql

# 不加选项,直接指定库名(备份表)
mysqldump -uroot -p123 world > /tmp/city.sql
mysql -uroot -p123 ljy < /tmp/city.sql

# 单表备份
mysqldump -uroot -p123 库名1 表名1 表名2 > /tmp/city.sql
mysqldump -uroot -p123 ljy student > /tmp/ljy_student.sql

备份时刷新 binlog

# -F: 备份数据,并刷新binlog(有几个库就刷几个binlog,总结狗都不用)
mysqldump -uroot -p123 -B world -F > /tmp/world.sql

mysqldump -uroot -p123 -A -F > /tmp/all.sql

打点备份参数

--master-data=0|1|2
0:关闭
1:备份一个change master语句,不注释
2:备份一个change master语句,注释

## 可用于在生产环境中做主从复制(在用户不断写入的情况下进行主从复制)

change master to
master_user='rep',
master_host='172.16.1.51',
master_password='123',
master_log_file='使用日志中截取的change master的binlog编号',
master_log_pos=使用日志截取的change master的binlog大小;

快照备份

不锁表备份,需要指定一个快照,否则永远备份不完
--single-transaction

# 完整备份语句v1.0
mysqldump -uroot -p123 -A --master-data=2 --single-transaction > /tmp/all.sql

额外扩展选项(忘记他)

-d:只备份表结构
-t:只备份数据

实用的扩展选项

-R: 备份MySQL的函数
--triggers: 备份MySQL的触发器
# 完整备份语句v2.0
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/all.sql

完整备份语句 v3.0

# 不显示日期版
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/all.sql.gz

# 显示日期版
mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/all_$(date +%F).sql.gz

mysqldump 的恢复

# 先临时不记录二进制日志
mysql> set sql_log_bin=0;
# 库内恢复操作
mysql> source /tmp/all.sql
# 库外恢复操作
mysql -uroot -p123 < /tmp/all.sql

注意:

1)mysqldump在备份或恢复时都需要MySQL实例启动为前提
2)一般数据量级100GB以内,大约15-30分钟可以恢复(PB、EB就需要考虑别的方式)
3)mysqldump是以覆盖的形式恢复数据的

mysqldump 企业案例

背景:

正在运行的网站系统,MySQL 数据库,数据量 25G,日业务增量 10-15M。

备份策略:

每天 23:00,计划任务调用 mysqldump 执行全备脚本

故障时间点:

上午 10 点开发人员误删除一个核心业务表,如何恢复?

模拟故障

# 创库
root@localhost [(none)] >create database backup1;
# 切换到logs
root@localhost [(none)] >use backup1;
# 创建表test_logs
root@localhost [logs] >create table test_backup1(id int);
# 插入数据
root@localhost [logs] >insert into test_backup1 values(1);
root@localhost [logs] >insert into test_backup1 values(2);
root@localhost [logs] >insert into test_backup1 values(3);
# 查询表
root@localhost [logs] >select * from test_backup1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
# 修改id
root@localhost [logs] >update test_backup1 set id=10 where id=2;
# 插入数据
root@localhost [logs] >insert into test_backup1 values(4);
root@localhost [logs] >insert into test_backup1 values(5);
root@localhost [logs] >insert into test_backup1 values(6);

# 拿到之前的全量备份
[root@db02 ~]# mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/all_$(date +%F-%H).sql.gz

# 删除表
root@localhost [logs] >drop table backup1.test_backup1;

思路:

# 1.准备一个新环境(模拟)
## 1.1删除data目录(模拟时操作)
## 1.2生成新环境
[root@db01 scripts]# ./mysql_install_db --basedir=/app/mysql --datadir=/app/mysql/data
## 1.3启动数据库
[root@db01 scripts]# /etc/init.d/mysqld start
## 1.4新环境创建用户
mysql> grant all on *.* to test@'%' identified by '123';

# 2.将旧库的全备恢复到新环境(如果全备文件很大,scp速度慢)
方法一:
[root@db02 ~]# scp /tmp/full_2023-08-02-11.sql.gz 172.16.1.51:/root
[root@db01 ~]# zcat /root/full_2023-08-02-11.sql.gz |mysql -uroot -p123

方法二:
[root@db02 ~]# zcat /tmp/all_2023-08-02-15.sql.gz |mysql -utest -p123 -h10.0.0.51

# 3.截取binlog
1)第一段binlog,全备后:起始位置点 95885
[root@db02 tmp]# zcat /tmp/all_2023-08-02-16.sql.gz|head -25

2)第一段binlog,删表前:结束位置点 98277
mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000009 | grep -i 'drop table' -C 5

## 截取第一段:
[root@db02 tmp]# mysqlbinlog --start-position=95885 --stop-position=98277 /app/mysql/data/mysql-bin.000009 > /tmp/inc1.sql

## 恢复第一段
[root@db02 tmp]# mysql -utest -p123 -h10.0.0.51 < /tmp/inc1.sql

3)第二段binlog,删表后,起始位置点 98411
mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000009 | grep -i 'drop table' -C 5

## 先停连接数据库的程序,挂维护页,截取结束位置点

4)第二段binlog,停库前,结束位置点 125666

## 截取第二段:
[root@db02 tmp]# mysqlbinlog --start-position=98411 --stop-position=125666 /app/mysql/data/mysql-bin.000009 > /tmp/inc2.sql

## 恢复第二段binlog
[root@db02 tmp]# mysql -utest -p123 -h10.0.0.51 < /tmp/inc2.sql 
### db01
mysql> select * from test_backup1;
+------+
| id   |
+------+
|    1 |
|   10 |
|    3 |
|    4 |
|    5 |
|    6 |
+------+

# 4.应用割接
- 改代码
- mysqldump 新环境的数据,恢复到旧环境
[root@db02 tmp]# mysqldump -utest -p123 -h10.0.0.51 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full_51.sql.gz

[root@db02 tmp]# zcat /tmp/full_51.sql.gz |mysql -uroot -p123

# 5.启动连接数据库的程序,取消维护页

编写 MySQL 备份脚本 mysqldump 并放入定时任务

# 编写脚本
vim bf.sh
#!/bin/bash
# 定义备份目录和文件名
backup_dir="/tmp/bf"
backup_file="${backup_dir}/$(date +%Y%m%d%H%M%S).sql.gz"

# 定义配置文件路径
config_file="$HOME/.my.cnf"

# 创建备份目录
/usr/bin/mkdir -p ${backup_dir}

# 执行备份目录
/app/mysql/bin/mysqldump --defaults-file=${config_file} -A -R --triggers --master-data=2 --single-transaction|/usr/bin/gzip > ${backup_file}

# 检查备份是否成功
backup_size=$(stat -c %s ${backup_file})
/usr/bin/md5sum ${backup_file} >/tmp/bf/1.txt
if [ $backup_size -gt 0 ];then
                /usr/bin/echo "备份文件:${backup_file},有内容"
        if /usr/bin/md5sum -c /tmp/bf/1.txt >/dev/null ;then
                /usr/bin/echo "数据库全备成功,文件完整,备份文件: ${backup_file}"
        else
                /usr/bin/echo "数据库全备失败,文件不完整,备份文件: ${backup_file}"
        fi
else
        /usr/bin/echo "备份文件:无内容"
fi

# 取消注释版----------------------------------------------------------------------------------------------------------------------------------
#!/bin/bash
backup_dir="/tmp/bf"
backup_file="${backup_dir}/$(date +%Y%m%d%H%M%S).sql.gz"

config_file="$HOME/.my.cnf"

/usr/bin/mkdir -p ${backup_dir}

/app/mysql/bin/mysqldump --defaults-file=${config_file} -A -R --triggers --master-data=2 --single-transaction|/usr/bin/gzip > ${backup_file}

backup_size=$(stat -c %s ${backup_file})
/usr/bin/md5sum ${backup_file} >/tmp/bf/1.txt
if [ $backup_size -gt 20 ];then
                /usr/bin/echo "备份文件:${backup_file},有内容"
        if /usr/bin/md5sum -c /tmp/bf/1.txt >/dev/null ;then
                /usr/bin/echo "数据库全备成功,文件完整,备份文件: ${backup_file}"
        else
                /usr/bin/echo "数据库全备失败,文件不完整,备份文件: ${backup_file}"
        fi
else
        /usr/bin/echo "备份文件:无内容"
fi

# 增加权限
[root@db02 ~]# chmod +x bf.sh 

# 创建数据库密码文件
[root@db02 ~]# vim /root/.my.cnf 
[client]
user=root
password=123

# 编辑crontab加入定时任务
[root@db02 ~]# crontab -e
0 23 * * * /bin/bash /root/bf.sh

物理备份

Xtrabackup 下载安装

# 1.下载Xtrabackup
[root@db02 ~]# wget http://test.driverzeng.com/MySQL_Package/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm
# 2.安装Xtrabackup
[root@db02 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

# 法二
[root@db01 ~]# yum install -y https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.28/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.28-1.el7.x86_64.rpm

Xtrabackup 全备

## 老版本(温备:会锁表)
xtrabackup

## 新版本(热备:针对innodb存储引擎进行备份,不会锁表)
innobackupex

# 备份方式(物理备份)
1)对于非innodb表(比如myisam)是直接锁表cp数据文件,属于一种温备。
2)对于innodb的表(支持事务),不锁表,cp数据页最终以数据文件方式保存下来,并且把redo和undo一并备走,属于热备方式。
3)备份时读取配置文件/etc/my.cnf

innobackupex 命令基础选项

innobackupex
--user:     指定用户名
--password: 指定密码
--socket:   指定socket文件
--host:     指定主机名或ip地址
--port:     指定端口
--datadir:  指定MySQL数据文件存储的目录路径
--parallel: 指定并行备份线程的数量
--compress: 在备份时使用压缩算法对数据进行压缩
--throttle: 设置备份速率限制,以控制备份操作对系统资源的影响。
--apply-log:模拟CSR
--copy-back:恢复数据
--incremental:增量备份的开关,开启增量备份
--incremental-basedir:指定上一次备份的目录
--no-timestamp:不需要时间戳
--redo-only: 只做redo

innobackupex 全备

innobackupex --user=root --password=123 /backup/
[root@db02 ~]# ll /backup/2023-08-03_08-57-01/
-rw-r----- 1 root root       25 Aug  3 08:57 xtrabackup_binlog_info
-rw-r----- 1 root root      115 Aug  3 08:57 xtrabackup_checkpoints
-rw-r----- 1 root root      469 Aug  3 08:57 xtrabackup_info
-rw-r----- 1 root root     2560 Aug  3 08:57 xtrabackup_logfile

[root@db02 2023-08-03_08-57-01]# cat xtrabackup_binlog_info    --master-data=2 打点文件
mysql-bin.000010        5548729

[root@db02 2023-08-03_08-57-01]# cat xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0           数据备份版本号开始
to_lsn = 42078688      数据备份版本号结束
last_lsn = 42078688    最新数据备份版本号
compact = 0
recover_binlog_info = 0

[root@db02 2023-08-03_08-57-01]# cat xtrabackup_info 
uuid = a87507b1-3198-11ee-b1c2-000c29db5c20
name = 
tool_name = innobackupex
tool_command = --user=root --password=... /backup/
tool_version = 2.4.4
ibbackup_version = 2.4.4
server_version = 5.6.50-log
start_time = 2023-08-03 08:57:01
end_time = 2023-08-03 08:57:02
lock_time = 0 # 热备
binlog_pos = filename 'mysql-bin.000010', position '5548729'
innodb_from_lsn = 0
innodb_to_lsn = 42078688
partial = N
incremental = N
format = file
compact = N
compressed = N
encrypted = N

xtrabackup_logfile # redo log

全备的恢复

1) 前提:被恢复的目录是空的
2) 前提:被恢复的数据库的实例是关闭的,需要停库恢复

# 1.先停库
systemctl stop mysqld

# 2.清空被恢复的目录
rm -fr /app/mysql/data

# 3.先手动CSR,将redo中的数据,重做一遍,然后将undo的数据,回滚一遍(不能做增量备份了)
innobackupex --apply-log 2023-08-03_08-57-01

# 4.恢复数据(my.cnf配置文件中写datadir的文件路径)
innobackupex --copy-back /backup/2023-08-03_08-57-01

# 5.修改权限
chown -R mysql.mysql /app/mysql/data

innobackupex 增量备份

# 增量备份
基于上一次备份后新增的数据备份

# 1.全备
innobackupex --user=root --password=123 --no-timestamp /backup/full-$(date +%F)
[root@db02 full-2023-08-03]# cat xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0
to_lsn = 42113609
last_lsn = 42113609
compact = 0
recover_binlog_info = 0

# 2.第一次增量备份
[root@db02 full-2023-08-03]# innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full-2023-08-03/ /backup/inc1-$(date +%F-%H)
[root@db02 inc1-2023-08-03-10]# cat xtrabackup_checkpoints
backup_type = incremental
from_lsn = 42113609
to_lsn = 42128959
last_lsn = 42129234
compact = 0
recover_binlog_info = 0

# 3.第二次增备
innobackupex --no-timestamp --incremental --incremental-basedir=/backup/inc1-2023-08-03-10/ /backup/inc2-$(date +%F-%H)
[root@db02 inc2-2023-08-03-10]# cat xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 42128959
to_lsn = 42152473
last_lsn = 42152748
compact = 0
recover_binlog_info = 0

# 4.第三次增备
innobackupex --no-timestamp --incremental --incremental-basedir=/backup/inc2-2023-08-03-10/ /backup/inc3-$(date +%F-%H)
[root@db02 inc3-2023-08-03-10]# cat xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 42152473
to_lsn = 42158994
last_lsn = 42158994
compact = 0
recover_binlog_info = 0

xtrabackup 增量备份恢复

1)基于上一次备份进行增量

2)增量备份无法单独恢复,必须基于全备进行恢复

3)所有增量必须要按顺序合并到全备当中

root@localhost [(none)] >drop database binlog;
root@localhost [(none)] >drop database prod;
# 1.停库
[root@db02 inc3-2023-08-03-10]# systemctl stop mysqld

# 2.移动data数据
mv /app/mysql/data /tmp

# 3.先模拟CSR
1)全备模拟CSR,只做redo,不做undo
innobackupex --apply-log --redo-only /backup/full-2023-08-03/

[root@db02 full-2023-08-03]# cat xtrabackup_checkpoints
backup_type = log-applied
from_lsn = 0
to_lsn = 42113609
last_lsn = 42113609
compact = 0
recover_binlog_info = 0

[root@db02 full-2023-08-03]# cat xtrabackup_binlog_info 
mysql-bin.000001        7250

2)第一次增备合并到全备的时候,只做redo,不做undo
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc1-2023-08-03-10 /backup/full-2023-08-03/

[root@db02 full-2023-08-03]# cat xtrabackup_binlog_info 
mysql-bin.000001        13690

[root@db02 full-2023-08-03]# cat xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 42128959
last_lsn = 42129234
compact = 0
recover_binlog_info = 0

3)第二次增备合并到全备的时候,只做redo,不做undo
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc2-2023-08-03-10 /backup/full-2023-08-03/
[root@db02 full-2023-08-03]# cat xtrabackup_binlog_info 
mysql-bin.000002        1776

[root@db02 full-2023-08-03]# cat xtrabackup_checkpoints 
backup_type = log-applied
from_lsn = 0
to_lsn = 42152473
last_lsn = 42152748
compact = 0
recover_binlog_info = 0

4)第三次增备合并到全备的时候,做redo和做undo都做
innobackupex --apply-log --incremental-dir=/backup/inc3-2023-08-03-10 /backup/full-2023-08-03/

[root@db02 full-2023-08-03]# cat xtrabackup_binlog_info 
mysql-bin.000002        4904

[root@db02 full-2023-08-03]# cat xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 42158994
last_lsn = 42158994
compact = 0
recover_binlog_info = 0

5)最后全部合并完成后,把全备redo和undo都做一遍
innobackupex --apply-log /backup/full-2023-08-03/

# 4.恢复数据
innobackupex --copy-back /backup/full-2023-08-03/

# 5.授权
[root@db02 mysql]# chown -R mysql.mysql /app/mysql/data

# 6.截取binlog
1)第一段 全备恢复后 —— drop database binlog
起始点位置:4904
[root@db02 backup]# cat /app/mysql/data/xtrabackup_binlog_pos_innodb 
mysql-bin.000002        4904
结束点位置:7848
mysqlbinlog --base64-output=decode-rows -vvv /tmp/data/mysql-bin.000002|grep -i 'drop database' -C 5

## 导出第一段数据
[root@db02 backup]# mysqlbinlog --start-position=4904 --stop-position=7848 /tmp/data/mysql-bin.000002 > /tmp/inc1.sql

2)第二段 drop database binlog —— drop database prod
起始点位置:7946
mysqlbinlog --base64-output=decode-rows -vvv /tmp/data/mysql-bin.000002|grep -i 'drop database' -C 5
结束点位置:8130
mysqlbinlog --base64-output=decode-rows -vvv /tmp/data/mysql-bin.000002|grep -i 'drop database' -C 5

## 导出第二段数据
[root@db02 backup]# mysqlbinlog --start-position=7946 --stop-position=8130 /tmp/data/mysql-bin.000002 > /tmp/inc2.sql

3)第三段 drop database prod —— binlog 结尾(停库之前)
起始点位置:8222
mysqlbinlog --base64-output=decode-rows -vvv /tmp/data/mysql-bin.000002|grep -i 'drop database' -C 5
结束点位置:8245

## 导出第三段数据
[root@db02 backup]# mysqlbinlog --start-position=8222 --stop-position=8245 /tmp/data/mysql-bin.000002 > /tmp/inc3.sql

# 7.导入数据
root@localhost [(none)] >set sql_log_bin=0;
root@localhost [(none)] >source /tmp/inc1.sql
root@localhost [(none)] >source /tmp/inc2.sql
root@localhost [(none)] >source /tmp/inc3.sql

innobackupex 差异备份

基于全备后新增的数据备份

# 1.全备
innobackupex --no-timestamp /backup/full1-$(date +%F)

# 2.第一次差异备份
innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full1-2023-08-03/ /backup/chayi1-$(date +%F-%H)

# 3.第二次差异备份
innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full1-2023-08-03/ /backup/chayi2-$(date +%F-%H)

# 4.第三次差异备份
innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full1-2023-08-03/ /backup/chayi3-$(date +%F-%H)

# 5.第四次差异备份
innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full1-2023-08-03/ /backup/chayi4-$(date +%F-%H)

# 6.模拟环境
root@localhost [(none)] >drop database prod;

# 7.停库
systemctl stop mysqld

# 8.移动data数据
mv /app/mysql/data /opt

# 9.模拟CSR
1)全备模拟CSR,只做redo,不做undo
innobackupex --apply-log --redo-only /backup/full1-2023-08-03/

2)合并最后一次差异备份,redo和undo全做
innobackupex --apply-log --incremental-dir=/backup/chayi4-2023-08-03-12 /backup/full1-2023-08-03/

3)最后全部合并完成后,把全备redo和undo都做一遍
innobackupex --apply-log /backup/full1-2023-08-03/

# 10.恢复数据
innobackupex --copy-back /backup/full1-2023-08-03/

# 11.授权
chown -R mysql.mysql /app/mysql/data

# 12.截取binlog新增数据
起始位置 6514
cat /app/mysql/data/xtrabackup_binlog_pos_innodb
mysql-bin.000001        6514
结束位置 8175
mysqlbinlog --base64-output=decode-rows -vvv /opt/data/mysql-bin.000001 |grep -i 'drop database ' -C 5

# 13.导出binlog数据
mysqlbinlog --start-position=6514 --stop-position=8175 /opt/data/mysql-bin.000001 > /tmp/chayi.sql

# 14.导入数据
## 临时关闭binlog的记录
root@localhost [(none)] >set sql_log_bin=0;

## 恢复数据
root@localhost [(none)] >source /tmp/chayi.sql;

img

# 报错没有指定socket文件

[root@db01 ~]# innobackupex --user=root --password=123 /tmp
230802 16:23:38 innobackupex: Starting the backup operation

IMPORTANT: Please check that the backup run completes successfully.
           At the end of a successful backup run innobackupex
           prints "completed OK!".

230802 16:23:38  version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup' as 'root'  (using password: YES).
Failed to connect to MySQL server: DBI connect(';mysql_read_default_group=xtrabackup','root',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at - line 1314.
230802 16:23:38 Connecting to MySQL server host: localhost, user: root, password: set, port: 0, socket: (null)
Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2).

## 如果找不到socket位置
mysqladmin -uroot -p123 variables | grep socket

# 解决方法
方法一
[root@db01 mysql]# innobackupex --user=root --password=123 --socket=/app/mysql-5.6.50/tmp/mysql.sock /tmp

方法二
加进配置文件重启数据库
[root@db01 ~]# cat /etc/my.cnf
[mysqld]
socket=/app/mysql-5.6.50/tmp/mysql.sock

[root@db01 ~]# ll /tmp/2023-08-02_16-27-40/
total 129056
drwxr-x--- 2 root root       56 Aug  2 16:27 backup
-rw-r----- 1 root root      430 Aug  2 16:27 backup-my.cnf
-rw-r----- 1 root root 79691776 Aug  2 16:27 ibdata1
-rw-r----- 1 root root 52428800 Aug  2 16:27 ibdata2
drwxr-x--- 2 root root      166 Aug  2 16:27 linux50
drwxr-x--- 2 root root     4096 Aug  2 16:27 mtlbbdb_19900
drwxr-x--- 2 root root     4096 Aug  2 16:27 mysql
drwxr-x--- 2 root root     4096 Aug  2 16:27 performance_schema
drwxr-x--- 2 root root       48 Aug  2 16:27 prod
drwxr-x--- 2 root root       20 Aug  2 16:27 test
drwxr-x--- 2 root root      144 Aug  2 16:27 world
drwxr-x--- 2 root root       88 Aug  2 16:27 www
drwxr-x--- 2 root root       20 Aug  2 16:27 wudier
drwxr-x--- 2 root root       20 Aug  2 16:27 wudier1
-rw-r----- 1 root root       25 Aug  2 16:27 xtrabackup_binlog_info
-rw-r----- 1 root root      115 Aug  2 16:27 xtrabackup_checkpoints
-rw-r----- 1 root root      507 Aug  2 16:27 xtrabackup_info
-rw-r----- 1 root root     2560 Aug  2 16:27 xtrabackup_logfile
drwxr-x--- 2 root root       20 Aug  2 16:27 xx

企业级增量恢复实战

背景:

某大型网站,mysql 数据库,数据量 500G,每日更新量 100M-200M

备份策略:

xtrabackup,每周六 0:00 进行全备,周一到周五及周日 00:00 进行增量备份。

故障场景:

周三下午 2 点出现数据库意外删除表操作。

模拟故障:

# 编辑脚本
#!/bin/bash

mysql -uroot -p123 -e "drop database if exists prod;"
mysql -uroot -p123 -e "create database if not exists prod;"
mysql -uroot -p123 -e "create table if not exists prod.t1(id int);"

num=1
while true;do
  mysql -uroot -p123 -e "insert into prod.t1 values($num);commit;"
  ((num++))
  sleep 1
done

# 创库
root@localhost [(none)] >create database logs;
# 切换到logs
root@localhost [(none)] >use logs;
# 创建表test_logs
root@localhost [logs] >create table test_logs(id int);
# 插入数据
root@localhost [logs] >insert into test_logs values(1);
root@localhost [logs] >insert into test_logs values(2);
root@localhost [logs] >insert into test_logs values(3);
# 查询表
root@localhost [logs] >select * from test_logs;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+
# 修改id
root@localhost [logs] >update test_logs set id=10 where id=2;
# 删除id
root@localhost [logs] >delete from test_logs where id=3;

# 全备
innobackupex --no-timestamp /backup/full-$(date +%F)

# 第一次增量备份
innobackupex --no-timestamp --incremental --incremental-basedir=/backup/full-2023-08-03/ /backup/inc-$(date +%F-%H)

# 第二次增备
innobackupex --no-timestamp --incremental --incremental-basedir=/backup/inc-2023-08-04-00/ /backup/inc-$(date +%F-%H)

# 第三次增备
innobackupex --no-timestamp --incremental --incremental-basedir=/backup/inc-2023-08-05-00/ /backup/inc-$(date +%F-%H)

# 第四次增备
innobackupex --no-timestamp --incremental --incremental-basedir=/backup/inc-2023-08-06-00/ /backup/inc-$(date +%F-%H)

# 删除表
root@localhost [logs] >drop table test_logs;

解决思路:

# 1.停库
systemctl stop mysqld

# 2.先模拟CSR(旧库)
1)全备模拟CSR,只做redo,不做undo
innobackupex --apply-log --redo-only /backup/full-2023-08-03/

2)第一次增备合并到全备的时候,只做redo,不做undo
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc-2023-08-04-00 /backup/full-2023-08-03/

3)第二次增备合并到全备的时候,只做redo,不做undo
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc-2023-08-05-00 /backup/full-2023-08-03/

4)第三次增备合并到全备的时候,只做redo,不做undo
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc-2023-08-06-00 /backup/full-2023-08-03/

5)第四次增备合并到全备的时候,做redo和做undo都做
innobackupex --apply-log --redo-only --incremental-dir=/backup/inc-2023-08-07-00 /backup/full-2023-08-03/

6)最后全部合并完成后,把全备redo和undo都做一遍
innobackupex --apply-log /backup/full-2023-08-03/

# 3.准备一个新环境(模拟)
## 3.1生成新环境
[root@db01 scripts]# ./mysql_install_db --basedir=/app/mysql --datadir=/app/mysql/data
## 3.2删除data目录
rm -fr /app/mysql/data

# 4.将旧库的全备及增备恢复到新环境(如果全备文件很大,scp速度慢)
## 切换到db01
mkdir /backup
scp /backup/full-2023-08-03 root@10.0.0.51:/backup/
innobackupex --copy-back /backup/full-2023-08-03/

# 5.授权
[root@db01 mysql]# chown -R mysql.mysql /app/mysql/data

# 6.截取binlog
1)第一段binlog,全备后:起始位置点 95885
[root@db02 tmp]# cat /app/mysql/data/xtrabackup_binlog_pos_innodb
mysql-bin.000015        95885
2)第一段binlog,删表前:结束位置点 98277
mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000015 | grep -i 'drop table' -C 5

## 导出第一段数据:
[root@db02 tmp]# mysqlbinlog --start-position=95885 --stop-position=98277 /app/mysql/data/mysql-bin.000015 > /tmp/inc1.sql

## 恢复第一段
[root@db02 tmp]# scp /tmp/inc1.sql root@10.0.0.51:/tmp/

3)第二段binlog,删表后,起始位置点 98411
mysqlbinlog --base64-output=decode-rows -vvv /app/mysql/data/mysql-bin.000015 | grep -i 'drop table' -C 5

## 先停连接数据库的程序,挂维护页,截取结束位置点

4)第二段binlog,停库前,结束位置点 125666

## 截取第二段:
[root@db02 tmp]# mysqlbinlog --start-position=98411 --stop-position=125666 /app/mysql/data/mysql-bin.000015 > /tmp/inc2.sql

## 恢复第二段binlog
[root@db02 tmp]# scp /tmp/inc2.sql root@10.0.0.51:/tmp/

# 7.启动数据库
/etc/init.d/mysqld start

# 8.导入数据
root@localhost [(none)] >set sql_log_bin=0;
root@localhost [(none)] >source /tmp/inc1.sql
root@localhost [(none)] >source /tmp/inc2.sql

# 7.应用割接
- 改代码
- mysqldump 新环境的数据,恢复到旧环境
[root@db02 tmp]# mysqldump -utest -p123 -h10.0.0.51 -A -R --triggers --master-data=2 --single-transaction|gzip > /tmp/full.sql.gz

[root@db02 tmp]# zcat /tmp/full.sql.gz |mysql -uroot -p123

# 5.启动连接数据库的程序,取消维护页

使用 Xtrabackup 备份脚本,每天全备,每小时増备

全备

db_user="root"
db_pass="123"
#全备份目录
backup_dir="/data/backup"
#间隔时间2小时
#incremental_interval=$((2 * 60 * 60))
#当前日期
current_time=$(date "+%H:%M")
current_day=$(date "+%w")
#当前小时
hour=$(date "+%H")

#全量备份
full_backup(){
        innobackupex --user="$db_user" --password="$db_pass" --no-timestamp --parallel=4 --backup "$backup_dir/full-$(date +%F)"
exit 0
}
full_backup

増备

#!/bin/bash

. /root/b.sh
#增量备份目录
backup_inc='/data/backup2'
#间隔时间2小时
#incremental_interval=$((2 * 60 * 60))
#当前日期

#增量备份
incremental_backup(){
        incremental_dir="${backup_inc}/inc-$(date "+%F_%H%M%S")"   #增量备份目录名字 日期:时:分:秒
dir_num=$(ls -d "$backup_inc"/inc-* 2>/dev/null|sort -r|head -n1) #增量目录内容行数
if [ -z $dir_num ];then
#开始增量备份
        innobackupex --user="$db_user" --password="$db_pass" --no-timestamp --parallel=4 --incremental --incremental-basedir="$last_full"  "$incremental_dir" 
        echo "$(date) - 第一次增量备份完成"
else
#最新的增量备份文件位置
last_inc=$(ls -d "$backup_inc"/inc-* 2>/dev/null | sort -r | head -n1)
#非第一次增量备份
        innobackupex --user="$db_user" --password="$db_pass" --no-timestamp --parallel=4 --incremental --incremental-basedir="$last_inc"  "$incremental_dir"
        echo "$(date) - 非首次增量备份完成"
fi
}

while true;do

#找最新的全备目录
last_full=$(ls -d "$backup_dir"/full-* 2>/dev/null | sort -r | head -n1)
if [ -z "$last_full" ]; then 
    echo "找不到最新的全量备份,请先做全备" #这条最好加个报警触发
    exit 1
else
# 执行增量备份
incremental_backup 
fi
sleep 7200
done