MySQL log system detailed information sharing

Anyone who has worked on a large system knows that the role of logs should not be underestimated. Often in the later stages of a project, decisions on project optimization and upgrades are made based on logs. So when learning MySQL, the log part must not be missed. The optimizations we discussed in the actual application during the interview were all obtained from the logs. Systematic study of MySQL logs will help us accurately locate problems and improve our work level. In addition, the following series of logs will focus on the operation and maintenance of DBA, systematically understand the configuration of various aspects of MySQL, know yourself and your enemy, and make MySQL a data warehouse that you are familiar with.

1. MySQL log types

By default, all MySQL logs are stored as files in the database root directory:

[root@roverliang data]# pwd
/usr/local/webserver/extend_lib/mysql/data
[root@roverliang data]# ls
auto.cnf ibdata1 ib_logfile0 ib_logfile1 mysql mytest performance_schema roverliang roverliang.err roverliang.pid test

MySQL log types are as follows:

1. Error log (error), information related to the start, run or stop of the MySQL service instance.
2. General query log (general), all SQL statements or MySQL commands run by the MySQL service instance.
3. Binary log (binary), all update statements executed on the database, excluding select and show statements.
4. Slow query log (slow), SQL statements whose execution time exceeds the long_query_time setting value, or SQL statements that do not use indexes.

MySQL Log Cache

In a high-speed, stable and reliable system, cache plays a vital role. MySQL log processing also uses a caching mechanism. MySQL logs are initially stored in the memory of the MySQL server. If the specified storage capacity is exceeded, the logs in the memory are written (or flushed) to the external memory and permanently saved on the hard disk in the form of a database table or file.

3. MySQL error log

The MySQL error log mainly records detailed information about each startup and stop of the MySQL service instance, as well as warnings or error messages generated during the operation of the MySQL instance. Unlike other logs, MySQL error log must be enabled and cannot be disabled.

By default, the error log file name is: hostname.err. However, the error log does not record all error messages. Only critical errors that occur during the operation of the MySQL service instance will be recorded.

mysql> show variables like 'log_error'\G
*************************** 1. row ***************************
Variable_name: log_error
Value: /usr/local/webserver/extend_lib/mysql/data/roverliang.err
1 row in set (0.02 sec)

4. MySQL general log

MySQL general query log records all operations of the MySQL service instance, such as select, update, insert, delete, etc., regardless of whether the operation is successfully executed. There is also information about the MySQL client connecting to and disconnecting from the MySQL server, whether the connection is successful or unsuccessful. There are three parameters related to MySQL general query log.

[]()general_log

mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | OFF |
+---------------+-------+
1 row in set (0.01 sec)

You can enable general query logging by setting @@global.general_log = 1.

mysql> set @@global.general_log = 1;
mysql> show variables like 'general_log';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| general_log | ON |
+---------------+-------+

However, modifying MySQL variables in this way will only take effect while the current MySQL instance is running. Once MySQL is restarted, it will be restored to the default state. The way to make it permanent is to modify the my.cnf file of mysql. Add after the configuration file:

general_log = 1
general_log_file

Once the general query log is enabled, the MySQL service instance will automatically create a general query log file. The general_log_file parameter sets the physical location of the general query log file. as follows:

mysql> show variables like 'general_log_file';
+------------------+-----------------------------------------------------------+
| Variable_name | Value |
+------------------+-----------------------------------------------------------+
| general_log_file | /usr/local/webserver/extend_lib/mysql/data/roverliang.log |
+------------------+-----------------------------------------------------------+

Note: Since the general query log records almost all MySQL operations, for database servers with frequent data access, if the general query log of MySQL is turned on, the performance of the database will be greatly reduced. Therefore, it is recommended to turn off the general query log. Only in special periods, such as when you need to track certain special query logs, can you temporarily open the normal query log.

log_output

The log_output parameter sets the contents of the normal query log and the slow query log to be stored in the database table. You can use set @@global.log_output='table' to store general query logs and slow query logs in the general table and slow_log table in the mysql system database. It is worth noting that the storage engine of these two tables is CSV. After that, SQL statements can be used when viewing new common query log contents.

