2017/05/13

MySQL基礎コマンドチートシート(随時更新)

忘れがちなMySQL基礎コマンドをまとめてみました。

先頭に「mysql> 」が付いたものはmysqlログイン後、ついていないものはログイン前に実施する。 ちなみに値を入れるときは’‘ではなく``でいれるので注意。

基本操作

サーバ起動

$ service mysqld start //centos7以降 $ systemctl start mysqld //centos6以前

サーバ停止

$ mysqladmin -uroot -p shutdown

サーバ接続開始

$ mysql -h[ホスト名orIP] -u[ユーザー名] -p[パスワード] $ mysql -h[ホスト名orIP] -u[ユーザー名] -p[パスワード] -e “[実行コマンド]” //コマンド直接実行 $ mysql -h[ホスト名orIP] -u[ユーザー名] -p[パスワード] DB名 < input.sql //特定ユーザで接続し外部SQLファイルを投入する場合 $ mysql -h[ホスト名orIP] -u[ユーザー名] -p[パスワード] DB名 > output.sql //特定ユーザで接続しやりとりを外部SQLファイルに記録

サーバ接続終了

$ mysql> exit

パスワード設定

$ mysql> set password for ‘ユーザ名’@’接続元ホスト’=’パスワード’; 初回ログイン時は、$ mysql> set password for ‘root’@’localhost’=’testpassword’;の要領でパスワード設定 以下メッセージが出たらok。 Query OK, 0 rows affected (0.07 sec)

設定情報確認

MySQLバージョン確認

$ mysql -V 以下のような画面が表示されたらok $ mysql Ver 14.14 Distrib 5.5.34, for Linux (x86_64) using readline 5.1

アクティブプロセス一覧

$ mysql> show full processlist;

サマリー取得(MySQL Version/currentUser/connectionID/Threads/Query Per Sec …)

$ mysql> STATUS;

登録済ユーザー・権限一覧

登録済ユーザー一覧 $ mysql> select host,user from mysql.user;
グローバルレベルの権限情報一覧 $ mysql> select * from information_schema.user_privileges;
DBスキーマレベルの権限情報一覧 $ mysql> select * from information_schema.schema_privileges;
テーブルレベルの権限情報一覧 $ mysql> select * from information_schema.table_privileges;
カラムレベルの権限情報一覧 $ mysql> select * from information_schema.column_privileges;
アクセス許可 grant all privileges on . to ‘ユーザー名’@’ホスト名’ identified by ‘’ with grant option;

環境変数の確認

MySQLで扱う環境変数は以下2種類がある。 セッション変数：一時設定。現在の接続だけが影響する。
$ mysql> show session variables; グローバル変数：恒久設定。変数設定後のすべての接続が影響する。
$ mysql> show global variables; 絞り込み表示したい場合は以下 $ mysql> show variables like ‘キーワード’ %はアスタリスク的な使い方が出来るので’%キーワード%’のように囲むとあいまい検索が可能 $ mysql> show variables like ‘%character_set%’‘

統計情報の確認

$ mysql> show global status; 例)過去の最大コネクション数 $ mysql> show status like ‘%Max_used%’; 例)現在のコネクション数 $ mysql> show status like ‘%Threads_connected%’;

現在のINNODBの状態確認

$ mysql> show engine innodb status;

MySQLプラグイン一覧確認

$ mysql> show plugins;

エラー一覧

$ mysql> show warnings;

各DBの全テーブルのストレージエンジンの一覧取得

$ mysql> SELECT table_name,engine from information_schema.tables where table_schema=’データベース名’;

ユーザー操作

ユーザー作成

$ mysql> create user ユーザー名@接続元ホスト IDENTIFIED BY パスワード;
ちなみに全ホストへのアクセス許可は*ではなく%なので注意。
$ mysql> create user ‘ユーザー名’@’%’ IDENTIFIED BY ‘パスワード’;

ユーザー削除

$ mysql> drop user ‘ユーザー名’@’接続元ホスト’;

ユーザー毎の権限情報確認

$ mysql> show grants for ‘ユーザー名’@’接続元ホスト’;

ユーザーへの権限追加

**$ GRANT 権限 ON DB名.テーブル名 TO ユーザー名@ホスト名 **

管理者ユーザを作りたい

$ GRANT ALL ON . TO 管理ユーザ名@’%’ IDENTIFIED BY ‘パスワード’ WITH GRANT OPTION;

ユーザーへの権限削除

$ REVOKE 権限 ON DB名.テーブル名 FROM ユーザー名@ホスト名; $ REVOKE ALL ON testdb.* FROM ‘testuser’@’testhost’; $ 要注意 ユーザーの追加、削除、権限操作の後は必ずFLUSH PRIVILEGESで反映が必要。

ユーザー毎の権限情報確認

$ mysql> grant all privileges on [DB2名].[テーブル名] to ‘ユーザー名’@’接続元ホスト’ WITH GRANT OPTION;

