MYSQL/MARIADB's Log

The logs in MYSQL/MARIADB are broadly divided into the following categories:

Query Log

General Query log:

Slow query log:

error Log

Binary log

Relay Log

Transaction log

A brief introduction to these kinds of logs, logs for our analysis of MySQL service has a very important help;

Query log:

General Query log: The default is closed, because all the query statements will be recorded;

MariaDB [(None)]> SELECT @ @general_log; +---------------+|             @ @general_log |+---------------+| 0 |+---------------+

Slow query log: Default off, recommended to open, in SQL statement tuning and MySQL server performance analysis has a certain reference significance;

It refers to the query time which exceeds the time set by the server parameter long_query_time, but the time that the query acquires the lock is not counted in the query time evaluation;

MariaDB [(None)]> show global variables like ' long_query_time '; +-----------------+-----------+| variable_name | Value |+-----------------+-----------+| Long_query_time | 10.000000 |+-----------------+-----------+

Slow query log can be stored in two forms, but also by the Log_output server parameters control;

1. Stored in a file system in the form of a document;

2. In the form of a system table stored in the Mysql.slow_log table;

function switch for slow query log:

log_slow_queries {on | OFF}

Slow_query_log {on | OFF}

Slow query log function switch, if the two switches are in, then modify one of the values, the other value also changed;

MariaDB [(None)]> SELECT @ @log_slow_queries; +--------------------+|                  @ @log_slow_queries |+--------------------+| 0 |+--------------------+1 row in Set (0.00 sec) MariaDB [(None)]> SELECT @ @slow_query_log; +------------------+|                @ @slow_query_log |+------------------+| 0 |+------------------+

Log_slow_rate_limit = N Slow query log recording frequency;

log_slow_verbosity {on | OFF} whether the function switch of slow query log is recorded in detail;

Slow_query_log_file/path

This server parameter is valid only if the value of the Log_output server parameter is file, and its value is the storage path and file name of the slow query log, the default path is the Data directory (DATADIR), and the file name is: Hostname-slow.log;

log_queries_not_using_indexes {on | OFF}

For those slow queries that do not use indexes, the function switch is recorded in the slow query log; The default value is off;

To test a slow query:

MariaDB [(None)]> Select sleep (+-----------+|         Sleep (|+)-----------+| 0 |+-----------+

The MySQL client tool mysqldumpslow can help analyze the slow query log;

Common options:

-A: In the collation is not using n instead of the number, do not use s instead of the string;

--debug,-D: Output debugging information, more convenient log analysis;

-T N: Displays only the first N slow query results in the slow query log;

--verbose,-V: Displays detailed information;

-G pattern: Selects the SELECT statement through grep;

[[email protected] ~]# mysqldumpslow-a-vreading mysql slow query log from/var/lib/mysql/master-slow.logcount:1 time=1 0.00s (10s) lock=0.00s (0s) rows_sent=1.0 (1), rows_examined=0.0 (0), Root[root] @localhost Select Sleep (10)

Error log:

The contents of the information can be recorded

1.mysqld|mysqld_safe the information that is output during the process of starting or shutting down the program;

2.MYSQLD error messages generated during the operation of the service;

3. Information generated by the time scheduler at runtime;

4. In the MySQL replication model of the master-slave architecture, the information generated when the replication thread of the slave server starts;

5. The information of the "Warning" class can be recorded in the Log_warnings server parameters.

MariaDB [(None)]> SELECT @ @log_error; +------------------------------+| @ @log_error |+------------------------------+| /var/log/mariadb/mariadb.log |+------------------------------+

Server variable parameters that are first closed with the error log;

Log_error/var/log/mariadb/mariadb.log

Turn On error logging and define the file path and filename of the logging error log;

Log_warnings 1

Whether the information in the "Warning" class generated during the mysqld operation is recorded in the error log file as a function switch;

Binary log:

Binary logs contain information about events that cause or may cause data changes: for example:

Insert,update,delete

The update and DELETE statements do not have matching rows at execution time according to the specified conditions;

Create,alter,drop

However, query statements such as Select or show will never be recorded in the binary log;

The "event" in the binary log contains the result of the execution of the SQL statement or SQL statement that caused the data to change or may be causing the data to change, or it may be a mixture of the two;

1. If the SQL statement is logged, the amount of data recorded in the log is small, potentially inconsistent results, such as the execution of time-related functions;

2. If you record the result of the SQL statement, you can save the data result set accurately, but it may cause the data volume to be too large;

In the end how to record the binary log content, by a server parameter to determine;

Binlog_format STATEMENT

The value of the variable:

STATEMENT: Log binary logs as SQL statements;

Row: Logs the binary log with information about the data row that was sent after the SQL statement was executed;

MIXED: Mixed mode, statement and row two modes are supported, but when an event is recorded, only one format can be selected for recording, and MySQL decides exactly what format to use;

MariaDB [(None)]> show global variables like ' binlog_format '; +---------------+-----------+| variable_name | Value |+---------------+-----------+| Binlog_format | STATEMENT |+---------------+-----------+

The function of binary log: Data replay;

1. master-slave replication

2. Incremental backup for data;

MySQL or mariadb default does not have the ability to open binary log records, if you want to open;

1. Modify the server parameters:

SET @ @GLOBAL. logbin={on | OFF | PATH}

2. Modify the configuration file:

In the main profile/etc/my.cnf, add a row

Log_bin=/path/to/binlog

Attention:

1) MySQL or mariadb 5.5+, do not support on or off switch value, only support the path;

