MySQL之六 5.7日志文件

修改英文

localectl  set-locale LANG=en_US.UTF-8

localectl  list-locales

 

Centos 7 编译安装：

　　yum groupinstall "Development Tools" -y   安装包组

　　yum install ncurses-devel  openssl-devel  libevent-devel  jemalloc-devel cmake

 

systemd service unit file

MySQL安装参考： https://www.cnblogs.com/f-ck-need-u/p/7590376.html#2-3-systemd-

一、日志

  • 查询日志(query log)：记录建立的客户端连接和执行的语句
  • 慢查询日志(slow query log) ：记录所有执行时间超过long_query_time秒的所有查询(并不一定是查询语句自己执行速度慢导致的)
  • 错误日志(error log) ：  记录mysql服务的启停时正确和错误的信息，还记录启动、停止、运行过程中的错误信息。
  • 二进制日志(binary log) ：记录所有更改数据的语句，还用于复制，恢复数据库用
  • 中继日志(reley log)：mysql 主从复制架构中用到的日志
  • 事务日志：transaction  log   （支持事务型存储引擎的ACID功能）
  • 更新日志: -log-update （二进制日志已经代替了老的更新日志，更新日志在MySQL 5.1中不再使用）

官方文档：https://dev.mysql.com/doc/

二、日志刷新

mysql> FLUSH LOGS;
shell> mysqladmin flush-logs
shell> mysqladmin refresh

三、查询当前日志记录的状况

mysql>show variables like 'log%';（是否启用了日志）

mysql> show master status;（怎样知道当前的日志）

mysql> show master logs;（显示二进制日志的数目）

在这里插入图片描述

 

四、日志

4.1、一般查询日志(不启用)   了解即可 

查询日志分为一般查询日志和慢查询日志，它们是通过查询是否超出变量 long_query_time 指定时间的值来判定的。在超时时间内完成的查询是一般查询，可以将其记录到一般查询日志中，但是建议关闭这种日志（默认是关闭的），超出时间的查询是慢查询，可以将其记录到慢查询日志中。

a）记录查询操作l两种地方：

      • 文件：file
      • 表：table　　

b）和查询日志有关的变量有：　

long_query_time = 10 # 指定慢查询超时时长，超出此时长的属于慢查询，会记录到慢查询日志中
log_output={TABLE|FILE|NONE}  # 定义一般查询日志和慢查询日志的输出格式，不指定时默认为file

TABLE表示记录日志到表中，FILE表示记录日志到文件中，NONE表示不记录日志。只要这里指定为NONE，即使开启了一般查询日志和慢查询日志，也都不会有任何记录。

 

c）和一般查询日志相关的变量有：

　　mysql> show global variables like 'general%';

general_log=off # 是否启用一般查询日志，为全局变量，必须在global上修改。
sql_log_off=off # 在session级别控制是否启用一般查询日志，默认为off，即启用
general_log_file=/mydata/data/hostname.log  # 默认是库文件路径下主机名加上.log

 

d）查看日志的存放方式：show variables like 'log_output';

如果设置mysql> set global log_output=’table’ 的话，则日志结果会记录到名为gengera_log的表中，这表的默认引擎都是CSV
如果设置表数据到文件set global log_output=file;

设置general log的日志文件路径：
set global general_log_file=’/tmp/general.log’;

开启general log： set global general_log=on;
关闭general log： set global general_log=off;

 

注：在MySQL 5.6以前的版本还有一个"log"变量也是决定是否开启一般查询日志的。在5.6版本开始已经废弃了该选项。

　　默认没有开启一般查询日志，也不建议开启一般查询日志。此处打开该类型的日志，看看是如何记录一般查询日志的。

　　一般查询日志查询的不止是select语句，几乎所有的语句都会记录。


4.2、 慢查询（重点）

mysql记录慢查询日志是在查询执行完毕且已经完全释放锁之后才记录的，因此慢查询日志记录的顺序和执行的SQL查询语句顺序可能会不一致(例如语句1先执行，查询速度慢，语句2后执行，但查询速度快，则语句2先记录)。

