MySQL argon.

Presentation on theme: "MySQL argon."— Presentation transcript:

1 MySQL argon

2 Contents 1. MySQL이란 무엇인가? 2. MySQL의 특징 3. MySQL 설치, 실행, 사용자 관리
5. 서버 설정 및 관리

3 1. MySQL이란 무엇인가? MySQL? DBMS? RDBMS? RDB? SQL? MySQL!

4 MySQL? 정의 SQL을 사용하는 오픈 소스 RDBMS SQL? RDBMS?

5 Database? 정의 특징 여러 사람에 의해 공유되어 사용될 목적으로 통합하여 관리되는 데이터의 집합 실시간 접근성
지속적인 변화 동시 공유 내용에 대한 참조 장점 데이터 중복 최소화 데이터 공유 일관성, 무결성, 보안성 유지 최신의 데이터 유지 데이터의 표준화 가능 데이터의 논리적, 물리적 독립성 용이한 데이터 접근 데이터 저장 공간 절약 자료를 고도로 구조화함으로써 검색과 갱신을 효율화

6 DBMS? 정의 DataBase Managaement System 즉, 데이터베이스 관리 시스템
다수의 컴퓨터 사용자들이 데이터베이스 안에 데이터를 기록하거나 접근할 수 있도록 해주는 프로그램

7 RDBMS? 정의 Relational DataBase Management System
RDB(Relational Database)를 관리하기 위한 시스템

8 RDB? 정의 Relational DataBase Entity와 Relation
Entity간의 Relation을 table로써 표현 계층형 데이터 모델 HDB 네트워크형 데이터 모델 NDB 관계형 데이터 모델 객체지향형 데이터 모델 OODB Entity와 Relation으로 이루어짐 Relational model은 가장 점유율이 높은 data model로 table에 기반한 모델

9 RDB? RDBMS? Table 학번 이름 아이디 나이 학과 … … … … Tuple … … Attribute
Attribute: column Tuple: row Domain: data type relation: 1개의 table schema: database의 논리적 구조로 변환이 불가능하다. - Relation name, attribute name, domain definition Instance:Schema에 실제 들어있는 정보로 변환이 가능하다. key key: tuple을 구별하기 위한 attribute의 집합 superkey: relation 에서 tuple을 식별할 수 있는 unique한 attribue의 집합 candidate key: superkey 중에서 minimal key primary key: candidate key중 하나이며 null이 될 수 없다. foreign key: relation을 연결, 참조 해주는 키로 참조되는 relation에서는 primary key이여야 한다. 이름 id 학과 가 있으면 id / id, 이름 / id, 학과/ id, 이름, 학과/는 super key id는 candidate key id는 primary key(중복 금지) Attribute Controlled by RDBMS

10 SQL? 정의 Structured Query Language: 구조화 질의어
관계형 데이터베이스 관리 시스템(RDBMS)의 데이터를 관리하기 위해 설계된 특수 목적의 프로그래밍 언어

11 MySQL! 정의 SQL을 사용하는 오픈 소스 RDBMS 왜 MySQL요?(Why MySQL?)

12 2. MySQL의 특징 MySQL의 장점 Table Type MyISAM vs InnoDB transaction?
Locking

13 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 오호.. 라고 홍보하네요.

14 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의 용량 제한을 극복한 타입

15 MySQL Table Type(2) InnoDB (MySQL 5.6 이후 default) 트랜잭션, Foreign key, row-level locking 지원 용량 제한이 거의 없음 MyISAM에 비해 용량을 많이 사용함 대규모의 DB에 적합 BDB 트랜잭션, page-level locking 지원 데이터는 옮길 수 없음 HEAP 메모리를 저장공간으로 사용 엑세스 타임이 빨라 임시 테이블이나 검색결과 테이블 등으로 활용

16 MyISAM vs InnoDB

17 Transaction? 쪼갤 수 없는 업무 처리의 단위! Transaction 실행 양식
Begin the transaction Execute several queries Commit the transaction 쿼리 하나가 실패하면 Database system은 전체 트랜잭션 또는 실패한 쿼리를 롤백 한다.

18 Locking? database의 수정을 차단 다수의 user들이 동일한 database를 동시에 수정하면 문제가 발생
>이를 방지한다. Locking의 단위에 따라 속도 차이

