运维-1.日志
MySQL日志
- 1.错误日志
- 2.二进制日志
- 3.查询日志
- 4.慢查询日志
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/*!*/;
BEGINBINLOG '
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_FBINLOG '
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