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

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

インストール

セキュリティ関連

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

$ sudo mysql_secure_installation

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

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

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

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

CLI

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

ログイン

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

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

パスワードを起動時に直接指定することも可能だが、セキュリティ上よろしくないので怒られる。ログ(~/.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で指定可能な権限の一部。

削除

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版 (基礎からシリーズ)

posted with amazlet at 18.04.12

西沢 夢路
SBクリエイティブ
売り上げランキング: 178,403

Amazon.co.jpで詳細を見る

[改訂第4版]SQLポケットリファレンス

posted with amazlet at 18.04.12

朝井 淳
技術評論社
売り上げランキング: 113,992

Amazon.co.jpで詳細を見る

このブログを応援する

お寄せいただいたお気持ちは全額サーバ代や次の記事を執筆するための原資として活用させていただいております。この記事が参考になった場合などぜひご検討ください。

PayPalで300円支払う
※金額は任意で変更できます。
※100円でも泣いて喜びますw
※住所の入力欄が現れた場合は「no needed」を選択ください
これまでのご協力者さま

- Sponsored Link -