mysql日志介绍

内容导航：

mysql日志介绍

MySQL都有哪些日志

MySQL软件的日志操作介绍

mysql有几种日志格式

一、mysql日志介绍

错误日志(log_error)

--记录启动\关闭\日常运行过程中,状态信息,警告,错误

默认就是开启的:  /数据路径下/hostname.err手工设定:Master [(none)]>select @@log_error;vim /etc/my.cnflog_error=/var/log/mysql.loglog_timestamps=system重启生效show variables like 'log_error';

默认位置：

DATADIR/hostname.err

配置方式

vim .etc/my.cnf

log_error=/data/mysql/data/mysql.log

查看：

mysql> select @@log_error;+----------------------+| @@log_error          |+----------------------+| /data/3307/mysql.log |+----------------------+1 row in set (0.00 sec)

binlog(binary logs):二进制日志 *****

(1)备份恢复必须依赖二进制日志(2)主从环境必须依赖二进制日志

binlog配置 (5.7必须加server_id)

注意：MySQL默认是没有开启二进制日志的。基础参数查看:开关:[(none)]>select @@log_bin;日志路径及名字[(none)]>select @@log_bin_basename;服务ID号:[(none)]>select @@server_id;二进制日志格式:[(none)]>select @@binlog_format;双一标准之二:[(none)]>select @@sync_binlog;

log_error=/data/mysql/data/mysql.loglog_bin=/data/binlog/3306/mysql-bin #/data/binlog/3306/ ：目录 mysql-bin：文件名

[root@localhost 3306]# pwd/data/binlog/3306[root@localhost 3306]# ll总用量 8-rw-r----- 1 mysql mysql 154 9月   4 13:51 mysql-bin.000001-rw-r----- 1 mysql mysql  35 9月   4 13:51 mysql-bin.index

查看配置

mysql> show variables like '%log_bin%';+---------------------------------+-----------------------------------+| Variable_name                   | Value                             |+---------------------------------+-----------------------------------+| log_bin                         | ON                                || log_bin_basename                | /data/binlog/3306/mysql-bin       || log_bin_index                   | /data/binlog/3306/mysql-bin.index || log_bin_trust_function_creators | OFF                               || log_bin_use_v1_row_events       | OFF                               || sql_log_bin                     | ON                                |+---------------------------------+-----------------------------------+6 rows in set (0.00 sec)

binlog记录了什么?

-----binlog是SQL层的功能。记录的是变更SQL语句，不记录查询语句。

记录SQL语句种类

DDL ：原封不动的记录当前DDL(statement语句方式)。DCL ：原封不动的记录当前DCL(statement语句方式)。DML ：只记录已经提交的事务DML

DML三种记录方式binlog_format（binlog的记录格式）参数影响（1）statement（5.6默认）SBR(statement based replication) ：语句模式原封不动的记录当前DML。（2）ROW(5.7 默认值)RBR(ROW based replication) ：记录数据行的变化(用户看不懂，需要工具分析)（3）mixed（混合）MBR(mixed based replication)模式 ：以上两种模式的混合

mysql> show variables like 'bnlog_format';+---------------+-------+| Variable_name | Value |+---------------+-------+| binlog_format | ROW   |+---------------+-------+1 row in set (0.00 sec)

面试题

SBR与RBR模式的对比STATEMENT：可读性较高，日志量少，但是不够严谨ROW      ：可读性很低，日志量大，足够严谨update t1 set xxx=xxx where id>1000   ? -->一共500w行，row模式怎么记录的日志为什么row模式严谨？id  name    intimeinsert into t1 values(1,'zs',now())我们建议使用：row记录模式

event（事件）是什么?事件的简介

二进制日志的最小记录单元对于DDL,DCL,一个语句就是一个event对于DML语句来讲:只记录已提交的事务。例如以下列子,就被分为了4个eventbegin; 120 - 340DML1 340 - 460DML2 460 - 550commit; 550 - 760

event的组成

三部分构成:(1) 事件的开始标识(2) 事件内容(3) 事件的结束标识Position:开始标识: at 194结束标识: end_log_pos 254194? 254?某个事件在binlog中的相对位置号位置号的作用是什么？为了方便我们截取事件

