AI 助理
备案控制台
开发者社区 数据库 文章 正文

MySQL日志

2023-06-23 137
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
日志服务 SLS，月写入数据量 50GB 1个月
简介： MySQL日志

一. 上节回顾

  1. 表空间
    1.1 说明
    独立表空间 : 5.6 开始的默认表空间,一个表一个ibd文件,存储数据行和索引. *
    共享表空间 : 5.5 默认的模式, 所有表的行和索引都存储到ibdata1
    说明: 从5.6 开始,不再使用共享表空间模式
    5.6版本 数据字典信息 + UNDO + tmp
    5.7版本 把tmp独立了
    8.0版本 把UNDO独立
    1.2 表空间迁移 *
    (1)创建和原表结构相同的表
    (2)新建表的ibd删除
    alter table t1 discard tablespace ;
    (3)拷贝原表ibd到新位置
    (4)导入ibd到新表
    alter table t1 import tablespace;
    1.3 ibtmp1 保存临时表
    1.4 undo 重做的日志存储位置

  1. ACID
    原子性
    一致性
    隔离性
    持久性

  2. redo *
    (1) 记录 内存数据页变化日志
    (2) 提供 快速的事务的提交(commit)
    (3) CSR redo提供的前滚的功能

  3. undo *
    (1) 记录 数据修改之前的状态
    (2) 提供 事务工作过过程中回滚操作(rollback)
    (3) CSR 中将未提交的事务进行回滚

  1. 隔离级别 *
    RU : 读未提交,会有脏读 , 幻读 ,不可重复读
    RC *: 读已提交,会有 幻读,不可重复读. 在大部分互联网企业中是可以容忍的.
    RR *: 可重复读:(MVCC ,undo快照)防止不可重复读, 有可能出现幻读,可以用通过GAP+Next LOCK来防止幻读(索引).
    SR : 可串行化:

  2. 不可重复读(现象) *

  3. 幻读(现象)*

  4. InnoDB核心参数的介绍

存储引擎默认设置

default_storage_engine=innodb

表空间模式

innodb_file_per_table=1

共享表空间文件个数和大小

innodb_data_file_path=ibdata1:512M:autoextend

"双一" 标准的其中一个 **

innodb_flush_log_at_trx_commit=1

Innodb_flush_method=(O_DIRECT, fsync) *

作用: 控制的是 Redo buffer 和 buffer pool
fsync :
O_DIRECT :
O_DSYNC :

最高安全模式
innodb_flush_log_at_trx_commit=1
innodb_flush_method=O_DIRECT

最高性能:
innodb_flush_log_at_trx_commit=0
innodb_flush_method=fsync

redo日志设置有关的
show variables like "innodb_log_buffer_size"; #查看当前日志缓冲区大小

vim /etc/my.cnf
添加：
innodb_log_buffer_size=16777216 #内存日志缓冲区大小
innodb_log_file_size=50331648 #磁盘日志文件大小
innodb_log_files_in_group = 2 #磁盘日志文件文件个数

脏页刷写策略
innodb_max_dirty_pages_pct=25
还有哪些机制会触发写磁盘?
CSR
redo满了

二. 日志管理

  1. 错误日志 *
    1.1 作用
    排查MySQL运行过程的故障.
    1.2 默认配置
    默认就开启了.
    默认路径和名字: datadir/hostname.err
    查看方法: [ERROR]
    1.3 人为定制位置
    log_error=/tmp/mysql3306.log
    重启生效.
    select @@log_error;

  2. 二进制日志(binlog)**
    2.1 作用
    (1) 主从要依赖二进制日志
    (2) 数据恢复时需要依赖于二进制日志

2.2 如何配置?
(1)参数介绍
默认没有开启.
server_id=6
log_bin=/data/binlog/mysql-bin
说明:
mysql-bin : 二进制日志文件名的前缀
例如: mysql-bin.000001 ,mysql-bin.000002 ......
binlog_format=row ---> 5.7版本默认配置是row,可以省略.

(2) 参数配置
server_id=6
log_bin=/data/binlog/mysql-bin
binlog_format=row