set @@global.log_output = 'table';
mysql> show variables like 'log_output';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output | TABLE |
+---------------+-------+

5. MySQL slow query log (slow log)

Questions related to slow query logs. During the interview, the interviewer is very fond of talking about these issues. In the past, I could only talk about the MySQL master-slave architecture and optimize MySQL from various aspects, but I didn’t really understand how to enable slow queries and related configurations.

Using MySQL slow query log can effectively track query statements that take too long to execute or do not use indexes. This includes select statements, update statements, delete statements, and insert statements, which help optimize queries. Another difference from the normal query log is that the slow query log only contains successfully executed query statements. There are five parameters related to MySQL slow query log.

1. slow_query_log

slow_query_log sets whether the slow query log is enabled.

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | OFF |
+----------------+-------+

2. slow_query_log_file

Once the slow query log is enabled, the MySQL instance will automatically create a slow query log file. The file specified by slowquerylog_file stores the slow query log content. The modification method is the same as shown above. Go directly to the my.cnf file for editing.

3. long_query_time

long_query_time sets the time threshold for slow queries. The default threshold is 10s.

4. log_quries_not_using_indexes

log_quries_not_using_indexes Whether to log queries that do not use indexes to the slow query log, no matter how fast the query is.

mysql> set @@global.log_queries_not_using_indexes=1;

mysql> show variables like 'log_queries_not_using_indexes';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON |
+-------------------------------+-------+

5. log_output

Set the output format of normal query log and slow query log, with two values: file and table;

6. Viewing MySQL slow query logs

The log_output parameter can set the output format of the slow query log. The default is FILE, which can be set to TABLE;

mysql> desc mysql.slow_log;
+----------------+---------------------+
| Field | Type |
+----------------+---------------------+
| start_time | timestamp |
| user_host | mediumtext |
| query_time | time |
| lock_time | time |
| rows_sent | int(11) |
| rows_examined | int(11) |
| db | varchar(512) |
| last_insert_id | int(11) |
| insert_id | int(11) |
| server_id | int(10) unsigned |
| sql_text | mediumtext |
| thread_id | bigint(21) unsigned |
+----------------+---------------------+

Where: lock_time indicates the time the SQL is blocked by a lock when executing. rows_send indicates the number of rows returned after SQL is executed. rows_examined indicates the number of records actually scanned when the SQL is executed.

However, it is not common to use TABLE to store slow query logs. When the business volume is large, it will affect the main service of the system. We can use FILE to store logs. When you install MySQL, the mysqldumpslow.pl tool is installed by default in the MySQL bin directory to analyze the slow query logs. Using this tool under Windows may require some configuration, which is beyond the scope of this article. To learn about system services, it is better to move to Linux. For commands and tools under Linux, you can use the command itself + the --help option to view the help documentation.

-s indicates the sorting method

Suboptions: c, t, l, r

c : Number of times the SQL statement is executed
t : execution time
l : lock waiting time
r: Return the number of data items
at, al, ar are the average values ​​of the corresponding tlr. -t: means returning the first N records.

-g: Abbreviation for grep. Contains fuzzy matching

Commonly used methods are as follows:

//Return the 20 SQL statements with the highest number of accesses./mysqldumpslow -sc -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

//Return the 20 SQL statements with the largest number of return records./mysqldumpslow -sr -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

//Return SQL statements containing like./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log 

7. Binary log

Binary logs are different from the previously mentioned logs. Binary logs cannot be viewed directly using the cat or less text viewer. Need to use professional tools. The binary log mainly records the changes in the database, so it can be used for synchronization between the master and slave libraries. The content mainly includes all database update operations, use statement, insert statement, delete statement, update statement, create statement, alter statement, and drop statement. To sum it up in a more concise and understandable way: all operations involving data changes must be recorded in the binary log.

Start binary logging Use show variables like 'log_bin'\G to see whether binary logging is enabled.

mysql> show variables like 'log_bin'\G
*************************** 1. row ***************************
Variable_name: log_bin
    Value: OFF
1 row in set (0.00 sec)

