MySQL数据库的备份和恢复-CSDN博客

备份

完全备份和完全备份

完全备份

完全备份就是将整个数据库完整的进行备份

增量备份

增量备份就是在完全备份的基础之上对后续新增的内容进行备份

备份的需求

1、 在生产环境中数据的安全至关重要任何数据的丢失都可能产生严重的后果。

2、 数据为什么会丢失例如可能是程序操作、运算错误、磁盘故障、不可预测事件地震、海啸之类、人为操作等。

备份的方法

冷备份关机备份。停止MySQL的服务然后进行备份

热备份开机备份。无需关闭MySQL的服务进行备份

物理备份对数据系统的物理文件(数据文件、日志文件)进行备份

逻辑备份对组件进行备份(表结构)以sql语句的形式把库表结构表数据进行备份保存。直接在数据库中删除全部文件逻辑备份无法恢复

物理备份

物理备份完全备份对整个数据库进行完整的打包备份。

优点操作简单 缺点数据库文件占用量很大。占用空间太大备份和恢复的时间都很长而且需要站厅数据库服务。

如何进行物理备份

systemctl stop mysqld
#关闭MySQL1的服务器。因为关闭服务可以避免新的数据插入或者数据覆盖

cd usr/local/mysql/

cd data

cd kgc

安装软件包

tar Jcvf /opt/mysql_all_$(data +%F).tar.xz /usr/local/mysql/data/
#打包data的内容

ls

tar -Jxvf mysql_all_
#解压文件

cd usr/local/mysql/

rm -rf data
#删库跑路

cd /opt/

cd usr/local/mysql

cp -a data/ /usr/local/mysql
#保留权限复制

systemctl restart mysqld
#重启mysql服务

到另外一台库

cd /usr/local/mysql/

cp -a data/ /opt/
#MySQL2自己先做一个文件备份

scp -r root@20.0.0.60:/opt/usr/local/mysql/data /opt/
#远程复制到MySQL2

cp -a data/ /usr/local/mysql/data
#保留权限复制到MySQL2

systemctl restart mysqld
#重启服务登录一下

chown -R mysel.mysql kgc kgc1
#递归给两个库更改归属
#看到无法阅读就说明没有给权限的问题

到另外一台主机MySQL2

打包备份最好是把服务关闭避免有新的数据进入被覆盖。也可能会导致恢复失败。

基于热备份的逻辑备份

MySQL自带的工具 MySQLdump用于逻辑恢复库、表结构表数据。

在命令行操作
mysqldump -u root -p --databases 库名 > /opt/文件名.sql
#备份单个库
mysqldump -u root -p --databases 库1 库2 > /opt/文件名2.sql
#多个备份
mysqldump -u root -p --all-databases > /opt/文件名3.sql
#备份所有库

只是逻辑上的备份

systemctl restart mysqld

netstart -antp | grep 3306

mysqldump -u root -p --databases kgc > /opt/kgc.sql
#把kgc库备份到/opt、目录下

cd /usr/local/mysql/data
rm -rf kgc
#物理删库

mysql -u root -p < /opt/kgc.sql
#会直接报错库会回来但是kgc库里面的表结构消失了。只能使用逻辑删除在命令行删除

mysql -u root -p123456 -e 'show databases;'
#-e表示连接指定MySQL之后执行完命令后就自动退出
#进入MySQL执行一次show databases命令后就退出

mysql -u root -p -e 'drop database kgc;'
#需要手动数据密码

mysql -u root -p < /opt/kgc.sql
#手动输入密码

对多个库一次性备份
先备份多个库
mysqldump -u root -p --databases kgc kgc1 > /opt/kgc_all.sql
#手动输入密码。保存多个库

mysql -u root -p -e 'drop database kgc;'
mysql -u root -p -e 'drop database kgc1;'
#手动输入密码。模拟删除库

mysqldump -u root -p < /opt/kgc_all.sql
#手动输入密码。一次性恢复多个库

