設定値を表示および設定

グローバル変数を一覧表示

$ mysql -uroot -p -e 'SHOW GLOBAL VARIABLES'

状態を表示

$ mysql -uroot -p -e 'SHOW GLOBAL STATUS'

グローバル変数を個別表示

mysql> SELECT @@innodb_flush_log_at_trx_commit;  ← InnoDBにおいて、コミット毎にディスク書き込みを行うかどうか

グローバル変数をその場で変更 (mysqldを再起動すると元に戻ります。恒久的な設定は "my.cnf" に記述しましょう)

mysql> SET GLOBAL innodb_flush_log_at_trx_commit = 0;  ← 0: 1秒毎にまとめて, 1: コミット毎 (既定値)

プロセスを表示

mysql> SHOW PROCESSLIST;
mysql> SHOW FULL PROCESSLIST;  ← Info が長い文字列の場合に省略しない
+----+------+-----------+------+---------+------+-------+------------------+
| Id | User | Host      | db   | Command | Time | State | Info             |
+----+------+-----------+------+---------+------+-------+------------------+
| 92 | root | localhost | mydb | Query   |    0 | NULL  | show processlist |
+----+------+-----------+------+---------+------+-------+------------------+

State はスレッドの状態を示します。「...」→「freeing items」→「cleaning up」となってリソースが解放されます。MySQL 5.5 からは以下のように SELECT することもできます。WHERE 句で条件を指定したい場合などに便利です。

mysql> SELECT * FROM information_schema.PROCESSLIST;

特にレプリケーションスレーブの有無を調査したいときは、単に以下のコマンドを実行すればよいです。

mysql> SHOW SLAVE HOSTS;

プロセス kill

遅いクエリを実行しているスレッドを kill できます。公式ページはこちらです。mysqladmin のことや kill が実行されるまでには時間を要することが記載されています。

mysql> KILL 123;

コネクション ID を取得

mysql> SELECT CONNECTION_ID();
+-----------------+
| CONNECTION_ID() |
+-----------------+
|           84274 |
+-----------------+
1 row in set (0.00 sec)

インデックス

作成

mysql> ALTER TABLE mytable ADD INDEX myindex(mycolumn1);
mysql> ALTER TABLE mytable ADD INDEX myindex(mycolumn1, mycolumn1);  ←複合インデックス

削除

mysql> ALTER TABLE mytable DROP INDEX myindex;

確認 (公式ページ)

mysql> SHOW INDEX FROM mytable;

マスタースレーブ関連

レプリケーション遅延などのスレーブ情報を表示するためには、スレーブサーバで以下のコマンドを実行します。

mysql> SHOW SLAVE STATUS \G

ちなみに、

mysql> SHOW MASTER STATUS \G

というのもあります。

レプリケーション設定の保存先について (MySQL 5.6 以降)

以下の結果が FILE であれば datadir 内にファイルとしてレプリケーションのポジションやユーザーのパスワードが保存されます。

select @@master_info_repository;
select @@relay_log_info_repository;

TABLE であればそれぞれ以下のテーブルに保存されるようになります。

mysql.slave_relay_log_info
mysql.slave_master_info

mysqldump コマンドで --all-databases を付与すると mysql スキーマの中の上記テーブルも含まれてしまうことに注意します。

文字列の一部を切り出す

mysql> SELECT SUBSTRING({'文字列'や列名}, {スタート地点 1,2,...,-1}[, {取得する長さ}]) FROM テーブル名;

数値で表現された IPv4 アドレスをドット区切りに変換 (INET_NTOA/INET_ATON)

mysql> SELECT INET_NTOA(167773449);
+----------------------+
| INET_NTOA(167773449) |
+----------------------+
| 10.0.5.9             |
+----------------------+
1 row in set (0.00 sec)

逆の変換も可能です。

mysql> SELECT INET_ATON('10.0.5.9');
+-----------------------+
| INET_ATON('10.0.5.9') |
+-----------------------+
|             167773449 |
+-----------------------+
1 row in set (0.00 sec)

INSERT 文は複数の行を作成可能

mysql> INSERT INTO my_table VALUES ('abcde', 1), ('fghij', 2), ('klmno', 3);

あるテーブルを作成するための Create 文を取得

作成したテーブルの構造を確認したり、別の DB に同じ構造のテーブルを作成したい場合に使用します。

mysql> CREATE TABLE my_table (cola VARCHAR(32) PRIMARY KEY, colb INT);
mysql> SHOW CREATE TABLE my_table\G

