Linux中的mysql逻辑备份与恢复

article/2025/6/23 12:47:33

一、安装mysql社区服务

二、数据库的介绍

三、备份类型和备份工具

一、安装mysql社区服务

这是小编自己写的，没有安装的去看看

Linux换源以及yum安装nginx和mysql-CSDN博客

二、数据库的介绍

2.1 数据库的组成

数据库是一堆物理文件的集合，主要包括：

  1. 数据文件 /var/lib/mysql

  2. 配置文件 => /etc/my.cnf

  3. 日志文件（主要是二进制日志文件）

2.2 存储引擎层

简单来说，就是数据的存储方式。在MySQL中，我们可以使用show engines查看当前数据库版本支持哪些引擎。

常见的数据存储引擎：InnoDB、MyISAM、NDB等。

MyISAM与InnoDB引擎的区别

  1. MyISAM引擎：

    • 擅长数据的查询，支持全文索引。

  2. InnoDB引擎：

    • 支持事务处理、行级锁、支持外键。==5.7也是支持全文索引==

2.3 存储层（数据文件与日志文件 - InnoDB）

存储引擎的数据文件存储方式

首先创建一个数据库：

create database lsh DEFAULT CHARSET=utf8;

MyISAM引擎的文件存储

mysql> USE lsh;
mysql> CREATE TABLE tb_user(id INT, name CHAR(1)) ENGINE=MyISAM DEFAULT CHARSET=utf8;

InnoDB引擎的文件存储

mysql> USE lsh;
mysql> CREATE TABLE tb_user(id INT, name CHAR(1)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.4 日志文件

日志类型写入日志的信息
错误日志 (error log)启动、运行或停止 mysqld 时遇到的问题
通用查询日志 (general log)服务器收到的所有客户端连接和语句
二进制日志 (binary log)数据更改语句 (更新、删除、更改表结构)
慢查询日志 (slow query log)执行时间超过特定阈值的查询语句
DDL日志 (元数据日志)由DDL语句执行的元数据操作
二进制日志

作用：

二进制日志记录数据库的所有更改操作（DDL/DML/DCL），不包含select或者show这类语句。

场景：

  1. 用于主从复制：在主从复制中，master主服务器将二进制日志中的更改操作发送给slave从服务器，从服务器执行这些更改操作是的和主服务器的更改相同。

  2. 用于数据的恢复：用于数据的恢复操作。

默认binlog日志是关闭的，可以通过修改配置文件完成开启，如下：

# vim /etc/my.cnf[mysqld]
log-bin=/usr/local/mysql/data/binlog
server-id=1

当我们更改了my.cnf配置文件，一定要记得重启MySQL服务器。service命令

systemctl restart mysqld

二进制日志比较特殊，需要使用 mysqlbinlog 工具查看，如 mysqlbinlog 二进制日志文件。

mysqlbinlog 【文件名】

三、备份类型和备份工具

3.1 备份和冗余的区别

  • 备份：能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。

  • 冗余：数据有多份冗余，但不等于备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。

3.2 备份类型

逻辑备份（mysqldump）

  • 备份的是建表、建库、插入等操作所执行SQL语句（DDL,DML,DCL）。

  • 适用于中小型数据库，效率相对较低一般。在数据库正常提供服务的前提下进行，如：mysqldump、mydumper等。

  • 备份实质：就是把要备份的数据导出成.sql或.txt文件

物理备份（tar、cp、xtrabackup）

  • 直接复制数据库文件

  • 适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。

  • 一般是在数据库彻底关闭或者不能完成正常提供服务的前提下进行的备份；

  • 如：tar、cp、xtrabackup（数据库可以正常提供服务lvm，snapshot、rsync等)

  • 备份的实质：对数据文件+配置文件+日志文件进行拷贝操作

在线热备（数据冗余、AB复制、主从复制）

  • MySQL的replication架构，如M-S|M-S-S|M-M-S等

  • 实时在线备份

3.3 备份工具

社区版安装包中的备份工具

mysqldump（逻辑备份，只能全量备份)

1）企业版和社区版都包含

2）本质上使用SQL语句描述数据库及数据并导出

3）在MYISAM引|擎上锁表，Innodb引擎上锁行

4）数据量很大时不推荐使用

mysql hotcopy（物理备份工具）

1）企业版和社区版都包含

