MySQL Log System details

MySQL Log System details

All those who have done large systems know that the log function is not to be underestimated. In the middle and late stages of the project, the optimization and upgrade of the project are based on the log to make the upgrade and optimization decisions. Therefore, to learn MySQL, the log part cannot be missed. The optimization mentioned in our actual application during the interview should be obtained from the log. The system learns mysql logs to help us locate problems accurately and improve our work level. In addition, a series of logs will focus on dba o & M, and the system will understand MySQL configurations in various aspects to make MySQL a handy data warehouse.

1. MySQL Log Type

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

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

MySQL Log types include:

1. error logs, MySQL service instance startup, running, or stop information.
2. general query logs, all SQL statements or MySQL commands run by the MySQL service instance.
3. binary logs: All update statements executed on the database, excluding select and show statements.
4. slow query log (slow), SQL statement with execution time exceeding the value set by long_query_time, or SQL statement without indexes.

Ii. MySQL Log Cache

Cache must play a vital role in a high-speed, stable, and reliable system. MySQL Log processing also uses the cache mechanism. MySQL logs are originally stored in the memory of the MySQL server. If the specified storage capacity is exceeded, logs in the memory are written (or flush is refreshed) to the external storage, store data in a database table or file permanently on the hard disk.

Iii. MySQL error log)

The MySQL Error Log mainly records the detailed information about each MySQL service instance startup and stop, as well as the warning or error information generated during the MySQL instance running process. Unlike other logs, MySQL error logs must be enabled and cannot be disabled.

By default, the file name of the error log is: Host Name. err. However, the error log does not record all error information. Only the critical errors (critical) generated during the running of the MySQL service instance will be recorded.

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

Iv. MySQL general query log)

The MySQL common query log records all the operations of the MySQL service instance, such as select, update, insert, and delete operations, whether or not the operation is successfully executed. There is also information about the connection and disconnection between the MySQL client and the MySQL server, whether the connection is successful or failed. There are three parameters related to MySQL common query logs.

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

You can enable normal query logs by using set @ 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 during the running of the current MySQL instance. Once MySQL is restarted, it will be restored to the default state. The Permanent modification method is to modify the mysql my. cnf file. Add the following after the configuration file:

general_log = 1
general_log_file

Once the common query log is enabled, the MySQL service instance automatically creates a common query log file. The general_log_file parameter sets the physical location of the common 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: because the common query log records almost all MySQL operations, for database servers with frequent data access, enabling the common query log of MySQL will greatly reduce the database performance, therefore, we recommend that you disable common query logs. You can temporarily open common query logs only when you need to track some special query logs in special periods.

Log_output

The log_output parameter sets the content of common query logs and slow query logs to be stored in the database table. You can use set @ global. log_output = 'table' to store common query logs and slow query logs to the general and slow_log tables in the mysql System database. It is worth noting that the storage engines of these two tables are CSV. After that, you can use SQL statements to view the new common query log Content;

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

V. MySQL slow query log (slow log)

Slow query of log-related issues, the interviewer is very fond of chatting about this issue during the interview. In the past, I was able to talk about the MySQL master-slave architecture and optimize MySQL from various aspects. However, I didn't really understand how to enable and configure slow queries.

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

1. slow_query_log

Slow_query_log: Set whether to enable the slow query log.

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 automatically creates the slow query log file. The file specified by slowquerylog_file stores the slow query log Content. The modification method is the same as that shown above. Directly go to the my. cnf file to edit the file.

3. long_query_time

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

4. log_quries_not_using_indexes

Whether or not log_quries_not_using_indexes records query statements that do not use indexes to slow query logs, 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

Set the output format of common query logs and slow query logs. The values include two files and tables;

Vi. Viewing MySQL slow query logs

The log_output parameter can be used to set the output format of slow query logs. The default value 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 |+----------------+---------------------+

Lock_time indicates the time when the SQL statement is blocked by the lock. Rows_send indicates the number of rows returned after the SQL statement is executed. Rows_examined indicates the number of records actually scanned during SQL Execution.

