MySQL数据库的日志管理以及备份和恢复

目录

1、日志管理

2、查询日志 

3、数据备份的重要性 

4、数据库备份的分类 

4.1物理备份

4.2逻辑备份：

4.3完全备份

5、常见的备份方法 

6、MySQL完全备份 

6.1MySQL完全备份优缺点

6.2数据库完全备份分类  

6.2.1物理冷备份与恢复

6.2.2mysqldump备份与恢复

 7、完全备份

7.1MySQL物理冷备份及恢复

7.2逻辑备份mysqldump备份与恢复

完全备份一个或多个完的库(包括其中所有的表)

 完全备份指定库中的部分表

完全备份MySQL,服务器中所有的库

查看备份文件 

8、MySQL完全恢复

8.1恢复数据库

8.2恢复数据表 

​编辑 9、MySQL增量备份

10、MySQL增量备份与恢复试验

MySQL增量备份

开启二进制日志功能

 可每周对数据库或表进行完全备份

可每天进行增量备份操作,生成新的二进制日志文件 

再次生成新的二进制日志文件 

查看二进制日志文件的内容 

MySQL 增量恢复 

一般恢复

基于位置恢复

基于时间点恢复

1、日志管理

MySQL的日志默认保存位置为/usr/local/mysql/data

vim /etc/my.cnf
------------------------------------------
[mysqld]  ​
#错误日志，用来记录当MySQL启动、停止或运行时发生的错误信息，默认已开启
log-error=/usr/local/mysql/data/mysql_error.log #指定日志的保存位置和文件名​
 
#通用查询日志，用来记录MySQL的所有连接和语句，默认是关闭的
general_log=ON #开启通用查询 日志
general_log_file=/usr/local/mysql/data/mysql_general.log
 
#二进制日志（binlog），用来记录所有更新了数据或者已经潜在更新了数据的语句，记录了数据的更改，可用于数据恢复，默认己开启
log-bin=mysql-bin            #也可以log_bin=mysql-bin
 
#慢查询日志，用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=2     #设置超过2秒执行的语句被记录，缺省时为10秒
-------------------------------------------------    ​
systemctl restart mysqld

2、查询日志 

#登录进入数据库
mysql -u root -p123123
 
#查看通用查询日志是否开启
show variables like 'general%';      
 
#查看二进制日志是否开启
show variables like 'log_bin%';  
 
#查看慢查询日志功能是否开启
show variables like '%slow%';  
 
#查看慢查询时间设置
show variables like 'long_query_time'; 
 
#在数据库中设置关闭慢查询的方法
set global slow_query_log=OFF;

 

 

3、数据备份的重要性 

备份的主要目的是灾难恢复

在生产环境中，数据的安全性至关重要

任何数据的丢失都可能产生严重的后果

造成数据丢失的原因

  • 程序错误
  • 人为操作错误
  • 运算错误
  • 磁盘故障
  • 灾难（如火灾、地震）和盗窃

4、数据库备份的分类 

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

从数据库的备份策略角度，备份可分为完全备份、差异备份、 增量备份

4.1物理备份

对数据库操作系统的物理文件（如数据文件、 日志文件等）的备份

物理备份方法

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

4.2逻辑备份：

对数据库逻辑组件（如：表等数据库对象）的备份 

4.3完全备份

  • 完全备份：每次对数据库进行完整的备份
  • 差异备份：备份自从上次完全备份之后被修改过的文件
  • 增量备份：只有在上次完全备份或者增量备份后被修改的文件才会被备份

5、常见的备份方法 

物理冷备

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

专用备份工具mydump或mysqlhotcopy

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

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

  • 进行增量备份，需要刷新二进制日志

第三方工具备份

  • 免费的MySQL热备份软件Percona XtraBackup

6、MySQL完全备份 

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

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

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

6.1MySQL完全备份优缺点

优点

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

缺点

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

6.2数据库完全备份分类  

6.2.1物理冷备份与恢复

  • 关闭MySQL数据库
  • 使用tar命令直接打包数据库文件夹
  • 直接替换现有MySQL目录即可

