mysql数据库日志查询

最新推荐文章于 2024-10-01 00:00:00 发布

鱼小旭

最新推荐文章于 2024-10-01 00:00:00 发布

阅读量825

点赞数

分类专栏： SQL进阶学习(包括基础MySQL) 文章标签： mysql 数据库

原文链接：https://www.cnblogs.com/yaokaka/p/11067369.html

版权

SQL进阶学习(包括基础MySQL) 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

Mysql数据库日志
日志分类
MySql日志类型解析说明
错误日志（error log）当数据库启动、运行、停止时产生该日志
普通查询日志（general query log）客户端连接数据库执行语句时产生该日志
二进制日志（binary log）当数据库内容发生改变时产生该日志，也被用来实现主从复制功能
中继日志（relay log）从库上收到主库的数据更新时产生该日志
慢查询日志（show query log） SQL语句在数据库查询超过指定时间时产生该日志
DDL日志（metadata log）执行DDL语句操作元数据时产生该日志
一、错误日志
错误日志说明
Mysql的错误日志用于记录mysql服务进程mysqld在启动、关闭或运行过程中遇到的错误信息。
通常由mysqld或mydql_safe程序产生。

错误日志的配置
配置方法：
1、在my.cnf配置文件的[mysql_safe]模块下配置
[mysql_safe]
log-error=/application/mysql-5.6.36/data/mysql-2.err

2、在启动mysql服务的命令中添加错误日志的参数****
mysqld_safe --log-error=/application/mysql-5.6.36/data/mysql-2.err &

查看日志结果：

[root@mysql-2 ~]# tail -n 4 /application/mysql-5.6.36/data/mysql-2.err
2019-06-15 03:08:26 28735 [Note] Server socket created on IP: ‘::’.
2019-06-15 03:08:26 28735 [Note] Event Scheduler: Loaded 0 events
2019-06-15 03:08:26 28735 [Note] /application/mysql-5.6.36/bin/mysqld: ready for connections.
Version: ‘5.6.36-log’ socket: ‘/application/mysql-5.6.36/tmp/mysql.sock’ port: 3306 Source distribution

错误日志轮询
管理员可以使用命令来轮询错误日志，按天轮询
#进入日志目录
[root@mysql-2 ~]# cd /application/mysql-5.6.36/data/
#将错误日志移动改名
[root@mysql-2 data]# mv mysql-2.err mysql-2_$(date +%F).err
#执行刷新日志命令
[root@mysql-2 data]# mysqladmin flush-logs
#查看新的错误日志
[root@mysql-2 data]# ls -l *.err
-rw-rw---- 1 mysql mysql 28491 Jun 15 03:08 mysql-2_2019-06-15.err
-rw-rw---- 1 mysql mysql 0 Jun 15 10:17 mysql-2.err

安装数据库故障排除
安装完成新数据库无法启动
1、先清空错误日志，启动mysql数据库，查看错误日志内的错误信息经行处理
2、初始化参数错误，删除数据文件，重新初始化数据库

二、普通查询日志
普通查询日志说明
普通查询日志的作用是记录客户端的连接信息及执行的SQL语句信息。

普通查询日志的配置
普通查询日志的重要性很低，默认情况下该日志是关闭的
mysql> show variables like ‘%general_log%’;
±-----------------±-------------------------------------------+
| Variable_name | Value |
±-----------------±-------------------------------------------+
| general_log | OFF |
| general_log_file | /application/mysql-5.6.36/data/mysql-2.log |
±-----------------±-------------------------------------------+
2 rows in set (0.00 sec)

如果希望永久开启，在my.cnf的[mysqld]模块下配置
[mysqld]
general_log=on
general_log_file=/application/mysql-5.6.36/data/mysql_gerenal.log

执行几条命令后，查看普通日志
[root@mysql-2 ~]# tail /application/mysql/data/mysql-2_general.log
/application/mysql-5.6.36/bin/mysqld, Version: 5.6.36-log (Source distribution). started with:
Tcp port: 0 Unix socket: (null)
Time Id Command Argument
[root@mysql-2 ~]# tail /application/mysql/data/mysql-2_general.log
190615 15:13:03 1 Connect root@localhost on
1 Query select @@version_comment limit 1
190615 15:13:10 1 Query SELECT DATABASE()
1 Init DB test
1 Query show databases
1 Query show tables
1 Field List stu
1 Field List test
1 Field List test1
190615 15:13:18 1 Query show tables

