pythonでデータ分析する人のためのmySQLサーバーの構築

## 1.はじめに
目的は，
1. ubuntuサーバーにmySQLサーバーを立てる
2. csvからデータベースを作成する
3. ローカル（mac）からpythonでアクセスしてデータを取得する
をやることです．試しにIRISデータセットでやります．

上の過程を調べながらやっているのでそのメモです．
mySQLと一緒にmongoDBでも同様のことを試して使いやすさを検証していているので，多分そっちもそのうちアップします．

## 2.環境

### server
- OS : ubuntu 16.04
- mySQL : Server version: 5.7.20-0ubuntu0.16.04.1 (Ubuntu)

### local
- OS : macOS Sierra Version 10.12.6
- mysql-connector-python (8.0.5)


## 3.mySQLサーバー側の準備
### 3.1.mySQLのインストール
サーバーであるubuntu側で，mySQLサーバーとクライアントをインストールする．

```bash
$ sudo apt update
$ sudo apt install mysql-server mysql-client
```
ちなみにここでmySQLサーバーのrootパスワードの設定を要求されるのでやる．

### 3.2.mySQLのセッションを起動
```bash
$ mysql -u root -p
Enter password:


mysql>
```

ちなみに終了は以下のように行う．

```mysql
mysql> quit
```

### 3.3.databaseを作る
```mysql
mysql> CREATE DATABASE testdata;
Query OK, 1 row affected (0.00 sec)
```
testdataという名前のデータベースを作る．
このデータベースは，mySQLではスキーマとも呼ばれる．

### 3.4.databaseを確認する
```mysql
mysql> SHOW databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| testdata           |
+--------------------+
```
データベースの下にテーブルを作り，そのテーブルにデータを格納している．
testdataというデータベースが増えていることがわかる．

### 3.5.テーブルを作成する
irisという名前でテーブルを作成する．

```mysql
mysql> USE testdata;
Database changed

mysql> CREATE TABLE iris (t INT(1), sepal_len FLOAT(3),sepal_wid FLOAT(3), petal_len FLOAT(3),petal_wid FLOAT(3));
Query OK, 0 rows affected (0.04 sec)
```



### 3.6.テーブルを確認する
テーブルもデータベースと同じように確認できる．

```mysql
mysql> SHOW tables;
+--------------------+
| Tables_in_testdata |
+--------------------+
| iris               |
+--------------------+
1 row in set (0.00 sec)
```


### 3.7.データをcsvからテーブルに読み込む
まず以下のようにして，`secure_file_priv`変数の値を確認します．このディレクトリにあるファイルのインポートは許可されています．

```mysql
mysql> SHOW VARIABLES LIKE "secure_file_priv";
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)
```

`secure_file_priv`変数のディレクトリに読み込ませたいcsvを配置します．

```bash
# pwd
/var/lib/mysql-files
# ls
iris.csv
```
上のように置いて置いたら，mySQLのセッションを起動してcsvデータを読み込ませる．
ちなみにこの`iris.csv`は，5.4.この記事で使用したiris.csvの作成に作成用コードがあります．

```mysql
LOAD DATA INFILE '/var/lib/mysql-files/iris.csv'
INTO TABLE testdata.iris
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
```

試しに表示して見る．

```mysql
mysql> USE testdata;
mysql> SELECT * FROM iris;
+------+-----------+-----------+-----------+-----------+
| t    | sepal_len | sepal_wid | petal_len | petal_wid |
+------+-----------+-----------+-----------+-----------+
|    0 |       5.1 |       3.5 |       1.4 |       0.2 |
|    0 |       4.9 |         3 |       1.4 |       0.2 |
|    0 |       4.7 |       3.2 |       1.3 |       0.2 |
```


### 3.8.ユーザーを作成する
次に作ったデータベースにアクセスできるユーザーを作って行きます．

```mysql
mysql> CREATE USER 'hoge_user'@'%' IDENTIFIED BY 'hoge_password';
Query OK, 0 rows affected (0.00 sec)
```
ここで，`'hoge_user'@'%'`の%の場所にはホスト名（localhostなど）を入れる．`%`はワイルドカードで，どこからでもアクセスして良いということになる．

### 3.9.ユーザーの確認
作ったユーザーを確認する．

```mysql
mysql> SELECT Host, User FROM mysql.user;
+-----------+------------------+
| Host      | User             |
+-----------+------------------+
| %         | hoge_user        |
| %         | root             |
| %         | user             |
| localhost | debian-sys-maint |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
7 rows in set (0.00 sec)
```

