当前位置： 首页 > news >正文

MySQL的日志和备份

news 2025/5/30 13:48:47

目录

一. MySQL的日志

1.1 日志的作用

1.2 日志的分类

1.3 事务日志

1.4 错误日志

1.5 通用日志

1.6 慢查询日志 

1.7 二进制备份

二. 备份

2.1 数据备份的重要性

2.2 备份的分类

2.3 MySQL备份的内容

2.4 备份的注意点

2.5 备份的工具

2.6 实战案例

2.7 mysqldump备份工具

1. 备份表

2. 备份数据库

3. 备份所有

4. 实战案例

2.8 xtrabackup工具

1. 安装

2. 完全备份

3. 增量备份

一. MySQL的日志

1.1 日志的作用

  1. 故障排查：通过日志可以追踪数据库操作中的错误和异常，帮助快速定位问题。
  2. 性能优化：分析日志可以了解数据库的性能瓶颈，从而进行优化。
  3. 数据恢复：某些日志类型（如二进制日志）可以用于数据恢复，特别是在数据丢失或损坏的情况下。

1.2 日志的分类

  1. 错误日志（Error Log）：

    • 描述：记录 MySQL 服务器启动、运行和停止过程中的错误信息。
    • 用途：主要用于故障排查和错误监控。

  2. 查询日志（General Query Log）：

    • 描述：记录所有客户端发送到 MySQL 服务器的 SQL 查询语句。
    • 用途：用于审计和性能分析。

  3. 慢查询日志（Slow Query Log）：

    • 描述：记录执行时间超过指定阈值的查询语句。
    • 用途：用于识别和优化执行效率低下的查询。

  4. 二进制日志（Binary Log）：

    • 描述：记录所有对数据库进行更改的 SQL 语句（如 INSERT、UPDATE、DELETE）。
    • 用途：用于数据恢复和主从复制。

  5. 中继日志（Relay Log）：

    • 描述：在主从复制中，从服务器记录从主服务器接收到的二进制日志事件。
    • 用途：用于主从复制的数据同步。

  6. 事务日志（Transaction Log）：

    • 描述：记录事务的开始、提交和回滚操作。
    • 用途：用于确保事务的原子性和持久性
    • 文件名： ib_logfile0， ib_logfile1

1.3 事务日志

事务日志相关的操作

show variables like '%innodb_log%'; #查看事务日志常见返回参数：
innodb_log_file_size：单个日志文件的大小。
innodb_log_files_in_group：日志文件组的数量。
innodb_log_buffer_size：日志缓冲区的大小。
innodb_log_compressed_pages：是否启用日志页压缩。

#通过编辑配置文件调整日志大小
vim  /etc/my.cnf[mysqld]
innodb_log_file_size=503316480
innodb_log_files_in_group=3

事务日志的优化

innodb_flush_log_at_trx_commit，用于控制事务提交时重做日志的刷新策略select @@innodb_flush_log_at_trx_commit; 
#用于查询当前该参数的设置值。innodb_flush_log_at_trx_commit=0|1|2#=0：事务提交时，日志不会立即写入磁盘，而是每秒刷新一次。这种设置性能最高，但存在数据丢失的风险（如系统崩溃时）。
#=1：事务提交时，日志会立即写入磁盘。这种设置最安全，但性能较低。
#=2：事务提交时，日志会写入操作系统的缓存，但不会立即刷新到磁盘。这种设置介于性能和安全性之间。

事务是操作的集合， 事务提交， 意味着你要把这些操作结果保存到硬盘上。 

1.4 错误日志

#查看错误日志的路径
SHOW GLOBAL VARIABLES LIKE 'log_error' ;

#指定错误日志的存放位置
vim  /etc/my.cnf
[mysqld]
log_error=/data/log_error/mysql-error.logsystemctl   restart mysqld

#如何查看错误日志SHOW VARIABLES LIKE 'log_error';
#看错误日志文件路径tail -f 错误日志路径;

1.5 通用日志

通用日志：记录对数据库的通用操作，包括:错误的SQL语句

#配置通用日志
vim  /etc/my.cnf
[mysqld]
general_log=ON
#log_output=TABLEsystemctl   restart mysqldcd /var/lib/mysqlls#查看日志
tail -f ubuntu2204.log

 

 

 

将通用日志放入数据库中

vim  /etc/my.cnflog_output=TABLEsystemctl restart mysql

 

mysql -u rootuse mysql;show tables;生成一张general_log表select * from  mysql.general_log\G

 

还会生成general_log.CSV

1.6 慢查询日志 

慢查询日志：记录执行查询时长超出指定时长的操作

#开启慢查询
vim  /etc/my.cnf
[mysqld]
slow_query_log=ON
long_query_time=5 #慢查询的阀值
log_queries_not_using_indexes=ON  
#不使用索引或使用全索引扫描，不论是否达到慢查询阀值的语句是否记录日志，默认OFF，即不记录mysql restart mysql

 

