mysql 通过日志统计表的增删改_mysql日志总结

最新推荐文章于 2023-06-20 23:18:49 发布
最新推荐文章于 2023-06-20 23:18:49 发布
阅读量647 收藏
点赞数
文章标签： mysql 通过日志统计表的增删改
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/weixin_29677553/article/details/114815890
版权

(1)mysql的日志有哪些？

①错误日志error_log：记录Mysql启动，运行，停止期间的问题。

②常规日志general_log：记录所有发向mysql的请求。

③慢查询日志slow_query_log: 记录符合条件的查询。

④二进制日志binary_log: 记录全部有效的数据修改日志。(非常重要，高可用，主从备份，数据恢复，增量备份等)

⑤中继日志relay_log: 用于主从复制，可以看做主服务器数据库二进制日志在从服务器的副本，其内容就是增量获取主服务器的二进制日志。

(2)我们在什么情况下使用这些日志?

(3)如何通过日志来审计用户活动？

mysql存储引擎层日志：innodb的重做日志以及回滚日志;

mysql服务层日志：二进制日志，慢查询日志，通用日志。

1 二进制日志binary_log

记录了所有对mysqsl数据库的修改事件，包括增删改查和对表结构的修改。binlog记录的日志都是成功执行的。二进制日志可以帮助我们实现主从复制，基于时间点的备份和恢复。包括innodb, myisam等其他存储引擎。

1.1基于段的二进制日志

binlog_format=STATEMENT #mysql5.7之前默认的模式

优点：

可以清楚的看出mysql执行了那些操作。记录了每一个事件执行的sql语句。日志量相对较小，节约磁盘，网络IO性能。

缺点：

必须记录上下文信息。以保证在从服务器上执行结果和在主服务器上相同。

但是对于UUID(),user(),now()这样的非确定函数还是没法复制，可能造成主从数据不一致。

show variables like 'binlog_format';

set session binlog_format=statement;

show binary logs查看binlog;

flush logs;刷新二进制日志，产生新的二进制日志binlog;

user statementdb;

insert into test(1,'1'),(2,'2');

update test set name='11' where id = 1

进入日志存放目录去查看日志

cd /home/mysql/sql_log

ls 查看有哪些日志

mysqlbinlog mysql-bin.000002

5b5babd18eefd88590c303b258a76c2a.png

1.2基于行的日志格式 binlog_format=ROW

row格式可以避免mysql复制过程中的主从不一致问题。不记录修改sql语句，仅记录哪条数据被修改了，修改成什么样了。每修改一行都有一条日志，若批量修改基于段的日志格式只会记录这个sql语句，基于行的日志会记录所有修改每一行的数据修改日志。

优点：

使用mysql主从复制更加安全。

对每一行数据的修改比基于段的复制高效。

误操作修改了数据库中的数据，同时有没有备份恢复，那么我们就可以分析二进制日志，对日志中记录的数据修改操作反向处理已达到恢复数据的目的。

缺点：

日志量大。

解决方法：

binlog_row_image=FULL|MINIMAL|NOBLOB 默认FULL记录修改记录所在行所有列的数据，MINIMAL表示只记录修改了的前后列的数据。

1.3混合模式

以上两种模式的混合使用，又数据库来决定日志保存方式。

2 redo日志

mysql里常说的WAL技术，Write-Ahead Loggin，关键点就是先写日志，然后写磁盘。

当有一条记录要跟新，会清空缓存，同时把记录写到redo log里，并且更新内存，innodb执行引擎会在适当的时候将操作记录刷新到磁盘。redo log可以保证即使数据库发生异常，之前的提交记录都不会丢失。

重做日志记录的是修改的页信息，而且只记录Innodb存储引擎本身的事务日志。

redo日志在事务执行期间，连续写入。binlog是在事务提交之后。

事务提交有三个步骤：(1)write prepare log在预提交日志中写入xid。(2)writer binlog。(3)write commit log。

3 undo日志

为了满足事务的原子性，在操作任何数据之前，首先将数据备份到一个地方。然后进行数据的修改。如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。

4中继日志(relay_log)

用于主从复制。

relay_log=filename指定日志存放位置以及文件名前缀。设置固定名防止主机名变化影响主从复制。

默认自动删除中继日志relay_log_purge=ON。

5 错误日志

5.1 错误日志的使用场景

(1)分析排除mysql运行错误：mysql服务器启动失败，异常重启，主从失败等