19 3. MySQL 설치, 실행, 사용자관리 설치 실행 사용자 관리 서버 실행 로그인 사용자 추가
사용자 외부 접속 사용자 삭제

20 설치 MySQL 설치하기 #apt-get install mysql-common mysql-server mysql-client
설치 중에 root의 비밀번호를 생성 설치가 완료되면 msyql 데몬이 실행됨 소스를 이용한 설치는 reno의 mysql 세미나를 참고합시다. (

21 서버 실행 MySQL 서버 실행 및 종료 #/etc/init.d/mysql start|stop|restart
apt로 설치한 경우

22 로그인 MySQL로 서버에 접속하기 $mysql u[username] default : connected username
p(password): confirm password needed h[host address]: default: localhost P[port]: default: 3306 S[socket address] mysql [login] [database]로 접속합니다. mysql –u yasik –p –h wseminar3.sparcs.org -p 뒤에 공백 없이 패스워드를 쳐도 접속은 가능합니다만 보안상 추천하지 않습니다

23

24 사용자 추가 MySQL 사용자관리 사용자 추가하기 GRANT
grant all privileges on 접근 허용 할 db이름.* to IDENTIFIED BY ‘new_user_password’ with grant option 모든 테이블에 접근하기 위해서는 접근 허용 할 db이름.* 에 *.* 에서 ‘%’의 의미는 신규 계정이 접속할 위치의 제한을 두지 않겠다는 의미 ex) loacalhost로 접속을 하였을 경우만 허용

25 사용자 추가 MySQL 사용자관리 사용자 추가하기 2. 직접 사용자 테이블에 등록
: 사용자ID가 저장되어있는 테이블에 직접 추가해주는 방법 Use mysql; //mysql 테이블로 이동 Insert into user (host, user, password) values (‘%’, ‘user_id’, password(‘user_password))’; //mysql의 경우 사용자 패스워드를 ‘MD5’형태의 암호화 처리하여 관리하므로 직접 등록시 password()함수를 통해 패스워드를 MD5로 암호화 하여 저장

26 사용자 추가 MySQL 사용자관리 사용자 추가하기 2. 직접 사용자 테이블에 등록
사용자 계정 추가 후 ‘DB’ 테이블에 사용할 데이터 베이스 접근 권한을 추가하여야 합니다.

27 MySQL의 권한 테이블 MySQL의 mysql 데이터베이스에는 권한 테이블이 존재! 이들 중 우리가 살펴볼 것은
columns_priv db tables_priv user

28 MySQL의 권한 테이블 테이블 명 테이블의 역할 user
user table은 MySQL 사용자 이름과 암호, 어떤 호스트에서 접속이 가능한지, 해당 사용자는 어떤 권한을 갖는지를 지정합니다. db db테이블은 데이터베이스 차원의 권한 테이블입니다. tables_priv tables_priv는 테이블 차원의 권한 테이블입니다. columns_priv columns_priv는 column 차원의 권한 테이블입니다. ZippyZiggy MySQL (허정수, 한규섭 지음)

29 클라이언트 Query 권한이 없을 경우 에러 메시지 출력 User 테이블 검사 (권한 있으면 query 실행)
db 테이블 검사 (권한 있으면 query 실행) Query Tables_priv 테이블 검사 (권한 있으면 query 실행) 권한이 없을 경우 에러 메시지 출력 Columns_priv 테이블 검사 (권한 있으면 query 실행)

30 MySQL의 권한 테이블 User table 사용하기

31 host: 해당 사용자가 연결 가능한 호스트 지정, host 이외의 호스트에서는 연결할 수 없음 (%는 모든 host에서 허용)
User: 사용자 이름 Password: 사용자 암호 ㅁㅁㅁㅁㅁ_priv: ㅁㅁㅁㅁㅁ에 대한 권한

32 MySQL의 권한 테이블 User table 사용 방법 user table에는 이미 모든 user가 등록 되어 있다.
뒤에 배울 update를 이용하여 user의 권한을 설정하면 된다. update table_name set column1_name=value1, column2_name=value2, ... where condition;

33

34

35

36 MySQL의 권한 테이블 db table 사용 방법

37

38 MySQL의 권한 테이블 db table 사용 방법
db table은 user table과 달리 모든 user가 기존에 등록되어 있지 않다. 따라서 모든 user에 대해 새로 등록 해 주어야 한다. 그러므로 뒤에서 배울 insert를 사용한다. insert into table_name (column1_name, column2_name, ...) values ( value1, value2, ...)