在高并发数据库的场景下，建议关闭普通查询日志（默认为关闭），因为查询日志的信息量很大，容易导致磁盘I/O性能问题。当访问量不是很大，企业又有审计执行的SQL语句的需求时，可以考虑开启。

三、二进制日志
二进制日志的说明
二进制日志的时记录数据库里的数据被修改的SQL语句，一般为DDL和DML语句，例如：insert、update、delete、create、drop、alter等关键字的语句。
具体作用：
1、记录mysql数据的增量数据，用来做增量数据库恢复，没有二进制日志功能，mysql的备份将无法完成的还原数据
2、实现主从复制

二进制日志的配置
二进制日志实在my.cnf的[mysqld]模块下配置
[mysqld]
log-bin=/application/mysql-5.6.36/data/mysql-bin
binlog_format=row
binlog_format
mysql复制主要有三种方式：基于SQL语句的复制(statement-based replication, SBR)，基于行的复制(row-based replication, RBR)，混合模式复制(mixed-based replication, MBR)。对应的，binlog的格式也有三种：STATEMENT，ROW，MIXED。

① STATEMENT模式（SBR）

每一条会修改数据的sql语句会记录到binlog中。优点是并不需要记录每一条sql语句和每一行的数据变化，减少了binlog日志量，节约IO，提高性能。缺点是在某些情况下会导致master-slave中的数据不一致(如sleep()函数， last_insert_id()，以及user-defined functions(udf)等会出现问题)

② ROW模式（RBR）

不记录每条sql语句的上下文信息，仅需记录哪条数据被修改了，修改成什么样了。而且不会出现某些特定情况下的存储过程、或function、或trigger的调用和触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是alter table的时候会让日志暴涨。

③ MIXED模式（MBR）

以上两种模式的混合使用，一般的复制使用STATEMENT模式保存binlog，对于STATEMENT模式无法复制的操作使用ROW模式保存binlog，MySQL会根据执行的SQL语句选择日志保存方式。

mysql> show variables like ‘%log_bin%’;
±--------------------------------±----------------------------------------+
| Variable_name | Value |
±--------------------------------±----------------------------------------+
| log_bin | ON #记录binlog开关 |
| sql_log_bin | ON #临时不记录binlog开关
±--------------------------------±----------------------------------------+

sql_log_bin可是临时关闭binlog，只是当前session窗口有效，不影响其他会话

mysql> set sql_log_bin=OFF;
Query OK, 0 rows affected (0.00 sec)

该会话下sql_log_bin=ON开启的
查看sql_log_bin=OFF是否生效

mysql> set sql_log_bin=OFF;
Query OK, 0 rows affected (0.00 sec)

mysql> create database king;
Query OK, 1 row affected (0.00 sec)

binlog日志没有变化

mysql> system mysqlbinlog mysql-bin.000004|grep king;
mysql>
过滤binlog文件，没有记录binlog
开启sql_log_bin=ON

mysql> set sql_log_bin=ON;
Query OK, 0 rows affected (0.00 sec)

mysql> drop database king;
Query OK, 0 rows affected (0.17 sec)

mysql> system mysqlbinlog mysql-bin.000004|grep king;
drop database king
mysql>
发现了有记录的binlog
sql_bin_log的功能实在用户使用mysql恢复数据时，不希望恢复的数据SQL记录到binLog里面
二级制日志文件的刷新条件
1、数据库重启会自动刷新binlog新文件
2、执行"mysqldump -F" 或"mysqladmin flush-logs"会将binlog刷新为新文件
3、binlog文件达到1GB时，会自动刷新binlog为新文件
4、人为配置切割及调整
binlog最大值控制参数及默认大小查看：

二进制日志索引文件
二进制日志除了很多按序列生成的binlog文件列表外，还有一个索引文件

[root@mysql-2 data]# pwd
/application/mysql-5.6.36/data
[root@mysql-2 data]# ls -l mysql-bin*
-rw-rw---- 1 mysql mysql 143 Jun 15 03:08 mysql-bin.000001
-rw-rw---- 1 mysql mysql 376 Jun 15 10:17 mysql-bin.000002
-rw-rw---- 1 mysql mysql 143 Jun 15 15:12 mysql-bin.000003
-rw-rw---- 1 mysql mysql 299 Jun 15 15:49 mysql-bin.000004
-rw-rw---- 1 mysql mysql 164 Jun 15 15:12 mysql-bin.index

