Database JUNKY

MySQL,MariaDBを中心としたブログです

MySQL 各クライアントの接続数を簡単に集計する

MySQLのプロセス状況を確認するには、show processlist コマンドで確認することはできるのですが、じゃあ、各接続クライアント毎のサマリを出したい場合はどうすんの?と言ったときに役立つシェルを作ってみました。

結局、show processlistで取得される結果は、information_schema.processlistにあります。このinformation_schema.processlist の定義はこんな感じになっております、あぁ、テンポラリテーブルなのね。ということでこの部分の説明は割愛です。 [SQL] CREATE TEMPORARY TABLE PROCESSLIST ( ID bigint(4) NOT NULL DEFAULT '0', USER varchar(16) NOT NULL DEFAULT '', HOST varchar(64) NOT NULL DEFAULT '', DB varchar(64) DEFAULT NULL, COMMAND varchar(16) NOT NULL DEFAULT '', TIME int(7) NOT NULL DEFAULT '0', STATE varchar(64) DEFAULT NULL, INFO longtext ) ENGINE=MyISAM DEFAULT CHARSET=utf8 [/SQL]

今回の例では、SQLが実行中だかスリープなんだかはあまり関係なく、単純に各クライアントがどれくらいのコネクションを保有しているのか確認したかっただけなので、USER、HOST,DBだけ分かればいいや(^_^)・・なんて感じで作ったのが以下のシェルです。

[SQL] ELECT X.USER, X.HOST, X.DB, COUNT(X.ID) AS CNT FROM ( SELECT USER, REPLACE(HOST,MID(HOST,INSTR(HOST,':'),LENGTH(HOST)),'') AS HOST, DB, ID FROM INFORMATION_SCHEMA.PROCESSLIST ) AS X GROUP BY X.USER, X.HOST, X.DB ORDER BY X.HOST ASC ; [/SQL]

SQL分中の下記箇所は、ポート番号の指定ぽいので、削除してます。(必要な人は必要) [SQL] REPLACE(HOST,MID(HOST,INSTR(HOST,':'),LENGTH(HOST)),'') AS HOST [/SQL]

んで、上記のSQLをSHELLに含めめるとこんな感じになります。

[SHELL]

cat ./mysql_process_summary.sh

!/bin/bash

DATE=date +"%Y%m%d" HOSTNAME=hostname -a

DB Config

DBUSER=mysqluser DBPASSWD=mypassword

mysql -u ${DBUSER} -p${DBPASSWD} << EOF SELECT X.USER, X.HOST, X.DB, COUNT(X.ID) AS CNT FROM ( SELECT USER, REPLACE(HOST,MID(HOST,INSTR(HOST,':'),LENGTH(HOST)),'') AS HOST, DB, ID FROM INFORMATION_SCHEMA.PROCESSLIST ) AS X GROUP BY X.USER, X.HOST, X.DB ORDER BY X.HOST ASC ; EOF

exit 0 [/SHELL]

でわ、shellを実行してみましょう、以下のような結果になりました。各列は、左から順に、接続ユーザー名、接続元ホスト名、スキーマ名、接続カウント数になります。

[SHELL]

./mysql_process_summary.sh

user host db cnt myuser 192.168.101.XXX db01 2 myuser 192.168.101.XXX db01 1 myuser 192.168.101.XXX db01 2 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 5 myuser 192.168.101.XXX db01 5 slave 192.168.101.XXX NULL 1 myuser 192.168.101.XXX db01 5 myuser 192.168.101.XXX db01 5 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 2 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 10 myuser 192.168.101.XXX db01 5 myuser 192.168.101.XXX db01 5 myuser 192.168.101.XXX db01 2 myuser 192.168.101.XXX db01 2 myuser 192.168.101.XXX db01 2 sysopr localhost NULL 1 [/SHELL]

単純なSQLなんですが、ちょっとした調査にはとても便利!!