cd /var/lib/mysqlls

 

 

 

1.7 二进制备份

  1. 记录导致数据改变或潜在导致数据改变的SQL语句

  2. 记录已提交的日志

  3. 不依赖于存储引擎类型

 二进制日志记录三种格式

  1. 基于"语句"记录：statement，记录语句，默认模式（ MariaDB 10.2.3 版本以下 ），日志量较少

  2. 基于"行"记录：row，记录数据，日志量较大，更加安全，建议使用的格式,MySQL8.0默认格式

  3. 混合模式：mixed, 让系统自行判定该基于哪种方式进行，默认模式（ MariaDB 10.2.4及版本以上）

#开启二进制日志
mkdir - p data/binlog/
chown -R mysql:mysql /data/binlog/vim  /etc/my.cnf
[mysqld]
sercer_id=100
log_bin=/data/binlog/mysql-bin
#指定存放位置systemctl restart mysqllog_bin=/data/binlog/ls

 

 

 

#查看所有二进制日志
show master logs;#查看使用中的二进制文件
show master status;#彻底清空二进制日志
reset  master;

 

 

mysqlbinlog命令用于解析和查看二进制日志文件的内容，将二进制日志文件转换为可读的 SQL 语句或事件信息

 mysqlbinlog的备份过程

#查看日志内容
mysqlbinlog binlog.000001#过滤特定时间段/特定位置的日志
mysqlbinlog --start-datetime="2025-05-26 00:00:00" --stop-datetime="2025-05-26 23:59:59" binlog.000001
或
mysqlbinlog --start-position=12345 --stop-position=12456 binlog.000001#导出日志到文件： 将解析后的日志内容导出到文件。
mysqlbinlog binlog.000001 > output.sql#发生误操作，进行备份
mysql -u root < output.sql

 

例子：

mysqlbinlog binlog.000001

#把binlog.000001备份下来
mysqlbinlog /data/mysql/bimlog.000001 > backup.sql#如果失误删除了hellodb
mysql -u root < backup.sql

 

二. 备份

2.1 数据备份的重要性

  1. 数据安全：防止因硬件故障、软件错误或人为操作导致的数据丢失。

  2. 灾难恢复：在发生灾难性事件（如火灾、洪水）时，能够快速恢复数据。

2.2 备份的分类

  1. 完全备份：

    • 描述：备份整个数据库的所有数据。
    • 优点：恢复简单，只需一个备份文件。
    • 缺点：占用存储空间大，备份时间长。

  2. 增量备份：

    • 描述：只备份自上次备份以来发生变化的数据。
    • 优点：节省存储空间，备份速度快。
    • 缺点：恢复过程复杂，需要所有增量备份文件。

  3. 差异备份：

    • 描述：备份自上次完全备份以来发生变化的数据。
    • 优点：恢复过程相对简单，只需完全备份和最新的差异备份。
    • 缺点：占用存储空间介于完全备份和增量备份之间

增量备份和差异备份又可称部分备份

部分备份之前，需要完全备份

按照数据可用性和备份时数据库的运行状态来分类 

  1. 冷备（Cold Backup）

    • 特点：数据库完全关闭后进行备份，数据不可用。
    • 优点：操作简单，备份一致性高。
    • 缺点：备份期间服务中断。

  2. 温备（Warm Backup）

    • 特点：数据库部分功能可用，备份时可能锁定部分表或数据。
    • 优点：比冷备对服务影响小，一致性较好。
    • 缺点：部分功能受限，备份速度较慢。

  3. 热备（Hot Backup）

    • 特点：数据库完全运行，备份时数据可用且无中断。
    • 优点：服务不受影响，备份效率高。
    • 缺点：实现复杂，可能占用较多资源。

2.3 MySQL备份的内容

  1. 数据库结构

    • 包括表结构、索引、视图、存储过程等元数据信息。
    • 例如：CREATE TABLE语句、ALTER TABLE语句等。

  2. 数据内容

    • 表中存储的实际数据，即INSERT语句生成的内容。
    • 这是备份的核心部分，确保数据丢失后可以恢复。

  3. 事务日志（如binlog）

    • 记录数据库的所有变更操作，用于实现增量备份和恢复。
    • 例如：UPDATE、DELETE等操作记录。

  4. 用户权限和配置

    • 包括用户账户、权限设置、数据库配置参数等。
    • 例如：GRANT语句、my.cnf配置文件等。

2.4 备份的注意点

  1. 能容忍最多丢失多少数据

  2. 备份产生的负载

  3. 备份过程的时长

  4. 温备的持锁多久

  5. 恢复数据需要在多长时间内完成

  6. 需要备份和恢复哪些数据

