MySQLのエラー『Too Many Connections』の解決方法

MySQLで『Too Many Connections』エラーが発生すると、新しい接続を確立できず、データベースにアクセスできなくなります。このエラーは、MySQLの接続数制限を超えた場合に発生します。この記事では、エラーの発生条件と具体的な解決策を詳しく説明します。

1. エラーの発生条件

『Too Many Connections』エラーは、以下の状況で発生します。

• MySQLの接続数上限（max_connections）を超えた。
• アプリケーションの接続管理が適切に行われていない。
• 長時間アイドル状態の接続が解放されていない。
• クエリの実行が遅く、接続が滞留している。
• ボットや悪意のあるアクセスによる過剰な接続が発生している。

2. 現在の接続数を確認する

現在の接続状況を確認するには、以下のSQLを実行します。

SHOW STATUS WHERE Variable_name = 'Threads_connected';

また、最大接続数の設定を確認するには、以下のコマンドを実行します。

SHOW VARIABLES LIKE 'max_connections';

3. 一時的に接続数の上限を増やす

すぐに解決したい場合、MySQLの設定を変更して接続数の上限を増やします。

SET GLOBAL max_connections = 500;

ただし、この設定はMySQLの再起動後にリセットされるため、永続化するにはmy.cnf（またはmy.ini）に設定を追加します。

[mysqld]
max_connections = 500

変更を反映させるために、MySQLを再起動します。

systemctl restart mysql

4. 長時間アイドル状態の接続を切断する

長時間アクティブでない接続を強制的に切断するには、wait_timeout の設定を変更します。

SET GLOBAL wait_timeout = 60;

この設定を永続化するには、my.cnfに以下を追加します。

[mysqld]
wait_timeout = 60

5. 現在の接続一覧を確認し、不必要な接続を切断する

MySQLに接続しているプロセスを一覧表示するには、以下のコマンドを実行します。

SHOW PROCESSLIST;

不要な接続を手動で終了するには、KILL コマンドを使用します。

KILL 12345;

6. 連続接続制限を設定する

1つのユーザーが過剰に接続を行わないように、max_user_connectionsを設定します。

SET GLOBAL max_user_connections = 50;

永続化するには、my.cnf に以下を追加します。

[mysqld]
max_user_connections = 50

7. アプリケーション側の接続管理を見直す

アプリケーションが不要な接続を開き続けないように、以下の対策を行います。

• コネクションプールを使用して接続を再利用する。
• データベース接続を適切にクローズする。
• トランザクションを適切に終了させる。

例えば、PHPでは以下のように接続を明示的に閉じることができます。

$pdo = new PDO('mysql:host=localhost;dbname=test', 'user', 'password');
$pdo = null; // 接続を閉じる

8. MySQLの負荷を分散する

接続数を分散させる方法として、以下の対策を検討します。

• リードレプリカを導入して、読み取り専用クエリを別サーバーに分散する。
• ロードバランサを使用して複数のMySQLサーバーに負荷を分散する。
• キャッシュ（RedisやMemcached）を活用して、データベースへの問い合わせ回数を減らす。

9. 不正アクセスやボットを制限する

大量の接続がボットや攻撃によるものの場合、ファイアウォールやfail2banを使って制限をかけます。

iptables -A INPUT -p tcp --dport 3306 -s 192.168.1.100 -j ACCEPT
iptables -A INPUT -p tcp --dport 3306 -j DROP

10. MySQLのメモリ設定を最適化する

MySQLの接続数が増えるとメモリ消費も増加します。システムのメモリ使用状況を確認し、必要に応じて調整します。

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

必要に応じて、バッファプールサイズを増やします。

SET GLOBAL innodb_buffer_pool_size = 512M;

永続化するには、my.cnf に以下を追加します。

[mysqld]
innodb_buffer_pool_size = 512M

11. 定期的に不要な接続を整理する

シェルスクリプトを作成し、定期的に不要な接続を整理することもできます。

#!/bin/bash
mysql -u root -p'yourpassword' -e "SHOW PROCESSLIST" | grep Sleep | awk '{print "KILL "$1";"}' | mysql -u root -p'yourpassword'

このスクリプトをcronに登録すると、定期的にアイドル接続を削除できます。