索引文件名与binlog文件一样，只是扩展名为index，索引文件的内容
[root@mysql-2 data]# cat mysql-bin.index
/application/mysql/data/mysql-bin.000001
/application/mysql/data/mysql-bin.000002
/application/mysql/data/mysql-bin.000003
/application/mysql/data/mysql-bin.000004

binlog索引文件的控制参数、
mysql> show variables like ‘log_bin_index’;
±--------------±----------------------------------------+
| Variable_name | Value |
±--------------±----------------------------------------+
| log_bin_index | /application/mysql/data/mysql-bin.index |
±--------------±----------------------------------------+
1 row in set (0.00 sec)
删除二进制日志的方法（谨慎操作！！！）
binlog日志很重要，不要随意删除。
确定什么时候可以删除binlog
每天的数据库全备时刻,以前的binlog无用了，实际企业中需要保留3-7天的本地binlog文件按。
方法1：
设置参数自动删除binlog
expire_logs_days = 7 ##设置删除7天前的日志
默认该参数是没有配置的
mysql> show variables like ‘expire_logs_days’;
±-----------------±------+
| Variable_name | Value |
±-----------------±------+
| expire_logs_days | 0 |
±-----------------±------+
1 row in set (0.00 sec)

在全局下设置

mysql> set global expire_logs_days=7;
Query OK, 0 rows affected (0.00 sec)

方法2：
从最开始一直删除到指定的文件位置（不含指定文件）
一般用于处理临时的需求

做一个备份
[root@mysql-2 data]# cp mysql-bin.* /tmp

删除到mysql-bin.000002的binlog(不包含mysql-bin.000002)
只删除mysql-bin.000001

mysql> purge binary logs to ‘mysql-bin.000002’;
Query OK, 0 rows affected (0.00 sec)

方法3：
按照时间删除binlog日志
用于处理临时需求

[root@mysql-2 data]# ls -l --time-style=long-iso mysql-bin*
-rw-rw---- 1 mysql mysql 376 2019-06-15 10:17 mysql-bin.000002
-rw-rw---- 1 mysql mysql 143 2019-06-15 15:12 mysql-bin.000003
-rw-rw---- 1 mysql mysql 299 2019-06-15 15:49 mysql-bin.000004
-rw-rw---- 1 mysql mysql 123 2019-06-15 16:15 mysql-bin.index

mysql> system ls -l --time-style=long-iso mysql-bin*;
-rw-rw---- 1 mysql mysql 376 2019-06-15 10:17 mysql-bin.000002
-rw-rw---- 1 mysql mysql 143 2019-06-15 15:12 mysql-bin.000003
-rw-rw---- 1 mysql mysql 299 2019-06-15 15:49 mysql-bin.000004
-rw-rw---- 1 mysql mysql 123 2019-06-15 16:15 mysql-bin.index

mysql> purge master logs before ‘2019-06-15:23’;
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> system ls -l --time-style=long-iso mysql-bin*;
-rw-rw---- 1 mysql mysql 299 2019-06-15 15:49 mysql-bin.000004
-rw-rw---- 1 mysql mysql 41 2019-06-15 16:22 mysql-bin.index

mysql> reset master;
Query OK, 0 rows affected (0.01 sec)

mysql> system ls -l --time-style=long-iso mysql-bin*;
-rw-rw---- 1 mysql mysql 120 2019-06-15 16:26 mysql-bin.000001
-rw-rw---- 1 mysql mysql 41 2019-06-15 16:26 mysql-bin.index

1、binlog_cache_size:
二进制日志缓存时数据库为每一个客户连接分配的内存空间。对于事务引擎来说，适当调整该参数可以获得更好的性能
默认值：

2、max_binlog_size:
该参数用于设置binlog日志的最大大小，默认为1GB，但是该值并不能严格控制binlog的大小。若binlog大小接近1GB，而此时又在执行一个较大的事务，那么为了保证事务的完整性，数据库不会做日志刷新动作，而是直到该事务的日志全部记录进入当前binlog日志后才会进行刷新。
默认值：
mysql> show variables like ‘%max_binlog_size%’;
±----------------±-----------+
| Variable_name | Value |
±----------------±-----------+
| max_binlog_size | 1073741824 |
±----------------±-----------+
1 row in set (0.00 sec)

