Pythonで作られた便利なコマンドラインツール MySQL Utilities

「MySQL Utilities」とは?

MySQL UtilitiesはPython言語により作成された便利なコマンドラインツール群です。MySQL Workbenchに付属する形で公開され、現在はMySQL Workbenchから独立する形で配布されています。そしてMySQLサーバーへの接続にはConnector/Pythonが利用されています。
LinuxをはじめとするPythonの環境が整っているプラットフォームではPythonスクリプトがそのまま提供され、Windowsのように標準的なPython環境を持たないプラットフォームではPythonのスクリプトをネイティブの実行形式（*.exe）に変換された形で配布されています。

MySQL Utilities自体はコマンドラインのツール群ですので、Workbenchの実行に必要な、比較的新しめのGUI環境は必要ありません。そのため、Workbenchがサポートするよりも、より多くのプラットフォームで単独利用が可能です。

MySQL Utilitiesコマンドラインツール一覧

MySQL Utilitiesは原稿執筆時点（2014-01）で、以下の20ユーティリティ＋実行環境（mysqluc: MySQL Utilities Client）の21ユーティリティから構成されています。

MySQL Utilitiesならではの注意点

MySQL Utilitiesは従来のコマンドラインツール群とは違い、以下のような記述で接続先を指定します。
これは、従来のコマンドラインツール群が主に1つのMySQLサーバーを対象として動作するものなのに対して、MySQL Utilitiesは2つ以上のMySQLサーバーを対象として動作するものが多いため、このような記法になっています。

[MySQL Utilitiesの記法]

--server=ユーザ名:パスワード@ホスト名:ポート番号

[MySQL コマンドラインツール群の記法]

--user=ユーザ名 --password=パスワード --host=ホスト名 --port=ポート番号

なおWindows環境ではローカルホストとしてlocalhostと127.0.0.1のどちらを指定しても同じですが、LinuxやUNIXではホスト名に対してlocalhostを指定するとUNIXドメインソケットを利用して接続を行います。そのため、localhostを指定する場合にはポート番号の代わりにソケット名を指定してください（例: --server=root:root@localhost:/var/lib/mysql/mysql.sock）。

コマンドがうまく動作しない場合、多くのコマンドでは-vオプションが使えますので、-vを追加のうえ再実行して動作の詳細を表示してください。またWindows環境のようにmysqlucから実行している場合には、show last errorにてエラーメッセージを調べることも可能です。

MySQL Utilitiesのインストール

MySQL UtilitiesはWorkbenchホーム画面右上（図1）のMySQL Utilitiesからクリック（もしくはメニューのTools-Start Shell for MySQL Utilitiesから）して起動します。

図1：MySQL Utilitiesの起動（クリックで拡大）

まだインストールされていなければインストールのガイダンスがエラーメッセージにて表示されるので、それに従ってインストールしてください。Windowsの場合はMSI形式のインストーラになります。

またLinuxではプラットフォーム別、もしくは一般的なLinuxに利用できるGeneric Linuxのパッケージを利用することになります。RHEL/Oracle Linuxにインストールする場合はrpmパッケージを以下のコマンドでインストールします。

shell> yum install mysql-utilities-1.3.6-1.el6.noarch.rpm

通常はデフォルトで入っているPythonで事足りるため、Connector/Pythonパッケージの追加インストールだけで利用できるようになります。

実際に利用してみよう

MySQLサーバーの情報を表示するmysqlserverinfoを実際に利用してみましょう。指定するオプションは--server=での接続先の指定と、表示を垂直に表示する--format=verticalの二つです（MySQLコマンドラインツールの\Gに相当します）。

mysqluc> mysqlserverinfo --server=root:root@localhost:3306 --format=vertical
  # Source on localhost: ... connected.
  *************************       1. row *************************
  server: localhost:3306
  config_file:
  binary_log:
  binary_log_pos:
  relay_log:
  relay_log_pos:
  version: 5.6.14-log
  datadir: C:\ProgramData\MySQL\MySQL Server 5.6\data\
  basedir: C:\Program Files\MySQL\MySQL Server 5.6\
  plugin_dir: C:\Program Files\MySQL\MySQL Server 5.6\lib\plugin\
  general_log: ON
  general_log_file: MEIJI-ACER-PC.log
  general_log_file_size:
  log_error: .\MEIJI-ACER-PC.err
  log_error_file_size:
  slow_query_log: OFF
  slow_query_log_file:
  slow_query_log_file_size:
  1 row.

データベース管理に便利なユーティリティ（対象のサーバーが一つ）

mysqldiskusage　データベースのディスク仕様量の表示

