mysql相关日志学习笔记

相关日志

1. 错误日志：-log-err 在my.cnf中的mysqld节配置log-error=E:/wamp/logs/mysql_error.log，或是在启动数据库时 --log-error='/var/usr/local/mysql/log/local_error.log'

2. 查询日志(记录所有SQL语句)：

show variables like '%general_log%'

set global general_log=1 --开启全局日志，开启后所有SQL，set global log_output='table'，会被记录一张表中mysql.general_log，

或是set global log_output='file ，set global general_log_file='/tmp/general.log'

在my.cnf中的mysqld节配置,以上可以在mysqld节点配置，或是在数据库启动时--general-log=1开启或0关闭,--log-output='TABLE，FILE'可同时输出到表和文件，表是mysql.general_log,--general-log-file='/var/usr/local/mysql/log/general.log',如果在session级别控制，则可以在session中 set sql_log_off='on'或sql_log_off='off'

3. 慢查询日志：-log-slow-queries      

1) 查看慢查询时间     show variables like "long_query_time";默认10s   

2) 查看慢查询配置情况     show status like "%slow_queries%";    

3) 查看慢查询日志路径     show variables like "%slow%"; //存储位置、长SQL的阈值 E:\wamp\bin\mysql\mysql5.6.12\data\LittleHann-PC-slow.log long_query_time=5

set global slow_query_log=1临时开启慢查询日志

永久开启my.cnf

［mysqld］

slow_query_log=1

slow_query_log_file=/var/lib/mysql/xxx-slow.log

set long_query_time=5，是5秒，然后重新登录

开启慢查询日志后与创建存储过程冲突，用set global log_bib_trust_function_creators=1，这是临时解决方案，永久解决需要在my.cnf中的mysqld节加入log_bin_trust_function_creators=1配置

当用--slow_query_log=0或1关闭或开启， --slow_query_log_file=慢查询日志路径，可以使用--log-output=FILE或TABLE指定日志输出到文件或表mysql.slow_log

设置微秒级查询set global long_query_time=0.01，可以用mysqldumpslow查看慢日志文件内容。

mysqldumpslow -s r -t 3 文件名

mysqldumpslow -s c -t 3 文件名

mysqldumpslow -s s -t 10 -g 文件名

5. 二进制日志：-log-bin//记录除select语句之外的所有sql语句到日志中，可以用来恢复数据文件log-bin=E:/wamp/logs/bin；在启动是 --log-bin=0或1关闭或启动，如果没有给出文件名，会将主机名加上-bin作为文件名，默认写到datadir指定的目录。bin-log有三种格式：

statement、row、mixed,三种格式区别如下：

statement:日志中记录的都是sql语句，可以通过mysqlbinlog看到相关sql语句。优点：日志清晰易读，日志量少，对I/O影响小。缺点在某些情况下slave的日志会出错。

row:将每一行变更记录到日志中。优点：记录每一行记录变化的细节，不会出现在某些情况下无法复制的情况。 缺点：日志量大，对I/O影响较大。

mixed:这是mysql5.5的默认格式，mysql5.6的默认格式是statement,mysql5.7的默认格式是row。采用这种格式默认情况下用statement记录，但在一些特殊情况下用row来进行记录。比如采用了NDB存储引擎，此时对表的DML全部用row；客户端用了不确定的函数，比如current_user()，因不确定函数在主从中得到的值可能不同，导致主从数据库不一致。mixed尽可能利用他们的优点，避开他们的缺点。可以在global和session对binlog_format格式进行设置。

 

 

MySQL的binlog有有几种录入格式？分别有什么区别？

有三种格式，statement，row和mixed。

statement模式下，每一条会修改数据的sql都会记录在binlog中。不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。由于sql的执行是有上下文的，因此在保存的时候需要保存相关的信息，同时还有一些使用了函数之类的语句无法被记录复制。

row级别下，不记录sql语句上下文相关信息，仅保存哪条记录被修改。记录单元为每一行的改动，基本是可以全部记下来但是由于很多操作，会导致大量行的改动(比如alter table)，因此这种模式的文件保存的信息太多，日志量太大。

mixed，一种折中的方案，普通操作使用statement记录，当无法使用statement的时候使用row。

此外，新版的MySQL中对row级别也做了一些优化，当表结构发生变化的时候，会记录语句而不是逐行记录。

 

binlog日志读取：mysqlbinlog log-file

日志删除：

方法1:rest master该命令将删除所有binlog日志，新日志编号从000001开始。查看删除前日志：

system ls -ltr 日志文件名前缀，如localhost-bin*