(3)创建目录和授权
mkidr -p /data/binlog
chown -R mysql.mysql /data
systemctl restart mysqld

2.3 二进制日志记录了什么?
2.3.1 概括
记录的数据库所有变更类的操作日志.
DDL: create drop alter
DCL: grant revoke
DML: insert update delete

2.3.2 DDL 和 DCL
以语句的方式,原模原样的记录.

2.3.3 DML
(1) 他记录的已提交的事务
(2) DML记录格式(statement,row,mixed),通过binlog_format=row参数控制 **
说明:
statement:SBR,语句模式记录日志,做什么命令,记录什么命令.
row :RBR,行模式,数据行的变化
mixed :MBR,混合模式

面试问题: SBR和RBR什么区别?怎么选择? *
SBR: 可读性较强,对于范围操作日志量少,但是可能会出现记录不准确的情况.
RBR: 可读性较弱,对于范围操作日志大,不会出现记录错误.高可用环境中的新特性要依赖于RBR

为什么用RBR？ 我们公司对数据的严谨性要求较高,也用到了新型的架构,所以选择RBR

2.4 二进制日志记录单元
2.4.1 event 事件
二进制日志的最小单元
DDL :
create database event; 事件1

对于DDL等语句是每一个语句就是一个事件
DML: 一个事务包含了多个语句
begin; 事件1
a 事件2
b 事件3
commit; 事件4

2.4.2 event事件的开始和结束号码
作用,方便我们从日志中截取我们想要的日志事件.

2.5 二进制日志的管理
2.5.1 查看二进制日志位置
mysql> show variables like '%log_bin%';

2.5.2 查看所有已存在的二进制日志
mysql> show binary logs;
mysql> flush logs; #刷新，产生新的二进制日志
mysql> show binary logs;

2.5.3 查看正在使用的二进制日志
mysql> show master status ;

2.5.4 查看二进制日志事件
create database binlog charset utf8mb4;
use binlog
create table t1(id int);
insert into t1 values(1);
show master status ;
show binlog events in 'mysql-bin.000001'; #查看日志中详细事件

2.5.5 查看二进制日志内容
mysqlbinlog mysql-bin.000001 #看语句级日志，不能看行数据
mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000001 #使用解码的方式，可看行数据
mysqlbinlog -d event mysql-bin.000001 #摘要显示事件号码

2.5.6 截取二进制日志，还原
mysqlbinlog --start-position=219 --stop-position=758 mysql-bin.000001 >/tmp/a.sql
set sql_log_bin=0;
source /tmp/a.sql;
set sql_log_bin=1;

2.5.7 通过binlog恢复数据
(1) 模拟数据
create database hehe charset utf8mb4;
use hehe;
create table t1(id int);
insert into t1 values(1);
commit;

(2)模拟故障
mysql> drop database hehe;

(3)分析和截取binlog
mysql> show master status ; --->确认使用的是哪一个日志
mysql> show binlog events in 'mysql-bin.000001' ; --->查看事件
说明: 找到起点和终点,进行截取
mysqlbinlog --start-position=219 --stop-position=758 /data/binlog/mysql-bin.000002 >/tmp/bin.sql

(4)恢复binlog

mysql> set sql_log_bin=0; --->临时关闭恢复时产生的新日志
mysql> source /tmp/bin.sql
mysql> set sql_log_bin=1; --->改回来

2.6 binlog的gtid记录模式的管理 **
2.6.1 GTID介绍
对于binlog中的每一个事务,都会生成一个GTID号码
DDL ,DCL 一个event就是一个事务,就会有一个GTID号.
DML语句来讲,begin到commit,是一个事务,就是一个GTID号

2.6.2 GTID的组成
server_uuid:TID

cat /usr/local/mysql/data/auto.cnf
[auto]
server-uuid=e2e9b01e-9687-11eb-b577-000c29b0384b

TID是一个:自增长的数据,从1开始
e2e9b01e-9687-11eb-b577-000c29b0384b:1-15

2.6.3 GTID的幂等性
如果拿有GTID的日志去恢复时,检查当前系统中是否有相同GTID号,有相同的就自动跳过
会影响到binlog恢复和主从复制.

