MySQL에서 "Too many connections"에러가 나왔을 때의 대처법

MySQL에서 "Too many connections"에러가 나왔을 때의 대처법을 적어 둡니다.

MySQL에 접속하여 가서 다음과 같은 오류 메시지가 표시됩니다.

ERROR 1040 (HY000): Too many connections

 

위의 오류가 발생하면 원인은 크게 2 가지가 있습니다.

• DB에 동시 접속 제한
• 바이너리 로그를 통해 스토리지의 압박

 

DB에 동시 접속 제한

요약

현재 MySQL에 동시에 연결되어있는 수를 초과했기 때문입니다.

 

대처법

먼저 현재 발행되고있는 프로세스의 상태를 확인합니다.

MySQL에 대해 다음 쿼리를 실행하십시오.

mysql> show processlist; +-----+------+-----------+--------+-------------+--------+-------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----+------+-----------+--------+-------------+--------+-------+------------------+ | 5 | user | localhost | sample | Sleep | 244 | | NULL | | 6 | user | localhost | sample | Query | 1827 | init | commit | | 50 | user | localhost | sample | Sleep | 3308 | | NULL | | 51 | user | localhost | sample | Query | 0 | init | show processlist | | 425 | user | localhost | sample | Query | 1626 | init | COMMIT | | 494 | user | localhost | sample | Query | 1378 | init | COMMIT | | 513 | user | localhost | sample | Query | 1257 | init | COMMIT | +------+--------+-------------+-------+-------------+--------+-------+------------------+ 7 rows in set (0.00 sec)

Time이 긴 것이나 Command가 Sleep되어있는 것들을 대상 프로세스를 죽일 수에서 체류가 해소되고, 신규로 DB에 연결 할 수 있습니다.

 

프로세스 킬은 위에서 조사한 목록 중 대상 프로세스의 ID를 지정 킬 수 있습니다.

mysql> kill 5; Query OK, 0 rows affected (0.00 sec) mysql> kill 50; Query OK, 0 rows affected (0.00 sec) mysql> kill 1626; Query OK, 0 rows affected (0.00 sec)

 

[보충] 최대 동시 연결 수 설정 변경

위의 대처법을 실시하고 잠정적으로 해소했다고해도 다시 액세스 초과되어 DB에 연결할 수 없습니다되자도 모릅니다.

그럴 때는 동시 연결 수의 상한을 확인하고, MySQL의 설정에서 최대 동시 연결 수를 변경해야합니다.

현재 설정을 확인하려면 다음 SQL을 실행하여보십시오.

-- 現状の設定を確認 mysql> show global variables like 'max_connections'; +-----------------+-------+ | Variable_name | Value | +-----------------+-------+ | max_connections | 151 | +-----------------+-------+ 1 row in set (0.00 sec)

 

설정을 변경할 때는 다음 SQL을 실행하면 OK입니다.

-- 設定の変更 mysql> set global max_connections = 200; Query OK, 0 rows affected (0.00 sec)

 

바이너리 로그를 통해 스토리지의 압박

요약

binlog 등의 바이너리 로그의 건수가 매우 많다 / var / lib / mysql 스토리지의 용량을 초과하는 로그 파일이 출력되는 것으로, 디렉토리를 압박하고있는 케이스입니다.

바이너리 로그는 데이터의 등록과 업데이트 등 DB에 작업 이력을 보유하고 있으며, 뭔가 오류가 발생했을 때 발생 이전의 상태로 복구 할 경우에 사용합니다.

실제로 나의 경우는 DB에 대량의 INSERT 문이나 UPDATE 문을 흘려 버리고 로그 파일의 크기가 커져 버린 것이 원인이었습니다.

 

바이너리 로그의 위치는 "my.cnf"에 기재되어 있습니다.

# my.cnf [mysqld] log_bin=log-bin=/var/log/mysql/bin_log/mysql-bin-log

"log-bin = / var / log / mysql / bin_log / mysql-bin-log"라고되어 있다면, "/ var / log / mysql / bin_log"디렉토리 바로 아래에,

　　mysql-bin-log.000001 
　　mysql-bin-log.000002 
　　mysql-bin-log.000003 
　　mysql-bin-log.000004 
　　...

등등 일련 번호로 이름이 매겨져 일정한 용량별로 구분하여 작성됩니다.

log_bin 설정이 기재되어 있지 않은 경우는 바이너리 로그가 생성되지 않습니다 유의하십시오.

 

대처법

바이너리 로그의 상태 확인은 다음과 같이 실시합니다.

mysql> show master logs; +------------------+------------+ | Log_name | File_size | +------------------+------------+ | mysql-bin.000001 | 654 | | mysql-bin.000002 | 2030 | | mysql-bin.000003 | 23908332 | ・・・ | mysql-bin.000019 | 877177933 | | mysql-bin.000020 | 3725 | +------------------+------------+ 20 rows in set (0.00 sec)

 

위의 결과로부터, 현재는 바이너리 로그가 20 파일 만들어져있는 것을 알 수 있습니다.

삭제하려면 직접 파일을 rm 명령으로 삭제하는 것이 아니라 SQL 쿼리를 실행합니다 .

이번에는 모든 바이너리 로그를 삭제하기 때문에 다음과 같이 "mysql-bin.000020"을 지정하여 이전의 바이너리 로그가 모두 삭제됩니다.

mysql> purge master logs to 'mysql-bin.000020';

 

[추가] 복제 구성의 바이너리 로그 삭제

바이너리 데이터 삭제시주의가 필요합니다. 복제가되는 경우 SLAVE와 MASTER 간의 데이터 연계가 중단되는 경우가 있기 때문에 바이너리 로그를 모두 삭제 해 버리면 동기화가 끊어져 버릴 우려가 있습니다.

그것을 방지하기 위해 우선 SLAVE 측에서 바이너리 로그가 어디까지 읽을 수 있는지 확인합니다.

mysql> SHOW SLAVE STATUS \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: user Master_Port: 3306 Connect_Retry: 3 Master_Log_File: mysql-bin.005 Read_Master_Log_Pos: 79 Relay_Log_File: mysql-relay-bin.009 Relay_Log_Pos: 548 Relay_Master_Log_File: mysql-bin.005 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 79 Relay_Log_Space: 552 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 8 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec)

위의 결과에서 확인하고자하는 열의 " Master_Log_File "입니다. "Master_Log_File"는 현재 SLAVE 측에서 읽고있는 바이너리 로그되므로이를 이전 데이터를 삭제하면 영향을 최소화 할 수 있습니다.

 

대상의 바이너리 로그를 삭제합니다.

mysql> purge master logs to 'mysql-bin.000005';

 

성공적으로 제거되었는지 확인한다.

mysql> show master logs; +------------------+------------+ | Log_name | File_size | +------------------+------------+ | mysql-bin.000005 | 463467 | | mysql-bin.000006 | 468756 | | mysql-bin.000007 | 54532443 | ・・・ | mysql-bin.000019 | 877177933 | | mysql-bin.000020 | 3725 | +------------------+------------+ 16 rows in set (0.00 sec)