数据库日志管理
一:日志管理
![image-20220925214046253](https://img-blog.csdnimg.cn/img_convert/c623cb6d78da4917c603dc7a896af9ad.png)
1.日志分类
错误日志 :启动,停止,关闭失败报错。rpm安装日志位置 /var/log/mysqld.log
通用查询日志:所有的查询都记下来
二进制日志:实现备份,增量备份。只记录改变数据,除了select都记
中继日志:读取主服务器的binlog,在本地回放。保持一致
slow log:慢查询日志,定义限定时间,若某条查询命令超过该限定时间,则被慢查询日志记录,通过日志提供调整优化
DDL log: 定义语句的日志
2.Error Log
log-error=/var/log/mysqld.log
3.Binary Log
若不指定,默认在 /var/lib/mysql 目录下,以 mysql-bin.xxxxxx 命名
log-bin=/var/log/mysql-bin/slave2
server-id=2
[root@slave2 ~]# mkdir /var/log/mysql-bin
[root@slave2 ~]# chown mysql.mysql /var/log/mysql-bin/
[root@slave2 ~]# systemctl restart mysqld
注意:
需要提前开启
查看binlog日志:
[root@slave2 ~]# mysqlbinlog slave2-bin.000001 -v --base64-output=decode-rows
时间点 : 141126 14:04:49
位置点 : at 106
注:
1. 重启mysqld 会截断
2. flush logs 会截断
3. reset master 删除所有binlog rm -rf /var/lib/mysql/*.000001
4. 删除部分
PURGE BINARY LOGS TO 'mysql-bin.010';
删除 mysql-bin.010 之前的所有binlog日志
PURGE BINARY LOGS BEFORE '2019-04-02 22:46:26';
删除 2019-04-02 22:46:26 这个时间点之前的所有binlog日志
截取binlog
all:
# mysqlbinlog mysql.000002
datetime:
# mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56"
# mysqlbinlog mysql.000002 --stop-datetime="2018-12-05 11:02:54"
# mysqlbinlog mysql.000002 --start-datetime="2018-12-05 10:02:56" --stop-datetime="2018-12-05 11:02:54"
position:
# mysqlbinlog mysql.000002 --start-position=260
# mysqlbinlog mysql.000002 --stop-position=260
# mysqlbinlog mysql.000002 --start-position=260 --stop-position=930
4.Slow Query Log
开启慢查询日志:
[root@xingdian ~]# vim /etc/my.cnf
slow_query_log=1
slow_query_log_file=/var/log/mysql-slow/slow.log
long_query_time=3 设置慢查询超时时间 单位是:秒
创建对应目录:
[root@xingdian ~]# mkdir /var/log/mysql-slow
[root@xingdian ~]# chown mysql.mysql mysql-slow
重启服务:
[root@xingdian ~]# systemctl restart mysqld
验证:
[root@xingdian ~]# mysql -uroot -pQianFeng@123
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.39-log MySQL Community Server (GPL)
Copyright (c) 2000, 2022, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> select sleep(6);
+----------+
| sleep(6) |
+----------+
| 0 |
+----------+
1 row in set (6.00 sec)
mysql> exit
Bye
[root@xingdian ~]# cat /var/log/mysql-slow/slow.log
/usr/sbin/mysqld, Version: 5.7.39-log (MySQL Community Server (GPL)). started with:
Tcp port: 0 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
# Time: 2022-09-25T06:58:05.496205Z
# User@Host: root[root] @ localhost [] Id: 2
# Query_time: 6.007094 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1664089085;
select sleep(6);