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

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

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)

PHP's big data structure processing skills PHP's big data structure processing skills May 08, 2024 am 10:24 AM

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.

How to optimize MySQL query performance in PHP? How to optimize MySQL query performance in PHP? Jun 03, 2024 pm 08:11 PM

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.

How to use MySQL backup and restore in PHP? How to use MySQL backup and restore in PHP? Jun 03, 2024 pm 12:19 PM

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 a MySQL table using PHP? How to insert data into a MySQL table using PHP? Jun 02, 2024 pm 02:26 PM

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.

How to fix mysql_native_password not loaded errors on MySQL 8.4 How to fix mysql_native_password not loaded errors on MySQL 8.4 Dec 09, 2024 am 11:42 AM

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

How to use MySQL stored procedures in PHP? How to use MySQL stored procedures in PHP? Jun 02, 2024 pm 02:13 PM

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.

How to create a MySQL table using PHP? How to create a MySQL table using PHP? Jun 04, 2024 pm 01:57 PM

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.

The difference between oracle database and mysql The difference between oracle database and mysql May 10, 2024 am 01:54 AM

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.

See all articles