Home Database Mysql Tutorial In which log are mysql's data update operations recorded?

In which log are mysql's data update operations recorded?

Nov 02, 2020 pm 01:56 PM
mysql log

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.

In which log are mysql's data update operations recorded?

(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)
Copy after login

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
Copy after login

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 |
+----+--------+
Copy after login

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
Copy after login

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)
Copy after login

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]
Copy after login

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
Copy after login

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
Copy after login

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)
Copy after login

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)
Copy after login

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
Copy after login

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
......
Copy after login

由于日志内容较长,这里只展示了 mylog 中的部分内容。

使用 mysqlbinlog 命令时,可以指定二进制文件的存储路径。这样可以确保 mysqlbinlog 命令可以找到二进制文件。上面例子中的命令可以变为如下形式:

mysqlbinlog C:\log\mylog.000001
Copy after login

这样,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!

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

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to open phpmyadmin How to open phpmyadmin Apr 10, 2025 pm 10:51 PM

You can open phpMyAdmin through the following steps: 1. Log in to the website control panel; 2. Find and click the phpMyAdmin icon; 3. Enter MySQL credentials; 4. Click "Login".

MySQL: An Introduction to the World's Most Popular Database MySQL: An Introduction to the World's Most Popular Database Apr 12, 2025 am 12:18 AM

MySQL is an open source relational database management system, mainly used to store and retrieve data quickly and reliably. Its working principle includes client requests, query resolution, execution of queries and return results. Examples of usage include creating tables, inserting and querying data, and advanced features such as JOIN operations. Common errors involve SQL syntax, data types, and permissions, and optimization suggestions include the use of indexes, optimized queries, and partitioning of tables.

How to use single threaded redis How to use single threaded redis Apr 10, 2025 pm 07:12 PM

Redis uses a single threaded architecture to provide high performance, simplicity, and consistency. It utilizes I/O multiplexing, event loops, non-blocking I/O, and shared memory to improve concurrency, but with limitations of concurrency limitations, single point of failure, and unsuitable for write-intensive workloads.

MySQL's Place: Databases and Programming MySQL's Place: Databases and Programming Apr 13, 2025 am 12:18 AM

MySQL's position in databases and programming is very important. It is an open source relational database management system that is widely used in various application scenarios. 1) MySQL provides efficient data storage, organization and retrieval functions, supporting Web, mobile and enterprise-level systems. 2) It uses a client-server architecture, supports multiple storage engines and index optimization. 3) Basic usages include creating tables and inserting data, and advanced usages involve multi-table JOINs and complex queries. 4) Frequently asked questions such as SQL syntax errors and performance issues can be debugged through the EXPLAIN command and slow query log. 5) Performance optimization methods include rational use of indexes, optimized query and use of caches. Best practices include using transactions and PreparedStatemen

Why Use MySQL? Benefits and Advantages Why Use MySQL? Benefits and Advantages Apr 12, 2025 am 12:17 AM

MySQL is chosen for its performance, reliability, ease of use, and community support. 1.MySQL provides efficient data storage and retrieval functions, supporting multiple data types and advanced query operations. 2. Adopt client-server architecture and multiple storage engines to support transaction and query optimization. 3. Easy to use, supports a variety of operating systems and programming languages. 4. Have strong community support and provide rich resources and solutions.

How to connect to the database of apache How to connect to the database of apache Apr 13, 2025 pm 01:03 PM

Apache connects to a database requires the following steps: Install the database driver. Configure the web.xml file to create a connection pool. Create a JDBC data source and specify the connection settings. Use the JDBC API to access the database from Java code, including getting connections, creating statements, binding parameters, executing queries or updates, and processing results.

Monitor Redis Droplet with Redis Exporter Service Monitor Redis Droplet with Redis Exporter Service Apr 10, 2025 pm 01:36 PM

Effective monitoring of Redis databases is critical to maintaining optimal performance, identifying potential bottlenecks, and ensuring overall system reliability. Redis Exporter Service is a powerful utility designed to monitor Redis databases using Prometheus. This tutorial will guide you through the complete setup and configuration of Redis Exporter Service, ensuring you seamlessly build monitoring solutions. By studying this tutorial, you will achieve fully operational monitoring settings

How to view sql database error How to view sql database error Apr 10, 2025 pm 12:09 PM

The methods for viewing SQL database errors are: 1. View error messages directly; 2. Use SHOW ERRORS and SHOW WARNINGS commands; 3. Access the error log; 4. Use error codes to find the cause of the error; 5. Check the database connection and query syntax; 6. Use debugging tools.

See all articles