2.6.4 GTID的开启和配置
vim /etc/my.cnf
gtid-mode=on
enforce-gtid-consistency=true

2.6.5 查看GTID信息
create database gtid charset utf8mb4;
show master status;
use gtid;
create table t1(id int);
show master status;
insert into t1 values(1);
commit;
show master status;
drop database gtid;

2.6.6 基于GTID,binlog恢复
(1) 截取日志
cd /usr/local/mysql/data/binlog/
mysqlbinlog --include-gtids='820f8917-d358-11ec-b243-000c29cbdce4:1-3' mysql-bin.000001 >/tmp/gtid.sql

(2)恢复
mysql> set sql_log_bin=0;
mysql> source /tmp/gtid.sql
mysql> set sql_log_bin=1;
(3) 报错
ERROR 1049 (42000): Unknown database 'gtid'
Query OK, 0 rows affected (0.00 sec)
ERROR 1046 (3D000): No database selected
为什么报错?
因为幂等性的检查,1-3事务已经做过了.

(4)正确的做法?
mysqlbinlog --skip-gtids --include-gtids='820f8917-d358-11ec-b243-000c29cbdce4:1-3' mysql-bin.000001 >/tmp/gtid.sql
--skip-gtids 作用:在导出时,忽略原有的gtid信息,恢复时生成最新的gtid信息

(5) 恢复

set sql_log_bin=0;
source /tmp/gtid.sql
set sql_log_bin=1;

2.6.7 GTID相关的参数
--skip-gtids
--include-gtids='e2e9b01e-9687-11eb-b577-000c29b0384b:6','e2e9b01e-9687-11eb-b577-000c29b0384b:8'
--exclude-gtids='e2e9b01e-9687-11eb-b577-000c29b0384b:6','e2e9b01e-9687-11eb-b577-000c29b0384b:8'

3.慢日志(slow-log)
3.1 作用
记录运行较慢的语句,优化过程中常用的工具日志.
3.2 如何配置

查看慢日志状态

show variables like '%slow_query_log%';

开关

slow_query_log=1

文件位置及名字

slow_query_log_file=/data/slow.log

设定慢查询时间

long_query_time=0.1

没走索引的语句也记录

log_queries_not_using_indexes

vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/data/slow.log
long_query_time=0.1
log_queries_not_using_indexes

3.3 模拟慢查询
create database test;
use test;
source /root/t100w.sql

select from t100w limit 100;
select from t100w where id=1568;
select from t100w where num=1100;
select from t100w where num=102000 order by k1;
select * from t100w where k2='MN88';

3.4 分析慢日志
mysqldumpslow -s c -t 10 /usr/local/mysql/data/slow.log

3.5 第三方工具(自己扩展)

下载地址：https://www.percona.com/downloads/percona-toolkit/LATEST/
yum -y install perl-DBI perl-DBD-MySQL perl-Time-HiRes perl-IO-Socket-SSL perl-Digest-MD5 perl-TermReadKey
rpm -ivh percona-toolkit-3.3.0-1.el7.x86_64.rpm
toolkit工具包中的命令:
pt-query-digest /usr/local/mysql/data/slow.log

Anemometer基于pt-query-digest将MySQL慢查询可视化

