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!
Statement of this 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
The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.
Laravel is a PHP framework for easy building of web applications. It provides a range of powerful features including: Installation: Install the Laravel CLI globally with Composer and create applications in the project directory. Routing: Define the relationship between the URL and the handler in routes/web.php. View: Create a view in resources/views to render the application's interface. Database Integration: Provides out-of-the-box integration with databases such as MySQL and uses migration to create and modify tables. Model and Controller: The model represents the database entity and the controller processes HTTP requests.
MySQL and phpMyAdmin are powerful database management tools. 1) MySQL is used to create databases and tables, and to execute DML and SQL queries. 2) phpMyAdmin provides an intuitive interface for database management, table structure management, data operations and user permission management.
Compared with other programming languages, MySQL is mainly used to store and manage data, while other languages such as Python, Java, and C are used for logical processing and application development. MySQL is known for its high performance, scalability and cross-platform support, suitable for data management needs, while other languages have advantages in their respective fields such as data analytics, enterprise applications, and system programming.
I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.
Article summary: This article provides detailed step-by-step instructions to guide readers on how to easily install the Laravel framework. Laravel is a powerful PHP framework that speeds up the development process of web applications. This tutorial covers the installation process from system requirements to configuring databases and setting up routing. By following these steps, readers can quickly and efficiently lay a solid foundation for their Laravel project.
The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA
When developing an e-commerce website using Thelia, I encountered a tricky problem: MySQL mode is not set properly, causing some features to not function properly. After some exploration, I found a module called TheliaMySQLModesChecker, which is able to automatically fix the MySQL pattern required by Thelia, completely solving my troubles.
