Mysql日志管理、备份与恢复

最新推荐文章于 2024-07-29 04:04:53 发布
最新推荐文章于 2024-07-29 04:04:53 发布
阅读量685 收藏 6
点赞数 1
文章标签： mysql 数据库
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/SmileLife_/article/details/125389452
版权

目录

一、Mysql日志管理

1、日志的分类

1.1 错误日志

1.2 通用查询日志

1.3 二进制日志

1.4 慢查询日志

1.5 配置日志文件

2、日志的查询

二、Mysql备份与分类

1、数据备份的重要性

2、造成数据丢失的原因

3、数据库备份的分类

3.1 从物理与逻辑的角度划分

3.2 从数据库的备份策略角度划分

3.2-1 画图演示三种备份

三、Mysql完全备份

模拟环境如下：在school_kgc中有boss1的表

3.1 物理冷备与恢复

3.2 专用备份工具mydump和mysqlhotocopy

3.2-1 完全备份一个或多个完整的库（包括库中的表）

3.2-2 完全备份mysql服务器中的所有库

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

3.2-4 Mysql完全备份（使用免交互）

3.2-5 恢复数据表

3.3 Mysql增量备份

3.3-1 开启二进制日志功能

3.3-2 可每天进行增量备份操作，生成新的二进制日志文件（mysql-bin.000003）

3.3-3 插入新数据，以模拟数据的增加或变更

3.3-4 再次生成新的二进制日志文件（例如mysql-bin.000004）

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

3.4 Mysql增量恢复

3.4-1 一般恢复

3.4-2 基于位置恢复

一、Mysql日志管理

Mysql的日志默认保存位置为/usr/local/mysql/date，Mysql的日志配置文件为/etc/my.cnf，里面有一个【mysqld】项。

1、日志的分类

1.1 错误日志

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

vim /etc/my.cnf
log-error=/usr/local/mysql/data/mysql_error.log

1.2 通用查询日志

用来记录MySQL的所有连接和语句，默认是关闭的

vim /etc/my.cnf
general_log=ON
general_log_file=/usr/local/mysql/data/mysql_general.log

1.3 二进制日志

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

vim /etc/my.cnf
#也可以 log_bin=mysql-bin
log-bin=mysql-bin

1.4 慢查询日志

用来记录所有执行时间超过long_query_time秒的语句，可以找到哪些查询语句执行时间长，以便于优化，默认是关闭的

vim /etc/my.cnf
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5

1.5 配置日志文件

1. #修改my.cnf配置文件
 
#错误日志
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	
#慢查询日志
slow_query_log=ON
slow_query_log_file=/usr/local/mysql/data/mysql_slow_query.log
long_query_time=5
 
2. #重新mysql服务
systemctl restart mysqld.service

 

2、日志的查询

#登入mysql
mysql -u root -p[密码]
 
#查看通用查询日志是否开启
show variables like 'general%';	
 
#查看二进制日志是否开启
show variables like 'log_bin%';									

#查看慢查询日功能是否开启
show variables like '%slow%';								

#查看慢查询时间设置
show variables like 'long_query_time';						

#在数据库中设置开启慢查询的方法
set global slow_query_log=ON;

二、Mysql备份与分类

1、数据备份的重要性

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

2、造成数据丢失的原因

  1. 程序错误
  2. 人为操作错误
  3. 运算错误
  4. 磁盘故障
  5. 灾难（地震、火灾）和盗窃

3、数据库备份的分类

3.1 从物理与逻辑的角度划分

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

物理备份的方法：

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

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

3.2 从数据库的备份策略角度划分

完全备份：每次只对数据库进行完整的备份

差异备份：备份自从上次完全备份之后修改的文件

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

3.2-1 画图演示三种备份

1、完全备份

 每次都会进行完全的备份，会导致占用大量的空间，并且有大量的重复数据

恢复时，直接使用完全备份的文件即可

2、差异备份

每一次差异备份，都会备份上一次完全备份之后的数据，可能会出现备份重复数据，导致占用额外的磁盘空间。

恢复时，先恢复完全备份的数据，然后在恢复差异备份的数据。

3、增量备份