6.2.2mysqldump备份与恢复

  • MySQL自带的备份工具，可方便实现对MySQL的备份
  • 可以将指定的库、表导出为SQL脚本
  • 使用命令mysql导入备份的数据

 7、完全备份

7.1MySQL物理冷备份及恢复

从192.168.92.7备份到192.168.92.6

配置192.168.92.7

先查看192.168.92.7

关闭数据库并打包数据库文件

systemctl stop mysqld           #关闭mysql服务
mkdir /backup                   #创建备份目录
rpm -q xz                       #使用xz工具进行压缩，查看本系统中是否安装xz工具，如果没安装用yum安装  yum install xz -y 
tar Jcf /backup/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data  #打包数据库文件并将数据库文件存放到/usr/local/mysql/data 
​​cd /backup/                      #切换到备份目录 
ls                              #查看目录内容
tar tf mysql_all_2024-03-26.tar.xz    #查看tar包内的文件

 

 

恢复数据库

scp /backup/mysql_all_2024-03-26.tar.xz 192.168.92.6:/root
#将本机数据库文件远程传输到192.168.92.6主机的root目录下

配置192.168.92.6

 

将data复制到/usr/local/mysql目录下

7.2逻辑备份mysqldump备份与恢复

完全备份一个或多个完的库(包括其中所有的表)

mysqldump -u root -p[密码]--databases库名1[库名2]..>/备份路径/备份文件名.sql #导出的就是数据库脚本文件
例:
mysqldump -u root -p123123 --databases my > /opt/backup/my.sql
mysqldump -u root -p123123 --databases my qyd > /opt/backup/my_qyd.sql

 完全备份指定库中的部分表

mysqldump -u root -p[密码] [-d] 库名 表名1 [表名2] ... > /备份路径/备份文件名.sql
#使用-d"选项,说明只保存数据库的表结构 ，不使用"-d"选项,说明表数据也进行备份
例:
mysqldump -u root -p123123 my account t1 > /opt/backup/account_t1.sql

完全备份MySQL,服务器中所有的库

mysqldump -u root -p[密码] --all-databases > / 备份路径/备份文件名.sql
例： ​
mysqldump -u root -p --all-databases > /opt/backup/all.sql   #完全备份所有的库

查看备份文件 

cat 备份文件名 | grep -v "^--" | grep -v "^/" | grep -v "^$"
例：
cat /opt/backup/account_t1.sql | grep -v "^--" | grep -v "^/" | grep -v "^$"

8、MySQL完全恢复

8.1恢复数据库

systemctl start mysqld #开启mysql服务
 
#删除已备份数据库
mysql -u root -p[密码] -e 'drop database 数据库;'
#-e"选项,用于指定连接MySQL后执行的命令,命令执行完后自动退出
#查看所有的库
mysql -u root -p[密码] -e 'SHOW DATABASES;'
#将备份的数据库重定向输入到数据库中
mysql -u root -p[密码] < /备份路径/备份文件名
#查看系统中的库
mysql -u root -p[密码] -e 'SHOW DATABASES;'
#查看库中的表
mysql -u root -p[密码] -e ‘select * from 库名.表名;'

 恢复数据库

 

8.2恢复数据表 

当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。

mysqldump -u root -p123123 my t1 > /opt/backup/t1.sql #备份my库中的表t1
mysql -u root -p123123 -e 'drop table my.t1;'         #删除my库中的已备份的表t1
mysql -u root -p123123 qyd < /opt/backup/t1.sql       #恢复表,讲备份的表重定向输入到qyd库中
mysql -u root -p123123 -e 'show tables from qyd;'     #查看所有qyd库中的表
mysql -u root -p123123 -e 'select * from qyd.t1;'     #查看t1表数据记录

 9、MySQL增量备份

使用mysqldump进行完全备份存在的问题

  • 备份数据中有重复数据
  • 备份时间与恢复时间过长

增量备份是自上一次备份后增加/变化的文件或者内容

特点

  • 没有重复数据，备份量不大，时间短
  • 恢复需要上次完全备份及完全备份之后所有的增量备份才 能恢复，而且要对所有增量备份进行逐个反推恢复