注意，MySQL 5.1之后就支持微秒级的慢查询超时时长，对于DBA来说，一个查询运行0.5秒和运行0.05秒是非常不同的，前者可能索引使用错误或者走了表扫描，后者可能索引使用正确。

另外，指定的慢查询超时时长表示的是超出这个时间的才算是慢查询，等于这个时间的不会记录。

 

mysql> SHOW GLOBAL VARIABLES LIKE 'long_query_time';   默认10s

　　　　SET GLOBAL long_query_time=number  #修改默认时长

 

和慢查询有关的变量：

long_query_time=10 # 指定慢查询超时时长(默认10秒)，超出此时长的属于慢查询
log_output={TABLE|FILE|NONE} # 定义一般查询日志和慢查询日志的输出格式，默认为file
slow_query_log={1|ON|0|OFF}  # 也是是否启用慢查询日志，默认不启用
slow_query_log_file=/mydata/data/hostname-slow.log  #默认路径为库文件目录下主机名加上-slow.log
log_queries_not_using_indexes=OFF # 查询没有使用索引的时候是否也记入慢查询日志

 

a） 启用慢查询日志

 mysql> set @@global.slow_query_log=on;     （注：@@是服务器全局变量，@是用户级别变量）

b）查看慢查询配置情况：

　　mysql> show global status like '%slow%';

c） 查看慢查询日志路径：

 mysql> show variables like '%slow%';

 

 

4.3、错误日志

错误日志是最重要的日志之一，它记录了MariaDB/MySQL服务启动和停止正确和错误的信息，还记录了mysqld实例运行过程中发生的错误事件信息。

  • mysqld 启动和关闭过程中输出的事件信息
  • mysqld运行中产生的错误信息
  • event  scheduler运行一个event时产生的日志信息
  • 在主从复制架构中的从服务器上启动从服务器线程时产生的信息

log_warnings=1|0 : 是否记录警告信息至错误日志文件中

mysql> show variables like '%log_err%';
错误日志归档，备份错误日志
shell>mv host_name.err host_name.err-old
shell> mysqladmin -u root -p flush-logs
ahell>mv host_name.err-old back-directory

 

4.4、二进制日志 （重点）

　　二进制日志包含了引起或可能引起数据库改变(如delete语句但没有匹配行)的事件信息，但绝不会包括select和show这样的查询语句。语句以"事件"的形式保存，所以包含了时间、事件开始和结束位置等信息。

　　二进制日志是以事件形式记录的，不是事务日志(但可能是基于事务来记录二进制日志)，不代表它只记录innodb日志，myisam表也一样有二进制日志。

　　对于事务表的操作，二进制日志只在事务提交的时候一次性写入(基于事务的innodb二进制日志)，提交前的每个二进制日志记录都先cache，提交时写入。对于非事务表的操作，每次执行完语句就直接写入。

　　MariaDB/MySQL默认没有启动二进制日志，要启用二进制日志使用 --log-bin=[on|off|file_name] 选项指定，如果没有给定file_name，则默认为datadir下的主机名加"-bin"，并在后面跟上一串数字表示日志序列号，如果给定的日志文件中包含了后缀(logname.suffix)将忽略后缀部分。

 

　　当重启mysql服务或刷新日志或者达到日志最大值时，将滚动二进制日志文件，滚动日志时只修改日志文件名的数字序列部分。

　　二进制日志文件的最大值通过变量 max_binlog_size 设置(默认值为1G)。但由于二进制日志可能是基于事务来记录的(如innodb表类型)，而事务是绝对不可能也不应该跨文件记录的，如果正好二进制日志文件达到了最大值但事务还没有提交则不会滚动日志，而是继续增大日志，所以 max_binlog_size 指定的值和实际的二进制日志大小不一定相等。

 