接続先を指定すれば、その接続先のデータベースディスク利用量を計算してくれます。以下が実行例です。

mysqluc> mysqldiskusage --server=root:root@localhost:3306
  # Source on localhost: ... connected.
  # Database totals:
  +---------------------+-------------+
  | db_name             |      total  |
  +---------------------+-------------+
  | mydb                | 406,979     |
  | mysql               | 1,645,921   |
  | performance_schema  | 510,023     |
  | sakila              | 30,495,478  |
  | test                | 123,270     |
  | world               | 471,349     |
  +---------------------+-------------+
Total database disk usage = 33,653,020 bytes or 32.09 MB
#...done.

各種ログ類のサイズも結果に含めることができます。バイナリログ（-b）, リレーログ（-r）, 一般ログ・スロークエリ・エラーログ（-l）, InnoDB関連（InnoDBログ、共有テーブルスペース）ファイル（-i）を個別に指定するか、-aですべてを表示するようにしてください。

ただし、図2のようにユーティリティをローカルで実行する場合とリモートで実行する場合には、取得できる情報に差があります。

図2：ローカルとリモートの差（クリックで拡大）

mysqlindexcheck 重複、もしくは冗長なインデックスをチェック

データベースのパフォーマンスアップにはインデックスの設計、追加が不可欠です。しかしながら大規模でテーブル数・カラム数が多い場合や、長期にわたって多人数によるメンテナンスが実施されたテーブルなどでは、重複したインデックスや、冗長なインデックスが作成されていることがよくあります。

本ユーティリティはそのチェックに利用できます。チェック内容は表とインデックスの定義から静的にチェックされ、あくまでもサジェスチョンであることにお気をつけください（すべてが正しいとは限りませんので、すべてに対処が必要とは限りません）。

例えば以下のような定義があるとき、idx2があればidx1は不要です。

create table t1(i1 int, v2 varchar(20)) engine=myisam;
  insert into t1 values(1,'Firebird'),(2,'MySQL'),(3,'PostgreSQL');
  create index idx1 on t1(i1);
  create index idx2 on t1(i1,v2);

このテーブルのインデックスチェックを、修正のためのDROP文も表示（-d）するオプション込みで行うと以下の実行例となります。

mysqluc> mysqlindexcheck --server=root:root@localhost:3306 -d test.t1
  # Source on localhost: ... connected.
  # The following index is a duplicate or redundant for table test.t1:
  #
  CREATE INDEX `idx1` ON `test`.`t1` (`i1`) USING BTREE
  #     may be redundant or duplicate of:
  CREATE INDEX `idx2` ON `test`.`t1` (`i1`, `v2`) USING BTREE
  #
  # DROP statement:
  #
  ALTER TABLE `test`.`t1` DROP INDEX `idx1`;
  #

結果としてidx1が冗長、もしくは重複としてカウントされ、DROP文が表示されます。

MySQLのインデックスについて詳細は米facebookの松信さんがサン・マイクロシステムズ勤務時代に作成した次のドキュメントが役立ちます。ご興味のあるかたはご参照ください。

＞　インデックスを使いこなす

また参考文献にあげた拙書や奥野さんの本にもインデックスの説明と、その効果を確認するためのEXPLAIN句の読み方の情報があります。

mysqlmetagrep　メタデータの検索

データベースオブジェクト（'database', 'trigger', 'user','routine', 'column', 'table', 'partition', 'event', 'view'）から指定パターンのオブジェクトを検索します。grepと名前がついていますが、デフォルトの検索はLIKEにより行われます。

例えばTで始まるデータベースから、Tで始まるテーブル、ビューを検索するには以下の実行例で検索します。

mysqluc> mysqlmetagrep --server=root:root@localhost:3306 -e 't%' --search-objects=table,view --database='t%' --format=vertical
  *************************       1. row *************************
  Connection: root:*@localhost:3306
  Object Type: TABLE
  Object Name: t1
  Database: test
  Field Type: TABLE
  Matches: t1
  1 row.

検索対象のデータベースオブジェクト種別は上記のように--search-objectsオプションにカンマで区切って指定できます。
--search-objectsを指定しない場合は、すべてのデータベースオブジェクトがリストアップされ大変なことになります。

またストアドルーチン、トリガ、イベントのBODY部分も検索対象にしたい場合には-bオプションもあわせて指定してください。

mysqlprocgrep　プロセス情報の検索

MySQLではクライアントからの各接続の状態をSHOW FULL PROCESSLIST;（もしくは、同内容をSQLにて絞り込める情報スキーマ（I_S）のPROCESSLISTテーブル）にて確認できますが、mysqlprocgrepはI_Sと同等の絞り込みに加えてアクション（クエリのKILL, 接続のKILL）を行えるようにしたものです。