MySQL没有提供直接的增量备份方法 可通过MySQL提供的二进制日志间接实现增量备份

MySQL二进制日志对备份的意义

  • 二进制日志保存了所有更新或者可能更新数据库的操作
  • 二进制日志在启动MySQL服务器后开始记录，并在文件达到 max_binlog_size所设置的大小或者接收到flush logs命令后重新 创建新的日志文件
  • 只需定时执行flush logs方法重新创建新的日志，生成二进制文件序列，并及时把这些日志保存到安全的地方就完成了一个时间 段的增量备份

10、MySQL增量备份与恢复试验

MySQL增量备份

开启二进制日志功能

vim /etc/my.cnf
------------------------------------
[mysqld]
log-bin=mysql-bin
binlog_format = MIXED    #可选,指定二进制日志(binlog)的记录格式为MIXED
server-id =1
systemctl start mysqld
cd /usr/local/mysql/data
ls

 

二进制日志(binlog)有3种不同的记录格式:

  • STATEMENT(基于SQL语句)
  • Row(基于行)
  • MIXED(混合模式),默认格式是STATEMENT

 

 

 可每周对数据库或表进行完全备份

mysqldump -u root -p123123 my t1 >/opt/my_t1_$(date +%F).sql   #备份my库里的t1表
mysqldump -u root -p123123 --databases my > /opt/my_$(date +%F).sql   #备份my库

可每天进行增量备份操作,生成新的二进制日志文件 

mysqladmin -uroot -p flush-logs

再次生成新的二进制日志文件 

mysqladmin -u root -p123123 flush-logs
#之前的步骤4的数据库操作会保存到mysql-bin.000004文件中，之后数据库数据再发生变化则保存在mysql-bin.00005文件中

查看二进制日志文件的内容 

cp /usr/local/mysql/data/mysql-bin.000004 /opt/
mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000004
#--base64-output=decode-rows:使用64位编码机制去解码并按行读取
#-v:显示详细内容

MySQL 增量恢复 

一般恢复

将所有备份的二进制日志内容全部恢复

use my;
delete from t1 where id=7;
delete from t1 where id=8;
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000004 | mysql -u root -p123123
#通过二进制日志进行恢复

基于位置恢复

数据库在某一时间点可能既有错误的操作也有正确的操作 可以基于精准的位置跳过错误的操作

在数据库中插入数据并查看

insert into t1 values(7,'1212',7); #插入语句
insert into t1 values(8,'1313',8); #插入语句
mysqladmin -u root -p123123 flush-logs #刷新日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql-bin.000006  #查看日志文件内容（新生成日志的前一个日志）

基于时间点恢复

跳过某个发生错误的时间点实现数据恢复

基于时间恢复

mysqlbinlog --no-defaults --start-datetime='2022-06-10 17:10:58' /usr/local/mysql/data/mysql-bin.000006 |mysql -uroot -p123123

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/490230.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

什么是回归测试？

什么是回归测试？

今天看看回归测试的基本概念。 什么是回归测试? 回归测试被定义为一种软件测试&#xff0c;以确认最近的程序或代码更改没有对现有功能产生不利影响。回归测试只是对已经执行的测试用例的全部或部分选择&#xff0c;重新执行这些用例以确保现有功能正常工作。 进行此测试是…
阅读更多...
MYSQL高级语句(一)

MYSQL高级语句(一)

目录 一、常用查询 1、order by 按关键字排序 1.升序排序 2.降序排序 3.结合where进行条件过滤再排序 4.多字段排序 2、区间判断及查询不重复记录 1. and / or 且与或的使用 2.嵌套、多条件使用 3.distinct 查询不重复记录 3、GROUP BY 对结果进行分组 4、Li…
阅读更多...
就业班 第二阶段 2401--3.25 day5 mycat读写分离

就业班 第二阶段 2401--3.25 day5 mycat读写分离