DB操作

作成済DBの一覧表示

$ mysql> show databases;

作成済DBの作成用SQL出力(文字コードを確認)

$ mysql> show create database DB名;

DB作成

$ mysql> create database DB名; 以下メッセージが出たらok $ Query OK, 1 row affected (0.00 sec)

DB削除

$ mysql> drop database DB名; 以下メッセージが出たらok。 $ Query OK, 0 rows affected (0.13 sec) ※show databasesで消えている事を確認。

使用するDBの指定

$ mysql> use DB名; 以下メッセージが出たらok。 Database changed

指定したDBへの特定ユーザーの権限付与

$ mysql> grant all on DB名.テーブル名 to ‘ユーザー名’;

テーブル関連

作成済テーブルの一覧表示

$ mysql> show tables;

作成済テーブルの作成用SQL出力(文字コードを確認)

$ mysql> show create table テーブル名;

テーブル定義の確認

$ mysql> desc テーブル名; ※descはdescriptionの略

テーブル作成

$ mysql> create table テーブル名 -> ( -> id INT(10), -> name VARCHAR(30) -> ); $ Query OK, 0 rows affected (0.09 sec)

テーブル削除 $ mysql> drop table テーブル名;

テーブルの中身を全て表示

$ mysql> select * from テーブル名;

テーブルの中身の追加

$ mysql> insert into テーブル名フィールド名 values フィールドの値; 例) $ mysql> insert into table (field1,field2,field3) values (value1,value2,value3); 例) $ mysql> insert into テーブル名 VALUES(‘AAA’,’佐藤’,’40);

テーブルの中身の削除

$ mysql> delete from テーブル名 where 条件文; 例) $ mysql> delete from table1 where id=3;

テーブルの中身の全消去

$ mysql> truncate テーブル名; 例) $ mysql> truncate table1;

テーブルの中身の更新

$ mysql> update テーブル名 set フィールド名 where 条件; 例) $ mysql> update table1 set (field1=1) where (field2=4);

テーブルのロック/アンロック

$ mysql> lock tables テーブル名ロック種類(write/read) 例) $ mysql> lock tables testtable1 write; 例) $ mysql> lock tables testtable2 read; $ mysql> unlock tables;

テーブルの最適化

$ mysql> analize table テーブル名; 例) $ mysql> analize table table1;

バックアップ

全DBのバックアップ

データ + 定義情報
- mysqldump -u’ユーザー名’ -p’パスワード’ –all-databases > backup.sql
定義情報のみ
- mysqldump -u’ユーザー名’ -p’パスワード’ -d –all-databases > backup.sql

特定DBのバックアップ

データ + 定義情報
- mysqldump -u’ユーザー名’ -p’パスワード’ DB名 > backup.sql
定義情報のみ
- mysqldump -u’ユーザー名’ -p’パスワード’ -d DB名 > backup.sql

実行時にエラーが出た場合

以下エラーが出た場合はmy.cnfを修正「mysqldump: unknown variable ‘symbolic-links=0’」変更箇所

MySQLの設定ファイル「my.cnf」にて「symbolic-links」をコメントアウト

リストア

全DBのリストア

mysql -u’ユーザー名’ -p’パスワード’ < backup.sql

特定DBのリストア

mysql -u’ユーザー名’ -p’パスワード’ DB名 < backup.sql

レプリケーション

マスター稼働状況

$ mysql> show master status \G

スレーブ稼働状況

$ mysql> show slave status \G

運用のポイント

クライアントホスト名の不要なDNS名前解決を避ける

/etc/my.cnfにskip-name-resolveオプションをつける

ファイルデータはテーブル単位で分割する

/etc/my.cnfにinnodb_file_per_tableをつける
後々テーブル単位でファイル整理&容量削減出来るため

適切なインデックスをはる

適切なメモリサイジングをする

こちらでかなり整理してまとめて頂いていたのでリンク
http://masato.ushio.org/blog/index.php/2015/12/31/uco-tech_mysql-memory-usage/
MySQLのメモリは、サーバ全体で使用する「GlobalBuffer」と、各接続毎に確保される「ThreadBuffer」の2種類がある。

全体のメモリ容量 > GlobalBuffer一式 + (max_connections * ThreadBuffer一式)に収まるようにする。