例えば現在の処理を4時間（14400秒）より長く続けている接続の一覧を取得する場合、I_Sの場合は以下のクエリにて求めます。

mysql> select * from information_schema.processlist where time > 14400;

同じ処理をmysqlprocgrepで行うには以下の実行例にて検索します。

mysqluc> mysqlprocgrep --server=root:root@localhost:3306 --age=4:00:00
  +------------------------+-----+-------+------------------+-------+----------+--------+--------+-------+
  | Connection             | Id  | User  | Host             | Db    | Command  | Time   | State  | Info  |
  +------------------------+-----+-------+------------------+-------+----------+--------+--------+-------+
  | root:*@localhost:3306  | 13  | root  | localhost:53549  | None  | Sleep    | 27634  |        | None  |
  | root:*@localhost:3306  | 36  | root  | localhost:57658  | test  | Sleep    | 17770  |        | None  |
  +------------------------+-----+-------+------------------+-------+----------+--------+--------+-------+

Commandで絞り込むには--match-commandオプション、Stateで絞り込むには--match-stateオプションで絞り込めます。またそのほかの項目（id,user,host,db,info）でも同様の書式（--match-項目）で絞り込めます。
絞り込んだ結果、当該クエリのKILLには--kill-queryオプション、当該接続のKILLには--kill-connectionオプションを指定します。

例えば、CommandがSleepになって4時間（14400秒）より長い時間経っている接続をKILLするには、以下のオプションにて実行します。

mysqluc> mysqlprocgrep --server=root:root@localhost:3306 --age=4:00:00 --match-command='Sleep' --kill-connection

データベース管理に便利なユーティリティ（対象のサーバーが複数）

--server=SERVER相当のオプションを二つ以上指定することにより、複数のサーバー（インスタンス）を対象として処理を行うことができるユーティリティを紹介します。

図3はmysqlservercloneで作成した新規のインスタンスと、そのオリジナルとなった既存のインスタンスの両方に、それぞれポート番号3307, 3306でアクセスするイメージ図です。

図3：2つのインスタンス（クリックで拡大）

mysqlserverclone 動作中のMySQLを元に別インスタンスを開始

現在すでに動作しているMySQLサーバーの複製を、同じサーバー上に作りたい場合にはmysqlservercloneが利用できます。

これは稼働しているMySQLに接続して同じバイナリを用いて違うインスタンス（datadir, port, socket等が違う）を生成します。コマンドの名称はクローン（clone）となっていますが、その名称の印象からうける以下のような処理は行われません。

• クローン元からデータベースオブジェクト（表、ビュー、プロシジャ、トランザクション、等）は引き継がない。あくまで新規のMySQLインスタンスとなる。
• my.cnfは引き継がない。デフォルトでは--no-defaults指定となり、mysqlclonecommandのオプションで暗黙的に指定されたもの（--new-dataで指定されたものが--datadirになる）や、明示的に指定されたもの（--mysqld=明示的なオプション）が指定される。

まずは以下のコマンドで新しいMySQLサーバーのインスタンスを作製してみましょう。

mysqluc> mysqlserverclone --server=root:root@localhost:3306 --new-data=c:/users/meiji/data2 --root-password=root -w c:/users/meiji/start.txt
  # Cloning the MySQL server running on localhost.
  # Creating new data directory...
  # Configuring new instance...
  # Locating mysql tools...
  # Setting up empty database and mysql tables...
  # Starting new instance of the server...
  # Testing connection to new instance...
  # Success!
  # Setting the root password...
  # Connection Information:
  #  -uroot -proot --port=3307
  #...done.

他のコマンドプロンプトから以下のコマンドラインで新しいインスタンスに接続できます。

mysql -uroot -proot --port=3307

また --server2=root:root@localhost:3307 とすると、MySQL Utilitiesの2つ目のサーバーとして指定できます。

mysqldbcopy　サーバー間でのデータベースのコピー

mysqlindexcheckの際に作ったテーブル含むtestデータベースを同じ名称で、新しいインスタンスにコピーしてみましょう。以下のコマンドでコピーできます。

mysqluc> mysqldbcopy --source=root:root@localhost:3306 --destination=root:root@localhost:3307 test
  # Source on localhost: ... connected.
  # Destination on localhost: ... connected.
  # Copying database test
  # Copying TABLE test.t1
  # Copying GRANTS from test
  # Copying data for TABLE test.t1
  #...done.

コピー元に対するロックは--locking=snapshotがデフォルトとなっています（mysqldumpの--single-transactionと同じ）。すべてをロックするには--locking=lock-all、ロックを取得しない場合には--locking=no-locksを指定してください。