2）perl写的一个脚本，本质上是使用锁表语句后再拷贝数据

3)只支持MYISAM数据引擎

企业版安装包中的备份工具

mysqlbackup

1）在线备份

2）增量备份

3）部分备份

4）在某个特定时间的一致性状态的备份

第三方备份工具

XtraBackup

Xtrabackup是一个对InnoDB做数据备份的工具，支持寺在线热备份（备份时不影响数据读写），是商业备份工具InnoDBHotbackup的一个很好的替代品。

Xtrabackup有两个主要的工具：xtrabackup、innobackupex

xtrabackup只能备份InnoDB和XtraDB3两种数据表，不能备份myisam类型的表。

innobackupex是将Xtrabackup进行封装的per脚本，所以能同时备份处理innodb和myisam的存储引擎，但在处理myisam时需要加一个读锁。

3.3 mysqldump工具（逻辑备份工具）

本质：导出的是sql语句文件

优点：无论是什么存储引擎，都可以用mysqldump备成sql语句

缺点：速度较慢，导入时可能会出现格式不兼容的突发状况

无法直接做增量备提供三种级别的备份，表级，库级和全库级

mysqldump基本语法

表级别备份
mysqldump [OPTIONS] database [tables] > b.sql
库级别备份
mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]
全库级别备份
mysqldump [OPTIONS] --all-databases [OPTIONS]

准备一些要备份的数据：

