歳のせいかど忘れすることが増えたためMySQLの操作方法をまとめてメモ。MySQL5.6〜5.7あたりでの利用を想定しています。
目次
インストール
セキュリティ関連
インストール完了後に以下のコマンドを実行する。
$ sudo mysql_secure_installation
実際に設定されるのは以下
- rootのパスワード変更
- パスワード検証プラグインを入れる
- ユーザー「anonymous」削除
- rootで外部からのログイン禁止
- データベース「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 (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
構文とほぼおなじ指定(オプション)が利用可能
関連書籍
SBクリエイティブ
売り上げランキング: 178,403
このブログを応援する
お寄せいただいたお気持ちは全額サーバ代や次の記事を執筆するための原資として活用させていただいております。この記事が参考になった場合などぜひご検討ください。