目录
1.3.2 专用备份工具mydump或mysqlhotcopy
一、 MySQL的备份
1.1 数据备份的重要性
- 在生产环境中,数据的安全性至关重要
- 任何数据的丢失都可能产生严重的后果
- 造成数据丢失的原因:程序错误、人为操作错误、运算错误、磁盘故障、灾难(如火灾、地震)和盗窃
1.2 数据库备份的分类
从物理与逻辑的角度,备份可分为以下两种。
1.2.1 物理备份
对数据库操作系统的物理文件(如数据文件日志文件等)的备份。
- 物理备份方法
- 冷备份(脱机备份):是在关闭数据库的时候进行的
- 热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件
- 温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
1.2.2 逻辑备份
对数据库逻辑组件(如:表等数据库对象)的备份。
从数据库的备份策略角度,备份可分为以下三种。
1.2.3 完全备份
每次对数据库进行完整的备份。会导致备份文件占用巨大的空间,并且有大量的重复数据。恢复时,直接把文件导入进去即可。
1.2.4 差异备份
备份自从上次完全备份之后被修改过的文件,可能会出现备份重复数据,导致占用大量额外的磁盘空间。恢复时,先恢复完全备份,再恢复导入差异备份的数据。
1.2.5 增量备份
只有在上次完全备份或者增量备份后被修改的文件才会被备份。不会出现重复数据,也不会占用额外的磁盘空间。恢复时,需要完全恢复,再增量恢复。
1.3 常见的备份方法
1.3.1 物理冷备
备份时数据库处于关闭状态,直接打包数据库文件
备份速度快,恢复时也是最简单的
1.3.2 专用备份工具mydump或mysqlhotcopy
mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
1.3.3 启用二进制日志进行增量备份
进行增量备份,需要刷新二进制日志
1.3.4 第三方工具备份
免费的MySQL热备份软件Percona XtraBackup
二、MySQL的恢复
2.1 MySQL完全备份与恢复
2.1.1 物理冷备份与恢复
1、关闭数据库,下载xz软件包
systemctl stop mysqld
yum install -y xz
2、压缩备份
tar Jcvf /opt/mysql_all_$(date +F).tar.xz /usr/local/mysql/data/
3、 解压恢复
cd /opt
mv /usr/local/mysql/data/* ./
ls
2.1.2 mysqldump备份与恢复(温备份)
1、先创建好表和数据
create database kgc;
show databases;
use kgc
create table student(id int(5),name char(4),age varchar(2),xingbie char(1),jinshi char(1));
insert into student values(1,'陶夏宇','22','男','否');
insert into student values(2,'史彬','22','女','是');
show tables;
select * from student;
2、完全备份一个或多个完整的库(包括其中所有的表)
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql #导出的就是数据库脚本文件
mysqldump -uroot -p123456 --databases kgc > /opt/kgc.sql
3、完全备份MySQL服务器中所有的库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
mysqldump -u root -p123456 --all-databases > /opt/all.sql
4、查看备份文件
grep -v "^--" /opt/kgc.sql | grep -v "^/" | grep -v "^$"
2.1.3 MySQL完全恢复
1、先删除kgc的库作为测试恢复库的实验
2、恢复kgc的库
mysql -uroot -p123456 < /opt/all.sql
3、恢复数据表,先对表进行备份及验证
mysqldump -uroot -p123456 kgc student > /opt/student.sql
grep -v "^--" /opt/student.sql | grep -v "^/" | grep -v "^$"
4、开始用source的方法恢复数据表
source /opt/student.sql
5、用第二种方式恢复数据表
mysql -uroot -p123456 kgc < /opt/student.sql
恢复成功!
2.2 MySQL增量备份与恢复
2.2.1 一般恢复
将所有备份的二进制日志内容全部恢复。
1、开启二进制日志功能
vim /etc/my.cnf
systemctl restart mysqld
#二进制日志(binlog)有3种不同的记录格式: STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式),默认格式是STATEMENT
- STATEMENT(基于sQL语句):
每一条涉及到被修改的sql都会记录在binlog中
缺点:日志量过大,如sleep()函数,last_insert_id()>,以及user-definedfuctions ( udf) 、垂从复制等架构记录日志时会出现问题
总结:增删改查通过sql语句来实现记录,如果用高并发可能会出错,可能时间差异或者延迟,可能不是我们想想的恢复可能你先删除或者在修改,可能会倒过来。准确率低
- ROW(基于行)
只记录变动的记录,不记录sql的上下文环境
缺点:如果遇到update… …set… . .where true那么binlog的数据量会越来越大
总结: update、 delete以多行数据起作用,来用行记录下来,只记录变动的记录,不记录sql的上下文环境,比如sql语句记录一行,但是Row就可能记录10行,但是准确性高,高并发的时候由于操作量能变低比较大所以记录都记下来。
2.2.2 基于位置恢复
数据库在某一时间点可能既有错误的操作也有正确的操作,可以基于精准的位置跳过错误的操作。发生错误节点之前的一个节点,上一次正确操作的位置点停止。
二进制日志中需要关注的部分
1、at :开始的位置点
2、end_log_pos:结束的位置
3、时间戳: 240325 18:50:30
4、SQL语句
5、查看日志文件命令:
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000004
开始实验:
cp /usr/local/mysql/data/mysql-bin.000004 /opt/
mysqlbinlog --no-defaults --start-position='790099' --stop-position='790807' /opt/mysql-bin.000004 | mysql -uroot -p
实验成功!
2.2.3基于时间点恢复
跳过某个发生错误的时间点实现数据恢复,在错误时间点停止,在下一个正确时间点开始。
1、先检查表中是否存在数据
2、mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日小时:分钟:秒' 二进制日志 | mysql -u 用户名 -p 密码
mysqlbinlog --no-defaults --start-datetime='2024-03-25 19:38:40' --stop-datetime='2024-03-25 19:40:15' /opt/mysql-bin.000004 | mysql -uroot -p
3、验证结果
实验成功!