运维-1.日志

1.错误日志

mysql> show variables like '%log_error%';
+----------------------------+----------------------------------------+
| Variable_name              | Value                                  |
+----------------------------+----------------------------------------+
| binlog_error_action        | ABORT_SERVER                           |
| log_error                  | /var/log/mysqld.log                    |
| log_error_services         | log_filter_internal; log_sink_internal |
| log_error_suppression_list |                                        |
| log_error_verbosity        | 2                                      |
+----------------------------+----------------------------------------+
5 rows in set (0.01 sec)

2.二进制日志

mysql> show variables like '%log_bin%';
+---------------------------------+-----------------------------+
| Variable_name                   | Value                       |
+---------------------------------+-----------------------------+
| log_bin                         | ON                          |
| log_bin_basename                | /var/lib/mysql/binlog       |
| log_bin_index                   | /var/lib/mysql/binlog.index |
| log_bin_trust_function_creators | OFF                         |
| log_bin_use_v1_row_events       | OFF                         |
| sql_log_bin                     | ON                          |
+---------------------------------+-----------------------------+
6 rows in set (0.01 sec)

mysql> show variables like '%%binlog_format%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
1 row in set (0.01 sec)

当在数据库进行创建表、插入数据操作，使用参数-v输出日志

[root@hdss7-6 mysql]# mysqlbinlog -v binlog.000005

#240826  9:41:17 server id 1  end_log_pos 1262 CRC32 0x59a10c83         Query   thread_id=14    exec_time=0     error_code=0    Xid = 102
use `test`/*!*/;
SET TIMESTAMP=1724636477/*!*/;
/*!80013 SET @@session.sql_require_primary_key=0*//*!*/;
create table student(
 id int auto_increment primary key COMMENT 'ID',
 name varchar(10) comment '姓名',
 no varchar(10) comment '学号'
)comment '学生表'

SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1341
#240826  9:41:56 server id 1  end_log_pos 1416 CRC32 0x84848c88         Query   thread_id=14    exec_time=0     error_code=0
SET TIMESTAMP=1724636516/*!*/;
BEGIN

BINLOG '
ZN3LZhMBAAAAQAAAAMgFAAAAAIEAAAAAAAEABHRlc3QAB3N0dWRlbnQAAwMPDwQoACgABgEBAAID
/P8A9voWZA==
ZN3LZh4BAAAAaAAAADAGAAAAAIEAAAAAAAEAAgAD/wABAAAABGxpc2EFMjAwMDEAAgAAAAV0cmFj
eQUyMDAwMgADAAAABWphbm1zBTIwMDAzAAQAAAAHcWlhb2RhbgUyMDAwNDU903I=
'/*!*/;
### INSERT INTO `test`.`student`
### SET
###   @1=1
###   @2='lisa'
###   @3='20001'
### INSERT INTO `test`.`student`
### SET
###   @1=2
###   @2='tracy'
###   @3='20002'
### INSERT INTO `test`.`student`
### SET
###   @1=3
###   @2='janms'
###   @3='20003'
### INSERT INTO `test`.`student`
### SET
###   @1=4
###   @2='qiaodan'
###   @3='20004'
# at 1584
#240826  9:41:56 server id 1  end_log_pos 1615 CRC32 0x94eac79d         Xid = 109
COMMIT/*!*/;
# at 1615
#240826  9:46:28 server id 1  end_log_pos 1694 CRC32 0xff6a03e2         Anonymous_GTID  last_committed=6        sequence_number=7       rbr_only=yes    original_committed_timestamp=1724636788123563        immediate_commit_timestamp=1724636788123563     transaction_length=332
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= 'ANONYMOUS'/*!*/;
# at 1694
#240826  9:46:28 server id 1  end_log_pos 1778 CRC32 0x0d72a437         Query   thread_id=15    exec_time=0     error_code=0
SET TIMESTAMP=1724636788/*!*/;
BEGIN
/*!*/;
# at 1778
#240826  9:46:28 server id 1  end_log_pos 1842 CRC32 0x210e431a         Table_map: `test`.`student` mapped to number 129
# has_generated_invisible_primary_key=0
# at 1842
#240826  9:46:28 server id 1  end_log_pos 1916 CRC32 0x7786c0d0         Update_rows: table id 129 flags: STMT_END_F

BINLOG '
dN7LZhMBAAAAQAAAADIHAAAAAIEAAAAAAAEABHRlc3QAB3N0dWRlbnQAAwMPDwQoACgABgEBAAID
/P8AGkMOIQ==
dN7LZh8BAAAASgAAAHwHAAAAAIEAAAAAAAEAAgAD//8AAQAAAARsaXNhBTIwMDAxAAEAAAAKbGlz
YWxhbGFsYQUyMDAwMdDAhnc=
'/*!*/;
### UPDATE `test`.`student`
### WHERE
###   @1=1
###   @2='lisa'
###   @3='20001'
### SET
###   @1=1
###   @2='lisalalala'
###   @3='20001'
# at 1916
#240826  9:46:28 server id 1  end_log_pos 1947 CRC32 0xced1c482         Xid = 131
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> purge master logs to '/var/lib/mysql/binlog.000002';
Query OK, 0 rows affected (0.02 sec)

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

mysql>

查看日志过期时间30天。

mysql> show variables like '%binlog_expire_log%';
+-------------------------------+---------+
| Variable_name                 | Value   |
+-------------------------------+---------+
| binlog_expire_logs_auto_purge | ON      |
| binlog_expire_logs_seconds    | 2592000 |
+-------------------------------+---------+
2 rows in set (0.03 sec)

3.查询日志

mysql> show variables like '%general%';
+------------------+----------------------------+
| Variable_name    | Value                      |
+------------------+----------------------------+
| general_log      | OFF                        |
| general_log_file | /var/lib/mysql/hdss7-6.log |
+------------------+----------------------------+
2 rows in set (0.00 sec)

修改完配置文件，重启mysql服务。
查询日志操作

[root@hdss7-6 mysql]# tail -f mysql_query.log
2024-08-26T02:53:41.695404Z         8 Query     show databases
2024-08-26T02:53:44.936258Z         8 Query     show databases
2024-08-26T02:53:58.231995Z         8 Query     SELECT DATABASE()
2024-08-26T02:53:58.241255Z         8 Init DB   test
2024-08-26T02:53:58.242515Z         8 Query     show databases
2024-08-26T02:53:58.250721Z         8 Query     show tables
2024-08-26T02:53:58.254893Z         8 Field List        student
2024-08-26T02:54:05.963128Z         8 Query     SELECT DATABASE()
2024-08-26T02:54:05.963804Z         8 Init DB   test
2024-08-26T02:54:20.021264Z         8 Query     show tables

4.慢查询日志

vi /etc/my.cnf

#开启慢日志查询开关
slow_query_log=1

#设置慢日志的时间为2s。SQL语句执行时间超过2s，就会视为慢查询，记录慢查询日志
long_query_time=2