MySQL备份恢复

开化者

已于 2023-08-25 09:25:39 修改

阅读量127

点赞数

分类专栏： MySQL 文章标签： adb android mysql 数据库

于 2023-08-25 09:23:50 首次发布

本文链接：https://blog.csdn.net/li175325/article/details/132488321

版权

MySQL 专栏收录该内容

20 篇文章 0 订阅

订阅专栏

本文详细介绍了MySQL的备份策略，包括日志管理（如binlog、GTID）、mysqldump的使用、不同类型的备份（逻辑全备、增量、物理XBK），以及InnoDB一致性恢复的过程。还涉及了XtraBackup的安装和使用，以及备份恢复的实战演练。

摘要由CSDN通过智能技术生成

1.1 日志

   binlog如何开启?
   log_bin=master-bin
   binlog_format=row ******
       RBR   行级
       SBR 语句，可能会出现恢复歧义
   sync_binlog=1   每次事务提交都立即刷写binlog到磁盘 ******

   show master status ;
   show binlog events in 'master-bin.000004';
   非GTID
   mysqlbinlog --start-position --stop-position
   --base64-output=decode-rows -vvv (--help可以查到)

GTID :全局事务标识符
show variables like '%gtid%';
mysqlbinlog --help |grep gtid
--skip-gtids
--include-gtids=''
--exclude-gtids=''

set sql_log_bin=0;
source ..
set sql_log_bin=1;

1.2 二进制日志清理
1.2.1 自动
expire_logs_days=15
设置的依据: 至少1轮全备周期长度的过期时间.
1.2.2 手工
mysql> help purge
PURGE BINARY LOGS TO 'mysql-bin.000010';
PURGE BINARY LOGS BEFORE '2020-04-02 22:46:26';
mysql> reset master ; #清空所有二进制日志，主从结构不建议执行，单服务器可以

1.3 日志如何滚动
flush logs;
数据库重启
max_binlog_size=1073741824

1.4 slow
mysql> show variables like 'slow_query_log%';
mysql> show variables like 'long%';
mysql> show variables like '%using_indexes%';
mysqldumpslow -s c -t 10 slow.log
pt-query-digest slow.log

备份恢复

1. 在备份恢复中的职责

1.1 备份策略的设计
(1) 备份周期:
   根据数据量.
(2)备份工具:
   mysqldump (MDP) , XBK (PBK) percona Xtrabackup , MEB(MySQL Enterprise BACKUP MEB) ,mysqlbinlog
(3)备份方式:
逻辑:
   全备 mysqldump
   增量 binlog (flush logs ,cp)
物理备份:
   全备 : XBK
   增量 : XBK

1.2 检查备份可用性
crontab -l ----->
备份脚本 ----->
备份路径 ----->
看备份日志,检查备份文件(大小,内容)

1.3 定期的恢复演练
开启测试环境，还原数据库，连接前台业务。

1.4 数据恢复
只要备份和日志是完整的,恢复到故障之前的时间点(快速)

1.5 数据迁移 ***
操作系统不同的迁移
mysql -> mysql
其他 -> mysql
mysql -> 其他

2. 备份的介绍

2.1 备份的策略：
按数据量（50-80G），小数据量每天全备；大数据量（1T以上），周日全备，其余增备。

2.2 备份的工具
mysqldump xtrabackup

2.3 备份类型
   热备 : 对于业务影响最小 InnoDB
   温备 : 长时间锁表备份 MyISAM
   冷备 : 业务关闭情况下备份

3. mysqldump

3.1 连接数据库
-u   用户
-p    密码
-S    安全套接字
-h    主机
-P    端口号

3.2 基础备份参数
-A 全库(等于 --all-databases)
   mysqldump -uroot -p123 -A >/backup/full.sql

-B 单库或多个单库
   mysqldump -uroot -p123 -B world wordpress >/backup/db.sql

库表
   mysqldump -uroot -p123 world city country > /backup/tab.sql

3.3 特殊备份参数

-R 存储过程和函数

-E 事件

--triggers 触发器

--master-data=2 *****
(值为1：change master to 语句可以被slave直接执行；值为2：change master会被注释)

--single-transaction *****
对于InnoDB的表,进行一致性快照备份,不锁表；
不加本参数，执行温备份

4. 恢复案例