select
@@GLOBAL.INNODB_BUFFER_POOL_SIZE as GLOBAL_INNODB_BUFFER_POOL_SIZE,
@@GLOBAL.INNODB_LOG_BUFFER_SIZE as GLOBAL_INNODB_LOG_BUFFER_SIZE,
@@GLOBAL.KEY_BUFFER_SIZE as GLOBAL_MyISAM_KEY_BUFFER_SIZE,
@@GLOBAL.TMP_TABLE_SIZE as GLOBAL_TMP_TABLE_SIZE,
@@GLOBAL.QUERY_CACHE_SIZE as GLOBAL_QUERY_CACHE_SIZE,
@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE as THREAD_BUFFER_SIZE,
@@GLOBAL.MAX_CONNECTIONS as MAX_CONNECTIONS,
@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.NET_BUFFER_LENGTH + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS AS TOTAL_MEMORY_SIZE,
 (@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.NET_BUFFER_LENGTH + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024 AS TOTAL_MEMORY_SIZE_kb,
 (@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.NET_BUFFER_LENGTH + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024/1024 AS TOTAL_MEMORY_SIZE_mb,
 (@@GLOBAL.KEY_BUFFER_SIZE + @@GLOBAL.INNODB_BUFFER_POOL_SIZE + @@GLOBAL.INNODB_LOG_BUFFER_SIZE + @@GLOBAL.NET_BUFFER_LENGTH + (@@GLOBAL.SORT_BUFFER_SIZE + @@GLOBAL.MYISAM_SORT_BUFFER_SIZE + @@GLOBAL.READ_BUFFER_SIZE + @@GLOBAL.JOIN_BUFFER_SIZE + @@GLOBAL.READ_RND_BUFFER_SIZE) * @@GLOBAL.MAX_CONNECTIONS)/1024/1024/1024 AS TOTAL_MEMORY_SIZE_gb
 \G

innodb_buffer_pool_size(80%程度), myisam_key_size(25%程度)で用途に応じて設定できているか

クエリキャッシュの利用を検討する

更新が多い場合は、キャッシュを切った方がよい場合もある。
- SELECT実行の度にSELECT文と結果をキャッシュに格納する。キャッシュ利用頻度が少ないのに格納のオーバーヘッドが多いので逆に負担になりかねない。
http://qiita.com/muran001/items/14f19959d4723ffc29cc
thread_cache_size = max_connections/3になっているか
- Treads_createdが目安
クエリキャッシュヒット率　- ＝キャッシュヒット数 / クエリ発行総数　- ＝キャッシュヒット数 / (キャッシュヒット数＋キャッシュミス数) 　- ＝Qcache_hits / ( Qcache_hit + Com_select)
ヒット率が6割を下回るようであれば見直しをしたほうが良いかも。

クエリを最適化する

EXPLAINで実行計画を立ててクエリを最適化しよう
クエリは必ずWHERE句による対象絞り込みを行う。全件フェッチは行わない。

TCPIP接続を避ける(同一ホスト内の場合のみ)

UNIXドメインソケット接続にする(–skip-networking)

MySQLの各ファイルの説明

テーブル名.frm
- テーブル定義ファイル
テーブル名.MYD
- MyISAMテーブルデータファイル
テーブル名.MYI
- MyISAMテーブルインデックスファイル
テーブル名.ibd
- InnoDBテーブルデータファイル

参考

MySQL 運用時に便利なコマンド
- https://www.qoosky.io/techs/3a369dd466
mysqldumpまとめ
- http://qiita.com/PlanetMeron/items/3a41e14607a65bc9b60c

MySQL基礎コマンド チートシート(随時更新)

基本操作

サーバ起動

サーバ停止

サーバ接続開始

サーバ接続終了

パスワード設定

設定情報確認

MySQLバージョン確認

アクティブプロセス一覧

サマリー取得(MySQL Version/currentUser/connectionID/Threads/Query Per Sec …)

登録済ユーザー・権限一覧

環境変数の確認

統計情報の確認

現在のINNODBの状態確認

MySQLプラグイン一覧確認

エラー一覧

各DBの全テーブルのストレージエンジンの一覧取得

ユーザー操作

ユーザー作成

ユーザー削除

ユーザー毎の権限情報確認

ユーザーへの権限追加

管理者ユーザを作りたい

ユーザーへの権限削除

ユーザー毎の権限情報確認

DB操作

作成済DBの一覧表示

作成済DBの作成用SQL出力(文字コードを確認)

DB作成

DB削除

使用するDBの指定

指定したDBへの特定ユーザーの権限付与

テーブル関連

作成済テーブルの一覧表示

作成済テーブルの作成用SQL出力(文字コードを確認)

テーブル定義の確認

テーブル作成

テーブルの中身を全て表示

テーブルの中身の追加

テーブルの中身の削除

テーブルの中身の全消去

テーブルの中身の更新

テーブルのロック/アンロック

テーブルの最適化

バックアップ

全DBのバックアップ

特定DBのバックアップ

実行時にエラーが出た場合

リストア

全DBのリストア

特定DBのリストア

レプリケーション

マスター稼働状況

スレーブ稼働状況

運用のポイント

クライアントホスト名の不要なDNS名前解決を避ける

ファイルデータはテーブル単位で分割する

適切なインデックスをはる

適切なメモリサイジングをする

クエリキャッシュの利用を検討する

クエリを最適化する

TCPIP接続を避ける(同一ホスト内の場合のみ)

MySQLの各ファイルの説明

参考

MySQL基礎コマンドチートシート(随時更新)