2.5 备份的工具

  1. cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份

  2. mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份

  3. xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份

  4. MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现

  5. mysqlbackup：热备份， MySQL Enterprise Edition 组件

  6. mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库

2.6 实战案例

实验目标：数据库冷备份和还原

实验工具：cp, tar等复制归档工具

准备工作：

两台下载了同一版本MySQL的虚拟机
IP地址分别是：
Ubuntu=192.168.52.105
Ubuntu1=192.168.52.31将hellodb文件分别下载到虚拟机上，并输入数据库中
mysql -u root < hellodb_innodb.sql

实验步骤：

#在Ubuntu1上模拟发送事故，误删除了数据库
cd /var/lib/mysql
rm -rf *#在Ubuntu上进行冷备份操作
systemctl stop mysql
cd /var/lib/mysql
tar zcvf all.tar.gz *
scp all.tar.gz  192.168.52.31:/opt#在Ubuntu1上解压
cd /var/lib/mysql
tar xf /opt/all.tar.gz
chown mysql:mysql /var/lib/mysql -R#在Ubuntu1上查看是否备份成功
systemctl start mysql
mysql -u root
show database;

 

2.7 mysqldump备份工具

1. 备份表

mysqldump -u root hellodb students > stu.sql

use hellodbdrop table students;

source /root/stu.sql

 

注意：因为备份表的时候不包含数据库创建语句，需要先切换到目标数据库。例如本实验就需要先use hellodb.

2. 备份数据库

mysqldump  -B  hellodb > hb.sqdrop database hellodb;source /root/hb.sq

 

注意：

关于 mysqldump -uroot hellodb > hb.sql 和 mysqldump -B hellodb > hb.sql 的区别mysqldump -uroot hellodb > hb.sql
仅备份 hellodb 数据库的表结构和数据
不包含创建数据库的语句mysqldump -B hellodb > hb.sql
备份 hellodb 数据库的表结构和数据
包含创建数据库的语句所以：
使用 mysqldump -uroot hellodb > hb.sql 备份：
恢复时需要先执行 create database hellodb; 和 use hellodb;
然后执行 source /root/hb.sql使用 mysqldump -B hellodb > hb.sql 备份：
直接执行source /root/hb.sql即可恢复

 以下是使用mysqldump -uroot hellodb > hb.sql 进行备份（了解）

mysqldump -uroot hellodb > hb.sqlmysql -u rootdrop database hellodb;create database hellodb;use hellodb;source /root/hb.sql

 

3. 备份所有

#有密码
mysqldump -uroot -p 密码 -A  -F  --single-transaction --master-data=1  > /opt/all.sql
#没有密码
mysqldump -uroot  -A  -F  --single-transaction --master-data=1  > /opt/all.sql

注意：

使用 mysqldump 或 xtrabackup 等工具进行备份时，备份节点的时间点与真正节点的时间点可能会有微小差异。

#查看现在的操作节点
mysql -u root SHOW MASTER STATUS;

 

 可以发现，备份节点的时间点与真正节点的时间点可能会有微小差异。

 

4. 实战案例

每天2：30做完全备份，早上10：00误删除了表students，10：10才发现故障，现需要将数据库还原到10：10的状态，且恢复被删除的students表4

思路：

先恢复 完全备份 到2:30

把二进制日志中的 drop students 语句删除即可

#执行全备
cd /opt
mysqldump -uroot -A -F --single-transaction --source-data=2 > /opt/all.sql#完全备份后数据更新
mysql -uroot
use hellodb
insert students (name,age,gender) values('rose',20,'f');
insert students (name,age,gender) values('jack',20,'f');#10:00误删除了一个students的表
drop table students;#后续其余的表继续更新
insert teachers (name,age,gender)values('test',30,'M');
insert teachers (name,age,gender)values('test1',30,'M');#####10点10分发现进行还原
#停止数据库访问
systemctl stop mysql#从完全备份中，找到二进制位置
grep '\-\- CHANGE MASTER TO'  /opt/all.sql#备份  完全备份后的二进制日志
mysqlbinlog --start-position=157 binlog.000005 > /opt/inc.sql#删除 删表的那一行
sed   -n   '/^DROP TABLE/p'  /opt/inc.sql   #先测试下
sed -i.bak '/^DROP TABLE/d'  /opt/inc.sql systemctl start mysql
mysql -u root
source /opt/all.sql
source /opt/inc.sql#验证实验结果
show databases
use hellodb;
select * from students;
select * from teachers;

补充：一个用于备份MySQL数据库的Shell脚本命令 

for db in `mysql -uroot -e 'show databases'|grep -Ev '^(Database|information_schema|performance_schema)$'`;do mysqldump -B $db | gzip > /backup/$db.sql.gz;donecd /backupgzip -d hellodb.sql.gz

 

