Transcript MySQL - SPARCS - SPARCS에 오신 것을 환영합니다!

MySQL
argon
Contents
• 1. MySQL이란 무엇인가?
• 2. MySQL의 특징
• 3. MySQL 설치, 실행, 사용자 관리
• 4. MySQL 사용하기
• 5. 서버 설정 및 관리
1. MySQL이란 무엇인가?
• MySQL?
• DBMS?
• RDBMS?
• RDB?
• SQL?
• MySQL!
MySQL?
• 정의
– SQL을 사용하는 오픈 소스 RDBMS
SQL?
RDBMS?
Database?
• 정의
– 여러 사람에 의해 공유되어 사용될 목적으로 통합하
여 관리되는 데이터의 집합
• 특징
–
–
–
–
실시간 접근성
지속적인 변화
동시 공유
내용에 대한 참조
장점
데이터 중복 최소화
데이터 공유
일관성, 무결성, 보안성 유지
최신의 데이터 유지
데이터의 표준화 가능
데이터의 논리적, 물리적 독립성
용이한 데이터 접근
데이터 저장 공간 절약
DBMS?
• 정의
– DataBase Managaement System
– 즉, 데이터베이스 관리 시스템
• 다수의 컴퓨터 사용자들이 데이터베이스 안에
데이터를 기록하거나 접근할 수 있도록 해주는
프로그램
RDBMS?
• 정의
– Relational DataBase Management System
– RDB(Relational Database)를 관리하기 위한 시
스템
RDB?
• 정의
– Relational DataBase
– Entity와 Relation
– Entity간의 Relation을 table로써 표현
RDB? RDBMS?
Table
Tuple
학번
…
…
…
…
…
…
이름
…
…
…
…
…
…
아이디
…
…
…
…
…
…
Attribute
Controlled
by
RDBMS
나이
…
…
…
…
…
…
학과
…
…
…
…
…
…
SQL?
• 정의
– Structured Query Language: 구조화 질의어
– 관계형 데이터베이스 관리 시스템(RDBMS)의
데이터를 관리하기 위해 설계된 특수 목적의
프로그래밍 언어
MySQL!
• 정의
– SQL을 사용하는 오픈 소스 RDBMS
• 왜 MySQL요?(Why MySQL?)
2. MySQL의 특징
• MySQL의 장점
• Table Type
– MyISAM vs InnoDB
– transaction?
– Locking
Why MySQL?
–
–
–
–
–
–
–
–
–
–
Scalability and Flexibility
High Performance
High Availability
Robust Transactional Support
Web and Data Warehouse Strengths
오호..
Strong Data Protection
Comprehensive Application Development
Management Ease
Open Source Freedom and 24 x 7 Support
Lowest Total Cost of Ownership
라고 홍보하네요.
MySQL Table Type(1)
ISAM
MYSQL 3.x버전까지의 테이블
테이블 최대용량은 4GB
5.0 버전부터 사라짐
MyISAM (MySQL 5.6이전 Default)
MYSQL 4.x버전의 기본 테이블 타입
ISAM의 확장
용량은 OS에 달림
작은 규모의 DB에 적합
MERGE
여러 개의 MyISAM 테이블을 하나로 취급하여 MyISAM의 용량 제한을
극복한 타입
MySQL Table Type(2)
InnoDB (MySQL 5.6 이후 default)
트랜잭션, Foreign key, row-level locking 지원
용량 제한이 거의 없음
MyISAM에 비해 용량을 많이 사용함
대규모의 DB에 적합
BDB
트랜잭션, page-level locking 지원
데이터는 옮길 수 없음
HEAP
메모리를 저장공간으로 사용
엑세스 타임이 빨라 임시 테이블이나 검색결과 테이블 등으로
활용
MyISAM vs InnoDB
Transaction?
• 쪼갤 수 없는 업무 처리의 단위!
• Transaction 실행 양식
– Begin the transaction
– Execute several queries
– Commit the transaction
• 쿼리 하나가 실패하면 Database system은 전
체 트랜잭션 또는 실패한 쿼리를 롤백 한다.
Locking?
• database의 수정을 차단
• 다수의 user들이 동일한 database를 동시
에 수정하면 문제가 발생
– >이를 방지한다.
• Locking의 단위에 따라 속도 차이
3. MySQL 설치, 실행, 사용자관리
• 설치
• 실행
– 서버 실행
– 로그인
• 사용자 관리
–
–
–
–
사용자
사용자
사용자
사용자
추가
권한 부여 // MySQL의 권한 테이블
외부 접속
삭제
설치
• MySQL 설치하기
– #apt-get install mysql-common mysqlserver mysql-client
– 설치 중에 root의 비밀번호를 생성
– 설치가 완료되면 msyql 데몬이 실행됨
– 소스를 이용한 설치는 reno의 mysql 세미나를
참고합시다.
(http://dev.mysql.com/doc/refman/5.7/en/sour
ce-installation.html)
서버 실행
• MySQL 서버 실행 및 종료
– #/etc/init.d/mysql start|stop|restart
• apt로 설치한 경우
로그인
• MySQL로 서버에 접속하기
• $mysql
–
–
–
–
–
u[username] default
p(password):
h[host address]:
P[port]:
S[socket address]
:
connected username
confirm password needed
default: localhost
default: 3306
mysql [login] [database]로 접속합니다.
mysql –u yasik –p –h wseminar3.sparcs.org
사용자 추가
• MySQL 사용자관리
– 사용자 추가하기
1. GRANT
grant all privileges on 접근 허용 할 db이름.* to ‘new_user_id’@’%’
IDENTIFIED BY ‘new_user_password’ with grant option
모든 테이블에 접근하기 위해서는 접근 허용 할 db이름.* 에 *.*
‘new_user_id@’%’ 에서 ‘%’의 의미는 신규 계정이 접속할 위치의 제한
을 두지 않겠다는 의미
ex) ‘new_user_id’@’localhost’ loacalhost로 접속을 하였을 경우만 허용
사용자 추가
• MySQL 사용자관리
– 사용자 추가하기
2. 직접 사용자 테이블에 등록
: 사용자ID가 저장되어있는 테이블에 직접 추가해주는 방법
Use mysql; //mysql 테이블로 이동
Insert into user (host, user, password) values (‘%’, ‘user_id’,
password(‘user_password))’;
//mysql의 경우 사용자 패스워드를 ‘MD5’형태의 암호화 처리하여 관리하
므로 직접 등록시 password()함수를 통해 패스워드를 MD5로 암호화 하여
저장
사용자 추가
• MySQL 사용자관리
– 사용자 추가하기
2. 직접 사용자 테이블에 등록
사용자 계정 추가 후 ‘DB’ 테이블에 사용할 데이터 베이스 접근 권한을 추
가하여야 합니다.
MySQL의 권한 테이블
• MySQL의 mysql 데이터베이스에는 권한 테이블
이 존재!
이들 중 우리가 살펴볼 것은
columns_priv
db
tables_priv
user
MySQL의 권한 테이블
테이블 명
테이블의 역할
user
user table은 MySQL 사용자 이름과 암호, 어떤 호스트에서
접속이 가능한지, 해당 사용자는 어떤 권한을 갖는지를 지정
합니다.
db
tables_priv
columns_priv
db테이블은 데이터베이스 차원의 권한 테이블입니다.
tables_priv는 테이블 차원의 권한 테이블입니다.
columns_priv는 column 차원의 권한 테이블입니다.
User 테이블 검사
(권한 있으면 query 실행)
클라이언트
Query
db 테이블 검사
(권한 있으면 query 실행)
Tables_priv 테이블 검사
(권한 있으면 query 실행)
권한이 없을 경우
에러 메시지 출력
Columns_priv 테이블 검사
(권한 있으면 query 실행)
MySQL의 권한 테이블
• User table 사용하기
MySQL의 권한 테이블
• User table 사용 방법
– user table에는 이미 모든 user가 등록 되어 있다.
– 뒤에 배울 update를 이용하여 user의 권한을 설정하
면 된다.
– update table_name set column1_name=value1,
column2_name=value2, ... where condition;
MySQL의 권한 테이블
• db table 사용 방법
MySQL의 권한 테이블
• db table 사용 방법
– db table은 user table과 달리 모든 user가 기존에 등
록되어 있지 않다.
– 따라서 모든 user에 대해 새로 등록 해 주어야 한다.
– 그러므로 뒤에서 배울 insert를 사용한다.
– insert into table_name (column1_name,
column2_name, ...) values ( value1, value2, ...)
MySQL의 권한 테이블
– tables_priv, columns_priv table의 사용 방법
MySQL의 권한 테이블
– tables_priv, columns_priv table의 사용 방법
• db table과 거의 유사합니다.
Grantor는 뭐야?
누가
Timestamp는?
언제
권한을 부여해 주었는가?
사용자 외부 접속
• MySQL 사용자관리
– 사용자 외부 접속 허용
/etc/mysql/my.cnf에서
bind address=127.0.0.1을 주석처리
mysql에서
use mysql;
select host, user, password from user;
update user set host=‘%’ where user=‘user_id’ and
host = ‘old host’
flush privileges;
사용자 삭제
• MySQL 사용자 관리
– 사용자 삭제하기
사용자 삭제 시에는 테이블에서 삭제하면 됩니다.
delete from user where user = ‘user_id’;
4. MySQL 사용하기
• 기초 문법
• Data Type
• Query
• Condition
• Wildcard
• Index
• Slowquery
기초 문법
• 기초 문법
–
–
–
–
명령어는 항상 ;나 \g 로 끝난다.
\q, ^D, quit, exit 명령어로 접속 종료
command clear 는 \c
도움말은 \h
Data Type
• Data Type
–
–
–
–
–
–
–
–
–
–
varchar(N): 가변 길이 문자열
char(N): 고정 길이 문자열
text: 최대 65536개의 문자 저장
blob: 최대 65536개의 문자 저장
int: 정수형
float: 실수형
date: 날짜(yy-mm-dd)
time : 시간(hh:mm:ss)
datetime
timestamp
Query
• SQL 주요 명령어(Query)
–
–
–
–
–
–
–
–
–
create
insert
select
update
delete
show
explain
describe
alter
지금 모든 query 를 다루기엔 너무 많다 @_@
그때그떄 필요한걸 찾아쓰자!
Query
• create
– : DB, 테이블, column 생성
– create database db_name;
– use db_name;
– create table table_name (column_name type, ...);
Query
• insert
– : 테이블에 record 추가
– use db_name;
– insert into table_name (column1_name, column2_name, ...)
values ( value1, value2, ...)
– 축약형
– insert into table_name values (value1, value2, ...)
Query
• Show, explain, describe
– DB와 테이블에 대한 정보를 얻어 옴
– show db;
– show tables from db;
– show columns from table;
=explain table;
=describe table;
=desc table;
Query
• select
– 테이블에서 record 검색
– user db_name;
– select column1_name, column2_name, ... from table_name
(where condition);
– *는 모든 column이라는 의미로 쓰일 수 있다.
Query
• update
– update: 레코드 수정
– use db_name;
– update table_name set column1_name=value1,
column2_name=value2, ... where condition;
– (where condition을 안 주면 모든 column의 모든 데이터가 바뀜)
Query
• delete
– :레코드 삭제
– user db_name;
– delete from table_name where conditions;
– where conditions를 안 주면 모든 레코드가 삭제됨!
Query
• drop
– : db, 테이블을 삭제
– drop database db_name;
– use db_name;
– drop table table_name
Condition
• Condition
– 앞서 where 뒤에 있던 condition
– where 필드명=조건값
– where 필드명 like %찾을 문자
가 기본적인 형태
Wildcard
•
•
•
•
•
•
. 는 문자 하나
* 는 앞에 나온 문자의 0개 이상의 반복
^는 문자열의 처음
$는 문자열의 끝
[,]는 괄호 안의 문자열 일치를 확인
{N}는 앞에 나온 문자의 N번의 반복을 나
타냅니다.
Index
• create table sparcs(id text, name text,
age int);
• 에 대해 select를 실행하면, 레코드 수가 증
가할 수록 시간이 점차 늘어난다.
• 모든 레코드에 대해 검사를 수행하기 때문!
Index를 쓰면 됩니다 ㅇㅇ
Index
• create table sparcs(id text not null
default, name text, age int, INDEX
myindex1 (id)};
• index를 설정할 필드는 null이나 default가
될 수 없다.
• index를 설정하면 query시간이 거의 일정!
Index
• Index를 사용하지 않는 query?
• 쿼리문 앞에 explain을 붙였을 때 type의
결과값이
– ALL이면 인덱스 X
– range, index 등이면 인덱스 O
Slowquery
• 일정 시간을 초과하는 쿼리를 로그로 남김
으로써 로그 분석을 통해 쿼리 성능을 개
선
• my.cnf에 다음을 추가한다.
– [mysqld]
– log-slow-queries = /temp/mysql-slow.log
– long_query_time=2 // 수행 시간이 2초가 넘
는 쿼리를 로그로 남김
5. 서버 설정 및 관리
• mysql 환경설정
• mysqladmin
• mysqldump
mysql 환경설정
– /etc/mysql/my.cnf : MySQL 모든 프로그램에
대한 옵션 설정
– DATADIR/my.cnf: 서버에 관련된 옵션
• DATADIR은 /etc/my.cnf 의 [mysqld]에 있음
– $HOME/my.cnf : 개인 사용자의 옵션
mysql 환경설정
• 기본 설정 예시
– 예를 들어 인코딩 기본값을 UTF-8로 설정하려
면 [mysqld]아래에 다음을 추가하면 됩니다.
mysql 환경설정
• my.cnf를 열어 봅시다.
mysqladmin
• mysqladmin
•
–
–
–
–
–
–
–
Mysql 관리자
mysqladmin [login]
mysqladmin [login]
mysqladmin [login]
지 확인
mysqladmin [login]
mysqladmin [login]
mysqladmin [login]
mysqladmin [login]
create [db_name]
drop [db_name]
ping : mysqld 가 돌아가는
status : mysql 상태
extended-status
shutdown
processlist
= mysqladmin [login] process : 실행중인 클라이언트 목록
– mysqladmin [login] variables : 변수, 값 출력
mysqldump
• mysqldump
• MySQL 백업 유틸리티
– mysqldump [login] –A –-add-drop-table >
[bak]
•
전체 데이터베이스 백업
•
선택한 데이터베이스 백업
•
선택한 데이터베이스의 테이블 을 선택하여 백업
•
보통 .sql 확장자를 붙여서 백업함
– mysqldump [login] –B [options] [db1] [db2]
… > [bak]
– mysqldump [login] [db name] [tb1] [tb2] … >
[bak]
mysqldump
• mysqldump
• 데이터 복구 시
– mysql [login] < [bak]
•
여러 개의 데이터베이스 복구 ( -B로 백업한 경우)
•
•
선택한 데이터베이스 백업(하나씩 백업한 경우)
db는 생성 되어있어야 함
– mysql [login] [db_name] < [bak]
한글이 깨지면 –-default-character-set 옵션을 이용
하자.
혹은 mysql 옵션에서 ascii 코드 외의 문자를 ?로
표기하는 옵션이 켜져 있을수도 있으니 확인!
Citation
•
•
•
•
reno 선배님의 mysql 세미나
grandmarnier 선배님의 mysql 세미나
yasik 회원의 mysql 세미나
zzongaly 선배님의 relational model 세미
나
Reference
• http://dev.kthcorp.com/2011/06/17/why-we-need-to-use-innodbon-mysql-vs-myisam-comparison/
• https://project.sparcs.org/arara/wiki/ARAraEngine/EnvironmentHo
wto
• http://blog.naver.com/kilsu1024?Redirect=Log&logNo=110164289
473
• http://blog.naver.com/nwind7?Redirect=Log&logNo=10018852177
4
• http://dev.mysql.com/doc/refman/5.7/en/index.html