39

40 MySQL의 권한 테이블 tables_priv, columns_priv table의 사용 방법

41

42 MySQL의 권한 테이블 tables_priv, columns_priv table의 사용 방법 Grantor는 뭐야? 누가
db table과 거의 유사합니다. Grantor는 뭐야? 누가 Timestamp는? 언제 권한을 부여해 주었는가?

43 사용자 외부 접속 MySQL 사용자관리 사용자 외부 접속 허용 /etc/mysql/my.cnf에서
bind address= 을 주석처리 mysql에서 use mysql; select host, user, password from user; update user set host=‘%’ where user=‘user_id’ and host = ‘old host’ flush privileges;

44 사용자 삭제 MySQL 사용자 관리 사용자 삭제하기 사용자 삭제 시에는 테이블에서 삭제하면 됩니다.
delete from user where user = ‘user_id’;

45 4. MySQL 사용하기 기초 문법 Data Type Query Condition Wildcard Index Slowquery

46 기초 문법 기초 문법 명령어는 항상 ;나 \g 로 끝난다. \q, ^D, quit, exit 명령어로 접속 종료
command clear 는 \c 도움말은 \h

47 Data Type Data Type varchar(N): 가변 길이 문자열 char(N): 고정 길이 문자열
text: 최대 65536개의 문자 저장 blob: 최대 65536개의 문자 저장 int: 정수형 float: 실수형 date: 날짜(yy-mm-dd) time : 시간(hh:mm:ss) datetime timestamp varchar 는 저장 시 낭비가 적지만 검색 속도가 느림 text, blob 역시 가변 길이, 만약 가변길이를 가지는 칼럼이 하나라도 있으면 char 가 varchar 로 바뀔 수 있음 DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is ' :00:00' to' :59:59'. The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of' :00:01' UTC to ' :14:07' UTC.

48 지금 모든 query 를 다루기엔 너무 많다 @_@
SQL 주요 명령어(Query) create insert select update delete show explain describe alter 지금 모든 query 를 다루기엔 너무 많다 그때그떄 필요한걸 찾아쓰자!

49 Query create : DB, 테이블, column 생성 create database db_name;
use db_name; create table table_name (column_name type, ...);

50 Query insert : 테이블에 record 추가 use db_name;
insert into table_name (column1_name, column2_name, ...) values ( value1, value2, ...) 축약형 insert into table_name values (value1, value2, ...)

51 Query Show, explain, describe DB와 테이블에 대한 정보를 얻어 옴 show db;
show tables from db; show columns from table; =explain table; =describe table; =desc table;

52 Query select 테이블에서 record 검색 user db_name;
select column1_name, column2_name, ... from table_name (where condition); *는 모든 column이라는 의미로 쓰일 수 있다.

53 Query update update: 레코드 수정 use db_name;
update table_name set column1_name=value1, column2_name=value2, ... where condition; (where condition을 안 주면 모든 column의 모든 데이터가 바뀜)

54 Query delete :레코드 삭제 user db_name;
delete from table_name where conditions; where conditions를 안 주면 모든 레코드가 삭제됨!

55 Query drop : db, 테이블을 삭제 drop database db_name; use db_name;
drop table table_name

56 Condition Condition 앞서 where 뒤에 있던 condition where 필드명=조건값
where 필드명 like %찾을 문자 가 기본적인 형태

57 Wildcard . 는 문자 하나 * 는 앞에 나온 문자의 0개 이상의 반복 ^는 문자열의 처음 $는 문자열의 끝
[,]는 괄호 안의 문자열 일치를 확인 {N}는 앞에 나온 문자의 N번의 반복을 나타냅니다.

58 Index create table sparcs(id text, name text, age int);
에 대해 select를 실행하면, 레코드 수가 증가할 수록 시간이 점차 늘어난다. 모든 레코드에 대해 검사를 수행하기 때문! Index를 쓰면 됩니다 ㅇㅇ

59 Index create table sparcs(id text not null default, name text, age int, INDEX myindex1 (id)}; index를 설정할 필드는 null이나 default가 될 수 없다. index를 설정하면 query시간이 거의 일정!

60 Index Index를 사용하지 않는 query? 쿼리문 앞에 explain을 붙였을 때 type의 결과값이
ALL이면 인덱스 X range, index 등이면 인덱스 O

61 Slowquery 일정 시간을 초과하는 쿼리를 로그로 남김으로써 로그 분석을 통해 쿼리 성능을 개선
my.cnf에 다음을 추가한다. [mysqld] log-slow-queries = /temp/mysql-slow.log long_query_time=2 // 수행 시간이 2초가 넘는 쿼리를 로그로 남김

62 5. 서버 설정 및 관리 mysql 환경설정 mysqladmin mysqldump

63 mysql 환경설정 /etc/mysql/my.cnf : MySQL 모든 프로그램에 대한 옵션 설정
DATADIR/my.cnf: 서버에 관련된 옵션 DATADIR은 /etc/my.cnf 의 [mysqld]에 있음 $HOME/my.cnf : 개인 사용자의 옵션 Grandmarnier’s MySQL 세미나

64 mysql 환경설정 기본 설정 예시 예를 들어 인코딩 기본값을 UTF-8로 설정하려면 [mysqld]아래에 다음을 추가하면 됩니다.

65 mysql 환경설정 my.cnf를 열어 봅시다.
[mysqld] server-id=100    #mysql실행 인스턴스 의 고유번호. 서버별로 중복 안 되게 설정한다. user=mysql    #실행계정. rpm설치하면 보통 mysql 을 쓸 수 있음 port=3306    #접근포트. 기본이 3306 basedir=/usr    #기본폴더. 이아래 ./bin 에 실행파일들 있음 datadir=/var/lib/mysql    #여기에 데이터 파일들이 있음. 데이터파티션이 따로 있으면 바꾸면 됨  tmpdir=/tmp socket=/var/lib/mysql/mysql.sock character-set-server=utf8    #문자셋 collation-server=utf8_general_ci    #문자정렬형식 skip-name-resolve    #접속한 계정의 도메인네임 찾기 안함 skip-external-locking     lower_case_table_names=1    #모든 테이블명 소문자형식으로 back_log=100    #서버가 바쁠때 대기할 SQL문 숫자의 한도 max_connections=1000    #최대연결수 max_connect_errors=    #한 연결에서 오류가 나더라도 연결을 끊지 않는 최대횟수. 커넥션 풀을 쓸때는 많이 키워야 한다. thread_cache_size=50    #연결캐쉬 table_open_cache=400    #테이블 캐쉬 수. read가 발생하기 시작하면 늘려본다. wait_timeout=28800     max_allowed_packet=32M    #쿼리 데이터의 허용 최대크기 (BLOB포함) max_heap_table_size=32M    #메모리타입 테이블 최대크기 tmp_table_size=512K    #임시테이블 최대크기(세션변수) sort_buffer_size=256K    #정렬버퍼(세션변수) join_buffer_size=128K    #인덱스없는 조인할때 쓰는 버퍼(세션변수) read_buffer_size=128K    #랜덤읽기버퍼(세션변수) read_rnd_buffer_size=128K    #순차읽기버퍼(세션변수) query_cache_size=32M    #쿼리캐쉬크기. 0이면 쿼리캐쉬안씀 query_cache_limit=2M    #쿼리캐쉬에 넣을 결과값을 최대크기 group_concat_max_len=1024    #group_concat 에 할당하는 최대바이트 수 transaction-isolation=REPEATABLE-READ    #READ-UNCOMMITED : READ-COMMITED : REPEATABLE-READ : SERIALIZABLE 중에 하나 ## innodb option innodb_buffer_pool_size=9G    #물리 메모리의 50~80% 수준. innodb_additional_mem_pool_size=32M    #16~32M (테이블 1천개일떄 16M 기준) innodb_file_per_table=1     innodb_data_home_dir=/var/lib/mysql    #innodb용 데이터 폴더. datadir 와 일치시켜야 편함. innodb_data_file_path=ib_system:100M:autoextend innodb_autoextend_increment=100 innodb_log_group_home_dir=/var/lib/mysql    #innodb용 데이터 폴더. datadir 와 일치시켜야 편함. innodb_log_buffer_size=32M    #innodb log를 디스크에 쓰기전에 사용하는 버퍼. innodb_log_file_size=1024M    #innodb log 파일 하나당크기 innodb_log_files_in_group=2    #innodb log 파일 갯수. innodb_log_file_size * innodb_log_files_in_group 이 MySQL 5.5에서는 4G미만 이어야 한다. 보통 innodb_buffer_pool 이 10G 미만이면 2G 정도면 된다. innodb_support_xa=OFF innodb_thread_concurrency=0 innodb_lock_wait_timeout=60 innodb_flush_log_at_trx_commit=1    #1:log를 바로 Disk에 씀.  0:쓰지 않음. 대략 4~5초간 데이터 유실 위험. 단 입출력 속도가 매우 향상됨. innodb_force_recovery=0 innodb_flush_method=O_DIRECT    #O_DIRECT / O_DSYNC / fdatasync(묵시적인 기본값) :  시스템캐쉬사용안함/ 시스템캐쉬사용/ 시스템캐쉬사용. 메타데이터 변경안함. Raid controler가 없거나 SAN을 쓰는 경우 사용됨. innodb_doublewrite=1 innodb_sync_spin_loops=20 innodb_table_locks=1 innodb_thread_sleep_delay=1000 innodb_max_purge_lag=0 innodb_commit_concurrency=0 innodb_concurrency_tickets=500 innodb_old_blocks_time=500 ## MYISAM option key_buffer_size=32M bulk_insert_buffer_size=32M myisam_sort_buffer_size=1M myisam_max_sort_file_size=2G myisam_repair_thread=1 myisam_recover ft_min_word_len=3 ## logging option ## pid-file=/var/lib/mysql/mysql_db.pid ## general_log=1 ## general_log_file=/var/lib/mysql/general_query.log ## log_slow_admin_statements    #DDL문장도 slow log에 추가하기 log-warnings=1 log-error=/var/lib/mysql/mysql_err.log slow-query-log=1    #0/1:long_query_time보다 오래걸린 쿼리 로그남기지 않기/남기기 long_query_time=5     slow_query_log_file=/var/lib/mysql/slow_query.log    #slow log 위치 log-bin=/var/lib/mysql/binary_log    #바이너리로그 저장경로 binlog_cache_size=128K    #커넥션별 바이너리 로그 저장 캐쉬크기 max_binlog_size=512M    #바이너리로그 파일최대크기 expire_logs_days=14    #바이너리로그 저장기간 log-bin-trust-function-creators=1 sync_binlog=1    #바이너리로그 동기화여부 0/1/n : 동기화 안함/동기화/n개 트랜젝션을 모아 동기화 binlog_format=mixed #relay-log=/var/lib/mysql/relay_log #relay_log_purge=TRUE ## read_only ## log-slave-updates [mysql] no-auto-rehash show-warnings pager="less -n -i -F -X -E"

