mysqlで知っておくと便利な小技集～どちらかというと運用

日々のデータベースの運用で、知っておくと、作業効率がアップする小技集です。設計の観点ではなくて、運用の観点で・・ですが。
何かしらの障害や、アプリケーションエンジニアからの問い合わせがあった場合に、以下周りをサクッと実行できれば、なんか、できるデータベースエンジニアみたいな感覚に陥ります（自己陶酔って意味で）


## 環境設定編

* CONCAT関数を、パイプ（｜）で省略可能にする

ちょっとわからないかもしれませんが、例えばSQLで

```sql
SELECT 'ABC' || 'DEF' ;
```

と

```sql
SELECT CONCAT('ABC','DEF')
```

は同じ結果になります。ちなみに出力結果は、ABCDEFになります

* my.cnf から

```sql
sql_mode = 'PIPES_AS_CONCAT'
```

* MySQLプロンプトから

```sql
mysql> SET GLOBAL sql_mode = 'PIPES_AS_CONCAT'
```

### スキーマ名・テーブル名を大文字・小文字を区別なく問合せ可能となる。

* mysqlプロンプトから

```sql
mysql>
SET GLOBAL lower_case_table_names=1 ;
```
* my.cnf に設定

```
lower_case_table_names=1
```

### my.cnf の読み込み順序を確認する

