mysql log 记录报错 sql语句_MySQL日志

说明

MySQL日志，是工具日志。需要时开启，不需要时关闭

日志列举

错误日志 排错

慢日志 优化

二进制日志 主从，备份

常规日志

审计日志

错误日志

作用： 排错MySQL执行过程中的故障

配置：

默认开启的

默认路径: DATADIR/hostname.err

人为定制

select @@log_error

log_error=/tmp/mysql3306.log

二进制日志

sql层的日志

作用

主从要依赖于二进制日志

数据恢复要依赖二进制日志

配置

默认没有开启

server_id=5 标识这个实例的一个号码，在5.7以后的版本，要想开binlog，必须要有server_id

log_bin=/data/binlog/mysql-bin

binlog_format=row 5.7版本默认配置

二进制日志记录了什么?

记录的是数据库所有变更类操作的日志

DCL DDL DML 的增删改

DDL 和 DCL

以语句的方式，原模原样的记录

DML

1) 记录的是已提交的事务DML语句

2) DML记录的格式(statement,row,mixed),通过binlog_format参数控制。这个参数仅仅会影响到DML语句

说明：

statement:(SBR) 语句模式，你做什么语句，就记录什么语句。记录日志

row(RBR)： 行记录模式，记录了行的变化

mixed(MBR)： 混合模式(不可控)

二进制日志记录单元

event事件是二进制记录的最小单元

DDL，每一个语句就是一个事件

DML，一个事务包含了多个语句，每个语句就是一个事件。只有提交的事务，才会被记录

begin 事件1

a 事件2

b 事件3

commit 事件4

event事件的开始和结束号码

作用: 方便我们从日志中截取我们想要的日志事件

二进制日志的操作和管理

查看二进制的日志位置

select @@log_bin;

show variables like '%log_bin%';

查看所有已存在的二进制日志

show binary logs;

flush logs; 滚动一个新的日志

查看二进制日志事件

show master status;

查看二进制日志事件

1 .先查看正在使用的日志文件

show master status;

2 .查看二进制日志的事件

show binlog events in 'mysql-bin.00004'

查看二进制内容

mysqlbinlog mysql-bin.000004

mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000004 把rows格式翻译成可读文件

截取二进制日志

mysqlbinlog --start-position=219 --stop-position=335 mysql-bin.00004 > /tmp/a.sql

通过二进制日志恢复数据

？

mysqlbinlog 针对库操作

mysqlbinlog -d haoge mysql-bin.00004 指针对某个库进行操作

binlog的gtid记录模式的管理

GTID的介绍

对于binlog中的每一个事务(跟Innodb中的DML事务不一样)，生成一个GTID号，对于DDL和DCL来讲。这个事务是一个event(语句)，就会打上GTID号。对于DML语句来讲，从begin到commit就是一个GTID号

GTID的组成

server-uuid:TID

这个TID是二进制日志的事务号(SQL层面的)，TXID是innodb中的事务ID(存储引擎方面的)

server-uuid存放在datadir/auto.cnf 里面

TID是一个自增长的数字，从1开始

GTID的幂等性

如果拿有GTID的日志去恢复，系统会检查是否有相同的GTID号，有相同的就自动跳过。会影响binlog的恢复和主从复制

关于GTID的开启和配置

vim /etc/my.cnf

gtid-mode=on

enforce-gtid-consistency=true

查看GTID

show master status

基于GTID，binlog恢复

截取日志

mysqlbinlog --include-gtids='server-uuid:1-3' mysql-bin.000005 > /tmp/gtid.sql

截取1-3号事务

2) 恢复

set sql_log_bin=0

source /tmp/gtid.sql

set sql_log_bin=1

3) 报错

unknown database 'gtid'

4)报错的原因？

GTID的幂等性，1-3事务已经都做过了

5)正确的做法

mysqlbinlog --skip-gtids --include-gtids='server-uuid:1-3' mysql-bin.00005 > /tmp/gtid.sql

GTID的相关的参数

--skip-gtids: 在导出时，忽略原有的gtid信息，恢复时生成新的gtid信息

--include-gtids: 想截取哪些gtid

--exclude-gtids: 排除哪些gtid

--exclude-gtids='server-uuid:TID',server-uuid:TID 排除多个gtid不连续的

--exclude-gtids='server-uuid:TID-TID' 排除多个gtid连续的

慢日志

作用

记录MySQL工作过程中，运行较慢的语句。优化过程中常用的工具日志

配置

select @@long_query_time; =》 10.000000 最小粒度 0.000001秒

# 开关

slow_query_log=1

# 文件位置及名字

slow_query_log_file=/data/mysql/slow.log

# 设置慢查询的时间

long_query_time=0.1

# 没走索引的语句记录

log_queries_not_using_indexs

分析慢日志

mysqldumpslow -s c -t 10 /data/mysql/slow.log

-s 排序 sort

c 次数 count

-t top 前10个语句

第三方工具

pt-query-diagest 工具分析

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

日志相关补充

双一标准的binlog

sync_binlog=1 每次提交事务，都会立即刷写binlog

binlog如何进行清理和删除

自动

expire_logs_days 默认二进制日志过期参数，一般企业保留两轮全备周期，

例： 7天一次备份

expire_logs_days=15

手工

purge binary logs to 'mysql-bin.000010' 删除mysql-bin.00001到mysql-bin.00009的日志

reset master 清楚所有的binlog日志，主从这么做，从库必崩

日志如何进行滚动

flush logs

数据库重启

max_binlog_size=10734824(1G) 刷满之后，就自动切换