mysql> set @@global.log_bin=1;
ERROR 1238 (HY000): Variable 'log_bin' is a read only variable
mysql> 

You can see that log_bin is not enabled by default and is a read-only variable. You need to configure it in my.cnf and then restart MySQL. After restarting MySQL with service mysql restart, you will find a 1.000001 file generated in the data directory. In fact, every time MySQL is restarted, a file like this will be generated in the directory, and the file name will increase in sequence. In addition, MySQL will create a binary log index file in this directory. You can view the location of the index file by running the command show variables like 'log_bin_index'\G, and then use the cat command to view it. You will find that the relative path of the binary file is recorded in it.

You can use the tools that come with MySQL to view the binary log. The specific location is in the bin directory of mysql. Common options for the mysqlbinlog command:

-s Display log content in a concise way
-v Display log content in detailed mode
-d=database name only displays the log content of the specified database
-o=n Ignore the first n lines of MySQL commands in the log
-r=file Write the specified content to the specified file

--start-datetime
Display the log contents within the specified time range
--stop-datetime

--start-position
Display the log content within the specified position interval
--stop-position

Get the current binary log file

mysql> show master status;
+----------+----------+--------------+------------------+-------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+----------+----------+--------------+------------------+-------------------+
| 1.000002 | 120 | | | |
+----------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

Restoring data using binary logs

The syntax is simple:

mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p

mysqlbinlog can be followed by parameters such as --start-datetime, --stop-datetime, start-position, and stop-position.

The two parameters --start-datetime and --stop-datetime can be used to restore data based on time points;

Start-position and stop-position can be used to recover data at more detailed operation points;

MySQL binary log related parameters

mysql> show variables like '%binlog%';
+-----------------------------------------+----------------------+
| Variable_name | Value |
+-----------------------------------------+----------------------+
| binlog_cache_size | 32768 |
| binlog_checksum | CRC32 |
| binlog_direct_non_transactional_updates | OFF |
| binlog_error_action | IGNORE_ERROR |
| binlog_format | STATEMENT |
| binlog_gtid_simple_recovery | OFF |
| binlog_max_flush_queue_time | 0 |
| binlog_order_commits | ON |
| binlog_row_image | FULL |
| binlog_rows_query_log_events | OFF |
| binlog_stmt_cache_size | 32768 |
| binlogging_impossible_mode | IGNORE_ERROR |
| innodb_api_enable_binlog | OFF |
| innodb_locks_unsafe_for_binlog | OFF |
| max_binlog_cache_size | 18446744073709547520 |
| max_binlog_size | 1073741824 |
| max_binlog_stmt_cache_size | 18446744073709547520 |
| simplified_binlog_gtid_recovery | OFF |
| sync_binlog | 0 |
+-----------------------------------------+----------------------+

max_binlog_size

maxbinlogsize The size of a single binary log file. If the value is exceeded, a new file will be generated with the suffix +1;

binlog_cache_size

binlogcachesize The size of the cache used to store binary logs in memory

sync_binlog

sync_binlog writes binary logs to the cache several times and starts to synchronously refresh them to the external memory (hard disk).

log_slave_updates

logslvaeupdates for master-slave replication

Binary log cleaning

In principle, the logs to be cleaned should be backed up physically to other storage devices for permanent storage. Then we recommend the following two less risky cleaning methods:

The first one:

purge master logs before '2017-02-16 00:00:00';

Second type:

Directly set the expire_logs_days parameter in the MySQL configuration file my.cnf to set the expiration days for binary files. Expired binary files will be automatically deleted. It is recommended to start another periodic scheduled task before deleting to regularly back up binary tasks. This is to prevent some data from being found to be wrong several days later and the binary log being automatically deleted.

expire_logs_days=90

8. InnoDB transaction log

MySQL will make maximum use of cache to improve data access efficiency. In other words, any high-performance system must use cache, and cache plays a huge role at all levels. Let's summarize it from a higher level: caching and queues are the only way to achieve high performance. This is a very tricky problem for the database. To ensure more efficient reading and storage of data, cache must be used. However, to ensure data consistency, it is necessary to ensure that all data must be stored in the database accurately and even if an accident occurs, the data must be recoverable. We know that InnoDB is a transaction-safe storage engine, and consistency is an important feature of transaction ACID. The InnoDB storage engine mainly achieves data consistency through the InnoDB transaction log. The InnoDB transaction log includes the redo log and the rollback log.

