MySQL log type
MySQL log category:
Query logs:Log, general_log, log_output
Slow query log:The query execution time exceeds the specified query time, that is, the slow query;
Error log:Generally, it refers to the error log information, usually the log information of server shutdown and startup, the error information during server running, and the warning information.
Binary log:It is only an operation related to modification. it can be understood as a redo log for copying basic creden;
Relay log:It is actually related to replication, which is almost the same as binary logs;
Transaction log:Random I/O is converted to sequential I/O, which is usually stored in two files. if one is full, it is changed to another storage.
Query Logs
Log = {ON | OFF} # whether to record the log information of all statements in the general query log FILE (general_log); log_output = {TABLE | FILE | NONE} # define the log storage format, none indicates that logs are not saved. tables and files can appear at the same time and can be separated by commas. general_log = {ON | OFF} # whether to enable log query; general_log_file # defines the files stored in general query logs; mysql> set golbal general_log = {OFF | ON}; enable or disable mysql> set glogal log_output = 'table '; option to control the log storage method, table means to store in the form of tables.
Note:
-
To enable log Query, log and general_log must both be in the ON state;
-
We do not recommend that you enable this log for busy MySQL instances;
Slow query log: Slow query if the query execution time exceeds the specified time length
Long_query_time # defines the length of the slow query; slow_query_log {ON | OFF} # global parameter, set whether to enable the slow query log; its output position also depends ON the value of log_output; slow_query_log_file # defines the log file path and name; log_slow_filter # slow query log filtering type; log_slow_queries # indicates whether to enable slow query logs at the session level; log_rate_limit = 1 # defines the log record rate; log_slow_verbosity # whether to record detailed log information;
Error log
Main record content:
-
Information during Server startup and shutdown;
-
Error message during server running;
-
Information generated when the event scheduler runs an event;
-
Information generated when the slave server thread is started from the server in the replication architecture;
Parameter settings:
Log_error =/path/to/error_log_file # define the error log file log_warnnings = {1 | 0} # whether to record warning information in the error log
Binary log:
It records the modification operations on the MySQL database, and information that affects the potential content of the data. select logs are not recorded in binary logs. binary logs are also called replication logs, by default, it is in the data directory,The command used to view logs is mysqlbinlog.
Function: 1. restore at a time point. we can manually adjust the time point from which to recover the data. Therefore, this is an important backup recovery tool.
2. mysql master-slave replication architecture;
Because our MySQL data is a single-process multi-thread mechanism, it can initiate many statements that we modify at the same time, but our server only uses one log, if the requests of multiple threads simultaneously write data to the log file at the same time, the log file becomes a resource hotspot, also called a resource requisition point, which will be messy, to solve this problem, when our thread needs to write data to the binary log file, it does not directly write data to the log file, or write data to the log buffer in a unified manner, the log buffer is used to write log files one by one. However, for a very busy server, our log files produce a large amount of data every day, if all the binary log data is written in a log file, it is inconvenient to manage and unreasonable. if the log file is lost, all log data information is lost. if this is not the case, we have a log rolling mechanism. we can define binary logs by ourselves, there are two methods to define log rollback, one is defined by size, for example, we define the size of a log file to 1 GB, the storage will scroll around 1 GB to make The next log is used to store binary log data. the other one can be defined by time, for example, rolling once a week or once a month or once a day; or the log will be rolled every time you restart the service or execute the flush logs command.
MariaDB [(none)]> show master status; you can view the binary log files being used by the current server and the Position based on when the next event starts. if you are currently using 00004, so 00001, 2, 3 will not be used again, because it has been rolled over. MariaDB [hellodb]> show binary logs; view all the BINARY log files on the current system, in fact, is the information in the mysql-bin.index file under the Data Directory, this file is the entry that saves the log file that has been rolled. MariaDB [hellodb]> flush logs; command for clearing log files: PURGEMariaDB [hellodb]> show binlog events in 'log _ file '; # mysqlbinlog -- start-time -- stop-time -- start-# mysqlbinlog -- start-protion = 1139 mysql-bin.000001: in the command line, view the data recorded after a certain location of a log file. You can use output redirection to save it to a file for future execution. Server-id: id of the server, MariaDB [hellodb]> SELECTVERSION (); view the current database version MariaDB [(none)]> show binlog events in 'MySQL-bin.000001 'FROM 1139; view the event information of a binary log file. MariaDB [(none)]> show binlog events in 'MySQL-bin.000006 '/G.
Format of binary logs recorded by MySQL:
Statement: Statement-based
-
Defect: when a statement with a time function is executed, the execution results of the master and slave servers are inconsistent;
-
For example, insert into tb1 value (current_date ());
Row: Based on rows
-
Defect: batch update operations will lead to more operations from the server update tb2 set salary = salary + 1000;
-
Mixed: mixed mode. MySQL determines the appropriate method based on statements or line logs;
Binary log file content format
-
Event Date and time
-
Server id: server_id
-
Event end location: end_log_pos
-
Event Type: such as Query and GTID
-
Thread id when the original server generates this event: thread_id
-
The time difference between the statement timestamp and the binary log file written, exec_time
-
Error Code: error_code
-
Event Location: at #, equivalent to the starting position of the next event
Set parameters:
Og_bin = {ON | OFF | FileName} # global variable, which controls the location of the binary file log_bin_trust_function_creators # global variable, which is only valid when binary logs are enabled, it is used to control whether to prohibit the creation of the storage function SQL _log_bin = {ON | OFF} # session level if an insecure event occurs during the creation of a storage function, used to control whether binary log information is recorded in the log file sync_binlog # sets how often binary logs are synchronized to the disk file. 0 indicates that binary logs are not synchronized, any positive value indicates the number of binary write operations that are synchronized once. binlog_format = {statement | row | mixed} # specify the binary log type max_binlog_cache_size = # the size of the binary log buffer space, the upper limit is determined by max_binlog_stmt_cache_size. max_binlog_size = # The upper limit of the binary log file size, in bytes. the default value is 1 GB. # note: do not place binary logs and data files on the same disk to reduce disk I/O
Relay log: It is actually related to replication. it is almost the same as binary logs, except that it is not used to record events, but is used as the source for reading data and executed locally, of course, the relay log is on the slave server.
Set parameters:
MariaDB [hellodb]> SHOW GLOBAL VARIABLES LIKE '%relay%'; View the parameter information of the relay log
Relay_log = file_name # set the relay log file name relay_log_index = file_name # set the relay log index file name, the default is host_name-relay-bin.indexrelay_log_purge in the data directory = {ON | OFF} # whether to automatically clear relay logs that are no longer needed relay_log_space_limit = # set the available space for storing all relay log files, 0 indicates no limit
Relay_log_recovery: related to automatic recovery of relay logs
Relay_log = {null}: whether to enable relay log
Transaction log:
-
Store the data submitted by the transaction to convert random I/O to sequential I/O;
-
Transaction data submission process: innodb_buffer --> transaction log --> data file;
-
The transaction log file group should contain at least two log files, which are generally stored in the data directory ib_logfile0 and ib_logfile1;
Notes:
-
Use as few transactions as possible to improve the performance of the transaction engine; because the larger the transaction, the larger the rollback sales;
-
Crash Recovery: synchronize committed transactions from transaction logs to data files, and roll back uncommitted transactions to ensure data consistency;
-
Avoid the loss of transaction files due to disk faults: perform data mirroring on the disk where the transaction logs are located;
-
Transaction log files and data files should be separated for storage: to prevent I/O operations on the same disk from being too frequent;
Set parameters: