Mysql-related log files

There are many types of Mysql Log Files. Some log files record the service status of mysql and the system information generated by mysql during work, we can use this information to operate and maintain mysql services more conveniently. However, incorrect configuration of some log files will cause adverse consequences to the mysql server host, next, let's take a look at the log files in mysql and their respective functions.
Mysql-related log files mainly fall into two categories: transaction logs and Event Logs:
Transaction Log]
Transaction Log: records in detail when and at which time the data has been changed and event replay can be implemented, generally, only operations that change data are recorded, and read operations are not recorded.
Transaction logs provide the following functions for the database server:
1. Converting random I/O to sequential I/O greatly improves database performance. Stored data may exist in different locations on disks, reducing Data Reading and operation performance. The principle of converting to sequential IO is to first store the data in the log file, and then store the data in the log on the disk in the RDBSM background, this ensures that the stored data is continuous.
2. It provides the foundation for event replay. The transaction log records the time when the event occurred and the Data Objects of the operation in detail. The transaction process can replay the time based on the information.
The default transaction log file contains two numbers ending with ibdata + number in the data directory. we can define the location, file size, and growth mode of the transaction log, the method is as follows:
Here we use the Innodb Storage engine that supports transactions as an example.
In the master configuration file/etc/my. cnf of the server:
Innodb_data_home_dir =/innodata defines the directory for storing transaction logs
Innodb_data_file_path = ibdata1: 1024 M defines the log name and size
Innodb_data_file_path = ibdata2: 50 M: autoextend defines the log size growth mode
[Event Log]
Event Log: records historical events of the server, that is, what happened during the production time. The Event Logs can be divided into the following types based on the recorded content:
1. the error log: the file named after host name +. err in the corresponding data directory.
Error Log information type:
1. records the error messages generated during server running.
2. records the information generated when the service is started and stopped.
3. If the replication process is started on the slave server, the information of the replication process will also be recorded.
Enable Error Log: by default, the system has nearly started it automatically
In the main configuration file:
Log-error = specifies the location of the error log. The myaql user must have the write permission,
2. binary log: enabled by default. Precisely records the Data Objects of commands and operations performed on the data in the database.
Roles of binary log files:
1. Provides the Incremental backup function
2. Data is recovered based on time points, which can be controlled by users.
3. It provides the foundation for mysql replication architecture. You can copy the binary logs of the master server to the slave server and perform the same operation to synchronize the data.
Enabled: by default, files named by mysql-bin.number under the Data Directory
Log-bin = Specify the binary log file name
Log-bin-index = Location of the index file that defines the name of the binary log file, which is used to save the name of the available binary log file
(Note: the deletion of binary log files is different from that 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 '2017-3-5 23:00:00 'clear records of all binary logs BEFORE a certain time point
3. the general query log: All query information recorded in the log except slow query will be recorded, which will generate a lot of pressure on the server host, therefore, this log should be disabled for busy servers.
How to enable and disable it:
In the main configuration file:
General_log = [ON/OFF]
Log_output = specifies the location where the query log is stored. It can be stored in a file or in a database table, which is easier to view than in a file.

4. slow query log of the slow query log: by default, the log is disabled. the query records the logs that have been queried for more than the specified time. These query logs are recorded by slow query logs.
Configure in the main configuration file:
Use long_query_time = num to define the default duration. The default duration is 10 seconds.
Enable the following in the configuration file:
Slow_query_log = ON
Slow_query_log_file = Specify the location and name of slow query logs
Methods enabled in mysql:
Mysql> set global slow_query_log = ON

5. the relay log is mainly used on the slave server in the architecture of the mysql server. When the slave server wants to synchronize data with the master server, the slave server copies the binary log file of the master server to its host and stores it in the relay log, then the SQL thread is called to execute the binary log file in the relay log file to synchronize data.
How to enable: (only enable on the slave server)
Configure the mysql master configuration file:
Relay-log = specifies the location and name of the relay log
Relay-log-index = specifies the location and name of the index file for the name of the relay log

6. As long as the ratationg logs rolling log is targeted at binary logs, a new corresponding log file is generated when a type of log files are rolled every time, this method ensures the specific size of the log file, so that the server has a high response capability in log file query.
Command for Rolling binary LOGS: FLUSH LOGS
Difference between transaction logs and binary log files:
Both of them can restore data operations. However, the restoration operations of the former are completed automatically without human intervention, the recovery of the latter relies entirely on humans.
 
Author "redhat"