In which log are mysql's data update operations recorded?
Mysql’s update operations on data are recorded in the general query log and binary log. The general query log is used to record all user operations, including starting and shutting down MySQL services, update statements and query statements, etc.; the binary log records various database operations in binary form, but does not record query statements.
(Recommended tutorial: mysql video tutorial)
The log is an important part of the database, mainly used to record The running status, daily operations and error information of the database.
MySQL General Query Log (General Query Log)
The General Query Log (General Query Log) is used to record all user operations, including Start and shut down MySQL services, update statements and query statements, etc.
By default, the general query log function is turned off. You can check whether the general query log is turned on by running the following command:
mysql> SHOW VARIABLES LIKE '%general%'; +------------------+----------------------------------------------------------------+ | Variable_name | Value | +------------------+----------------------------------------------------------------+ | general_log | OFF | | general_log_file | C:\ProgramData\MySQL\MySQL Server 5.7\Data\LAPTOP-UHQ6V8KP.log | +------------------+----------------------------------------------------------------+ 2 rows in set, 1 warning (0.01 sec)
From the results, it can be seen that the general query log is turned off, and the general_log_file variable specifies the location of the general query log file.
Start and set up the general query log
In MySQL, you can turn on the general query log by adding the log option to the MySQL configuration file. The format is as follows:
[mysqld] log=dir/filename
Among them, the dir parameter specifies the storage path of the general query log; the filename parameter specifies the file name of the log. If you do not specify a storage path, the general query log will be stored in the data folder of the MySQL database by default. If you do not specify a file name, the default file name is hostname.log, where hostname represents the host name.
View the general query log
If you want to know the user’s recent operations, you can view the general query log. General query logs are stored in the form of text files, and you can use ordinary text files to view the log content of this type.
Example 1
First we check whether the general query log function is turned on, and then query the records of the tb_student table. The SQL command and execution process are as follows:
mysql> SHOW VARIABLES LIKE '%general%'; +------------------+----------------------------------------------------------------+ | Variable_name | Value | +------------------+----------------------------------------------------------------+ | general_log | ON | | general_log_file | C:\ProgramData\MySQL\MySQL Server 5.7\Data\LAPTOP-UHQ6V8KP.log | +------------------+----------------------------------------------------------------+ 2 rows in set, 1 warning (0.02 sec) mysql> use test; Database changed mysql> SELECT * FROM tb_student; +----+--------+ | id | name | +----+--------+ | 1 | Java | | 2 | MySQL | | 3 | Python | +----+--------+
3 rows in set (0.06 sec)
After successful execution, open the general query log. The log name here is LAPTOP-UHQ6V8KP.log. The following is part of the general query log.
C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqld.exe, Version: 5.7.29-log (MySQL Community Server (GPL)). started with: TCP Port: 3306, Named Pipe: MySQL Time Id Command Argument 2020-05-29T06:43:44.382878Z 7 Quit 2020-05-29T06:44:10.001382Z 8 Connect root@localhost on using SSL/TLS 2020-05-29T06:44:10.007532Z 8 Query select @@version_comment limit 1 2020-05-29T06:44:11.748179Z 8 Query SHOW VARIABLES LIKE '%general%' 2020-05-29T06:44:25.487472Z 8 Query SELECT DATABASE() 2020-05-29T06:44:25.487748Z 8 Init DB test 2020-05-29T06:44:35.390523Z 8 Query SELECT * FROM tb_student
It can be seen that the log records all the client's behaviors very clearly.
MySQL Binary Log (Binary Log)
The Binary Log (Binary Log) can also be called the Change Log (Update Log), which is MySQL Very important log. It is mainly used to record changes in the database, that is, DDL and DML statements of SQL statements, and does not include data record query operations.
If the MySQL database stops unexpectedly, you can use the binary log file to view what operations the user has performed and what modifications have been made to the database server file, and then restore the database server based on the records in the binary log file.
By default, the binary log function is turned off. You can check whether the binary log is turned on by running the following command:
mysql> SHOW VARIABLES LIKE 'log_bin'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_bin | OFF | +---------------+-------+ 1 row in set, 1 warning (0.02 sec)
It can be seen from the results that the binary log is turned off.
Start and set up binary logs
In MySQL, you can enable binary logs by adding the log-bin option in the configuration file. The format is as follows:
[mysqld] log-bin=dir/[filename]
Among them, the dir parameter specifies the storage path of the binary file; the filename parameter specifies the file name of the binary file, in the form of filename.number, and the form of number is 000001, 000002, etc.
Every time the MySQL service is restarted, a new binary log file will be generated. The filename part of the file names of these log files will not change, and the number will continue to increase.
If there are no dir and filename parameters, the binary log will be stored in the data directory of the database by default. The default file name is hostname-bin.number, where hostname represents the host name.
Add the following statement in the [mysqld] group of the my.ini file:
log-bin
After restarting the MySQL server, you can see LAPTOP-UHQ6V8KP-bin in the data directory of the MySQL database. 000001 this file, the LAPTOP-UHQ6V8KP-bin.index file is also generated. Here, the host name of the MySQL server is LAPTOP-UHQ6V8KP.
You can also make the following modifications in the [mysqld] group of the my.ini file. The statement is as follows:
log-bin=C:log\mylog
After restarting the MySQL service, you can see the mylog.000001 file and mylog.index file in the C:log folder.
View the binary log
1. View the binary log file list
You can use the following command to view which binaries are in MySQL Log file:
mysql> SHOW binary logs; +----------------------------+-----------+ | Log_name | File_size | +----------------------------+-----------+ | LAPTOP-UHQ6V8KP-bin.000001 | 177 | | LAPTOP-UHQ6V8KP-bin.000002 | 154 | +----------------------------+-----------+ 2 rows in set (0.00 sec)
2. View the binary log file currently being written
You can use the following command to view the binary log file currently being written in MySQL.
mysql> SHOW master status; +----------------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------------------+----------+--------------+------------------+-------------------+ | LAPTOP-UHQ6V8KP-bin.000002 | 154 | | | | +----------------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)
3. View the contents of the binary log file
The binary log is stored in binary format and cannot be opened and viewed directly. If you need to view the binary log, you must use the mysqlbinlog command.
mysqlbinlog 命令的语法形式如下:
mysqlbinlog filename.number
mysqlbinlog 命令只在当前文件夹下查找指定的二进制日志,因此需要在二进制日志所在的目录下运行该命令,否则将会找不到指定的二进制日志文件。
例 1
下面使用 mysqlbinlog 命令,来查看 C:\log 目录下的 mylog.000001 文件,代码执行如下:
C:\Users\11645>cd C:\log C:\log>mysqlbinlog mylog.000001 /*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/; /*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/; DELIMITER /*!*/; # at 4 #200527 9:33:37 server id 1 end_log_pos 123 CRC32 0x69738cfd Start: binlog v 4, server v 5.7.29-log created 200527 9:33:37 at startup ......
由于日志内容较长,这里只展示了 mylog 中的部分内容。
使用 mysqlbinlog 命令时,可以指定二进制文件的存储路径。这样可以确保 mysqlbinlog 命令可以找到二进制文件。上面例子中的命令可以变为如下形式:
mysqlbinlog C:\log\mylog.000001
这样,mysqlbinlog 命令就会到 C:\log 目录下去查找 mylog.000001 文件。如果不指定路径,mysqlbinlog 命令将在当前目录下查找 mylog.000001 文件。
除了 filename.number 文件,MySQL 还会生成一个名为 filename.index 的文件,这个文件存储着所有二进制日志文件的列表,可以用记事本打开该文件。
小技巧:实际工作中,二进制日志文件与数据库的数据文件不放在同一块硬盘上,这样即使数据文件所在的硬盘被破坏,也可以使用另一块硬盘上的二进制日志来恢复数据库文件。两块硬盘同时坏了的可能性要小得多,这样可以保证数据库中数据的安全。
更多编程相关知识,请访问:编程入门!!
The above is the detailed content of In which log are mysql's data update operations recorded?. For more information, please follow other related articles on the PHP Chinese website!

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