每次增量备份都是备份在上一次完全备份或者增量备份之后的数据，不会出现备份重复数据的情况，也不会占用额外的磁盘空间

恢复数据时，需要按照次序恢复完全备份和增量备份的数据。

三、Mysql完全备份

InnoDB存储引擎的数据库在磁盘上存储成三个文件：

  • db.opt（表属性文件）
  • 表名.frm（表结构文件）
  • 表名.ibd（表数据文件）

模拟环境如下：在school_kgc中有boss1的表

3.1 物理冷备与恢复

  • 备份时数据库处于关闭状态，直接打包数据库文件
  • 备份速度快，恢复时也是最简单的 
1. #关闭mysql，备份data目录
systemctl stop mysqld
yum -y install xz
cd /usr/local/mysql
 
#压缩备份data目录
tar Jcvf /opt/mysql_all_$(date +%F).tar.xz data/
 
2. #登录mysql，删除school库 
systemctl start mysqld.service 
mysql -u root -p001122
 
 
3. #解压之前备份的数据库data目录，不用删除原目录，会自动替换
cd /opt
ls
cd /usr/local/mysql
tar Jxvf /opt/mysql_all_2021-11-28.tar.xz -C ./
 
4. #重启服务查看被删除的库

关闭mysql，备份data目录

 登录数据库，删除school_kgc库

 解压之前备份的数据库data目录，不用删除原目录，会自动替换

重启服务查看被删除的库

3.2 专用备份工具mydump和mysqlhotocopy

  • mysqldump常用的逻辑备份工具

  • mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

3.2-1 完全备份一个或多个完整的库（包括库中的表）

#导出的就是数据库脚本文件
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql

例：备份一个或者多个库

mysqldump -uroot -p --databases school > /opt/mysql_bak/school.sql

mysqldump -uroot -p --databases school ky19 > /opt/mysql_bak/school_ky19.sql

3.2-2 完全备份mysql服务器中的所有库

mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql

例：备份所有的库

mysqldump -uroot -p --all-databases > /opt/mysql_bak/all.sql

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

mysqldump -u root -p[密码] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql

例：备份school库中的boss1和boss2表

mysqldump -uroot -p school boss1 boss2  > /opt/mysql_bak/school_boss1_boss2.sql

  • 使用“-d”选项，说明只保存数据库的表结构

  • 不使用“-d”选项，说明表数据也进行备份

3.2-4 Mysql完全备份（使用免交互）

systemctl start mysqld

mysql -u root -p -e 'drop database school;'
#“-e”选项，用于指定连接 MySQL 后执行的命令，命令执行完后自动退出
mysql -u root -p -e 'show databases;'
 
#恢复school数据库
mysql -u root -p < /opt/mysql_bak/school.sql		
mysql -u root -p -e 'show databases;'

3.2-5 恢复数据表

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

mysqldump -uroot -p school  boss1 boss2 > /opt/mysql_bak/school_boss1_boss2.sql
 
1. #删除school中的boss1表 
mysql -u root -p -e 'drop table school.boss1;'
mysql -u root -p -e 'show tables from school;'
 
2. #恢复school库中的表 
mysql -u root -p school < /opt/mysql_bak/school_boss1_boss2.sql 
mysql -u root -p -e 'show tables from school;'

恢复school库中的boss1表

3.3 Mysql增量备份

3.3-1 开启二进制日志功能

vim /etc/my.cnf
[mysqld]
log-bin=mysql-bin
#可选，指定二进制日志(binlog)的记录格式为 MIXED（混合输入）
binlog_format = MIXED				
server-id = 1

二进制日志（binlog）有三种不同的记录格式：

  • STATEMENT(基于SQL语句)

  • ROW(基于行)

  • MIXED(混合模式)，默认格式是STATEMENT

#只要重启就会自动生成二进制文件
systemctl start mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

测试

3.3-2 可每天进行增量备份操作，生成新的二进制日志文件（mysql-bin.000003）

mysqladmin -u root -p flush-logs

3.3-3 插入新数据，以模拟数据的增加或变更

insert into boss1 values (6,'yangshuo','喜欢黑丝');

