Experience Sharing: Summary of MySQL log maintenance policy _ MySQL

MYSQL log files can be divided into six types. This blog post summarizes seven mysql log maintenance policies and related suggestions based on the learning and experiment of the bloggers.

These days I have to deal with mysql servers, so I have collected some maintenance strategies on the Internet, and then I will summarize my experiments. The following is my summary of experience and some suggestions from others.

Log type:

MySQL has several different log files to help you find out what happened inside mysqld:

Log files	Types of information recorded in files
Error log	Record problems during startup, running, or stop
Query Logs	Record the established client connection and executed statements
Binary log	Records all statements for changing data. It is mainly used for replication and instant point recovery.
Slow log	Record all queries whose execution time exceeds long_query_time seconds or where no index is used
Transaction log	Logs generated when transactions are executed by InnoDB and other transaction-supporting storage engines

1. start the slow query log:

If the slow_query_log = ON option is enabled for MySQL, the query with the execution time exceeding long_query_time (10 s by default) is recorded (the initial time for locking the table is not counted as the execution time ). The log file is slow_query_log_file [= file_name]. if the file_name value is not given, the default value is the host name and the suffix is-slow. log. If the file name is provided but not the absolute path name, the file is written to the data directory.

[This can be enabled when debugging mysql Performance. you can find out which SQL command is the most time-consuming. Disable it in the production environment]

2. disable common query logs in the production environment:

The general query log is used to record all user operations. in the production environment, the log volume is very large, so it is generally not opened, by default, this log function is disabled for myslq. in special cases, this function is enabled. [this function is generally enabled only in the development and testing environment to locate the specific SQL statements used for some functions, in a short period of time.]

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 query logs are enabled
  
  
 
  
  
| General_log_file |/var/run/mysqld. log | # log path
  
  
 
  
  
+ ------------------ + ---------------------------- +
 
 

2 rows in set (0.00 sec)

3. regular backup of binary logs and SQL data: [Local copy, remote log host copy, and storage host copy]

In my. in cnf, log-bin = [filename] indicates that binary logs are enabled. the default value is [filename]. 000001. after log-bin is enabled, use mysqldump to save the data of a mysql database, because the binary log only records all the SQL statements from now on to the latest mysql restart, mysql starts to record every SQL statement. once mysql needs to be restarted for various reasons, A new binary log is generated, and the suffix of 000001 is constantly added. If the mysql data is damaged (for example, the disk is damaged) during the mysql server, all the previous data is damaged. in this case, this backup policy can help you recover the loss. You can recover data from the binary log from the beginning to the last mysql restart. [Every SQL statement is recorded in the binary log. you can use mysqlbinlog [filename] to view the log Content]

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

By default, the binary log is not synchronized with the hard disk every time it is written. Therefore, if the operating system or machine (not just the MySQL server) crashes, the final statement in the binary log may be lost. To prevent this situation, you can use the sync_binlog global variable (1 is the safest value, but also the slowest) to synchronize binary logs with the hard disk after each N binary logs are written. The update of non-transaction tables is saved to the binary log immediately after execution.

The following explains sync_binlog:

"Sync_binlog": this parameter is crucial for the MySQL system. it not only affects the performance loss caused by Binlog on MySQL, but also affects the data integrity in MySQL. The settings of the "sync_binlog" parameter are described as follows:

Sync_binlog = 0. after the transaction is committed, MySQL does not perform disk synchronization commands such as fsync to refresh the information in binlog_cache to the disk, and Filesystem determines when to synchronize the data, or, after the cache is full, it is synchronized to the disk.

Sync_binlog = n. after each n transaction commits, MySQL runs a disk synchronization command such as fsync to forcibly write data in binlog_cache to the disk.

In MySQL, sync_binlog is set to 0 by default, that is, no mandatory disk refresh command is performed. at this time, the performance is the best, but the risk is also the biggest. This is because all binlog information in binlog_cache will be lost once the system Crash is implemented. When it is set to "1", it is the safest but the biggest loss of performance. When set to 1, even if the system Crash is used, a transaction not completed in binlog_cache can be lost at most, without any material impact on the actual data. From past experience and related tests, for high-concurrency transaction systems, the system write performance gap between "sync_binlog" and "1" may be as high as 5 times or more.

5. if the database has many transaction-type operations, we recommend that you set a larger limit for binary log rollback:

For transaction tables, such as BDB or InnoDB tables, all UPDATE (UPDATE, DELETE, or INSERT) changes to the table are cached until the server receives the COMMIT statement. At this point, before the execution of COMMIT, mysqld writes the entire transaction to the binary log. When the transaction processing thread starts, it allocates binlog_cache_size memory for the buffer query. If the statement is greater than this value, the thread opens a temporary file to save the transaction. Therefore, if the bunlog_cache_size is large enough, too many disk I/O operations are avoided, all data can be cached in the memory ]. The temporary file is deleted after the thread ends. ["Max_binlog_cache_size": corresponds to "binlog_cache_size", but represents the maximum cache memory size that binlog can use. When we execute Multi-statement transactions, if max_binlog_cache_size is not large enough, the system may report the error "Multi-statementtransactionrequiredmorethan 'max _ binlog_cache_size 'bytesofstorage. So it is best to increase the value of max_binlog_cache_size (depending on your server )]

6. set max_binlog_size to a greater value.

Maximum Binlog log value. Generally, the value is set to MB or 1 GB, but cannot exceed 1 GB. This size does not strictly control the Binlog size. especially when a large transaction arrives near the end of the Binlog, the system ensures the integrity of the transaction, it is impossible to switch logs. you can only record all SQL statements of the transaction into the current log until the transaction ends.

7. The following is 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| 
  
  
 
  
  
+--------------------------------+------------+ 

 
 

Blog Source: http://my.oschina.net/u/1759688/blog/384530