　　因为二进制日志文件增长迅速，但官方说明因此而损耗的性能小于1%，且二进制目的是为了恢复定点数据库和主从复制，所以出于安全和功能考虑，极不建议将二进制日志和datadir放在同一磁盘上。

 

a） 开启二进制日志首先需添加如下到配置文件中

注意：对于mysql 5.7，直接启动binlog可能会导致mysql服务启动失败，这时需要在配置文件中的mysqld为mysql实例分配server_id。

[root@localhost ~]# vi /etc/my.cnf

[mysqld]

server_id=1234           #文件名随便去

log_bin=mysql-bin      #等号两边不能有空格

[root@localhost mysql5.7]# systemctl restart mysqld.service

 

b）查看二进制日志

1.使用mysqlbinlog工具。

2.使用show显示对应的信息。

SHOW {BINARY | MASTER} LOGS      　　　　　　　　  # 查看使用了哪些日志文件列表
SHOW BINLOG EVENTS [IN 'log_name'] [FROM pos]    # 查看日志中进行了哪些操作
SHOW MASTER STATUS         　　　　　　　　　　　　　# 显式正在使用中的二进制日志信息

mysql> SHOW BINLOG EVENTS IN 'mysql-bin.000001';
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+
| mysql-bin.000001 | 4 | Format_desc | 1234 | 123 | Server ver: 5.7.24-log, Binlog ver: 4 |
| mysql-bin.000001 | 123 | Previous_gtids | 1234 | 154 | |
+------------------+-----+----------------+-----------+-------------+---------------------------------------+

Pos ：当前这个事件在日志文件中的位置  （起始位置）

Server_id ：代表在哪一个服务器上运行的

End_log_pos ：当前事件结束以后的下一个位置

 

c）二进制日志记录格式

  • 　　基于“语句”记录：statement
  • 　　基于“行”记录：row 
  • 　　混合模式：mixed ，让系统自行判定该基于哪种方式进行

d） 二进制日志文件的构成

  • 　　日志文件：mysql-bin.文件名后缀 ，二进制格式
  • 　　索引文件：mysql-bin.index  ，文本格式

e）服务变量

查看是否启用二进制日志：
　　show variables like '%log_bin%';

查看所有的二进制参数
　　show variables like '%binlog%';

查看文件的位置
　　show variables like '%datadir%';

注意：在配置binlog相关变量的时候，相关变量名总是搞混，

因为有的是binlog，有的是log_bin，当他们分开的时候，log在前，当它们一起的时候，bin在前。在配置文件中也同样如此。

  • log_bin = {on | off | base_name} #指定是否启用记录二进制日志或者指定一个日志路径(路径不能加.否则.后的被忽略)
  • sql_log_bin ={ on | off } #指定是否启用记录二进制日志，只有在log_bin开启的时候才有效（5.7以下才有）
  • expire_logs_days = #指定自动删除二进制日志的时间，即日志过期时间  （0: 不启用此功能）
  • binlog_do_db = #明确指定要记录日志的数据库
  • binlog_ignore_db = #指定不记录二进制日志的数据库   
  • log_bin_index = #指定mysql-bin.index文件的路径
  • binlog_format = { mixed | row | statement } #指定二进制日志基于什么模式记录
  • binlog_rows_query_log_events = { 1|0 } # MySQL5.6.2添加了该变量，当binlog format为row时，默认不会记录row对应的SQL语句，设置为1或其他true布尔值时会记录，但需要使用mysqlbinlog -v查看，这些语句是被注释的，恢复时不会被执行。
  • max_binlog_size =1073741824（1G）#指定单个二进制日志文件最大值，超出指定值将自动滚动。但由于事务不会跨文件，所以并不一定总是精确。
    • 　注：到达最大值会自动滚动；文件达到上限时的大小未必为指定的精确值
  • binlog_cache_size = 32768 #基于事务类型的日志会先记录在缓冲区，当达到该缓冲大小时这些日志会写入磁盘
  • max_binlog_cache_size = #指定二进制日志缓存最大大小，硬限制。默认4G，够大了，建议不要改
  • binlog_cache_use：使用缓存写二进制日志的次数(这是一个实时变化的统计值)
  • binlog_cache_disk_use:使用临时文件写二进制日志的次数，当日志超过了binlog_cache_size的时候会使用临时文件写日志，如果该变量值不为0，则考虑增大binlog_cache_size的值
  • binlog_stmt_cache_size = 32768 #一般等同于且决定binlog_cache_size大小，所以修改缓存大小时只需修改这个而不用修改binlog_cache_size
  • binlog_stmt_cache_use：使用缓存写二进制日志的次数
  • binlog_stmt_cache_disk_use: 使用临时文件写二进制日志的次数，当日志超过了binlog_cache_size的时候会使用临时文件写日志，如果该变量值不为0，则考虑增大binlog_cache_size的值
  • sync_binlog = { 0 | n } #这个参数直接影响mysql的性能和完整性（重点）
    • sync_binlog=0:不同步，日志何时刷到磁盘由FileSystem决定，这个性能最好。
    • sync_binlog=n:每写n次二进制日志事件(不是事务)，MySQL将执行一次磁盘同步指令fdatasync()将缓存日志刷新到磁盘日志文件中。Mysql中默认的设置是sync_binlog=0，即不同步，这时性能最好，但风险最大。一旦系统奔溃，缓存中的日志都会丢失。

