MySQL Log-related content

This article describes the MySQL backup and log, because the backup needs to use the log, so before the backup, if the log content is too long, will be the log and backup separately, the first simple introduction of MySQL log related content.

MySQL Log

Logs are an important part of the MySQL database. The log file records the changes that occurred during the MySQL database operation, which is used to record the client connection status of the MySQL database, the execution of the SQL statement, and the error message. When the database is accidentally damaged, you can view the cause of the file error through the log, and you can use the log file for data recovery.

MySQL mainly includes: error log, query log, slow query log, transaction log, binary log, relay log; More important logs have error logs, slow query logs, and binary logs. Here is a brief description of these kinds of log content:

Error log

The error log is one of the important logs of MySQL, which records information about when MySQL starts and stops, and any critical errors that occur during the operation of the server. You can view the error log first when any failure in the database results in an inability to work properly.
The definition of the error log is defined in MY.CNF:

[mysqld_safe]log-error=/var/log/mariadb/mariadb.log

If the error log is not defined in my.cnf, then mysqld uses HOST_NAME.ERR (hostname name) and is stored by default in the DataDir defined directory.

Slow query log

The slow query log records the value of all execution times over the parameter long_query_time (in seconds), the default time for Long_query_time is 10 seconds, the minimum is 0, and the precision can be subtle.

MariaDB [(none)]> show variables like 'long_query_time';+-----------------+-----------+| Variable_name   | Value     |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.00 sec)

The slow query log is not turned on by default: see if Slow queries are enabled:

MariaDB [(none)]> show variables like 'slow%';+---------------------+---------------------------------+| Variable_name       | Value                           |+---------------------+---------------------------------+| slow_launch_time    | 2                               || slow_query_log      | ON                              || slow_query_log_file | /var/log/mariadb/mysql_slow.log |+---------------------+---------------------------------+3 rows in set (0.00 sec)

Enable slow Query
Slow_query_log is a global variable that supports command settings to enable or disable, it is important to note that the terminal usage instruction setting does not take effect permanently:

MariaDB [(none)]> set global slow_query_log=ON | OFF;

To be permanently effective, you need to define it in the configuration file:

[mysqld]slow_query_log=on

Takes effect after restarting the service. View on Status:

MariaDB [(none)]> show variables like 'slow_query_log';+----------------+-------+| Variable_name  | Value |+----------------+-------+| slow_query_log | ON    |+----------------+-------+1 row in set (0.00 sec)

At the same time, when the slow query is turned on, it is named Host_name_slow.log by default in writing the log to the DataDir directory, and of course, you can use Slow_query_log_file=/path/filename to define a slow query log storage path. The following is a custom log path:

[mysqld]slow_query_log=onslow_query_log_file=/var/log/mariadb/mysql_slow.log

Restart the service to see if/var/log/mariadb/mysql_slow.log is generating

Systemctl Restart MARIADB

[[email protected] mysql]# ls /var/log/mariadb/mariadb.log  mariadb.log.rpmsave  mysql_slow.log

Setting the Long_query_time threshold

The above mentioned that as long as the query time exceeds the Long_query_log set value will be recorded in the slow query log, so this threshold depends on the actual application environment.
Set Long_query_time threshold:

MariaDB [(none)]> set long_query_time=1;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show variables like 'long_query_time';+-----------------+----------+| Variable_name   | Value    |+-----------------+----------+| long_query_time | 1.000000 |+-----------------+----------+1 row in set (0.00 sec)

To test the slow query log, set the query time to one second

MariaDB [db4]> select sleep(2), id from tb1 where id = 55;+----------+------+| sleep(2) | id   |+----------+------+|        0 |   55 |+----------+------+1 row in set (3.90 sec)   #查询时间3.9秒MariaDB [db4]> select id from tb1 where id = 55;+------+| id   |+------+|   55 |+------+1 row in set (0.00 sec)

View Slow query log

# [email protected]: root[root] @ localhost []# Thread_id: 5  Schema: db4  QC_hit: No# Query_time: 3.898127  Lock_time: 1.889486  Rows_sent: 1  Rows_examined: 49SET timestamp=1528775278;select sleep(2), id from tb1 where id = 55;

As you can see, queries with queries longer than 1 seconds are recorded in the log, and the last query is not recorded.

The official Mysqldumpshow Slow query analysis tool implements the following functions

• Statistics for different slow SQL
• Number of occurrences (count),
• The maximum time to execute,
• Total time Spent,
• Time to wait for the lock (lock),
• The total number of rows sent to the client (rows),
• Total number of rows scanned (rows),

Other definition parameters

Log_slow_filter:

Log_slow_filter is a filtering mechanism for slow queries, and operations defined in Log_slow_filter will be logged to the slow query log if the execution time exceeds the threshold defined by the slow query.

Log_queries_not_using_indexes
Log_queries_not_using_indexes is the definition of whether the record is not using an index query statement, and the default is off