4.1 背景环境：
正在运行的网站系统，mysql-5.7.20 数据库，数据量50G，日业务增量3-10M。
4.2 备份策略：
每天23:00点，计划任务调用mysqldump执行全备脚本
4.3 故障时间点：
年底故障演练:模拟周三上午10点误删除数据库.
4.4 思路：
1、停业务，挂维护页,避免数据的二次伤害
2、找一个临时库，恢复周二23：00全备
3、截取周二23：00 --- 周三10点误删除之间的binlog，恢复到临时库
4、测试可用性和完整性
5、
5.1 方法一：直接使用临时库顶替原生产库，前端应用割接到新库
5.2 方法二：将误删除的表导出，导入到原生产库
6、开启业务
处理结果：经过20分钟的处理，最终业务恢复正常

4.5 故障模拟演练
4.5.1 准备数据
create database backup;
use backup
create table t1 (id int);
insert into t1 values(1),(2),(3);
commit;

4.5.2 周二 23：00全备

mysqldump -uroot -p123456 -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction > /backup/full_$(date +%F).sql

4.5.3 模拟周二 23：00到周三 10点之间数据变化
use backup
insert into t1 values(11),(22),(33);
commit;
create table t2 (id int);
insert into t2 values(11),(22),(33);
commit;

4.5.4 模拟故障,删除表(只是模拟，不代表生产操作)
drop database backup;

4.6 恢复过程
4.6.1 准备临时数据库（多实例3307）
systemctl start mysqld3307
4.6.2 准备备份
（1）准备全备：
cd /backup
ls
full_2021-05-31.sql
（2）截取二进制日志
mysql
show binlog events in 'mysql-bin.000001';

mysqlbinlog --skip-gtids --include-gtids='820f8917-d358-11ec-b243-000c29cbdce4:4-6' mysql-bin.000001 > /tmp/a.sql

4.6.3 恢复备份到临时库
mysql -S /data/3307/mysql.sock
set sql_log_bin=0;
source /backup/full_2021-05-31.sql
source /tmp/a.sql

4.6.4 将临时库导出并恢复到生产
mysqldump -S /data/3307/mysql.sock -B backup >/backup/bak.sql
mysql -uroot -p123
set sql_log_bin=0
source /backup/bak.sql;

5. 练习：

1、创建一个数据库hehe
2、在hehe下创建一张表t1
3、插入5行任意数据
4、全备
5、插入两行数据，任意修改3行数据，删除1行数据
6、删除所有数据
7、再t1中又插入5行新数据，修改3行数据

需求，跳过第六步恢复表数据

6. 扩展参数 ***

在构建主从时,使用AUTO/ON
--set-gtid-purged=AUTO/ON

仅是做普通的本机备份恢复时,可以添加
--set-gtid-purged=OFF

SET @@GLOBAL.GTID_PURGED='aa648280-a6a6-11e9-949f-000c294a1b3b:1-11';

--max_allowed_packet=128M 控制的是备份时传输数据包的大小.(默认max_allowed_packet变量为16MB)

mysqldump -uroot -p123 -A -R --max_allowed_packet=128M --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

7. 物理备份-XBK

7.1 安装依赖包：
wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repo
yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

7.2 下载软件并安装
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 install percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpm

yum -y localinstall percona-toolkit-3.3.0-1.el7.x86_64.rpm

7.3 innobackupex 使用
7.3.1 备份核心理念
1. 针对非InnoDB,进行锁表备份,copy所有的非innoDB表文件
2. 针对InnoDB表,立即触发CKPT,copy所有InnoDB表相关的文件(ibdata1,ibd,frm).
并且将备份过程中产生,新的数据变化的部分redo一起备份走
3. 在恢复时,xbk会调用InnoDB引擎的CSR过程,将数据和redo的LSN追平,然后进行一致性恢复.

7.3.2 备份过程
(1) 全备
innobackupex --user=root --password=123 -S /tmp/mysql.sock --no-timestamp /backup/full
(2) 利用全备进行恢复
1.
pkill mysqld
2.
rm -rf /usr/local/mysql/data/*
3. *****
innobackupex --apply-log /backup/full/
4.
cp -a /backup/full/* /usr/local/mysql/data/
5.
chown -R mysql.mysql /usr/local/mysql/data/*
6.
/etc/init.d/mysqld start