too many connections, max_connections, wait_timeout

MySQL too many connections 에러시

1. max_connectons 값 조절

mysql> show global variables like 'max%';

+----------------------------+------------+

| Variable_name              | Value      |

+----------------------------+------------+

| max_allowed_packet         | 1048576    |

| max_binlog_cache_size      | 4294963200 |

| max_binlog_size            | 104857600  |

| max_connect_errors         | 10         |

| max_connections            | 512        |

| max_delayed_threads        | 20         |

| max_error_count            | 64         |

| max_heap_table_size        | 16777216   |

| max_insert_delayed_threads | 20         |

| max_join_size              | 4294967295 |

| max_length_for_sort_data   | 1024       |

| max_long_data_size         | 1048576    |

| max_prepared_stmt_count    | 16382      |

| max_relay_log_size         | 0          |

| max_seeks_for_key          | 4294967295 |

| max_sort_length            | 1024       |

| max_sp_recursion_depth     | 0          |

| max_tmp_tables             | 32         |

| max_user_connections       | 0          |

| max_write_lock_count       | 4294967295 |

+----------------------------+------------+

20 rows in set (0.00 sec)

512 로 되어 있는 것을 1024로 늘리자.

mysql> set @@global.max_connections = 1024;

적용되었는지 화인

mysql> show global variables like 'max%';

+----------------------------+------------+

| Variable_name              | Value      |

+----------------------------+------------+

| max_allowed_packet         | 1048576    |

| max_binlog_cache_size      | 4294963200 |

| max_binlog_size            | 104857600  |

| max_connect_errors         | 10         |

| max_connections            | 1024       |

| max_delayed_threads        | 20         |

| max_error_count            | 64         |

| max_heap_table_size        | 16777216   |

| max_insert_delayed_threads | 20         |

| max_join_size              | 4294967295 |

| max_length_for_sort_data   | 1024       |

| max_long_data_size         | 1048576    |

| max_prepared_stmt_count    | 16382      |

| max_relay_log_size         | 0          |

| max_seeks_for_key          | 4294967295 |

| max_sort_length            | 1024       |

| max_sp_recursion_depth     | 0          |

| max_tmp_tables             | 32         |

| max_user_connections       | 0          |

| max_write_lock_count       | 4294967295 |

+----------------------------+------------+

20 rows in set (0.00 sec)

mysql> 

1024로 변경되어 있다.

2. wait_timeout 확인

mysql> show global variables like 'wait%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| wait_timeout  | 28800 |

+---------------+-------+

1 row in set (0.00 sec)

mysql> 

28800 으로 되어 있는 것을 60으로 바꾸자

mysql> set @@global.wait_timeout = 60;

변경되었는지 확인

mysql> show global variables like 'wait%';

+---------------+-------+

| Variable_name | Value |

+---------------+-------+

| wait_timeout  |  60   |

+---------------+-------+

1 row in set (0.00 sec)

mysql> 

변경되어 있다.

3. unauthenticated user 삭제

processlist 에서 unauthenticated user 사용자 리스트를 삭제하는 텍스트 파일로 저장하고

mysql> select concat('KILL ', ID, ';') from information_schema.processlist where user='unauthenticated user' and command='query'  into outfile '/tmp/kill_list.txt';

한번에 실행시킨다

mysql> \. /tmp/kill_list.txt