mysq日志

wuchangjian2021-11-16 00:59:00编程学习

MySQL日志记录了MySQL数据库日常操作和错误信息。MySQL有不同类型的日志文件(各自存储了不同类型的 日志),从日志当中可以查询到MySQL数据库的运行情况、用户的操作、错误的信息等。 

MySQL日志分为4大类 

1. 错误日志:记录mysql服务的启动,运行或停止mysql服务时出现的问题

2. 查询日志:记录建立的客户端的连接和执行的语句

3. 二进制日志:记录所有更改数据的语句,可以用于数据的复制

4. 慢查询日志:记录所有执行的时间超过long_query_time的所有查询或不使用索引的查询 

在mysql数据库中,错误日志功能是默认开启的。默认情况下,错误日志存储在mysql数据库的数据目录中。错 误日志文件通常的名称为hostname.err。其中,hostname表示服务器主机名。 错误日志信息可以自己进行配置 的,错误日志所记录的信息是可以通过log-error和log-warnings来定义的,其中log-error是定义是否启用错误 日志的功能和错误日志的存储位置,log-warnings是定义是否将警告信息也定义至错误日志中。默认情况下错 误日志大概记录以下几个方面的信息:服务器启动和关闭过程中的信息(未必是错误信息,如mysql如何启动 InnoDB的表空间文件的、如何初始化自己的存储引擎的等等)、服务器运行过程中的错误信息、事件调度器运 行一个事件时产生的信息、在从服务器上启动服务器进程时产生的信息 注1:MySQL有很多系统变量可以设 置,系统变量设置不同,会导致系统运行状态的不同。因此mysql提供两组命令,分别查看系统设置和运行状态。

//查看系统的设置
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
show global variables like 'autocommit'; //查看全局变量:自动提交
show session variables like 'autocommit'; //查看当前临时变量:自动提交

 查看mysql的版本

status        select version();        show global variables like 'version';

//查看运行状态
SHOW [GLOBAL | SESSION] STATUS [like_or_where]

show status like 'uptime';//查看主机运行时间

这个是登录就开始计数,每次重启重新开始。

一般而言,日志级别的定义没有会话变量都只是在全局级别下进行定义 错误日志的状态:

 更改错误日志位置可以使用log-error来设置形式如下
vim /etc/my.cnf
log_bin=/var/log/mysqld.log
server_id=1    5.7以后得加这

 每次改完主配都得重新启动 systemctl restart mysqld

 查看mysql错误日志:
tail -f /var/log/mysqld.log

为了方便维护需要,有时候会希望将错误日志中的内容做备份并重新开始记录,这时候就可以利用MySQL 的 FLUSH LOGS 命令来告诉MySQL 备份旧日志文件并生成新的日志文件。备份文件名以“.old”结尾。 删除错误日 志: 在mysql5.5.7之前:数据库管理员可以删除很长时间之前的错误日志,以保证mysql服务器上的硬盘空间。 mysql数据库中,可以使用mysqladmin命令开启新的错误日志。mysqladmin命令的语法如下:mysqladmin –u root –p flush-logs也可以登录mysql数据库中使用FLUSH LOGS语句来开启新的错误日志。 在mysql5.5.7之后: 服务器将关闭此项功能。只能使用重命名原来的错误日志文件,手动冲洗日志创建一个新的:方式如下:

二进制日志:
 

主要记录MySQL数据库的变化,二进制日志以一种有效的格式,并且是事务安全的方式包含更新日志中可用的 信息。二进制日志包含了所有更新了数据或者已经潜在更新了数据。二进制日志还包含关于每个更新数据库的 语句的执行时间,它不包含没有修改任何数据的语句。使用二进制日志的主要目的是大可能地恢复数据库。
启动二进制日志,默认情况下二进制日志是关闭的 编辑配置文件My.ini 或my.cnf

 