方法2：purge master logs to 'mysql-bin.00012'命令将删除12号之前所有日志

方法3：purge master logs to 'yyyy-mm-dd hh24:mi:ss'，将删除该日期之前所产生的所有日志。

方法4：设置参数，--expire_logs_days=122，此参数的含义是设置日志的过期天数，过了指定的天数后将自动删除。在my.cnf中的mysqld节点expire_logs_day=3;用mysqladmin flush-log 触发日志文件更新。--binlog-do-db=db_name告诉主服务器只记录该数据的binlog,--binlog-ignore-db=db_name不将该数据库的binlog记录到日志中。如果有多个就需要多次引用 --binlog-ignore=db=xxx,或--binlog-db-name=sdd;

 

 

启动binlog:set sql_log_bin=0或1关闭或是开启；--innodb-safe-binlog此选项经常的--sync-binlog=N(是批积累了多少个事务后同步到磁盘数据文件)

 

如果想在主库上执行一些操作，但不复制到slave库上，可以通过修改参数sql_log_bin来实现。

比如想在主库上修改某个表的定义，但是在slave库上不做修改：

master mysql> set sql_log_bin=0;#设为0后，在Master数据库上执行的语句都不记录binlog

master mysql> alter table test1 drop index idx_id add index (id,username);

master mysql> set sql_log_bin=1;

 

 

6. 分析海量数据

show profiles --默认关闭，会记录所有查询语句

show variables like '%profiling%'

set profiling=on ;

show query for 上面查询出来的query id

show profile all(或是cpu block io) for query 上面查询出来的query id

 

redolog和binglog

存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为的记录持久到硬盘上的事务日志中，而不用每次都将数据本身持久到磁盘。事务日志采用追加的方式，因此写日志操作是操作磁盘内顺序I/O，会非常快，事务持久以后，被修改的数据可在后台慢慢的刷回到磁盘。这种方式称为write-ahead-logging，修改数据需要写两次磁盘。如果修改的数据已记录到事务日志并持久化，但还还没有写回磁盘，此时发生系统崩溃，在重启时会自动恢复这部分修改的数据。

修改事务隔离级别：set session transaction isolation level read commited;

 

innodb的redolog和binlog只记录了数据页的改变部分，并未记录数据页完整的镜像。

用系统表空间中一段连接磁盘空间（100个数据页，2M）作为doubble write buffer。当进行刷脏页时，首先将脏页（内存中的数据）的副本写到系统表空间的doubble write buffer(即redo log 在磁盘中的顺序存储空间)，然后调用fsync()刷新操作系统的I/O缓存。确保副本被真正写磁盘（即redo log 在磁盘中的顺序存储空间），最后innodb后台IO线程将脏页（内存中的数据）刷新到磁盘数据文件。(这里需要参考redolog和binlog的两阶段提交过程及相关参数设置，并不一定是脏页，可能是redolog磁盘数据，即double write buffer)。

 

1、正常运行中的实例，数据写入后的最终更新磁盘，是把内存中的数据页写盘。这个过程与redolog毫无关系。

2、在做崩溃恢复时，如果发现不一致的页,即一个数据页可能在崩溃恢时丢失了更新，将磁盘数据读到内存，innodb会用系统表空间的doublewrite buffer区相应的副本来恢复读入内存的数据页（即将redolog file 中的变更应用到内存，然后如果内存数据页和磁盘的数据不一致，即将可能发生刷脏页

3、默认double write是开启的，对于要求超高性能。可以设置innodb_doublewrte=0，来满足性能。

 

redolog：重做日志，innodb引擎特有的。固定大小，一组4个文件，每个1GB，物理日志，可循环写，保证innodb具有crash safe 能力。redolog file存在磁盘中一小块有序存储空间。redo log 只是用来作崩溃恢复用的，即将redo log中的变更数据应用到相应的内存数据页中。

 

redolog在prepare阶段是指已将日志写到redolog buffer，即mysql的进程的内存中，不是每次生成后都要写到redo log 的磁盘顺序空间，因为这时还没有到commit步骤（不是sql语句的commit,这个），即使crash了，日志丢了也没关系；另外在还没有到commit步骤(不是sql语句的commit)时，redo log buffer中的数据也是有可能写到redo log file中的，即磁盘顺序空间。

redolog三种状态：

a:在内存中，即redo log buffer中

b:物理上是在page cache中，即磁盘顺序写空间，但没有持久化fsync

c:持久化到磁盘，对应的是hard disk

写redo log buffer 是很快的要，write到page cache（page files）也差不多，但持化到磁盘速度慢多了。