查看一共多少个binlog

Master [(none)]>show binary logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |       154 |+------------------+-----------+1 row in set (0.01 sec)Master [(none)]>flush logs;Query OK, 0 rows affected (0.03 sec)Master [(none)]>flush logs;Query OK, 0 rows affected (0.01 sec)Master [(none)]>show binary logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| mysql-bin.000001 |       201 || mysql-bin.000002 |       201 || mysql-bin.000003 |       154 |+------------------+-----------+3 rows in set (0.00 sec)Master [(none)]>

通过执行flush logs命令之后，再次查询binary log信息，发现已经使用了一个新的bin log文件了。flush logs命令的作用就是关闭当前使用的binary log，然后打开一个新的binary log文件，binlog文件的序号递增加1

查看mysql正在使用的日志文件

Master [(none)]>show master status;+------------------+----------+--------------+------------------+-------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000003 |      154 |              |                  |                   |+------------------+----------+--------------+------------------+-------------------+Master [(none)]>

file：当前MySQL正在使用的文件名Position：最后一个事件的结束位置号

日志内容查看event查看

Master [binlog]>show binlog events in 'mysql-bin.000003';+------------------+-----+----------------+-----------+-------------+----------------------------------------+| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                   |+------------------+-----+----------------+-----------+-------------+----------------------------------------+| mysql-bin.000003 |   4 | Format_desc    |         6 |         123 | Server ver: 5.7.20-log, Binlog ver: 4  || mysql-bin.000003 | 123 | Previous_gtids |         6 |         154 |                                        || mysql-bin.000003 | 154 | Anonymous_Gtid |         6 |         219 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   || mysql-bin.000003 | 219 | Query          |         6 |         319 | create database binlog                 || mysql-bin.000003 | 319 | Anonymous_Gtid |         6 |         384 | SET @@SESSION.GTID_NEXT= 'ANONYMOUS'   || mysql-bin.000003 | 384 | Query          |         6 |         486 | use `binlog`; create table t1 (id int) |+------------------+-----+----------------+-----------+-------------+----------------------------------------+Log_name：binlog文件名Pos：开始的position    *****Event_type：事件类型Format_desc：格式描述，每一个日志文件的第一个事件，多用户没有意义，MySQL识别binlog必要信息Server_id：mysql服务号标识End_log_pos：事件的结束位置号 *****Info：事件内容*****补充:SHOW BINLOG EVENTS   [IN 'log_name']   [FROM pos]   [LIMIT [offset,] row_count][root@db01 binlog]# mysql -e "show binlog events in 'mysql-bin.000004'" |grep drop

binlog文件内容详细查看

mysqlbinlog /data/mysql/mysql-bin.000006mysqlbinlog --base64-output=decode-rows -vvv /data/binlog/mysql-bin.000003mysqlbinlog  -d binlog /data/binlog/mysql-bin.000003[root@db01 binlog]# mysqlbinlog --start-datetime='2019-05-06 17:00:00' --stop-datetime='2019-05-06 17:01:00'  /data/binlog/mysql-bin.000004 

基于Position号进行日志截取

核心就是找截取的起点和终点--start-position=321--stop-position=513 mysqlbinlog --start-position=219 --stop-position=1347 /data/binlog/mysql-bin.000003 >/tmp/bin.sql案例: 使用binlog日志进行数据恢复模拟:1. [(none)]>create database binlog charset utf8;2. [(none)]>use binlog;[binlog]>create table t1(id int);3. [binlog]>insert into t1 values(1);[binlog]>commit;[binlog]>insert into t1 values(2);[binlog]>commit;[binlog]>insert into t1 values(3);[binlog]>commit;4. [binlog]>drop database binlog;恢复:[(none)]>show master status ;[(none)]>show binlog events in 'mysql-bin.000004';[root@db01 binlog]# mysqlbinlog --start-position=1227 --stop-position=2342 /data/binlog/mysql-bin.000004 >/tmp/bin.sql[(none)]>set sql_Log_bin=0;#设为0后，在Master数据库上执行的语句临时都不记录binlog，退出窗口可以恢复[(none)]>source /tmp/bin.sql

