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

Mysql数据库之日志与备份

news 2025/5/30 15:33:58

目录

一.日志

1.日志类型

2.事务日志

3.错误日志

4.通用日志

5.二进制日志

二.备份

1.备份类型

2.备份内容

3.备份工具

4.数据库冷备份和还原

5.mysqldump备份工具

5.1 命令格式

5.2 mysqldump 常见通用选项

6.实战案例​

7.xtrabackup备份工具

一.日志

1.日志类型

  • 事务日志：transaction log

    事务日志的写入类型为"追加"，因此其操作为"顺序IO"；通常也被称为：预写式日志 write ahead logging

    事务日志文件： ib_logfile0， ib_logfile1

  • 错误日志 error log

  • 通用日志 general log

  • 慢查询日志 slow query log

  • 二进制日志 binary log （备份时使用的）

  • 中继日志 reley log，在主从复制架构中，从服务器用于保存从主服务器的二进制日志中读取的事件

2.事务日志

事务日志：transaction log

  • redo log：实现 WAL（Write Ahead Log) ,数据更新前先记录redo log

  • undo log：保存与执行的操作相反的操作,用于实现rollback

事务型存储引擎自行管理和使用，建议和数据文件分开存放

Innodb事务日志相关配置：

show variables like '%innodb_log%';   #查看事务日志
innodb_log_file_size   50331648 #每个日志文件大小  字节
innodb_log_files_in_group 2     #日志组成员个数
innodb_log_group_home_dir ./ #事务文件路径
​
vim  /etc/my.cnf
[mysqld]
innodb_log_file_size=503316480
innodb_log_files_in_group=3

事务日志性能优化

innodb_flush_log_at_trx_commit=0|1|2
​
select @@innodb_flush_log_at_trx_commit;
#查看默认值

1 此为默认值，日志缓冲区将写入日志文件，并在每次事务执行完成后执行刷新到磁盘。 这是完全遵守ACID特性
0 提交时没有写磁盘的操作; 而是每秒执行一次将日志缓冲区的提交的事务写入刷新到磁盘。 这样可提供更好的性能，但服务器崩溃可能丢失最后一秒的事务
2 每次提交后都会写入OS的缓冲区，但每秒才会进行一次刷新到磁盘文件中。 性能比0略差一些，但操作系统或停电可能导致最后一秒的交易丢失

级别012
安全性较高最高最高
性能最高最差较高

3.错误日志

  • mysqld启动和关闭过程中输出的事件信息

  • mysqld运行中产生的错误信息

  • event scheduler运行一个event时产生的日志信息

  • 在主从复制架构中的从服务器上启动从服务器线程时产生的信息

错误文件路径

SHOW GLOBAL VARIABLES LIKE 'log_error' ;
​
yum  安装
cat /var/log/mysqld.log

记录哪些警告信息至错误日志文件*

#CentOS7 mariadb 5.5 默认值为1
#CentOS8 mariadb 10.3 默认值为2
log_warnings=0|1|2|3...         #MySQL5.7之前
log_error_verbosity=0|1|2|3...   #MySQL8.0

例子：

SHOW GLOBAL VARIABLES LIKE 'log_warnings';

例子： 指定错误日志位置

vim  /etc/my.cnf
[mysqld]
log_error=/data/log_error/mysql-error.log
​
systemctl   restart mysqld

4.通用日志

通用日志相关设置

general_log=ON|OFF
general_log_file=HOSTNAME.log
log_output=TABLE|FILE|NONE

例子： 打开通用日志

vim  /etc/my.cnf
[mysqld]
general_log=ON
​
​
systemctl   restart mysqld

例子： 将通用日志放入数据库中

vim  /etc/my.cnf
[mysqld]
general_log=ON
log_output=TABLE
​
select * from  mysql.general_log\G;
vim /data/mysql/mysql/general_log.CSV

例子：

范例: 启用通用日志并记录至文件中
​
select @@general_log;     #默认没开启
set global general_log=1; #开启
​
SHOW GLOBAL VARIABLES LIKE 'log_output';
#默认通用日志存放在文件中
select @@general_log_file;
#通用日志存放的文件路径

5.二进制日志

二进制日志记录三种格式

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

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

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

查看二进制日志

show variables like 'binlog_format';

二进制日志文件格式

有两类文件
1.日志文件：mysql|mariadb-bin.文件名后缀，二进制格式,如： on.000001,mariadb-bin.000002
2.索引文件：mysql|mariadb-bin.index，文本格式,记录当前已有的二进制日志文件列表

