深入解析MySQL(8)——核心日志与备份恢复

Ne0inhk

— 14 min read
深入解析MySQL(8)——核心日志与备份恢复

1.二进制日志

1.1 概述

作用：二进制日志(Binary Log)以二进制格式存储，记录所有修改数据库数据的SQL语句(如insert、update、delete)或事件(如表结构变更)

核心功能：

  • 主从复制：主库通过二进制日志将数据变更同步到从库
  • 数据恢复：配合MySQL 自带的二进制日志解析工具mysqlbinlog，可将二进制日志转换为 SQL 语句并执行

配置：

会话级配置：在命令行客户端中设置变量session sql_log_bin，仅本次连接生效

-- 1 -> 开启 -- 0 -> 关闭 mysql>setsession sql_log_bin =[1|0]; mysql>show variables like'%sql_log_bin%';+---------------+-------+| Variable_name |Value|+---------------+-------+| sql_log_bin |ON|+---------------+-------+

系统级配置：使用Vim 编辑器编辑MySQL的配置文件(vim /etc/mysql/my.cnf)，永久生效

在这里插入图片描述

1.2 磁盘文件

  • 二进制日志文件名由 基本名 + 数字扩展名 组成，每生称新的文件时数字扩展名递增，从而保证有序的文件序列。当发生以下情况时会生成新的日志文件：
    • 服务器重启

可以使用reset master重置日志文件和索引文件为初始状态

在这里插入图片描述

日志文件的大小达到 max_binlog_size设置的上线(默认值1GB)

在这里插入图片描述

在命令行客户端刷新日志

在这里插入图片描述

1.3 过期时间

查看系统变量

mysql>show variables like'%binlog_expire_logs_seconds%';+----------------------------+---------+| Variable_name |Value|+----------------------------+---------+| binlog_expire_logs_seconds |2592000|+----------------------------+---------+

在配置文件my.cnf中配置日志文件的过期时间，默认为2592000秒(30天)，过期后会自动删除

#MySQL服务器配置 [mysqld] #二进制日志过期时间 binlog_expire_logs_seconds=2592000

1.4 日志格式

  • statement：记录的是 SQL 语句本身，即实际执行的 SQL 语句
    • 优点：仅记录 SQL 语句，占用空间少
    • 缺点：某些情况下可能导致主从数据不一致，例如使用非确定性函数(如 now() 或 rand() )时，主从服务器执行结果可能不同
  • row：记录的是每一行数据的变更情况，即具体哪些行被修改以及修改后的值
    • 优点：数据一致性高，避免了非确定性函数的问题，适用于复杂的复制场景
    • 缺点：日志文件较大，尤其是批量操作时，会记录大量行变更信息
  • mixed：结合了statement和row格式的优点。默认情况下使用statement格式记录，但在某些可能导致不一致的场景下自动切换为row格式
    • 优点：平衡了日志大小和数据一致性
    • 缺点：仍需注意某些特殊情况下可能存在的一致性问题
推荐使用row格式

1.5 刷盘策略

在这里插入图片描述


binlog日志文件的刷盘策略可以通过sync_binlog系统变量来设置

  • sync_binlog=0：由操作系统决定刷盘时机。性能最高，但宕机时可能丢失较多binlog数据
  • sync_binlog=1(推荐)：每次事务提交时都会刷盘，确保binlog不丢失。安全性最高，但性能较低
  • sync_binlog=N(N>1)：每N次事务提交后刷盘一次。平衡性能与安全性

1.6 mysqlbinlog工具

简介：mysqlbinlog是MySQL自带的二进制日志解析工具，用于查看和管理MySQL的二进制日志文件(binlog)
主要功能：

  • 查看二进制日志内容：将二进制日志转换为可读的文本格式
  • 过滤日志事件：按时间、位置或数据库名筛选特定日志记录
  • 生成SQL脚本：将日志内容还原为SQL语句，用于数据恢复
  • 远程解析：支持解析远程MySQL服务器的二进制日志
postion：每条日志都以 # at 开始，后⾯的数字表示该条日志记录的事件在文件中的偏移量timestamp：事件发生的时间戳server id：服务器标识end_log_pos：下一个事件在文件中的偏移量(等于当前事件的结束偏移量+1)type：事件类型(Query)thread_id：执行事件的线程idexec_time：执行事件花费的时间error_code：错误码(0表示没有错误)

1.6.1 准备数据