2) The path of the binary log file, in principle, should not be stored on the same storage device as the database;

You can view the list of binaries in MySQL's interactive mode to view the log files currently in use;

mariadb [(None)]> show master logs;+---------------+-----------+| log_name       | file_size |+---------------+-----------+| binlog.000001 |       3478 | |  binlog.000002 |     33156 | |  binlog.000003 |      1568 | |  binlog.000004 |       264 | |  binlog.000005 |       264 | |  binlog.000006 |       245 |+---------------+-----------+ mariadb [(None)]> show master status;+---------------+----------+--------------+------- -----------+| file          | position |  binlog_do_db | binlog_ignore_db |+---------------+----------+--------------+------------- -----+| binlog.000006 |      245 |               |                   |+---------------+----------+--------------+------------ ------+

To view the time content in a specified binary file:

MariaDB [(None)]> show Binlog events in ' binlog.000006 ' \g*************************** 1. Row *************************** log_name:binlog.000006 pos:4 event_type:format_desc server_id:101end_log_pos : 245 info:server ver:5.5.56-mariadb, Binlog ver:4

Scrolling of the binary log:

Automatic scrolling: Log scrolling by MySQL self-control;

The maximum size of a single binary log file controlled by a server parameter;

MariaDB [(None)]> show global variables like ' max_binlog_size '; +-----------------+------------+| variable_name | Value |+-----------------+------------+| Max_binlog_size | 1073741824 |+-----------------+------------+

Note: Because the binary log is saved after the transaction is committed and each transaction must reside in a binary log file, the commit of some large transaction may make the size of the binary log file exceed the value of this upper limit;

Manual scrolling: User-controlled log scrolling;

1. Restart the MySQL service;

2. Execute the "FLUSH LOGS" statement in the interactive mode of the MySQL client;

Other important parameter information related to the binary log;

Sql_log_bin = {on | OFF}

Specifies whether the binary logging feature is enabled, only if the value of the Log_bin parameter is on;

Sync_binlog = N

Sync_binlog=0: Asynchronous update;

MySQL does not choose to synchronize the cached information to the disk file, when the binary log in the cache is written to disk by the file system, the performance of MySQL is very good, but it does not guarantee the consistency and persistence of the data;

