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

  • このエントリーをはてなブックマークに追加
  • LINEで送る
この記事は作成中です。随時更新されます

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

- SponsoredLink -

インストール

セキュリティ関連

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

$ sudo mysql_secure_installation

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

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

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

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

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

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すべての権限を付与
ALTERALTER TABLEの使用を許可グローバル、データベース、テーブル
CREATEデータベースおよびテーブルの作成を許可グローバル、データベース、テーブル
CREATE USERCREATE USER、DROP USER、RENAME USER、REVOKE ALL PRIVILEGES の使用を許可グローバル
CREATE VIEWビューの作成、変更を許可グローバル、データベース、テーブル
DELETEDELETE の使用を許可グローバル、データベース、テーブル
DROPデータベース、テーブル、およびビューの削除を許可グローバル、データベース、テーブル
FILEファイルの読み込み、書き込みを許可グローバル
GRANT OPTION他アカウントへ権限付与、権限削除を許可グローバル、データベース、テーブル、プロシージャー、プロキシ
INDEXインデックスの作成、削除を許可グローバル、データベース、テーブル
INSERTINSERTの使用許可グローバル、データベース、テーブル、カラム
LOCK TABLESLOCK TABLESの使用を許可。別途SELECT権限も必要グローバル、データベース
RELOADFLUSH操作を許可グローバル
REPLICATION CLIENTマスター/スレーブサーバーの場所を問い合わせ可能にグローバル
REPLICATION SLAVEレプリケーションスレーブがマスターからバイナリログの読み取りを許可グローバル
SELECTSELECTの使用を許可グローバル、データベース、テーブル、カラム
SHOW DATABASESSHOW DATABASESですべてのデータベースの表示を許可グローバル
SHOW VIEWSHOW CREATE VIEW の使用を許可グローバル、データベース、テーブル
SHUTDOWNmysqladmin shutdown の使用を許可グローバル
UPDATEUPDATEの使用を許可グローバル、データベース、テーブル、カラム
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
- SponsoredLink -

Donate

投げ銭お待ちしております!

BTC3A9nH1j7qQdKrSTrmnEdweo6zPqpHBmkxC
ETH0x1aE0541198D1F9f2908a25C35032A473e74D3731
XPXaQ9zv65F9ovfoMBrFGiPRG47aSHFhy8SX
MONAMTKgzSiS5BDueZkRCHySih24TGFwHThaDQ (MonaCoin)
ZNYZhnpf4RFYVQTAQiyoJg9dGoeC4bgT3BoSy (BitZeny)

コメントはお気軽に(゚∀゚)