mysql 二进制日志原理_MySQL的日志原理

1、mysql日志概念

概念：日志文件(log)就是一个跟踪记录的列表，它可以协助我们时刻掌握系统及应用服务的动作状态，在故障排查的时候提供最详细准确地信息，帮助我们快速查找原因，减少我们凭主观的经验去猜测，这样的答案更具有说服力，机器通常是不会撒谎的。

1.1、如何开启mysql日志

1.1.1、确认日志是否启用

show globalvariables like '%log_bin%';

如果启用了，即ON,日志文件就在MySQL的安装目录的data目录下，可以看出上面没有开启。

Linux下修改步骤：

注意：利用rpm 安装的mysql找不到my.cnf,可以在/usr/share/mysql/目录下把my-default.cnf复制到/etc/下，然后对my.cnf进行修改。命令如下

cp /usr/share/mysql/my-default.cnf /etc/my.cnf

(修改步骤省略)

windows下修改步骤：

在mysql的安装目录下，打开my.ini,在后面加上上面的参数，保存后重启mysql服务就行了。

例如：#Enter aname for the binary log. Otherwise a default name will be used.

#log-bin=#Enter a name for the query log file.Otherwise a default name will be used.

#log=#Enter a name for the error log file. Otherwise adefault name will be used.

log-error=#Enter a name for the update log file.Otherwise a default name will be used.

#log-update=

上面只开启了错误日志，要开其他的日志就把前面的“#”去掉

再次查看：

1.1.2、查看当前的日志

Show master status

1.1.3、查看二进制日志文件

测试准备，创建一个数据库demo，在demo下创建一个表tb1，插入一个条数据。

然后日志看有没有记录。

查看方式：

方法一、通过命令show binlog events in'mysql-bin.000001';可以查看刚才做的一些操作。

方法二、可以在命令行下查看、利用MySQLbinlog，必须在数据目录下

命令如下：

mysqlbinlog mysql-bin.000001

1.1.4、导出此数据库的信息

1.1.5、导入此数据库的信息

命令：

2、MySQL数据库日志分类

通过命令可以查看所有日志：show variables like 'log_%';

MySQL数据库分为6种日志——错误日志、二进制日志、查询日志、慢查询日志和事务日志、中继日志。

前4种日志文件默认情况下都存放在$MYSQL_HOME/data目录下面，我们也可以使用服务器启动选项来对日志存放的位置以及名称来进行自定义。

2.1、二进制日志

2.1.1、概念

也叫作变更日志，主要用于记录修改数据或有可能引起数据改变的mysql语句，并且记录了语句发生时间、执行时长、操作的数据等等(记录所有更改数据的语句)。

用途：

通过二进制日志可以查询mysql数据库中进行了哪些变化。

还用于复制。

文件名：

mysql-bin.*，在my.cnf中配置。数据库的主从依赖此文件。mysql-bin 记录了mysql的DDL和DML操作(不含select)，但文件是二进制格式，必需用mysqlbinlog命令查看。

二进制开启状态：log_bin为ON开启状态

2.1.2、二进制日志相关的参数

mysql> showglobal variables like "%log%";

sql_log_bin ={ON|OFF}   #用于控制会话级别二进制日志功能的开启或关闭。默认为ON，表示启用记录功能。用户可以在会话级别修改此变量的值，但其必须具有SUPER权限。

binlog_cache_size =32768 #默认值32768Binlog Cache用于在打开了二进制日志(binlog)记录功能的环境，是MySQL用来提高binlog的记录效率而设计的一个用于短时间内临时缓存binlog数据的内存区域。一般来说，如果我们的数据库中没有什么大事务，写入也不是特别频繁，2MB～4MB是一个合适的选择。但是如果我们的数据库大事务较多，写入量比较大，可与适当调高binlog_cache_size。同时，我们可以通过binlog_cache_use 以及binlog_cache_disk_use来分析设置的binlog_cache_size是否足够，是否有大量的binlog_cache由于内存大小不够而使用临时文件(binlog_cache_disk_use)来缓存了。