innodb_flush_log_at_trx_commit:控制redo log写入策略。控制redo log buffer（在内存中）更新记录写入到日志文件（一小块顺序磁盘存储空间）以及将日志文件数据（内存中数据页即脏页）刷到磁盘数据文件的时机。

0：表示在事务提交时，不会立即触发将缓存日志（内存中的redolog数据）写到磁盘（顺序I/O）文件，而是每秒触发一次缓存日志（内存中的redolog数据）写到顺序存储空间，并调用操作系统fsync刷（I/O）缓存（内存中的数据脏页）到磁盘数据文件中。只是将redolog 留在redo log buffer中。

 

1：表示立即写到磁盘（page cache顺序I/O），并调用fsync刷（I/O）缓存（内存中的数据脏页）到磁盘数据文件。每次事务提交时都将redlo log直接持久化到磁盘 page cache(顺序写空间)。

 

2：表示立即写到磁盘（page cache顺序I/O），但并不马上调用fsync来刷I/O缓存（内存中的数据脏页），而是每秒只做一次磁盘I/O缓存（内存中的数据脏页）刷新操作。设置为2，如果数据库崩溃，由于日志已写到顺序磁盘空间（page cache顺序I/O），只要不发生操作系统崩溃，重启后不会数据丢失（根据下面的崩溃改恢复逻辑）。每次事务提交时都只是把redolog写到page cache(顺序写空间)

innodb 有一个后台线程，每隔1秒，就会把redolog buffer 中的日志调用write写到文件系统中的page cache，然后调用fsync持久化到磁盘。

 

注意：事务执行中间过程的redolog也是直接写在redo log buffer中的， 这些redo log 也会被后台线程一起持久化到磁盘。也就是说，一个没有提交的事务的redolog也是可能已经持久化到磁盘的。

这有两种情况：

a:redo log buffer占用的空间达到innodb_log_buffer_size的一半时，后台线程主动写盘，即将内存中的redo log直接定到磁盘顺序空间（只write ，没有调用fsync，redolog 留在了page cache中）。

b:并行事务提交时顺带将这个事务的redo log buffer持久化到磁盘（page cache ,只调用了write，没有调用fsync）。

需要说明的是，两阶段提交的时候，时序上redo log 先prepare，再写binlog,最后再把redo log commit,如果innodb_flush_log_at_trx_commit=1，那么redolog在prepre阶段就要持久化一次，因为有一个崩溃恢复逻辑要依赖于prepare的redolog，再加上binlog来恢复的。每秒一次的后台轮询刷盘，再加上崩溃恢复的逻辑，innodb就认为redo log 在commit的时候就不需要fsync了，只会write到文件系统的page cache中就够了。

 

redolog崩溃恢复过程：

a:redolog和binlog都没有提交，即两个文件中都没有commit标识，则在数据库崩溃恢复后，通过redolog直接回滚事务

b:redolog和binlog都提交了，即redolog中有commit标识，binlog在statement格式下有commit标识，在row格式下有xid event，则在数据库崩溃恢复后，通过redolog提交事务

c:redolog提交了，即redolog中有commit标识,则在数据库崩溃恢复后，从redolog提交事务，此时会产生新的binlog(用于复制等异构计算)，不会再次生这个redolog。

d:redolog有prepare，无commit标识，再判断binlog是否存在并完整，若完整 ，则通过redolog提交事务，否则回滚事务

redolog和binlog是怎么关联起来的：

有一个共同的字段xid，崩溃恢复时，按顺序扫描redolog

a:如果碰到既有prepare又有commi的redolog就直接提交

b:如果碰到只有prepare，而没有commit的redolog,就拿关xid去binlog找对应的事务。

binlog没有能力恢复数据页，redolog file在page cache中不是完整的数据页，所以，刷脏页是指将内存中的数据写入磁盘中的数据文件。

 

binglog：归档日志，mysql server层实现的，所有引擎都可以使用。大小不定，逻辑日志，追加写达到一定大小会切换到下一个文件，不会覆盖以前的日志。只是用来作为归档和复制及其他异构计算使用。

binlog写入机制：事务执行过程中，先把日志写到binglog cache中，即内存中，没有到commit步骤（这里不是指sql语句的commit）时，再把binlog cache 写到binlog文件即磁盘的数据文件。一个事务的binlog是不能拆开的，因此不论这事务多大也要确定一次性写入。系统给binlog cache分配了一片内存，每个线程参数binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

 