insert into boss1 values (7,'yaoran','喜欢帅哥');

3.3-4 再次生成新的二进制日志文件（例如mysql-bin.000004）

mysqladmin -u root -p flush-logs

之前的步骤4的数据库操作会保存到mysql-bin.000003文件中，之后数据库数据再发生变化则保存在mysql-bin.000004文件中 

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

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：显示详细内容

3.4 Mysql增量恢复

1、一般恢复

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

2、基于位置恢复

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

发生错误节点之前的一个节点，上一次正确操作的位置点停止。

3、基于时间点恢复

跳过某个发生错误的时间点来实现数据恢复；在错误的时间点停止，在下一个正确的时间点开始。

3.4-1 一般恢复

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

①.模拟丢失更改的数据的恢复

use school;
delete from boss1 where id=6;
delete from boss1 where id=7;
 
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000004 | mysql -u root -p

删除数据

 通过二进制日志进行恢复

②.模拟丢失所有的数据恢复

use school;
drop table boss1;
 
mysql -u root -p school < /opt/mysql_bak/school_boss1_boss2.sql
mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000004 | mysql -u root -p

删除school库中boss1表

先恢复整表，然后在恢复二进制日志文件

3.4-2 基于位置恢复

  • 数据库在某一时间点可能既有错误的操作也有正确的操作

  • 可以基于精准的位置跳过错误的操作

  • 发生错误节点之前的一个节点，上一次正确操作的位置点停止

# 刷新生成新的二进制日志文件
mysqladmin -u root -p flush-logs 	
# 进入到data目录
cd /usr/local/mysql/data	
#查看二进制日志文件
mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000008

实例：

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

②.再插入一条数据

以上插入的数据都存放在 mysql-bin.000008

③.测试恢复到302之前的数据，不恢复后面一条数据

删除数据之前必须刷新一下日志文件

mysqladmin -u root -p flush-logs

 删除表中数据

对数据进行恢复

mysqlbinlog --no-defaults --stop-position='611' mysql-bin.000008 | mysql -u root -p

④.仅恢复最后一条数据，跳过前面多条数据的恢复

删除表中所有的数据

⑤.基于时间恢复

测试恢复到ID为611之前的数据

仅恢复最后一条数据，跳过前面的数据

mysqlbinlog --no-defaults --start-datetime='2022-06-22 1:16:59' /usr/local/mysql/data/mysql-bin.000008 |mysql -uroot -p

 

