MySQLでよく使用するコマンドまとめ - 2018年版

この記事は作成中です。随時更新されます

歳のせいかど忘れすることが増えたためMySQLの操作方法をまとめてメモ。MySQL5.6〜5.7あたりでの利用を想定しています。

インストール

セキュリティ関連

インストール完了後に以下のコマンドを実行する。

$ sudo mysql_secure_installation

実際に設定されるのは以下

  1. rootのパスワード変更
  2. パスワード検証プラグインを入れる
  3. ユーザー「anonymous」削除
  4. rootで外部からのログイン禁止
  5. データベース「test」削除

「パスワード検証プラグイン(validate_password)」は、本番運用する場合などは重宝するのですが、ローカル等で軽い作業や検証したい場合などは正直面倒ですw TPOによってON/OFFを。

検証パターンは3つありデフォルトはMEDIUMです。

ポリシー 概要
LOW 文字列長が8文字以上必要
MEDIUM 1つ以上のの数値文字、アルファベットの小文字および大文字、特殊文字(英数字以外)を含む必要がある
STRONG 4文字以上の部分文字列が、(辞書ファイルが指定された場合に) 辞書ファイル内の単語と一致してはならない

CLI

ここでは公式のCLIツールを用います。

ログイン

$ mysql -u (ユーザー名) -p
Enter password:

以下はオプションの一例。

オプション 説明
-h (ホスト名) 接続先のホストを指定。未指定時はlocalhost
-P (ポート番号) ポート番号を指定。未指定時は3306
-D (DB名) DB(スキーマ)名を指定。指定するとuseされた状態でログインできる

パスワードを起動時に直接指定することも可能だが、セキュリティ上よろしくないので怒られる。ログ(~/.bash_historyなど)に残るしショルダーハッキングされる恐れがあるので注意。

$ mysql -u foo --password=xxxxxxxx
mysql: [Warning] Using a password on the command line interface can be insecure.

ユーザー

新規作成

CREATE USER構文を使用します。

### 通常
mysql> CREATE USER '(ユーザー名)'@'(ホスト名など)' IDENTIFIED BY 'パスワード';

### パスワードなし
mysql> CREATE USER '(ユーザー名)'@'(ホスト名など)';

特殊な文字列を使わなければ引用符(シングルコーテーション)は不要。ホストの指定を行わない場合は '(ユーザー名)'@'%' と同じ意味になりどこからでログインできる状態になり危険、ホストの指定は忘れずに。

ホスト名には接続を許可するIPアドレスやFQDNなどを指定可能。ワイルドカードは '%'。以下は指定例です。

  • 192.168.1.1
  • localhost
  • foo.example.com
  • 192.168.1.%
  • %.example.com

複数のホストを許容したい場合は必要な数だけCREATE USER文を発行。ユーザー名が同じでもホストが異なると同姓同名の別人として扱われる点に注意。

mysql> CREATE USER 'foo'@'localhost' IDENTIFIED BY 'パスワード';
mysql> CREATE USER 'foo'@'example.com' IDENTIFIED BY 'パスワード';

一覧

現在登録されているMySQLのユーザーの一覧を表示するにはmysql.userテーブルに対して以下のようなSQLを実行。適当なコマンドを作ってほしいところですが。

mysql> SELECT Host, User FROM mysql.user;
+-----------+---------------+
| Host      | User          |
+-----------+---------------+
| %         | foo           |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
4 rows in set (0.00 sec)

権限

権限付与

GRANT構文を使用します。複数の権限を付与する場合はカンマで区切る。同一のユーザー名でもホストが異なると別人として扱われるので注意。

mysql> GRANT (権限) ON (DB名).(テーブル名) TO '(ユーザー名)'@'(ホスト名)';
  • DB名、テーブル名にはワイルドカードとしてアスタリスク(*)が使用可能
    • *.*の場合は全DBの全テーブルが対象
    • foo.*の場合はfoo DBのすべてのテーブルが対象
  • 権限名につづいてカラム名を記述すると、そのカラムに対しての権限付与が可能
    • 例: GRANT SELECT (col1) ON mydb.mytbl TO 'foo'@'localhost';

権限を剥奪

REVOKE構文を使用します。

mysql> REVOKE (権限) ON (DB名).(テーブル名) FROM '(ユーザー名)'@'(ホスト名)';
  • GRANTと同様にワイルドカード(*)が使用可能