[TOC] 启动并更改临时密码 [rootmysql1~]# systemctl start mysqld && passwdgrep password /var/log/mysqld.log | awk END{ print $NF} && mysqladmin -p"$passwd" password Qwer123..; MyCAT读写分离 Mycat 是一个开源的数据库系统&#xff0c;但…
阅读更多...
【Node.js】WebSockets

【Node.js】WebSockets

概述 WebSockets是一种在浏览器和服务器之间建立持久连接的协议&#xff0c;它允许服务器主动推送数据给客户端&#xff0c;并且在客户端和服务器之间实现双向通信。 建立连接&#xff1a;客户端通过在JavaScript代码中使用WebSocket对象来建立WebSockets连接。例如&#xff1…
阅读更多...
安达发|电子产品制造企业APS生产排程软件

安达发|电子产品制造企业APS生产排程软件

在电子脉动的世界中&#xff0c;时间是芯片上的电流&#xff0c;效率是电路板上的速度。在这个时代&#xff0c;每一微秒都蕴藏着无限可能&#xff0c;每一决策都关乎着企业的生死存亡。APS生产排程软件&#xff0c;是您的电子制造帝国中的智慧大脑&#xff0c;以卓越的创造力&…
阅读更多...
C++手撕AVL树

C++手撕AVL树

文章目录 AVL树概念 节点插入右单旋左右双旋 验证AVL树AVL树的性能 AVL树 之前我们讲了二叉搜索树的相关内容&#xff0c;但是也了解到二叉搜索树有其自身的缺陷&#xff0c;就是当插入的元素有序或者接近有序&#xff0c;退化成单支树的时候&#xff0c;他的时间复杂度就会退…
阅读更多...
AIGC: 4 IT从业者如何构建自己的AI知识体系

AIGC: 4 IT从业者如何构建自己的AI知识体系

图片是我使用dall.e模型生成的图片&#xff0c; 提示词&#xff1a; 程序员系统学习OpenAI开发者平台系统学习。 我按照SCQA模型&#xff0c;来开始今天的内容。 S 场景 今天是2024年3月23日&#xff0c;我在深圳&#xff0c;从事IT行业&#xff0c;每个人从事的行业各不相…
阅读更多...
redis启动后无法被外部主机连接

redis启动后无法被外部主机连接

目录 一、场景二、连接异常三、排查四、原因五、解决 一、场景 1、CentOS安装redis后&#xff0c;外部主机无法连接到redis 二、连接异常 1、RedisDesktopManager无法连接 2、使用telnet命令测试6379端口是否能正常访问 三、排查 1、redis服务是否启动 四、原因 从以下信息…
阅读更多...
前端学习之JavaScript基础语法三种引入方式、三种输出方式、输入框、确认框、循环加强、arguments

前端学习之JavaScript基础语法三种引入方式、三种输出方式、输入框、确认框、循环加强、arguments

目录 三种引入方式 三种输出方式 运行结果 变量 确认框、输入框 运行结果 循环加强 arguments 三种引入方式 <!DOCTYPE html> <html lang"en"> <head><meta charset"UTF-8"><title>Document</title><!-- 三…
阅读更多...
MySQL索引优化二

MySQL索引优化二

分页查询优化 很多时候我们的业务系统实现分页功能可能会用如下sql实现 select * from employees limit 10000,10;表示从表employees中取出从10001行开始的10条记录.看似只查询了10条记录,实际这条sql是先读取10010条记录,然后抛弃前10000条记录,然后读到后面10条想要的数据,…
阅读更多...
蓝鹏智能测量仪应用于这些方面！助力发展新质生产力！

蓝鹏智能测量仪应用于这些方面！助力发展新质生产力！

新质生产力是未来几年着重发展的方向&#xff0c;关于如何实现产业化升级&#xff0c;各厂家会在自身的基础上进行产业化调整升级&#xff0c;利用新工具、新手段&#xff0c;大幅缩短研发设计周期&#xff0c;从而让产品迭代速度不断加快&#xff1b;提升产品品质&#xff0c;…
阅读更多...
防静电检测设备如何完善PCBA车间的防静电管控？

防静电检测设备如何完善PCBA车间的防静电管控？

