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ファイル作られていることが分かります。
削除するには直接ファイルを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)
以上です。