Mysql数据库日志和数据的备份恢复

一、数据库备份的重要性

        备份的主要目的是灾难恢复，在生产环境中，数据的安全性至关重要，任何数据的丢失都可能产生严重的后果。

        造成数据丢失的原因主要有：程序错误、人为操作错误、运算错误、磁盘故障、灾难(如火灾、地震)和盗窃。

二、数据库备份的分类

从物理与逻辑的角度，备份可分为

1.物理备份：对数据库操作系统的物理文件（如数据文件、日志文件等)的备份。

• 冷备份（脱机备份)︰是在关闭数据库的时候进行的；
• 热备份（联机备份)︰数据库处于运行状态，依赖于数据库的日志文件；
• 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作。

2.逻辑备份：对数据库逻辑组件（如表等数据库对象）的备份，导出sql文件。

从数据库的备份策略角度，备份可分为

3.完全备份：每次对数据库进行完整的备份。

4.差异备份：备份自从上次完全备份之后被修改过的文件。

5.增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份。

推荐使用 完全备份+增量备份 的方式

三、常见的备份方法

1.物理冷备

备份时数据库处于关闭状态，直接打包数据库文件，备份速度快，恢复时也是最简单的。

2.专用备份工具

• mysqldump常用的逻辑备份工具；
• mysqlhotcopy。（仅拥有备份MyISAM和ARCHIVE表）

3.启用二进制日志进行增量备份

进行增量备份，需要刷新二进制日志。（本文会介绍）

4.第三方工具备份

免费的MySQL热备份软件：Percona、XtraBackup等。

四、完全备份

1.简介

是对整个数据库、数据库结构和文件结构的备份；

保存的是备份完成时刻的数据库；

是差异备份与增量备份的基础。

2.优缺点

优点

• 备份与恢复操作简单方便。

缺点

• 数据存在大量的重复；
• 占用大量的备份空间；
• 备份与恢复时间长。

五、完全备份与恢复

1.物理冷备份与恢复

关闭mysql数据库，使用tar命令直接打包数据库数据文件夹，实现备份

#关闭mysql服务
systemctl stop mysqld.service

#创建备份目录
mkdir /sqlbak

#压缩打包数据文件夹，并以归档日期命名
tar zcf /sqlbak/mysql_bak_$(date +%F).tar.gz /usr/local/mysql/data/

替换数据文件夹实现恢复

#如果有需要，可以将现在的数据库文件迁移到一个目录中
mkdir /sqldata
mv /usr/local/mysql/data/ /sqldata

#将备份压缩包解压到一个目录下，并将其迁移到mysql的数据目录下
mkdir /sqlrestore
tar zxf /sqlbak/mysql_bak_2022-10-26.tar.gz -C /sqlrestore/

mv /sqlrestore/usr/local/mysql/data/ /usr/local/mysql/

#启动mysql服务
systemctl start mysqld.service

2.mysqldump备份与恢复

单库备份

mysqldump -u用户名 -p密码 [选项] 数据库名 > /备份路径/备份文件名

                                               --databases        可以保存创建库名

多库备份

mysqldump -u用户名 -p密码 [选项] --databases 库名1 库名2 ... > /备份路径/备份文件名

所有库备份

 mysqldump -u用户名 -p密码 [选项] --all-databases > /备份路径/备份文件名

表备份

mysqldump -u用户名 -p密码 [选项] 数据库名 表名 > /备份路径/备份文件名 

                                                -d        只保存表结构

恢复库

mysqldump -u用户名 -p密码 < /备份路径/库备份文件

恢复表

 mysqldump -u用户名 -p密码 库名 < /备份路径/表备份文件

六、mysql日志管理

修改/etc/my.cnf，添加错误日志、通用查询日志、慢查询日志和二进制日志

错误日志：用来记录当MysQL启动、停止或运行时发生的错误信息，默认已开启；

通用查询日志：用来记录MySQL的所有连接和语句，默认是关闭的；

