Shulou(Shulou.com)05/31 Report--

This article is to share with you what the log files related to mysql contain. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

There are many kinds of log files in Mysql. Some log files record the service status of mysql and the system information generated by mysql at work. We can use this information to operate and maintain mysql services more conveniently, but some log files will cause adverse consequences to the server host of mysql if we do not configure them correctly. Let's take a look at what log files mysql has and their respective roles.

There are two main types of Mysql-related log files: transaction log and event log:

[transaction log]

Transaction log: records in detail when and when the data was changed, and then the events can be replayed. Generally, only the operations that change the data are recorded, and the read operations are generally not recorded.

The transaction log performs the following functions for the server:

1. Converting random IO to sequential IO greatly improves the performance of the database, and the stored data may exist in different locations of the disk, which reduces the performance of data reading and operation. The principle of converting to sequential IO is that the data is first stored in the log file, and then the data in the log is stored on disk by the background of RDBSM, which ensures that the stored data is continuous.

2. Provide the basis for event playback. The transaction log records the time of occurrence and the data object of the operation in detail. The transaction process can replay the time according to this information.

There are two default transaction log files, which are located in the number ending in ibdata+number in the data directory. We can define the location, file size and growth mode of the transaction log as follows:

Here is an example of using an Innodb storage engine that supports transactions

In the server's main configuration file / etc/my.cnf:

Innodb_data_home_dir = / innodata defines the directory where transaction logs are stored

Innodb_data_file_path = ibdata1:1024M defines the name and size of the log

Innodb_data_file_path = ibdata2:50M:autoextend defines how the log size increases

[event Log]

Event log: records the historical events of the server, that is, what happened during the birth time. Event logs can be divided into the following categories according to the content of the record:

1. The error log error log: a file named with the hostname + .err in the corresponding data directory.

The type of information recorded in the error log:

1. Record the error messages generated during the operation of the server.

2. Record the information generated when the service is started and stopped.

3. When the replication process is started on the slave server, the information of the replication process will also be recorded

Enable error logging: by default, the system has nearly started it automatically

In the main configuration file:

Log-error= specifies the location of the error log, where the myaql user must have write permission

2. The binary log binary log: enabled by default. A data object that accurately records the commands and operations of the user to operate on the data in the database.

The purpose of binary log files:

1. Provide the function of incremental backup

2. Provide a point-in-time recovery of data, which can be controlled by the user.

3. Provide the basis for the replication architecture. By copying the binary logs of the master server to the slave server and performing the same operation, the data can be synchronized

Enabled: files named with mysql-bin.number by default in the data directory

Log-bin= specifies the name of the binary log file

Log-bin-index= defines the location of the index file for the name of the binary log file, which is used to hold the names of the available binary log files

(note: the deletion of binary log files is different from the deletion of general files, we can use the following methods

PURAGE BINARYLOGS BEFORE 'mysql-bin.00001' clears the binary log file before a binary log file

PURAGE BINARYLOGS BEFORE '2011-3-5 2300 purge all binary log records before a certain point in time

3. The general query log query log: all the query information recorded in the slow log will be recorded, which will exert a lot of pressure on the server host, so this log should be closed for busy servers.

Methods to enable and close:

In the main configuration file:

General_log= [ON / OFF]

Log_output= specifies the location to store the query log, which can be placed in a file or in a table in the database. It is easier to view in a table than in a file.

4. The slow query log slow log: if the query time exceeds the set time, the log will be recorded by slow log.

Configure in the main configuration file:

Define the default duration through long_query_time=num. The default duration is 10 seconds.

Enable in the configuration file:

Slow_query_log=ON

Slow_query_log_file= specifies the location and name of the slow log

Methods enabled in mysql:

Mysql > SET GLOBAL slow_query_log=ON

5, the the relay log relay log is mainly used on the slave server in the mysql server architecture. When the slave server wants to synchronize the data with the master server, the slave server copies the binary log file of the master server to its own host and puts it in the relay log, and then calls the SQL thread to execute according to the binary log file in the copy relay log file so that the data synchronization can be achieved.

Method to enable it: (open it only from the server)

Configure the main configuration file for mysql:

Relay-log= specifies the location and name of the relay log

Relay-log-index= specifies the name of the relay log, the location and name of the index file

6. The ratationg logs scrolls the log. As long as it scrolls for the binary log, a new corresponding log file is generated once for a certain type of log file. In this way, the specific size of the log file is guaranteed, thus ensuring that the server has a high response ability to the log file query.

Command for scrolling binary logs: FLUSH LOGS

The difference between transaction logs and binary log files:

Both of them can restore the data operation, but the former is automatically completed by the system, which does not need and does not allow human intervention, while the recovery of the latter depends entirely on human.

Thank you for reading! This is the end of this article on "what are the log files related to mysql?". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it for more people to see!

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.