binlog_stmt_cache_size= 32768    #当非事务语句使用二进制日志缓存，但是超出binlog_stmt_cache_size时，使用一个临时文件来存放这些语句。

log_bin = mysql-bin#指定binlog的位置，默认在数据目录下。

binlog-format= {ROW|STATEMENT|MIXED}    #指定二进制日志的类型，默认为MIXED。如果设定了二进制日志的格式，却没有启用二进制日志，则MySQL启动时会产生警告日志信息并记录于错误日志中。

sync_binlog = 10#设定多久同步一次二进制日志至磁盘文件中，0表示不同步，任何正数值都表示对二进制每多少次写操作之后同步一次。当autocommit的值为1时，每条语句的执行都会引起二进制日志同步，否则，每个事务的提交会引起二进制日志同步

max_binlog_cache_size= {4096 ..18446744073709547520}      #二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存，其上限由max_binlog_stmt_cache_size决定。

max_binlog_stmt_cache_size={4096 .. 18446744073709547520}    #二进定日志缓存空间大小，5.5.9及以后的版本仅应用于事务缓存

expire_log_days ={0..99}  #设定二进制日志的过期天数，超出此天数的二进制日志文件将被自动删除。默认为0，表示不启用过期自动删除功能。如果启用此功能，自动删除工作通常发生在MySQL启动时或FLUSH日志时。

2.1.3、查看二进制日志

mysql>show binary logs;     #显示当前服务器使用的二进制文件及大小

mysql>show master logs;      #显示主服务器使用的二进制文件及大小

mysql>show master status;   #当前使用的二进制文件及所处位置

2.1.4、查看二进制日志信息的命令

见1.1.3查看二进制日志文件

2.1.5、删除日志

RESET MASTER;

删除所有binlog日志，新日志编号从头开始

PURGE MASTER LOGS TO 'mysql-bin.010';

删除mysql-bin.010之前所有日志

PURGE MASTER LOGS BEFORE '2003-04-02 22:46:26'；

删除2003-04-02 22:46:26之前产生的所有日志

相关选项：

1. --binlog-do-db=db_name

告诉主服务器，如果当前的数据库(即USE选定的数据库)是db_name，应将更新记录到二进制日志中。其它所有没有明显指定的数据库被忽略

2. --binlog-ignore-db=db_name

告诉主服务器，如果当前的数据库(即USE选定的数据库)是db_name，不应将更新保存到二进制日志中要想记录或忽视多个数据库，使用多个选项，为每个数据库指定相应的选项。

3. -innodb-safe-binlog

使用此选项和sync_binlog＝N(每写N次日志同步磁盘)全局变量将使得事务能够记录的更加安全

4.具有SUPER权限的客户端可以通过SET SQL_LOG_BIN=0语句禁止将自己的语句记入二进制记录

2.1.6、临时关闭二进制日志功能

mysql> set sql_log_bin=0;

Query OK, 0 rows affected (0.00 sec)

应用场景：还原数据库 可临时关闭二进制日志

2.2、错误日志

2.2.1、概念

记录内容：包含了当mysqld启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息

2.2.2、文件位置和格式

可以用--log-error[=file_name]选项来指定mysqld保存错误日志文件的位置。如果没有给定file_name值，mysqld使用错误日志名host_name.err 并在数据目录中写入日志文件

文件名：*.err，记录启动/关闭、运行时严重错误信息。

2.2.3、查看mysql错误日志

在linux下可以利用VI打开错误日志。

Windows下可以直接打开错误日志。

2.3、查询日志

2.3.1、概念

记录客户端所有的sql，包含select，对mysql有一定性能影响，不建议开启。由于查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响mysql的性能的。如若不是为了调试数据库的目的建议不要开启查询日志。

2.3.2、文件位置和格式

用--log[=file_name]或-l [file_name]选项启动它。如果没有给定file_name的值，默认名是host_name.log。

2.4、慢查询日志

2.4.1、概念

慢查询日志是用来记录执行时间超过指定时间的查询语句。通过慢查询日志，可以查找出哪些查询语句的执行效率很低，以便进行优化。一般建议开启，它对服务器性能的影响微乎其微，但是可以记录mysql服务器上执行了很长时间的查询语句。可以帮助我们定位性能问题的。记录了低于my.cnf配置中，long_query_time的sql. 文件名：*-slow.log。

