MySQL /MariaDB Tips

忘れやすいのでMySQL/MariaDBに関するTipsをメモ。完全に自分用メモ。

# 師匠のまとめ。分かり易い。
http://qiita.com/hit/items/7747394e8f2f6a515535

# information_schemaから情報を引っ張る系。

## カラム名に「XXX」を含むテーブルを調べる。

```sql
SELECT table_name,table_schema,column_name,column_type FROM information_schema.COLUMNS WHERE column_name LIKE '%XXX%' ;  
```

## テーブル名に「YYY」を含むテーブルを調べる。

```sql
SELECT table_name,table_schema,table_type FROM information_schema.TABLES WHERE table_name LIKE '%YYY%' ;
```

## SHOW FULL PROCESSLISTを見やすい形で引っ張る。

```sql
SELECT
  ID,
  USER,
  HOST,
  DB,
  COMMAND,
  TIME,
  STATE,
  INFO,
  TIME_MS,
  STAGE,
  PROGRESS,
  QUERY_ID
FROM
  information_schema.PROCESSLIST;
```

### スキーマ名指定でshow full processlistを引っ張る。

```sql
mysql> SELECT * FROM information_schema.PROCESSLIST WHERE DB = '<スキーマ名>';
```

* 多分これが一番簡単。

# レプリケーション系。
## レプリケーション用にdump取得。

```sql
# mysqldump -h <対象ホスト> --databases <対象スキーマ> -u root -p<PW> --master-data --routines --single-transaction > /var/data/<対象スキーマ>.dump
```

* single-transactionを付けることで、テーブルロックを避ける(はず…)
* --master-data…CHANGE MASTER TO を含む。レプリケーション構築時に必須。
* --routines…ストアドプロシージャー、ストアドファンクションも含む。

## レプリケーション設定用にbin-logのポジションを確認。

```bash
# grep "CHANGE MASTER TO" <対象スキーマ>.dump 
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=19199388;
#
```

## レプリケーション設定(ノーマル)

```sql
CHANGE MASTER TO 
MASTER_HOST = 'マスタサーバのIP or FQDN',
MASTER_USER = 'レプリケーション用のアカウント',
MASTER_PASSWORD = 'PW',
MASTER_PORT = 3306 ,
MASTER_LOG_FILE='mysql-bin.XXXXXX', 
MASTER_LOG_POS=YYYYYY;
```

## レプリケーション設定(マルチソース)
```sql
SET @@default_master_connection='任意のコネクション名';

CHANGE MASTER '任意のコネクション名' TO 
MASTER_HOST = 'マスタサーバのIP or FQDN',
MASTER_USER = 'レプリケーション用のアカウント',
MASTER_PASSWORD = 'PW',
MASTER_PORT = 3306 ,
MASTER_LOG_FILE='mysql-bin.XXXXXX', 
MASTER_LOG_POS=YYYYYY;
```

## 意図的にレプリケーションエラーをSKIPする。

```sql
STOP SLAVE ; SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; SHOW SLAVE STATUS\G
```

## マルチソースレプリケーションの場合はこっち。

```sql
SET @@default_master_connection='<レプリケーション名>'; STOP SLAVE; SET GLOBAL  SQL_SLAVE_SKIP_COUNTER=1; START SLAVE; SHOW SLAVE STATUS\G
```

# CONCATで文字列結合。