微笑着敲代码
关注
  • 1
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
mysql日志
eluanshi12的博客
04-20 200
MySQL数据库可以实现主从复制，实现持久化，实现回滚，其关键在于MySQL里的三种log，分别是： binlog redo log undo log binlog 用于记录数据库表结构和表数据变更的二进制日志，比如insert、update、delete、create、truncate等等操作，不会记录select、show操作，因为没有对数据本身发生变更。 使用mysqlbinlog命令可以查看binlog文件内容。 会记录下每条变更的sql语句，还有执行开始时间，结束时间，事务id等等信息。 使用命
MySQL数据库和MySQL管理、备份与恢复
12-14
3. 表的创建与管理：学习如何定义数据表的结构，包括字段、数据类型和约束，如主键、外键等。 4. 用户权限管理：创建和管理数据库用户，分配不同级别的权限，确保数据安全。 5. 日常操作：了解如何进行数据导入...
MySQL 日志
qq_60271706的博客
04-22 1638
redo log：记录的是脏数据的变化--》buffer pool里的作用：解决事务commit不成功MySQL意外宕机重启也不要紧。只要在重启时解析redo log中的事务而后重做一遍。将Buffer Pool中的缓存页重作成脏页。后续再在合适的时机将该脏页刷入磁盘便可。undo log：记录某数据被修改前的值作用：解决事务rollback，撤销。
mysqldump 导出日志
最新发布
weixin_41439119的博客
07-29 55
数据库相关学习资料：https://edu.51cto.com/video/655.htmlMySQL 数据库导出日志的实用指南 在数据库管理中，备份和导出数据是一个重要环节，尤其是对于MySQL数据库。mysqldump 是MySQL的命令行工具，用于导出数据库的结构和数据。本文将详细介绍如何使用mysqldump来导...
MySQL日志详解
weixin_47019016的博客
01-05 2668
日志@[TOC](日志) MySQL日志记录了MySQL数据库日常操作和错误信息。MySQL有不同类型的日志文件(各自存储了不同类型的日志)，从日志当中可以查询到MySQL数据库的运行情况、用户的操作、错误的信息等。 MySQL日志分为4大类 1.错误日志：记录mysql服务的启动，运行或停止mysql服务时出现的问题 2.查询日志：记录建立的客户端的连接和执行的语句 3.二进制日志：记录所有更改数据的语句，可以用于数据的复制 4.慢查询日志：记录所有执行的时间超过long_query_time的所有查询或
mysql日志管理、备份与恢复.md
08-19
### MySQL 日志管理、备份与恢复 #### 一、MySQL 常用日志 **1. 概述** 在 MySQL 数据库系统中，日志文件对于实现有效的备份与恢复策略至关重要。通过日志文件，我们可以追踪数据变化、诊断问题，并在必要时恢复...
mysql日志管理、备份与恢复.pdf
07-24
MySQL作为一款开源的关系型数据库管理系统，其日志管理和备份恢复功能对于保障数据的安全性、完整性和可靠性至关重要。接下来我将详细介绍MySQL中不同日志的作用、如何管理和配置这些日志、以及数据库备份和恢复的...
Mysql实现企业级日志管理、备份与恢复的实战教程
09-09
总之，企业级的MySQL日志管理、备份与恢复是一个综合性的任务，需要结合业务需求、存储资源、恢复速度和安全性等多个因素来制定和实施策略。通过合理规划和实践，可以确保企业的数据安全和业务连续性。
关于Mysql的日志管理、备份与恢复
11-09
MySQL数据库在日常运维中，日志管理、备份与恢复是至关重要的环节，它们关系到数据的安全性和业务的连续性。本文将深入探讨这三大主题，帮助你更好地理解并掌握相关知识。 首先，我们来了解MySQL的日志系统。MySQL...
Mysql日志
hujunbo553271的专栏
06-21 138
Mysql日志分类及管理
mysql 日志
一路向前
04-26 2732
慢查询日志主要用来记录查询时间较长的日志。在开发环境下，可以开启慢查询日志来记录查询时间较长的查询语句，然后对这些语句进行优化。通过配置long_query_time的值，可以灵活地掌握不同程度的慢查询语句
MySQL日志
TimeFriends
06-09 426
mysql日志
mysql之日志
奋斗的青年
03-15 1748
mysql日志主要有：错误日志，慢日志，事物日志，二进制日志等几大类。比较重要的还要属二进制日志binlog（归档日志）和事务日志redo log（重做日志）和undo log（回滚日志） redo log：InnoDB存储引擎，让mysql有奔溃后恢复的能力。 mysql按页读取数据，先查询Buffer Pool，没有命中再去硬盘加载。 mysql更新数据时，发现 Buffer Pool 里存在要更新的数据，就直接在 Buffer Pool 里更新。然后会把“在某个数据页上做了什么修改”记录...
MYSQL数据库日志
lxp503238的博客
06-29 9130
MySQL有不同类型的日志文件，用来存储不同类型的日志，分为 二进制日志 、 错误日志 、 通用查询日志和 慢查询日志 ，这也是常用的4种。MySQL 8又新增两种支持的日志： 中继日志 和 数据定义语句日志 。使用这些日志文件，可以查看MySQL内部发生的事情。 **这6类日志分别为： **除二进制日志外，其他日志都是 文本文件 。默认情况下，所有日志创建于 MySQL数据目录 中。日志功能会 降低MySQL数据库的性能 。 日志会 占用大量的磁盘空间 。通用查询日志用来 记录用户的所有操作 ，包括启动和
MySQL日志管理与备份恢复实战
"这篇文档详细介绍了MySQL的日志管理、备份与恢复的重要概念和实践方法，包括错误日志、二进制日志、中继日志和慢查询日志的使用，以及备份策略和恢复技术。" MySQL日志管理是数据库运维中的关键环节，它对于故障...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值