1未命名0

Mysql 常用日志介绍

柳三千

文章最后更新时间：2022年10月26日已超过693天没有更新。

常见的日志文件

日志类型写入日志的信息备注
错误日志（error-log）
记录MySQL在启动、运行和停止时遇到的问题
用于排查mysql运行故障
一般查询日志（general_log）建立所有客户端发向服务端的请求
无论请求是否成功都会记录在这个日志，数据非常庞大，不建议开启，当怀疑客户端请求有问题时候，可以临时开启
慢查询日志(slow_query_log)long_query_time执行时间超过几秒的查询
可以排查哪些SQL语句执行比较慢，方便优化sql语句，提高查询效率
二进制日志（binary_log）更改数据的语句（也用于复制）
可以用来恢复数据、主从复制也使用到这个日志，建议开启
中继日志（relay_log）从复制主服务器收到的数据更改
用于主从复制，临时存储从主库同步的二进制日志
DDL日志（元数据日志）
由DDL语句执行的元数据操作


错误日志（默认是开启的）

修改my.cnf文件

log_error=/path路径   #默认存在$datadir/hostname.err

指定错误日志的路径

图片.png


一般查询日志（默认是禁用的）

#开启常规查询日志（0表示禁用，1表示开启）
general_log= 0 

#指定常规日志的路径及文件名
general_log_file=file_name


该参数用于定义general log 和 slow log的输出目标

log-output=value

#value=[ TABLE | FILE | NONE ]默认值为FILE
#TABLE表示将日志记录到表中，general_log表或者slow_log表中
#FILE表示将日志记录到文本文件中
#NONE表示不记录到表或者文件


慢查询日志

#开启慢查询日志(0表示关闭，1表示开启)
slow_query_log=1

#指定慢查询日志的路径及文件名，默认$datadir /host_name-slow.log
slow_query_log_file=file_name

#定义日志输出目标
log-output=value

#定义查询语句超过N秒就被记录到slow log里，最小值为0，默认值为10秒
long_query_time=1

#默认情况下，不会记录管理语句，也不会记录不使用索引进行查找的查询；可以使用log_slow_admin_statements和更改此行为log_queries_not_using_indexes
#记录执行较慢的管理语句
log_slow_admin_statements=1

#记录执行较慢的未使用索引的语句
log_queries_not_using_indexes=1


案例：

vim /etc/my.cnf
slow_query_log=1  #开启慢查询日志功能
slow_query_log_file=/data/log/slow.log  #指定慢查询日志存放路径
long_query_time=1   #超过一秒就视为慢查询语句并写入日志

图片.png

图片.png


二进制日志

作用：二进制日志记录数据库的所有更改操作（DDL/DML/DCL），不包含select或者show这类语句

  用于主从复制中，master主服务器将二进制日志中的更改操作发送给slave从服务器，从服务器执行这些更改操作，达到与主服务器同步的效果。

  用于数据的恢复操作。

  默认二进制日志是关闭的，可以使用log-bin=xxx参数开启

  mysqlbinlog工具查看


二进制日志三种格式：

日志格式
含义
STATEMENT
基于SQL语句的日志记录，记录是SQL语句，对数据进行修改的SQL都会记录在日志文件中。
ROW
基于行的日志记录，记录的是每一行的数据变更（默认）。
MIXED
混合了STATEMENT和ROW两种格式，默认采用STATEMENT，在某些特殊情况下会自动切换为ROW进行记录。


案例：

#创建bin目录存放二进制日志
[root@localhost mysql5.7]# mkdir /data/mysql57/log/bin
[root@localhost mysql5.7]# chown -R mysql:mysql /data/

[root@localhost mysql5.7]# vim my.cnf
#开启二进制日志
log-bin=/data/mysql57/log/bin/mybin.log

#设置二进制日志使用内存大小
binlog_cache_size=1M

#设置使用二进制的日志格式
binlog_format=row

