MySQL server log manage
发布时间：2022-03-29 15:51:12 所属栏目：编程 来源：互联网
导读：################# Binary Log binlog_cache_size的大小必须为4096的倍数，不然会有一个warning， Warning | 1292 | Truncated incorrect binlog_cache_size value: 33792，然后向下取最接近n个4096的值。 mysql show warnings; +---------+------+---------
|
################# Binary Log binlog_cache_size的大小必须为4096的倍数，不然会有一个warning， “Warning | 1292 | Truncated incorrect binlog_cache_size value: '33792'”，然后向下取最接近n个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 log file ./ib_logfile101 size to 150 MB InnoDB: Progress in MB: 100 2016-06-02 11:16:18 4299 [Note] InnoDB: Setting log file ./ib_logfile1 size to 150 MB InnoDB: Progress in MB: 100 2016-06-02 11:16:19 4299 [Note] InnoDB: Setting log file ./ib_logfile2 size to 150 MB InnoDB: Progress in MB: 100 2016-06-02 11:16:20 4299 [Note] InnoDB: Setting log file ./ib_logfile3 size to 150 MB InnoDB: Progress in MB: 100 2016-06-02 11:16:21 4299 [Note] InnoDB: Renaming log file ./ib_logfile101 to ./ib_logfile0 2016-06-02 11:16:21 4299 [Warning] InnoDB: New log files created, LSN=23031318 （编辑：厦门网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