对全部库一次性备份
mysqldump -r root -p --all-databases > /opt/all_database.sql
#备份全部

备份单个库还原

备份多个库还原

mysql -u root -p123456 -e 'show databases;'
#-e表示连接指定MySQL之后执行完命令后就自动退出
​​​​​​​#进入MySQL执行一次show databases命令后就退出

如何恢复数据表

只恢复单个表或者多个表
准备另外一张表插入数据
恢复一张表
mysqldump -u root -p kgc info1 > opt/kgc_info1.sql

msql -u root -p -e 'drop table kgc.info1;'
#指定库删除表

mysql -u root -p kgc < /opt/kgc_info1.sql
#指定库名恢复
#刷新一下查看一下

恢复多个表
mysqldump -u root -p kgc info1 info2 > /opt/kgc_info1-2.sql

msql -u root -p -e 'drop table kgc.info1;'

msql -u root -p -e 'drop table kgc.info2;'

mysql -u root -p kgc < /opt/kgc_info1-2.sql
#指定库名恢复
#刷新一下查看一下

MySQL1全部数据库的逻辑备份文件恢复到MySQL2。

scp root@20.0.0.60:/opt/all_database.sql /opt/

mysql -u root -p < all_database.sql
#用sql语句的方式热备份直接转换。
#会覆盖掉MySQL2的全部数据

恢复单个表

恢复多个表

物理冷备份和热备份 特点简单 数据量占用的备份空间大 mysqldump这是MySQL自带的备份文件命令 特点方便简单。但是只能是基于逻辑上的表结构和表数据恢复。物理删除之后再用逻辑恢复会报错。也可也作为数据迁移。会占用大空间。比较物理备份相对来说占用的空间小得多。

百G之内使用逻辑备份。百G以上建议使用工具。

增量备份

增量备份 mysqldump支持增量备份 特点没有重复数据备份量小时间短。 MySQL增量备份恢复表数据期间表会锁定。可以查但是无法写入。 缺点备份时锁表必然会影响业务。超过10G耗时会比较长。导致服务不可用。

增量备份的过程

1、 MySQL提供的二进制日志来间接的实现增量备份 二进制文件怎么来 答修改配置文件

vim /etc/my.conf
#插入内容
log-bin=mysql-bin

binlog_format=MIXED
#表示混合模式
systemctl restart mysqld

MySQL二进制日志记录格式有三种

1、 STATEMNET基于sql语句。是记录修改的sql语句在高并发情况下记录sql语句时的顺序可能会出错。数据恢复可能会导致丢失或者误差。效率比较高但是不适合高并发场景。

2、 ROW基于行。会精准记录每一行的数据准确率高但是回复的时效率低。

3、 MIXED混合模式。既可以根据语句也可也根据行。在正常情况下使用STATEMNET一旦发生高并发会智能的切换到ROW模式虽然效率低但是一定不会出错。

表数据设置多一点

select * from info1;
#查看数据是否写入

cd /usr/local/mysql/data/
#会生成了两个文件
mysql -bin.index
mysql-bin.000001
表内写入信息后再查看日志文件 mysql-bin.000001
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000001
#查看新插入表的日志

mysqladmin -u root -p flush-logs
#刷新日志.
#此时data目录下会生成一个新的日志文件mysql-bin.000002

mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002
#刷新之后更新的内容会更新在2里面这就是断点

如何恢复
mysqlbinlog --no-defaults mysql-bin00000.1 | mysql -u root -p
#增量备份恢复之前表内插入的数据。这个叫断点恢复。


此时再对表插入信息。此时新插入的数据再000002里面。只要没有刷新日志就不会出现断点。会先插入再删除。
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

如果需要备份新的数据之前需要再刷新一次。
mysqladmin -u root -p flush-logs
#刷新日志.

重新在表内插入数据
此时断点之后数据都在新生成的000003里面
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002