#二进制日志过期清理时间
expire_logs_days=7

#跳过主从复制遇中遇到的1062问题，1062错误是指一些主键重复，1032是主从数据库数据不一致导致的
slave_skip_errors=1062

#mysql服务器ID，需保证唯一
server-id=1

#是否只读，1代表只读，0代表读写
read-only=0

#不需要备份的数据库
binlog-ignore-db=mysql

#指定同步的数据库
#binlog-do-db=数据库名
#查看二进制日志
[root@localhost mysql5.7]# ls /data/mysql57/log/bin/mybin.000001  mybin.index

查看二进制日志是否开启

mysql> show variables like "%log_bin%";
+---------------------------------+-----------------------------------+
| Variable_name                   | Value                             |
+---------------------------------+-----------------------------------+
| log_bin                         | ON                                |
| log_bin_basename                | /data/mysql57/log/bin/mybin       |
| log_bin_index                   | /data/mysql57/log/bin/mybin.index |
| log_bin_trust_function_creators | OFF                               |
| log_bin_use_v1_row_events       | OFF                               |
| sql_log_bin                     | ON                                |
+---------------------------------+-----------------------------------+
6 rows in set (0.00 sec)

#临时开启或者关闭
#mysql> set global log_bin=on;



查看二进制日志

mysqlbinlog 参数选项 日志名

 由于日志是由二进制方式储存的，不能直接读取，需要通过二进制日志查询工具mysqlbinlog来查看

参数选项：

 -d   指定数据库名称，只列出指定的数据库相关操作

 -o   忽略掉日志中的前N行命令

 -v   将行事件（数据变更）重构为sql语句

 -vv   将行事件（数据变更）重构为sql语句，并输出注释信息


新建一个数据库：

[root@localhost mysql5.7]# mysql -S /tmp/mysql57.sock -uroot -p 
Enter password:
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> create database ldx;
Query OK, 1 row affected (0.00 sec)


查看二进制日志，啥也没看出，这是正常的，row模式下，是看不懂，需要加-v重构成SQL语句

[root@localhost bin]# /usr/local/mysql5.7/bin/mysqlbinlog mybin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#221025  2:46:14 server id 1  end_log_pos 123 CRC32 0x72141927     Start: binlog v 4, server v 5.7.39-log created 221025  2:46:14 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
NoZXYw8BAAAAdwAAAHsAAAABAAQANS43LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA2hldjEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AScZFHI=
'/*!*/;
# at 123
#221025  2:46:14 server id 1  end_log_pos 154 CRC32 0xeedca3e4     Previous-GTIDs
# [empty]
# at 154
#221025  2:46:32 server id 1  end_log_pos 219 CRC32 0x8185caf0     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#221025  2:46:32 server id 1  end_log_pos 290 CRC32 0x8b4f133e     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1666680392/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 290
#221025  2:46:32 server id 1  end_log_pos 343 CRC32 0x3ea46a33     Table_map: `ldx`.`student` mapped to number 109
# at 343
#221025  2:46:32 server id 1  end_log_pos 394 CRC32 0x7650b720     Write_rows: table id 109 flags: STMT_END_F

BINLOG '
SIZXYxMBAAAANQAAAFcBAAAAAG0AAAAAAAEAA2xkeAAHc3R1ZGVudAADAw8DAgwABzNqpD4=
SIZXYx4BAAAAMwAAAIoBAAAAAG0AAAAAAAEAAgAD//gDAAAABuW8oOmjnhoAAAAgt1B2
'/*!*/;
# at 394
#221025  2:46:32 server id 1  end_log_pos 425 CRC32 0x018f7225     Xid = 5
COMMIT/*!*/;
# at 425
#221025  2:46:45 server id 1  end_log_pos 490 CRC32 0xfbbe28f1     Anonymous_GTID    last_committed=1    sequence_number=2    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 490
#221025  2:46:45 server id 1  end_log_pos 561 CRC32 0xcd4d8237     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1666680405/*!*/;
BEGIN
/*!*/;
# at 561
#221025  2:46:45 server id 1  end_log_pos 614 CRC32 0x3e4608e5     Table_map: `ldx`.`student` mapped to number 109
# at 614
#221025  2:46:45 server id 1  end_log_pos 665 CRC32 0x94992531     Write_rows: table id 109 flags: STMT_END_F