3、sync_binlog
这个参数的作用是控制binlog什么时候同步到磁盘。对数据库来说，这是很重要的参数，它不仅会影响数据库的性能，还会影响数据库数据的完整性。
对于“sync_binlog”参数的说明具体如下。
·“sync_binlog=0”表示在事务提交之后，数据库不会将binlog_cache中的数据刷新到磁盘，而是让文件系统自行决定什么时候来做刷新或者在缓存满了之后才刷新到磁盘。
·“sync_binlog=n”表示每进行n次事务提交之后，数据库都会进行一次将缓存数据强制刷新到磁盘的操作。
该参数默认的设置是0，示例如下：
mysql> show variables like ‘%sync_binlog%’;
±--------------±------+
| Variable_name | Value |
±--------------±------+
| sync_binlog | 1 |
±--------------±------+
1 row in set (0.00 sec)
设置为0时数据库的性能是最好的，但数据风险也是最大的，对于数据安全性要求较高的数据库，应该调整该参数将其改为1，值得注意的是，即使参数设置为1，仍然有binlog记录的内容与数据库的实际内容不一致的风险。

可以在my.cnf下的[mysqld]模块设置
[mysqld]
sync_binlog=1
记录二进制日志的三种模式
(1)语句（statement-based）模式是MySQL5.6版本默认的模式，简单地说，就是每一条被修改的数据的SQL语句都会记录到master的binlog中。在复制slave库的时候，SQL进程会解析成与原来master端执行过的相同的SQL来再次执行。
该模式的优点是不需要记录细到每一行数据的更改变化，因此，可减少binlog日志量，实际上是减少了很多，节约了磁盘I/O，提高了系统性能。
但该模式同样有一些缺点，由于语句模式记录的是执行的SQL语句，所以，对于某些具有特殊功能的SQL语句来说，就可能会导致无法在从库上正确执行，从而导致主从库数据不一致的问题。
例如，当特殊的函数被执行时，当触发器、存储过程等特殊功能被执行时，而row level模式是基于每一行来记录变化的，所以不会出现类似的问题
(2)行级模式
简单地说，行级（row-based）模式就是将数据被修改的每一行的情况记录为一条语句。
优点：在行级模式下，binlog中可以不记录执行的SQL语句的上下文相关信息，仅仅记录哪一条记录被修改了，修改成什么样了即可，所以row level的日志内容会非常清楚地记录下每一行数据修改的细节，非常容易理解。而且不会出现某些特定情况下的存储过程或function以及trigger的调用和触发无法被正确复制的问题。
缺点：行级模式下，所有的执行语句都将根据修改的行来记录，而这就可能会产生大量的日志内容，例如一条语句修改了100万行，语句模式就用一条语句即可搞定，而行级模式执行之后，日志中记录的就是100万行的修改记录，binlog日志的量可能会大得惊人。
（3）混合模式
混合（mixed-based）模式默认采用语句模式记录日志，在一些特定的情况下会将记录模式切换为行级模式记录，这些特殊情况包含但不限于以下情况。
当函数中包含UUID()时。
当表中有自增列（AUTO_INCREMENT）被更新时。
·当执行触发器（trigger）或者存储过程（stored function）等特殊功能时。
·当FOUND_ROWS()、ROW_COUNT()、USER()、CURRENT_USER()、CURRENT_USER等执行时。
二进制日志模式的选择
在互联网公司中，使用MySQL的特殊功能比较少（存储过程、触发器、函数），此时可以选择默认的语句模式。
如果公司较多用到MySQL的特殊功能，如存储过程、触发器、函数等，并且需要做主从复制请首选行级模式，次选mixed模式。
二进制日志模式的配置
临时配置
mysql> SET GLOBAL binlog_format = ‘STATEMENT’;
mysql> SET GLOBAL binlog_format = ‘ROW’;
mysql> SET GLOBAL binlog_format = ‘MIXED’;

永久配置
在my.cnf的mysqld模块下配置
[mysqld]
binlog_format=statement
binlog_format=row
binlog_format=mixed

行模式二进制日志实际读取
mysql> use test;
mysql> INSERT INTO test1 values(1,‘a’),(2,‘b’);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> select * from test1;
±—±-----+
| id | name |
±—±-----+
| 1 | a |
| 2 | b |
±—±-----+
2 rows in set (0.00 sec)

[root@mysql-2 data]# mysqlbinlog --base64-output=decode-rows -v mysql-bin.000001
#<==–base64-output=decode-rows -v以行级模式解析binlog日志。
…
BEGIN
/!/;

at 192

#190615 19:17:34 server id 1 end_log_pos 243 CRC32 0x35c8b48a Table_map: test.test1 mapped to number 72

at 243

#190615 19:17:34 server id 1 end_log_pos 292 CRC32 0x3d5c2369 Write_rows: table id 72 flags: STMT_END_F