-----上述通过pos位置恢复 需要关闭gitd----经过测试发现 开起来gitd，通过pos位置无法恢复数据

mysqlbinlog --start-position=219 --stop-position=1012 /data/binlog/3306/mysql-bin.000002 >/tmp/bin.sql

binlog日志的GTID新特性GTID 介绍

5.6 版本新加的特性,5.7中做了加强5.6 中不开启,没有这个功能.5.7 中的GTID,即使不开也会有自动生成SET @@SESSION.GTID_NEXT= 'ANONYMOUS'

GTID(Global Transaction ID)

是对于一个已提交事务的编号，并且是一个全局唯一的编号。它的官方定义如下：

GTID = source_id ：transaction_id7E11FA47-31CA-19E1-9E56-C43AA21293967:29

重要参数介绍：

vim /etc/my.cnfgtid-mode=onenforce-gtid-consistency=truesystemctl restart mysqldMaster [(none)]>create database gtid charset utf8;Query OK, 1 row affected (0.01 sec)Master [(none)]>show master status ;+------------------+----------+--------------+------------------+----------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                      |+------------------+----------+--------------+------------------+----------------------------------------+| mysql-bin.000004 |      326 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1 |+------------------+----------+--------------+------------------+----------------------------------------+1 row in set (0.00 sec)Master [(none)]>use gtidDatabase changedMaster [gtid]>create table t1 (id int);Query OK, 0 rows affected (0.01 sec)Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000004 |      489 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1-2 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)Master [gtid]>create table t2 (id int);Query OK, 0 rows affected (0.01 sec)Master [gtid]>create table t3 (id int);Query OK, 0 rows affected (0.02 sec)Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000004 |      815 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1-4 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)Master [gtid]>begin;Query OK, 0 rows affected (0.00 sec)Master [gtid]>insert into t1 values(1);Query OK, 1 row affected (0.00 sec)Master [gtid]>commit;Query OK, 0 rows affected (0.00 sec)Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000004 |     1068 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1-5 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)Master [gtid]>begin;Query OK, 0 rows affected (0.00 sec)Master [gtid]>insert into t2 values(1);Query OK, 1 row affected (0.00 sec)Master [gtid]>commit;Query OK, 0 rows affected (0.01 sec)Master [gtid]>show master status ;+------------------+----------+--------------+------------------+------------------------------------------+| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |+------------------+----------+--------------+------------------+------------------------------------------+| mysql-bin.000004 |     1321 |              |                  | dff98809-55c3-11e9-a58b-000c2928f5dd:1-6 |+------------------+----------+--------------+------------------+------------------------------------------+1 row in set (0.00 sec)

基于GTID进行查看binlog

具备GTID后,截取查看某些事务日志:--include-gtids--exclude-gtidsmysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4'  /data/binlog/mysql-bin.000004跳过多个事务idmysqlbinlog --include-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:1-6' --exclude-gtids='dff98809-55c3-11e9-a58b-000c2928f5dd:4,dff98809-55c3-11e9-a58b-000c2928f5dd:5'  /data/binlog/mysql-bin.000004

GTID的幂等性

开启GTID后,MySQL恢复Binlog时,重复GTID的事务不会再执行了就想恢复?怎么办?--skip-gtidsmysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:4' /data/binlog/mysql-bin.000004 > /tmp/binlog.sqlset sql_log_bin=0;source /tmp/binlog.sqlset sql_log_bin=1;

使用二进制日志恢复数据案例故障环境介绍

创建了一个库  db, 导入了表t1 ,t1表中录入了很多数据一个开发人员,drop database db;没有备份,日志都在.怎么恢复?思路:找到建库语句到删库之前所有的日志,进行恢复.(开启了GTID模式)故障案例模拟:(0) drop database if exists db ;(1) create database db charset utf8;     (2) use db;(3) create table t1 (id int);(4) insert into t1 values(1),(2),(3);(5) insert into t1 values(4),(5),(6);(6) commit(7) update t1 set id=30 where id=3;(8) commit;(9) delete from t1 where id=4;(10)commit;(11)insert into t1 values(7),(8),(9);(12)commit;(13)drop database db;========================drop database if exists db ;create database db charset utf8; use db;create table t1 (id int);insert into t1 values(1),(2),(3);insert into t1 values(4),(5),(6);commit;update t1 set id=30 where id=3;commit;delete from t1 where id=4;commit;insert into t1 values(7),(8),(9);commit;drop database db;=======运行以上语句，模拟故障场景需求：将数据库恢复到以下状态（提示第9步和第13步是误操作，其他都是正常操作）

