MySQL之备份和恢复数据

最新推荐文章于 2025-05-12 16:51:23 发布
原创 最新推荐文章于 2025-05-12 16:51:23 发布 · 248 阅读 · 1 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#mysql #数据库 #数据备份 #数据恢复 #日志

本文详细介绍了MySQL的日志类型、开启方法及其验证，强调了数据备份的重要性，并讲解了完全备份、增量备份的策略与操作步骤，包括mysqldump工具的使用，以及基于二进制日志的恢复技术，如一般恢复和断点恢复。

MySQL之备份和恢复数据

一、MySQL日志

1、日志分类

错误日志
通用查询日志
二进制日志
慢查询日志

2、开启MySQL的日志

通过对MySQL的配置文件进行永久性修改

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.service

在这里插入图片描述

3、验证日志是否开启

①查看通用查询日志是否开启

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;

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

4、小补充

在这里插入图片描述

二、备份的重要性和方法

1、数据备份的重要性

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

造成数据丢失的原因
1、程序错误
2、人为操作错误
3、运算错误
4、磁盘故障
5、灾难（火灾、地震、盗窃等）

2、分类

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

3、常见的备份方法

在这里插入图片描述

三、MySQL完全备份与恢复

事先我们需要在MySQL创建一个表，这里我已经创好了，创建表不做演示了
在这里插入图片描述

1、数据库冷备份与恢复及完全备份与恢复

物理冷备份与恢复
systemctl stop mysqld
yum -y install xz					#xz是一个压缩工具
#压缩备份
tar jcvf /opt/mysql_all_$(date +%F).tar.xz /usr/local/mysql/data/
#解压恢复
tar jxvf /opt/mysql_all_2021-02-05.tar.xz -C /usr/local/mysql/data

systemctl start mysqld

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

2、mysqldump备份与恢复

1、完全备份一个或多个完整的库（包括其中所有的表）
#导出的备份文件就是数据库脚本文件
mysqldump -u root -p[密码] --databases 库名1 [库名2] … > /备份路径/备份文件名.sql
例：
mysqldump -uroot -p123 --databases school > /opt/school.sql
mysqldump -uroot -p123 --databases mysql school > /opt/mysql-school.sql

在这里插入图片描述

2、完全备份 MySQL 服务器中所有的库
mysqldump -u root -p[密码] --all-databases > /备份路径/备份文件名.sql
例：
mysqldump -u root -p --all-databases > /opt/all.sql

在这里插入图片描述

3、完全备份指定库中的部分表
mysqldump -u root -p[密码] [-d] 库名 [表名1] [表名2] … > /备份路径/备份文件名.sql
#使用“ -d ”选项，说明只保存数据库的表结构
#不使用“ -d ”选项，说明表数据也进行备份
例：
mysqldump -u root -p school class > /opt/school_class.sql

在这里插入图片描述

4、查看备份文件
cat /opt/备份的文件 |grep -v "^--" | grep -v "^/" | grep -v "^$"

例：
cat /opt/school_class.sql |grep -v "^--" | grep -v "^/" | grep -v "^$"

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

3、MySQL 完全恢复

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

mysql -u root -p < /opt/school.sql
mysql -u root -p -e 'show databases;'

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

2、恢复数据表
当备份文件中只包含表的备份，而不包含创建的库的语句时，执行导入操作时必须指定库名，且目标库必须存在。
mysql -u root -p -e 'drop table school.class;'
mysql -u root -p -e 'show tables from school;'

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

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

四、MySQL 增量备份与恢复

1、MySQL 增量备份

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

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

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

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

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

这种定时任务可以结合crontab -e 计划性任务来执行
#手动执行备份
mysqldump -u root -p school class > /opt/school_class_$(date +%F).sql
mysqldump -u root -p --all-databases > /opt/allmysql_$(date +%F).sql

#使用crontab -e 计划性任务来执行；每周1凌晨2点对表class和所有的库进行备份
0 2 * * 1 mysqldump -u root -p school class > /opt/school_class_$(date +%F).sql
0 2 * * 1 mysqldump -u root -p --all-databases > /opt/allmysql_$(date +%F).sql

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

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

mysqladmin -u root -p flush-logs
ls -l /usr/local/mysql/data/mysql-bin.*

mysql -u root -p
use school;
insert into class values ('6','srs6','男','00006','666666','城南');
insert into class values ('7','srs7','男','00007','777777','城南');

mysqladmin -u root -p flush-logs
ls -l /usr/local/mysql/data/mysql-bin.*

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

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

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

在这里插入图片描述

5、MySQL 增量恢复

①、一般恢复

