Shulou(Shulou.com)06/01 Report--

This article mainly introduces where the log of the data update operation of mysql is recorded. It is very detailed and has a certain reference value. Friends who are interested must read it!

Mysql updates to the 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.; binary logs record various operations of the database in binary form, but do not record query statements.

Log is an important part of the database, which is mainly used to record the operation, daily operation and error information of the database.

MySQL Universal query Log (General Query Log)

The generic query log (General Query Log) is used to record all the actions of the user, including starting and shutting down MySQL services, update statements, query statements, and so on.

By default, the generic query log feature is turned off. You can check whether the general query log is enabled with 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)

As you can see from the results, the generic query log is turned off, and the general_log_file variable specifies the location of the generic query log file.

Start and set up the generic query log

In MySQL, you can enable the general query log in the following format by adding the log option to the MySQL configuration file:

[mysqld] log=dir/filename

Where the dir parameter specifies the storage path of the generic query log, and the filename parameter specifies the file name of the log. If you do not specify a storage path, the generic query log is stored by default in the data folder of the MySQL database. If you do not specify a file name, the default file name is hostname.log, where hostname represents the host name.

View the generic query log

If you want to know about the user's recent actions, you can view the generic query log. The generic query log is stored as a text file, and you can use a plain text file to view the contents of this type of log.

Example 1

First, we check whether the general query log function is enabled, 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 changedmysql > SELECT * FROM tb_student;+----+-+ | id | name | +-- +-+ | 1 | Java | | 2 | MySQL | | 3 | Python | +-+-+

3 rows in set (0.06 sec)

After the execution is successful, open the general query log, where the name of the log is LAPTOP-UHQ6V8KP.log. Here are some of the contents 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: MySQLTime Id Command Argument2020-05-29T06:43:44.382878Z 7 Quit2020-05-29T06:44:10.001382Z 8 Connect root@localhost on using SSL/TLS2020-05-29T06:44:10.007532Z 8 Query select @ @ version_comment limit 12020-05-29T06:44:11.748179Z 8 Query SHOW VARIABLES LIKE'% general%'2020-05-29T06:44:25.487472Z 8 Query SELECT DATABASE () 2020-05-29T06 : 44Query SELECT 25.487748Z 8 Init DB test2020-05-29T06:44:35.390523Z 8 Query SELECT * FROM tb_student

As you can see, the log clearly records all the behavior of the client.

MySQL binary log (Binary Log)

Binary log (Binary Log), also known as change log (Update Log), is a very important log in MySQL. It is mainly used to record the changes of the database, that is, the DDL and DML statements of SQL statements, without data record query operations.

If the MySQL database stops unexpectedly, you can use the binary log file to see what the user has done, what changes 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, binary logging is turned off. You can check whether binary logging is enabled with the following command:

Mysql > SHOW VARIABLES LIKE 'log_bin';+-+-+ | Variable_name | Value | +-+-+ | log_bin | OFF | +-+-+ 1 row in set, 1 warning (0.02 sec)

As you can see from the results, the binary log is turned off.

Start and set up binary logs

In MySQL, you can open binary logs in the following format by adding the log-bin option to the configuration file:

[mysqld] log-bin=dir/ [filename]

Where 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,number in the form of 000001, 000002, and so on.

Each time the MySQL service is restarted, a new binary log file is generated. The filename part of the file name of these log files will not change, and the number will be incremented.

If there are no dir and filename parameters, the binary logs are stored by default in the data directory of the database, and the default file name is hostname-bin.number, where hostname represents the host name.

The following statement is added to the [mysqld] group of the my.ini file:

Log-bin

After restarting the MySQL server, you can see the LAPTOP-UHQ6V8KP-bin.000001 file in the data directory of the MySQL database, and the LAPTOP-UHQ6V8KP-bin.index file is also generated. Here, the hostname of the MySQL server is LAPTOP-UHQ6V8KP.

You can also make the following changes 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 the mylog.index file in the C:log folder.

View binary log

1. View a list of binary log files

You can use the following command to see which binary log files are in MySQL:

Mysql > SHOW binary logs +-- +-+ | Log_name | File_size | +-- +-+ | LAPTOP-UHQ6V8KP-bin.000001 | 177c | LAPTOP-UHQ6V8KP-bin.000002 | | 154 | +-- +-+ 2 rows in set (0.00 sec) |

two。 View the binary log file currently being written

You can use the following command to view the binary log files being written in the current MySQL.

Mysql > SHOW master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_ DB | Executed_Gtid_Set | +-- + | LAPTOP-UHQ6V8KP-bin.000002 | 154th | | | +-+ 1 row in set (0.00 sec) |

3. View the contents of the binary log file

Binary logs are 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.

The syntax of the mysqlbinlog command is as follows:

Mysqlbinlog filename.number

The mysqlbinlog command only looks for the specified binary log under the current folder, so you need to run the command in the same directory as the binary log, otherwise the specified binary log file will not be found.

Example 1

Let's use the mysqlbinlog command to view the mylog.000001 file in the C:\ log directory. The code executes as follows:

C:\ Users\ 11645 > cd C:\ logC:\ log > mysqlbinlog mylog.000001max / 50530 SET @ @ SESSION.PSEUDOSLAVEMODE1 server id 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.

Due to the long content of the log, only part of the content in mylog is shown here.

When you use the mysqlbinlog command, you can specify the path where the binaries are stored. This ensures that the mysqlbinlog command can find the binaries. The command in the above example can be in the following form:

Mysqlbinlog C:\ log\ mylog.000001

In this way, the mysqlbinlog command goes to the C:\ log directory to look for the mylog.000001 file. If you do not specify a path, the mysqlbinlog command looks for the mylog.000001 file in the current directory.

In addition to the filename.number file, MySQL generates a file called filename.index, which stores a list of all binary log files, which you can open with notepad.

Tip: in practice, binary log files and database data files are not on the same hard disk, so that even if the hard disk where the data files are located is destroyed, you can use the binary logs on another hard disk to recover the database files. Two hard drives are much less likely to break at the same time, which ensures the security of the data in the database.

The above is all the contents of the update operation of mysql to record where the log is, thank you for your reading! Hope to share the content to help you, more related knowledge, welcome to follow the industry information channel!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.