MySQLチートシート

MySQLのチートシート。MySQLのコマンドが覚えられないのでMySQLの操作時にはこれを見れば良いを目指してコマンドの一覧を作成してます。

インデックスを確認する方法

テーブルに張られているインデックスを確認する方法です。

SHOW INDEX FROM テーブル名;

データベースのサイズを確認するSQL

SELECT 
    table_schema, sum(data_length) /1024/1024 AS mb 
FROM 
    information_schema.tables  
GROUP BY 
    table_schema 
ORDER BY       
    sum(data_length+index_length) DESC;

テーブルのサイズを確認するSQL

SELECT  
    table_name, engine, table_rows AS tbl_rows,
    avg_row_length AS rlen,  
    floor((data_length+index_length)/1024/1024) AS allmb,  #総容量
    floor((data_length)/1024/1024) AS dmb,  #データ容量
    floor((index_length)/1024/1024) AS imb   #インデックス容量
FROM 
    information_schema.tables  
WHERE
    table_schema=database()  
ORDER BY
    (data_length+index_length) DESC;  

ユーザとホスト

ユーザー作成するコマンドは下記です。

CREATE USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password BY 'user'

削除する場合はDROP。

DROP USER 'user';

権限を確認する方法

下記コマンドで権限の設定を確認することができます。

SHOW GRANTS;

mysql.userテーブルからユーザとホスト、パスワードを確認することができます。

SELECT user, host, password FROM mysql.user;

次のコマンドでは指定のデータベース以下の全てのテーブルにパスワード付きの全権限を付与することができます。

GRANT ALL PRIVILEGES
ON データベース名.* TO ユーザ名@"ホスト名" 
IDENTIFIED BY 'パスワード' WITH GRANT OPTION;

各テーブルのレコード数を一括で確認する方法

どれくらいデータがあるのか知りたいときに SELECT + COUNT でレコード数を確認することはできますが、データベース中の全テーブルについて知りたいときには次のコマンドを使います。

SELECT table_name, table_rows FROM information_schema.TABLES;

実行中のクエリを確認する方法

データベースの応答が遅いときは、次のコマンドで実行中のクエリを確認します。処理に時間がかかっているクエリがあれば、そのプロセスを止める対応が必要です。実行中以外に遅いクエリを知りたい時はスロークエリログを確認します。

SHOW PROCESSLIST;

バージョンを確認する

select version();

環境構築系

インストール

brew install mysql

バージョン確認

mysql --version

mysqlサーバのコマンド

#状態確認
mysql.server status

#起動
mysql.server start

#停止
mysql.server stop

#リロード(設定ファイルの再読み込み）
mysql.server reload

#再起動
mysql.server restart

#ログイン
mysql -uroot

トラブルシューティング

You are not allowed to create a user with GRANT

下記のようなエラーが出る場合は、ユーザに適切な権限が付与されていないかユーザ情報が間違っています。

ERROR 1410 (42000): You are not allowed to create a user with GRANT

Can’t connect to local MySQL server through socket ‘/tmp/mysql.sock’ (2)

接続時に次のようなエラーが表示される場合はサーバへのsocket接続に失敗しています。MySQLサーバが正常に起動しているか確認します。

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)