BINLOG '
VYZXYxMBAAAANQAAAGYCAAAAAG0AAAAAAAEAA2xkeAAHc3R1ZGVudAADAw8DAgwAB+UIRj4=
VYZXYx4BAAAAMwAAAJkCAAAAAG0AAAAAAAEAAgAD//gEAAAABuWFs+e+vRsAAAAxJZmU
'/*!*/;
# at 665
#221025  2:46:45 server id 1  end_log_pos 696 CRC32 0x5a4cc9c9     Xid = 6
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
[root@localhost bin]# /usr/local/mysql5.7/bin/mysqlbinlog -v mybin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#221025  2:46:14 server id 1  end_log_pos 123 CRC32 0x72141927     Start: binlog v 4, server v 5.7.39-log created 221025  2:46:14 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
NoZXYw8BAAAAdwAAAHsAAAABAAQANS43LjM5LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAA2hldjEzgNAAgAEgAEBAQEEgAAXwAEGggAAAAICAgCAAAACgoKKioAEjQA
AScZFHI=
'/*!*/;
# at 123
#221025  2:46:14 server id 1  end_log_pos 154 CRC32 0xeedca3e4     Previous-GTIDs
# [empty]
# at 154
#221025  2:46:32 server id 1  end_log_pos 219 CRC32 0x8185caf0     Anonymous_GTID    last_committed=0    sequence_number=1    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 219
#221025  2:46:32 server id 1  end_log_pos 290 CRC32 0x8b4f133e     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1666680392/*!*/;
SET @@session.pseudo_thread_id=2/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1436549152/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 290
#221025  2:46:32 server id 1  end_log_pos 343 CRC32 0x3ea46a33     Table_map: `ldx`.`student` mapped to number 109
# at 343
#221025  2:46:32 server id 1  end_log_pos 394 CRC32 0x7650b720     Write_rows: table id 109 flags: STMT_END_F
BINLOG '
SIZXYxMBAAAANQAAAFcBAAAAAG0AAAAAAAEAA2xkeAAHc3R1ZGVudAADAw8DAgwABzNqpD4=
SIZXYx4BAAAAMwAAAIoBAAAAAG0AAAAAAAEAAgAD//gDAAAABuW8oOmjnhoAAAAgt1B2
'/*!*/;
### INSERT INTO `ldx`.`student`
### SET
###   @1=3
###   @2='张飞'
###   @3=26
# at 394
#221025  2:46:32 server id 1  end_log_pos 425 CRC32 0x018f7225     Xid = 5
COMMIT/*!*/;
# at 425
#221025  2:46:45 server id 1  end_log_pos 490 CRC32 0xfbbe28f1     Anonymous_GTID    last_committed=1    sequence_number=2    rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 490
#221025  2:46:45 server id 1  end_log_pos 561 CRC32 0xcd4d8237     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1666680405/*!*/;
BEGIN
/*!*/;
# at 561
#221025  2:46:45 server id 1  end_log_pos 614 CRC32 0x3e4608e5     Table_map: `ldx`.`student` mapped to number 109
# at 614
#221025  2:46:45 server id 1  end_log_pos 665 CRC32 0x94992531     Write_rows: table id 109 flags: STMT_END_F
BINLOG '
VYZXYxMBAAAANQAAAGYCAAAAAG0AAAAAAAEAA2xkeAAHc3R1ZGVudAADAw8DAgwAB+UIRj4=
VYZXYx4BAAAAMwAAAJkCAAAAAG0AAAAAAAEAAgAD//gEAAAABuWFs+e+vRsAAAAxJZmU
'/*!*/;
### INSERT INTO `ldx`.`student`
### SET
###   @1=4
###   @2='关羽'
###   @3=27
# at 665
#221025  2:46:45 server id 1  end_log_pos 696 CRC32 0x5a4cc9c9     Xid = 6
COMMIT/*!*/;
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
DELIMITER ;
# End of log file
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
mysql> select * from student;
+------+--------+------+
| id   | name   | age  |
+------+--------+------+
|    1 | 张三   |   23 |
|    2 | lisi   |   26 |
|    3 | 张飞   |   26 |
|    4 | 关羽   |   27 |
+------+--------+------+
4 rows in set (0.00 sec)

