mysql数据库备份和恢复和日志管理

已于 2023-06-21 17:04:49 修改
阅读量303 收藏
点赞数
分类专栏： mysql 文章标签： 数据库 mysql postgresql
于 2023-06-20 14:23:35 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jhy1798807161/article/details/131287057
版权

数据库备份和恢复和日志管理

一：数据库备份备份和恢复

数据备份的重要性

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

造成数据丢失的原因

  • 程序错误
  • 人为操作错误
  • 运算错误
  • 磁盘故障
  • 灾难 (如火灾、地震)和盗窃
    在这里插入图片描述

1.备份分类

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

  • 物理备份:对数据库操作系统的物理文件(如数据文件日志文件等)的备份
    物理备份方法

  • 1.冷备份 (脱机备份) :是在关闭数据库的时候进行的

  • 2.热备份(联机备份):数据库处于运行状态，依赖于数据库的日志文件

  • 3.温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作

  • 逻辑备份:对数据库逻辑组件(如: 表等数据库对象)的
    备份

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

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

2.常见的备份方法

  • 物理冷备
    备份时数据库处于关闭状态，直接打包数据库文件
    备份速度快，恢复时也是最简单的
  • 专用备份工具mysqldump或mysqlhotcopy
    mysqldump常用的逻辑备份工具
    mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
  • 启用二进制日志进行增量备份
    进行增量备份，需要刷新二进制日志
  • 第三方工具备份
    免费的MySQL热备份软件Percona ，XtraBackup

3.MySQL完全备份和恢复

InnoDB存储引擎的数据库在磁盘上存储成三个文件：db.opt(表属性文件)、表名.frm(表结构文件)、表名.ibd(表数据文件)。
在这里插入图片描述

是对整个数据库、数据库结构和文件结构的备份
保存的是备份完成时刻的数据库
是差异备份与增量备份的基础

分类

一：物理冷备份与恢复
关闭MySQL数据库

在这里插入图片描述

使用tar命令直接打包数据库文件夹

在这里插入图片描述
在这里插入图片描述

直接替换现有MySQL目录即可

在这里插入图片描述

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

（1）完全备份一个或多个完整的库（包括其中所有的表）

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

例：
mysqldump -u root -p --databases kfc > /opt/kfc.sql
mysqldump -u root -p --databases mysql kfc > /opt/mysql-kfc.sql

（2）完全备份 MySQL 服务器中所有的库

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

例：
mysqldump -u root -p --all-databases > /opt/backup/all.sql
在这里插入图片描述

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

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

例：

mysqldump -u root -p [-d] kfc info1 info2 > /opt/kfc_info1.sql

#使用“-d”选项，说明只保存数据库的表结构
#不使用“-d”选项，说明表数据也进行备份

（4）查看备份文件

grep -v "^--" /opt/kfc_info1.sql | grep -v "^/" | grep -v "^$"
恢复
systemctl start mysqld

（1）恢复数据库

mysql -u root -p -e 'drop database kfc;'

#“-e”选项，用于指定连接 MySQL 后执行的命令，命令执行完后自动退出

mysql -u root -p -e 'SHOW DATABASES;'

mysql -u root -p < /opt/kfc.sql
mysql -u root -p -e 'SHOW DATABASES;'

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

mysqldump -u root -p kfc info1 > /opt/kgc_info1.sql

mysql -u root -p -e 'drop table kfc.info1;'
mysql -u root -p -e 'show tables from kfc;'

mysql -u root -p kfc < /opt/kfc_info1.sql
mysql -u root -p -e 'show tables from kfc;'

拓展：xtrabackup

xtrabackup

4.MySQL 增量备份与恢复

----MySQL 增量备份----
1．开启二进制日志功能

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

#二进制日志(binlog)有3种不同的记录格式：STATEMENT(基于SQL语句)、ROW(基于行)、MIXED(混合模式)，默认格式是STATEMENT

systemctl start mysqld
ls -l /usr/local/mysql/data/mysql-bin.*

在这里插入图片描述

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

mysqldump -u root -p密码 kfc info1 > /opt/kfc_info1_$(date +%F).sql
mysqldump -u root -p密码 --databases kfc > /opt/kfc_$(date +%F).sql

在这里插入图片描述

3．可每天进行增量备份操作，生成新的二进制日志文件(例如 mysql-bin.000002)

mysqladmin -u root -p flush-logs

在这里插入图片描述

4．插入新数据，以模拟数据的增加或变更

use kfc;
insert into info1 values(3,'user3','male','game');
insert into info1 values(4,'user4','female','reading');