-- 建库dropdatabaseifexists testdb;createdatabase testdb characterset utf8mb4 collate utf8mb4_0900_ai_ci;use testdb -- 建表createtable t1 ( id bigintnotnull, name varchar(20)notnull);-- 写⼊insertinto t1 (id, name)values(101,'user101');insertinto t1 (id, name)values(102,'user102');insertinto t1 (id, name)values(103,'user103');insertinto t1 (id, name)values(104,'user104');insertinto t1 (id, name)values(105,'user105');insertinto t1 (id, name)values(106,'user106');-- 更新update t1 set name ='person101'where id =101;update t1 set name ='person102'where id =102;update t1 set name ='person103'where id =103;-- 删除deletefrom t1 where id =104;deletefrom t1 where id =105;deletefrom t1 where id =106;

1.6.2 查看binlog

mysqlbinlog --no-defaults --database=testdb --base64-output=decode-rows -vv --start-position=421 --stop-position=4626 binlog.000001
  • no-defaults：禁止读取默认配置文件(如my.cnf)，确保命令执行不受配置文件参数干扰
  • database：限定只显示与指定数据库相关的日志记录，过滤其他库的操作
  • base64-output=decode-rows：对二进制日志中的行事件(row格式日志)进行Base64解码，否则这类事件会以Base64编码形式显示
  • vv：双倍详细模式(verbose level 2)，输出最完整的日志信息
  • start-position：从二进制日志文件的指定位置开始解析
  • stop-position：在二进制日志文件的指定位置停止解析

1.6.3 数据恢复

通过日志文件恢复数据

mysqlbinlog --no-defaults --skip-gtids=true --start-position=234 --stop-position=4470 binlog.000001 | mysql -uroot -p -h127.0.0.1 -P3306

删除数据库

dropdatabase testdb;
在这里插入图片描述

2.数据备份与恢复

2.1 分类

2.1.1 按数据存储形式划分

    • 备份文件中包含SQL语句，稍加修改就可以在不同数据库系统上执行
    • 可以备份整个数据库或特定的数据库对象(使用database关键字创建的对象)
    • 备份和恢复的速度比物理备份慢
    • 通常不能跨数据库厂商进行备份与恢复
    • 可以非常快速地备份和恢复大型数据库，因为不需要解析SQL语句，直接复制文件即可

物理备份：直接复制数据库的物理文件(如数据文件、日志文件)。这种备份方法不涉及数据库的逻辑结构，而是直接在文件系统层面上复制数据库的存储结构(相当于Windows系统的Ctrl + C)

在这里插入图片描述

逻辑备份：备份数据库的逻辑结构和数据内容(如SQL语句、导出文件)，与物理存储无关。例如MySQL的mysqldump

在这里插入图片描述

2.1.2 按数据库运行状态划分

  • 冷备份(离线备份)：需停止数据库服务后备份，备份过程中数据库服务不可用
    • 影响业务连续性
    • 技术实现较简单，不需要考虑数据一致性和并发控制
    • 不会对数据库性能产生影响
  • 热备份(在线备份)：在数据库运行时进行备份，备份过程中数据库服务依然可用
    • 备份数据是系统当前最新的
    • 不影响业务连续性
    • 在高负载情况下，可能对数据库性能产生一定影响
    • 技术实现复杂，需要考虑数据一致性和并发控制
  • 温备份：介于冷备份和热备份之间的一种备份方式，数据库在备份过程中部分可用或者处于只读模式
    • 业务中断时间较少

2.1.3 按备份数据范围划分

差异备份：仅备份自上次全量备份以来所有变化的数据

在这里插入图片描述

增量备份：仅备份自上次备份后变化的数据块

在这里插入图片描述

全量备份：备份整个数据库或文件系统的所有数据，包括所有的文件、数据库表和配置文件等。不依赖于其他备份，可用独立恢复数据

在这里插入图片描述

2.2 mysqldump

2.2.1 工具介绍

作用：它是MySQL数据库系统提供的命令行工具，用于逻辑备份和数据导出。它生成包含SQL语句的文本文件，可用于重建数据库结构和数据
核心功能：

  • 备份数据库：导出表结构、数据、存储过程、触发器等
  • 恢复数据：通过导入SQL文件恢复数据库状态
  • 跨版本兼容：导出的SQL文件可在不同MySQL版本间迁移

2.2.2 示例

  • 导入数据

查看数据

mysql>use testdb;Database changed mysql>select*from t1;+-----+-----------+| id | name |+-----+-----------+|101| person101 ||102| person102 ||103| person103 |+-----+-----------+

登录MySQL客户端导入SQL文件

source /backup/mysql/dump.sql

在命令行通过MySQL客户端工具直接恢复