*************************** 1. row ***************************
       Table: my_table
Create Table: CREATE TABLE `my_table` (
  `cola` varchar(32) NOT NULL,
  `colb` int(11) DEFAULT NULL,
  PRIMARY KEY (`cola`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

テーブルの情報を取得

mysql> SHOW TABLE STATUS LIKE 'my_table'\G

*************************** 1. row ***************************
           Name: my_table
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 10485760
 Auto_increment: NULL
    Create_time: 2015-05-16 05:16:56
    Update_time: NULL
     Check_time: NULL
      Collation: utf8_general_ci
       Checksum: NULL
 Create_options: 
        Comment: 

各項目の意味は公式ページに記載されています。Rows はテーブルのレコード数であり Data_length / Avg_row_length によって計算されます。MyISAM など一部のストレージエンジンを除いて 1 レコードあたりのデータサイズは異なるため、InnoDB などについては Rows の値は概算となります。正確な値を知るためにはレコード数が多いと低速ですが SELECT COUNT(*) を使用するしかありません。

information_schema.tables を参照しても同様に行数の概算値が取得できます。

SELECT table_schema, table_name, table_rows FROM information_schema.tables;

ストアドプロシージャ

直訳すると保存された Stored 手続 Procedure です。関数のようなもので、複数の SQL 文を一纏めにします。

mysql> CREATE PROCEDURE myStoredProcedure()
       SELECT NOW();

mysql> CALL myStoredProcedure;
+---------------------+
| NOW()               |
+---------------------+
| 2015-04-13 12:57:34 |
+---------------------+
1 row in set (0.01 sec)

gzip 圧縮された MySQL ダンプファイルのスマートなリストア方法

パイプを利用するとスマートです。

$ mysqldump --single-transaction --events --skip-lock-tables \
-uroot -p データベース名 | gzip > database.dump.gz
$ zcat database.dump.gz | mysql -uroot -p

これは以下の二つが同じ意味を有することからも理解できます。

$ cat sample.txt | cat
$ cat < sample.txt

リモートホストからダンプする方法

リストア用のホストに scp する手順が省略できるため、以下のコマンドでダンプできることを知っておくと便利です。

$ ssh username@hostname "mysqldump --single-transaction --events --skip-lock-tables -uroot -pPASSWD データベース名 | gzip" > database.dump.gz

ファイル入出力

ちょっとしたデータを別のデータベースにコピーしたい場合などに使用します。

CSV を出力 (既にファイルが存在する場合はエラーが出ます)

mysql> SELECT id, name FROM users WHERE name LIKE 'Q%' LIMIT 10 INTO OUTFILE
  '/tmp/sample.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"';

$ cat /tmp/sample.csv
1,"Qoosky"
2,"Qoo"
3,"Quest"

CSV を取り込む (LOCAL INFILE ではなく単に INFILE とだけするとエラーになることがあります)

mysql> LOAD DATA LOCAL INFILE '/tmp/sample.csv' INTO TABLE my_table FIELDS TERMINATED BY ',' ENCLOSED BY '"';

遅い SQL でないかどうかを調査

ベンチマーク

指定した SQL を指定回数だけ実行して処理に要した時間を測定します。結果は常に 0 です。

mysql> SELECT BENCHMARK(1000000, "SELECT * FROM users");
+-------------------------------------------+
| BENCHMARK(1000000, "SELECT * FROM users") |
+-------------------------------------------+
|                                         0 |
+-------------------------------------------+
1 row in set (0.01 sec)

Disk を利用した演算になっていないかどうか確認

GROUP BY や ORDER BY で使用される tmp table は通常メモリ内に作成されます (Copying to tmp table)。このテーブルサイズが tmp_table_size よりも大きくなるとディスクが利用される (Copying to tmp table on disk) ようになり低速化します。

確認方法

on disk となっています。

mysql> SHOW FULL PROCESSLIST;

+----+------+-----------+------+---------+------+------------------------------+--------------+
| Id | User | Host      | db   | Command | Time | State                        | Info         |
+----+------+-----------+------+---------+------+------------------------------+--------------+
|  2 | root | localhost | mydb | Query   |    1 | Copying to tmp table on disk | (重いクエリ) |
...

対策

セッションでのみ一時的に対応するには SET します。

mysql> SHOW VARIABLES LIKE 'tmp_table_size';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| tmp_table_size | 1024  |
+----------------+-------+
1 row in set (0.00 sec)

mysql> SET tmp_table_size = 1024 * 1024 * 16; ←16MBにする例
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW VARIABLES LIKE 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
1 row in set (0.00 sec)

グローバルに対応するためには SET GLOBAL します。サーバ再起動後にも変更されたままになるように my.cnf も編集しておきましょう。

mysql> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| tmp_table_size | 1024  |
+----------------+-------+
1 row in set (0.00 sec)

mysql> SET GLOBAL tmp_table_size = 1024 * 1024 * 16; ←16MBにする例
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'tmp_table_size';
+----------------+----------+
| Variable_name  | Value    |
+----------------+----------+
| tmp_table_size | 16777216 |
+----------------+----------+
1 row in set (0.00 sec)

結果

on disk が消えました。

mysql> SHOW FULL PROCESSLIST;

+----+------+-----------+------+---------+------+----------------------+--------------+
| Id | User | Host      | db   | Command | Time | State                | Info         |
+----+------+-----------+------+---------+------+----------------------+--------------+
|  2 | root | localhost | mydb | Query   |    1 | Copying to tmp table | (重いクエリ) |
...

実行計画の調査

メモリ上で完結する演算であったとしてもインデックスを利用しない場合は低速です。EXPLAIN で確認して Using temporary や Using filesort が表示されたらインデックスの作成を検討しましょう。

• EXPLAIN
• Using temporary; Using filesort

もちろん後になればなるほど処理が重くなるので、出来るだけそのようなクエリはさけて1番目や2番目の実行計画になるようにすると良いわけである。1番目や2番目の実行計画になるようにするには、次のような点に注意しなければいけない。

重いクエリの例

mysql> EXPLAIN SELECT a1.f1, COUNT(*) FROM mytable AS a1 LEFT JOIN mytable AS a2 ON a1.f1 = a2.f1 GROUP BY a1.f1;
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                           |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+
|  1 | SIMPLE      | a1    | ALL  | NULL          | NULL | NULL    | NULL | 9790 | Using temporary; Using filesort |
|  1 | SIMPLE      | a2    | ALL  | NULL          | NULL | NULL    | NULL | 9790 |                                 |
+----+-------------+-------+------+---------------+------+---------+------+------+---------------------------------+

mysql> SELECT a1.f1, COUNT(*) FROM mytable AS a1 LEFT JOIN mytable AS a2 ON a1.f1 = a2.f1 GROUP BY a1.f1;
+------+----------+
| f1   | COUNT(*) |
...

+---------+----------+
9999 rows in set (22.23 sec) ← 遅い

インデックスを作成した後

mysql> EXPLAIN SELECT a1.f1, COUNT(*) FROM MYTABLE AS a1 LEFT JOIN mytable AS a2 ON a1.f1 = a2.f1 GROUP BY a1.f1;
+----+-------------+-------+-------+---------------+----------+---------+------------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref        | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------------+------+-------------+
|  1 | SIMPLE      | a1    | index | NULL          | myindex1 | 5       | NULL       |  200 | Using index | ★
|  1 | SIMPLE      | a2    | ref   | myindex1      | myindex1 | 5       | mydb.a1.f1 |   52 | Using index | ★
+----+-------------+-------+-------+---------------+----------+---------+------------+------+-------------+
2 rows in set (0.00 sec)

mysql> SELECT a1.f1, COUNT(*) FROM mytable AS a1 LEFT JOIN mytable AS a2 ON a1.f1 = a2.f1 GROUP BY a1.f1;
+------+----------+
| f1   | COUNT(*) |
...

+---------+----------+
9999 rows in set (0.02 sec)  ← 0.02 秒

参考

上記例では Using temporary; Using filesort ではなく JOIN が遅さの原因でした。試しに GROUP BY を外してみましょう。

mysql> EXPLAIN SELECT a1.f1 FROM mytable AS a1 LEFT JOIN mytable AS a2 ON a1.f1 = a2.f1;
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | a1    | ALL  | NULL          | NULL | NULL    | NULL | 10443 |       |
|  1 | SIMPLE      | a2    | ALL  | NULL          | NULL | NULL    | NULL | 10443 |       |
+----+-------------+-------+------+---------------+------+---------+------+-------+-------+
2 rows in set (0.00 sec)

やはり遅いままです。インデックスを作成して Using index が付与されるようにしましょう。

mysql> SELECT a1.f1, COUNT(*) FROM mytable AS a1 LEFT JOIN mytable AS a2 ON a1.f1 = a2.f1 GROUP BY a1.f1;
+------+----------+
| f1   | COUNT(*) |
...

+---------+----------+
9999 rows in set (22.35 sec) ← やはり遅い

プロセスの各ステータスで要した時間を調査

mysql> set profiling = 1;
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set (0.00 sec)
mysql> 調査したいクエリ;
mysql> show profile;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000152 |
| checking permissions | 0.000011 |
| checking permissions | 0.000009 |
| checking permissions | 0.000012 |
| Opening tables       | 0.000052 |
| System lock          | 0.000094 |
| optimizing           | 0.000019 |
| statistics           | 0.000022 |
| preparing            | 0.000018 |
| executing            | 0.000013 |
| Sorting result       | 0.000010 |
| Sending data         | 0.006497 |
| init                 | 0.000069 |
| optimizing           | 0.000012 |
| statistics           | 0.000029 |
| preparing            | 0.000020 |
| Creating tmp table   | 0.000045 |
| executing            | 0.000010 |
| Copying to tmp table | 4.331556 | ★
| Sorting result       | 0.000165 |
| Sending data         | 0.000040 |
| end                  | 0.000011 |
| removing tmp table   | 0.000045 |
| end                  | 0.000012 |
| query end            | 0.000012 |
| closing tables       | 0.000009 |
| removing tmp table   | 0.000011 |
| closing tables       | 0.000016 |
| freeing items        | 0.000214 |
| logging slow query   | 0.000011 |
| cleaning up          | 0.000010 |
+----------------------+----------+
31 rows in set (0.02 sec)

トランザクション分離レベルの確認

MySQL のバージョンによって以下のいずれかで確認できます。最新の MySQL では REPEATABLE-READ が既定値です。

SELECT @@tx_isolation;
SELECT @@transaction_isolation;

+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.00 sec)

トランザクション A、トランザクション B

BEGIN;

トランザクション A

DELETE FROM mytbl;
COMMIT;

トランザクション B では削除前のデータが読み取れます。

SELECT * FROM mytbl;

トランザクション が終了すると削除されたデータは読み取れなくなります。

COMMIT;
SELECT * FROM mytbl;

Python の mysql.connector などを利用している際に、SELECT 用のトランザクションであっても COMMIT し忘れると最新のデータが読み取れなくなるため注意します。

コマンドラインからタブ区切りで SELECT

$ users=(root readonly)
$ for user in "${users[@]}";do mysql -uroot -B -N -e "select user,host from mysql.user where user = '$user'";done
root    127.0.0.1
root    ::1
root    localhost
readonly        %

• -B → バッチモード (タブ区切り)
• -N → ColumnName をスキップ

テーブル指定でダンプ

mysqldump --single-transaction --skip-lock-tables -uroot -p mydb mytable | gzip > mydb-mytable.dump.gz

データ出力せず create table 文だけを出力するためには --no-data を付与します。

mysqldump --single-transaction --skip-lock-tables -uroot -p mydb mytable --no-data > mydb-mytable-schema.dump.gz

カラムの追加および削除

もととなるテーブル

mysql> CREATE TABLE my_table (cola VARCHAR(32) PRIMARY KEY, colb INT);
mysql> DESC my_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| cola  | varchar(32) | NO   | PRI | NULL    |       |
| colb  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

追加します。既定値には Default が利用されます。

mysql> ALTER TABLE my_table ADD colc INT;
mysql> ALTER TABLE my_table ADD col00 INT FIRST;
mysql> ALTER TABLE my_table ADD col01 INT AFTER col00;
mysql> DESC my_table;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| col00 | int(11)     | YES  |     | NULL    |       |
| col01 | int(11)     | YES  |     | NULL    |       |
| cola  | varchar(32) | NO   | PRI | NULL    |       |
| colb  | int(11)     | YES  |     | NULL    |       |
| colc  | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

削除します。

mysql> ALTER TABLE my_table DROP cola;
mysql> DESC my_table;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| col00 | int(11) | YES  |     | NULL    |       |
| col01 | int(11) | YES  |     | NULL    |       |
| colb  | int(11) | YES  |     | NULL    |       |
| colc  | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

Windows MySQL 利用時の注意点

Windows などで文字コードの既定値が UTF-8 でない環境の場合、明示的に指定してリストアします。

zcat database.dump.gz | mysql -uroot -p --default-character-set=utf8

Windows における my.cnf 相当のファイルは my.ini です。以下のコマンドで出力されるディレクトリに既定の my.ini ファイルが存在します。

SHOW VARIABLES LIKE 'datadir';