権限一覧

以下はGRANT/REVOKEで指定可能な権限の一部。

権限 説明 レベル
ALL すべての権限を付与
ALTER ALTER TABLEの使用を許可 グローバル、データベース、テーブル
CREATE データベースおよびテーブルの作成を許可 グローバル、データベース、テーブル
CREATE USER CREATE USER、DROP USER、RENAME USER、REVOKE ALL PRIVILEGES の使用を許可 グローバル
CREATE VIEW ビューの作成、変更を許可 グローバル、データベース、テーブル
DELETE DELETE の使用を許可 グローバル、データベース、テーブル
DROP データベース、テーブル、およびビューの削除を許可 グローバル、データベース、テーブル
FILE ファイルの読み込み、書き込みを許可 グローバル
GRANT OPTION 他アカウントへ権限付与、権限削除を許可 グローバル、データベース、テーブル、プロシージャー、プロキシ
INDEX インデックスの作成、削除を許可 グローバル、データベース、テーブル
INSERT INSERTの使用許可 グローバル、データベース、テーブル、カラム
LOCK TABLES LOCK TABLESの使用を許可。別途SELECT権限も必要 グローバル、データベース
RELOAD FLUSH操作を許可 グローバル
REPLICATION CLIENT マスター/スレーブサーバーの場所を問い合わせ可能に グローバル
REPLICATION SLAVE レプリケーションスレーブがマスターからバイナリログの読み取りを許可 グローバル
SELECT SELECTの使用を許可 グローバル、データベース、テーブル、カラム
SHOW DATABASES SHOW DATABASESですべてのデータベースの表示を許可 グローバル
SHOW VIEW SHOW CREATE VIEW の使用を許可 グローバル、データベース、テーブル
SHUTDOWN mysqladmin shutdown の使用を許可 グローバル
UPDATE UPDATEの使用を許可 グローバル、データベース、テーブル、カラム
USAGE 「権限なし」

削除

DROP USER構文を使用します。

mysql> DROP USER '(ユーザー名)'@'(ホスト名)';

データベース

作成

CREATE DATABASE構文を使用。

CREATE DATABASE (DB名);

一覧

SHOW DATABASES構文を使用。

SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+

デフォルトのDB設定

USE構文を使用。

USE (DB名);

削除

DROP DATABASE構文を使用。

DROP DATABASE (DB名);

名前の変更

残念ながらMySQLはRENAME DATABASEがサポートされていません。 新しいDBを作成し、ダンプしたデータをインポートしてやるか、すべてのテーブルをRENAME TABLEしてやる必要があります。

テーブル

作成

一覧

詳細

名前の変更

削除

外部ファイル

SQLファイルを実行

MySQLクライントにログインした状態で実行

mysql> SOURCE /home/foo/hoge.sql

MySQLクライアントにリダイレクトしてもOK

$ mysql -u (ユーザー名) -p (DB名) < /home/foo/hoge.sql

CSVファイルをインポート

MySQLクライアント

LOAD DATA INFILE構文を使用します。普通にINSERTするよりも圧倒的に高速。インポート先のテーブルは事前に作成しておきます。

mysql> use (DB名);
mysql> LOAD DATA LOCAL INFILE (CSVファイルのパス)
    -> INTO TABLE (テーブル名)
    -> FIELDS TERMINATED BY ','
    -> OPTIONALLY ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';
  • FIELDS TERMINATED BYは列の区切り文字に使用している文字。デフォルトはタブ(\t)
  • OPTIONALLY ENCLOSED BYは文字列型の値を囲むために使用している文字。デフォルトは空文字。
  • LINES TERMINATED BYはレコードの区切り文字に使用している文字。デフォルトは改行(\n)

mysqlimport

シェルなどからインポートする場合はmysqlimportコマンドを利用します。

$ mysqlimport -u (ユーザー名) -p --local (DB名) (ファイル名)
Enter password:
  • セキュリティの関係で所定のパスの下にいないファイルは--localオプションを付ける必要あり
  • ファイル名は テーブル名.csv とする
  • LOAD DATA INFILE構文とほぼおなじ指定(オプション)が利用可能

関連書籍

基礎からのMySQL 第3版 (基礎からシリーズ)
西沢 夢路
SBクリエイティブ
売り上げランキング: 178,403
[改訂第4版]SQLポケットリファレンス
朝井 淳
技術評論社
売り上げランキング: 113,992