Big data structure processing skills: Chunking: Break down the data set and process it in chunks to reduce memory consumption. Generator: Generate data items one by one without loading the entire data set, suitable for unlimited data sets. Streaming: Read files or query results line by line, suitable for large files or remote data. External storage: For very large data sets, store the data in a database or NoSQL.

MySQL query performance can be optimized by building indexes that reduce lookup time from linear complexity to logarithmic complexity. Use PreparedStatements to prevent SQL injection and improve query performance. Limit query results and reduce the amount of data processed by the server. Optimize join queries, including using appropriate join types, creating indexes, and considering using subqueries. Analyze queries to identify bottlenecks; use caching to reduce database load; optimize PHP code to minimize overhead.

Backing up and restoring a MySQL database in PHP can be achieved by following these steps: Back up the database: Use the mysqldump command to dump the database into a SQL file. Restore database: Use the mysql command to restore the database from SQL files.

How to insert data into MySQL table? Connect to the database: Use mysqli to establish a connection to the database. Prepare the SQL query: Write an INSERT statement to specify the columns and values to be inserted. Execute query: Use the query() method to execute the insertion query. If successful, a confirmation message will be output.

One of the major changes introduced in MySQL 8.4 (the latest LTS release as of 2024) is that the "MySQL Native Password" plugin is no longer enabled by default. Further, MySQL 9.0 removes this plugin completely. This change affects PHP and other app

To use MySQL stored procedures in PHP: Use PDO or the MySQLi extension to connect to a MySQL database. Prepare the statement to call the stored procedure. Execute the stored procedure. Process the result set (if the stored procedure returns results). Close the database connection.

Creating a MySQL table using PHP requires the following steps: Connect to the database. Create the database if it does not exist. Select a database. Create table. Execute the query. Close the connection.

Oracle database and MySQL are both databases based on the relational model, but Oracle is superior in terms of compatibility, scalability, data types and security; while MySQL focuses on speed and flexibility and is more suitable for small to medium-sized data sets. . ① Oracle provides a wide range of data types, ② provides advanced security features, ③ is suitable for enterprise-level applications; ① MySQL supports NoSQL data types, ② has fewer security measures, and ③ is suitable for small to medium-sized applications.
