MySQL リンクを表示し、異常なリンクを削除する方法

序文：

データベースの運用や保守の際には、リンクの総数がいくつあるか、アクティブなリンクがいくつあるか、実行に時間がかかりすぎるリンクはないかなど、データベースの接続状態に注意を払うことがよくあります。さまざまなデータベースの異常も、リンクの状態を通じて間接的に反映される可能性があります。特に、データベースがデッドロックしたり、ひどくスタックしたりした場合は、まずデータベースに異常なリンクがあるかどうかを確認し、これらの異常なリンクを削除する必要があります。この記事では、主にデータベースリンクを表示する方法と異常なリンクを削除する方法について説明します。

1. データベースリンクを表示

データベースリンクを表示するために最もよく使用されるステートメントは show processlist です。これにより、データベース内に存在するスレッドのステータスを表示できます。通常のユーザーは現在のユーザーが開始したリンクのみを表示できますが、PROCESS グローバル権限を持つユーザーはすべてのユーザーのリンクを表示できます。

show processlist の結果の Info フィールドには、各ステートメントの最初の 100 文字のみが表示されます。さらに情報を表示する必要がある場合は、show full processlist を使用できます。同様に、information_schema.processlist テーブルを表示することで、データベース接続ステータス情報を表示できます。

# 通常のユーザーは、現在のユーザーによって開始されたリンクのみを表示できます。mysql> select user();
+--------------------+
| ユーザー() |
+--------------------+
| テストユーザー@localhost |
+--------------------+
セット内の 1 行 (0.00 秒)

mysql> 権限を表示します。
+----------------------------------------------------------------------+
| testuser@% への権限付与 |
+----------------------------------------------------------------------+
| 'testuser'@'%' に *.* の使用権限を付与 |
| `testdb`.* に対する SELECT、INSERT、UPDATE、DELETE 権限を 'testuser'@'%' に付与します |
+----------------------------------------------------------------------+
セット内の 2 行 (0.00 秒)

mysql> プロセスリストを表示します。
+--------+-----------+-----------+---------+--------+---------+----------+------------------+
| ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 |
+--------+-----------+-----------+---------+--------+---------+----------+------------------+
| 769386 | testuser | localhost | NULL | スリープ | 201 | | NULL |
| 769390 | testuser | localhost | testdb | クエリ | 0 | 開始 | プロセスリストを表示 |
+--------+-----------+-----------+---------+--------+---------+----------+------------------+
セット内の 2 行 (0.00 秒)

mysql> information_schema.processlist から * を選択します。
+--------+-----------+-----------+---------+--------+----------+-----------+----------------------------------------------+
| ID | ユーザー | ホスト | DB | コマンド | 時間 | 状態 | 情報 |
+--------+-----------+-----------+---------+--------+----------+-----------+----------------------------------------------+
| 769386 | testuser | localhost | NULL | スリープ | 210 | | NULL |
| 769390 | testuser | localhost | testdb | クエリ | 0 | 実行中 | information_schema.processlist から * を選択 |
+--------+-----------+-----------+---------+--------+----------+-----------+----------------------------------------------+
セット内の 2 行 (0.00 秒)

# PROCESS 権限を付与すると、すべてのユーザーのリンクが表示されます。mysql> grant process on *.* to 'testuser'@'%';
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

mysql> 権限をフラッシュします。
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

mysql> 権限を表示します。
+----------------------------------------------------------------------+
| testuser@% への権限付与 |
+----------------------------------------------------------------------+
| *.* のプロセスを 'testuser'@'%' に許可 |
| `testdb`.* に対する SELECT、INSERT、UPDATE、DELETE 権限を 'testuser'@'%' に付与します |
+----------------------------------------------------------------------+
セット内の 2 行 (0.00 秒)

mysql> プロセスリストを表示します。
+--------+----------+--------------------+---------+--------+---------+------------------+
| ID | ユーザー | ホスト | db | コマンド | 時間 | 状態 | 情報 |
+--------+----------+--------------------+---------+--------+---------+------------------+
| 769347 | ルート | ローカルホスト | testdb | スリープ | 53 | | NULL |
| 769357 | ルート | 192.168.85.0:61709 | NULL | スリープ | 521 | | NULL |
| 769386 | testuser | localhost | NULL | スリープ | 406 | | NULL |
| 769473 | testuser | localhost | testdb | クエリ | 0 | 開始 | プロセスリストを表示 |
+--------+----------+--------------------+---------+--------+---------+------------------+
セット内の 4 行 (0.00 秒)