The InnoDB transaction log is different from the log mentioned above. The InnoDB transaction log is maintained by the InnoDB storage engine itself, and its content cannot be read by the database administrator.

Redo log (redo)

The redo log mainly records all completed transactions, that is, the logs that have been committed. By default, the values ​​of the redo logs are recorded in the iblogfile0 and iblogfile1 redo logs.

[root@roverliang data]# pwd
/usr/local/webserver/mysql/data
[root@roverliang data]# ls ib*
ibdata1 ib_logfile0 ib_logfile1

Rollback log (undo)

The rollback log mainly records the unfinished transactions that have been partially completed and written to the hard disk. By default, the rollback log information is recorded in the tablespace file, the shared tablespace file ibdata1 or the exclusive tablespace file ibd.

From the above figure we can see that the rollback log is recorded in ibdta1 by default. My MySQL system version is: 5.6.24.

Checkpoint Mechanism

After the MySQL server crashes, when the MySQL service is restarted, due to the existence of redo logs (redo) and rollback logs (undo), InnoDB rolls back all incomplete transactions that have been partially completed and written to the hard disk through the rollback log (undo). Then re-execute all transactions in the redo log to recover all data. However, the amount of data is too large, so InnoDB introduced the Checkpoint mechanism to shorten the recovery time.

Dirty page

When a transaction needs to modify a record, InnoDB first reads the data block where the data is located from the external memory to the hard disk. After the transaction is committed, InnoDB modifies the record in the data page. At this time, the cached data page is no longer the same as the data block in the external memory. The data page in the cache is called a dirty page. The dirty page is refreshed to the external memory and becomes a clean page.

Note: A memory page defaults to 4K, or a multiple of 4K. You can think of the memory as a book that can be wiped clean. Every time MySQL reads data, it requests a few clean pages from the memory and then writes on them. When the data is refreshed to the hard disk, the data pages are immediately erased and made available for use by other programs.

Log sequence number

The log sequence number (LSN) is the end point of each log in the log space, expressed as a byte offset, and is used during checkpoint and recovery.

The principle of the Checkpoint mechanism assumes that at a certain point in time, all dirty pages are refreshed to the hard disk, and all redo logs before this point in time do not need to be redone. The system uses the end position of the redo log at this point in time as the Checkpoint, and the redo logs before the Checkpoint do not need to be redone and can be safely deleted. In order to better utilize the redo log space, InnoDb uses a round-robin strategy to use the redo log space, so InnoDB has at least 2 redo log files. Through the Checkpoint mechanism, the transactions that have been completed but not yet fully written to the external memory when the database crashed can be redone (undo) through the redo log, thereby ensuring data consistency and shortening the recovery time.

InnoDB redo log (redo) parameters

innodb_log_buffer_size: Sets the size of the redo log cache.
innodb_log_files_in_group: Sets the number of redo logs in the log file group.
innodb_log_file_size: Sets the size of the redo log file. The larger the file, the longer it takes to recover.
innodb_mirrored_log_groups: The number of redo log mirror file groups. This can only be set to 1.
innodb_log_group_home_dir: Set the directory where the log file group is stored. The default directory is the database root directory.

InnoDB rollback log (undo) parameters

innodb_undo_directory: Set the directory where rollback logs are stored.
innodb_undo_logs: Set the rollback segment size of the rollback log, the default is 128k
innodb_undo_tablespace: sets the number of rollback log files that the rollback log consists of. The default value is 0.
Warning Special attention: After installing MySQL, you need to set the rollback log parameters in my.cnf. If you set the rollback log parameters after creating the database, MySQL will report an error, and after the rollback log is created, it cannot be modified or added again.

9. Log file backup

When backing up, you can use flush logs to close all current log files and then generate new log files. After closing the log file, you can back it up physically. In addition, flush logs can add specific log types:

flush error logs
flush general logs
flush binary logs
flush slow logs