<commit_before>MySQL
===

## ER Diagram

ALL DB
* [Windows only](http://www.wind.sannet.ne.jp/m_matsu/developer/a5m2/)

MySQL
* [MySQL Workbench](http://dev.mysql.com/downloads/workbench/)

## Best practice my.cnf

* [kamipo-san's my.cnf](https://github.com/kamipo/etcfiles/blob/master/etc/my.cnf)
 * [default_character_set, character_set_server](http://kamipo.github.io/talks/20150827-myna201508/#/step-12)

   > utf8mb4_bin or utf8mb4_general_ci

* [yoku0825-san](https://github.com/yoku0825)
 * [dotfiles my.cnf](https://github.com/yoku0825/dotfiles/blob/master/my.cnf)
 * [my_setup my.cnf](https://github.com/yoku0825/my_setup/blob/master/my.cnf)

## Add plugin?

* [semisync](https://dev.mysql.com/doc/refman/5.6/ja/replication-semisync-installation.html)
* [HandlerSocket](https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL)
* [Mroonga](http://mroonga.org/ja/)
* [Q4M](http://q4m.github.io/)
* [InnoDB Memcached](http://dev.mysql.com/doc/refman/5.6/ja/innodb-memcached-setup.html)

## Slave/Master

Slave or Master  
Slave is shown at infomation. Master is empty.
```sql
SHOW MASTER STATUS\G
SHOW SLAVE  STATUS\G
```

[Slave safely stop](http://qiita.com/catatsuy/items/8f2fbea88b9bc7b8acf7)
```sql
SET GLOBAL innodb_fast_shutdown=0;
STOP SLAVE IO_THREAD;

SHOW SLAVE STATUS\G

    Read_Master_Log_PosとExec_Master_Log_Posが一致する事を確認

STOP SLAVE ALL;
```

# Trobule

[Check status](http://qiita.com/muran001/items/14f19959d4723ffc29cc)
```sql
SHOW GLOBAL STATUS;
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

KILL xxxx;
```

## TSV/CSV

'\47' is SingleQuote.  
$0 is all words. $1-N is separate words.

TSV
```bash
$ mysql --skip-column-names -u<Account> -h<Host> -p <DataBase> -e "
<SQL>
" > /tmp/<dumpFile>.tsv

$ awk -F '\t' '{print "INSERT INTO <Table1> (<Column1>, <Column2>, ...) VALUES ("$1",\047"$2"\047,...);"}' /tmp/<dumpFile>.tsv > /tmp/<dumpFile>.sql
$ sed -i -e s/\'NULL\'/NULL/ /tmp/<dumpFile>.sql
```

CSV (Exist heder case)
```bash
$ mysql -u<Account> -h<Host> -p <DataBase> -e "
<SQL>
INTO OUTFILE '/tmp/<dumpFile>.csv' FIELDS TERMINATED BY ',';
"

$ sed -i -e '1d' /tmp/<dumpFile>.csv
$ awk -F ',' '{print "INSERT INTO <Table1> (<Column1>, <Column2>, ...) VALUES ("$1",\047"$2"\047,...);"}' /tmp/<dumpFile>.csv > /tmp/<dumpFile>.sql
$ sed -i -e s/\'NULL\'/NULL/ /tmp/<dumpFile>.sql
```

Big TSV
```bash
$ mysql --skip-column-names -u<Account> -h<Host> -p <DataBase> -e "
<SQL>
" > /tmp/<dumpFile>.tsv

$ awk -F $"\t" '{print "(\47"$1"\47, "$2", \47"$3"\47),"}' /tmp/<dumpFile>.tsv > /tmp/<dumpfile>_param.txt
$ sed -i -e s/\'NULL\'/NULL/ /tmp/<dumpfile>_param.txt
$ split -l 10000 /tmp/<dumpfile>_param.txt /tmp/<dumpfile>_param_split.
$ sed -i -e '1i INSERT INTO <Table1> VALUES' /tmp/<dumpfile>_param_split.*
$ sed -i -e '$s/),/);/' /tmp/<dumpfile>_param_split.*<commit_msg>Update MySQL memo<commit_after>MySQL
===

## ER Diagram

ALL DB
* [Windows only](http://www.wind.sannet.ne.jp/m_matsu/developer/a5m2/)

MySQL
* [MySQL Workbench](http://dev.mysql.com/downloads/workbench/)

## Best practice my.cnf

* [kamipo-san's my.cnf](https://github.com/kamipo/etcfiles/blob/master/etc/my.cnf)
 * [default_character_set, character_set_server](http://kamipo.github.io/talks/20150827-myna201508/#/step-12)

   > utf8mb4_bin or utf8mb4_general_ci

* [yoku0825-san](https://github.com/yoku0825)
 * [dotfiles my.cnf](https://github.com/yoku0825/dotfiles/blob/master/my.cnf)
 * [my_setup my.cnf](https://github.com/yoku0825/my_setup/blob/master/my.cnf)

## Add plugin?

* [semisync](https://dev.mysql.com/doc/refman/5.6/ja/replication-semisync-installation.html)
* [HandlerSocket](https://github.com/DeNA/HandlerSocket-Plugin-for-MySQL)
* [Mroonga](http://mroonga.org/ja/)
* [Q4M](http://q4m.github.io/)
* [InnoDB Memcached](http://dev.mysql.com/doc/refman/5.6/ja/innodb-memcached-setup.html)

## Slave/Master

Slave or Master  
Slave is shown at infomation. Master is empty.
```sql
SHOW MASTER STATUS\G
SHOW SLAVE  STATUS\G
```

[Slave safely stop](http://qiita.com/catatsuy/items/8f2fbea88b9bc7b8acf7)
```sql
SET GLOBAL innodb_fast_shutdown=0;
STOP SLAVE IO_THREAD;

SHOW SLAVE STATUS\G

    Read_Master_Log_PosとExec_Master_Log_Posが一致する事を確認

STOP SLAVE ALL;
```

# Trobule

[Check status](http://qiita.com/muran001/items/14f19959d4723ffc29cc)
```sql
SHOW GLOBAL STATUS;
SHOW PROCESSLIST;
SHOW FULL PROCESSLIST;

KILL xxxx;
```

## TSV/CSV

'\47' is SingleQuote.  
$0 is all words. $1-N is separate words.

TSV
```bash
$ mysql --skip-column-names -u<Account> -h<Host> -p <DataBase> -e "
<SQL>
" > /tmp/<dumpFile>.tsv

$ awk -F '\t' '{print "INSERT INTO <Table1> (<Column1>, <Column2>, ...) VALUES ("$1",\047"$2"\047,...);"}' /tmp/<dumpFile>.tsv > /tmp/<dumpFile>.sql
$ sed -i -e s/\'NULL\'/NULL/ /tmp/<dumpFile>.sql
```

CSV
```bash
$ mysql --skip-column-names -u<Account> -h<Host> -p <DataBase> -e "
<SQL>
" | sed -e 's/\t/,/g' > /tmp/<dumpFile>.csv

$ sed -i -e '1d' /tmp/<dumpFile>.csv
$ awk -F ',' '{print "INSERT INTO <Table1> (<Column1>, <Column2>, ...) VALUES ("$1",\047"$2"\047,...);"}' /tmp/<dumpFile>.csv > /tmp/<dumpFile>.sql
$ sed -i -e s/\'NULL\'/NULL/ /tmp/<dumpFile>.sql
```

CSV (Exist heder case)
```bash
$ mysql -u<Account> -h<Host> -p <DataBase> -e "
<SQL>
INTO OUTFILE '/tmp/<dumpFile>.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '';
"

$ sed -i -e '1d' /tmp/<dumpFile>.csv
$ awk -F ',' '{print "INSERT INTO <Table1> (<Column1>, <Column2>, ...) VALUES ("$1",\047"$2"\047,...);"}' /tmp/<dumpFile>.csv > /tmp/<dumpFile>.sql
$ sed -i -e s/\'NULL\'/NULL/ /tmp/<dumpFile>.sql
```

Big TSV
```bash
$ mysql --skip-column-names -u<Account> -h<Host> -p <DataBase> -e "
<SQL>
" > /tmp/<dumpFile>.tsv

$ awk -F $"\t" '{print "(\47"$1"\47, "$2", \47"$3"\47),"}' /tmp/<dumpFile>.tsv > /tmp/<dumpfile>_param.txt
$ sed -i -e s/\'NULL\'/NULL/ /tmp/<dumpfile>_param.txt
$ split -l 10000 /tmp/<dumpfile>_param.txt /tmp/<dumpfile>_param_split.
$ sed -i -e '1i INSERT INTO <Table1> VALUES' /tmp/<dumpfile>_param_split.*
$ sed -i -e '$s/),/);/' /tmp/<dumpfile>_param_split.*
{'fraction_non_alphanumeric': 0.22055388722055388, 'fraction_numerical': 0.04004004004004004, 'mean_word_length': 3.8859005704971477, 'pattern_counts': {'":': 0, '<': 87, '<?xml version=': 0, '>': 101, 'https://': 12, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 2, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '949221', 'n_tokens_mistral': 2801, 'n_tokens_neox': 2621, 'n_words': 458}