MySQLを管理する場合、phpMyAdminが使えないサーバーを管理することもあるので、ターミナルなどから使えるコマンドもマスターしましょう。今回はそういった方のために、MySQLをターミナルからコマンドで使う方法を解説します。
目次
MySQLをコマンドラインから操作する
オープンソースのデータベースとして多くのWebシステムで利用されているMySQLですが、コマンドラインからも操作できるのをご存知でしょうか。なお、MySQLには、Webブラウザから操作できる便利なphpMyAdminがあるので、わざわざコマンドラインから実行しなくても大抵のことができます。しかし、データベースを管理することになったら、コマンドラインからの操作が必要なケースが発生します。
そこで、まずは、コマンドラインからMySQLを操作するための基本についてご紹介します。
MySQLのクライアントプログラムとは何か
MySQLもそうですが、SQLで操作するデータベース管理ツールは、コンピューターに常駐するサーバープログラムがデータベースを管理します。そして、このサーバープログラムには、予め準備されたネットワークポートにアクセスしたり、LinuxなどのOSで実現されるプロセス間通信などで、SQLを受け付けます。
なお、よく利用されるMySQLの管理ツールであるphpMyAdminは、MySQLのサーバーがWebサーバーと通信することで、Webブラウザに必要な情報を表示したり、データの追加や修正等を実施します。
では、コマンドラインから直接MySQLのサーバーと通信するにはどうすればよいでしょうか。この場合に使われるのが、MySQLのクライアントプログラムです。
MySQLクライアントプログラムをインストールする
代表的なLinuxであるDebian, Ubuntu、Red Hat Enterprise Linux, CentOSなどにMySQLをインストールする場合、aptコマンドやdnf, yumコマンドを使うのが一般的です。そして、こういったコマンドでMySQL、または、互換性のあるmariadbをインストールすると、サーバー用のプログラムといっしょに、コマンドラインからSQLを実行するためのプログラムがインストールされます。
例えば、UbuntuでMySQLと互換性のあるmariadbのインストールに使われるパッケージは、次の3つです。
Ubuntu用mariadbインストールパッケージの例
mariadb-client-10.3
mariadb-common
mariadb-server-10.3
そのため、Linuxサーバーに、mariadbのサーバープログラムとクライアントプログラムを同時にインストールするには、次のコマンドを使用します。なお、以下の例で使用するaptコマンドはDevianやUbuntuのパッケージ管理コマンド、yumはRed Hat Enterprise LinuxやCentOSの管理用コマンドです。
Devian, Ubuntuの場合
sudo apt install mariadb-client
Red Hat Enterprise Linux, CentOSの場合
sudo yum insyall mariadb-client
コマンドラインから操作するには
MySQLのクライアントプログラムがインストールされると、それらのプログラムはターミナルから実行できます。もし、LinuxサーバーやMacから実行するなら、まずはターミナルを起動してください。
なお、MySQLのクライアント用プログラムは、mysqlです。次のように、キーボードから入力すると、このプログラムのバージョンが表示されます。
MySQLのクライアントプログラムが動作するか確認する実行例
mysql --version
また、クライアント用プログラムとして、mysqlのほかにも幾つかインストールしてあるので、必要に応じてそれらも利用します。
クライアントプロフラムmysqlを使う
先ほど紹介したように、MySQLのクライアントプログラムとしてよく使われるのが、mysqlコマンドです。なお、mysqlコマンドを実行すると、シェルのように動作し、キーボードからSQLを打ち込むと、そのSQLを処理した結果を返します。次から、このmysqlコマンドを使い方について解説します。
mysqlコマンドをターミナルで実行する
mysqlコマンドを利用する場合、通常、Linuxのターミナルなどで、オプションを指定して実行します。なお、mysqlにはたくさんのオプションが用意されており、必要に応じてそれらを指定しますが、ここでは個々のオプションの説明は省きます。興味のある方は、下記のWebサイトなどを参照してください。
MySQL :: MySQL 5.6 リファレンスマニュアル :: 4.5.1.1 mysql のオプション
そして、こらのオプションの中で、よく使われるのがユーザー名を指定するオプションとパスワードを指定するオプションです。次に、MySQLに登録してあるuserというユーザー名とパスワードを使ってMySQLのクライアントプログラムを実行する例を紹介します。
mysqlの実行例
$ mysql -u user -p Entrt password:
なお、パスワードは、次のようにも指定できます。
オプションでパスワードを指定して実行する例
$mysql -uuser -ppassword
mysqlコマンド内でSQLを使う
mysqlを実行すると、カーソルのある行の左側、すなわちプロンプトが変わります。これが変わったら、SQLが操作してデータベースを参照したり、データの登録や修正、さらにはテーブルの削除やファイルへの書き出しなどの管理コマンドが実行できます。
mysqlコマンドの実行例
$mysql -uuser -ppassword mysql>
この例では、ターミナルのプロンプト「$」からmysqlコマンドを実行して、プロンプトが「mysql>」に変わっています。この状態で、SQLを実行します。
また、mysqlを終了してシェルに戻るには、コントールキーとqを同時に押すか、exitはたはquitを実行します。
mysql> quit $
リダイレクトで実行する
mysqlコマンドを使ってSQLを実行する場合、キーボードから打ち込んで実行することもできますが、シェルのリダイレクトを使ってテキストファイルに書いたSQLの実行も可能です。
なお、シェルのリダイレクトとは、あるコマンドのターミナルへの出力を、別のコマンドへの入力に使う方法で、ファイルを指定して、それをあるコマンドの入力に使うことも可能です。そして、この機能を使うことで、ある時刻になったら、予め用意したSQLを自動で実行させることも可能です。
リダイレクトでmysqlを実行する例
$mysql -uuser -ppassword db1 log
この例では、db1というデータベースに対してsample.sqlに書かれたSQLを実行し、その結果をlogというファイルに出力しています。
mysqlコマンドをリモートで使うには
Webシステムによっては、Webサーバーとデータベースが別のサーバーで構成されており、MySQLを使っているのに、その管理にphpMyAdminが使えないケースもあります。その場合、直接データベースサーバーにログインして、mysqlコマンドを使う方法のが普通ですが、mysqlコマンドでリモート接続しての操作も可能です。
そして、mysqlコマンドをリモートで使うには、–hostオプション、または、-hオプションで接続するホスト名またはIPアドレスを指定します。
リモート接続で使う例
$ mysql -u user -p -h hostname
なお、クラウド上のサーバーに設定されたMySQLのサーバーに接続して操作するなら、暗号化が必須です。そして、MySQLは暗号化もサポートしており、サーバー側で設定しておけば、SSLによる暗号化による接続が可能です。ぜひ、活用してください。
暗号化でリモート接続する例
$mysql -u user -p ^h hostname --ssl-ca=ca-cert.pem
この例では、SSLのCA証明書、ca-cert.pemを指定してSSL通信でMySQLに接続しています。SSLの設定に関しては書きのURLなどを参考にしてください。
https://dev.mysql.com/doc/refman/5.6/ja/ssl-connections.html
MySQL :: MySQL 5.6 リファレンスマニュアル :: 6.3.10 セキュアな接続のための SSL の使用
mysqlからよく使われるコマンド
普段、phpMyAdminなどのGUIなデータベース管理ツールに慣れている方が、ターミナルからmysqlを使うと、不便だと感じることがたくさんあります。例えば、phpMyAdminなら、画面を見れば一目で解ることでも、いちいちキーボードから打ちこんで表示しなければなりません。mysqlに使い慣れた方も、同じように不便に感じていますが、コマンドを使って必要な情報を表示して使いこなしています。次から、mysqlコマンドを使った際に、キーボードから操作する際によく使われるコマンドをご紹介します。
一覧を表示する
先ほど例として紹介したように、データベースやテーブルの一覧は、phpMySQLなどのGUI管理ツールを使えば、簡単に見れやり、選んだりできるのですが、ターミナルから操作するには手で打ち込まなければなりません。そのため、一覧を表示するコマンドは、よく使われます。まずは、このコマンドをマスターして、いつでもデータベースやテーブルの一覧を表示できるようになりましょう。
なお、mysqlで一覧を表示するには、showを使います。そして、データベースとテーブルの一覧を表示するには次のように実行します。
データベースの一覧を表示する
mysql> show databases;
テーブルの一覧を表示する
mysql> show tables;
また、テーブルに定義された列の名称やデータの型を調べるには、次のコマンドを使います。
テーブルの構造をチェックする
mysql> desc テーブル名;
SQLの実行結果をテキストファイルに書き出す
ターミナルでSQLを実行して不便な点のもう一つは、ターミナルに表示できる文字が限られており、SQLで大きなデータを表示すると、スクロールして流れてしまったり、画面が乱れて読み取れなかったりする点です。そういった大きなデータをチェックするには、一旦ファイルに格納し、テキストエディタに表示して確認しましょう。
なお、mysqlコマンドには、SELECT文の実行結果をファイルに書き出す機能があります。使い方は簡単で、通常のSELECT文の後に「INTO OUTFILE ファイル名」と付けて実行するだけです。
MySQLで抽出結果をファイルに出力するSQLの書き方
SELECT ファイルに書き出したいフィールド名 FROM テーブル名 WHERE 条件 INTO OUTFILE ‘ファイル名’;
抽出結果をファイルに書き出す例
mysql> select * from 商品テーブル INTO OUTFILE '/home/user/test/out.txt';
この例では、アカウント「user」が書き込み権を持つ、/home/user/test/に、out.txtというファイル名で、SELECT文の結果を出力しました。このファイルは、パソコンなどに転送すれば、テキストエディタで詳しく解析できます。
MySQLを管理するたらコマンドでの操作もマスターしよう
MySQLには、phpMyAdminという優れたGUIの管理ツールがあるので、ターミナルで操作する機会は特別なケースがなければ無いかもしれません。しかし、ターミナルで操作した方が簡単でできるケースも少なくありません。MySQLのデータベースの管理を任されたら、phpMyAdminによるGUIの操作と、ターミナルからmysqlコマンドを使った操作の両方をマスターしておきましょう。