Show variables 或 show variables like 'log_%'; 语句来查询日志设置

 【查看二进制日志】 MySQL二进制日志存储了所有的变更信息,MySQL二进制日志经常使用。当MySQL创建 二进制日志文件时,首先创建一个以’filename’为名称,以’.index’为后缀的文件;在创建一个以’filename’为名 称,以’.000001’为后缀的文件。当MySQL服务重启一次,以’.000001’为后缀的文件会增加一个,并且后缀名加1 递增。如果日志长度超过max_binlog_size的上限,也会创建一个新的日志。 Show binary logs;可以查看当前的 二进制日志文件个数及其文件名。二进制日志并不能直接查看,如果想要查看日志内容,可以通过mysqlbinlog 命令查看。

【查看二进制日志的内容】 退出mysql在命令行
 

 【删除二进制日志】MySQL的二进制文件可以配置自动删除,同时MySQL提供了手动删除二进制文件的方法: RESET MASTER 删除所有的二进制日志文件;PURGE MASTER LOGS只删除部分二进制日志文件。 Reset master; 删除所有二进制日志 Purge master logs to ‘二进制名’ 删除单个二进制日志之前的

 事务日志(或称redo日志)
事务日志(InnoDB特有的日志)可以帮助提高事务的效率。使用事务日志,存储引擎在修改表的数据时只需要 修改其内存拷贝,再把修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的数据本身持久到磁 盘。事务日志采用追加的方式,因此写日志的操作是磁盘上一小块区域内的顺序I/O,而不像随机I/O需要在磁 盘的多个地方移动磁头,所以采用事务日志的方式相对来说要快得多。事务日志持久以后,内存中被修改的数 据在后台可以慢慢的刷回到磁盘。目前大多数的存储引擎都是这样实现的。 如果数据的修改已经记录到事务日 志并持久化,但数据本身还没有写回磁盘,此时系统崩溃,存储引擎在重启时能够自动恢复这部分修改的数 据。具有的恢复方式则视存储引擎而定。

查看事务日志的定义

show global variables like '%log%'; 

| innodb_flush_log_at_timeout     | 1                                      

 | | innodb_flush_log_at_trx_commit  | 1   #在事务提交时innodb是否同步日志从缓冲区到文件 中,当这个值为1(默认值)之时,在每个事务提交时,日志缓冲被写到日志文件,对日志文件做到磁 盘操作的刷新,性能会很差造成大量的磁盘I/O但这种方式安全;如果设为2,每次提交事务都会写日 志,但并不会执行刷的操作。每秒定时会刷到日志文件。要注意的是,并不能保证100%每秒一定都会 刷到磁盘,这要取决于进程的调度。每次事务提交的时候将数据写入事务日志,而这里的写入仅是调用 了文件系统的写入操作,而文件系统是有 缓存的,所以这个写入并不能保证数据已经写入到物理磁 盘。设置为0,日志缓冲每秒一次地被写到日志文件,并且对日志文件做到磁盘操作的刷新,但是在一 个事务提交不做任何操作。 注:刷写的概念 刷写其实是两个操作,刷(flush)和写(write),区分这两个概念是很重要的。在大多数的操作系 统中,把Innodb的log buffer(内存)写入日志(调用系统调用write),只是简单的把数据移到操 作系统缓存中,操作系统缓存同样指的是内存。并没有实际的持久化数据。 所以,通常设为0和2的时候,在崩溃或断电的时候会丢失后一秒的数据,因为这个时候数据只是存在 于操作系统缓存。之所以说“通常”,可能会有丢失不只1秒的数据的情况,比如说执行flush操作的时 候阻塞了。 总结 设为1当然是安全的,但性能页是差的(相对其他两个参数而言,但不是不能接受)。如果对数据 一致性和完整性要求不高,完全可以设为2,如果只求性能,例如高并发写的日志服务器,设为0来获 得更高性能               | | innodb_locks_unsafe_for_binlog   | OFF                                    

 | | innodb_log_buffer_size          | 16777216                                

| | innodb_log_checksums                 | ON                                       | | innodb_log_compressed_pages    | ON                                    

  | | innodb_log_file_size    | 50331648  #日志文件大小                               | | innodb_log_files_in_group     | 2 # DB中设置几组事务日志,默认是2                    
| | innodb_log_group_home_dir    | ./  #定义innodb事务日志组的位置,此位置设置默认为 MySQL的datadir

