MySQLの運用で役立つコマンド集

# MySQLの運用で役立つコマンド集
書き留めておいたコマンド集。随時更新予定。

## サーバー情報を見る

* MySQLのバージョンを確認

``` sql
SHOW VARIABLES LIKE 'version';
```

* 使用できるストレージエンジンの確認

``` sql
SHOW ENGINES;
```

* 現在実行中のプロセスの確認

``` sql
SHOW PROCESSLIST;
```

``` sql:mysql5以降からSELECT文でも取得できるようになった。
SELECT * FROM information_schema.PROCESSLIST;
```

``` sql:3秒以上かかっているクエリを取得
SELECT * FROM information_schema.PROCESSLIST WHERE TIME > 3;
```

## スキーマ


* テーブルのCREATE文を表示

``` sql
SHOW CREATE DATABASE `__SCHEMA_NAME__`\G
```

* スキーマ毎に使用している容量を表示

``` sql
SELECT
    TABLE_SCHEMA,
    round( sum( DATA_LENGTH ) / ( 1024 * 1024 ) , 2 ) AS DATA_SIZE,
    round( sum( INDEX_LENGTH ) / ( 1024 * 1024 ) , 2 ) AS INDEX_SIZE,
    round( round( sum( DATA_LENGTH ) + sum( INDEX_LENGTH ) ) / ( 1024 *1024 ) , 2 ) AS TOTAL_SIZE
FROM
    information_schema.TABLES
GROUP BY
    TABLE_SCHEMA
ORDER BY
    TOTAL_SIZE DESC;
```

## テーブル

* テーブル構造の確認

``` sql:
DESCRIBE `__TABLE_NAME__`;
```

``` sql:
DESC `__TABLE_NAME__`;
```

* テーブルで使用しているのストレージエンジン表示

``` sql:
SELECT
    TABLE_NAME,
    ENGINE
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA LIKE '__SCHEMA_NAME__';
```


* テーブルのCREATE文を表示

``` sql:
SHOW CREATE TABLE `__TABLE_NAME__`\G
```


* テーブルのコピー

``` sql:
CREATE TABLE `__TO_TABLE_NAME__` LIKE `__FROM_TABLE_NAME__`;
INSERT INTO `__TO_TABLE_NAME__` SELECT * FROM `__FROM_TABLE_NAME__`;
```

* テーブルの容量の一覧を表示

``` sql
SELECT
    TABLE_NAME,
    round( DATA_LENGTH / ( 1024 * 1024 ) , 2 ) AS DATA_SIZE,
    round( INDEX_LENGTH / ( 1024 * 1024 ) , 2 ) AS INDEX_SIZE,
    round( round( DATA_LENGTH + INDEX_LENGTH ) / ( 1024 * 1024 ) , 2 ) AS TOTAL_SIZE
FROM
    information_schema.TABLES
WHERE
    TABLE_SCHEMA LIKE '__SCHEMA_NAME__'
ORDER BY
    TOTAL_SIZE DESC;
```

## インデックス

* テーブルのインデックスの情報を見る

``` sql:
SHOW INDEX FROM `__TABLE_NAME__`\G
```

* 使用されていないインデックス情報を見る

``` sql:MySQL5.5/5.6
SELECT
    OBJECT_SCHEMA,
    OBJECT_NAME,
    INDEX_NAME
FROM
    performance_schema.table_io_waits_summary_by_index_usage
WHERE
    INDEX_NAME IS NOT NULL
    AND INDEX_NAME NOT LIKE 'PRIMARY'
    AND COUNT_READ = 0;
```

``` sql:MySQL5.7
SELECT * FROM `sys`.`schema_unused_indexes`;
```

## ユーザー関係

* ユーザー表示

``` sql:
SELECT user,host FROM mysql.user;
```

* 全権限を持ったユーザーの追加

``` sql:
GRANT ALL PRIVILEGES ON *.* TO 'user'@'localhost' IDENTIFIED BY '********' WITH GRANT OPTION;
```

* ユーザーのパスワード変更

``` sql:
SET PASSWORD FOR 'user'@'localhost' = password('********');
```

* レプリケーション用ユーザーの追加(マスタで実行)

``` sql:
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.1.%' IDENTIFIED BY 'password';
```

* ユーザー削除

``` sql:
DROP USER 'repl'@'192.168.1.%';
```


