The MySQL log records the daily operations and error information of the MySQL database. MySQL has different types of log files (each stores different types of logs). From the logs, you can query the running status of the MySQL database, user operations, error information, etc.
MySQL logs are divided into 4 major categories
Error log: record the problems that occur when starting, running or stopping the mysql service
Query log: records the established client connection and executed statements
Binary log: records all statements that change data, which can be used for data replication
Slow query log: records all queries that take longer than long_query_time to execute or queries that do not use indexes. By default, all logs are created with the MySQL data directory. By flushing the log, you can force MySQL to shut down and Reopen the log file, Flush logs to refresh the log, or execute mysqladmin flush-logs. If you are using the MySQL replication function, you can maintain more log files on the replication server. This kind of log is called a replacement log. Enabling the logging function will reduce the performance of the MySQL database.
Error log
In the mysql database, the error log function is enabled by default. By default, error logs are stored in the data directory of the mysql database. The error log file is usually named hostname.err. Among them, hostname represents the server host name. The error log information can be configured by yourself. The information recorded in the error log can be defined through log-error and log-warnings. Log-error defines whether to enable the error log function and the storage location of the error log. log- warnings defines whether warning information is also defined in the error log.
By default, the error log probably records the following aspects of information: information during server startup and shutdown (not necessarily error information, such as how mysql starts the InnoDB table space file, how to initialize its own storage engine, etc.), error messages during server operation, information generated when the event scheduler runs an event, information generated when starting the server process from the server Note 1: MySQL has many system variables that can be set, system variable settings Differences will lead to different system operating status. Therefore, mysql provides two sets of commands to view system settings and operating status respectively.
1. View system settings:
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW VARIABLES： shows the values of MySQL system variables.
Copy after login
2. Running status:
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW STATUS： provides server status information.
Copy after login
Modify system configuration
Method 1: Configuration file settings my.cnf such as ：binlog_cache_size = 1M
Method 2: set global binlog_cache_size = 1048576;
Note: Check the mysql version
[root@localhost ~]# mysql -V
mysql Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using EditLine wrapper
Copy after login
or
mysql> status;
--------------
mysql Ver 14.14 Distrib 5.7.40, for linux-glibc2.12 (x86_64) using EditLine wrapper
Connection id: 11
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.7.40 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: utf8
Conn. characterset: utf8
UNIX socket: /tmp/mysql.sock
Uptime: 87 days 2 hours 22 min 4 sec
Threads: 1 Questions: 61 Slow queries: 0 Opens: 114 Flush tables: 1 Open tables: 107 Queries per second avg: 0.000
--------------
Copy after login
or
mysql> select version();
+-----------+
| version() |
+-----------+
| 5.7.40 |
+-----------+
1 row in set (0.00 sec)
Copy after login
Generally speaking, the definition of log level without session variables is only defined at the global level. The status of the error log is:
mysql> show global variables like '%log_error%';
+---------------------+---------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------+
| binlog_error_action | ABORT_SERVER |
| log_error | /usr/local/mysql/data/mysql.log |
| log_error_verbosity | 3 |
+---------------------+---------------------------------+
3 rows in set (0.00 sec)
Copy after login
where log_error is defined as the error log file path log_error_verbosity:
verbosity value
message types logged
##1
errors only
2
errors and warnings
3
errors,warinigs,and notes(default)
更改错误日志位置可以使用log-error来设置形式如下
[root@localhost ~]# vim /etc/my.cnf
log-error = /usr/local/mysql/data/mysqld.err
[root@localhost ~]# vim /etc/my.cnf
[mysqld]
log_bin=my-bin //二进制日志[路径[指定日志文件的名字]]
Expire_logs_days = 10 //清除日志的天数
Max_binlog_size = 100M //单个日志文件的大小限制，超出会新建一个默认为1GB
server_id=1 //mysql5.7版本以后需要添加serverid
[root@localhost ~]# service mysqld restart
Copy after login
Show variables 或show variables like 'log_%'; 语句来查询日志设置
mysql> show variables like 'log_bin%';
+---------------------------------+------------------------------------+
| Variable_name | Value |
+---------------------------------+------------------------------------+
| log_bin | ON |
| log_bin_basename | /usr/local/mysql/data/my-bin |
| log_bin_index | /usr/local/mysql/data/my-bin.index |
| log_bin_trust_function_creators | OFF |
| log_bin_use_v1_row_events | OFF |
+---------------------------------+------------------------------------+
5 rows in set (0.00 sec)
Copy after login
查看二进制日志
MySQL的二进制日志包含了所有修改的信息，因此常被广泛应用。当MySQL创建二进制日志文件时，首先创建一个以’filename’为名称，以’.index’为后缀的文件；在创建一个以’filename’为名称，以’.000001’为后缀的文件。当MySQL服务重启一次，以’.000001’为后缀的文件会增加一个，并且后缀名加1递增。如果日志长度超过max_binlog_size的上限，也会创建一个新的日志。You can view the number and file names of current binary log files by using the command "Show binary logs;".。若需查看二进制日志的内容，应使用mysqlbinlog命令，而无法直接查看。
mysql> show binary logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| my-bin.000001 | 154 |
+---------------+-----------+
1 row in set (0.00 sec)
Copy after login
或者
mysql> show master logs;
+---------------+-----------+
| Log_name | File_size |
+---------------+-----------+
| my-bin.000001 | 154 |
+---------------+-----------+
1 row in set (0.00 sec)
Copy after login
查看二制日志的内容
退出mysql在命令行
[root@mysql ~]# mysqlbinlog myusql-bin.000001
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
mysqlbinlog: File 'myusql-bin.000001' not found (Errcode: 2 - No such file or directory)
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> show global variables like '%slow_query_log%';
+---------------------+--------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------+
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/mysql-slow.log |
+---------------------+--------------------------------------+
2 rows in set (0.01 sec)
mysql> show global variables like '%long%';
+----------------------------------------------------------+-----------+
| Variable_name | Value |
+----------------------------------------------------------+-----------+
| long_query_time | 10.000000 |
| performance_schema_events_stages_history_long_size | 10000 |
| performance_schema_events_statements_history_long_size | 10000 |
| performance_schema_events_transactions_history_long_size | 10000 |
| performance_schema_events_waits_history_long_size | 10000 |
+----------------------------------------------------------+-----------+
5 rows in set (0.00 sec)
Copy after login
启动和设置慢查询日志：
方法1：通过配置文件my.cnf开启慢查询日志：
注：在不同的mysql版本中，开启慢查询日志参数不太一样，不过都可以通过 show variables like "%slow%" 和show variables like "%long%"查看出来。
mysql> show global variables like '%slow%';
+---------------------------+--------------------------------------+
| Variable_name | Value |
+---------------------------+--------------------------------------+
| log_slow_admin_statements | OFF |
| log_slow_slave_statements | OFF |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | /usr/local/mysql/data/mysql-slow.log |
+---------------------------+--------------------------------------+
5 rows in set (0.00 sec)
例如： /path/mysqldumpslow -s r -t 10/database/mysql/slow-log 得到返回记录集最多的10个查询。/path/mysqldumpslow -s t -t 10 -g “left join” /database/mysql/slow-log 得到按照时间排序的前10条里面含有左连接的查询语句。
The above is the detailed content of What are the four major types of logs in MySQL?. For more information, please follow other related articles on the PHP Chinese website!
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
![[Web front-end] Node.js quick start](https://img.php.cn/upload/course/000/000/067/662b5d34ba7c0227.png)