show processlist の結果を通じて、各スレッドリンクの詳細情報を明確に把握できます。特定のフィールドの意味は比較的簡単に理解できます。各フィールドが何を表しているかの詳細な説明は次のとおりです。

Id: このリンクの一意の識別子です。この Id 値と kill コマンドを使用して、このリンクを強制終了できます。
ユーザー: このリンクを開始したユーザー名を指します。
ホスト: リクエストを送信するクライアントの IP とポート番号を記録します。これにより、クライアントのどのプロセスがリクエストを送信したかを特定できます。
db: コマンドが現在実行されているデータベース。データベースが指定されていない場合、この値は NULL になります。
コマンド: スレッドリンクが現在実行しているコマンドを指します。
時間: スレッドリンクが現在の状態にある時間を示します。
状態: コマンドに対応するスレッドの状態。
情報: スレッドによって実行された特定のステートメントを記録します。

データベースリンクが多すぎると、特定のユーザーまたは特定のステータスのリンクのみを確認したい場合など、有用な情報をフィルタリングするのが面倒になります。この時点で、show processlist を使用すると、必要のない情報が見つかります。この時点で、information_schema.processlist を使用してフィルタリングすると、はるかに簡単になります。次に、いくつかの一般的なフィルタリング要件を示します。

# 特定の ID のリンク情報のみを表示します。 select * from information_schema.processlist where id = 705207;

# 特定のユーザーのリンクを除外する select * from information_schema.processlist where user = 'testuser';

# アイドル状態ではないリンクをすべて除外します。select * from information_schema.processlist where command != 'Sleep';

# アイドル時間が 600 秒を超えるリンクを除外します。select * from information_schema.processlist where command = 'Sleep' and time > 600;

# 特定の状態のリンクを除外する select * from information_schema.processlist where state = 'Sending data';

# 特定のクライアント IP のリンクをフィルタリングします。select * from information_schema.processlist where host like '192.168.85.0%';

2. データベースリンクを削除する

データベースリンクが異常な場合は、kill ステートメントを使用してリンクを強制終了できます。kill の標準構文は次のとおりです: KILL [CONNECTION | QUERY] processlist_id;

KILL ではオプションの CONNECTION または QUERY 修飾子を使用できます。

KILL CONNECTION は修飾子なしの KILL と同じで、プロセスに関連付けられたリンクを終了します。
KILL QUERY は、リンク上で現在実行中のステートメントを終了しますが、リンク自体はそのまま残ります。

リンクを削除する機能は SUPER 権限に依存します。

SUPER 権限を持っていない場合は、現在のユーザーによって開始されたリンクのみを削除できます。
SUPER 権限を持つユーザーはすべてのリンクを削除できます。

緊急事態が発生し、リンクを一括で削除する必要がある場合は、SQL を連結して削除ステートメントを取得し、それを実行できます。これは非常に便利です。リンクを削除するために使用できる SQL ステートメントをいくつか示します。

# 600 秒以上アイドル状態になっているリンクを強制終了し、それらを連結して kill ステートメントを取得します。select concat('KILL ',id,';') from information_schema.`processlist`
 ここで、command = 'Sleep'、time > 600;

# 特定の状態のリンクを削除し、それらを連結して kill ステートメントを取得します。select concat('KILL ',id,';') from information_schema.`processlist`
 ここで、state = 'データを送信中';

information_schema.`processlist` から concat('KILL ',id,';') を選択します。
 ここで、state = 'テーブル メタデータ ロックを待機しています';

# ユーザーが開始したリンクを強制終了し、kill ステートメントを連結します。select concat('KILL ',id,';') from information_schema.`processlist`
  ユーザー = 'testuser';

kill ステートメントは注意して使用する必要があることに注意してください。特に、このリンクが更新ステートメントまたはテーブル構造変更ステートメントを実行する場合、リンクを強制終了するには比較的長いロールバック操作が必要になることがあります。