二进制日志相关的服务器变量：

sql_log_bin

sql_log_bin=ON|OFF：
#是否记录二进制日志，默认ON，支持动态修改，系统变量，而非服务器选项，一般默认开启
select  @@sql_log_bin;

log_bin

log_bin=mysql-bin      #默认是关闭
#  指定文件位置；默认OFF，表示不启用二进制日志功能，上述两项都开启才可以    
#  此选项作用是指明 日志文件的位置所在
#  需要加  server-id = 1   数据库编号
​
server-id  = 1
# 数据库的 编号   区别数据库
​
​
select  @@log_bin;
#变量 log_bin 和服务器选项是两个意思

binlog_format ： 日志格式

binlog_format=STATEMENT|ROW|MIXED：
#二进制日志记录的格式，mariadb5.5默认STATEMENT
​
​
update  students set  age=20  where stuid >=10;
​
STATEMEN: 语句型那么只记录此条命令
update  students set  age=20  where stuid >=10;
​
ROW: 行型  就会记录具体的操作
update  students set  age=20  where stuid  =1;
update  students set  age=20  where stuid  =2;
update  students set  age=20  where stuid  =3;
.............................................
update  students set  age=20  where stuid  =10;
​
​
mysql> select @@binlog_format;
+-----------------+
| @@binlog_format |
+-----------------+
| ROW             |
+-----------------+
1 row in set (0.01 sec)

max_binlog_size 最大日志大小

max_binlog_size=1073741824：
#单个二进制日志文件的最大体积，到达最大值会自动滚动，默认为1G
#说明：文件达到上限时的大小未必为指定的精确值

其他：

binlog_cache_size=4m
#此变量确定在每次事务中保存二进制日志更改记录的缓存的大小（每次连接）
max_binlog_cache_size=512m
#限制用于缓存多事务查询的字节大小。
sync_binlog=1|0
#设定是否启动二进制日志即时同步磁盘功能，默认0，由操作系统负责同步日志到磁盘，1立即写入磁盘
expire_logs_days=N
#二进制日志可以自动删除的天数。 默认为0，即不自动删除

例子：开启二进制日志

[root@localhost ~]#vim  /etc/my.cnf
log_bin=/data/mysql-bin      
#指明二进制日志所存在的目录
server-id = 1
#指明数据库的编号
[root@localhost ~]#chown mysql.mysql /data/
[root@localhost ~]#systemctl restart  mysqld

在线查看二进日志

SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos] [LIMIT [offset,] row_count]
例子：
show binlog events in '日志名称';
show binlog events in 'mysql-bin.000001';

例子：

 show master logs;
show binlog events in "mysql-bin.000001";

离线查看二进制日志

例子：

mysqlbinlog  -v  /data/mysql-bin.000003
mysqlbinlog --start-position=678 --stop-position=752 /var/lib/mysql/mariadb-bin.000003 -v
mysqlbinlog  --start-datetime="2018-01-30 20:30:10"   --stop-datetime="2018-01-30 20:35:22" mariadb-bin.000003 -vvv

删除二进制日志

purge binary logs to 'mysql-bin.000002';
#代表删除002 之前的  日志

reset  master;#彻底清空二进制日志

flush logs;#刷新日志

二.备份

1.备份类型

  • 完全备份，部分备份

    完全备份：整个数据集

    部分备份：只备份数据子集，如部分库或表

做部分备份前一定要有 完全备份

  • 完全备份、增量备份、差异备份

    增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂

    增量备份 还原规则就是 由远及近

差异备份：仅备份最近一次完全备份以来变化的数据或者增长的数据，备份较慢，还原简单， 直接还原最新的备份

注意：二进制日志文件不应该与数据文件放在同一磁盘冷、温、热备份

  • 冷备：读、写操作均不可进行，数据库停止服务

  • 温备：读操作可执行；但写操作不可执行

  • 热备：读、写操作均可执行

MyISAM：温备，不支持热备 不支持 事务

InnoDB：都支持

2.备份内容

  • 数据

  • 二进制日志、InnoDB的事务日志

  • 用户帐号，权限设置，程序代码（存储过程、函数、触发器、事件调度器） mysql

  • 服务器的配置文件

3.备份工具

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

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

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

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

  • mysqlbackup：热备份， MySQL Enterprise Edition 组件

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

4.数据库冷备份和还原

例子： 迁移数据库先不要启动数据库， 复制好文件后再启动