mysql -uroot -p < /backup/mysql/dump.sql

删除数据库

dropdatabase testdb;

查看导出的SQL文件

cat /backup/mysql/dump.sql
在这里插入图片描述

导出

mysqldump -uroot -p -h127.0.0.1 -P3306 -B testdb > /backup/mysql/dump.sql

创建目录

mkdir /backup/mysql

2.2.3 数据一致性问题

  • mysqldump备份数据时的执行流程如下
    • 连接数据库
    • 收集需要备份的数据
    • 对所有待备份表加读锁
    • 生成数据插入语句
    • 释放锁
  • 备份过程中可能会产生数据一致性问题
    • mysqldump在备份时默认情况下不会回滚未提交的事务，因此备份可能包含未提交的事务中的数据更改
    • 如果在加读锁后发生事务回滚，备份结果备份可能包含部分事务的中间状态

解决办法：使用--single-transaction，在事务中备份，使用MVCC获取一致性视图

在这里插入图片描述

2.3 mysqlimport

2.3.1 工具介绍

作用：它是MySQL数据库系统提供的命令行工具，用于高效地将文本文件数据导入到数据库表中。它是load data infile语句的封装，适用于批量数据加载场景
核心功能：

  • 快速导入：直接读取文件并加载到表，跳过SQL解析环节，性能优于逐行insert

2.3.2 示例

    • 查看数据

导入

mysqlimport -uroot -p testdb /var/lib/mysql-files/t1.txt 
mysql>select*from t1;+-----+-----------+| id | name |+-----+-----------+|101| person101 ||102| person102 ||103| person103 |+-----+-----------+

删除数据表

deletefrom t1;

查看导出的文本文件

在这里插入图片描述

导出

select*from t1 intooutfile'/var/lib/mysql-files/t1.txt';

查看MySQL允许导出的授权目录

mysql>show variables like'secure_file_priv';+------------------+-----------------------+| Variable_name |Value|+------------------+-----------------------+| secure_file_priv |/var/lib/mysql-files/|+------------------+-----------------------+

2.4 Xtrabackup

2.4.1 工具介绍

作用：Xtrabackup 是由 Percona 开发的一款开源MySQL数据库备份工具。它通过热备份实现高性能的数据库备份与恢复，适用于大规模生产环境
核心功能：

  • 热备份：在不中断数据库服务的情况下进行备份
  • 压缩与加密：支持备份文件的压缩和加密，提升安全性和存储效率
  • 快速可靠：备份速度快且可靠，同时会对备份的数据进行自动校验，确保备份数据的完整性
  • 性能影响小：在备份过程中，Xtrabackup对数据库的性能影响较小，不会增加太多的性能压力

官网：Xtrabackup下载网址

版本选择：XtraBackup 2.4：支持 MySQL 5.1、5.5、5.6 和 5.7，但不支持 MySQL 8.0XtraBackup 8.0：专为 MySQL 8.0 设计，但早期版本(如 8.0.12)不支持 MySQL 8.0.20 及以上版本若使用 MySQL 8.0.20 及以上版本，建议选择 XtraBackup 8.0.27-19 或更高版本

查看CPU架构

在这里插入图片描述

查看MySQL版本

在这里插入图片描述

查看Linux系统版本

在这里插入图片描述

安装软件源

  • 把在Windows系统上下载完毕的percona-xtrabackup-80_8.0.35-34-1.noble_amd64.deb文件上传至Linux服务器

验证是否安装成功

在这里插入图片描述

安装软件源

# 安装软件源 dpkg -i percona-xtrabackup-80_8.0.35-34-1.noble_amd64.deb # 更新源 apt update # 安装xtrabackup apt install percona-xtrabackup-80 # 如果提示缺少依赖运行以下命令安装 apt-get install -f # 更新源 apt update # 安装xtrabackup apt install percona-xtrabackup-80

2.4.2 示例

    • 默认情况下密码策略要求密码包含大小写字母、数字和特殊字符
    • xtrabackup --prepare 命令用于将备份数据转换为可恢复的数据库状态。该步骤对物理备份文件执行事务日志回放和回滚未提交事务，确保数据文件的一致性

登录数据库验证数据

mysql>use testdb;No connection. Trying to reconnect... Connection id: 8Currentdatabase: *** NONE *** Reading table information for completion oftableandcolumn names You can turn off this feature to get a quicker startup with-A Database changed mysql>select*from t1;+-----+-----------+| id | name |+-----+-----------+|101| person101 ||102| person102 ||103| person103 |+-----+-----------+