在这里插入图片描述

5．再次生成新的二进制日志文件(例如 mysql-bin.000003)

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

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

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

在这里插入图片描述

----MySQL 增量恢复----
1．一般恢复
（1）模拟丢失更改的数据的恢复步骤

use kfc;
delete from info1 where id=3;
delete from info1 where id=4;

在这里插入图片描述

mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

（2）模拟丢失所有数据的恢复步骤

use kfc;
drop table info1;

mysql -u root -p kfc < /opt/kfc_info1_2020-11-22.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000002 | mysql -u root -p

2．断点恢复

mysqlbinlog --no-defaults --base64-output=decode-rows -v /opt/mysql-bin.000002

在这里插入图片描述

例：
#at 302
#201122 16:41:16
插入了“user3”的用户数据

#at 623
#201122 16:41:24
插入了“user4”的用户数据

（1）基于位置恢复
#仅恢复到操作 ID 为“623”之前的数据，即不恢复“user4”的数据

mysqlbinlog --no-defaults --stop-position='623' /opt/mysql-bin.000002 | mysql -uroot -p

#仅恢复“user4”的数据，跳过“user3”的数据恢复

mysqlbinlog --no-defaults --start-position='623' /opt/mysql-bin.000002 | mysql -uroot -p

（2）基于时间点恢复
#仅恢复到 16∶41∶24 之前的数据，即不恢复“user4”的数据

mysqlbinlog --no-defaults --stop-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p

#仅恢复“user4”的数据，跳过“user3”的数据恢复

mysqlbinlog --no-defaults --start-datetime='2020-11-22 16:41:24' /opt/mysql-bin.000002 |mysql -uroot -p

如果恢复某条SQL语句之前的所有数据，就stop在这个语句的位置节点或者时间点
如果恢复某条SQL语句以及之后的所有数据，就从这个语句的位置节点或者时间点start

二：日志管理

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=5												#设置超过5秒执行的语句被记录，缺省时为10秒

在这里插入图片描述