2.8 xtrabackup工具

1. 安装

#在centos安装
yum install epel-release -yyum install percona-xtrabackup   -y#在Ubuntu安装
apt updateapt install curl -ycurl -O https://repo.percona.com/apt/percona-release_latest.generic_all.debapt install gnupg2 lsb-release ./percona-release_latest.generic_all.deb -ypercona-release setup pxb-80apt install percona-xtrabackup-80 -y

2. 完全备份

#准备工作ubuntu和Ubuntu1都要做
#下载hellodb
mysql -u root < hellodb_innodb.sql#新建文件夹
mkdir /backup#在原主机做完全备份到/backup
xtrabackup -uroot  --datadir=/var/lib/mysql    --backup --target-dir=/backup/base#拷贝到目标主机
scp -r /backup/   目标主机:/#在目标主机Ubuntu1上还原，让目标主机出现误删除操作
drop database hellodb;#注意：恢复主机MySQL服务停止，并且数据目录为空
systemctl stop mysql
cd /var/lib/mysql
rm -rf *#1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
xtrabackup --prepare --target-dir=/backup/base#2）复制到数据库目录
#注意：数据库目录必须为空，否则MySQL服务不能启动
xtrabackup   --datadir=/var/lib/mysql    --copy-back  --target-dir=/backup/basecd /var/lib/mysql
chown -R mysql:mysql /var/lib/mysqlsystemctl  start  mysqlmysql -u root
use hellodbselect * from students;

 

 

 

 

 

3. 增量备份

#在需要恢复的主机Ubuntu1上
drop database hellodb;systemctl stop mysql#在Ubuntu上先完备
mkdir /backup/
xtrabackup -uroot  --datadir=/var/lib/mysql    --backup --target-dir=/backup/base#第一次修改数据
mysql  -uroot  -e   "insert hellodb.students (name,age,gender) values('rose',20,'f');"#第一次增量备份
xtrabackup -uroot   --datadir=/var/lib/mysql   --backup --target-dir=/backup/inc1   --incremental-basedir=/backup/base#第二次修改数据
mysql  -uroot  -e     "insert hellodb.students (name,age,gender) values('tony',20,'f');"#第二次增量备份
xtrabackup -uroot --datadir=/var/lib/mysql  --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1#观察目录文件的大小
du -sh /backup/*scp -r /backup/*      目标主机:/backup/#在Ubuntu1上
du -sh /backup/*

 

 

 

 

 

 

#在Ubuntu1上还原
#预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
xtrabackup --prepare --apply-log-only --target-dir=/backup/base
du -sh /backup/*#合并第1次增量备份到完全备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1#合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
du -sh /backup/*#复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
cd /var/lib/mysql
rm -rf *xtrabackup --prepare --target-dir=/backup/base
#准备备份目录
xtrabackup --datadir=/var/lib/mysql --copy-back --target-dir=/backup/basechown -R mysql:mysql /var/lib/mysqlsystemctl  start   mysqlshow databases;use hellodb;show tables;select * from students;

 

 

 

 

 

http://www.xdnf.cn/news/665155.html

相关文章：

  • 热点数据的统计到应用
  • C 语言学习笔记二
  • 202505系分论文《论模型驱动分析方法及应用》
  • FallbackHome的启动流程（android11）
  • 泪滴攻击详解
  • MDM在智能健身设备管理中的技术应用分析
  • 计算机系统简介（二）
  • python打卡day36@浙大疏锦行
  • C++ STL Queue容器使用详解
  • SPL 轻量级多源混算实践 1 - 在 RDB 上跑 SQL
  • vue3 浮点数计算
  • 码蹄集——矩形相交
  • 【大模型】分词（Tokenization）
  • unix的定时任务和quartz和spring schedule的cron表达式区别
  • C# 中 INI 文件操作扩展类：轻松管理配置文件
  • 开发一个交易所大概需要多少成本
  • 调试的按钮
  • 2.1 一文掌握 TypeScript 操作符
  • 配置Maven环境(全局)
  • 【辰辉创聚生物】JAK-STAT信号通路相关蛋白：细胞信号传导的核心枢纽
  • 【C++高级主题】异常处理(四)：auto_ptr类
  • Linux三剑客之grep命令使用教程
  • 在Java集合中存储对象时，修改已添加到集合中的可变对象会影响集合中已存储的内容
  • AI系统的冲锋队：在线系统构建与应用
  • OpenFOAM中实现UDF(User Defined Function)的方法
  • 品融电商：品牌全域运营的领航者，赋能中国质造新时代
  • MySQL各种日志类型介绍
  • python每日剂量（2）探讨Python中不同解析库的提取速度对比
  • C++笔记-哈希表
  • 嵌入式学习之系统编程（五）进程（2）