直接2つのインスタンスに接続できない場合には、mysqldbexportとmysqldbimportの組み合わせ利用をご検討ください。

mysqldiff　データベースオブジェクト定義の比較

mysqldiffはデータベースオブジェクトの定義比較を行います。先ほどコピーした内容を比較してみましょう。

mysqluc> mysqldiff --server1=root:root@localhost:3306 --server2=root:root@localhost:3307 test:test
  # server1 on localhost: ... connected.
  # server2 on localhost: ... connected.
  # Comparing `test` to `test`                                       [PASS]
  # Comparing `test`.`t1` to `test`.`t1`                             [PASS]
  Success. All objects are the same.

今回では定義は同一でしたが、差分が出た時、デフォルトではserver1を基準として差分が表示されます。

server2を基準とする場合には--cahnges-for=server2を指定してください。また差分取得に失敗すると処理は途中で打ち切られます。最後まで実行するには--foreceオプションを指定して実行してください。

mysqldbcompare　データベース比較

表の場合mysqldiffは表の定義だけを比較しますが、mysqldbcompareは定義以外にも件数や内容も確認します。その確認のため、主キーを各テーブルに設定してください。主キーが設定されていない場合には、比較の一部がスキップされ、結果として比較が失敗します。

以下は主キーがないためデータのチェック（Data Check）がスキップされ、比較が失敗する例です。

mysqluc> mysqldbcompare --server1=root:root@localhost:3306 --server2=root:root@localhost:3307 test:test
  # server1 on localhost: ... connected.
  # server2 on localhost: ... connected.
  # Checking databases test on server1 and test on server2
  #
  #                                                   Defn    Row     Data
  # Type      Object Name                             Diff    Count   Check
  # -------------------------------------------------------------------------
  # TABLE     t1                                      pass    pass    SKIP
  #
  # No primary key found.

  # Database consistency check failed.
  #
  # ...done

server1, server2の双方にalter table t1 add primary key(i1);を実行すると、Data Checkも行われます。

件数や内容の確認には、すべてのレコードを確認する必要があるので、長大なテーブルを確認するのには、それ相応の時間がかかることにご注意ください。
mysqldbcompareは比較して不一致があるとエラー終了してしまうため、最後までチェックするには-aオプションを指定して実行すると良いでしょう。

その他のユーティリティ

mysqlrpl*のユーティリティはレプリケーション構成のMySQLサーバーに役立つツール群です。これらのツールの動作確認にはmysqlservercloneとmysqlreplicationの組み合わせでレプリケーション構成を作成して確認できますが、複雑な構成の構築には結構手間がかかります。このような場合はMySQL SandboxというPerlのツール群にて構築すると、簡単にさまざまな構成のレプリケーションが作成できます。

• ＞　MySQL Sandboxオフィシャルページ
• ＞　気軽に新しいMySQLを試してみる: MySQL Sandboxのススメ

また監視するレプリケーションがすべてMySQL 5.6.x以降のバージョンで構成され、GTID（Global Transaction ID）が設定（gtid_mode=ON）されている場合には、Master:Slave = 1:n （nは2以上）のレプリケーション構成で監視と自動フェイルオーバーを行えるmysqlfailoverを利用することができます。利用方法は複雑であるため次のマニュアルをよくご確認のうえご利用ください。

＞　5.9.1. mysqlfailover — Automatic replication health monitoring and failover

残念ながらMySQL 5.5以前のバージョンではGTIDの機能がないため、mysqlfailoverは利用できません。その場合は米facebookの松信さんがDeNA勤務時代に開発したPerlスクリプト群であるMHA（Master High Availability manager and tools） for MySQLのご利用を検討されてはいかがでしょうか?

MySQL-MHAの詳細は次のドキュメントをご参照ください。

＞　Announcing MySQL-MHA: "MySQL Master High Availability manager and tools"

MySQL-MHAについては参考文献にあげたRonald/Chrisの本にも説明があります。

さて次回は、Workbenchの商用版にのみ追加されている機能と、その他商用版のMySQLに付属する便利なツールについて説明したいと思います。

【参考文献】

• 木村明治『プロになるためのデータベース技術入門 MySQL for Windows 困ったときに役立つ開発・運用ガイド』技術評論社（発行年：2012）
• 奥野幹也『エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド』技術評論社（発行年：2010）
• Charles Bell『Expert MySQL SECOND EDITION』Apress（発行年：2012）
• Ronald Bradford/Chris Schneider『Effective MySQL Replication Techniques in Depth』Oracle Press（発行年：2012)