mysql--日志管理

mysql日志管理

文件位置

/etc/my.cnf

一.文件分类

general_log=on

general_log_file =

错误日志：记录启动、运行或关闭MySQL服务器遇到的问题

二进制日志：又称binlog日志，以二进制文件的方式记录数据库中除SELECT以外的操作查询日志：记录查询的信息

慢查询日志：记录执行时间超过指定时间的操作

中继日志：备库将主库的二进制日志复制到自己的中继日志中，从而在本地进行重放通用日志：审计哪个账号、在那个时段、做了哪些事件

事务日志：或称redo日志，记录Innodb事务相关的如事务执行时间、检查点等

二.日志管理

1.慢查询日志

mysql> SHOW GLOBAL VARIABLES; // 查看MySQL服务器的变量

mysql> show variables like ‘%slow%’;

±--------------------------±----------------------+

| Variable_name | Value |

±--------------------------±----------------------+

| log_slow_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| slow_launch_time | 2 |

| slow_query_log | OFF |

| slow_query_log_file | /backup/slow_log_file |

±--------------------------±----------------------+

变量：

long_query_time 指定慢查询的时间，单位：秒 （默认10s）

slow_query_log 是否开启慢查询日志

slow_query_log_file 指定慢查询日志的文件名称及路径

slow_launch_time=# 表示如果建立线程花费了比这个值更长的时间,slow_launch_threads 计数器将增加

mysql>set global slow_query_log =ON; 开启慢查询日志(临时开启,重启服务失效)

mysql> show variables like ‘%slow%’;

±--------------------------±---------------------+

| Variable_name | Value |

±--------------------------±---------------------+

| log_slow_admin_statements | OFF |

| log_slow_slave_statements | OFF |

| slow_launch_time | 2 |

| slow_query_log | ON |

| slow_query_log_file | /data/mysql-slow.log |

±--------------------------±---------------------+

5 rows in set (0.01 sec)

永久Th效慢查询 修改主配置文件

修改配置文件： # vim /etc/my.cnf [mysqld]

long_query_time=3 slow_query_log=on slow_query_log_file=/data/slow.log 注意：

如果想要让配置Th效，需重启mysql服务。且要注意【slow.log】文件的属主和属组。

测试：

mysql> select sleep(3); # cat /backup/slow.log

mysql> select *,sleep(3) from t3;

#cat /data/slow.log

# Query_time: 3.001503 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 use school;

SET timestamp=1594968923; select sleep(3);

# Time: 2020-07-17T06:56:08.124569Z

# User@Host: root[root] @ localhost [] Id: 2

# Query_time: 9.003956 Lock_time: 0.000197 Rows_sent: 3 Rows_examined: 3 SET timestamp=1594968968;

select *,sleep(3) from t3;

2.bin-log

binlog日志是把数据库的每一个变化都记载到一个专用的文件里，select语句不记录，这种文件叫日志文件， mysql默认只打开错误的日志文件，因为过多的日志会影响系统的处理性能。

mysql记录日志的开销是比较大的，所以最好将日志单独分离到一个磁盘上，不要和数据放在同一块磁盘上。二进制的binlog日志在shell中是看不了的，需要使用对应的工具mysqlbinlog。

1. 查看log_bin状态

mysql> show variables like ‘%log_bin%’;

±--------------------------------±------+

| Variable_name | Value |

±--------------------------------±------+

| log_bin | OFF |

| log_bin_basename | |

| log_bin_index | |

| log_bin_trust_function_creators | OFF |

| log_bin_use_v1_row_events | OFF |

| sql_log_bin | ON |

±--------------------------------±------+

2. 开启binlog日志

# vi /etc/my.cnf [mysqld] server_id=1

log_bin=/var/lib/mysql/mysql-bin binlog_format=statement expire_logs_days=5

注释：

server_id表示为单个结点的id，在集群中有多个结点，结点id不能相同。log_bin指定binlog日志文件的名字为mysql-bin，以及其存储路径