(2)记录未经授权的访问：错误日志中会记录异常登录信息，对登录信息进行审计。

5.2 配置

(1)log_error = $mysql/sql_log/mysql-error.log 默认将错误日志保存到数据文件中，而且错误日志名取主机名，后缀为-error.log的文件。通常情况下出于性能优化的考虑将错误日志于数据日志分离，存放到不同的文件或设备上。例如数据文件放到高速io磁盘上，错误日志放到相对性能低的磁盘上。

(2)log_error_verbosity=[1,2,3]

错误日志级别，默认值为2。1表示错误信息；2表示错误和警告信息；3表示错误和警告和提示信息note message；

(3)log_error_services=[日志服务组件;日志服务组件]  mysql8以上

e5bbd84c454dd5df8b7b45771b4fb81f.png

show variables like ‘log_error_services’;(默认log_filter_internal, log_sink_internal)

若去掉默认的日志过滤组件，set persist log_error_services=’log_sink_internal’；

7常规日志general_log

记录从连接到退出为止，与mysql所有交互信息，会产生大量的日志，默认情况关闭。

8 慢查询日志

8.1慢查询日志配置

将执行成功并且符合条件的查询记录到日志中，找到需要优化的sql。默认关闭。

slow_query_log = ON

slow_query_log_file=$mysql/mysql_log/slowlog.log

long_query_time = xx秒

log_queries_not_using_indexes=ON

8.2慢查询日志分析工具

(1) mysqldumpslow，mysql自带工具。统计信息比较少，包括了执行sql语句，执行时间，锁定时间，发送的行数，扫描的行数。

执行命令：查询慢查询日志前10个：mysqldumpslow -t 10 /var/lib/mysql/mysql-slow.log

5861f753a4c346a605d557a0b561908d.png

(2) pt-query-digest

安装：

wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum localinstall -y  percona-toolkit-2.2.16-1.noarch.rpm

pt-query-digest --version-check 版本进行升级

wget http://percona.com/get/pt-summary

检查是否安装成功：

pt-query-digest --help

用途

查看服务器信息：

pt-summary

查看磁盘开销

pt-diskstats

查看mysql数据库信息

pt-mysql-summary --user=root --password=123456

分析慢查询日志

pt-query-digest /data/mysql/data/db-slow.log

查看mysql主从同步状态

pt-slave-find --host=localhost --user=root --password=123456

查看mysql死锁信息

pt-deadlock-logger --user=root --password=123456 localhost

查看慢查询日志中索引使用情况

pt-index-usage --user=root --password=123456 slow_20131009.log

查看数据库表中重复的索引

pt-duplicate-key-checker --host=localhost --user=root --password=123456

对比mysql配置文件差异

pt-config-diff /etc/my.cnf /etc/my_master.cnf

查看mysql表和文件的当前活动IO开销

pt-ioprofile

pt-find查看数据库中大于500g的表

pt-find --user=root --password=123456 --tablesize +500G

pt-kill 杀掉符合标准的mysql进程

查看查询时间大于60s的查询并且杀掉

pt-kill --user=root --password=123456 --busy-time 60 --print

pt-kill --user=root --password=123456 --busy-time 60 --kill

查询mysql授权

pt-show-grants --user=root --password=123456

pt-show-grants --user=root --password=123456 --separate –revoke

验证数据库复制的完整性

pt-table-checksum --user=root --password=123456