每个事务日志都是大小为50兆的文件(不同版本的mysql有差异): 在mysql中默认以ib_logfile0,ib_logfile1名 称存在
慢查询日志:slow query log
顾名思义,慢查询日志中记录的是执行时间较长的query,也就是我们常说的slow query。 慢查询日志采用的是 简单的文本格式,可以通过各种文本编辑器查看其中的内容。其中 记录了语句执行的时刻,执行所消耗的时 间,执行用户,连接主机等相关信息。 慢查询日志的作用: 慢查询日志是用来记录执行时间超过指定时间的查 询语句。通过慢查询日志,可以查找出哪些查询语句的执行效率很低,以便进行优化。一般建议开启,它对服 务器性能的影响微乎其微,但是可以记录mysql服务器上执行了很长时间的查询语句。可以帮助我们定位性能 问题的。MySQL 还提供了专门用来分析满查询日志的工具程序mysqldumpslow,用来帮助数据库管理人员解 决可能存在的性能问题。 查看慢查询日志的定义:

 其中: slow_query_log: off关闭状态  on开启状态 slow_query_log_file  慢查询日志存放地点 long_query_time 选项来设置一个时间值,时间以秒为单位,可以精确到微秒。如果查询时间超过了这个时间值(默认为10 秒),这个查询语句将被记录到慢查询日志中, 设置为0的话表示记录所有的查询。 slow_launch_time 表示如 果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加 注:如果不指定存储路径,慢查询 日志默认存储到mysql数据库的数据文件下,如果不指定文件名,默认文件名为hostname-slow.log 修改my.cnf 文件:

vim /ect/my.conf
slow_query_log=1

slow_query_log_file=/usr/local/mysql/data/mysql-slow.log

long_query_time=1

slow_launch_time=1 

重启mysqld服务 再次查询慢查询日志定义 

方法2:通过登录mysql服务器直接定义,方式如下:

 查看慢查询日志

 或用系统查看文件内容命令如cat直接查看慢日志文件 第一行表示记录日志时的时间。其格式是 YYYY-MM-DD HH:MM:SS。我们可以看出上面的查询记录于 2016 年 8 月 29 日下午 15:47:24 - 注意:这个是服务器时间. MySql 用户、服务器以及主机名第三行表示总的查询时 间、锁定时间、"发送"或者返回的行数 Query_time: 0.000304 表示用了0.000304秒 Lock_time: 0.000128 表示锁 了0.000128秒 Rows_sent: 4 表示返回4行 Rows_examined: 4 表示一共查了4行 SET timestamp=UNIXTIME; 这是 查询实际发生的时间 何将其变成一个有用的时间,将 Unix 时间转成一个可读的时间,可以使用 date –d@日志 中的时间戳

以看到查询进行的同时记录了该日志 ,但是对于一台超负载的服务器常常并非如此。因此记住:SET timestamp= value 才是实际的查询的执行时间。 

慢查询分析mysqldumpslow 们可以通过打开log文件查看得知哪些SQL执行效率低下。从日志中,可以发现查 询时间超过long_query_time时间的query为慢查询,而小于long_query_time时间的没有出现在此日志中。 如果 慢查询日志中记录内容很多,可以使用mysqldumpslow工具(MySQL客户端安装自带)来对慢查询日志进行分 类汇总。mysqldumpslow对日志文件进行了分类汇总,显示汇总后摘要结果 进入log的存放目录,运行

 mysqldumpslow mysqld-slow.log 

mysqldumpslow -s c -t 5 -a mysql.slow (-s:何种方式排序,-a:显示数字和字符串,-t:显示前几条)

  mysqldumpslow -s c -t 10 /database/mysql/slow-query.log  这会输出记录次数多 的10条SQL语句,其中: -s, 是表示按照何种方式排序,c、t、l、r分别是按照记录次数、时间、查询时 间、返回的记录数来排序,ac、at、al、ar,表示相应的倒序; -t, 是top n的意思,即为返回前面多少条 的数据; -g, 后边可以写一个正则匹配模式,大小写不敏感的; 例如: /path/mysqldumpslow -s r -t 10 /database/mysql/slow-log  得到返回记录集多的10个查询。 /path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log  得到按照时间排序的前10 条里面含有左连接的查询语句。
 

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。