MySQL备份与恢复
一、数据备份的重要性
- 备份的主要目的是灾难恢复
- 在生产环境中,数据的安全性至关重要。
- 任何数据的丢失都可能产生严重的后果。
- 造成数据丢失的原因:
程序错误
人为操作错误
运算错误
磁盘故障
灾难(如火灾、地震)和盗窃
二、数据库备份的分类
2.1 从物理与逻辑的角度,备份可分为
- 物理备份:对数据库系统的物理文件(如数据文件、日志文件等)的备份
物理备份的方法:
1、冷备份(脱机备份):是在关闭数据库的时候进行的
2、热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件
3、温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
- 逻辑备份:对数据库逻辑组件的备份
2.2 从数据库的备份策略角度,备份可分为:
1、完全备份:每次对数据库进行完全的备份
2、差异备份:备份自从上次完全备份之后被修改过的文件
3、增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份
三、常见的备份方法
物理备份
- 备份时数据库处于关闭状态,直接打包数据库文件
- 备份速度快,恢复时也是最简单的
专用备份工具mysqldump或mysqlhotcopy
- mysqldump常见的逻辑备份工具
- mysqlhotcopy仅拥有MyISAM和ARCHIVE表
启用二进制日志进行增量备份
- 进行增量备份,需要刷新二进制日志
第三方工具备份
- 免费的MySQL热备份软件Percona XtraBackup
四、MySQL完全备份
- 是对整个数据库、数据库结构和文件结构的备份
- 保存的是备份完成时刻的数据库
- 是差异备份与增量备份的****基础
优点:备份与恢复操作简单方便
缺点:
1、数据存在大量的重复
2、占用大量的备份空间
3、备份与恢复时间长
4.1 数据库完全备份分类
- 物理冷备份与恢复
1、关闭MySQL数据库
2、使用tar命令直接打包数据库文件夹
3、直接替换现有的MySQL目录即可
4.2 物理冷备份:
systemctl stop mysqld #先关闭数据库,之后打包备份
mkdir /backup/ #创建备份目录
tar Jcvf /backup/mysql_all_$(date+%Y%m%d).tar.xz /usr/local/mysql/data/
完全恢复:
#主机A,使用scp命令将tar包传给另一台主机B
scp /backup/mysql_all_2022-06-05.tar.xz 192.168.44.30:/opt
##主机B的操作##
systemctl stop mysqld #关闭mysql
cd /opt/
mkdir /opt/bak/ #创建备份目录
tar Jxf mysql_all_2022-06-05.tar.xz -C /opt/bak/ #将tar包解压到备份目录
cd /opt/bak/ #切换到tar包的解压目录
\cp -af usr/local/mysql/data/ /usr/local/mysql #将data目录复制到/usr/local/mysql/目录下,覆盖原有文件
systemctl start mysqld #启动mysql
mysql -u root -p #登录数据库查看
show databases;
use yuji;
show tables;
select * from class;
4.3 逻辑备份
- mysqldump备份与恢复
4.3.1 mysqldump完全备份
1、完全备份一个、多个或所有完整的库(包括库中所有的表)
1、完全备份多个或一个库
mysqldump -uroot -p[密码] --databases 库名1 [库名2].. >/备份路径/备份文件名.sql#导出的就是数据库脚本文件
2、完全备份所有库
mysqldump -u root -p[密码] --all-databases > / 备份路径/备份文件名.sql
3、完全备份指定库中的部分表
mysqldump -uroot -p[密码] [-d] 库名 表名1 [表名2] ... > /备份路径/备份文件名.sql
#使用“-d"选项,说明只保存数据库的表结构
#不使用“-d”选项,说明表数据也进行备份
示例:
mysqldump -u root -p --databases ysj > /opt/mysql_bak/ysj.sql #完全备份一个库ysj
mysqldump -u root -p --databases ysj mysql > /opt/mysql_bak/ysj-yy.sql
#完全备份多个库,ysj库和mysql库
mysqldump -u root -p --all-databases > /opt/mysql_bak/all.sql #完全备份所有库
mysqldump -uroot -p ysj yy > /opt/mysql_bak/ysj_yy.sql #完全备份ysj库中的yy表
2、查看备份文件
备份文件中保存的是sql语句。即以sql语句的形式,把库、表结构、表数据保存下来。
cd /opt/mysql_bak
cat ysj.sql | grep -v "^--" | grep -v "^/" | grep -v "^$"
4.3.2 mysqldump完全恢复
- 先启动mysql
systemctl start mysqld
(1)恢复数据库
先删除数据库,之后进行恢复。
#删除数据库ysj
mysql -uroot -p -e 'drop database ysj;'
//“-e”选项,用于指定连接MySQL后执行的命令,命令执行后自动退出
mysql -uroot -p -e 'show databases;' #查看当前有哪些数据库
#恢复数据库ysj
mysql -uroot -p < /opt/mysql_bak/ysj.sql #重定向输入库文件
mysql -uroot -p -e 'show databases;' #查看当前有哪些数据库
(2)恢复数据表
当备份文件中只包含表的备份,而不包含创建的库的语句时,执行导入操作时必须指定库名,且目标库必须存在。
#备份ysj库中的yy表
mysqldump -uroot -p ysj yy > /opt/mysql_bak/ysj_yy.sql
#删除ysj库中的yy表
mysql -uroot -p -e 'drop table ysj.yy;'
#查看ysj库中的所有表
mysql -uroot -p -e 'show tabels from ysj;'
#恢复ysj库中的yy表
mysql -uroot -p ysj < /opt/mysql_bak/ysj_yy.sql
//重定向导入备份文件,必须指定库名
mysql -uroot -p -e 'show tables from ysj;'
使用mysqldump进行完全备份存在的问题
- 备份数据中有重复数据
- 备份时间与恢复时间长
五、MySQL增量备份与恢复
5.1 增量备份是什么
- 是自上次备份后增加的文件或者内容
增量备份的特点
- 没有重复数据,备份量不大,时间短
- 恢复需要上次完全备份及完全备份之后的增量备份才能恢复,而且要对所有增量备份进行逐个反推恢复
5.2 增量备份的方法
- MySQL没有提供直接的增量备份方法
- 可通过MySQL提供的二进制日志间接实现增量备份
- MySQL二进制日志对备份的意义
1、二进制日志保存了所有更新或者可能更新数据库的操作
2、二进制日志在启动MySQL服务器后开始记录,并在文件达到 max_binlog_size所设置的大小或者接收到flush logs命令后重新 创建新的日志文件
3、只需定时执行flush logs方法重新创建新的日志,生成二进制文 件序列,并及时把这些日志保存到安全的地方就完成了一个时间 段的增量备份
六、增量备份与恢复操作
6.1 增量备份
- 开启二进制日志功能
vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin //开启二进制日志,如果使用相对路径,则保存在/usr/local/mysql/data/目录下
binlog_format=MIXED //可选,指定二进制日志(binlog) 的记录格式为MIXED
server-id=1
systemctl restart mysqld
ls /usr/local/mysql/data
#二进制日志(binlog)有3种不同的记录格式:
STATEMENT (基于SQL语句)、ROW(基于行)、MIXED(混合模式),默认格式是STATEMENT
STATEMENT (基于SQL语句):记录修改的sql语句。高并发的情况下,记录操作的sql语句时可能顺序会有错误,导致恢复数据时,数据丢失或有误差。效率高,但数据可能有误差。
ROW(基于行):记录每一行数据,准确,但恢复时效率低。
MIXED(混合模式):正常情况下使用STATEMENT,高并发的情况下会智能地切换到ROW。
- 可每周对数据库或表进行完全备份
mkdir /bak
mysqldump -uroot -p ysj yy > /bak/ysj_yy_$(date +%F).sql
mysqldump -uroot -p --databases ysj > /bak/ysj_$(date +%F).sql
- 可每天进行增量备份,生成新的二进制日志文件
mysqladmin -uroot -p flush-logs
- 插入数据,模拟数据的变更
use ysj;
insert into yy values(3,'刻晴');
insert into yy values(4,'优菈');
- 再次生成新的二进制日志文件
mysqladmin -uroot -p flush-logs
#之前的步骤4的数据库操作会保存到mysql-bin.000002文件中,之后数据库数据再发生变化则保存在mysql-bin.00003文件中
- 查看二进制日志文件的内容
cp /usr/local/mysql/data/mysql-bin.000002 /bak/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /bak/mysql-bin.000002
#--base64-output=decode-rows:使用64位编码机制去解码并按行读取
#-v:显示详细内容
6.2 增量恢复
一般恢复
将所有备份的二进制日志内容全部恢复基于位置恢复
1、数据库在某一时间点可能既有错误操作也有正确的操作
2、可以基于精准的位置跳过错误操作基于时间恢复
跳过某个发生错误的时间点实现数据恢复
6.2.1 一般恢复
将所有备份的二进制日志内容全部恢复。
- 模拟丢失更改的数据的恢复步骤
use ysj;
#删除新增的两条数据
delete from yy where id=3;
delete from yy where id=4;
#增量恢复(今天新增加的两条数据记录保存在mysql-bin.000002日志中)
mysqlbinlog --no-defaluts /bak/mysql-bin.000002 | mysql -uroot -p
#查看表中数据
mysql -uroot -p -e 'select * from ysj.yy;'
- 模拟丢失表中所有数据的恢复步骤。
mysql -u root -p -e 'drop table ysj.yy;' #删除整个class表
mysql -u root -p -e 'show tables from ysj;' #查看yuji库中的表
#先完全恢复历史数据
mysql -u root -p yuji < /bak/ysj_yy_2022-10-30.sql
#再进行增量恢复今天新增的2条数据(mysql-bin.000002日志中保存了今天新增加的两条数据记录)
mysqlbinlog --no-defaults /bak/mysql-bin.000002 | mysql -u root -p
mysql -u root -p -e 'select * from ysj.yy;' #查看表中数据
6.2.2 断点恢复
向yy表中插入4条数据,之后刷新二进制日志,移动前一个日志:
use ysj;
insert into yy values(5,'书法');
insert into yy values(6,'音乐');
insert into yy values(7,'刺绣');
insert into yy values(8,'围棋');
mysqladmin -u root -p flush-logs #刷新日志,生成新的二进制日志
cp /usr/local/mysql/data/mysql-bin.000003 /bak/ #将前一个日志复制到/bak/目录下
mysqlbinlog --no-defaults --base64-output=decode-rows -v /bak/mysql-bin.000003 > /bak/binlog.txt #将二进制文件内容重定向到binlog.txt文件中
(1)基于位置的断点恢复
mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p
#从某一个位置点开始恢复,一直到日志结尾
mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p
#从日志开头,一直恢复到某一个位置点前结束
mysqlbinlog --no-defaults --start-position='xxx'--stop-position='位置点' 文件名 | mysql -u root -p
#从某一个位置点开始恢复,一直到某一个位置点前结束
例:
恢复最后两行,第7~8行
use ysj;
delete from yy where id between 5 and 8; #删除id为5到8的数据记录
cat /bak/binlog.txt #查看二进制文件内容,找到刺绣的位置点
#从刺绣的位置点3876一直恢复到结尾
mysqlbinlog --no-defaults --start-position='3774' /bak/mysql-bin.000003 | mysql -u root -p
mysql -u root -p -e 'select * from ysj.yy;' #查看表数据
例2:
只恢复第5行
cat /bak/binlog.txt #查看二进制文件内容,找到竹笛后面的位置点
#从头开始恢复,在"书法"的SQL语句后面截止
mysqlbinlog --no-defaults --stop-position='3496' /bak/mysql-bin.000003 | mysql -u root -p
mysql -u root -p -e 'select * from yuji.class;' #查看表数据
示例3:
只恢复第6~7行
use ysj;
delete from yy where id between 5 and 8; #删除cid为5到8的数据记录
cat /bak/binlog.txt #查看二进制文件内容,找到"音乐"和"刺绣"的位置点
#从“音乐”前面的位置点开始恢复,一直到"刺绣"后面的位置点
mysqlbinlog --no-defaults --start-position='3496' --stop-position='3975' /bak/mysql-bin.000003 | mysql -u root -p
mysql -u root -p -e 'select * from ysj.yy;' #查看表数据
(2)基于时间点的断点恢复
日期必须是"yyyy-mm-dd"的格式。
mysqlbinlog --no-defaults --start-datetime='时间点' 文件名 | mysql -u root -p
#从某一个时间点开始恢复,一直到日志结尾
mysqlbinlog --no-defaults --stop-datetime='时间点' 文件名 | mysql -u root -p
#从日志开头,一直恢复到某一个时间点前结束
mysqlbinlog --no-defaults --start-datetime='起始时间点' --stop-datetime'结束时间点' 文件名 | mysql -u root -p
#从某一个时间点开始恢复,一直到某一个位置点前结束
示例:
只恢复第6~7行
use ysj;
delete from yy where id between 5 and 8; #删除id为5到8的数据记录
cat /bak/binlog.txt #查看二进制文件内容,找到"音乐"和"刺绣"的时间点
#从“古琴”前面的时间点开始恢复,一直到"小号"后面的时间点
mysqlbinlog --no-defaults --start-datetime='2022-10-30 16:24:58' --stop-datetime='2022-10-30 16:25:10' /bak/mysql-bin.000003 | mysql -u root -p
mysql -u root -p -e 'select * from ysj.yy;' #查看表数据
七、使用脚本备份
-
使用脚本每周三进行一次完全备份,之后每天进行增量备份。
如果数据量很大,可以一周做2次完全备份,比如星期一和星期四各一次。
-
增量备份减少磁盘空间的占用,但恢复起来比较麻烦。需要一个一个恢复。
-
一般使用crontab在凌晨进行备份
7.1 获取最新的二进制文件名称
查看索引文件,可获取最新的二进制文件。
tail -1 /usr/local/mysql/data/mysql-bin.index
或者
sed -n '$p' /usr/local/mysql/data/mysql-bin.index
7.2 完全备份脚本(每周三完全备份一次):
vim /opt/mysqlquan.sh #编写脚本
#!/bin/bash
mysqldump -uroot -pabc123 --all-databases > /bak/all_$(date +%F).sql
chmod 700 /opt/mysqlquan.sh
crontab -e #定时任务执行脚本
00 1 * * 3 /opt/mysqlquan.sh #每周三凌晨1点备份一次
7.3 增量备份脚本(每天凌晨3点备份一次):
vim /opt/binlog.sh
#!/bin/bash
#使用sed命令打印二进制日志文件最后一行内容作为filename变量的值
filename=$(sed -n '$p' /usr/local/mysql/data/mysql-bin.index | awk -F '/' '{print $2}')
#将二进制日志移动到备份目录下,并将移动后的文件名称加上日期
mv /usr/local/mysql/data/$filename /bak/binlog_$(date +%F)
#生成新的二进制日志文件
mysqladmin -uroot -pabc123 flush-logs
chmod 700 /opt/binlog.sh
crontab -e
00 1 * * 3 /opt/mysqlquan.sh #完全备份,每周三凌晨1点备份一次
00 3 * * * /opt/binlog.sh #增量备份,每天凌晨3点备份一次