总结：重启mysql后，我添加了两行数据，在二进制日志文件加了-v就可以看出关羽、张飞等sql语句！


中继日志

作用：

  用于主从复制，master主服务器将自己的二进制日志发送给slave从服务器，slave先保存在自己的中继日志中，然后再执行自己本地的relay log里的sql达到数据库更改和master保持一致。

  默认中继日志没有开启，可以使用relay-log参数开启。

#开启中继日志
relay-log=/data/mysql57/log/relay/myrelay.log

#保证了relay-log的完整性
relay-log-recovery=1

#binlog日志写入系统缓存，保证安全性
sync-relay-log=1


mysql> show variables like '%relay%';
+---------------------------+---------------------------------------+
| Variable_name             | Value                                 |
+---------------------------+---------------------------------------+
| max_relay_log_size        | 0                                     |
| relay_log                 | /data/mysql57/log/relay/myrelay.log   |
| relay_log_basename        | /data/mysql57/log/relay/myrelay       |
| relay_log_index           | /data/mysql57/log/relay/myrelay.index |
| relay_log_info_file       | relay-log.info                        |
| relay_log_info_repository | FILE                                  |
| relay_log_purge           | ON                                    |
| relay_log_recovery        | ON                                    |
| relay_log_space_limit     | 0                                     |
| sync_relay_log            | 1                                     |
| sync_relay_log_info       | 10000                                 |
+---------------------------+---------------------------------------+
11 rows in set (0.00 sec)
max_relay_log_size0 
relay log 允许的最大值，如果该值为0，则默认值为 max_binlog_size (1G)；
relay_log
定义relay_log的位置路径，开启中继日志功能；如果值为空，则默认位置在数据文件的目录
relay_log_info_file
relay-log.info记录 master 主库的 binary_log 的恢复位置和 slave库 relay_log 的位置
relay_log_purgeON是否自动清空中继日志，默认值为1(启用)
relay_log_recoveryOFF当slave从库宕机后，假如relay-log损坏了，导致一部分中继日志没有处理，则自动放弃所有未执行的relay-log，并且重新从master上获取日志，这样就保证了relay-log的完整性。默认情况下该功能是关闭的，将relay_log_recovery的值设置为 1时，可在slave从库上开启该功能，建议开启
sync_relay_log10000

  当设置为1时，slave的I/O线程每次接收到master发送过来的binlog日志都要写入系统缓冲区，然后刷入relay log中继日志里，这样是最安全的，因为在崩溃的时候，你最多会丢失一个事务，但会造成磁盘的大量I/O；

  当设置为0时，并不是马上就刷入中继日志里，而是由操作系统决定何时来写入，虽然安全性降低了，但减少了大量的磁盘I/O操作。这个值默认是0，可动态修改；

sync_relay_log_info
10000这个参数和 sync_relay_log 参数一样
文章版权声明：除非注明，否则均为柳三千运维录原创文章，转载或复制请以超链接形式并注明出处。

相关阅读

  • Mysql 常用日志介绍
  • Mysql重置root密码
  • Mysql用户、密码、权限管理

    • 目录[+]

    取消
    微信二维码
    微信二维码
    支付宝二维码