MySQL数据库：掌握备份与恢复的艺术，确保数据安全无忧

•  作者简介：我是团团儿，是一名专注于云计算领域的专业创作者，感谢大家的关注
•  座右铭：   云端筑梦，数据为翼，探索无限可能，引领云计算新纪元
•  个人主页：团儿.-CSDN博客

目录

前言：

正文：

一.日志

1.binlog如何开启?

2.二进制日志清理

2.1 自动

2.2 手工

3. 日志如何滚动

flush logs;

slow：

二.备份恢复

1. 在备份恢复中的职责

1.1  备份策略的设计

(1) 备份周期:

(2)备份工具:

(3)备份方式:

逻辑:

物理备份:

1.2 检查备份可用性

1.3 定期的恢复演练

1.4 数据恢复

1.5 数据迁移   ***

2. 备份的介绍

2.1 备份的策略：

2.2 备份的工具

2.3 备份类型

3. mysqldump  

3.1 连接数据库

3.2 基础备份参数

-A 全库(等于 --all-databases)

-B 单库或多个单库

库 表

3.3 特殊备份参数

4. 恢复案例

4.1 背景环境：

4.2 备份策略：

4.3 故障时间点：

4.4 思路：

4.5 故障模拟演练

4.5.1 准备数据

4.5.2 周二 23：00全备

4.5.3 模拟周二 23：00到周三 10点之间数据变化

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

4.6 恢复过程

4.6.1 准备临时数据库（多实例3307）

4.6.2 准备备份

（1）准备全备：

（2）截取二进制日志

4.6.4 将临时库导出并恢复到生产

三.分库分表备份脚本：

扩展参数  ***

物理备份-XBK

1.安装依赖包：

2.下载软件并安装

3.innobackupex 使用

3.1 备份核心理念

3.2备份过程

(1) 全备

(2) 利用全备进行恢复

---

前言：

在数字化时代，数据已成为企业最宝贵的资产之一。无论是初创公司还是行业巨头，数据的完整性和可用性都是其业务运营和决策制定的基石。MySQL，作为世界上最流行的开源关系型数据库管理系统之一，广泛应用于各类网站、应用及企业系统中，承载着海量的数据信息。

然而，随着数据量的不断膨胀和网络环境的日益复杂，数据面临的威胁也日益增多。硬件故障、软件漏洞、人为错误、甚至是自然灾害，都可能瞬间导致数据丢失或损坏，给企业带来无法估量的损失。因此，如何确保MySQL数据库中数据的安全性、完整性和可恢复性，成为了每一个数据库管理员和IT运维人员必须面对的重要课题。

备份，作为数据保护的基本手段，其重要性不言而喻。通过定期或实时的数据备份，我们可以将数据存储在安全的位置，以便在遭遇数据丢失或损坏时能够迅速恢复，从而最大限度地减少业务中断和数据损失。而恢复，则是备份工作的延伸和最终目的，它要求我们在需要时能够高效、准确地从备份中恢复数据，确保业务能够迅速恢复正常。

本章将深入探讨MySQL数据库的备份与恢复技术，从基础概念到高级实践，全面解析不同类型的备份方法、备份工具的使用、备份策略的制定、以及数据恢复的流程和技巧。我们旨在通过丰富的实例和详细的步骤指导，帮助读者掌握MySQL数据库备份恢复的精髓，提升数据保护能力，确保数据安全无忧。

---

正文：

一.日志

1.binlog如何开启?

log_bin=/data/binlog/mysql-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;

2.二进制日志清理

2.1 自动

expire_logs_days=15

设置的依据: 至少1轮全备周期长度的过期时间.

2.2 手工

help purge

PURGE BINARY LOGS TO 'mysql-bin.000010';

PURGE BINARY LOGS BEFORE '2020-04-02 22:46:26';

reset master ;   #清空所有二进制日志，主从结构不建议执行，单服务器可以

3. 日志如何滚动

flush logs;

数据库重启

slow：

show variables like 'slow_query_log%';show variables like 'long%';show variables like '%using_indexes%';mysqldumpslow -s c -t 10 slow.logpt-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 backupcreate table t1 (id int);insert into t1 values(1),(2),(3);commit;

4.5.2 周二 23：00全备

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

4.5.3 模拟周二 23：00到周三 10点之间数据变化

use backupinsert 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 /backuplsfull_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.4 将临时库导出并恢复到生产

mysqldump   -S /data/3307/mysql.sock -B  backup  >/backup/bak.sql

mysql 

set sql_log_bin=0source /backup/bak.sql;set sql_log_bin=1

---

三.分库分表备份脚本：

vim for1.sh 

#!/bin/bashdb=$(mysql -uroot -p123 -e "show databases;"|sed 1d|grep -Ev ".*_schema|sys|mysql")for dbname in $dbdobackdir=/backup/mysql/$dbnameif [ ! -d $backdir ];thenmkdir -p $backdirfimysqldump -uroot -p123  -B $dbname > $backdir/${dbname}_db_$(date +%F).sqlecho "$dbname 数据库已经备份完成"table=$(mysql -uroot -p123 -e "use ${dbname};show tables;"|sed 1d)for tablename in $tabledomysqldump -uroot -p123 $dbname $tablename > $backdir/${dbname}_${tablename}_table_$(date +%F).sqlecho "$dbname 库的 $tablename 表 已经备份成功"done

---

扩展参数  ***

在构建主从时,使用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 -A  -R  --max_allowed_packet=128M --triggers --set-gtid-purged=OFF --master-data=2  --single-transaction|gzip > /backup/full_$(date +%F).sql.gz

物理备份-XBK

1.安装依赖包：

wget -O /etc/yum.repos.d/epel.repo http://mirrors.aliyun.com/repo/epel-7.repoyum -y install perl perl-devel perl-Digest libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev

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.rpmyum -y localinstall percona-xtrabackup-24-2.4.12-1.el7.x86_64.rpmyum -y localinstall percona-toolkit-3.3.0-1.el7.x86_64.rpm

3.innobackupex 使用

3.1 备份核心理念

  1. 针对非InnoDB,进行锁表备份,copy所有的非innoDB表文件

  2. 针对InnoDB表,立即触发CKPT,copy所有InnoDB表相关的文件(ibdata1,ibd,frm).

并且将备份过程中产生,新的数据变化的部分redo一起备份走

  3. 在恢复时,xbk会调用InnoDB引擎的CSR过程,将数据和redo的LSN追平,然后进行一致性恢复.

3.2备份过程

前提：起日志和gtid

(1) 全备

innobackupex  --user=root  -S /tmp/mysql.sock --no-timestamp /backup/full

(2) 利用全备进行恢复

pkill mysqldrm -rf /usr/local/mysql/data/*innobackupex --apply-log /backup/full/cp -a /backup/full/* /usr/local/mysql/datachown -R mysql.mysql /usr/local/mysql/*/etc/init.d/mysqld start

---

期待您的关注~