恢复过程(无GTID时的恢复)

1，查看当前使用的 binlog文件

oldguo [db]>show master status ;+------------------+----------+--------------+------------------+-------------------+| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |+------------------+----------+--------------+------------------+-------------------+| mysql-bin.000006 |    1873 |              |                  |                  |+------------------+----------+--------------+------------------+-------------------+2.查看事件：第一段：| mysql-bin.000006 |  813 | Query      |        1 |        907 | use `db`; create table t1 (id int)                  || mysql-bin.000006 |  907 | Query      |        1 |        977 | BEGIN                                              || mysql-bin.000006 |  977 | Table_map  |        1 |        1020 | table_id: 77 (db.t1)                                || mysql-bin.000006 | 1020 | Write_rows  |        1 |        1070 | table_id: 77 flags: STMT_END_F                      || mysql-bin.000006 | 1070 | Table_map  |        1 |        1113 | table_id: 77 (db.t1)                                || mysql-bin.000006 | 1113 | Write_rows  |        1 |        1163 | table_id: 77 flags: STMT_END_F                      || mysql-bin.000006 | 1163 | Xid        |        1 |        1194 | COMMIT /* xid=74 */                                || mysql-bin.000006 | 1194 | Query      |        1 |        1264 | BEGIN                                              || mysql-bin.000006 | 1264 | Table_map  |        1 |        1307 | table_id: 77 (db.t1)                                || mysql-bin.000006 | 1307 | Update_rows |        1 |        1353 | table_id: 77 flags: STMT_END_F                      || mysql-bin.000006 | 1353 | Xid        |        1 |        1384 | COMMIT /* xid=77 */   mysqlbinlog --start-position=813 --stop-position=1384 /data/mysql/mysql-bin.000006 >/tmp/bin1.sql 

第二段：

| mysql-bin.000006 | 1568 | Query      |        1 |        1638 | BEGIN                                              || mysql-bin.000006 | 1638 | Table_map  |        1 |        1681 | table_id: 77 (db.t1)                                || mysql-bin.000006 | 1681 | Write_rows  |        1 |        1731 | table_id: 77 flags: STMT_END_F                      || mysql-bin.000006 | 1731 | Xid        |        1 |        1762 | COMMIT /* xid=81 */ mysqlbinlog --start-position=1568 --stop-position=1762 /data/mysql/mysql-bin.000006 >/tmp/bin2.sql

3，恢复

set sql_log_bin=0;source /tmp/bin1.sqlsource /tmp/bin2.sqlset sql_log_bin=1;oldguo [db]>select * from t1;+------+| id  |+------+|    1 ||    2 ||  30 ||    4 ||    5 ||    6 ||    7 ||    8 ||    9 |

有GTID的恢复:

mysqlbinlog --skip-gtids --include-gtids='3ca79ab5-3e4d-11e9-a709-000c293b577e:7-12' mysql-bin.000004> /tmp/bin.sql

恢复

set sql_log_bin=0;source /tmp/bin.sql

二进制日志其他操作自动清理日志

show variables like '%expire%';expire_logs_days  0   自动清理时间,是要按照全备周期+1set global expire_logs_days=8;永久生效:修改配置文件vim my.cnfexpire_logs_days=15;企业建议,至少保留两个全备周期+1的binlog

手工清理（危险）

PURGE BINARY LOGS BEFORE now() - INTERVAL 3 day;PURGE BINARY LOGS TO 'mysql-bin.000010';注意:不要手工 rm binlog文件1. my.cnf binlog关闭掉,启动数据库2.把数据库关闭,开启binlog,启动数据库删除所有binlog,并从000001开始重新记录日志

reset master; 主从关系中，主库执行此操作，主从环境必崩

日志是怎么滚动

