加入收藏 | 设为首页 | 会员中心 | 我要投稿 52站长网 (https://www.52zhanzhang.com/)- 科技、建站、经验、云计算、5G、大数据,站长网!
当前位置: 首页 > 站长学院 > MySql教程 > 正文

MySQL server log manage

发布时间:2022-03-29 16:21:52 所属栏目:MySql教程 来源:互联网
导读:################# 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
 

(编辑:52站长网)

【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!

    热点阅读