systemctl restart mysqld

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——使用mysqldump命令备份
m0_72882572的博客
11-06 1万+
使用mysqldump命令备份 mysqldump命令可以将数据库中的数据备份成一个文本文件。表的结构和表中的数据将存储在生成的文本文件中。本节将介绍mysqldump命令的工作原理和使用方法。
使用mysqldump命令进行数据备份
欧阳啸天的专栏
05-26 1万+
简介 在日常工作中，我们经常需要对MySQL数据库进行一些数据备份，以便在出现数据问题时能够进行恢复。经常使用的方式就是通过mysqldump命令来进行这项工作，今天就来简单介绍些该命令。 msqldump命令 官网地址奉上，可以自行查看： https://dev.mysql.com/doc/refman/5.7/en/using-mysqldump.html 使用mysqldump命令生成的文件能干什么？ 1、可以作为备份，以便在数据丢失的情况下恢复数据。 2、在主从复制中，可以作为设置Slave的数据源
mysqldump分库备份，分表备份，分库分表备份
weixin_55769892的博客
08-14 185
过滤备份的数据库，并过滤到指定目录。
编写脚本，使用mysqldump实现分库分表备份
m0_59250127的博客
08-04 193
1. 进入backup_db.sh。
mysqldump备份mysql数据库
fengchengwu2012的博客
04-24 3201
select into 、mysqldump 数据库备份
MySQL数据库备份和恢复
04-10
### MySQL数据库备份与恢复：深度解析与实践指南 在当今数据驱动的世界中，数据库的稳定性和数据的安全性显得尤为重要。MySQL作为全球最流行的开源关系型数据库管理系统之一，其备份和恢复策略是确保数据安全和系统...
Mysql数据库备份恢复测试报告-xtrabackup.docx
03-29
1. **全量备份**：全量备份是数据库备份的一种方式，它会完整地复制数据库的所有数据。在本次测试中，全量备份发生在2022年6月24日02:20至02:30左右，备份集大小为2.4GB。全量备份通常作为恢复的基础，后续的增量...
mysql数据库备份和恢复工具
11-23
MySQL数据库是世界上最受欢迎的开源关系型数据库管理系统之一，其稳定性和高效性使其在各种规模的企业中得到广泛应用。在日常操作中，对数据库进行备份和恢复是保障数据安全、防止意外丢失的重要环节。本文将深入...
MySQL数据库和MySQL管理、备份与恢复
12-14
本教程将带你深入了解MySQL数据库的使用，管理以及备份与恢复的相关知识。 首先，让我们从基础开始，理解什么是数据库。数据库是存储和组织数据的系统，它允许用户以结构化方式访问、管理和更新数据。MySQL以其高效...
使用Python代码调用命令行来执行MySQL数据库备份和恢复操作
最新发布
08-16
### 使用Python进行MySQL数据库备份与恢复 在现代软件开发中，数据库备份与恢复是一项至关重要的任务，它确保了数据的安全性和可恢复性。虽然大多数情况下，数据库管理员会使用专门的工具或命令行来进行这些操作，...
mysql mysqldump数据备份和增量备份
09-10
本篇文章主要讲如何使用shell实现mysql全量，增量备份，还可以按时间备份。
【mysql系列】使用mysqldump实现数据库|表 备份
Think_and_work的博客
03-25 4565
大家好，我是walker 一个从文科自学转行的程序员~ 爱好编程，偶尔写写编程文章和生活 欢迎关注公众号【I am Walker】，一块学习和成长！ 简介 mysqldump 是 MySQL 自带的逻辑备份工具。 它的备份原理是通过协议连接到 MySQL 数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert 语句，当我们需要还原这些数据时，只要执行这些 insert 语句，即可将对应的数据还原。 备份命令 命令格式 mysqldump [选项] 数据库名 [表名] > 脚本名 .
mysqldump 全表备份 sql
xing
01-30 249
sql
Mysql备份——mysqldump
热门推荐
yolo2016的博客
02-07 1万+
Mysql备份——mysqldumpMysqldump备份流程一. mysqldump命令备份数据二. mysqldump常用操作示例三. 还原 MySQL备份内容 Mysqldump备份流程 mysqldump 备份出的文件就是 sql 文件，其核心就是对每个表执行 select ，然后转化成相应的 insert 语句。 mysqldump 的备份流程大致如下： 对某个库下所有表加读锁； 循环备份备份表数据； 释放读锁； 循环上面三个步骤； 备份完毕。 一. mysqldump命令备份数据 在MySQ
MySQLDump 备份
flyincyclone的博客
08-12 151
这里写自定义目录标题mysqldump mysqldump mysqldump databasename --user=UserName --password=YourPassword databasename：处理的数据库名 UserName和Password 对应你的授权口令 导出整库表结构和数据： mysqldump -uroot -p -d databasename > createtab.sql 导出整库表结构： 如果只需要导出表的结构,那么可以使用mysqldump的 -d 选项
mysql的日志及利用mysqldump备份及还原
reblue520的专栏
01-14 548
日志文件：6类       一般查询日志：log，general_log,log_output       慢查询日志：       错误日志       二进制日志       中继日志       事务日志  二进制日志：      记录了当前服务器的数据修改和有潜在可能性影响数据修改的语句      默认在数据目录下，通常情况下为mysql-bin  ...
MySQL mysqldump数据导出详解 --dump-slave 注意事项
weixin_34381666的博客
07-21 1830
http://www.cnblogs.com/chenmh/p/5300370.html 介绍  在日常维护工作当中经常会需要对数据进行导出操作，而mysqldump是导出数据过程中使用非常频繁的一个工具；它自带的功能参数非常多，文章中会列举出一些常用的操作，在文章末尾会将所有的参数详细说明列出来。   语法： 默认不带参数的导出，导出文本内容大概如下：创建数据库判断语句-删...
mysqldump备份数据库表
计算机毕业论文源码，学生个人网页制作html源码。贴近用户做网络推广和互联网优化。
11-17 1561
如何将mysql 导出导入数据库？？？ 用mysqldump命令就可以，你的例子比方mysqldumpa-uroot-p>g:\a.sql，是在命令行里直接操作，不是登录mysql之后的命令，另详细请参考 linux mysqldump导入数据库的问题 可以将显示重定向到/dev/null就不显示了啊 本...
MySQLDump的备份方法
Free雅轩的博客
07-28 686
mysqldump 是日常比较常用的一个工具了，在对数据库进行导出工作时，经常会用到 mysqldump 。本篇文章将介绍 mysqldump 工具的使用方法并分享几点备份技巧。 mysqldump使用简介 mysqldump 是 MySQL 系统自带的逻辑备份工具，主要用于转储数据库。它主要产生一系列的 SQL 语句，可以封装到文件，该文件包含重建数据库所需要的 SQL命令如 CREATE DATABASE ，CREATE TABLE ，INSERT 等等。当我们需要还原这些数据时，只需要执行.
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值