English | 简体中文 | 繁體中文 | Русский язык | Français | Español | Português | Deutsch | 日本語 | 한국어 | Italiano | بالعربية
Those who have worked on large systems know that the role of logs should not be underestimated. Often, in the later stages of a project, decisions for optimization and upgrade are made based on logs. Therefore, when studying MySQL, the log part certainly cannot be missed. The optimizations we discuss in interviews are all derived from logs. A systematic study of MySQL logs helps us accurately locate problems and improve our work level. In addition, the following series of logs will focus on the operation and maintenance aspects of DBAs, systematically understand the configuration of various aspects of MySQL, know ourselves and our competitors, and make MySQL an easy-to-use data warehouse.
One, the types of MySQL logs
By default, all MySQL logs are stored in the file form in the root directory of the database:
[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
The types of MySQL logs are as follows:
1. Error log (error), information related to the start, operation, or stop of the MySQL service instance.
2. General query log (general), all SQL statements or MySQL commands executed 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 that take longer than the value set by long_query_time, or SQL statements that do not use an index.
Two, MySQL log cache
A high-speed, stable, and reliable system, the cache in it must play a crucial role. MySQL log processing also uses a caching mechanism. The MySQL log is initially stored in the memory of the MySQL server, and if it exceeds the specified storage capacity, the log in memory is written (or flushed) to external storage, permanently stored on the hard disk in the form of a database table or a file.
Three, MySQL error log (error log)
The MySQL error log mainly records the detailed information of each start and stop of the MySQL service instance, as well as warnings or error information generated during the operation of the MySQL instance. Unlike other logs, the MySQL error log must be enabled and cannot be disabled.
By default, the file name of the error log is: hostname.err. However, the error log does not record all error information; 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)
Four, MySQL general query log (general log)
MySQL general query log records all operations of the MySQL service instance, such as select, update, insert, delete, and so on, regardless of whether the operation is executed successfully. It also includes relevant information about the connection and disconnection of the MySQL client and MySQL server, regardless of whether the connection is successful or not. There are three parameters related to the 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)
It can be enabled through set @@global.general_log = 1 to enable the general query log.
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 during the operation of the current MySQL instance, and will be restored to the default state after MySQL restart. The permanent effective way is to modify the my.cnf file of MySQL. Add the following to 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, and 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 almost records all operations of MySQL, for database servers with frequent data access, enabling the general query log of MySQL will significantly reduce the performance of the database. Therefore, it is recommended to disable the general query log. Only in special periods, such as when it is necessary to trace some special query logs, can the general query log be temporarily opened.
log_output
The log_output parameter sets the content of the general query log and slow query log to be stored in a database table. It can be used to store the general query log and slow query log in the general table and slow_log table of the MySQL system database by setting set @@global.log_output='table'. It is worth noting that the storage engine of these two tables is CSV, and SQL statements can be used to view the new content of the general query log after that;
set @@global.log_output = 'table'; mysql> show variables like 'log_output'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | log_output | TABLE | +---------------+-------+
Fifth, MySQL Slow Query Log (slow log)
Questions related to slow query logs are very favored by interviewers. In the past, one could only talk about MySQL master-slave architecture and optimize MySQL from various aspects, but there was no real understanding of how to enable slow queries and related configurations.
Using MySQL slow query logs can effectively track query statements that take too long to execute or do not use indexes. This includes select statements, update statements, delete statements, as well as insert statements, providing help for optimizing queries. Another difference from the normal query log is that the slow query log only contains query statements that have been successfully executed. Parameters related to MySQL slow query logs include5个.
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 content of the slow query log is stored in the file specified by slow_query_log_file. The modification method is consistent with the above. Directly edit the my.cnf file.
3、long_query_time
long_query_time sets the threshold for slow queries. The default threshold is10s.
4、log_quries_not_using_indexes
log_quries_not_using_indexes determines whether to record query statements not using indexes in the slow query log, regardless of the query speed.
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
The output format of the general query log and slow query log has been set, with two values: file, table;
Sixth, View MySQL Slow Query Log
The log_output parameter can be set to 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 | +----------------+---------------------+
Among them: lock_time indicates the time the SQL execution is blocked by the lock. rows_send indicates the number of rows returned after executing the SQL. rows_examined indicates the number of records actually scanned during the SQL execution.
However, using TABLE to store slow query logs is not common, and in the case of high business volume, it may affect the main service of the system. We can use the FILE method for log storage. When installing MySQL, the mysqldumpslow.pl tool for slow query log analysis is already installed by default in the MySQL bin directory. Using this tool on Windows may require some configuration, which is not within the scope of this article. If you want to learn about system services, it's better to move to Linux. Commands and tools under Linux can all use the command itself. + --Use the 'help' option to view the help document.
-s indicates the sorting method
Suboptions: c, t, l, r
c: Number of times SQL is executed
t: Execution time
l: Lock wait time
r: Returns the number of data records
at, al, ar are the averages corresponding to t, l, r respectively. -t: Represents returning the first N records.
-g: grep abbreviation. Contains fuzzy matching
Common usage is as follows:
//Returns the one with the most access times20 SQL statements ./mysqldumpslow -s c -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //Returns the one with the most 'return' records20 SQL statements ./mysqldumpslow -s r -t 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log //Returns SQL statements containing 'like' ./mysqldumpslow -g 'like' 20 /usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log
7. Binary Log (binary)
The binary log is different from the several types of logs mentioned earlier. The binary log cannot be directly viewed with 'cat' or 'less' text viewer. Professional tools are needed. The binary log mainly records the changes in the database and can be used for master-slave synchronization. The content mainly includes all update operations of the database, such as 'use' statements, 'insert' statements, 'delete' statements, 'update' statements, 'create' statements, 'alter' statements, and 'drop' statements. In a more concise and understandable way, it can be summarized as: all operations involving data changes must be recorded in the binary log.
To start the binary log, use 'show variables like 'log_bin'\G' to check if the binary log 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>
We can see that 'log_bin' is not enabled by default and is a read-only variable, which needs to be configured in the 'my.cnf' file, and then MySQL needs to be restarted. Run 'service mysql restart' to restart MySQL. After restarting MySQL, you will find that a file has been generated in the 'data' directory.1.000001The file. In fact, each time MySQL restarts, a file like this is generated under the directory, and the file name increments sequentially. In addition, MySQL will also create an index file for the binary log under the directory, which can be viewed by the command 'show variables like 'log_bin_index'\G', and then viewed with the 'cat' command. You will find that it records the relative path of the binary files.
You can use the built-in tools in MySQL to view the binary log. The specific location is in the bin directory of mysql. Common options for the mysqlbinlog command: }}
-s Display the log content in a concise manner
-v Display the log content in detail
-d=数据库名 Only display 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 content 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 in use
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)
Use binary log to recover data
The syntax is very simple:
mysqlbinlog -s 1.000001 | mysql -h 192.168.1.188 -u root -p
After mysqlbinlog, you can follow --start-datetime,--stop-datetime, start-position, stop-position and other parameters.
--start-datetime,--stop-datetime these two parameters can be used for data recovery based on the time point;
start-position, stop-The position can be used for more detailed data recovery operations;
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 is the size of a single binary log file. If it exceeds this value, a new file will be generated with the suffix name+1;
binlog_cache_size
binlogcachesize is the cache size for storing binary logs in memory
sync_binlog
sync_binlog writes several times binary log to cache, and starts to refresh to external storage (hard disk).
log_slave_updates
logslvaeupdates 用于主从复制
Cleaning of binary logs
In principle, the logs to be cleaned up should be backed up to other storage devices in the form of physical backups first, for permanent retention. Then it is recommended to use the following two risk-free cleaning methods:
First kind:
purge master logs before '2017-02-16 00:00:00';
Second kind:
Set the expiration days of the binary files by setting the expire_logs_days parameter directly in the MySQL configuration file my.cnf. The expired binary files will be automatically deleted. It is recommended to start another periodic task to back up binary logs regularly before deletion. In case some data is found to have errors after several days, and the binary log is automatically deleted.
expire_logs_days=90
Eight, InnoDB Transaction Log
The InnoDB transaction log is different from the previously mentioned log. The InnoDB transaction log is maintained by the InnoDB storage engine itself, and its content cannot be read by the database administrator. To put it another way, any high-performance system must make use of caching to improve data access efficiency. From all aspects, caching plays a huge role. To rise to a higher level and refine it: caching and queues are the necessary paths to achieve high performance. For databases, this is a very tricky problem. To ensure that data is read and stored more efficiently, caching must be used. However, to ensure data consistency, it is necessary to ensure that all data must be stored accurately in the database, and data recovery must be guaranteed in case of any accidents. We know that InnoDB is a transaction-safe storage engine, and consistency is an important characteristic of the ACID transaction. The InnoDB storage engine mainly implements data consistency through InnoDB transaction logs, which include redo (redo) logs and undo (undo) logs.
Redo log (redo)
The redo log mainly records transactions that have been fully completed, that is, logs that have executed commit, by default, the value of the redo log is recorded in iblogfile0 and iblogfile1in the redo log.
[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 incomplete transactions that have been partially completed and written to the hard disk, by default, the information of the rollback log is recorded in the tablespace file, the shared tablespace file ibdata1or exclusive table space, not seen in ibd.
From the above figure, we can know that the rollback log is recorded in the ibdta by default1My 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 undo logs, InnoDB rolls back all the partially completed transactions that have been written to the hard disk through the undo log (undo) log. Then, all the transactions in the redo log (undo) log are executed again to restore all the data. However, due to the large amount of data, to shorten the recovery time, InnoDB introduces the Checkpoint mechanism.
Dirty page (dirty page)
When a transaction needs to modify a record, InnoDB first reads the data block where the data is located from the external storage 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 different from the data block in the external storage, and the data page in the cache is called a dirty page (dirty page), and the dirty page is refreshed to the external storage and becomes a clean page (clean page).
Note: The default memory page is4K, or4K times. You can imagine the memory as a book that can be washed, each time MySQL reads data, it applies for several clean pages of memory, and then writes on them. After the data is refreshed to the hard disk, these data pages are immediately erased and available for other programs to use.
Log sequence number (log sequence number)
Log sequence number (LSN) is the end point of each log in the log space, represented by byte offset, used in Checkpoint and recovery.
The principle of Checkpoint mechanism Suppose at a certain point in time, all the dirty pages (dirty pages) have been refreshed to the hard disk, all the redo logs (redo) before this point in time do not need to be redone. The system uses this point as the end position of the redo log as Checkpoint, and the redo logs before the Checkpoint do not need to be redone anymore, and can be safely deleted. In order to better utilize the space of the redo log (redo), InnoDB adopts a circular strategy to use the redo log space, so the InnoDB redo log file must be at least2Through the Checkpoint mechanism, by performing redo operations (undo) on the transactions that have been completed but not yet written to the external storage due to the database crash, we can ensure data consistency and also shorten the recovery time.
InnoDB redo log parameters
innodb_log_buffer_size: Sets the size of the redo log buffer.
innodb_log_files_in_group: Sets the number of redo logs (redo) 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: Sets the number of redo log mirror file groups, which can only be set to1.
innodb_log_group_home_dir: Sets the directory where the log file group is stored, the default is under the database root directory.
InnoDB Rollback Log (undo) Parameters
innodb_undo_directory: Sets the directory where the rollback log is stored.
innodb_undo_logs: Sets the size of the rollback log rollback segment, the default is128k
innodb_undo_tablespace: Sets the number of rollback log files that make up the rollback log, the default is 0.
Warning: Special attention is required. 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 increased again.
9. Log File Backup
You can use flush logs when backing up to close all current log files and then generate new log files. After closing the log files, you can back up them physically. In addition, flush logs can add specific log types:
flush error logs flush general logs flush binary logs flush slow logs
Declaration: The content of this article is from the Internet, and the copyright belongs to the original author. The content is contributed and uploaded by Internet users spontaneously. This website does not own the copyright, has not been manually edited, and does not assume any relevant legal liability. If you find any content suspected of copyright infringement, please send an email to notice#w3Please report any infringement by sending an email to codebox.com (replace # with @ in the email address) and provide relevant evidence. Once verified, this site will immediately delete the infringing content.