mysqladmin -u root -p flush-logs
#刷新日志断点

再删除表内数据这时候删除的操作会保存到000004里面

mysqlbinlog --no-defaults --base64-out=decode-rows -v mysql-bin.000003

位置恢复

三种方式

1、 从某一点开始恢复到最后

2、 从开头一直恢复到某个位置

3、 从指定点开始恢复到指定的结束点

删除表内数据测试
基于位置点进行恢复
1、 从某一点开始恢复到最后
格式
mysqlbinlog --no-defaults --start-position='位置点' 文件名 | mysql -u root -p

2、 从开头一直恢复到某个位置
格式
mysqlbinlog --no-defaults --stop-position='位置点' 文件名 | mysql -u root -p

3、 从指定点开始恢复到指定的结束点
格式
mysqlbinlog --no-defaults --start-position='位置点' --stop-position='位置点' 文件名 | mysql -u root -p

mysqladmin -u root -p flush-logs
#刷新日志断点

删除表内数据此时数据再000005上

mysqlbinlog --no-defaults --start-position='6171' mysql-bin.000004 | mysql -u root -p

查看位置点
#at后面的数字就是位置点。要选择commit后面的位置点
mysqlbinlog --no-defaults --base64-out=decode-rows -v mysql-bin.000004
#查看000004日志内的位置点

从开头一直恢复到某个位置
mysqlbinlog --no-defaults --stop-position='6106' mysql-bin.000004 | mysql -u root -p
#从头恢复到指定点

范围恢复
mysqlbinlog --no-defaults --start-position='6106' --stop-position='6748' mysql-bin.000004 | mysql -u root -p
#指定位置到指定位置恢复

时间恢复

基于时间点进行恢复

1、 从某个时间点开始

2、 从开头到指定结尾时间点

3、 指定时间范围

基于时间点恢复
YYY-MM-DD 11:11:11 时间格式要按照标准格式来写
1、 从某个时间点开始
格式mysqlbinlog --no-defaults --start-datettime='时间点' 文件名 | mysql -u root -p

2、 从开头到指定结尾时间点
格式mysqlbinlog --no-defaults --stop-datettime='时间点' 文件名 | mysql -u root -p

3、 指定时间范围
格式mysqlbinlog --no-defaults --start-datettime='时间点' --stop-datettime='时间点' 文件名 | mysql -u root -p

从某个时间点开始
mysqlbinlog --no-defaults --start-datetime='2023-11-06 11:44:32' mysql-bin.000004 | mysql -u root -p
#从某个时间点开始

从开头到指定结尾时间点
mysqlbinlog --no-defaults --stop-datetime='2023-11-06 11:44:32' mysql-bin.000004 | mysql -u root -p
#从头开始到某个时间结束

指定时间范围
mysqlbinlog --no-defaults --start-datetime='2023-11-06 11:44:37' --stop-datetime='2023-11-06 11:49:05' mysql-bin.000004 | mysql -u root -p
#从某个时间开始到某个时间结束

日志单独存放的规则

单独存放错误日志
vim /etc/my.conf
log-error=/usr/local/mysql/data/mysql_error.log
#单独存放错误日志

单独存放通用访问日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log
#单独存放通用访问日志

开启慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_show_query.log
long_query_time=5
#设置超时时间5秒
#开启慢查询日志.所有超过5秒的都会被记录在内

systemctl restart mysqld

查询通用日志开始情况

show variables like 'general%';
#查询是否开启通用日志访问记录

show variables like 'log_bin%';
#查询二进制日志是否开启

show variables like 'slow%';
#查询是是否开启慢查询访问日志

总结

在生产中通过binlog进行增量恢复是非常好用的方法。

我们只需要对binlog文件进行备份随时可以进行备份和恢复。只能是root用户来进行操作

除非有特殊需要一般不打额外断点具体根据需求来一般是通过脚本按月来设置断点。