xiaocainiaoya's blog

日常积累

0%

mysql日志文件

发表于 更新于 分类于

mysql日志文件

​ mysql在进行数据的存储过程中，为了处理数据、预防数据丢失、查询数据优化等的场景的需要，约定了一些日志文件系统。

错误日志

​ 错误日志error_log文件对MYSQL的启动、允许、关闭过程进行记录，在遇到问题时可以查看该文件进行问题的定位。

1
show variables like 'log_error'

慢查询日志

​ 慢查询日志slow log文件，可以在MYSQL启动时设置一个阈值，将执行时间超过该值的SQL语句都记录到慢查询文件中，通过查看该文件中的SQL语句来对系统中的慢查询进行优化处理。

默认情况下，MYSQL不开启慢查询日志：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- 开启慢查询日志
set global slow_query_log='ON'
-- 查看慢查询的时间，多久认为是慢SQL 大于long_query_time才会认为是慢查询
-- 5.1版本之后long_query_time的单位改为微秒，5.1之前是秒
show variables like 'long_query_time' 

show variables like 'log_slow_queries'

-- 如果与语句没有使用到索引，都认为是慢查询
show variables like 'log_queries_not_using_indexes'

-- 5.6.5 版本之后才有的参数，用来表示每分钟允许记录的未使用索引的SQL次数
-- 默认值为0，表示没有限制；在生产环境下，若过多的SQL语句没有使用索引，会导致
-- 频繁的将SQL记录到slow log表中，消耗性能和占用资源
show variables like 'log_throttle_queries_not_using_indexes'

-- 5.1 之后允许将慢查询SQL记录到TABLE中，便于开发者查询

-- 查看记录的方式，默认是FILE
show variables like 'log_output'
-- 修改为TABLE
set global log_output = 'TABLE'
-- 通过MYSQL.slow_log 查看
SELECT * FROM MYSQL.SLOW_LOG

默认情况下slow_log表使用的是CSV引擎，如果数据量的情况下，查询效率可能不高，可以将slow_log表的引擎修改为MyISAM,并且对列start_time上添加索引来提交查询的效率。

二进制日志

二进制日志文件bin log记录对数据执行的所有操作，但不包含select等查询操作，因为本质上查询并不会对数据本身进行修改。

但非查询语句又对数据没有产生变化，这类还是会被记录。

1
2
-- 比如：数据库中并没有名为marry的数据
update user_info set username = 'tom' where username = 'marry'

bin log的主要作用为：

  • 恢复：比如对某些数据进行DELETE操作，可以通过使用mysqlbinlog工具对bin log文件中的的某些事件的起止节点或者时间的起止进行数据的恢复。
  • 复制：常用的场景为主从模式下，主节点通过将bin log文件传递给从节点，然后从节点通过读取bin log文件的数据保证主从数据一致性。
  • 审计：通过bin log文件中的信息进行审计，判断是否有对数据库注入的攻击。

默认情况下二进制文件binlog是关闭的，需要手动开启，逻辑上是存在一定的性能消耗，但是根据官方测试结果数据来看，开启仅消耗使得性能下降1%左右。

1
2
3
4
5
6
7
8
9
10
11
-- 查看binlog存放位置
show variables like 'datadir'

-- 显示缓存区大小
show variables like 'binlog_cache_size'

-- 显示使用了临时文件写二进制日志的次数
show global status like 'Binlog_cache_disk_use'

-- 显示使用缓存区写二进制日志的次数
show global status like 'Binlog_cache_use'

配置文件中相应的配置参数

  • max_binlog_size： 指定单个二进制文件的最大值。如果操作该值则产生新的二进制文件，MYSQL5.0之前默认值为1.1G，之后版本为1G
  • binlog_cache_size：在事务未提交的二进制日志会被记录到一个缓存区中，等到事务提交之后再写入到binlog中，缓存区默认大小为32K。且该参数是基于会话，也就是说当一个线程开启一个事务时，就会分配一个binlog_cache_size大小的缓存区。当事务中的记录容量超过该值时，MySql会将缓冲区中的日志写入到一个临时文件中。
  • sync_binlog：默认情况下，并不是每次写的时候都将日志内容写入到磁盘中，所以当数据库发生宕机时，可能有部分缓存区的数据还未写入到磁盘中。sync_binlog这个参数就是用来控制写入到缓存多少次后将缓存中的日志数据同步到磁盘中。默认值为0
  • binlog_do-db：表示需要写入那些库的日志
  • binlog-ignore-db：表示需要忽略那些库的日志

binlog_format：这个参数比较重要，单独罗列出来，表示记录到binlog日志的数据格式，在MySql5.1版本之后才有该参数。可选参数有：

  • STATEMENT：基于SQL语句记录。存在两个问题：

    • 如果主服务器使用了一些生成函数，比如uuid等，这时从服务器得到主服务器的binlog进行回放后会导致主从数据不一致。

    • 由于MySql的INnoDB存储引擎的默认事务隔离级别是RR(REPEATABLE READ)可重复读。如果事务隔离级别不是RR,会出现一种情况是：

      假设事务隔离级别为RC且有一个user_info（id， name）表和两个字段，且有两条数据(1, 'tom'), (2, 'marry')，下表的两个事务执行之后的数据为(3, 'tom'), (2, 'tom')，但是从服务器获取到的binlog中，由于事务B先提交，那么会先回放事务B的SQL语句，结果就变成了(3, 'tom'), (3, 'marry'),从而导致主从数据不一致。所以有些人说MySQL为了避免这种情况，将事务的隔离级别默认设置为RR，但Oracle的默认事务隔离级别为RC

      顺序 事务A 事务B
      1 update T set id=3 where name=’tom’
      2 Update T set name=’tom’ where name=’marry’
      3 commit
      4 commit

  • ROW：不是简单的SQL语句，而是记录表所对应行数据的变化，也就是从某某修改为某某。假设name=tom的数据有1w条，那么采用STATEMENT仅记录一条DELETE语句，采用ROW则记录1w条日志记录，显然后者需要更大的磁盘空间。

  • MIXED：混合模式，默认采用STATEMENT格式，指定一些情况使用ROW格式，比如使用了uuid()等生成函数，在innodb对表的数据进行增删改操作等等。

binlog的写入机制

  1. binlog的写入是利用事件触发执行机制，根据binlog_format格式和操作的类型触发Log Event事件。
  2. 将事务在执行过程中产生的所有Log Event写入到缓存区中，一个执行语句不一定都只对应一个Log Event，每个事务线程都有独立的缓存区。
  3. 事务提交后会将缓存区中的Log Event写入到bin log文件中。事务的写入是串行的方式，也就是说一个事务在写入的中间不会穿插其他事务的Log Event。

重做日志

重做日志redo log

回滚日志

​

-------------本文结束感谢您的阅读-------------