MariaDB [db4]> show variables like 'log_queries_not_using_indexes';+-------------------------------+-------+| Variable_name                 | Value |+-------------------------------+-------+| log_queries_not_using_indexes | OFF   |+-------------------------------+-------+1 row in set (0.00 sec)

Enable this parameter:

MariaDB [db4]> set global log_queries_not_using_indexes=on;Query OK, 0 rows affected (0.00 sec)

Log_slow_rate_limit = 1

Log_slow_rate_limit defines how many times a query is logged in the log

View Slow Query status

MariaDB [db4]> Show variables like '%slow% '; +---------------------+--------------------------------------------- -----------------------------------------------------------------+| variable_name | Value |+----------- ----------+---------------------------------------------------------------------------------------------------- ----------+| Log_slow_filter | Admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk | | log_slow_queries | On | | Log_slow_rate_limit | 1 | |                                                                                                              log_slow_verbosity | || slow_launch_tIME | 2 | | Slow_query_log | On | | Slow_query_log_file | /var/log/mariadb/mysql_slow.log |+----------- ----------+---------------------------------------------------------------------------------------------------- ----------+7 rows in Set (0.00 sec)

Binary log

The binary log (Bin-log) records all DDL (data definition Statements) and DML (data manipulation statements), and the operations that alter the database structure and content are recorded in the binary log.

Location format of the log
The Bin-log option is defined in the configuration file to enable binary logging, Mysqld begins to write data changes to the log file, and if no log file name is defined, the system defaults to the host name followed by the '- Bin ', if you specify a file name that does not specify a log storage path, it is stored in the DataDir directory by default.

[mysqld]log_bin=master-log

See if Bin-log is enabled

MariaDB [(none)]> show variables like '%log_bin';+---------------+-------+| Variable_name | Value |+---------------+-------+| log_bin       | ON    || sql_log_bin   | ON    |+---------------+-------+2 rows in set (0.00 sec)

Log_bin and Sql_log_bin to be enabled at the same time, Sql_log_bin on by default, log_bin default to OFF, you need to define the location and name of the store.

Bin-log Log Record format

Binlog_format=statement| row| MIXED: Binary logging format, default is statement

The Row:bin-log is recorded as "each row of data is modified" and then the same data is modified at the slave end.

Advantage: In row level mode, binnary log can not record the context-sensitive information of the query statement executed, as long as it records which row has been modified and what it looks like. The row level will detail the details of the changes in each row of data, with no stored procedures, or function, in a particular case, and trigger calls and triggers failing to replicate correctly.

Disadvantage: All executed statements when recorded in the log, will be recorded in each row of changes to record, which may produce a large number of log content, such as an UPDATE statement, modify multiple records, then binlog each modification will have a record, so that the Binlog log volume will be very large, In particular, when executing a statement such as ALTER TABLE, each record in the table is recorded in the log because of changes in its structure.

statment: "Every SQL statement that will be modified" is recorded in the binnary of master. When the slave is copied, the SQL thread parses the Sing Woo original master and executes the same SQL statement.

Advantages: First, solve the disadvantage of row level, do not need to record each row of data changes, reduce the binnary log volume, saving IO cost, improve performance. (compared to how much performance and log volume The row can save, depending on the SQL case of the application, the log volume generated by the normal record modification or the insertion of the row format is less than the amount of log generated by statement, but given the conditional update operation and the whole table deletion, ALTER TABLE operations, the row format generates a large number of logs, so the amount of log generated will increase, as well as the IO performance issues, when considering whether to use the row format log should be followed according to the actual application. )

Disadvantage: Because it is the execution statement of the record, in order for these statements to be executed correctly on the slave side. Then it must also record some information about the execution of each statement, that is, contextual information, to ensure that all statements are executed at the slave end and are executed at the same time as the master side of the results.

Mixed: In Mixed mode, it is a mix of the above two levels.
Mixed mixed with statment and row two logs, by default it is recorded in statment format, but in some specific cases using row to record better and more reasonable, Mixed, you can let the system to decide on the basis of which way, this is the better place, However, in the master-slave environment, data inconsistency between master and slave may occur.
MySQL default is to use the statement log format, the recommended use of mixed.

View the default Bin-log record format

MariaDB [(none)]> show variables like 'binlog_format';+---------------+-----------+| Variable_name | Value     |+---------------+-----------+| binlog_format | STATEMENT |+---------------+-----------+1 row in set (0.00 sec)

Binary log Correlation definition parameters

After the log_bin=master-log is defined in the configuration file, a file named master-log.000001 is generated in the database data store directory, which is the binary log file, and the Master-log.index file is the index log file.

Some related parameter definitions

• Sql_log_bin= on | OFF: Whether to log binary logs, default on
• Log_bin=/path/bin_log_file: Specifies the file location; default off, which means that binary logging is not enabled, both of the above are turned on to

• Binlog_format=statement| row| MIXED: The format of the binary logging, default statement
  The above parameters have been mentioned above

