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!