重启MySQL服务

systemctl restart mysql

为恢复后的数据目录授权

chown -R mysql:mysql /var/lib/mysql
在这里插入图片描述

数据恢复

# 准备 xtrabackup --prepare --target-dir=/backup/mysql/full # 数据恢复 xtrabackup --defaults-file=/etc/mysql/my.cnf --copy-back --parallel=2 --target-dir=/backup/mysql/full

创建数据目录同名的空目录

在这里插入图片描述

移动或删除原来的的数据目录

在这里插入图片描述

备份数据文件

xtrabackup --defaults-file=/etc/mysql/my.cnf --host=localhost --port=3306 --user=backup_user --password=123456Aa@@ --use-memory=1G --parallel=2 --backup --target-dir=/backup/mysql/full
在这里插入图片描述

查看需要备份的目录

在这里插入图片描述

创建备份用户

mysql>createuser'backup_user'@'localhost' identified with mysql_native_password by'123456Aa@@'; Query OK,0rows affected (0.00 sec) mysql>grant backup_admin,process,select,reload,locktables,replication client,event on*.*to'backup_user'@'localhost'; Query OK,0rows affected (0.00 sec)

Read more

Qwen3+Qwen Agent 智能体开发实战，打开大模型MCP工具新方式！（一）

Qwen3+Qwen Agent 智能体开发实战，打开大模型MCP工具新方式！（一）

系列文章目录 一、Qwen3+Qwen Agent 智能体开发实战，打开大模型MCP工具新方式！（一） 二、Qwen3+Qwen Agent +MCP智能体开发实战（二）—10分钟打造"MiniManus" 前言 要说最近人工智能界最火热的开源大模型，必定是阿里发布不久的Qwen3系列模型。Qwen3模型凭借赶超DeepSeek-V3/R1的优异性能，创新的混合推理模式，以及极强的MCP能力迅速成为AI Agent开发的主流基座模型。大家可参考我的文章一文解析Qwen3大模型详细了解Qwen3模型的核心能力。有读者私信我： “Qwen3官网特地强调增强了Agent和代码能力，同时加强了对MCP的支持，那么我该如何利用Qwen3快速开发MCP应用呢？” 这就就需要使用我们今天的主角——Qwen官方推荐的开发工具Qwen-Agent ，本期分享我们就一起学习快速使用Qwen3+QwenAgent 接入MCP服务端，快速开发AI Agent应用！ 一、注册 Qwen3 API-Key 本次分享通过阿里云百炼大模型服务平台API Key请求方式调用Qwen3大模型，获取服务平台

By Ne0inhk
Python实现 MCP 客户端调用（高德地图 MCP 服务）查询天气示例

Python实现 MCP 客户端调用（高德地图 MCP 服务）查询天气示例

文章目录 * MCP 官网 * MCP 官方文档中文版 * 官方 MCP 服务示例 * Github * MCP 市场 * 简介 * 架构 * 高德地图 MCP 客户端示例 * python-sdk 客户端 * java-sdk 客户端 MCP 官网 * https://modelcontextprotocol.io/introduction MCP 官方文档中文版 * https://app.apifox.com/project/5991953 官方 MCP 服务示例 * https://github.com/modelcontextprotocol/servers Github * python-sdk：https://github.com/modelcontextprotocol/python-sdk * java-sdk：

By Ne0inhk
43-dify案例分享-MCP-Server让工作流秒变第三方可调用服务

43-dify案例分享-MCP-Server让工作流秒变第三方可调用服务

1.前言 之前我们为大家介绍过MCP SSE插件，它能够支持MCP-server在Dify平台上的调用，从而帮助Dify与第三方平台提供的MCP-server进行无缝对接。有些小伙伴提出了疑问：既然Dify可以通过MCP SSE插件调用其他平台的MCP-server，那么Dify的工作流或Chatflow是否也能发布为MCP-server，供其他支持MCP client的工具使用呢？今天，我们将为大家介绍一款Dify插件——mcp-server，它能够实现这一功能，即将Dify的工作流或Chatflow发布为MCP-server，供其他第三方工具调用。 插件名字叫做MCP-server，我们在dify插件市场可以找到这个工具 Mcp-server 是一个由 Dify 社区贡献的 Extension 类型插件。安装后，你可以把任何 Dify 应用转变成符合 MCP 标准的 Server Endpoint，供外部 MCP 客户端直接访问。它的主要功能包括： * **暴露为 MCP 工具：**将 Dify 应用抽象为单一 MCP 工具，供外部 MCP 客户端（如

By Ne0inhk