[root@localhost ~]#systemctl stop mysqld
[root@localhost ~]#scp -r  /var/lib/mysql/    192.168.91.101:/opt/
#将数据库文件整个打包
[root@localhost ~]#scp  /etc/my.cnf   192.168.91.101:/etc/
#如果修改了配置文件需要一起复制


#模拟破坏数据
[root@localhost ~]#rm -rf /var/lib/mysql


#备份节点
[root@localhost ~]#scp -r  /opt/mysql/    192.168.91.100:/var/lib/

5.mysqldump备份工具

5.1 命令格式

mysqldump [OPTIONS] database [tables]   
#选择数据库   也可以选择数据库和表  只能备份表

例子：

#备份表
mysqldump   -uroot hellodb  students >  b.sql
#备份 hellodb 数据库中的 students表  重定向

#还原
mysql> source  /root/b.sql


#备份数据库
mysqldump   -uroot hellodb
#此处需要先建数据库， 备份只有数据库下的所有表

mysqldump [OPTIONS] -B DB1 [DB2 DB3...]
#-B  可以选择多个数据库  可以备份数据库
mysqldump  -B  hellodb   >  hb.sql

mysqldump  -B  hellodb  mysql  >  all.sql
#一次备份多个数据库

mysqldump [OPTIONS] -A [OPTIONS]       
#备份所有数据库   也可以备份数据库
mysqldump  -A  >all.sql

mysqldump  -A  --source-data=2  > test.sql

#生成新的二进制日志
mysqldump  -A  --source-data=2  -F > test.sql
mysql> show master log 

5.2 mysqldump 常见通用选项

选项含义
-A, --all-databases#备份所有数据库，含create database
-B, --databases db_name…#指定备份的数据库，包括create database语句
-E, --events：#备份相关的所有event scheduler
-R, --routines：#备份所有存储过程和自定义函数
--triggers：#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
--default-character-set=utf8#指定字符集
--master-data[=#]：#此选项须启用二进制日志 #1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复制多机使用 #2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原 #此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启-- single-transaction），#注意：MySQL8.0.26版以后，此选项变为--source-data
-F, --flush-logs#备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件， 配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--single transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
--compact#去掉注释，适合调试，节约备份占用的空间,生产不使用
-d, --no-data#只备份表结构,不备份数据,即只备份create table
-t, --no-create-info#只备份数据,不备份表结构,即不备份create table
-n,--no-create-db#不备份create database，可被-A或-B覆盖
--flush-privileges#备份mysql或相关时需要使用
-f, --force#忽略SQL错误，继续执行
--hex-blob#使用十六进制符号转储二进制列，当有包括BINARY， VARBINARY， BLOB，BIT的数据类型的列时使用，避免乱码
-q, --quick#不缓存查询，直接输出，加快备份速度

6.实战案例​

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

#开启二进制日志
vim /etc/my.cnf
log_bin=/data/mysql-bin
server-id = 1
chown mysql.mysql /data/ -R
systemctl restart   mysqld


#######2:30  执行全备
mysqldump -uroot -pabc123 -A -F --single-transaction --source-data=2 > /opt/all.sql

#完全备份后数据更新
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分发现进行还原
#停止数据库访问

[root@localhost ~]#grep '\-\- CHANGE MASTER TO'  /opt/all.sql 
#从完全备份中，找到二进制位置
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=154;


 
#备份  完全备份后的二进制日志
mysqlbinlog --start-position=154 /data/mysql-bin.000002 > /opt/inc.sql

#找到  删除的语句
[root@localhost ~]#grep -i "^drop table"  /opt/inc.sql 
DROP TABLE `students` /* generated by server */

#删除 删表的那一行
sed   -n   '/^DROP TABLE/p'  /opt/inc.sql   #  先测试下
sed -i.bak '/^DROP TABLE/d'  /opt/inc.sql 

#登录数据库还原
set sql_log_bin=0;     #先关闭二进制日志    临时关闭  
set sql_log_bin=1; 


source     /opt/inc.sql

7.xtrabackup备份工具

实际例子：

完全备份

1)新建文件夹
#mkdir /backup

2）备份
#在原主机做完全备份到/backup
xtrabackup -uroot    --datadir=/var/lib/mysql    --backup --target-dir=/backup/base

--data-dir=    指明数据库文件的的数据文件位置， 如果不写会以  /etc/my.cnf  中的 配置为准， 但是都没有  还原会报错  
--backup      备份
--target-dir=/backup/base     备份到/backup/base这个文件夹中

