Mysql Log Maintenance Policy Rollup _mysql

These days to toss MySQL server, so on the internet to collect some maintenance strategies, and then summarize their own experiments, the following is my experience and some of the suggestions of others.

Log type:

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

log files: types of information that are entered in a file
error log: Log issues that occur when starting, running, or stopping
query log: record established client connections and executed statements
binary log: records all statements that change data. Primarily for replication and instant point recovery
Slow log: log all queries that have run longer than long_query_time seconds or queries that do not use indexes
transaction log: logs that are generated when transactions are performed by the storage engine that supports transactions, such as InnoDB

1. Start Slow query log:

MySQL If the Slow_query_log=on option is enabled, queries that run longer than Long_query_time (the default 10s) are logged (the time at which the table is locked initially does not count as execution time). Log file is Slow_query_log_file[=file_name], if no file_name value is given, the default is host name and suffix is-slow.log. If a file name is given, but not an absolute pathname, the file is written to the data directory.

"This can be enabled while debugging MySQL performance to find out which SQL instruction is the most time-consuming." Recommended shutdown in production environment

2. Turn off common query log in production environment:

Since opening a common query log is all about recording the user's actions, in the production environment, the amount of this log is very large, so it is generally not open, MYSLQ the default log function is also closed, under special circumstances to open "generally only in the development test environment, In order to locate some of the features of the specific use of SQL statements, it will be in a short period of time to open the log to do the corresponding analysis. 】

mysql> Set Global general_log = 1; #1: Start common query log, 0: Turn off common query log

Mysql> show global variables like '%general_log% ';

+------------------+----------------------------+ 
 
| variable_name | Value | 
 
+------------------+----------------------------+ 
 
| general_log | On | #是否启用了通用查询日志 | 
 
general_log_file |/var/run/mysqld/mysqld.log | #日志路径 
 

2 rows in Set (0.00 sec)

3. Regular backup of binary logs and SQL data : "Local one, remote log host, a storage host"

in my.cnf log-bin = [FileName] is a binary log enabled, the default is to [filename].000001 up, from the Log-bin after enabled "At this time, it is best to save the current MySQL database data, Because the binary log only records all SQL statements from now to the most recent MySQL reboot MySQL will start recording every SQL statement, once MySQL for a variety of reasons need to restart, will produce a new binary log, 000001 of the suffix name will continue to add. If MySQL's data is compromised during MySQL (such as disk corruption), all of the previous data has been compromised, and this backup strategy will help you recover the damage. You can recover data from the binary log from the start to the most recent MySQL restart period. "The binary log records each SQL statement, you can use Mysqlbinlog [filename] To view the log content"

4.sync_binlog Global variables must be taken in the appropriate value:

By default, the binary logs are 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 last statement in the binary log is lost. To prevent this, you can use the Sync_binlog global variable (1 is the safest value, but also the slowest) so that the binary log synchronizes with the hard disk after every n binary log write. The update to the non-transaction table is saved to the binary log as soon as it finishes executing.

Below explains the following sync_binlog:

"Sync_binlog": This parameter is critical to the MySQL system, he not only affects the binlog of MySQL's performance loss, but also affect the integrity of the data in MySQL. The various settings for the "Sync_binlog" parameter are described below:

Sync_binlog=0, when the transaction is committed, MySQL does not do fsync, such as the disk synchronization instructions to refresh the information in the Binlog_cache to disk, and let filesystem decide when to do synchronization, or cache full before synchronizing to disk.

Sync_binlog=n, after each n transaction commits, MySQL will perform a disk synchronization instruction such as Fsync to force the data in the Binlog_cache to disk.

The default setting of the system in MySQL is sync_binlog=0, which is not to do any mandatory disk refresh instructions, at which point performance is the best, but the risk is greatest. Because once the system crash, all binlog information in the Binlog_cache will be lost. And when set to "1", is the most secure but the most performance loss of the settings. Because when set to 1, even if the system is crash, also lose most of the unfinished transaction in Binlog_cache, have no material influence to the actual data. From past experience and related testing, for systems with high concurrent transactions, the system write performance gap of "Sync_binlog" set to 0 and set to 1 may be as high as 5 times times or more.

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

For a transaction table, such as a bdb or InnoDB table, all changes to the table's update (update, delete, or insert) are cached until the server receives a commit statement. At that point, Mysqld writes the entire transaction to the binary log before executing the commit. When the thread that handles the transaction starts, it allocates binlog_cache_size memory for the buffered query. If the statement is greater than this value, the thread opens the temporary file to save the transaction "so if the bunlog_cache_size is large enough, you avoid excessive disk I/O operations, and you can cache all the data in memory." Temporary files are 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 a max_binlog_cache_size transaction, the system may report "Multi-statementtransactionrequiredmorethan" if it is not big enough max_binlog_cache _size ' bytesofstorage ' error. So it's best to turn the max_binlog_cache_size up a bit (see your server more specifically).

6. Try to set the max_binlog_size larger

Binlog log maximum value, generally set to 512M or 1G, but not more than 1G. This size is not very strict control of binlog size, especially when the Binlog is closer to the tail and encountered a larger transaction, the system in order to ensure the integrity of the transaction, it is impossible to do a switch log action, only the transaction of all the SQL records into the current log, until the transaction ends.

7. The following are the circumstances of 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| 
 
+--------------------------------+------------+ 

The above is the summary of the MySQL log maintenance strategy, I hope to maintain the MySQL log help.