-- 创建数据库
mysql> CREATE DATABASE db_it DEFAULT CHARSET=utf8;-- 使用数据库
mysql> USE db_it;-- 创建表
CREATE TABLE tb_student (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(20),age TINYINT UNSIGNED DEFAULT 0,gender ENUM('male', 'female'),subject ENUM('ui', 'java', 'yunwei', 'python'),PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- 插入数据
INSERT INTO tb_student VALUES (NULL, '刘备', 33, 'male', 'java'),(NULL, '关羽', 32, 'male', 'yunwei'),(NULL, '张飞', 30, 'male', 'python'),(NULL, '貂蝉', 18, 'female', 'ui'),(NULL, '大乔', 18, 'female', 'ui');

mysqldump表级备份与还原

备份

案例：把db_it数据库中的tb_student数据表进行备份

# mkdir /bak
# mysqldump -uroot -p db_it tb_student > /bak/tb_student.sql

还原

-- 还原之前先把表删了
drop table tb_studnet;# mysql -u root -pmysql> use db_it
mysql> source /bak/tb_student.sql

mysqldump库级备份与还原

备份

案例：把db_it数据库进行备份

# mysqldump -uroot -p --databases db_it > /bak/db_it.sql

还原

drop database db_it;# mysql -uroot -pmysql> source /bak/db_it.sql

mysqldump全库级备份

在MySQL中，如果想使用mysqldump进行全库级备份，必须开启二进制日志！！！

开启二进制日志

vim /etc/my.cnfserver-id=1
log-bin=/mysql_3306/data/binlog

# mysqldump -uroot -p --all-databases --master-data --single-transaction > /bak/all.sql
选项描述说明
--flush-logs, -F开始备份前刷新日志（二进制日志）binlog.000001 => binlog.000002
--flush-privileges备份包含mysql数据库时刷新授权表 => 刷新用户和授权信息
--lock-all-tables, -xMyISAM一致性，服务可用性（针对所有库所有表）
--lock-tables, -l备份前锁表（针对要备份的库）
--single-transaction适用InnoDB引擎，保证一致性，服务可用性
--master-data=2表示将二进制日志位置和文件名写入到备份文件并在dump文件中注释掉这一行
--master-data=1表示将二进制日志位置和文件名写入到备份文件，在dump文件中不注释这一行

3.4 实现增量备份（重要）

增量备份的核心思路

增量备份的关键：

  1. 要有全量备份作为基础

  2. 继续增删改数据

  3. 再次需要备份时，不需要进行全量备份，只需要备份binlog日志文件即可（因为binlog日志记录了增删改操作的所有SQL语句）

增量备份实验步骤

第一步：先准备数据
-- 创建数据库
CREATE DATABASE db_it DEFAULT CHARSET=utf8;-- 使用数据库
USE db_it;-- 创建表
CREATE TABLE tb_student (id INT NOT NULL AUTO_INCREMENT,name VARCHAR(20),age TINYINT UNSIGNED DEFAULT 0,gender ENUM('male', 'female'),subject ENUM('ui', 'java', 'yunwei', 'python'),PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;-- 插入数据
INSERT INTO tb_student VALUES (NULL, '刘备', 33, 'male', 'java'),(NULL, '关羽', 32, 'male', 'yunwei'),(NULL, '张飞', 30, 'male', 'python'),(NULL, '貂蝉', 18, 'female', 'ui'),(NULL, '大乔', 18, 'female', 'ui');
第二步：开启二进制日志，重启服务，然后进行全库备份
# 编辑 MySQL 配置文件
vim /etc/my.cnf# 添加以下配置
[mysqld]
server-id=10
log-bin=/mysql_3306/data/binlog# 重启 MySQL 服务
systemctl restart mysqld# 清理临时备份文件
rm -rf /tmp/sqlbak/*# 备份所有数据库
mysqldump \
--single-transaction \
--flush-logs \
--master-data=2 \
--all-databases \
> /bak/a.sql \
-p
第三步：继续对数据库进行增删改操作
-- 插入新记录
INSERT INTO tb_student VALUES (NULL, '小乔', 16, 'female', 'ui');
-- 删除记录
DELETE FROM tb_student WHERE id = 3;
第四步：突然发生了硬件故障，数据库丢失了
# 模拟故障情况，删除数据库
mysql -e "DROP DATABASE db_it;" -p
# 输入密码: 123
第五步：动员运维工程师开始进行数据恢复，马上把最新的二进制文件进行备份
# 复制二进制日志文件到备份目录
cp /usr/local/mysql/data/binlog.000003 /bak
第六步：先进行全库恢复
# 恢复全量备份
mysql < /tmp/sqlbak/all.sql -p
# 输入密码: 123

第七步：通过binlog增量备份还原数据到100%

学会读二进制日志文件，必须通过专业的工具

# 使用 mysqlbinlog 解析二进制日志文件，定位关键操作
/mysqlbinlog /bak/binlog.000003
# 重点关注事故临界点（如 DROP DATABASE）# 确认日志中操作位置的起始和结束偏移量
# 使用指定位置范围恢复数据
mysqlbinlog --start-position=201 --stop-position=629 /bak/binlog.000003 | mysql -uroot -p

http://www.hkcw.cn/article/IlvWycKktt.shtml

相关文章

mysql（十四）

mysql（十四）

目录 多表查询 1.准备工作 2--创建表格 3--插入数据 2.笛卡尔积查询 3.内连接查询 1--隐式内连接 格式 查询 2--显示内连接&#xff08;Inner join .. on &#xff09; 格式 查询 4.外连接查询 1--左外连接查询&#xff08;LEFT OUTER JOIN .. ON &#xff09; 格式 查询 2-- 右…
阅读更多...
莫言谈给冯巩起名“闭嘴”原因 饭局趣事揭秘

莫言谈给冯巩起名“闭嘴”原因 饭局趣事揭秘

莫言谈给冯巩起名“闭嘴”原因 饭局趣事揭秘！6月2日，作家莫言在个人社交账号上分享了一段趣事。他提到前年冬天在一个饭局上初次见到相声演员冯巩。冯巩握住莫言的手称赞他的小说写得好，并表示这得益于一个好笔名。冯巩还透露自己最近也想尝试写作，希望莫言能为他起个笔名。…
阅读更多...
Windows采用npx方式本地部署n8n

Windows采用npx方式本地部署n8n

windows本地部署n8n 1.安装node.js n8n Github首页: https://github.com/n8n-io/n8n 2.解决npx报错 Windows PowerShell 版权所有 (C) Microsoft Corporation。保留所有权利。尝试新的跨平台 PowerShell https://aka.ms/pscore6PS C:\WINDOWS\system32> npx -v npx : 无…
阅读更多...
存款10万不敢生病的中年人有多难？这是一个残酷的事实！

存款10万不敢生病的中年人有多难？这是一个残酷的事实！

人到了中年，就会遇到更多的危险，婚姻破裂，失业，房贷，子女的学业，这些都是他们生命中的最后一根救命稻草。不过只要有足够的资金，一切都会迎刃而解。问题是，一个拥有十万元存款的中年人能否应付中年危机？有人说，这是一个残酷的事实。一、存款10万元意味着什么？说实话…
阅读更多...
通讯录Linux的实现

通讯录Linux的实现

产品底层思考&#xff1a; 人员如何存储 -> 链表 &#xff08;增删改 但是排序不适合&#xff09;文件存储 -> 人员数据的格式 name:xxx,phone:xxx人员信息 -> 姓名、电话 引出2 name:xxx,phone:xxx,age:xxx,addr,xxx name:yyy,phone:yyy,age:yyy,addr,yyy 实现通讯录…
阅读更多...
Python：操作 Excel 格式化

Python：操作 Excel 格式化

🔧Python 操作 Excel 格式化完整指南（openpyxl 与 xlsxwriter 双方案） 在数据处理和报表自动化中，Python 是一把利器，尤其是配合 Excel 文件的读写与格式化处理。本篇将详细介绍两大主流库： openpyxl：适合读取与修改现有 Excel 文件xlsxwriter：适合创建新文件并进行复…
阅读更多...
阿里云服务器ECS详解：云服务器是什么，云服务器优势和应用场景及参考

阿里云服务器ECS详解：云服务器是什么，云服务器优势和应用场景及参考

云服务器ECS是阿里云众多云产品中&#xff0c;最受用户关注的产品&#xff0c;阿里云服务器提供多样化的计算能力&#xff0c;支持x86、Arm架构&#xff0c;涵盖CPU、GPU等多种服务器类型&#xff0c;满足各种用户需求。其便捷易用特性包括分钟级交付、通用API和性能监控框架&a…
阅读更多...
【笔记】Windows 下载并安装 ChromeDriver

【笔记】Windows 下载并安装 ChromeDriver

以下是 在 Windows 上下载并安装 ChromeDriver 的笔记&#xff1a; ✅ Windows 下载并安装 ChromeDriver 1️⃣ 确认 Chrome 浏览器版本 打开 Chrome 浏览器 点击右上角 ︙ → 帮助 → 关于 Google Chrome 记下版本号&#xff0c;例如&#xff1a;114.0.5735.199 2️⃣ 下载…
阅读更多...
ABP-Book Store Application中文讲解 - Part 4: Integration Tests - TBD

ABP-Book Store Application中文讲解 - Part 4: Integration Tests - TBD

ABP-Book Store Application中文讲解 - Part 4: Integration Tests 本章用于介绍如何做集成测试。 1. 汇总 ABP-Book Store Application中文讲解-汇总-CSDN博客 2. 前一章 ABP-Book Store Application中文讲解 - Part 3: Creating, Updating and Deleting Books 项目之间…
阅读更多...
房屋租赁系统 Java+Vue.js+SpringBoot，包括房屋类型、房屋信息、预约看房、合同信息、房屋报修、房屋评价、房主管理模块

房屋租赁系统 Java+Vue.js+SpringBoot，包括房屋类型、房屋信息、预约看房、合同信息、房屋报修、房屋评价、房主管理模块

房屋租赁系统 JavaVue.jsSpringBoot&#xff0c;包括房屋类型、房屋信息、预约看房、合同信息、房屋报修、房屋评价、房主管理模块 百度云盘链接&#xff1a;https://pan.baidu.com/s/1KmwOFzN9qogyaLQei3b6qw 密码&#xff1a;l2yn 摘 要 社会的发展和科学技术的进步&#xf…
阅读更多...
用wireshark抓包分析学习USB协议

用wireshark抓包分析学习USB协议

用 wireshark 抓包分析学习 USB 协议 1 框架 上图是使用USB协议通信的两个设备的实现细节。看起来比较复杂&#xff0c;可以看下面的示意图&#xff1a; 整个框架分为三层&#xff1a;Function Layer、USB Device Layer 和 USB Bus Interface Layer&#xff1b;黑色双向箭头代…
阅读更多...
PyTorch——卷积操作（2）

PyTorch——卷积操作（2）

二维矩阵 [[ ]] 这里面conv2d(N,C,H,W)里面的四个是 N就是batch size也就是输入图片的数量&#xff0c;C就是通道数这只是一个二维张量所以通道为1&#xff0c;H就是高&#xff0c;W就是宽&#xff0c;所以是1 1 5 5 卷积核 reshape 第一个参数是batch size样本数量 第二个参数…
阅读更多...
加拿大没邀请，莫迪不去G7 双边关系紧张所致

加拿大没邀请，莫迪不去G7 双边关系紧张所致

据知情人士透露，由于新德里与渥太华关系冷淡，印度总理莫迪或将缺席6月中旬在加拿大举行的七国集团（G7）峰会。这可能将是莫迪六年来首次缺席该峰会。消息人士表示，对于加拿大将于6月15日至17日主办的G7峰会，印方迄今未收到加方正式邀请，且印方也没有表明参加意愿。在进行…
阅读更多...
6岁女童被男子拖进小巷 警方通报 嫌疑人已被拘留

6岁女童被男子拖进小巷 警方通报 嫌疑人已被拘留

6月3日，湖南娄底市公安局娄星分局通报了一起事件。5月20日下午，嫌疑人刘某某（男，38岁）酒后行至娄底市娄星区涟滨中街附近，拖拽一名6岁女童进入居民区巷子。幸好被居民及时制止并报警。接警后，民警迅速赶到现场将刘某某控制。经侦查，刘某某涉嫌寻衅滋事已被拘留，并将依…
阅读更多...
央视披露“镇国神器”东风-5参数，网友：40多年前这水平

央视披露“镇国神器”东风-5参数，网友：40多年前这水平

央视披露“镇国神器”东风-5参数。6月2号，央视新闻报道了一条让人非常振奋而又非常炸裂的消息。那就是直接公布的我们在40多前就已经研发和发射成功的东风-5洲际导弹的具体参数，消息一出，引发全网强烈关注和热烈。我们知道我们很厉害，但是不知道在40多年前我们的镇国利器，…
阅读更多...
警方通报“男子拖拽女童”：追究刑责！

警方通报“男子拖拽女童”：追究刑责！

警方通报“男子拖拽女童”：追究刑责事件持续发酵。6月3日，湖南娄底市公安局娄星分局通报，5月20日下午，嫌疑人刘某某（男，38岁）酒后行至娄底市娄星区涟滨中街附近，拖拽一女童（6岁）进入居民区巷子，后被居民及时制止并报警。接警后，民警迅速赶到现场将刘某某控制。经侦…
阅读更多...
黄金大涨“带火”铂金：有人百万资金囤购 铂金市场热度飙升

黄金大涨“带火”铂金：有人百万资金囤购 铂金市场热度飙升

黄金大涨“带火”铂金：有人百万资金囤购 铂金市场热度飙升！铂金市场近期出现显著上涨行情，年内累计最大涨幅达到25%。这一波“复兴”浪潮迅速从深圳水贝这一珠宝中心蔓延开来，引发了投资者和买家的广泛关注。陈女士是一位热衷于投资的投资者，她最近在铂金上进行了大量投资…
阅读更多...
【 HarmonyOS 5 入门系列 】鸿蒙HarmonyOS示例项目讲解

【 HarmonyOS 5 入门系列 】鸿蒙HarmonyOS示例项目讲解

【 HarmonyOS 5 入门系列 】鸿蒙HarmonyOS示例项目讲解 一、前言&#xff1a;移动开发声明式 UI 框架的技术变革 在移动操作系统的发展历程中&#xff0c;UI 开发模式经历了从命令式到声明式的重大变革。 根据华为开发者联盟 2024 年数据报告显示&#xff0c;HarmonyOS 设备…
阅读更多...
蒋雨融回应在哈佛毕业典礼上的演讲 堂堂正正争取话语权

蒋雨融回应在哈佛毕业典礼上的演讲 堂堂正正争取话语权

蒋雨融回应在哈佛毕业典礼上的演讲 堂堂正正争取话语权！6月2日晚，蒋雨融Luanna在微博上回应了关于哈佛演讲的争议。她表示，无论是争取成为哈佛毕业致辞代表，还是在各个平台上开设账号，都是为了争夺公共话语空间。她认为，如果中国学生不去争取这个空间，就会被其他学生占据…
阅读更多...
Linux服务器安装GUI界面工具

Linux服务器安装GUI界面工具

注意&#xff1a; 本文内容于 2025-05-31 18:55:23 创建&#xff0c;可能不会在此平台上进行更新。如果您希望查看最新版本或更多相关内容&#xff0c;请访问原文地址&#xff1a;Linux服务器安装GUI界面工具。感谢您的关注与支持&#xff01; 本文记录在Linux使用Docker安装G…
阅读更多...
推荐文章

Copyright @ 2022~2023