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

書き留めておいたコマンド集。随時更新予定。

サーバー情報を見る

• MySQLのバージョンを確認

SHOW VARIABLES LIKE 'version';

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

SHOW ENGINES;

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

SHOW PROCESSLIST;

mysql5以降からSELECT文でも取得できるようになった。

SELECT * FROM information_schema.PROCESSLIST;

3秒以上かかっているクエリを取得

SELECT * FROM information_schema.PROCESSLIST WHERE TIME > 3;

スキーマ

• テーブルのCREATE文を表示

SHOW CREATE DATABASE `__SCHEMA_NAME__`\G

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

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;

テーブル

• テーブル構造の確認

DESCRIBE `__TABLE_NAME__`;

DESC `__TABLE_NAME__`;

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

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

• テーブルのCREATE文を表示

SHOW CREATE TABLE `__TABLE_NAME__`\G

• テーブルのコピー

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

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

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;

インデックス

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

SHOW INDEX FROM `__TABLE_NAME__`\G

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

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;

MySQL5.7

SELECT * FROM `sys`.`schema_unused_indexes`;

ユーザー関係

• ユーザー表示

SELECT user,host FROM mysql.user;

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

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

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

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

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

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

• ユーザー削除

DROP USER 'repl'@'192.168.1.%';

クエリキャッシュ

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

128MBの例

SET GLOBAL query_cache_size=128*1024*1024;
SET GLOBAL query_cache_type=1;

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

SET GLOBAL query_cache_size=0;
SET GLOBAL query_cache_type=0;

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

FLUSH QUERY CACHE;

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

SHOW STATUS LIKE 'Qcache%';

スロークエリ

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

3秒

SET GLOBAL long_query_time=3;

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

SET GLOBAL slow_query_log_file=slow.log

• スロークエリ取得開始

SET GLOBAL slow_query_log=ON;

• スロークエリ取得終了

SET GLOBAL slow_query_log=OFF;

レプリケーション

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

SHOW MASTER STATUS;

• バイナリログを消す

日付を指定して削除

PURGE MASTER LOGS BEFORE '2012-12-25';

ファイルを指定して削除

PURGE MASTER LOGS TO 'mysql-bin.001294';

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

RESET MASTER;

• スレーブの状態を確認

SHOW SLAVE STATUS;

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

SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
START SLAVE ;

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

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

• データベースのダンプを丸ごととる

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

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

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

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

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

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

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

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

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

その他

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

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

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

secure-file-priv = "/tmp/"

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

SHOW STATUS;

フルインデックススキャンの回数

SHOW STATUS LIKE "Handler_read_first";

両方のテーブルで全件JOINした回数

SHOW STATUS LIKE "Select_full_join";

片方のテーブルで全件JOINした回数

SHOW STATUS LIKE "Select_full_range_join";

全件スキャンの回数

SHOW STATUS LIKE "Select_scan";

全件スキャンによるソートの回数

SHOW STATUS LIKE "Sort_scan";

現在までの最大同時接続数

SHOW STATUS LIKE "Max_used_connections";

現在の同時接続数

SHOW STATUS LIKE "Threads_connected";

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

FLUSH STATUS;

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

pager less -S

標準に戻す

pager stdout