在PCBA&#xff08;Printed Circuit Board Assembly&#xff09;车间中&#xff0c;静电是一个极其重要的问题&#xff0c;因为静电可能对电子元器件和PCB板造成损坏&#xff0c;进而影响整个生产流程和产品质量。为了有效防止静电问题&#xff0c;企业通常会引入防静电检测设备…
阅读更多...
UE5学习日记——蓝图节点前缀关键字整理

UE5学习日记——蓝图节点前缀关键字整理

一、起因 节点如海&#xff0c;中英文翻译的时候还是有差别的&#xff0c;比如&#xff1a; 同一个中文&#xff0c;可能在英文里完全不同&#xff0c;连出现位置可能都不一样 附加 Attach Actor To Component&#xff08;将Actor附加到组件&#xff09;Append Array&#xf…
阅读更多...
CTF题型 nodejs(1) 命令执行绕过典型例题

CTF题型 nodejs(1) 命令执行绕过典型例题

CTF题型 nodejs(1) 命令执行绕过 文章目录 CTF题型 nodejs(1) 命令执行绕过一.nodejs中的命令执行二.nodejs中的命令绕过1.编码绕过2.拼接绕过3.模板字符串4.Obejct.keys5.反射6.过滤中括号的情况典型例题1.[GFCTF 2021]ez_calc2.[西湖论剑 2022]Node Magical Login 一.nodejs中…
阅读更多...
SpringBoot可以同时处理多少请求

SpringBoot可以同时处理多少请求

SpringBoot默认的内嵌容器是Tomcat&#xff0c;即看Tomcat可以处理多少请求 默认配置 server:tomcat:threads:min-spare: 10 # 最小工作线程数max: 200 # 最大线程数max-connections: 8192 # 接受和处理的最大连接数&#xff0c;超过8192的请求就会被放入到等待队列中ac…
阅读更多...
【原创教程】关于东方马达的控制方法（上）

【原创教程】关于东方马达的控制方法（上）

1 实现的功能 能够精准定位，快速移动到指定位置 2 硬件配置 东方马达组件一套包含：AZD-CD驱动器，AZM66MC马达电机。 如下图所示： 2.1 东方马达I/O端子分配 2.2 电路图 2.3 硬件接线
阅读更多...
代码随想录算法训练营第二十一天|530. 二叉搜索树的最小绝对差

代码随想录算法训练营第二十一天|530. 二叉搜索树的最小绝对差

530. 二叉搜索树的最小绝对差 已解答 简单 相关标签 相关企业 给你一个二叉搜索树的根节点 root &#xff0c;返回 树中任意两不同节点值之间的最小差值 。 差值是一个正数&#xff0c;其数值等于两值之差的绝对值。 示例 1&#xff1a; 输入&#xff1a;root [4,2,6,1,3] 输出…
阅读更多...
rfc793-page36

rfc793-page36

rfc793原文 If the connection is in any non-synchronized state (LISTEN,SYN-SENT, SYN-RECEIVED), and the incoming segment acknowledgessomething not yet sent (the segment carries an unacceptable ACK), orif an incoming segment has a security level or compart…
阅读更多...
Redis数据类型bitMap以及解决的相关实际需求

Redis数据类型bitMap以及解决的相关实际需求

在Redis数据库中&#xff0c;Bitmap&#xff08;位图&#xff09;是一种特殊的数据结构&#xff0c;它不是一个独立的数据类型&#xff0c;而是基于String类型实现的。Bitmap主要用于存储大量二进制位&#xff08;0或1&#xff09;的数据&#xff0c;这些位可以代表不同的状态或…
阅读更多...
CMU-TARE 探索算法官方社区问答汇总

CMU-TARE 探索算法官方社区问答汇总

参考引用 TARE机器人自主导航系统社区-CSDN社区云TARE平台资源链接汇总CMU团队开源算法点云地面分割 terrainAnalysis 代码解析Local Planner 代码详解以及如何适用于现实移动机器人论文翻译&#xff1a;Autonomous Exploration Development Environment and the Planning Algo…
阅读更多...
最新文章

Copyright @ 2022~2023