### 3.10.ユーザーに権限をあげる
作ったユーザーにテーブルにアクセスできる権限を与える．

```mysql
mysql> GRANT ALL PRIVILEGES ON testdata.iris TO 'hoge_user'@'%';
Query OK, 0 rows affected (0.00 sec)
```

### 3.11.ユーザーの権限の確認
これもSHOWで確認できる．

```mysql
mysql> SHOW GRANTS for 'hoge_user'@'%';
+-----------------------------------------------------------+
| Grants for hoge_user@%                                       |
+-----------------------------------------------------------+
| GRANT USAGE ON *.* TO 'hoge_user'@'%'                        |
| GRANT ALL PRIVILEGES ON `testdata`.`iris` TO 'hoge_user'@'%' |
+-----------------------------------------------------------+
2 rows in set (0.00 sec)
```

### 3.12.リモートアクセスの設定
ローカル環境からサーバーのデータベースにアクセスできるようにする．

以下のconfigファイルを開いて，

```
$ pwd
/etc/mysql/mysql.conf.d
$ vim mysqld.cnf
```

`bind-address`をコメントアウトする．

```mysql.conf.d
# bind-address          = 127.0.0.1
```

`bind-address`で指定されたIP以外をはじくようになっており，デフォルトでは`127.0.0.1`のみ受け付けるようになっているため，コメントアウトする．（個別に指定しても良い）


## 4.ローカルからアクセスしてデータを取得する
### 4.1.pythonのmySQLコネクタをインストール
```bash
$ pip install mysql-connector-python
```

### 4.2.pythonでデータの取得
```python
import mysql.connector
import pandas as pd

cnt = mysql.connector.connect(
    host='hoge_host',
    port='3306',
    db='testdata',
    user='hoge_user',
    password='hoge_pasword'
)

db = cnt.cursor(buffered=True)

sql = 'SELECT * FROM iris'
db.execute(sql)

rows = db.fetchall()
data = pd.DataFrame(rows, columns=db.column_names)
```

`host`にはIPアドレスを渡します．
`port`の3306はデフォルトです．

```python
data
#     t  sepal_len  sepal_wid  petal_len  petal_wid
#0    0        5.1        3.5        1.4        0.2
#1    0        4.9        3.0        1.4        0.2
#2    0        4.7        3.2        1.3        0.2
```

## 5.その他
### 5.1.mySQLの自動起動
```
$ sudo apt-get install sysv-rc-conf
Reading package lists... Done
Building dependency tree
Reading state information... Done
...

$ sudo sysv-rc-conf mysql on
$ sudo sysv-rc-conf --list | grep mysql
mysql        0:off      1:off   2:on    3:on    4:on    5:on    6:off
```
2,3,4,5がonになって入れば良い．この数字はrun-levelと言って，意味はここ（[run-level](https://debian-administration.org/article/212/An_introduction_to_run-levels)）を参照．

### 5.2.portが解放されているか確認する
```
$ netstat -untap
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      -
```
LISTENだと解放されている．

### 5.3.mySQLで設定されているポートの確認
```mysql
mysql> SHOW VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.00 sec)
```

### 5.4.この記事で使用したiris.csvの作成
おまけだがirisデータで再現したい人用に．

```python:make_iriscsv.py
import sklearn.datasets as data
import numpy as np
import pandas as pd

iris_dic = data.load_iris()
d = np.c_[iris_dic["target"],iris_dic["data"]]
df = pd.DataFrame(d)
names = np.array(iris_dic["feature_names"])
names = np.insert(names, 0, "t")
df.to_csv("./iris.csv", index=False)
```

## 6.おわりに
- この記事を書いておいてアレですが，ぶっちゃけデータ分析用のデータをデータベースで管理する積極的な理由というのが思いつかない...プロジェクトごとのディレクトリにcsvなりバイナリで保存の方がむしろ便利な気もしないでもない．こんな時に使うと便利みたいなものがあったら教えて欲しいです．

## 7.おわりに2
（2019/02/04追加）
データがオンラインに増え続ける時以外旨味ないかなあとおもってたけど、bokehで色々と条件変えてダッシュボードっぽいものを表示するときにmongoDBを使ったら結構便利だった。
データの型が上手くarrayやdata frameに合わないときに特に良いようだ。