INSERT INTO `test`.`test1`

SET

@1=1

@2=‘a’

INSERT INTO `test`.`test1`

SET

@1=2

@2=‘b’

at 292

#190615 19:17:34 server id 1 end_log_pos 323 CRC32 0x3e0a646e Xid = 61
COMMIT/!/;
DELIMITER ;

End of log file

ROLLBACK /* added by mysqlbinlog /;
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;
[root@mysql-2 data]#

四、慢查询日志
慢查询日志说明
慢查询日志（slow query log）是记录执行时间超出指定值（long_query_time）或其它地址条件（如：没有使用索引，结果集大于10000行）的SQL语句
慢查询日志的参数说明
慢查询参数解释说明
slow_query_log 慢查询开启开关，默认为”OFF“
slow-query-log-file 记录慢查询语句的文件，文件名如"hostname-slow.log"
long_query_time 记录大于指定N秒的SQL语句，默认为10s，也可以使用微秒单位
log_queries_not_using_indexes 记录没有使用到索引的SQL语句，默认为"OFF"
min_examined_row_limit 记录结果大于N行的SQL语句，默认为0行
log_slow_admin_statements 记录管理的慢SQL语句，如：alter table, analyze table, check table, create table, create index, drop index, optimize table, repair table
log_throttle_queries_not_using_indexes 限制每分钟写入记录的慢SQL语句的数量，默认为"0",表示没有限制
企业中常见的慢查询配置参数
在my.cnf的[mysqld]模块下配置
[mysqld]
slow-query-log = ON #<==慢查询开启开关
long_query_time = 2 #<==记录大于2秒的SQL语句。
log_queries_not_using_indexes = ON #<==没有使用到索引的SQL语句。
slow-query-log-file = /application/mysql-5.6.36/slow.log #<==记录SQL语句的文件。
min_examined_row_limit = 800 #<==记录结果集大于800行的SQL语句。
测试检查

慢查询日志的刷新方法
cd /application/mysql-5.6.36/
mv slow.log slow.log.$(date +%F)
mysqladmin flush-log

制作脚本放入定时任务
脚本：
在/server/scripts下创建脚本
vim /erver/scripts/cut_slow_log.sh
#!/bin/bash
export PATH=/application/mysql-5.6.36/bin:/sbin:/bin:/usr/bin:/usr/sbin
cd /application/mysql-5.6.35
mv slow.log slow.log.$(date +%F)
mysqladmin flush-log
放入定时任务
[root@mysql-2 data]# crontab -e
#cut mysql slow log
00 00 * * * /bin/sh /erver/scripts/cut_slow_log.sh
分析慢查询日志的工具案例
(1)mysqlsla安装
请提前下载好mysqlsla-2.03.tar.gz到指定目录下，然后执行如下命令安装：
yum install perl-devel perl-DBI perl-DBD-MySQL -y
rpm -qa perl-devel perl-DBI perl-DBD-MySQL
tar xf mysqlsla-2.03.tar.gz
cd mysqlsla-2.03
perl Makefile.PL
make && make install
（2）利用mysqlsla工具分析慢查询
mysqlsla命令的默认路径为：/usr/local/bin/mysqlsla。
简单语法如下：
mysqlsla -lt slow [SlowLogFilePath] > [ResultFilePath]
在实际工作中，老男孩通常使用脚本调用mysqlsla工具进行分析，然后每天早晨8点，把分析结果发给企业的核心人员（DBA、运维总监、CTO、研发总监、核心开发），最后由DBA配合核心开发共同优化这些棘手的SQL慢查询。
简单的案例脚本如下，注意切割日志和分析合并为一个脚本了：

vim /server/scripts/slow_log_analyze.sh
#!/bin/bash
export PATH=/application/mysql/bin:/bin:/sbin:/usr/bin:/usr/sbin
Date= $KaTeX parse error: Expected 'EOF', got '#' at position 21: \dots +%F -d -1day) #̲cut slow log cd\dots$ Date
mysqladmin flush-log
#analyze slow log
Path=/usr/local/bin/mysqlsla
cd /application/mysql
$KaTeX parse error: Expected group after '_' at position 32: \dotst slow slow.log_̲$ Date > analyzed_slow_$Dste.log 2>&1
#rsync analyzed_slow to backup server 省略此步
#send analyzed slow log to administrator on backup server by mail. 省略此步
定时任务
crontab -e
00 00 * * * /bin/sh /server/scripts/slow_log_analyze.sh
mysqldumpslow命令