（1）模拟丢失更改的数据的恢复步骤
mysql -u root -p
use school;
delete from class where id=6;
delete from class where id=7;
select * from class;
quit

mysqlbinlog --no-defaults /opt/mysql-bin.000006 | mysql -u root -p
mysql -u root -p -e "select * from school.class;"

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

(2)、模拟丢失所有数据的恢复步骤
mysql -u root -p
use school;
drop table class;
show tables;
quit

mysql -uroot -p school < /opt/school_class_2021-04-12.sql
mysqlbinlog --no-defaults /opt/mysql-bin.000006 | mysql -uroot -p
mysql -u root -p -e "select * from school.class;"

在这里插入图片描述

②、断点恢复

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

#部分二进制文件的内容
......
BEGIN
/*!*/;
##-------------解释：at xxx 表示位置点------------------------------------------------
# at 587
##--------------解释：开头210206 15:45:53表示时间，其他的现在用不到-----------------------------------
#210412  7:54:21 server id 1  end_log_pos 734 CRC32 0x46f74eb6 	Query	thread_id=25	exec_time=0	error_code=0
##--------------解释：这里是执行的操作语句---------------------
SET TIMESTAMP=1618228461/*!*/;<------------建立时间戳
insert into class values ('6','srs6','男','00006','666666','城南')<-------向表中插入数据
/*!*/;
# at 734
#210412  7:54:21 server id 1  end_log_pos 765 CRC32 0x8c58b8d9 	Xid = 411
COMMIT/*!*/;
# at 765
#210412  7:56:51 server id 1  end_log_pos 830 CRC32 0x546d845a 	Anonymous_GTID	last_committed=2	sequence_number=3
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 830
#210412  7:56:51 server id 1  end_log_pos 913 CRC32 0xdf882827 	Query	thread_id=25	exec_time=0	error_code=0
SET TIMESTAMP=1618228611/*!*/;

##-------------------------------插入第二个数据--------------------------
BEGIN
/*!*/;
# at 913
#210412  7:56:51 server id 1  end_log_pos 1060 CRC32 0xd56daa4d 	Query	thread_id=25	exec_time=0	error_code=0
SET TIMESTAMP=1618228611/*!*/;
insert into class values ('7','srs7','男','00007','777777','城南')
/*!*/;
# at 1060
#210412  7:56:51 server id 1  end_log_pos 1091 CRC32 0x42ca40ae 	Xid = 412
COMMIT/*!*/;
# at 1091
#210412  7:57:39 server id 1  end_log_pos 1138 CRC32 0x777f989b 	Rotate to mysql-bin.000007  pos: 4
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
.......

在这里插入图片描述

（1）基于位置恢复
仅恢复到位置点为“913”之前的数据，即不恢复“id=7”的数据
#模拟数据丢失
mysql -uroot -p school < /opt/school_class_2021-04-12.sql
mysql -uroot -p -e "select * from school.class;"
#到位置点913停止恢复数据
mysqlbinlog --no-defaults --stop-position='913' /opt/mysql-bin.000006 | mysql -uroot -p
#查看class表的数据
mysql -uroot -p -e "select * from school.class;"

仅恢复“id=7”的数据，跳过“id=6”的数据
#模拟数据丢失
mysql -uroot -p school < /opt/school_class_2021-04-12.sql
mysql -uroot -p -e "select * from school.class;"
#从位置点913开始恢复数据
mysqlbinlog --no-defaults --start-position='913' /opt/mysql-bin.000006 | mysql -uroot -p
#查看class表的数据
mysql -uroot -p -e "select * from school.class;"

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

（2）基于时间点恢复
仅恢复到210412  7:56:51之前的数据，即不恢复“id=7”的数据
#模拟数据丢失
mysql -uroot -p school < /opt/school_class_2021-04-12.sql
mysql -uroot -p -e "select * from school.class;"
#到21-04-12  7:56:51截止恢复数据
mysqlbinlog --no-defaults --stop-datetime='21-04-12  7:56:51' /opt/mysql-bin.000006 | mysql -uroot -p
#查看class表的数据
mysql -uroot -p -e "select * from school.class;"

仅恢复“id=7”的数据，跳过“id=6”的数据恢复
#模拟数据丢失
mysql -uroot -p school < /opt/school_class_2021-04-12.sql
mysql -uroot -p -e "select * from school.class;"
#从21-04-12  7:56:51开始恢复数据
mysqlbinlog --no-defaults--start-datetime='21-04-12  7:56:51' /opt/mysql-bin.000006 | mysql -uroot -p
#查看class表的数据
mysql -uroot -p -e "select * from school.class;"

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