However, using TABLE to store slow query logs is not common. When the business volume is large, it may affect the system's primary service. We can store logs using FILE. When installing MySQL, mysqldumpslow. pl is installed by default in the bin directory of MySQL for log analysis of slow queries. If you use this tool in Windows, you may need to make some configurations. This is not covered in this article. If you want to learn about system services, go to linux. For linux commands and tools, you can use the command itself + -- help option to view the help documentation.

-S indicates the sort method

Sub-options: c, t, l, r

C: Number of SQL executions
T: execution time
L: Lock wait time
R: number of returned data entries
At, al, and ar are the average values corresponding to t l r. -T: returns the first N records.

-G: the abbreviation of grep. Include fuzzy match

The common method is as follows:

// Return the 20 most frequently accessed SQL statements. /mysqldumpslow-s c-t 20/usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log // return 20 SQL statements with the maximum number of return records. /mysqldumpslow-s r-t 20/usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log // return the SQL statement containing like. /mysqldumpslow-G' like '20/usr/local/webserver/extend_lib/mysql/data/roverliang-slow.log

VII. binary log)

The binary log is different from the preceding logs. Binary logs cannot be viewed directly in cat or less text viewer. Professional tools are required. Binary logs mainly record changes in the database, so they can be used for synchronization between the master and slave databases. The content mainly includes all database update operations, use statements, insert statements, delete statements, update statements, create statements, alter statements, and drop statements. In a concise and easy-to-understand sentence, all operations involving data changes must be recorded in binary logs.

Start the binary log and use show variables like 'Log _ bin' \ G to check whether the binary log is enabled.

mysql> show variables like 'log_bin'\G*************************** 1. row ***************************Variable_name: log_bin    Value: OFF1 row in set (0.00 sec)mysql> set @@global.log_bin=1;ERROR 1238 (HY000): Variable 'log_bin' is a read only variablemysql> 

Log_bin is disabled by default and is a read-only variable. You need to configure it in my. cnf and restart MySQL. After service mysql restart restarts MySQL, A 1.000001 file is generated in the data directory. In fact, every time MySQL restarts, such a file will be generated in the directory, and the file names will increase sequentially. In addition, MySQL creates an index file for Binary logs in this directory. You can run the show variables like 'Log _ bin_index '\ G command to view the location of the index file, then run the cat command. You will find that the relative path of the binary file is recorded.

You can use the MySQL tool to view binary logs. The specific location is in the bin directory of mysql. Common options for mysqlbinlog commands:

-S: Display log Content in simplified mode
-V displays log Content in detail
-D = the database name only displays the log Content of the specified database
-O = n ignore the first n MySQL commands in the log
-R = file: Write the specified content to the specified file.

-- Start-datetime
Display logs within a specified time range
-- Stop-datetime

-- Start-position
Display the log content within the specified interval
-- Stop-position

Obtain the currently used 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)

Restore data using binary logs

The syntax is simple:

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

After mysqlbinlog, you can use -- start-datetime, -- stop-datetime, start-position, stop-position, and other parameters.

-- Start-datetime and -- stop-datetime parameters can be used to recover data based on time points;

Start-position and stop-position can be used for data recovery with more detailed operation points;

MySQL binary log 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 this value is exceeded, a new file with a suffix of + 1 is generated;

binlog_cache_size

Binlogcachesize cache size for storing binary logs in memory

sync_binlog

The binary log is written several times in the sync_binlog cache and refreshed to the external storage (hard disk) synchronously.

log_slave_updates

Logslvaeupdates for master-slave Replication

Binary log cleanup

In principle, the logs to be cleared must be backed up to other storage devices through physical backup for permanent retention. We recommend the following two cleanup methods with lower risks:

First:

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

Second:

Set the expire_logs_days parameter directly in the MySQL configuration file my. cnf to set the Expiration days of the binary file. Expired binary files will be automatically deleted. We recommend that you enable another scheduled periodic task before deleting the task and regularly back up the binary task. In this case, binary logs are automatically deleted because some data has been detected for several days.

expire_logs_days=90

VIII. InnoDB Transaction logs