66 mysqladmin mysqladmin Mysql 관리자 mysqladmin [login] create [db_name]
mysqladmin [login] drop [db_name] mysqladmin [login] ping : mysqld 가 돌아가는지 확인 mysqladmin [login] status : mysql 상태 mysqladmin [login] extended-status mysqladmin [login] shutdown mysqladmin [login] processlist = mysqladmin [login] process : 실행중인 클라이언트 목록 mysqladmin [login] variables : 변수, 값 출력

67 mysqldump mysqldump MySQL 백업 유틸리티
mysqldump [login] –A –-add-drop-table > [bak] 전체 데이터베이스 백업 mysqldump [login] –B [options] [db1] [db2] … > [bak] 선택한 데이터베이스 백업 mysqldump [login] [db name] [tb1] [tb2] … > [bak] 선택한 데이터베이스의 테이블 을 선택하여 백업 보통 .sql 확장자를 붙여서 백업함

68 mysqldump mysqldump 데이터 복구 시 mysql [login] < [bak]
mysql [login] [db_name] < [bak] 선택한 데이터베이스 백업(하나씩 백업한 경우) db는 생성 되어있어야 함 한글이 깨지면 –-default-character-set 옵션을 이용하자. 혹은 mysql 옵션에서 ascii 코드 외의 문자를 ?로 표기하는 옵션이 켜져 있을수도 있으니 확인!

69 Citation reno 선배님의 mysql 세미나 grandmarnier 선배님의 mysql 세미나
yasik 회원의 mysql 세미나 zzongaly 선배님의 relational model 세미나

70 Reference