mysql 数据备份与恢复使用详解
congge
11-20 7304
mysql数据备份与恢复
精选资源
Mysql数据库备份恢复测试报告-xtrabackup.docx
03-29
通过这次测试，我们可以了解到MySQL数据库的备份恢复流程，以及如何验证恢复的正确性。同时，也强调了恢复过程中服务器性能的重要性，以及备份策略对于数据保护的关键性。在实际环境中，企业应根据自身需求制定合适...
mysql中数据如何备份_mysql数据库如何进行备份和恢复
weixin_34145418的博客
02-02 2430
一、确保mysql开启了binlog日志功能在/etc/my.cnf文件里的[mysqld]区块添加：#这个是存储的位置为mysql配置文件的位置log-bin=mysql-bin然后重启mysql服务生效二、创建数据库先创建一个ops数据库create database ops;use ops;创建一个customers表并写上表结构create table customers(id int n...
Mysql数据库——数据备份与恢复
热门推荐
一坨小橙子的博客
03-25 1万+
数据备份的重要性、数据库备份的分类（完全备份、差异备份、增量备份）、常见的备份方法（mysqldump）物理冷备份与恢复（tar）、完全恢复（Source需要进入Mysql、mysql命令）、日志（STATEMENT、ROW、MIXED）、增量备份（二进制备份与恢复mysql-bin000001）恢复（mysqlbinlog --start-position或--start-datetime）
MySQL备份与恢复
最新发布
2501_91112123的博客
05-12 1168
特点：增量备份的优势在于备份量小、时间短，能快速完成备份任务，节省存储空间和备份时间。但恢复过程比较复杂，需要依赖上次完全备份及后续所有增量备份，并且要按照顺序逐个反推恢复。如果其中某个增量备份出现问题，就可能导致数据无法完整恢复。二进制日志意义：二进制日志在增量备份中起着关键作用，它记录了数据库的所有更新操作。MySQL 8.0 默认开启 binlog，如果需要自定义配置，可以在配置文件中添加相关项，如，然后重启服务，这样就可以根据需求定制二进制日志的记录和管理。
MySQL备份与恢复数据
qq_41030039的博客
06-05 604
一、备份命令：mysqldump MySQL自带的备份命令mysqldump：把数据库中的数据以逻辑的sql语句形式直接输出生成sql文件。 参数解析： 1 -A --all-databases：导出全部数据库 2 -Y --all-tablespaces：导出全部表空间 3 -y --no-tablespaces：不导出任何表空间信息 4 --add-drop-database每个数据库创建之前添加drop数据库语句。 5 --add-drop-table每个数据表创建之前添加drop数据表
mysql导入库排除某个表_MySQL备份时排除指定数据库的方法
weixin_28972031的博客
01-18 1476
使用mysqldump命令备份时候，--all-databases 可以备份所有的数据库。 使用ignore-table 还可以排除制定的表。但是，mysqldump没有参数可以排除数据库的。要备份的数据库少的时候，可以通过mysqldump -uroot -p123456 --databases db1 db2 db3 > mysqldump.sql 这样来备份。但是假如数据库有数十个的话...
Mysql数据备份与恢复操作文档.md
12-17
##### 步骤二：恢复数据 **1. 小版本更新** 对于小版本更新，通常不需要替换 `information_schema` 表。步骤如下： - **覆盖原始文件夹** 使用备份的文件夹替换原有 `/var/lib/mysql` 文件夹。 ```bash tar ...
C#实现MySQL命令行备份和恢复
09-09
在使用C#进行MySQL备份和恢复时，主要是通过C#执行外部程序的方式来调用mysqldump命令。下面是一个C#实现备份MySQL数据库的示例方法： ```csharp public static bool BackupDB(string binfolderpath, string server...
mysql数据库备份和恢复工具
11-23
在日常操作中，对数据库进行备份和恢复是保障数据安全、防止意外丢失的重要环节。本文将深入探讨MySQL数据库的备份与恢复策略，并提供相关工具的使用指南。 一、MySQL备份的重要性 数据库备份是防止数据丢失的关键...
linux系统备份mysql数据库脚本_linux中备份mysql数据库的一个shell脚本
weixin_28946193的博客
01-27 271
#!/bin/bash#FileName:select_into_bak.sh#Desc:Use select into outfile to backup db or tables#Created By:fedoracle#Date://DB=mysqlUSER=testPASSWD=testHOST=192.168.164.129BAK_DIR=/data/mysql/backup/$DBDA...
mysql 备份指定数据库命令_MySQL_MySQL备份时排除指定数据库的方法，使用mysqldump命令备份时候，--al - phpStudy...
weixin_33945512的博客
01-19 344
MySQL备份时排除指定数据库的方法使用mysqldump命令备份时候，--all-databases 可以备份所有的数据库。 使用ignore-table 还可以排除制定的表。但是，mysqldump没有参数可以排除数据库的。要备份的数据库少的时候，可以通过mysqldump -uroot -p123456 --databases db1 db2 db3 > mysqldump.sql 这...
MySQL日志管理，备份以及恢复
weixin_51432789的博客
01-28 1515
目录一、MySQL的日志管理1、MySQL的日志分类2、MySQL的日志开启二、MySQL 完全备份与恢复1、数据备份的重要性2、数据库备份的分类2-1 从物理与逻辑的角度，备份可以分为2-2 从数据库的备份策略角度，备份可分为3、常见的备份方法4、MySQL完全备份4-1 概念4-2 完全备份的优缺点4-3 数据库完全备份分类三、数据库冷备份与恢复及完全备份与恢复的基本命令四、MySQL 增量备份与恢复的方法1、MySQL 增量备份2、MySQL 增量恢复 一、MySQL的日志管理 MySQL 的日志默认
Mysql数据恢复操作
qq_35525666的博客
09-06 459
Mysql数据恢复操作 1、登录mysql,执行如下命令： show variables like ‘%log_bin%’; 查看：log_bin 是开启状态，未开启需要开启然后执行后续操作 2、查看binlog日志, 取最新的一个 mysql-bin.XXX show binary logs; 3、查看上述binlog日志文件所在目录： show variables like ‘%datadir%’; 4、退出mysql,然后根据大概需要恢复的数据时间点执行: mysqlbinlog --base64-o
【数据库数据恢复】MySQL数据库误删除未备份的数据恢复案例
beiya123的博客
12-07 2496
MySQL数据库数据恢复环境： 本地服务器，操作系统为windows server； 部署mysql单实例，引擎类型为innodb，表空间类型为独立表空间； 没有数据库备份，未开启binlog。 MySQL数据库故障： 用户使用Delete命令删除数据时未添加where子句进行筛选，导致全表数据被删除，删除后再未对该表做任何操作。
【MySql】Mysql之备份与恢复
种一颗树最好的时间是十年前，其次是现在！
08-15 937
mysql的日志默认保存位置为：/usr/local/mysql/data 下面二进制日志由三种不同的记录格式①STTATEMENT（基于sql语句）每一条涉及到被修改的sql语句都会记录在binlog中缺点日志量过大，如sleep（）函数，last_insert_id()>（聚合函数）,以及user-definedfuctions(udf)、垂从复制等架构记录日志时会出现问题。...
【黄啊码】MySQL入门—17、在没有备份的情况下，如何恢复数据库数据？
黄啊码
10-30 2232
在没有备份的情况下，如何恢复mysql数据库数据？
MySQL数据库备份与恢复：全面指南
萧鼎的博客
10-08 4731
MySQL数据库的备份与恢复是确保数据安全的核心环节。在选择备份策略时，需要考虑数据库的大小、性能需求、可用性要求等因素。对于中小型数据库，逻辑备份简单易行，适合频繁的备份需求。而对于大型数据库，物理备份效率更高。无论采用哪种方式，定期备份和验证备份的可恢复性都是最佳实践。通过本篇文章，我们学习了MySQL数据库的逻辑备份与物理备份的操作方法，以及如何在不同情况下恢复数据。这些技术对于保障数据库的安全性和可靠性至关重要。MySQL数据库的备份与恢复是数据库管理中的关键任务。
【Mysql】数据库备份与恢复
GB100200的博客
04-05 2658
备份方法我将从物理冷备、mysqldump的逻辑热备、增量备份、断点备份这几个方面介绍使用方法以及语句。4、使用第三方备份工具 Percona XtraBackup（简称PXB） （完全备份、增量备份、物理热备）3、增量备份：只备份上一次完全备份或增量备份后的更新数据，即每次都只备份最新的数据。2、使用mysql自带的备份工具 mysqldump（完全备份、逻辑热备）1、完全备份：也叫全量备份，每次都备份完整的数据库。2、差异备份：只备份上一次完全备份后的更新数据。3、刷新二进制日志（增量备份）
MySql数据备份与恢复操作指南
综合以上知识点，可以看出，MySql数据备份恢复涉及到的技术和策略比较复杂，需要综合考虑自动化备份的实现、手动恢复的操作步骤、工具软件的使用以及备份恢复的高级策略等多个方面。在实施过程中，确保备份的及时性...
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
表情包 代码片
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值