[Windows에서 MySQL 사용법]
Docker 설치 후, mysql를 pull하여 image 다운 후 실행
> docker run --name mysql_test -e MYSQL_ROOT_PASSWORD=1234 -d
-p 3306:3306 mysql:latest
> docker exec -it ee18155087ae /bin/bash
# mysql -u root -p
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
: 에러 메세지 나옴
> docker ps -a
ee18155087ae mysql:latest "docker-entrypoint.s…" 5 minutes ago Exited (0) 7 seconds ago mysql-container
598d87a1bddc mysql "docker-entrypoint.s…" 8 days ago Exited (255) 41 hours ago 0.0.0.0:3306->3306/tcp, 33060/tcp mysql_test
....
이미 실행된 후 정지된 container있음(port 3306번을 이미 물고 있어서 오류가 남).
실행 중인 container 모두 정지 후, 기존 container 제거.
> docker stop ee18155087ae # 실행 중인 container들 정지
> docker rm -f 598d87a1bddc # 기존 container 제거
> docker restart ee18155087ae # 재시작
...
# mysql -u root -p
비번 1234 넣으니 정상 실행 되며, mysql> prompt로 들어감
db생성하고, 생성된 db 선택 후, table 생성. data를 몇 개 삽입.
mysql> create database opentutorials; # db생성
mysql> drop database opentutorials; # db제거
mysql> show databases; # db보기
mysql> use opentutorials; # db선택
mysql> create table topic( # ";" 안 넣으면 ->로 넘어감
-> id int(11) not null auto_increment,
-> title varchar(100) not null,
-> description text null,
-> created datetime not null,
-> author varchar(30 null,
-> profile varchar(100) null,
-> primary key(id));
mysql> show tables; # 생성 table 확인
mysql> desc topic; # 입력 속성 확인(description)
mysql> insert into topic (title,description,created,author,profile)
values('mysql','mysql is ...',NOW(),'kdj','developer');
mysql> select * from topic; # 삽입된 데이터 확인
mysql> insert into topic (title,description,created,author,profile)
values('oracle','oracle is ...',NOW(),'egoing','developer');
mysql> select * from topic;
mysql> insert into topic (title,description,created,author,profile)
values('postgresql','postgresql is ...',NOW(),'egoing','data scientist, developer');
mysql> insert into topic (title,description,created,author,profile)
values('mongdb','mongodb is ...',NOW(),'egoing','developer');
일부 data만 선택하여 확인해 보기
mysql> select id,title,created,author from topic; # 일부 속성만 선택해 data확인
# author지정 data 선택 / 순서까지 지정 / 건수도 지정
mysql> select id,title,created,author from topic where author='egoing';
mysql> select id,title,created,author from topic where author='egoing' order by id desc;
mysql> select id,title,created,author from topic where author='egoing'
order by id desc limit 2;
mysql>
[python 연동]
> pip install mysql-connector-python
import mysql.connector
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="1234",
database="opentutorials"
)
mycursor = mydb.cursor()
mycursor.execute("SELECT * FROM topic")
myresult = mycursor.fetchall()
for x in myresult:
print(x)
mycursor.execute("insert into topic (title,description,created,author,profile)
values('sqlserver','sqlserver is ...',NOW(),'kaka','database administrator')");
# 데이터베이스 변경 내용 저장
mydb.commit()
출력물:
(1, 'mysql', 'mysql is ...', datetime.datetime(2023, 5, 10, 2, 39, 5), 'kdj', 'developer')
(2, 'oracle', 'oracle is ...', datetime.datetime(2023, 5, 10, 2, 41, 9), 'egoing', 'developer')
(3, 'postgresql', 'postgresql is ...', datetime.datetime(2023, 5, 10, 2, 43, 5), 'hahaha', 'data scientist, developer')
(4, 'mongdb', 'mongodb is ...', datetime.datetime(2023, 5, 10, 2, 43, 46), 'kaka', 'developer')
(5, 'sqlserver', 'sqlserver is ...', datetime.datetime(2023, 5, 10, 5, 16, 31), 'kaka', 'database administrator')
[References]
1. https://opentutorials.org/course/3161