TechTornado
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
MySQL数据库增删改日志_MySQL 如何查看某用户对数据库做了增删改操作
weixin_30585729的博客
01-19 2627
binlog日志会记录增删改操作：create，drop，insert，delete，update等的操作。但是不会记录是什么用户操作的。记录用户的上述操作：使用init-connect +binlog的方法进行mysql的操作审计。由于mysql binlog记录了所有对数据库长生实际修改的sql语句，及其执行时间，和connection_id但是却没有记录connection_id对应的详细...
mysql 通过日志统计表的增删改_MySQL根据二进制日志binlog统计每个表的添加、删除和修改次数,mysql,基于,各表,增删...
weixin_36303305的博客
01-21 299
分析一段时间内，数据库各个表insert,update,delete的次数。脚本如下：#!/usr/bin/env python3# _*_ coding:utf8 _*_from threading import Threadfrom queue import Queuefrom time import strptime,mktime,localtime,strftimefrom pymysql...
Mysql增删查改的简单使用(手把手详细)
qq_62718027的博客
06-20 743
作为小白的你和我就。
MySQL高级第十六篇：数据库日志有哪些（慢查询日志、通用查询日志、错误日志和二进制日志）
I wanna be myself, where I Belong.
04-23 1274
慢查询日志：记录所有执行时间超过long_query_time的所有查询，方便我们对查询进行优化。通用查询日志：记录所有连接的起始时间和终止时间，以及连接发送给数据库服务器的所有指令，对我们复原操作的实际场景、发现问题，甚至是对数据库操作的审计都有很大的帮助。错误日志：记录MySQL服务的启动、运行或停止MySQL服务时出现的问题，方便我们了解服务器的状态，从而对服务器进行维护。二进制日志：记录所有更改数据的语句，可以用于主从服务器之间的数据同步，以及服务器遇到故障时数据的无损失恢复。
MySQL day7 主从复制
果子哥丶的博客
06-06 524
主从复制搭建，主从复制原理，主从复制故障分析
MySQL日志总结
xinyuan的专栏
07-19 357
MySQL日志 MySQL中有七种日志文件，分别是 1. 重做日志（redo log） 2. 回滚日志（undo log） 3. 二进制日志（binlog） 4. 错误日志（errorlog） 5. 慢查询日志（slow query log） 6. 常规日志（general log） 7. 中继日志（relay log） 详细介绍 1. 重做日志（redo log） 作用 重做日志（red...
MySQL的日志总结
红鱼儿的博客
09-27 2432
前言 MySQL中有以下日志文件，分别是： 　　1：重做日志（redo log） 　　2：回滚日志（undo log） 　　3：二进制日志（binlog） 　　4：错误日志（errorlog） 　　5：慢查询日志（slow query log） 　　6：一般查询日志（general log） 　　7：中继日志（relay log）。 其中重做日志和回滚日志与事务操作息息相关，二进制日志也与事务操作有一定的关系，这三种日志，对理解MySQL中的事务操作有着重要的意义。 一、重做日志（red
分析 MySQL 的 binlog 日志的工具
06-03
通过遍历binlog，我们可以收集这些事件，然后按表分组，统计每个表的增删改次数。 具体步骤如下： 1. **读取binlog**：使用`mysqlbinlog`或自定义工具读取指定时间段内的binlog文件。 2. **解析事件**：逐个解析`...
mysql表优化、分析、检查和修复的方法详解
09-10
在实际应用中，随着数据量的增长和频繁的增删改操作，表可能会出现性能下降、数据碎片等问题，此时就需要进行表的优化、分析、检查和修复。下面将详细介绍这些方法。 **一、表优化** 表优化主要是为了减少数据碎片...
MySQL数据库应用形考实验1-4全答案.zip
12-21
在这个"MySQL数据库应用形考实验1-4全答案.zip"压缩包中，包含了四个关于MySQL基础操作的实验训练，涵盖了从数据库和表的创建，到数据的查询、增删改，以及数据库系统的维护等关键知识点。以下是对这些实验的详细...
动物园管理系统MySQL
02-16
总结起来，"动物园管理系统MySQL"是一个集成了MySQL数据库和JavaFX技术的系统，用于高效管理和展示动物园的各项数据，同时具备用户友好的界面、完善的权限控制、数据安全保护以及数据分析功能。这样的系统极大地提高...
java使用JDBC调用mysql的人员信息管理系统
11-05
4. **执行SQL**：通过Statement或PreparedStatement对象的`executeQuery()`或`executeUpdate()`方法执行SQL语句，前者用于查询，后者用于DML（增删改）操作。 5. **处理结果集**：对于查询操作，会返回一个ResultSet...
MySQL relay_log_purge=0 时的风险
很多时候，你缺少的不是知识而是热情
04-06 8174
http://ourmysql.com/archives/1453 有时候，我们希望将 MySQL 的 relay log 多保留一段时间，比如用于高可用切换后的数据补齐，于是就会设置 relay_log_purge=0，禁止 SQL 线程在执行完一个 relay log 后自动将其删除。但是在官方文档关于这个设置有这么一句话： Disabling purging of rel
MySQL主从复制
以梦为马不负韶华的博客
11-26 406
MySQL主从复制、读写分离、主节点高可用
MySQL监视增删改查以及查看监视的日志
太阳上的雨天
05-22 2973
MySQL监视增删改查以及查看监视的日志 1.脸上数据库，命令行模式输入show global variables like ‘%general%’;如下图 默认为OFF，开启的命令为：set global general_log=on;如下图 general_log_file对应的value就是记录日志的文件，即可对mysql的增删改查进行查看监视。 ...
mysql binlog sql统计_mysql的binlog详解
weixin_40003780的博客
02-17 517
binlog是mysql记录操作的二进制日志文件,有三种格式可选,但是老旧的SBR已经不适合现在大多数业务需求,所以大多数都建议用MBR和RBR,即mixed或row,而解析他的原因,几乎都只有一个,就是恢复数据库,或者是反编译来恢复数据库,目的都是一样.以下是基于mysql5.6的mysqlbinlog来描述的,请各位知照.binlog使用方法直接来看看例子:#可以在数据库界面操作一些命令#只查...
MySQL Binlog详解
weixin_30252709的博客
03-10 228
MySQL Binlog详解 　　Mysql的binlog日志作用是用来记录mysql内部增删改查等对mysql数据库有更新的内容的记录（对数据库的改动），对数据库的查询select或show等不会被binlog日志记录;主要用于数据库的主从复制以及增量恢复。 mysql的binlog日志必须打开log-bin功能才能生存binlog日志 -rw-rw---- 1 mysql mysql...
mysql增删查改总结_总结：mysql的各种增删改查！
weixin_42356139的博客
01-19 339
(原创总结)分为数据库的增删改查，数据表(和字段)的增删改查，数据的增删改查 三部分！// 创建用户并授权GRANT SELECT ON bodydb.user TO us@localhost IDENTIFIED BY '123456';//GRANT 权限 ON 数据库.[数据表] TO 用户名@登录主机 IDENTIFIED BY ‘密码’;// all代表接受所有操作，比如 select...
Crypto_30_vHsm_Types.h
最新发布
07-09
Crypto_30_vHsm_Types
springboot 读取mysql 增删改日志文件
05-31
要读取 MySQL 的增删改日志文件，可以使用 MySQL 的 binlog，binlog 是 MySQL 的二进制日志，记录了 MySQL 的所有更新操作，包括增删改等。 下面是使用 Spring Boot 读取 MySQL binlog 的步骤： 1. 在 MySQL 配置文件中开启 binlog，可以在 my.cnf 或 my.ini 文件中添加如下配置： ``` [mysqld] log-bin=mysql-bin binlog-format=ROW ``` 这里将 binlog 日志文件存储在名为 mysql-bin 的文件中，格式为 ROW。 2. 在 Spring Boot 中添加 MySQL 驱动和 binlog 相关的依赖，例如： ``` <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>8.0.23</version> </dependency> <dependency> <groupId>com.github.shyiko</groupId> <artifactId>mysql-binlog-connector-java</artifactId> <version>0.17.0</version> </dependency> ``` 这里使用了 mysql-connector-java 和 mysql-binlog-connector-java 两个依赖。 3. 在 Spring Boot 中编写读取 binlog 日志的代码，例如： ``` @Component public class BinlogReader { private final BinaryLogClient client; public BinlogReader() { client = new BinaryLogClient("localhost", 3306, "root", "password"); client.registerEventListener(event -> { EventData data = event.getData(); if (data instanceof WriteRowsEventData) { WriteRowsEventData write = (WriteRowsEventData) data; System.out.println("inserted rows: " + write.getRows()); } else if (data instanceof UpdateRowsEventData) { UpdateRowsEventData update = (UpdateRowsEventData) data; System.out.println("updated rows: " + update.getRows()); } else if (data instanceof DeleteRowsEventData) { DeleteRowsEventData delete = (DeleteRowsEventData) data; System.out.println("deleted rows: " + delete.getRows()); } }); } @PostConstruct public void start() throws IOException { client.connect(); } @PreDestroy public void stop() throws IOException { client.disconnect(); } } ``` 这里使用了 BinaryLogClient 类来连接 MySQL，通过 registerEventListener 方法注册事件监听器来监听 binlog 日志的写入、更新、删除操作。 需要注意的是，直接读取 MySQL 的 binlog 日志文件可能会对性能和稳定性造成影响，建议在使用前先进行充分测试和评估。同时，也建议使用专业的数据库同步工具来进行 MySQL 数据库的同步，如阿里云的 DTS、腾讯云的 CDC 等。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值