MySQL server log manage
#
Binary Log
The size of the binlog_cache_size must be a multiple of 4096, or there will be a warning, "Warning | 1292 | Truncated incorrect binlog_cache_size value: '33792'", and then fetch down the nearest n values of 4096.
Mysql > show warnings
+-- +
| | Level | Code | Message | |
+-- +
| | Warning | 1292 | Truncated incorrect binlog_cache_size value: '33792' | |
+-- +
1 row in set (0.0
SET GLOBAL binlog_format = 'STATEMENT'
SET GLOBAL binlog_format = 'ROW'
SET GLOBAL binlog_format = 'MIXED'
SET SESSION binlog_format = 'STATEMENT'
SET SESSION binlog_format = 'ROW'
SET SESSION binlog_format = 'MIXED'
Set global binlog_cache_size=4096*n
Log_bin = / data/my3306/bin_log/mysql_bin
Binlog_format = row
Binlog_cache_size=4096*n
#
General log
Mysql > SHOW global variables like'% general%'
+-- +
| | Variable_name | Value |
+-- +
| | general_log | OFF |
| | general_log_file | / data/my3306/centos65.log |
+-- +
Set global general_log=1
Set global general_log_file='/data/my3306/centos65_general.log'
Mysql > SHOW global variables like'% general%'
+-- +
| | Variable_name | Value |
+-- +
| | general_log | ON |
| | general_log_file | / data/my3306/centos65_general.log |
+-- +
2 rows in set (0.00 sec)
#
Slow_query_log
Mysql > show variables like'% slow%'
+-+
| | Variable_name | Value |
+-+
| | log_slow_admin_statements | OFF |
| | log_slow_slave_statements | OFF |
| | slow_launch_time | 2 | |
| | slow_query_log | OFF |
| | slow_query_log_file | / data/my3306/centos65-slow.log |
+-+
5 rows in set (0.00 sec)
Mysql > set global slow_query_log=1
Query OK, 0 rows affected (0.33 sec)
Mysql > set global long_query_time=1
Query OK, 0 rows affected (0.00 sec)
Mysql > show variables like'% quer%'
+-+
| | Variable_name | Value |
+-+
| | binlog_rows_query_log_events | OFF |
| | ft_query_expansion_limit | 20 |
| | have_query_cache | YES |
| | log_queries_not_using_indexes | OFF |
| | log_throttle_queries_not_using_indexes | 0 | |
| | long_query_time | 1.000000 | |
| | query_alloc_block_size | 8192 | |
| | query_cache_limit | 1048576 | |
| | query_cache_min_res_unit | 4096 | |
| | query_cache_size | 1048576 | |
| | query_cache_type | OFF |
| | query_cache_wlock_invalidate | OFF |
| | query_prealloc_size | 8192 | |
| | slow_query_log | ON |
| | slow_query_log_file | / data/my3306/centos65-slow.log |
+-+
15 rows in set (0.00 sec)
#
Innodb_log_file
To change the number or the size of your InnoDB redo log files, perform the following steps:
1 Stop the MySQL server and make sure that it shuts down without errors.
2 Edit my.cnf to change the log file configuration. To change the log file size
Configure innodb_log_file_size. To increase the number of log files, configure innodb_log_files_in_group.
3 Start the MySQL server again.
If InnoDB detects that the innodb_log_file_size differs from the redo log file size
It will write a log checkpoint, close and remove the old log files, create new log files at the requested size, and open the new log files.
Mysql > show variables like'% innodb%log%'
+-+ +
| | Variable_name | Value |
+-+ +
| | innodb_api_enable_binlog | OFF |
| | innodb_flush_log_at_timeout | 1 | |
| | innodb_flush_log_at_trx_commit | 1 | |
| | innodb_locks_unsafe_for_binlog | OFF |
| | innodb_log_buffer_size | 8388608 | |
| | innodb_log_compressed_pages | ON |
| | innodb_log_file_size | 50331648 | |
| | innodb_log_files_in_group | 2 | |
| | innodb_log_group_home_dir |. / | |
| | innodb_mirrored_log_groups | 1 | |
| | innodb_online_alter_log_max_size | 134217728 | |
| | innodb_undo_logs | 128 | |
+-+ +
12 rows in set (0.00 sec)
Innodb_log_group_home_dir = / hbdata/mysqldata/3306/binlog
Innodb_log_file_size = 1300m
Innodb_log_files_in_group = 3
2016-06-02 11:16:17 4299 [Warning] InnoDB: Resizing redo log from 5 "12800 to 4" 9600 pages, LSN=23031318
2016-06-02 11:16:17 4299 [Warning] InnoDB: Starting to delete and rewrite log files.
2016-06-02 11:16:17 4299 [Note] InnoDB: Setting logfile. / ib_logfile101 size to 150 MB
InnoDB: Progress in MB: 100
2016-06-02 11:16:18 4299 [Note] InnoDB: Setting logfile. / ib_logfile1 size to 150 MB
InnoDB: Progress in MB: 100
2016-06-02 11:16:19 4299 [Note] InnoDB: Setting logfile. / ib_logfile2 size to 150 MB
InnoDB: Progress in MB: 100
2016-06-02 11:16:20 4299 [Note] InnoDB: Setting logfile. / ib_logfile3 size to 150 MB
InnoDB: Progress in MB: 100
2016-06-02 11:16:21 4299 [Note] InnoDB: Renaming logfile. / ib_logfile101 to. / ib_logfile0
2016-06-02 11:16:21 4299 [Warning] InnoDB: New log files created, LSN=23031318