## クエリキャッシュ

* クエリキャッシュ有効化

```  sql:128MBの例
SET GLOBAL query_cache_size=128*1024*1024;
SET GLOBAL query_cache_type=1;
```

* クエリキャッシュ無効化

``` sql
SET GLOBAL query_cache_size=0;
SET GLOBAL query_cache_type=0;
```

* クエリキャッシュのデフラグメント化

``` sql
FLUSH QUERY CACHE;
```

* クエリキャッシュの状況確認

``` sql
SHOW STATUS LIKE 'Qcache%';
```


## スロークエリ

* スロークエリの秒数を設定

```  sql:3秒
SET GLOBAL long_query_time=3;
```

* スロークエリのファイル名を設定

``` sql
SET GLOBAL slow_query_log_file=slow.log
```

* スロークエリ取得開始

``` sql
SET GLOBAL slow_query_log=ON;
```

* スロークエリ取得終了

``` sql
SET GLOBAL slow_query_log=OFF;
```


## レプリケーション

* マスターサーバーの状態を確認

``` sql
SHOW MASTER STATUS;
```


* バイナリログを消す

```  sql:日付を指定して削除
PURGE MASTER LOGS BEFORE '2012-12-25';
```

```  sql:ファイルを指定して削除
PURGE MASTER LOGS TO 'mysql-bin.001294';
```

* バイナリログのリセット

``` sql
RESET MASTER;
```

* スレーブの状態を確認

``` sql
SHOW SLAVE STATUS;
```


* スレーブサーバーでレプリケーションが切断された時にエラーとなったクエリを読み飛ばす

``` sql
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE ;
```

* スレーブサーバーのマスターサーバー情報の変更

``` sql
CHANGE MASTER TO
MASTER_HOST='192.168.1.1',
MASTER_PORT=3306,
MASTER_USER='repl',
MASTER_PASSWORD='PASSWORD',
MASTER_LOG_FILE='mysql-bin.001234',
MASTER_LOG_POS=79;
```

## mysqldump
* データベースのダンプを丸ごととる

``` bash:
mysqldump \
--databases データベース名 \
--flush-logs \
--hex-blob \
--opt \
--user=ユーザー名 \
-p > ファイル名
```

* マスターサーバーでのダンプ

``` bash:
mysqldump \
--databases データベース名 \
--flush-logs \
--hex-blob \
--master-data
--opt \
--user=ユーザー名 \
-p > ファイル名
```


* データベースの構造のみダンプをとる

``` bash:
mysqldump \
--databases データベース名 \
--opt \
--no-data \
--user=ユーザー名 \
-p > ファイル名
```

* 特定のテーブルのダンプをとる

``` bash:
mysqldump \
--flush-logs \
--hex-blob \
--opt \
--user=ユーザー名 \
-p データベース名 テーブル名 > ファイル名
```

* 特定のテーブルの特定のデータのダンプをとる

``` bash:
mysqldump \
--flush-logs \
--hex-blob \
--opt \
"--where=条件" \
--user=ユーザー名 \
-p データベース名 テーブル名 > ファイル名
```

## その他

* SQLの結果をCSVファイルに吐き出す

``` sql
SELECT * FROM test INTO OUTFILE '/tmp/test.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';
```

上記を行うためにはmy.cnfに下記設定が必要

```
secure-file-priv = "/tmp/"
```

* セッション統計情報の表示

``` sql
SHOW STATUS;
```

``` sql:フルインデックススキャンの回数
SHOW STATUS LIKE "Handler_read_first";
```

``` sql:両方のテーブルで全件JOINした回数
SHOW STATUS LIKE "Select_full_join";
```

``` sql:片方のテーブルで全件JOINした回数
SHOW STATUS LIKE "Select_full_range_join";
```

``` sql:全件スキャンの回数
SHOW STATUS LIKE "Select_scan";
```

``` sql:全件スキャンによるソートの回数
SHOW STATUS LIKE "Sort_scan";
```

``` sql:現在までの最大同時接続数
SHOW STATUS LIKE "Max_used_connections";
```

``` sql:現在の同時接続数
SHOW STATUS LIKE "Threads_connected";
```

* セッション統計情報のクリア

``` sql
FLUSH STATUS;
```


* SQL実行結果にページャを設定

``` sql
pager less -S
```

``` sql:標準に戻す
pager stdout
```