• max_binlog_size=1073741824; maximum volume of a single binary log file, reaching the maximum will automatically scroll, default to 1G

View the size definition of the current binary log file

MariaDB [(none)]> show variables like 'max_binlog_size';+-----------------+------------+| Variable_name   | Value      |+-----------------+------------+| max_binlog_size | 1073741824 |  #1G+-----------------+------------+1 row in set (0.00 sec)

• Sync_binlog=1|0; Set whether to start the binary Log Instant Sync disk feature, default 0, by the operating
  Synchronizing logs to disk

  The Sync_binlog parameter is a more important parameter, and in MySQL replication, the replication reliability of the slave node can be increased. The 1 should be enabled in master-slave replication

MariaDB [(none)]> set global sync_binlog=1;Query OK, 0 rows affected (0.00 sec)MariaDB [(none)]> show variables like 'sync_binlog';+---------------+-------+| Variable_name | Value |+---------------+-------+| sync_binlog   | 1     |+---------------+-------+1 row in set (0.00 sec)

• Expire_logs_days=n: The number of days that binary logs can be automatically deleted. The default is 0, which is not automatically
  Delete

MariaDB [(none)]> show variables like 'expire_logs_days';+------------------+-------+| Variable_name    | Value |+------------------+-------+| expire_logs_days | 0     |+------------------+-------+1 row in set (0.00 sec)

MySQL binlog log format can be specified via MySQL's my.cnf. as follows:

log_bin=master-bin          #binlog日志名　binlog_format = MIXED       # binlog日志格式expire_logs_days    = 7     #binlog过期清理时间max_binlog_size    100m     #binlog每个日志文件大小sync_binlog=1               #启用bin-log同步功能

Binary log Read-mysqlbinlog

Because the logs are stored in binary mode, they cannot be read directly and need to be viewed using the Mysqlbinlog tool.

Shell> Mysqlbinlog Log-file

Mysqlbinlog Commands Common options:

-d  dbname:  指定数据库名称-o  #:   忽略掉日志中的前#个命令-r ： 将输出的文件格式日志输出到指定文件，重定向-s ： 简单格式显示，忽略掉一些信息--set-charset=charname : 在输出文件时加上set name charname ，装载数据时有用--start-datetime= #  ： 指定从开始时间显示内容--stop-datetime= #  ： 指定内容到结束的时间点--start-position= #  ：指定开始的pos值位置--stop-position= #  ： 指定到结束的pos值位置

Test creates a blank view of the contents of the binary log record

 MariaDB [db5]> create table tb1 (    -> id int,    -> name varchar(20)    -> ) charset=utf8;Query OK, 0 rows affected (0.33 sec)

View Bin-log

  [[email protected] mysql]# mysqlbinlog master-bin.000006# at 245#180612 16:26:22 Server ID 1 end_log_pos 370 Query thread_id=4 exec_time=0 #该行记录了当前最新的pos值, using show Master Status View: Error_code=0use ' DB5 '/*!*/; SET timestamp=1528791982/*!*/; SET @ @session. pseudo_thread_id=4/*!*/; SET @ @session. Foreign_key_checks=1, @ @session. sql_auto_is_null=0, @ @session. Unique_checks=1, @ @session. autocommit= 1/*!*/; SET @ @session. sql_mode=0/*!*/; SET @ @session. auto_increment_increment=2, @ @session. auto_increment_offset=1/*!*/;/*!\c UTF8 *//*!*/; SET @ @session. character_set_client=33,@ @session. collation_connection=33,@ @session. collation_server=8/*!*/; SET @ @session. lc_time_names=0/*!*/; SET @ @session. collation_database=default/*!*/;create table tb1 (ID int,name varchar) charset=utf8/*!*/;D elimiter; # End of Log filerollback/* Added by Mysqlbinlog */;/*!50003 Set [email protected]_completion_type*/;/*!50530 set @@ SESSION. pseudo_slave_mode=0*/;  

 MariaDB [db5]> show master status;+-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000006 |      370 |              |                  |+-------------------+----------+--------------+------------------+1 row in set (0.00 sec)

You can also use the specified POS value to view

[email protected] mysql]# mysqlbinlog--start-position=245--stop-position=370 master-bin.000006

Deletion of binary logs

In a busy system, a large number of log files are generated every day, and it is necessary to define a backup log file and clean up the log if the time is long without cleaning up the disk space.
How to delete a log

• Method 1

Perform reset master; Instructions

This command will delete all Bin-log logs, starting with the new log number 000001.

• Method 2

Execute purge Master logs to ' master-bin.****** ', executing this command will delete all Bin-log log files before the number specified.

MariaDB [db5]> purge master logs to 'master-bin.000006';Query OK, 0 rows affected (0.81 sec)

Only master-bin.000006 later logs are retained

There are so many important logs to compare, and the backup content of MySQL is explained later.

MySQL Log-related content