3) 拷贝到目标主机
#scp -r /backup/   目标主机:/


4)在目标主机上还原
注意：恢复主机MySQL服务停止，并且数据目录为空
1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
xtrabackup --prepare --target-dir=/backup/base


2）复制到数据库目录
注意：数据库目录必须为空，MySQL服务不能启动
xtrabackup   --datadir=/var/lib/mysql    --copy-back   --target-dir=/backup/base


chown -R mysql:mysql /var/lib/mysql


systemctl  start  msyql

增量备份

mkdir /backup/
1) 完全备份
xtrabackup -uroot  --datadir=/var/lib/mysql    --backup --target-dir=/backup/base

2）第一次修改数据
mysql>insert students (name,age,gender) values('rose',20,'f');

mysql  -uroot  -e     "insert hellodb.students (name,age,gender) values('rose',20,'f');"

3）第一次增量备份
xtrabackup -uroot   --datadir=/var/lib/mysql   --backup --target-dir=/backup/inc1   --incremental-basedir=/backup/base


4）第二次修改数据
mysql>insert students (name,age,gender) values('jack',22,'m');


5）第二次增量
xtrabackup -uroot --datadir=/var/lib/mysql  --backup --target-dir=/backup/inc2 --incremental-basedir=/backup/inc1


#观察目录文件的大小
du -sh /backup/*
62M /backup/base
2.0M /backup/inc1
2.0M /backup/inc2

6）scp -r /backup/*      目标主机:/backup/
#备份过程生成三个备份目录
/backup/{base，inc1，inc2}

1）预准备完成备份，此选项--apply-log-only 阻止回滚未完成的事务
xtrabackup --prepare --apply-log-only --target-dir=/backup/base
du -sh /backup/*


2）合并第1次增量备份到完全备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/base --incremental-dir=/backup/inc1
du -sh /backup/*
82M /backup/base
9.9M /backup/inc1
2.0M /backup/inc2

3）合并第2次增量备份到完全备份：最后一次还原不需要加选项--apply-log-only
xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2
du -sh /backup/*
178M /backup/base
9.9M /backup/inc1
9.9M /backup/inc2

4）复制到数据库目录，注意数据库目录必须为空，MySQL服务不能启动
xtrabackup   --datadir=/var/lib/mysql  --copy-back --target-dir=/backup/base

chown -R mysql:mysql /var/lib/mysql

systemctl  start   mysql


#或者执行下面操作也可以
cp -r /backup/base/* /var/lib/mysql
5）还原属性：
chown -R mysql:mysql /var/lib/mysql
6）启动服务：
service mysqld start

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

相关文章：

  • 论坛系统自动化测试实战
  • SpringAI--RAG知识库
  • Windows中安装Neo4j图数据库的配置
  • 数据架构：零售业数字化转型的“隐形引擎”
  • 什么是软件验收测试，出验收测试报告的软件检测机构推荐
  • MySQL问题：数据库有哪些存储引擎，它们有什么区别？
  • Jenkins部署
  • 小型电磁脉冲干扰（EMP）的原理及组成
  • L1-111 大幂数 - java
  • day37打卡
  • 二、网络安全常见编码及算法-（1）
  • 爱芯元智芯片推理cn-clip
  • 11.10 LangGraph状态管理实战：Reducers模式如何重塑企业级多节点协作？
  • 云化全场景+AI智算双擎驱动，打造高教数智化转型新范式，麒麟信安闪耀第63届高等教育博览会！
  • Linux基础IO----动态库与静态库
  • MQTT 在云平台与设备通讯中的连接特性与通讯性质深度解析
  • 网络原理与 TCP/IP 协议详解
  • AJAX-让数据活起来(一)：入门
  • 深度PCB干货：如何画出做好一块电路PCB板
  • YOLO 算法详解：实时目标检测的里程碑
  • 【unity游戏开发——编辑器扩展】Scene窗口拓展
  • ZYNQ实战：可编程差分晶振Si570的配置与动态频率切换
  • Powershell实现服务守护进程功能(服务意外终止则重启)
  • 湖北理元理律师事务所债务优化服务中的“四维平衡“之道
  • Ubuntu的shell脚本
  • Few-shot Personalized Scanpath Prediction
  • Monorepo 管理
  • 寒武纪显卡MLU编译安装mmcv1.7.0、mmdetection2.26.0并测试
  • 悬空指针问题回顾与实践总结（Dangling Pointers Retrospective）
  • 前端大文件分片上传与断点续传方案