binlog_format是设置记录的格式，如果不加，binlog文件中只记录create、drop记录，不记录insert记录。expire_logs_days指定二进制日志文件的有效天数，自动删除过期的文件。但若二进制文件没有增长和切换，服务

器不会清除老条目。注：

1. 重启mysqld会 截断旧日志产Th新的日志2.刷新日志会截断旧日志产Th新的日志mysql> flush logs;

<!-- -->

3. 删除所有binlog（禁用） mysql> reset master;

4. 删除部分日志

mysql> PURGE BINARY LOGS TO ‘mysql-bin.000003’; 删除mysql-bin.000003之前的日志

mysql> PURGE BINARY LOGS BEFORE ‘2018-01-01 11:11:11’; 删除"2018-01-01 11:11:11"此时间之前的日志

5. 暂停binlog日志功能（仅对当前会话Th效） mysql> SET SQL_LOG_BIN=0;

mysql> SET SQL_LOG_BIN=1;

3. 查看binlog日志文件 1）查看所有的二进制文件： mysql> show binary logs;

±-----------------±----------+

| Log_name | File_size |

±-----------------±----------+

| mysql-bin.000001 | 177 |

| mysql-bin.000002 | 154 |

±-----------------±----------+

2 rows in set (0.00 sec)

2）查看当前正在使用的二进制文件： mysql> show master status;

±-----------------±---------±-------------±-----------------±------------------+

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |

±-----------------±---------±-------------±-----------------±------------------+

| mysql-bin.000002 | 154 | | | |

±-----------------±---------±-------------±-----------------±------------------+

1 row in set (0.00 sec)

4. 查看日志详细

<!-- -->

1. show binlog events方式

   1. 只查看第一个binlog文件的内容

mysql> show binlog events; # 默认会返回mysql-bin.000001的日志

2. 查看指定binlog文件的内容

mysql> show binlog events in ‘mysql-bin.000002’; 3- 获取指定位置binlog的内容

mysql> show binlog events in ‘mysql-bin.000002’ from 123;

2. mysqlbinlog命令行1- 查看binlog日志

1> 查看指定的binlog日志

#mysqlbinlog mysql-bin.000001

200717 17:06:11 时间点：表示2020-07-17 17:06:11

at 593 位置点： 593

…

mysqlbinlog mysql-bin.000001 --start-datetime=“2018-12-10 17:00:00”

mysqlbinlog mysql-bin.000001 --start-datetime=“2018-12-10 17:00:00” --stop-datetime=“2018-12-10 17:30:00”

3> 按字节数查看二进制日志

mysqlbinlog mysql-bin.000001 --start-position=20 mysqlbinlog mysql-bin.000001 --stop-position=200

mysqlbinlog mysql-bin.000001 --start-position=20 --stop-position=200 4> 过滤insert、update操作

mysqlbinlog mysql-bin.000005 |grep insert

2- 查看binlog日志并输出

1> 提取指定position位置的binlog日志并输出到压缩文件

# mysqlbinlog --start-position=“120” --stop-position=“332” /opt/data/APP01bin.000001 | gzip >extra_01.sql.gz

2> 提取指定position位置的binlog日志导入数据库

# mysqlbinlog --start-position=“120” --stop-position=“332” /opt/data/APP01bin.000001 | mysql -uroot -p

3> 提取指定开始时间的binlog并输出到日志文件

# mysqlbinlog --start-datetime=“2014-12-15 20:15:23” /opt/data/APP01bin.000002 --result- file=extra02.sql

4> 提取指定位置的多个binlog日志文件

# mysqlbinlog --start-position=“120” --stop-position=“332” /opt/data/APP01bin.000001 /opt/data/ APP01bin.000002|more

5> 远程提取日志，指定结束时间

# mysqlbinlog -urobin -p -h192.168.1.116 -P3306 --stop-datetime=“2014-12-15 20:30:23” --read-from- remote-server mysql-bin.000033 |more