慢查询日志：用来记录所有执行时间超过long query time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的；

二进制日志：用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认已开启。

vim /etc/my.cnf

[mysqld]
...

#错误日志
log-error=/usr/local/mysql/data/mysql_error.log

#通用查询日志
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

#二进制日志
log_bin=mysql-bin
binlog_format=MIXED

#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=2

二进制日志格式有三种：binlog_format=STATEMENT  或  ROW 或  MIXED          

STATEMENT：基于SQL语句记录二进制日志，写入和恢复速度较快，在高并发的情况可能会出SQL语句顺序记录偏差导致恢复时数据丢失。（老版本的默认模式）

 

ROW：基于行记录二进制日志，写入和恢复速度较STATEMENT慢些，日志文件占用空间也较大，但是准确性较高。（5.7的默认模式）

 

MIXED：混合模式，平时负载压力较小时使用SQL语句记录二进制日志，在高并发的情况会切换成基于行记录二进制日志。

重启服务后，日志文件就生成了 

查看日志开启状态 

七、增量备份与恢复

1.备份和查看

（1）当我们设置了二进制日志，之后在数据库中的操作会保存在最新的二进制日志中（即文件后缀序号最大的），也可以在mysql-bin.index文件中查看

（2）使用以下命令刷新新的日志文件，会生成新的二进制日志，每次生成，后缀的序号会+1。 

mysqladmin -u用户 -p密码 flush-logs        

（3）将旧的二进制日志（当前最新的 -1）迁移出来（可以加上日期便于识别）

（4）由于是二进制文件，需要转码才能查看

mysqlbinlog --no-defaults --base64-output=decode-rows -v 二进制日志文件

（5）也可以将输出的内容重定向输入到文本文件中便于查看和编辑

2.恢复

（1）将二进制日志中的所有数据恢复

mysqlbinlog --no-defaults 二进制日志文件 | mysql -u用户名 -p密码

（2）断点恢复（将二进制日志中的指定数据恢复） 

基于位置点

从位置点恢复到结尾

mysqlbinlog --no-defaults --start-position='位置点' 二进制日志文件 | mysql -u用户名 -p密码

从位置点恢复到位置点

mysqlbinlog --no-defaults --start-position='位置点1' --stop-position='位置点2' 二进制日志文件 | mysql -u用户名 -p密码

基于时间点

时间点格式：'YYYY-mm-dd HH:MM:SS'        如：'2022-10-27 12:56:27'

从时间点恢复到结尾

mysqlbinlog --no-defaults --start-datetime='时间点1' 二进制日志文件 | mysql -u用户名 -p密码

 从时间点恢复到时间点

mysqlbinlog --no-defaults --start-datetime='时间点1' --stop-datetime='时间点2' 二进制日志文件 | mysql -u用户名 -p密码

八、备份加入计划任务

通常情况需要执行一次完全备份后，再定期执行增量备份。

需求：现需要实现今晚12:00做完全备份，之后每天执行增量备份

1.编写完全备份脚本和增量备份脚本

#完全备份脚本
vim generalbak.sh
#!/bin/bash
mysqldump -uroot -p123123 --all-databases > /sqlbinlog/mysql_general_bak.sql

#增量备份脚本
vim addbak.sh
#!/bin/bash
mysqladmin -uroot -p123123 flush-logs
filename=$(tail -2 /usr/local/mysql/data/mysql-bin.index | head -1 |awk -F'/' '{print $2}')
mv /usr/local/mysql/data/$filename /sqlbinlog/mysql-bin-$(date +%Y%m%d)

2.加入计划任务

crontab -e
0 0 28 10 * /sqlbinlog/generalbak.sh
0 0 * * * /sqlbinlog/addbak.sh

到此这篇关于Mysql数据库日志和数据的备份恢复的文章就介绍到这了,更多相关内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家！

原文地址：https://blog.csdn.net/weixin_58544496/article/details/127512732