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

MySQL日志

2023-06-23 123
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
RDS MySQL Serverless 基础系列，0.5-2RCU 50GB
推荐场景：
学生管理系统数据库设计 搭建个人博客
云数据库 RDS MySQL，集群系列 2核4GB
推荐场景：
搭建个人博客
云数据库 RDS MySQL，高可用系列 2核4GB
简介： 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
相关实践学习
如何在云端创建MySQL数据库
开始实验后，系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心，利用清洁计算为万物互联的新世界提供源源不断的能源动力，目前开服的区域包括中国（华北、华东、华南、香港）、新加坡、美国（美东、美西）、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程，来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品：云服务器ECS 云服务器 ECS（Elastic Compute Service）是一种弹性可伸缩的计算服务，助您降低 IT 成本，提升运维效率，使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
征服Bug
目录
相关文章
土木林森
|
1月前
|
SQL 关系型数据库 MySQL
【揭秘】MySQL binlog日志与GTID：如何让数据库备份恢复变得轻松简单？
【8月更文挑战第22天】MySQL的binlog日志记录数据变更，用于恢复、复制和点恢复；GTID为每笔事务分配唯一ID，简化复制和恢复流程。开启binlog和GTID后，可通过`mysqldump`进行逻辑备份，包含binlog位置信息，或用`xtrabackup`做物理备份。恢复时，使用`mysql`命令执行备份文件，或通过`innobackupex`恢复物理备份。GTID模式下的主从复制配置更简便。
土木林森
157 2
hh真是个慢性子
|
1月前
|
SQL 关系型数据库 MySQL
【MySQL】根据binlog日志获取回滚sql的一个开发思路
【MySQL】根据binlog日志获取回滚sql的一个开发思路
hh真是个慢性子
51 1
土木林森
|
28天前
|
API C# 开发框架
WPF与Web服务集成大揭秘：手把手教你调用RESTful API，客户端与服务器端优劣对比全解析！
【8月更文挑战第31天】在现代软件开发中，WPF 和 Web 服务各具特色。WPF 以其出色的界面展示能力受到欢迎，而 Web 服务则凭借跨平台和易维护性在互联网应用中占有一席之地。本文探讨了 WPF 如何通过 HttpClient 类调用 RESTful API，并展示了基于 ASP.NET Core 的 Web 服务如何实现同样的功能。通过对比分析，揭示了两者各自的优缺点：WPF 客户端直接处理数据，减轻服务器负担，但需处理网络异常；Web 服务则能利用服务器端功能如缓存和权限验证，但可能增加服务器负载。希望本文能帮助开发者根据具体需求选择合适的技术方案。
土木林森
65 0
土木林森
|
28天前
|
C# Windows 监控
WPF应用跨界成长秘籍：深度揭秘如何与Windows服务完美交互，扩展功能无界限！
【8月更文挑战第31天】WPF（Windows Presentation Foundation）是 .NET 框架下的图形界面技术，具有丰富的界面设计和灵活的客户端功能。在某些场景下，WPF 应用需与 Windows 服务交互以实现后台任务处理、系统监控等功能。本文探讨了两者交互的方法，并通过示例代码展示了如何扩展 WPF 应用的功能。首先介绍了 Windows 服务的基础知识，然后阐述了创建 Windows 服务、设计通信接口及 WPF 客户端调用服务的具体步骤。通过合理的交互设计，WPF 应用可获得更强的后台处理能力和系统级操作权限，提升应用的整体性能。
土木林森
63 0
土木林森
|
1月前
|
存储 关系型数据库 MySQL
深入MySQL：事务日志redo log详解与实践
【8月更文挑战第24天】在MySQL的InnoDB存储引擎中，为确保事务的持久性和数据一致性，采用了redo log（重做日志）机制。redo log记录了所有数据修改，在系统崩溃后可通过它恢复未完成的事务。它由内存中的redo log buffer和磁盘上的redo log file组成。事务修改先写入buffer，再异步刷新至磁盘，最后提交事务。若系统崩溃，InnoDB通过redo log重放已提交事务并利用undo log回滚未提交事务，确保数据完整。理解redo log工作流程有助于优化数据库性能和确保数据安全。
土木林森
174 0
土木林森
|
1月前
|
存储 SQL 关系型数据库
MySQL事务日志奥秘：undo log大揭秘，一文让你彻底解锁！
【8月更文挑战第24天】本文深入探讨了MySQL中undo log的关键作用及其在确保事务原子性和一致性方面的机制。MySQL通过记录事务前的数据状态，在需要时能回滚至初始状态。主要介绍InnoDB存储引擎下的undo log实现，包括undo segment和record的结构，而MyISAM则采用redo log保障持久性而非一致性。通过一个简单的SQL回滚示例，展示了undo log如何在实际操作中发挥作用，帮助读者更好地理解并运用MySQL事务管理功能。
土木林森
131 0
pbeskoyq7ffm4
|
1月前
|
SQL 存储 关系型数据库
MySQL日志,你知多少？
MySQL日志,你知多少？
pbeskoyq7ffm4
46 0
叫个什么名字
|
11天前
|
NoSQL 关系型数据库 MySQL
微服务架构下的数据库选择：MySQL、PostgreSQL 还是 NoSQL？
在微服务架构中，数据库的选择至关重要。不同类型的数据库适用于不同的需求和场景。在本文章中，我们将深入探讨传统的关系型数据库（如 MySQL 和 PostgreSQL）与现代 NoSQL 数据库的优劣势，并分析在微服务架构下的最佳实践。
叫个什么名字
77 9
小王老师呀
|
13天前
|
存储 SQL 关系型数据库
使用MySQL Workbench进行数据库备份
【9月更文挑战第13天】以下是使用MySQL Workbench进行数据库备份的步骤：启动软件后，通过“Database”菜单中的“管理连接”选项配置并选择要备份的数据库。随后，选择“数据导出”，确认导出的数据库及格式（推荐SQL格式），设置存储路径，点击“开始导出”。完成后，可在指定路径找到备份文件，建议定期备份并存储于安全位置。
小王老师呀
138 11
阿里云开发者
|
1月前
|
弹性计算 关系型数据库 数据库
手把手带你从自建 MySQL 迁移到云数据库，一步就能脱胎换骨
阿里云瑶池数据库来开课啦！自建数据库迁移至云数据库 RDS原来只要一步操作就能搞定！点击阅读原文完成实验就可获得一本日历哦～
阿里云开发者
60 0

