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サーバーとクライアントをインストールする．
$ sudo apt update
$ sudo apt install mysql-server mysql-client
ちなみにここでmySQLサーバーのrootパスワードの設定を要求されるのでやる．
3.2.mySQLのセッションを起動
$ mysql -u root -p
Enter password:
mysql>
ちなみに終了は以下のように行う．
mysql> quit
3.3.databaseを作る
mysql> CREATE DATABASE testdata;
Query OK, 1 row affected (0.00 sec)
testdataという名前のデータベースを作る．
このデータベースは，mySQLではスキーマとも呼ばれる．
3.4.databaseを確認する
mysql> SHOW databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdata |
+--------------------+
データベースの下にテーブルを作り，そのテーブルにデータを格納している．
testdataというデータベースが増えていることがわかる．
3.5.テーブルを作成する
irisという名前でテーブルを作成する．
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> SHOW tables;
+--------------------+
| Tables_in_testdata |
+--------------------+
| iris |
+--------------------+
1 row in set (0.00 sec)
3.7.データをcsvからテーブルに読み込む
まず以下のようにして，secure_file_priv変数の値を確認します．このディレクトリにあるファイルのインポートは許可されています．
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を配置します．
# pwd
/var/lib/mysql-files
# ls
iris.csv
上のように置いて置いたら，mySQLのセッションを起動してcsvデータを読み込ませる．
ちなみにこのiris.csvは，5.4.この記事で使用したiris.csvの作成に作成用コードがあります．
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> 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> CREATE USER 'hoge_user'@'%' IDENTIFIED BY 'hoge_password';
Query OK, 0 rows affected (0.00 sec)
ここで，'hoge_user'@'%'の%の場所にはホスト名（localhostなど）を入れる．%はワイルドカードで，どこからでもアクセスして良いということになる．
3.9.ユーザーの確認
作ったユーザーを確認する．
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> GRANT ALL PRIVILEGES ON testdata.iris TO 'hoge_user'@'%';
Query OK, 0 rows affected (0.00 sec)
3.11.ユーザーの権限の確認
これもSHOWで確認できる．
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をコメントアウトする．
# bind-address = 127.0.0.1
bind-addressで指定されたIP以外をはじくようになっており，デフォルトでは127.0.0.1のみ受け付けるようになっているため，コメントアウトする．（個別に指定しても良い）
4.ローカルからアクセスしてデータを取得する
4.1.pythonのmySQLコネクタをインストール
$ pip install mysql-connector-python
4.2.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はデフォルトです．
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）を参照．
5.2.portが解放されているか確認する
$ netstat -untap
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN -
LISTENだと解放されている．
5.3.mySQLで設定されているポートの確認
mysql> SHOW VARIABLES LIKE 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port | 3306 |
+---------------+-------+
1 row in set (0.00 sec)
5.4.この記事で使用したiris.csvの作成
おまけだがirisデータで再現したい人用に．
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に合わないときに特に良いようだ。