K-디지털트레이닝(빅데이터) 59일차

오늘은 centos에 mysql을 설치하고 jupyter 사용을 하는 방법에 관해 배웠다.

Virtual Environment

• virtual box download (oracle virtualbox)
• https://www.virtualbox.org/wiki/Downloads (window host 클릭)
• 환경설정 (가상머신 이름, os 종류), 메모리설정,

network 설정

#ping google.com(address)
#cd /etc/sysconfig/network-scripts
#vi ifcfg-enp0s3 (외부에 접속하는 network config -- onboot=yes no-->yes)
#vi ifcfg-enp0s8 (외부에서 나의 서버로 접속하는 network config -- onboot=yes no-->yes)
#reboot
-내부(enp0s8) ip를 확인하기 위해서
#yum install net-tools (ifconfig)
#ifconfig (내부 외부 ip확인)

putty(터미널프로그램)로 접속

-root user의 권한을 다른 user부여
#adduser bigdata
#passwd bigdata

• sudoer설정
  #chmod +w /etc/sudoers
  #vi /etc/sudoers
  -101번째 라인에 다음 내용을 추가
  bigdata ALL=(ALL) ALL
  저장후 나옴
  #chmod -w /etc/sudoers

-anaconda download (wget 필요)
$sudo yum install wget$wget https://repo.anaconda.com/archive/Anaconda3-2021.05-Linux-x86_64.sh
$ls -al

• 실행권한 부여 Anaconda3-2021.05-Linux-x86_64.sh
  $chmod +x Anaconda3-2021.05-Linux-x86_64.sh

• 콘다 설치
  $./Anaconda3-2021.05-Linux-x86_64.sh$source .bashrc (bashrc에 경로 저장)
  $conda (실행확인)

• 가상환경 설정
  $conda env list (가상환경 리스트)$conda create -n ml python=3.9 (python3.9버전의 가상환경)
  $conda activate ml$conda install pandas
  $conda install numpy$conda install matplotlib
  $conda install scikit-learn$conda install jupyter notebook
  $pip ipykernel$python -m ipykernel --user --name ml

-mysql 설치
$sudo yum update$sudo yum install mysql-server
$sudo systemctl start mysqld

-mysql 접속 및 user설정
$mysql -u root

show databases;
use mysql

• user 생성
  create user bigdata identified by "bigdata1234";
• 권한부여
  grant all privileges on . to "bigdata"@"%" with grant option;
  quit;

• bigdata user로 mysql 재접속
  $mysql -u bigdata -p

  create database bigdata;
  use bigdata;

-예제 데이터 다운로드
local 에서 web browser로 접속
https://blog.sqlauthority.com/2020/02/15/mysql-download-sample-database-sakila-world-employee/
$wget https://downloads.mysql.com/docs/sakila-db.zip$sudo yum install unzip
$unzip sakila-db.zip$cd sakila-db
$mysql -u bigdata -p

source sakila-schema.sql;
source sakila-data.sql;
show databases;
use sakila;

$netstat -tnlp
-- user, password, host, port, database

--

jupyter notebook --ip=192.168.37.3 --port=8888

inet

jupyter notebook --ip=0.0.0.0 --port=8888 --NotebookApp.token='' --NotebookApp.password=''

python -m ipykernel install --user --name ml

sudo systemctl resrtart mysqld

mysql -u root

create user bigdata identified by "dbgusals1";

grant all privileges on . to "bigdata"@"%" with grant option;

create database bigdata;
create table big(name varchar(50), age varchar(3));

wget 사킬라 깔기

sakila-db

source sakila

pip install sqlalchemy
pip install pymysql

mkdir work
cd work

vi note.sh

jupyter notebook --ip=0.0.0.0 --port=8888 --NotebookApp.token='' --NotebookApp.password=''

chmod +x note.sh

vi .bashrc
source .bashrc

source activate ml

PATH=$PATH:/home/bigdata/work

export PATH

---

ml - jupyter

import numpy as np
import pandas as pd
from sqlalchemy import text, create_engine

protocol = "mysql+pymysql://"
user = "bigdata"
passwd = "dbgusals1"
host = "@localhost"
port = "3306"
database="sakila"
engine = create_engine(protocol+user+":"+passwd+'host'+':'+port+'/'+database)

conn = create_engine("mysql+pymysql://bigdata:dbgusals1@localhost:3306/sakila")

stmt = """
SELECT first_name,
last_name,
last_update
FROM actor
WHERE last_name = 'DAVIS'
"""
pd.read_sql(stmt,conn)

pd.read_sql("select * from actor",conn)

stmt = """
select table_name,
table_rows
from information_schema.tables
where table_schema = 'sakila'
"""
pd.read_sql(stmt,conn)