flush logs; 重启mysql也会自动滚动一个新的日志文件达到1G大小(max_binlog_size)| max_binlog_size                          | 1073741824     备份时,加入参数也可以自动滚动

slow_log 慢日志

记录慢SQL语句的日志,定位低效SQL语句的工具日志

mysql> show variables like '%slow%';+---------------------------+-------------------------------------+| Variable_name             | Value                               |+---------------------------+-------------------------------------+| log_slow_admin_statements | OFF                                 || log_slow_slave_statements | OFF                                 || slow_launch_time          | 2                                   || slow_query_log            | ON                                  || slow_query_log_file       | /data/mysql/data/localhost-slow.log |+---------------------------+-------------------------------------+5 rows in set (0.00 sec)

开启慢日志(默认没开启)

开关:slow_query_log=1 文件位置及名字 slow_query_log_file=/data/mysql/slow.log设定慢查询时间:long_query_time=0.1没走索引的语句也记录:log_queries_not_using_indexes #不走索引vim /etc/my.cnfslow_query_log=1 slow_query_log_file=/data/mysql/slow.loglong_query_time=0.1log_queries_not_using_indexessystemctl restart mysqld

mysqldumpslow 分析慢日志

mysqldumpslow -s c -t 10 /data/mysql/slow.log# 第三方工具(自己扩展)https:///qq_41600067/article/details/112952251
https://naibawu.com/1250.html
https:///yaomaomao/p/12672283.html
本文来自博客园，作者：孙龙-程序员，转载请注明原文链接：https:///sunlong88/p/16655005.html
一、MySQL都有哪些日志
普通查询日志
二进制日志
慢查询日志

Mysql错误日志主要记录Mysql实例每次启动、停止的详细信息，以及Mysql实例运行过程中产生的警告或者错误信息，与其它日志不同，Mysql错误日志必须开启，无法关闭。 
Mysql general log记录了Mysql运行的所有操作，无论这些操作执行成功与否。另外还包括一些事件，例如客户端连接断开的一些信息，默认不开启。 
慢日志记录执行时间过程和没有使用索引的查询语句，报错select、update、delete以及insert语句，慢日志只会记录执行成功的语句。 
二进制日记记录数据库的变化情况，内容报错数据库所有的更新操作，ddl和dml。数据库管理员可以通过二进制日志查看数据库过去某一时刻发生了哪些变化，必要时可以使用二进制日志恢复数据库。二进制文件内容为二进制信息，不可直接查看。
二、MySQL软件的日志操作介绍
方法/步骤
1、首先，登陆mysql后，执行sql语句：
show
variables
like
'log_bin';
查看是否日志开启，详细结果如图：
2、#错误日志

log-error
开启方式：
 
在my.ini的[mysqld]选项下：
添加代码：
log-error=E:\log-error.txt
记录内容：
主要是记录启动、运行或停止mysqld时出现的致命性问题，都是系统级的错误记录如图：
3、#查询日志

log
开启方式：
在my.ini的[mysqld]选项下：
添加代码：
log=E:/mysql_log.txt
记录内容：
主要是记录数据库建立的客户端连接和执行的语句如图：
4、#二进制日志
 log-bin
开启方式：
在my.ini的[mysqld]选项下：
添加代码：
log-bin=E:/mysql_log_bin
记录内容：
主要是记录所有的更改数据的语句，可使用mysqlbinlog命令恢复数据。如图：
#慢日志
 log-slow-queries 
开启方式：
在my.ini的[mysqld]选项下：
添加代码：

long_query_time
=1
(设定慢查询的标准，单位是s/秒)
log-slow-queries=
E:/mysql_log_slow.txt
记录内容：
主要是记录所有执行时间超过long_query_time的查询或没有使用索引的查询如图：
 
以上就是专题栏小编带来的mysql中文版教程，更多教程请看“
三、mysql有几种日志格式
mysql 5.5 有以下几种日志： 
错误日志(error log)： log-err
查询日志(general  query log)： log
慢查询日志: -log-slow-queries       
二进制日志 (binary log)： log-bin
中继日志( relay log)
innodb 在线redo 日志
默认情况下，没有启动任何log，可以通过log 选项来启动相关的log