　　在innodb的主从复制结构中，如果启用了二进制日志(几乎都会启用)，要保证事务的一致性和持久性的时候，必须将sync_binlog的值设置为1，因为每次事务提交都会写入二进制日志，设置为1就保证了每次事务提交时二进制日志都会写入到磁盘中，从而立即被从服务器复制过去。

 

 

4.5、mysqlbinlog  （客户端CLI）

二进制日志可以使用mysqlbinlog命令查看。

　　mysqlbinlog [option] log-file1 log-file2...

-d,--database=name：只查看指定数据库的日志操作

-o,--offset=#：忽略掉日志中的前n个操作命令

-r,--result-file=name：将输出的日志信息输出到指定的文件中，使用重定向也一样可以。

-s,--short-form：显示简单格式的日志，只记录一些普通的语句，会省略掉一些额外的信息如位置信息和时间信息以及基于行的日志。可以用来调试，生产环境千万不可使用

--set-charset=char_name：在输出日志信息到文件中时，在文件第一行加上set names char_name
--start-datetime,--stop-datetime：指定输出开始时间和结束时间内的所有日志信息
--start-position=#,--stop-position=#：指定输出开始位置和结束位置内的所有日志信息
-v,-vv：显示更详细信息，基于row的日志默认不会显示出来，此时使用-v或-vv可以查看

 

4.6、二进制日志事件的格式：
# at 219
#200705 19:43:36 server id 1234 end_log_pos 337 CRC32 0x4626bea0 Query    thread_id=2    exec_time=0    error_code=0
use `testdb`/*!*/;
SET TIMESTAMP=1593949416/*!*/;
create table tb2 (id int, name char(30))
/*!*/;


事件发生的日期和事件 ：200705 19:43:36

事件发生的服务器标识：server  id  1234

事件结束位置：end_log_pos 337

事件的类型：Query

事件发生时所在服务器执行此事的线程的ID：thread_id=2
语句的时间戳与将其写入二进制文件中的事件差：exec_time=0 
错误代码：error_code=0
事件内容：后三行
 
GTID ：Global  Transaction ID  全局事务ID号
　　　　专属属性：GTID
　　

 

5、中继日志

　　复制架构中，从服务器用于保存从主服务器的二进制日志中读取到的事件

6、事务日志 ：transaction log

　　事务型存储引擎自行管理和使用

　　 redo  log    重做日志

　　 undo  log   撤销日志

https://www.cnblogs.com/f-ck-need-u/p/9001061.html#auto_id_6

posted @ 2020-07-11 10:25  幻落之瞳  阅读(1495)  评论(0)  编辑  收藏  举报