到了commit（非sql的commit）步骤时，执行器把binlog cache中的完整事务写入到binlog（就是顺序写磁盘page cache）中，并清空binlog cache。每个线程有自己的binlog cache，但共用同一份binlog文件。

write指的就是把日志写入到文件系统的page cache binlog files（就是顺序写磁盘page cache binlog files），并没有持久化到磁盘，所以速度比较快；fsync才是将数据持久到磁盘操作，才占IOPS;

提升binlog组提交效果：设置binlog_group_commit_sync_delay表示延迟多少秒后才调用fsync和binlog_group_commit_sync_no_delay_count表示累积多少次后才调用fsync。两个条件是或关系，只要有一个条件满足就会调用fsync.当binlog_group_commit_sync_delay=0时，binlog_group_commit_sync_no_delay_count也就无效了。组提交机制可以大幅降低磁盘的IOPS消耗。

 

sync_binlog:此参数控制write和fsync的时机。

设置为1时表示每次事务提交binlog都持久化到磁盘，即执行fsync。设置1可以保证mysql异常重启之后的binlog不丢失。0：每次提交事务只write,即写到page cache中，并没有持久化到磁盘，不fsync;sync_binlog=N(N>1),表示每次提交事务都write，即写到page cache中，但累积N个事务后才fsync，即持久化到磁盘。比较常见的是将其设置为100到1000中的某个数值。

innodb_log_file_size:是指redo log的日志文（顺序磁盘空间）件大小；

innodb_log_buffer_size:表示redlo log 在内存中的大小，默认8M,增加此量的大小：可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作，即执行过程还没有commit（两阶段的commit），就将缓存写到了redo log的顺序空间。所以增加此值可以减少日志写磁盘操作。从而提高事务处理性能。

两阶段提交流程：深色在执行器内执行，浅色在innodb引擎中执行

 

 

undolog：innodb每个事务有一个唯一的事务id，叫作transaction id ，在事务开始时向innodb事务系统申请的，申请时按顺序严格递增的，每行数据有多个版，每次事务更新时，都会生成一个新的数据版本，且把transaction id赋值给这个数据版本的事务id,记为row trx_id，同时，旧的数据版本要保留，并且在新的数据版本中能够有信息可直接拿到它。也就是说数据表中一行记录，可能有多个版本（row）,每个版本有自己的 row trx_id。如图所示就是一条记录被多个事务连续更新后的状态：

 

有4个版本，最新是V4。row trx_id和transaction id相同。图中的u1和u2,u3就是undolog。v1、v2、v3并不是物理上真实的存在，每次需要时候根据当前版本和undo log计算出来的。

 

innodb是怎么快速定义快照的。按可重复读定义：一个事务启动的时候能够看到所有已经提交的事务结果，但是之后，在这个事务执行期间，其他事务的更新对它不可见。 innodb为每个事务构造了一个数组，用来保存这个事务启动瞬间当前正在活跃的所有事务ID，活跃是指启动了但还没提交。数组里面事务id最小值记为低水位，当前系统里面已经创建过的事务id的最大值加1记为高水位。这个视频数组和高水位就组成了当前事务的一致性视图。而数据版本的可见性规则，就是基于数据的row trx_id的这个一致性视图的对比结果得到的。这个视图数组把所有的row trx_id分成了几种不同的情况：

 

 

 

row trx_id有三种情况：

a:绿色部分，表示这个版本日已提交或是自己生成的事务，可见

b:红色部分，表示这个版本是由将来的启动的事务生成的，是肯定不可见的。

c:黄色部分，有两种情况c-1:若row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见，c-2:若row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见。

innodb利用了所有数据都有多个版本的这个特必，实现了秒级创建快照的能力

 

show engine innodb status命令查看当前日志的写入情况。

更新数据操作流程：

1、将数据读入innodb buffer pool中，对相关记录加独占锁

2、将undo信息写入到undo表空间的回滚段中

3、更改缓存页中的数据，并将更新记录写入redo log buffer中

4、提交时，根据innodb_flush_log_at_trx_commit，用不同的方式将redo log buffer刷 到redo log file（顺序写），然后释放独占锁

5、IO线程根据需要择机将缓存（内存中的数据页，即脏页）中的更新过的数据刷新到磁盘数据文件中

 

分析日志工具

可以用mysqlsla工具，是mysql statement log analyzer的缩写，可以从http://hackmysql.com/mysqlsla下载

最基本用法:

mysqlsla --log-type slow LOG ； mysqlsla --log-type general LOG;

解析二进制日志

mysqlbinlog LOG| mysqlsla --log-type binary -； 解析微秒日志： mysqlsla --log-type msl LOG