2.4.2、文件位置和格式

用--log-slow-queries[=file_name]选项启动它。如果没有给出file_name值，默认为主机名，后缀为-slow.log。如果给出了文件名，但不是绝对路径名，文件则写入数据目录。

2.4.3、启动、设置和查看慢查询日志

第一步：

Linux下修改配置文件

(网上解决方法，但是我的不知道什么情况，改了起不来)在my.cnf中加上下面两句话

log-slow-queries = /var/lib/mysql/mysql_slow_query.log

long_query_time=5

第一句使用来定义慢查询日志的路径

第二句使用来定义查过多少秒的查询算是慢查询，我这里定义的是5秒

Windows下修改配置文件

在my.ini中加上下面两句话

log-slow-queries = D:\wamp\mysql_slow_query.log

long_query_time=5

第一句使用来定义慢查询日志的路径(因为是windows，所以不牵涉权限问题)

第二句使用来定义查过多少秒的查询算是慢查询，我这里定义的是5秒

我用的方法：在my.cnf中加上添加以下语句：

第二步：查看关于慢查询的状态

执行如下SQL语句来查看mysql慢查询的状态

show variables like '%slow%';

执行结果会把是否开启慢查询、慢查询的秒数、慢查询日志等信息打印在屏幕上。

第三步：执行一次慢查询操作

其实想要执行一次有实际意义的慢查询比较困难，因为在自己测试的时候，就算查询有20万条数据的海量表，也只需要0.几秒。我们可以通过如下语句代替：

SELECT SLEEP(10);

第四步：查看慢查询的数量

通过如下sql语句，来查看一共执行过几次慢查询：

show global status like '%slow%';

第五步：查看慢查询日志

Linux上使用vi /var/lib/mysql/slowquery.log 可以查看到

windows可以找到相应位置的目录的文件打开

2.5、事务日志

2.5.1、概念

事务日志(InnoDB特有的日志)可以帮助提高事务的效率(优点)。

2.5.2、为什么提高效率的原因？

原因一、使用事务日志，存储引擎在修改表的数据时只需要修改其内存拷贝，再把修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。(过程：修改内存---修改事务日志，不用每次都修改磁盘)

原因二、事务日志采用追加的方式，因此写日志的操作是磁盘上一小块区域内的顺序I/O，而不像随机I/O需要在磁盘的多个地方移动磁头，所以采用事务日志的方式相对来说要快得多。(追加的方式，写日志顺序I/O不是随机I/O)

原因三、事务日志持久以后，内存中被修改的数据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的，我们通常称之为预写式日志，修改数据需要写两次磁盘。(预写式日志：等空闭的时候刷回到磁盘)

如果数据的修改已经记录到事务日志并持久化，但数据本身还没有写回磁盘，此时系统崩溃，存储引擎在重启时能够自动恢复这部分修改的数据。具有的恢复方式则视存储引擎而定。

2.5.3、查看事务日志的定义

show global variableslike '%log%';

2.5.4、相关参数介绍

innodb_flush_log_at_trx_commit在事务提交时innodb是否同步日志从缓冲到文件中1表示事务以提交就同步不提交每隔一秒同步一次，性能会很差造成大量的磁盘I/O；定义为2表示只有在事务提交时才会同步但是可能会丢失整个事务。

innodb_log_files_in_group至少有两个             innodb_log_group_home_dir  定义innodb事务日志组的位置       |

innodb_mirrored_log_groups表示对日志组做镜像

2.6、中继日志

2.6.1、概念

从服务器I/O线程读取主服务器Binlog Dump线程发送的内容并将该数据拷贝到从服务器数据目录中的本地文件中.

3、问题总结

3.1、查询日志与慢查询日志区分

查询日志，记录客户端所有的sql，包含select。

慢查询日志是用来记录执行时间超过指定时间的查询语句。

3.2、怎样通过日志分析用户操作

可以通过查看二进制日志查看。