* [https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_concat](https://dev.mysql.com/doc/refman/5.6/ja/string-functions.html#function_concat)

```
CONCAT(str1,str2,...)

引数を連結することで生成される文字列を返します。1 つ以上の引数を持つ場合があります。
すべての引数が非バイナリ文字列の場合は、結果も非バイナリ文字列になります。
引数にバイナリ文字列が含まれる場合は、結果はバイナリ文字列になります。
数値の引数は、同等の非バイナリ文字列形式に変換されます。 
```

難しく書いてあるけど、以下の例のように文字列結合できるということ。

## CONCAT関数で文字列結合した場合の例。

```sql
mysql> SELECT CONCAT (1, ' + ', 1, ' = ', 2 ) AS '1 + 1 = 2';
+---------------------------------+
| CONCAT (1, ' + ', 1, ' = ', 2 ) |
+---------------------------------+
| 1 + 1 = 2                       |
+---------------------------------+
1 row in set (0.00 sec)

mysql> 
```

### CONCATしていない場合の例。

こっちは参考までに。

```sql
mysql> SELECT 1, ' + ', 1, ' = ', 2 ;
+---+-----+---+-----+---+
| 1 | +   | 1 | =   | 2 |
+---+-----+---+-----+---+
| 1 |  +  | 1 |  =  | 2 |
+---+-----+---+-----+---+
1 row in set (0.00 sec)

mysql> 
```

# 文字列結合をパイプ(||)で行う。

CONCAT関数でも良いけど、サーバ上で直接ワンライナーしたい時なんかは、パイプ(||)で結合した方が、
個人的には見やすい。


* [MySQL 文字列結合をパイプ（||）でやろう！](http://qiita.com/hit/items/ee4e54726f80a1fbdeb2)


## 一時的な変更。(DB再起動すると設定元に戻る)

```
mysql> SET sql_mode='PIPES_AS_CONCAT' ;
Query OK, 0 rows affected (0.00 sec)

mysql> 
```

## 永続変更。
_/etc/my.cnf_の[mysqld]セクションに以下を追記してMySQLを再起動する。

* MariaDBだったら_/etc/mycnf.d/server.cnf_

```bash
[mysqld]
sql_mode = 'PIPES_AS_CONCAT'
```

## パイプで文字列結合した場合の例。

```sql
mysql> SELECT 1 || ' + ' || 1 || ' = ' || 2 AS '1 + 1 = 2';
+-----------+
| 1 + 1 = 2 |
+-----------+
| 1 + 1 = 2 |
+-----------+
1 row in set (0.00 sec)

mysql>
``` 
# mysqldump系

## [TRIGGERだけmysqldumpする](http://qiita.com/koitatu3/items/465114dd3c16c2e5fddd)

## [レプリケーション用にdump取得。](http://qiita.com/koitatu3/items/d7308a899c6131c350d1#%E3%83%AC%E3%83%97%E3%83%AA%E3%82%B1%E3%83%BC%E3%82%B7%E3%83%A7%E3%83%B3%E7%94%A8%E3%81%ABdump%E5%8F%96%E5%BE%97)

# TRIGGER
## TRIGGERテンプレート(INSERT)
```
DELIMITER ;;

CREATE TRIGGER <トリガー名> AFTER INSERT ON <対象テーブル名>
FOR EACH ROW 
BEGIN
 ---- 処理内容 ----
END
;;
DELIMITER ;
```

## TRIGGERテンプレート(UPDATE)
```
DELIMITER ;;
CREATE TRIGGER <トリガー名> AFTER UPDATE ON <対象テーブル名>
FOR EACH ROW BEGIN
 ---- 処理内容 ----
END
;;
DELIMITER ;
```

## TRIGGERテンプレート(DELETE)
```
DELIMITER ;;
CREATE TRIGGER <トリガー名> AFTER DELETE ON <対象テーブル名>
FOR EACH ROW BEGIN
 ---- 処理内容 ----
END
;;
DELIMITER ;
```

```
insert文だけを出力するには、-t または --no-create-info オプションを指定します。
$ mysqldump -t DB名 テーブル名

テーブル定義だけを出力するには -d または --no-data オプションを指定します。
$ mysqldump -d DB名 テーブル名

テーブル定義だけを出力する方法として、-w でダンプするデータの条件が偽になるようにするという手もあります。
$ mysqldump DB名 テーブル名 -w '0=1'
```

http://blog.goo.ne.jp/dak-ikd/e/a541ad60c85b28c8ea262f0d6777aa1d

```
次の様に、データベース名に続けてバックアップしたいテーブル名を指定して mysqldump を実行すれば良いです。
$ mysqldump -u ユーザ名 -p -h ホスト名 データベース名 [テーブル名 ...] > ダンプファイル名

ダンプ対象の複数のテーブル名をあらかじめファイルに書いておき、それを使って指定することもできます。 次の例では tables.txt の各行にダンプ対象のテーブル名が書いてあるとします。
$ mysqldump -u ユーザ名 -p -h ホスト名 データベース名 `cat tables.txt` > ダンプファイル名
```

http://tipszone.jp/20120526_mysqldump/

```
mysqldump -h <対象ホスト名> -P 3306 --databases <対象スキーマ名> -u sysadm -p<パスワード> --master-data --routines --single-transaction > /var/data/<対象スキーマ名>.dump
```