MySQL数据库：掌握备份与恢复的艺术，确保数据安全无忧
在数字化时代，数据已成为企业最宝贵的资产之一。无论是初创公司还是行业巨头，数据的完整性和可用性都是其业务运营和决策制定的基石。MySQL，作为世界上最流行的开源关系型数据库管理系统之一，广泛应用于各类网站、应用及企业系统中，承载着海量的数据信息。然而，随着数据量的不断膨胀和网络环境的日益复杂，数据面临的威胁也日益增多。硬件故障、软件漏洞、人为错误、甚至是自然灾害，都可能瞬间导致数据丢失或损坏，给企
- 作者简介：我是团团儿，是一名专注于云计算领域的专业创作者，感谢大家的关注
- 座右铭： 云端筑梦，数据为翼，探索无限可能，引领云计算新纪元
- 个人主页：团儿.-CSDN博客
目录
前言：
在数字化时代，数据已成为企业最宝贵的资产之一。无论是初创公司还是行业巨头，数据的完整性和可用性都是其业务运营和决策制定的基石。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.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.sql3.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 -A -R --triggers --set-gtid-purged=OFF --master-data=2 --single-transaction > /backup/full_$(date +%F).sql4.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 mysqld33074.6.2 准备备份
（1）准备全备：
cd /backup
ls
full_2021-05-31.sql（2）截取二进制日志
mysqlshow binlog events in 'mysql-bin.000001';mysqlbinlog --skip-gtids --include-gtids='820f8917-d358-11ec-b243-000c29cbdce4:4-6' mysql-bin.000001 > /tmp/a.sql4.6.4 将临时库导出并恢复到生产
mysqldump -S /data/3307/mysql.sock -B backup >/backup/bak.sqlmysql set sql_log_bin=0
source /backup/bak.sql;
set sql_log_bin=1三.分库分表备份脚本：
vim for1.sh #!/bin/bash
db=$(mysql -uroot -p123 -e "show databases;"|sed 1d|grep -Ev ".*_schema|sys|mysql")
for dbname in $db
do
backdir=/backup/mysql/$dbname
if [ ! -d $backdir ];then
mkdir -p $backdir
fi
mysqldump -uroot -p123 -B $dbname > $backdir/${dbname}_db_$(date +%F).sql
echo "$dbname 数据库已经备份完成"
table=$(mysql -uroot -p123 -e "use ${dbname};show tables;"|sed 1d)
for tablename in $table
do
mysqldump -uroot -p123 $dbname $tablename > $backdir/${dbname}_${tablename}_table_$(date +%F).sql
echo "$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.repo
yum -y install perl perl-devel perl-Digest libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev2.下载软件并安装
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
yum -y localinstall percona-toolkit-3.3.0-1.el7.x86_64.rpm3.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 mysqld
rm -rf /usr/local/mysql/data/*
innobackupex --apply-log /backup/full/
cp -a /backup/full/* /usr/local/mysql/data
chown -R mysql.mysql /usr/local/mysql/*
/etc/init.d/mysqld start期待您的关注~
开放原子开发者工作坊旨在鼓励更多人参与开源活动，与志同道合的开发者们相互交流开发经验、分享开发心得、获取前沿技术趋势。工作坊有多种形式的开发者活动，如meetup、训练营等，主打技术交流，干货满满，真诚地邀请各位开发者共同参与！
更多推荐
27
0- 0
已为社区贡献2条内容
所有评论(0)