Python
MySQL
データ分析

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

More than 1 year has passed since last update.

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をコメントアウトする.

mysql.conf.d
# 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データで再現したい人用に.

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なりバイナリで保存の方がむしろ便利な気もしないでもない.こんな時に使うと便利みたいなものがあったら教えて欲しいです.