結構トラブルになりがちなのが、mysqlの環境設定ファイル(my.cnf）が適用されないという問題。結構の確率で、my.cnfの読み込み順序の問題です（特にmariadbは、my.cnf から、mysql/server.conf をインクルードする関係で、OSを再インストールしない前提で、mysqlから、mariadbに切り替える際などに過去の残骸が残っていて、余裕でもハマります

```bash
# mysql --help | grep -A1 "Default options"
-----------------
Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf 
```

### MySQLのコマンド補完を有効にする

* mysqlの起動オプションで・・

```
mysql --auto-rehash
```

* my.cnf に設定

```
auto-rehash
```

## コマンド編(bash)

### 複数のコマンドを続けて投げる

```
mysql -e "CREATE TABLE test.table1 (id int,value1 int) ; INSERT INTO test.table1 (id,value1) VALUES (1,2);"
```

### SQLファイルを読み込ませて実行

３通りの方法があるのをご存知ですか？

こんなSQLファイルを作成しましたのでこれを例に説明します

* SQLファイルを生成する

```
cat << '_EOT_' > /tmp/sample.sql

SELECT * FROM test.tables1 ;

_EOT_
```

結果は同じです

* catとパイプを使う

```
cat /tmp/sample.sql | mysql 
```

* リダイレクションを利用する

```
mysql < /tmp/sample.sql

```

* -e オプションの中で、ファイルを読み込ませる

ファイル読み込んで実行した後、普通のSQLとか続けて打ってみました

```
mysql -e "source /tmp/sample.sql ; select 'end';"
```

### おまけ

catとパイプとリダイレクションの合わせ技、ここでは、sample.sqlを実行し、その結果を、result.txtに戻していることを意味しております

```
cat /tmp/sample.sql | mysql > /tmp/result.txt
```

***
## コマンド/SQL編（mysqlコンソール）

### レプリケーションの設定をする (mariaDB 10.Xのマルチソースレプリケーションの場合）

マルチソースレプリケーションについては、 [こちら](http://qiita.com/hit/items/515b033874757b18323e)をご覧ください

```
mysql>
SET @@default_master_connection='CONN1';

STOP SLAVE 'CONN1' ; 
RESET SLAVE 'CONN1';

CHANGE MASTER 'CONN1' TO 
MASTER_HOST = 'XXX.XXX.XXX.XXX',
MASTER_USER = 'slave_account',
MASTER_PASSWORD = 'slaveパスワード',
MASTER_PORT = 3306 ,
MASTER_LOG_FILE='mysql-bin.XXXXX', 
MASTER_LOG_POS=XXXX;

START SLAVE ;

SHOW SLAVE 'CONN1' STATUS \G

```

### 複数のコマンドを投げて、レプリケーションエラーをスキップさせる

原因がわかってて、エラーをスキップさせる場合は以下の1行をコピペで・・・

```sql
mysql>
STOP SLAVE ; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; SELECT SLEEP(1); START SLAVE; SHOW SLAVE STATUS\G
```

### レプリケーションエラーを自動でスキップさせる

・・がしかし！延々とレプリケーションエラーが出ていると、いちいちスキップさせるのもしんどくなるので

my.cnf に以下を追記して再起動する（この設定をすると、当該エラーが発生した時はスルーしてしまい原因究明が困難になります。それでもエラー対応するよりまし！という方はどうぞ・・

* my.cnf （もしくは、server.cnf）に追記

該当エラーがレプリケーションで発生した場合は、エラーをなかったことにして、レプリケーションを継続させます

```
# 1159 Got timeout reading communication packets
# 1032 Message: '%s'のなかにレコードが見付かりません
# 1022 Message: table '%s' に key が重複していて書きこめません
# slave-skip-errors=1022,1032,1159
slave-skip-errors=1032
```

### テーブル一覧をSQLで実行する

データベース名（スキーマ名）を変数にしておくと、いろいろ便利です

```sql

SET @SCHEMA_NAME='wordpress' ;

SELECT
  TABLE_SCHEMA,
  TABLE_NAME,
  TABLE_TYPE,
  ENGINE
FROM
  information_schema.TABLES
WHERE
  TABLE_SCHEMA = @SCHEMA_NAME ;
---
+--------------+-----------------------+------------+--------+
| TABLE_SCHEMA | TABLE_NAME            | TABLE_TYPE | ENGINE |
+--------------+-----------------------+------------+--------+
| wordpress    | events_rec            | BASE TABLE | MyISAM |
| wordpress    | issues                | BASE TABLE | InnoDB |
| wordpress    | t1                    | BASE TABLE | InnoDB |
| wordpress    | test_users            | BASE TABLE | InnoDB |
| wordpress    | wp_commentmeta        | BASE TABLE | InnoDB |
| wordpress    | wp_comments           | BASE TABLE | InnoDB |
| wordpress    | wp_github             | BASE TABLE | InnoDB |
```
### 改行コード（Cr)が含まれているフィールドを、Windows形式の改行に変換する(CrLf)

```
UPDATE TABLE1 SET col1 =  REPLACE(col1, CHAR(10), CHAR(13) || CHAR(10)) ;
```
CHAR(13)が、CR キャリッジリターン
CHAR(10)が、LF ラインフィードということですね

パイプでつないでいるので、CrLf です

### show コマンドの曖昧検索

* 環境設定変数を、曖昧検索する

```sql
mysql> SHOW GLOBAL VARIABLES LIKE '%innodb_open%' ;
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| innodb_open_files | 300   |
+-------------------+-------+
```

* テーブル一覧を、曖昧検索する

```sql
mysql> 
SHOW TABLES LIKE '%users%' ;

Database changed
+---------------------------+
| Tables_in_movie (%users%) |
+---------------------------+
| users                     |
+---------------------------+
```

### 定義のみをコピーして別のテーブルを作成する

索引定義やらなんやらかんやらを、すべてコピーしてくれます。ただしデータはコピーしません

* 元となるテーブル定義

```
CREATE TABLE `table1` (
  `id` int(11) DEFAULT NULL,
  `value1` int(11) DEFAULT NULL,
  KEY `ix01_table1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
```

* コピー開始

```sql
mysql>
 CREATE TABLE test.table1_copy LIKE test.table1 ;
```

* コピーできました

```sql
mysql> 
  show create table test.table1_copy ;
----
  CREATE TABLE `table1_copy` (
   `id` int(11) DEFAULT NULL,
   `value1` int(11) DEFAULT NULL,
   KEY `ix01_table1` (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8
```

### 定義とデータをコピーする

データを含めて定義をコピーできますが、索引等まではコピーできません。

* コピー開始

```
mysql> 
CREATE TABLE table1_copy2 SELECT * FROM table1 ;
```

* 結果確認（データ）

```
mysql> SELECT * FROM table1_copy2 ;                                                                                                    
+------+--------+
| id   | value1 |
+------+--------+
|    1 |      2 |
+------+--------+
```

* 定義確認

インデクスの定義がなくなっていることがわかるかと思います

```
 CREATE TABLE `table1_copy2` (
  `id` int(11) DEFAULT NULL,
  `value1` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
```

### SQLでプロセスリストを取得する

何言っているのかわからないと思うのですが、MySQLでトラブルシューティングでよく実行するコマンドが、PROCESSLISTというものなのですが、ここでSQLがどれくらい滞留しているのかの状況等を確認することができます。

```sql
mysql>
SHOW PROCESSLIST ; 
-- もしくは、
SHOW FULL PROCESSLIST ;
```

ただし、ものによっては大量の結果が帰ってきたりして本質の問題を見落とす危険性があります、また、例えば、何かの条件に絞ったプロセス状況を見たいなどどいうケースにはこのコマンドは向きません。

それで、PROCESSLISTのコマンドを使うのではなく、PROCESSLIST というテンポラリテーブルを利用します。

* information_schema.PROCESSLIST

```
 CREATE TEMPORARY TABLE `PROCESSLIST` (
  `ID` bigint(4) NOT NULL DEFAULT '0',
  `USER` varchar(128) NOT NULL DEFAULT '',
  `HOST` varchar(64) NOT NULL DEFAULT '',
  `DB` varchar(64) DEFAULT NULL,
  `COMMAND` varchar(16) NOT NULL DEFAULT '',
  `TIME` int(7) NOT NULL DEFAULT '0',
  `STATE` varchar(64) DEFAULT NULL,
  `INFO` longtext,
  `TIME_MS` decimal(22,3) NOT NULL DEFAULT '0.000',
  `STAGE` tinyint(2) NOT NULL DEFAULT '0',
  `MAX_STAGE` tinyint(2) NOT NULL DEFAULT '0',
  `PROGRESS` decimal(7,3) NOT NULL DEFAULT '0.000',
  `MEMORY_USED` int(7) NOT NULL DEFAULT '0',
  `EXAMINED_ROWS` int(7) NOT NULL DEFAULT '0',
  `QUERY_ID` bigint(4) NOT NULL DEFAULT '0'
) ENGINE=Aria DEFAULT CHARSET=utf8 PAGE_CHECKSUM=0
```
* 上記テーブルを利用して、60秒以上経過しているプロセスを表示する

```sql
mysql> select COMMAND,TIME,STATE from information_schema.PROCESSLIST WHERE TIME > 60;
+-------------+--------+------------------------------------------------------------------+
| COMMAND     | TIME   | STATE                                                            |
+-------------+--------+------------------------------------------------------------------+
| Binlog Dump |    504 | Master has sent all binlog to slave; waiting for binlog to be up |
| Sleep       |    766 |                                                                  |
| Sleep       |   9353 |                                                                  |
| Connect     |    608 | Slave has read all relay log; waiting for the slave I/O thread t |
| Connect     | 439593 | Waiting for master to send event                                 |
| Connect     | 488328 | Waiting for master to send event                                 |
| Connect     | 488328 | Waiting for master to send event                                 |
| Connect     | 488328 | Waiting for master to send event                                 |
| Connect     | 488328 | Waiting for master to send event                                 |
+-------------+--------+------------------------------------------------------------------+
```