文章标签：
云数据库 RDS MySQL 版
日志服务
开发工具
数据可视化
SQL
关系型数据库
索引
文件存储
MySQL
安全
数据库
存储
关键词：
云数据库 RDS MySQL 版日志
mysql日志服务
日志服务mysql
相关实践学习
如何快速连接云数据库RDS MySQL
本场景介绍如何通过阿里云数据管理服务DMS快速连接云数据库RDS MySQL，然后进行数据表的CRUD操作。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
征服Bug
目录
相关文章
绿水长流*z
|
17天前
|
SQL 关系型数据库 MySQL
MySQL事务日志-Undo Log工作原理分析
事务的持久性是交由Redo Log来保证，原子性则是交由Undo Log来保证。如果事务中的SQL执行到一半出现错误，需要把前面已经执行过的SQL撤销以达到原子性的目的，这个过程也叫做"回滚"，所以Undo Log也叫回滚日志。
绿水长流*z
30 7
MySQL事务日志-Undo Log工作原理分析
蓝易云
|
1月前
|
SQL 存储 关系型数据库
Mysql并发控制和日志
通过深入理解和应用 MySQL 的并发控制和日志管理技术，您可以显著提升数据库系统的效率和稳定性。
蓝易云
116 10
栈江湖
|
28天前
|
安全 关系型数据库 MySQL
MySQL崩溃保险箱：探秘Redo/Undo日志确保数据库安全无忧！
《MySQL崩溃保险箱：探秘Redo/Undo日志确保数据库安全无忧！》介绍了MySQL中的三种关键日志：二进制日志（Binary Log）、重做日志（Redo Log）和撤销日志（Undo Log）。这些日志确保了数据库的ACID特性，即原子性、一致性、隔离性和持久性。Redo Log记录数据页的物理修改，保证事务持久性；Undo Log记录事务的逆操作，支持回滚和多版本并发控制（MVCC）。文章还详细对比了InnoDB和MyISAM存储引擎在事务支持、锁定机制、并发性等方面的差异，强调了InnoDB在高并发和事务处理中的优势。通过这些机制，MySQL能够在事务执行、崩溃和恢复过程中保持
栈江湖
66 3
拉丁解牛说技术
|
1月前
|
SQL 存储 缓存
MySQL进阶突击系列(02)一条更新SQL执行过程 | 讲透undoLog、redoLog、binLog日志三宝
本文详细介绍了MySQL中update SQL执行过程涉及的undoLog、redoLog和binLog三种日志的作用及其工作原理，包括它们如何确保数据的一致性和完整性，以及在事务提交过程中各自的角色。同时，文章还探讨了这些日志在故障恢复中的重要性，强调了合理配置相关参数对于提高系统稳定性的必要性。
拉丁解牛说技术
115 6
赵渝强老师
|
2月前
|
SQL 关系型数据库 MySQL
【赵渝强老师】MySQL的全量日志文件
MySQL全量日志记录所有操作的SQL语句，默认禁用。启用后，可通过`show variables like %general_log%检查状态，使用`set global general_log=ON`临时开启，执行查询并查看日志文件以追踪SQL执行详情。
赵渝强老师
69 4
赵渝强老师
|
2月前
|
关系型数据库 MySQL 数据库
【赵渝强老师】MySQL的binlog日志文件
MySQL的binlog日志记录了所有对数据库的更改操作（不包括SELECT和SHOW），主要用于主从复制和数据恢复。binlog有三种模式，可通过设置binlog_format参数选择。示例展示了如何启用binlog、设置格式、查看日志文件及记录的信息。
赵渝强老师
243 6
赵渝强老师
|
2月前
|
SQL 关系型数据库 MySQL
【赵渝强老师】MySQL的慢查询日志
MySQL的慢查询日志用于记录执行时间超过设定阈值的SQL语句，帮助数据库管理员识别并优化性能问题。通过`mysqldumpslow`工具可查看日志。本文介绍了如何检查、启用及配置慢查询日志，并通过实例演示了慢查询的记录与分析过程。
赵渝强老师
252 3
蓝染-惣右介
|
2月前
|
XML 安全 Java
【日志框架整合】Slf4j、Log4j、Log4j2、Logback配置模板
本文介绍了Java日志框架的基本概念和使用方法，重点讨论了SLF4J、Log4j、Logback和Log4j2之间的关系及其性能对比。SLF4J作为一个日志抽象层，允许开发者使用统一的日志接口，而Log4j、Logback和Log4j2则是具体的日志实现框架。Log4j2在性能上优于Logback，推荐在新项目中使用。文章还详细说明了如何在Spring Boot项目中配置Log4j2和Logback，以及如何使用Lombok简化日志记录。最后，提供了一些日志配置的最佳实践，包括滚动日志、统一日志格式和提高日志性能的方法。
蓝染-惣右介
721 31
【日志框架整合】Slf4j、Log4j、Log4j2、Logback配置模板
运维有小邓
|
1月前
|
监控 安全 Apache
什么是Apache日志？为什么Apache日志分析很重要？
Apache是全球广泛使用的Web服务器软件，支持超过30%的活跃网站。它通过接收和处理HTTP请求，与后端服务器通信，返回响应并记录日志，确保网页请求的快速准确处理。Apache日志分为访问日志和错误日志，对提升用户体验、保障安全及优化性能至关重要。EventLog Analyzer等工具可有效管理和分析这些日志，增强Web服务的安全性和可靠性。
运维有小邓
59 9
最好zzz
|
3月前
|
XML JSON Java
Logback 与 log4j2 性能对比：谁才是日志框架的性能王者？
【10月更文挑战第5天】在Java开发中，日志框架是不可或缺的工具，它们帮助我们记录系统运行时的信息、警告和错误，对于开发人员来说至关重要。在众多日志框架中，Logback和log4j2以其卓越的性能和丰富的功能脱颖而出，成为开发者们的首选。本文将深入探讨Logback与log4j2在性能方面的对比，通过详细的分析和实例，帮助大家理解两者之间的性能差异，以便在实际项目中做出更明智的选择。
最好zzz
405 3

热门文章

最新文章

  • 1
    java项目中jar启动执行日志报错：no main manifest attribute, in /www/wwwroot/snow-server/z-server.jar-jar打包的大小明显小于正常大小如何解决
  • 2
    云上数据安全保护：敏感日志扫描与脱敏实践详解
  • 3
    3D-Speaker：阿里通义开源的多模态说话人识别项目，支持说话人识别、语种识别、多模态识别、说话人重叠检测和日志记录
  • 4
    网络安全视角：从地域到账号的阿里云日志审计实践
  • 5
    开源更新｜通义3D-Speaker多说话人日志功能
  • 6
    日志服务 SQL 引擎全新升级
  • 7
    实时记录和查看Apache 日志
  • 8
    【赵渝强老师】MongoDB的Journal日志
  • 9
    启用Linux防火墙日志记录和分析功能
  • 10
    RAG Logger：专为检索增强生成(RAG)应用设计的开源日志工具，支持查询跟踪、性能监控
  • 1
    Docker安装mysql详细教程， mysqld: Can‘t read dir of ‘/etc/mysql/conf.d/‘（报错已解决）
    608
  • 2
    基于PHP+MYSQL开发制作的趣味测试网站源码
    80
  • 3
    MaxCompute产品使用合集之如何实现类似mysql实例中的数据库功能
    78
  • 4
    MaxCompute产品使用合集之用flink mysql的数据同步到mc的Transaction Table2.0，时间会比mysql的时间多8小时，是什么导致的
    67
  • 5
    利用 DataWorks 数据推送定期推播 MySQL 或 StarRocks Query 诊断信息
    134
  • 6
    java 如何实现监听mysql的binlog文件
    491
  • 7
    Mysql:如何自定义导出表结构
    115
  • 8
    MySQL高级功能与优化策略深度探索
    82
  • 9
    MySQL语句详解：从基础到进阶的全面指南
    117
  • 10
    MySQL连接池DataSource是什么？
    172

    • 相关课程

    更多
  • MySQL企业常见架构与调优经验分享
  • 云数据库MySQL版快速上手教程
  • 阿里云云原生数据仓库AnalyticDB MySQL版 使用教程
  • MySQL实战进阶
  • 数据库及SQL/MySQL基础
  • 云数据库MySQL快速入门

    • 相关电子书

    更多
  • 搭建电商项目架构连接MySQL
  • 搭建4层电商项目架构，实战连接MySQL
  • PolarDB MySQL引擎重磅功能及产品能力盛大发布

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • MySQL数据库快速部署实践
  • MySQL引擎及架构优化
  • 基于EBS部署高性能的MySQL服务
  • 使用CloudLens采集RDS日志并进行审计分析

    • 推荐镜像

    更多
  • mysql
  • mariadb
  • mongodb
    • 下一篇
    阿里云开通OSS存储服务详细流程