Shulou(Shulou.com)06/01 Report--

#

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.