6. MySQL数据库日志——二进制日志

一、MySQL日志类型

• 错误日志
• 慢查询日志
• 二进制日志
• 事务日志

1、慢查询日志

1.1 慢查询的概念

• 执行时长超过long_query_time变量定义的时长，默认是10秒
• 默认慢查询日志没有启用

1.2 启动慢查询日志

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

[mysqld]

long_query_time=10
slow_query_log=ON
slow_query_log_file=slow.log

mysql> SELECT sleep(15);
+-----------+
| sleep(15) |
+-----------+
|         0 |
+-----------+
1 row in set (15.01 sec)

[root@localhost ~]# cat /mysql/data/slow.log 
/usr/sbin/mysqld, Version: 5.7.44-log (MySQL Community Server (GPL)). started with:
Tcp port: 0  Unix socket: /var/lib/mysql/mysql.sock
Time                 Id Command    Argument
# Time: 2024-05-12T01:21:44.261295Z
# User@Host: root[root] @ localhost []  Id:     2
# Query_time: 15.004884  Lock_time: 0.000000 Rows_sent: 1  Rows_examined: 0
SET timestamp=1715476904;
SELECT sleep(15);

二、二进制日志 binary log

1、作用

1.1 记录的内容

• 记录数据库产生的写操作

1.2 作用

• 数据恢复，即时点还原
• 主从复制

2、启用二进制日志

• 强烈建议将二进制日志存放到不同的存储设备上

[root@localhost ~]# df -hT | grep "mysql"
/dev/sdb                xfs        20G  365M   20G   2% /mysql/data
/dev/sdd                xfs        20G   33M   20G   1% /mysql/log

[root@localhost ~]# ls -ldh /mysql/log/
drwxr-xr-x 2 mysql mysql 68 May 12 09:50 /mysql/log/

[root@localhost ~]# vim /etc/my.cnf
server_id=10
log_bin=/mysql/log/master

[root@localhost ~]# systemctl restart mysqld 

[root@localhost ~]# ls /mysql/log/
master.000001  master.000002  master.index

[root@localhost ~]# cat /mysql/log/master.index 
/mysql/log/master.000001
/mysql/log/master.000002

3、管理操作

3.1 查看二进制日志内容

[root@localhost ~]# mysqlbinlog /mysql/log/master.000002 

记录每个写操作：
	操作、起始位置、终止位置、时间、server_id

• 按时间查看
  –start-datetime=
  –stop-datetime=

  [root@localhost ~]# mysqlbinlog --start-datetime="2024-05-11 9:50:11" --stop-datetime="2024-05-11 10:51:02" /mysql/log/master.000002 
  

• 按位置查看
  –start-position=
  –stop-position=

 	[root@localhost ~]# mysqlbinlog --start-position=313 --stop-position=463 /mysql/log/master.000002  

3.2 查看当前二进制日志

mysql> SHOW MASTER STATUS;
+---------------+----------+--------------+------------------+-------------------+
| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+---------------+----------+--------------+------------------+-------------------+
| master.000002 |     6461 |              |                  |                   |
+---------------+----------+--------------+------------------+-------------------+

3.3 查看二进制日志文件中的操作

mysql> SHOW BINLOG EVENTS IN 'master.000002';