Shulou(Shulou.com)05/31 Report--

This article introduces what log maintenance strategies there are in MySQL, the content is very detailed, interested friends can use for reference, I hope it can be helpful to you.

Log type:

MySQL has several different log files that can help you find out what's going on inside mysqld:

The log file records the type of information in the file, the error log records the problems that occur when starting, running, or stopping, the query log records the client connections established and the statements executed, the binary log records all change data statements. It is mainly used for replication and instant point recovery slow logging to record all queries whose execution time exceeds long_query_time seconds or queries that do not use indexes. Transaction logs are recorded when transaction-supporting storage engines such as InnoDB execute transactions.

1. Start the slow query log:

If the slow_query_log=ON option is enabled in MySQL, queries that take longer than long_query_time (the default is 10s) are logged (the time the table was initially locked does not count as execution time). The logging file is slow_query_log_file [= file_name]. If no value of file_ name is given, the default hostname is-slow.log. If a file name is given, but not an absolute path name, the file is written to the data directory.

This can be enabled when debugging mysql performance to find out which sql instruction is the most time-consuming. It is recommended to close in the production environment]

two。 Turn off the generic query log in the production environment:

Since opening the general query log records all the operations of the user, the amount of this log in the production environment is very large, so it is not open in general. The log function is also turned off by default in myslq, and it is opened only in special cases. [generally, only in the development test environment, when the specific SQL statements are used for certain functions. Will open the log in a short period of time to do the corresponding analysis. ]

Mysql > set global general_log = 1; # 1: start the general query log, 0: disable the general query log

Mysql > show global variables like'% general_log%'

+-+-- + | Variable_name | Value | +-+-+ | general_log | ON | # whether generic is enabled Query log | general_log_file | / var/run/mysqld/mysqld.log | # Log path +-+-+

2 rows in set (0.00 sec)

3. Back up binary log and sql data regularly: [one copy locally, one copy of remote log host, one copy of storage host]

In my.cnf, log-bin = [filename] is enabled, which is recorded upwards with [filename] .000001 by default. Since log-bin is enabled [at this time * use mysqldump to save the data of a current mysql library, because the binary log only records all sql statements from now until the last mysql crash restart], mysql will start recording every sql statement. Once mysql needs to be restarted for various reasons. A new binary log is generated, and the suffix name of 000001 is constantly added. If the mysql data is corrupted (such as disk corruption) during the mysql crash, and all the previous data is corrupted, this backup strategy can help you recover the loss. You can recover data from the binary log from the start to the last mysql restart. [each sql statement is recorded in the binary log, and you can view the contents of the log with mysqlbinlog [filename]]

The value of the 4.sync_binlog global variable must be appropriate:

By default, the binary log is not synchronized with the hard disk every time you write. So if the operating system or machine (not just the MySQL server) crashes, it is possible that the statements in the binary log are lost. To prevent this, you can use the sync_binlog global variable (1 is the safest value, but also the slowest) to synchronize the binary log with the hard disk after every N binary log writes. Updates to non-transactional tables are saved to the binary log as soon as they are completed.

Here is an explanation of sync_binlog:

"sync_binlog": this parameter is critical for MySQL systems. It not only affects the performance loss caused by Binlog to MySQL, but also affects the integrity of data in MySQL. The various settings for the "sync_binlog" parameter are described as follows:

Sync_binlog=0, when a transaction is committed, MySQL does not do disk synchronization instructions such as fsync to refresh the information in binlog_cache to disk, but let Filesystem decide when to synchronize, or synchronize to disk after the cache is full.

Sync_binlog=n, after every n transaction commits, MySQL will issue a disk synchronization instruction such as fsync to force the data in binlog_cache to be written to disk.

In MySQL, the default setting of the system is sync_binlog=0, that is, no mandatory disk refresh instruction is made. At this time, the performance is * *, but the risk is also *. Because once the Crash is integrated, all the binlog information in binlog_cache will be lost. When set to "1", it is the safest but performance loss setting. Because when set to 1, even if the system Crash, at most one outstanding transaction in the binlog_cache is lost, without any material impact on the actual data. From past experience and related tests, for systems with high concurrent transactions, the write performance gap between systems with "sync_binlog" set to 0 and 1 may be as high as 5 times or more.

5. If the database has a lot of transactional operations, it is recommended that you set a larger rollback limit for binary logs:

For transactional tables, such as BDB or InnoDB tables, updates to all change tables (UPDATE, DELETE, or INSERT) are cached until the server receives the COMMIT statement. At this point, before the COMMIT is finished, mysqld writes the entire transaction to the binary log. When the thread processing the transaction starts, it allocates binlog_cache_size-sized memory for the buffered query. If the statement is greater than this value, the thread opens a temporary file to save the transaction [so if the bunlog_cache_size is large enough, it avoids the IBO operation of too many disks and caches all the data in memory]. The temporary file is deleted after the thread ends. ["max_binlog_cache_size": corresponds to "binlog_cache_size", but represents the amount of cache memory that binlog can use. When we execute a multi-statement transaction, the system may report a "Multi- statementtransactionrequiredmorethan'max_binlog_cache_size'bytesofstorage" error if the max_binlog_cache_size is not large enough. So * also increase the max_binlog_cache_size (depending on your server)]

6. Try to set the max_binlog_size larger.

The Binlog log * * value is generally set to 512m or 1G, but cannot exceed 1G. This size can not strictly control the size of the Binlog, especially when the Binlog is relatively close to the tail and encounter a larger transaction, in order to ensure the integrity of the transaction, it is impossible for the system to switch the log, so it can only record all the SQL of the transaction into the current log until the end of the transaction.

7. Here is what happens in the mysql environment:

Mysql > show variables like'% binlog%'

+-- +-+ | Variable_name | Value | +-+-+

| | binlog_cache_size | 1048576 | |

| | innodb_locks_unsafe_for_binlog | OFF |

| | max_binlog_cache_size | 4294967295 | |

| | max_binlog_size | 1073741824 | |

| | sync_binlog | 0 | |

+-+ +

This is the end of what log maintenance strategies are shared in MySQL. I hope the above content can be helpful to you and learn more knowledge. If you think the article is good, you can share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.