MySQL uses the cache to maximize data access efficiency. In other words, any high-performance system must use the cache. in all aspects, the cache has played a huge role. Raise it to a higher level and refine it: caching and queuing are essential to achieving high performance. This is a very difficult problem for databases. To ensure that data is read and stored more efficiently, you must use the cache. However, to ensure data consistency, you must ensure that all data must be stored in the database accurately, in case of accidents in a timely manner, and that data can be recovered. We know that InnoDB is a transaction-safe storage engine, and consistency is an important feature of ACID in transactions. The InnoDB Storage engine implements data consistency through InnoDB Transaction logs. InnoDB Transaction logs include redo logs and undo logs.

The InnoDB Transaction log is different from the preceding log. the InnoDB Transaction log is maintained by the InnoDB Storage engine and cannot be read by the database administrator.

Redo log)

Redo logs record all completed transactions, that is, logs that execute commit. By default, the redo log values are recorded in iblogfile0 and iblogfile1 redo logs.

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

Undo)

Rollback logs mainly record unfinished transactions that have been partially completed and written to the hard disk. By default, rollback logs are recorded in tablespace files, shared tablespace file ibdata1 or exclusive tablespace is not found in ibd.

As we can know, rollback logs are recorded in ibdta1 by default. My mysql system version is 5.6.24.

Checkpoint Mechanism

When the MySQL server crashes and the MySQL service is restarted, InnoDB rolls back the log (undo) by means of the redo and undo logs) the log rolls back all unfinished transactions that have been partially completed and written to the hard disk ). Then, all the transactions in the redo log are re-executed to restore all the data. However, the data volume is too large. InnoDB introduces the Checkpoint mechanism to shorten the recovery time.

Dirty page)

When a transaction needs to modify a record, InnoDB first reads the data block of the data from the external storage to the hard disk. After the transaction is committed, InnoDB modifies the records on the data page, the cached data page is different from the data block in the external store. The data page in the cache is called a dirty page, and the dirty page is refreshed to the external store, to a clean page ).

Note: A Memory Page is 4 kb by default, or a multiple of 4 kb. You can think of the memory as a book that can be scrubbed. Each time MySQL reads data, it requests several clean pages from the memory and then writes them. After the data is refreshed to the hard disk, the data pages are erased immediately for use by other programs.

Log sequence number)

The log serial number (LSN) is the end point of each log in the log space. It is expressed by the byte offset and used for Checkpoin and recovery.

The Checkpoint mechanism assumes that all dirty pages are refreshed to the hard disk at a certain time point, and all redo logs before this time point do not need to be redone. the system uses the end position of the redo log at this time point as the Checkpoint. The redo log before the Checkpoint does not need to be redone, so you can safely delete it. To make better use of the redo space, InnoDb adopts the round robin policy to use the redo log space. Therefore, InnoDB has at least two redo log files. The Checkpoint mechanism is used to redo the transactions that have been modified in the cache but not completely written to the external store by redo, this ensures data consistency and shortens the recovery time.

InnoDB redo log parameters

Innodb_log_buffer_size: sets the size of the redo log cache.
Innodb_log_files_in_group: sets the number of logs redo in the log file group.
Innodb_log_file_size: set the size of the redo log file. The larger the file, the longer the recovery time.
Innodb_mirrored_log_groups: Number of redo log image file groups, which can only be set to 1.
Innodb_log_group_home_dir: Set the directory where the log file group is stored. The default value is in the root directory of the database.

InnoDB rollback log (undo) Parameters

Innodb_undo_directory: directory for storing rollback logs.
Innodb_undo_logs: sets the size of the rollback segment of the rollback log. The default value is 128 kb.
Innodb_undo_tablespace: set the number of log rollback files in the rollback log. The default value is 0.
Note: After installing MySQL. set the rollback log parameters in cnf. If the rollback log parameters are set after the database is created, MySQL reports an error. After the rollback log is created, it cannot be modified or added again.

9. log file backup

During backup, you can use flush logs to close all current log files and generate new log files. After closing the log file, you can use physical backup. In addition, you can add specific log types to flush logs:

flush error logsflush general logsflush binary logsflush slow logs