热门文章

最新文章

  • 1
    Kubernetes全方位日志采集与管理的最佳实践 资料下载
  • 2
    Oracle自动清理日志脚本
  • 3
    海量游戏日志收集与分析
  • 4
    启用生产工单标准日志
  • 5
    Kafka实战－实时日志统计流程
  • 6
    浅谈Android系统开发中LOG的使用
  • 7
    解决华为手机不出现logcat日志的问题
  • 8
    Nginx状态监控及日志分析
  • 9
    打开haproxy的日志
  • 10
    Windows Phone 实用开发技巧（22）：使用日志记录当前信息与异常信息
  • 1
    MySQL的锁机制
    44
  • 2
    【Mysql】在多表查询过程中如果已经起了别名，就不能通过表名限定字段
    64
  • 3
    【MySQL-11】多表查询全解-【多表关系/内外自连接/子查询/多表查询案例链接】（可cv代码＆案例演示）
    85
  • 4
    【Mysql-12】一文解读【事务】-【基本操作/四大特性/并发事务问题/事务隔离级别】
    72
  • 5
    【MySQL-10】数据库函数-案例演示【字符串/数值/日期/流程控制函数】（代码演示＆可cv代码）
    441
  • 6
    【MySQL-10】DCL-数据控制语言-【管理用户＆权限控制】 （语法语句＆案例演示＆可cv案例代码）
    49
  • 7
    【MySQL-9】一文带你搞定 外键约束＆其【更新/删除行为】（可cv代码＆案例演示）
    160
  • 8
    【MySQL】DQL-案例练习-DQL基本介绍＆语法＆执行顺序（代码演示）
    50
  • 9
    【MySQL】DQL-排序查询-语法＆注意事项＆可cv例题语句
    50
  • 10
    【MySQL】DQL-排序查询-语法＆排序方式＆注意事项＆可cv例题语句
    51

    • 相关课程

    更多
  • MySQL高级应用 - 索引和锁
  • Linux MySQL服务器搭建与应用
  • 场景实践-基于阿里云Quick BI 对MOOC网站日志分析
  • MySQL完全自学手册
  • MySQL数据库入门学习
  • 云数据库MySQL版快速上手教程

    • 相关电子书

    更多
  • PostgresChina2018_赖思超_PostgreSQL10_hash索引的WAL日志修改版final
  • Kubernetes下日志实时采集、存储与计算实践
  • 日志数据采集与分析对接

    • 相关实验场景

    更多
  • MySQL基础-学生管理系统数据库设计
  • 如何快速连接云数据库RDS MySQL
  • 通过日志服务实现云资源OSS的安全审计
  • 如何将OSS数据导入至SLS中进行分析
  • 日志服务之使用Nginx模式采集日志
  • 如何开通MySQL的专属集群

    • 推荐镜像

    更多
  • mysql
  • postgresql
  • mongodb
    • 下一篇
    使用OSS上传下载文件