Sync_binlog=n; Every event that writes N binary logs (not the number of transactions), MySQL performs a disk synchronization instruction Fdatasync () writes the binary log brush in the cache to the disk log file, and in order to guarantee the persistence of the data set, it is usually used SYNC_ Binlog=1, especially in Mysql/mariadb's master-slave replication architecture;

Approximate content format for binary logs:

Relay LOG:

Its content is the content of binary log;

In the master-slave MySQL Cluster service, from the server will copy the binary log from the primary server to the local, save it in the relay log;

The binary log format is in binary format, and the file format of the trunk log is in plain text format;

Relevant parameters related to the trunk log:

Relay_log = {on | OFF | PATH}

The relay log function switch, which defines the path and file name of the relay log, and if this parameter value is empty, the default is to place the relay log in the data directory, the file name defaults to: hostname-relay-bin.xxxxxx;

Note: It is often necessary to use the trunk log to define the path of the file directly, without using the default value, to prevent the location of the log file from being located after the hostname has been changed;

Relay_log_recovery = {on | OFF}

When the recovery from the service from the outage, if the trunk log corruption, resulting in a portion of the log statements can not be replayed or all can not be replayed, at this time, whether to automatically discard all the contents of the trunk log is not executed, and from the primary server to regain the binary log function switch;

Transaction log:

In MySQL or mariadb system, generally refers to the InnoDB transaction log, contains two kinds of logs;

Redo Log

Provide data redo function to realize roll forward operation;

is usually a physical log, which is the physical modification of a data page, not the result of a modification to a row or rows;

If you use redo log to recover data, you can revert to the location after the last commit transaction;

Undo Log

Provides data recovery capabilities for rollback operations:

It provides recovery operation and MVCC function; the contents of undo log are the contents of the corresponding row before being modified, or the SQL statement opposite to the modified data;

The undo log is usually a logical log, which is recorded according to the data modifications in each row, and is typically stored in a logical table space;

Note: The execution of undo log does not redo the inverse process of log, in fact, two kinds of logs are used to implement the recovery function of the log;

Whether the first part of redo log can be persisted, MySQL is controlled by innodb_flush_log_at_trx_commit server parameters;

Innodb_flsuh_log_at_trx_commmit = N is 2 by default;

If the parameter is 1, each commit of the transaction writes the log in the log cache to the operating system's buffer and immediately calls Fsync () to write to "log file on disk", which will lose any data even if the system crashes directly, and the durability of the data can be guaranteed. But every commit transaction needs to be written to disk, the IO performance is very poor;

If the parameter is 0, after the transaction commits, the log in the transaction log cache buffer is not written to the operating system buffer, submitted to the operating system buffer once per second, and the Fsync () brush is written to "log file on disk", and if the system crashes, all data within one second is lost;

If the value of this parameter is 2, each commit transaction is written directly to the operating system's cache, and then the Fsync () function is called by the operating system every second to write the log contents of the operating system buffer to "log file on disk", and if the MySQL service crashes, no data is lost, but if the operating system crashes , all data in one second is lost;

MariaDB [(None)]> show global variables like ' innodb_flush_log_at_trx_commit '; +--------------------------------+- ------+| variable_name | Value |+--------------------------------+-------+| Innodb_flush_log_at_trx_commit | 2 |+--------------------------------+-------+

Undo Log is useful:

1. provide rollback function;

2. Multi-version parallel control (MVCC)

When the data was modified, InnoDB not only recorded the redo log, but also recorded the undo log;

Therefore, it is possible to rollback with undo log, since some operations failed or because some operations were not justified during the execution of the transaction after it was opened;

Undo log differs from redo log in the form of a logical log. stored in the table space;

The undo log is stored by default in the shared Tablespace, and if the innodb_file_per_table server parameter is enabled, it means that the undo log associated with the table is saved in a separate table space for each table;

MariaDB [(None)]> show global variables like ' innodb_file_per_table '; +-----------------------+-------+| variable_name | Value |+-----------------------+-------+| innodb_file_per_table | On |+-----------------------+-------+

MYSQL/MARIADB's Log