Category Archives: MySQL

MySQL(MariaDB) 사용자 추가

1. 사용자 추가
mysql> create user \'[아이디]\’@\'[접속지]\’ identified by \'[비밀번호]\’;

2. 사용자 권한 할당
mysql> grant all privileges on [DB명].* to \'[아이디]\’@\'[접속지]\’;

사용예 : 사용자 abc가 localhost에서 접근 가능하며 abc DB에 모든 권한을 가지도록 설정
mysql> create user \’abc\’@\’localhost\’ identified by \’abc123\’;
mysql> grant all privileges on abc.* to \’abc\’@\’localhost\’;

[Linux] MySQL Partitioning / Mysql 파티셔닝

출처 – http://trendofit.tistory.com/739
MySQL Partitioning

정보는 흘러 넘치고 분석해야 할 데이터도 계속 커지면서 많은 데이터를 효율적으로 운영하기 위한 노력들은 계속 진행 중이다. 이 같은 추세에 발 맞춰 최근에는 어플라이언스 시장도 국내에서 계속 커지고 있다. 대용량 데이터를 처리하기 위해 테이블 파티셔닝은 DBMS의 필수 기능으로 꼽히는데 MySQL이 테이블 파티셔닝을 지원하지 않는 것은 그 동안 MySQL을 대용량 DB에서 쓰기 어려웠던 이유이기도 했다. Merge 테이블을 이용해 일부 비슷한 효과를 낼 수는 있었지만, 정식으로 테이블 파티셔닝을 지원한 것은 5.1 버전부터다. 지금부터 MySQL Partitioning에 대한 이야기를 시작해 보자.

5.1버전의 테이블 파티셔닝 지원은 타 상용 DBMS에 비해 아직은 부족한 수준임을 감안해도, 이제껏 MySQL의 파티셔닝 기능에 목말라 있던 사용자들에겐 단비와 같은 소식이다. 일반적으로 테이블을 파티셔닝한다면 다음과 같은 기대를 가지고 있을 것이다.

– 특정 데이터 집합의 추가/삭제가 간편하다. – 데이터 검색 시 특정 파티션만 읽어서 속도를 향상시킬 수 있다. – 병렬 처리가 가능하다. – 한 테이블에 너무 많은 데이터가 입력되었을 시 발생하는 속도 저하를 방지할 수 있다.

그렇다면 위와 같은 기대를 어느 정도 충족해 주고 있는지 MySQL 파티셔닝 사용법과 특징에 대해 살펴보자. 먼저 자신이 사용하고 있는 MySQL에서 파티셔닝을 지원하는지 확인하는 방법은 <리스트 1>과 같다.

파티션 타입

MySQL에서 지원하는 파티셔닝 종류에 대해 살펴보자. 기본적으로 MySQL은 RANGE, LIST, HASH, KEY 파티셔닝을 지원한다. 많이 사용되는 파티셔닝은 모두 지원한다고 보면 되겠다.

RANGE

특정 범위 단위로 파티셔닝할 때 적용되는 가장 흔히 사용하는 파티셔닝 타입 중 하나이다. Create table 구문 뒤의 PARTITION BY RANGE() 절 안에는 파티셔닝할 컬럼을, VALUE LESS THAN() 절 안에는 해당 파티션의 MAX 값을 기술하면 된다. <리스트 2>를 예로 들어보자.
이 테이블을 store_id 컬럼에 의해 Range 파티셔닝하고 싶다면 <리스트 3>과 같이 정의한다.
데이터가 들어갈 파티션은 VALUES LESS THAN 구문에 의해 결정된다. <리스트 3>의 예제에서는 store_id가 1~5까지는 p0 파티션, 6~10까지는 p1 파티션으로 들어간다. 그렇다면 store_id에 21 값이 들어오면 어떻게 될까? 이때는 해당 값이 들어갈 파티션이 지정되어 있지 않기 때문에 에러가 발생한다. 따라서 어떤 값이 들어오더라도 에러가 나지 않도록 <리스트 4>와 같이 MAXVALUE를 지정할 수 있다.
21보다 크거나 같은 값이 들어오면 p4 파티션에 데이터가 들어갈 것이다. MAXVALUE는 이 타입이 가질 수 있는 가장 큰 값을 의미하고 제일 마지막 파티션에 기술해야 하며 오직 하나의 파티션에만 사용할 수 있다. 그에 반해 NULL 값은 다른 값보다 작은 값으로 인식되어 제일 작은 값으로 정의된 파티션으로 들어간다. 파티션 기준이 되는 컬럼은 VALUES LESS THAN 의미처럼 크기 비교가 가능해야 한다. 컬럼이 문자인 경우에도 문자 연산을 통해 숫자만을 걸러 낼 수 있다면 Range 파티셔닝이 가능하다.

Ex) PARTITION BY RANGE ( YEAR(separated) )

Range 파티셔닝은 다음과 같은 경우 유용하다.

– 대량의 과거 데이터를 지우기를 원할 때 : delete로 많은 데이터를 지운다면 시간이 오래 걸리지만 파티션별로 drop하면 순식간에 데이터 삭제가 가능하다

ALTER TABLE employees DROP PARTITION p0;

– 시간이나 날짜별로 데이터를 관리하고 싶을 때 – 파티션된 컬럼을 조건으로 쿼리를 자주 실행시킬 때

SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY store_id;

MySQL은 재빨리 해당 파티션만 읽어서 결과를 얻어낼 수 있다.

LIST

List 파티셔닝은 여러 가지로 Range 파티셔닝과 비슷한 점이 많다. Range 파티셔닝처럼 각각의 파티션은 명시적으로 정의되어 있어야 한다. Range 파티셔닝과 가장 다른 특징은 파티션을 결정하는 컬럼 값이 범위가 아니라 특정한 값들의 집합이라는 것이다.
Partition by list() 절에 파티셔닝할 기준 컬럼을 명시해 주고 values in() 절에 파티션을 구성할 값들을 지정하면 된다. VALUES IN() 절에 나열되는 값들은 연속될 필요가 없고 중간 값이 없어도 되지만 해당 파티션에 값이 하나도 없거나 여러 파티션에 중복된 값을 넣으면 에러가 난다. Range 파티셔닝에서의 MAXVALUE에 상응하는 것은 없고, value list에 null 값이 들어갈 수 있다. 삭제하고자 하는 데이터들이 해당 파티션에 있는 모든 데이터들이라면 역시 drop 구문을 이용해서 데이터를 삭제할 수 있다.

DELETE FROM employees WHERE store_id IN (4,12,13,14,18);

이와 같이 지우고자 하는 데이터가 pWest 파티션에 있는 모든 값이라면 해당 파티션을 drop함으로써 해당 값들을 모두 삭제할 수 있다.

ALTER TABLE employees DROP PARTITION pWest;

List 파티셔닝 역시 파티셔닝할 컬럼은 정수만 지원한다. 따라서 integer나 null이 아닌 값으로 파티셔닝하고 싶다면 파티션 기준 컬럼을 이러한 값으로 변환시켜야 한다. Job_code 값이 다음과 같이 들어온다고 가정해 보자

Management – D, M, O, P Sales – B, L, S Technical – A, E, G, I, T Clerical – K, N, Y Support – C, F, J, R, V Unassigned “Empty”

이렇게 문자로 들어온다면 이것은 integer value-list로 표현할 수 없으므로, 이럴 때 ascii() 함수를 사용해야 한다. list에 해당하는 값이 대소문자를 구별하지 않기를 원한다면 ucase 함수로 먼저 감싸고 ascii()를 사용하면 된다. 주의해야 할 점은 다음과 같은 insert 문은 실패한다는 것이다.

INSERT INTO employees VALUES (224, ‘Linus’, ‘Torvalds’, ‘2002-05-01’, ‘2004-10-12’, ‘Q’, 21);

Q의 ascii 값은 81인데 list 파티션 정의에 81에 해당하는 파티션이 존재하지 않기 때문이다. List 파티셔닝은 VALUES LESS THAN(MAXVALUE)과 비슷한 catch-all에 대한 정의가 없으므로 모든 list 값은 반드시 해당하는 파티션이 미리 정의되어 있어야 한다. 또한 MySQL 버전에 따라서는 PARTITION BY LIST() 절에 함수를 사용하지 못할 수도 있다. 최근 버전인 MySQL(5.1.36)에서는 PARTITION BY LIST() 절에 함수를 이용하지 못한다. 그러므로 integer 형이 아닌 컬럼을 기준으로 List 파티셔닝을 하고 싶다면 반드시 먼저 자신이 사용하는 MySQL 버전에서 테스트 후 사용하기를 바란다.

HASH

파티셔닝은 필요한데 마땅히 파티셔닝할 기간이나 특정 값들의 집합을 정의하기 모호할 때 보통 많이 사용한다. 사용 목적도 그렇듯이 Range나 List로 파티셔닝된 테이블을 생성할 때와는 달리 각각의 파티션들을 직접 정의해 줄 필요가 없다. <리스트 7>과 같이 PARTITIONS 절 뒤에 사용할 파티션 개수를 적어 주면 된다.
PARTITION BY HASH() 절에 역시 숫자 형식의 컬럼 값이 들어오면 된다. 숫자 컬럼이 아닐 경우 함수를 사용해 숫자로 변형이 가능하다면 Hash 파티셔닝이 가능하다. 그렇다면 각각의 데이터는 어느 파티션으로 들어가는 것일까? 내부적으로 파티션은 0, 1, 2, 3과 같은 식으로 번호가 붙여지는데 표현식을 expr, 파티션 수를 num이라고 하면, 입력되는 row가 들어갈 파티션 번호는 다음의 식으로 결정된다.

IF(ISNULL(expr), 0, ABS(expr)) MOD num

따라서 컬럼이 auto_increment 컬럼과 같이 순차적인 값을 가지는 경우 파티션별로 데이터가 고르게 분산된다.

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE) PARTITION BY HASH( YEAR(col3) ) PARTITIONS 4;

이러한 테이블에 col3 값이 ‘2005-09-15’라면 데이터가 어느 파티션에 들어갈지는 다음과 같은 공식으로 계산된다.

MOD(YEAR(‘2005-09-01’),4) = MOD(2005,4) = 1

MySQL은 또한 Linear Hash 파티셔닝을 지원한다. 문법은 partition by hash가 partition by linear hash로 바뀌는 것 외에는 동일하다.
내부적으로 데이터를 파티셔닝하는 Hash 알고리즘으로 linear powers-of-two를 사용하며 참고로 LINEAR HASH() 절에 사용된 표현식을 expr, 파티션 수를 num, 들어갈 파티션 번호를 N이라고 했을 때 N을 구하는 방식은 다음과 같다.

1. V = POWER(2, CEILING(LOG(2, num))) 2. N = expr & (V – 1) 3. While ( N >= num: ) V = CEIL(V / 2) N = N & (V – 1)

KEY

Key 파티셔닝은 Hash 파티셔닝과 비슷하다. Hash 파티셔닝은 사용자가 정의하는 함수가 이용되는 반면 Key 파티셔닝은 MySQL에 의해 제공하는 Hash 함수를 사용한다. MySQL Cluster는 Hash 함수로 MD5()를 사용한다.
특이하게 KEY() 절에 컬럼을 명시하지 않아도 에러가 나지 않는데 명시하지 않으면 PK가 파티셔닝 키로 사용된다. 만약 해당 테이블에 PK가 없다면 UK가 파티셔닝에 사용된다.
하지만 UK가 파티셔닝 키로 사용되려면 반드시 UK가 not null로 정의되어 있어야 한다. <리스트 10>의 경우는 id 컬럼이 UK이긴 하지만 not null로 정의되어 있지 않아 실패한 것이다. 다른 파티션과는 다르게 유일하게 Key 파티셔닝에서는 파티셔닝 키값으로 숫자나 null 이외의 값을 사용할 수 있다. <리스트 11>과 같이 파티셔닝 키가 되는 컬럼 자료형이 char이라도 굳이 integer로 변환할 필요가 없다.

SUBPARTITIONING

파티션 안에 또 다른 작은 파티션을 만들 수 있다. Range나 List 파티션 안에 서브 파티션으로 Hash나 Key 파티션을 만드는 것도 가능하다. 여러 가지 파티셔닝을 함께 사용할 수 있기 때문에 Composite 파티셔닝이라고도 알려져 있다.

Partition Managemt

MySQL 상에서 파티셔닝한다면 주로 성능보다는 관리적인 면에서 더 유용하다. 월별로 관리하는 데이터를 Range 파티셔닝했다면 매달 말에 다음 달에 대한 파티션을 미리 만들어야 할 필요가 생긴다.
<리스트 13>에 정의된 파티션 테이블에 2009년 10월 데이터를 쌓을 파티션을 추가하고 싶다면 다음과 같이 추가할 수 있다.
mysql> ALTER TABLE in_ptest ADD PARTITION -> ( PARTITION p200910 VALUES LESS THAN (to_days(‘2009-11-01’)) ); Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0

또한 특정 파티션을 간편하게 제거할 수도 있다.

mysql> ALTER TABLE in_ptest DROP PARTITION p200902; Query OK, 0 rows affected (0.57 sec) Records: 0 Duplicates: 0 Warnings: 0

짧은 시간 내에 Add, Drop이 되므로 온라인 중에서도 사용할 수 있다. 또한 다음과 같은 작업을 필요한 파티션만 진행할 수 있다. 해당 파티션만 작업하기 때문에 범위가 작아져 전체 작업하는 것보다 속도 향상이 있다.

ALTER TABLEREBUILD PARTITION; ALTER TABLEOPTIMIZE PARTITION; ALTER TABLEANALYZE PARTITION; ALTER TABLEREPAIR PARTITION; ALTER TABLECHECK PARTITION;

하지만 타 DBMS처럼 병렬 처리 효과를 얻기는 아직까지는 힘들다. <리스트 14>와 같이 서로 다른 두 파티션을 순차적으로 check할 때보다 제 각기 다른 세션에서 병렬로 처리할 때 속도가 빠를 것이라고 기대했지만 다음에서 볼 수 있듯이 실제 결과는 그렇지 않았다.
<리스트 14>에서 p200903, p200904 파티션을 순서대로 check할 때는 10초, 20초 걸리던 것이 <리스트 15>와 <리스트 16>처럼 각기 다른 세션에서 한꺼번에 실행시켰을 때는 각각55초, 54초가 걸렸다. 또한 보통 테이블 파티셔닝을 하면 특정 파티션에 어떤 작업을 수행할 때 다른 파티션에는 영향을 미치지 않을 것을 기대하지만 아직 MySQL에서는 이러한 점을 지원하지 않으니 유의해야 한다.
기존의 파티션을 병합하거나 분리하는 작업도 REO RGANIZE PARTIION 구문을 이용하면 가능하다. <리스트17>은 p200901 파티션과 p200902 파티션을 병합하는 것이다. 파티션 Add와 Drop은 순식간에 이뤄져 애플리케이션 운영 중에도 가능하지만 Reorganize를 하면 작업 대상이 아닌 파티션에 어떤 영향을 미칠까?
<리스트 17>의 세션에서 실행되는 쿼리는 p200901,p200902 파티션만 관여한다. 따라서 해당 파티션만 lock이 걸리고 나머지 파티션은 사용할 수 있을 것으로 기대하겠지만 실제 테스트 결과 <리스트 19>의 결과를 보면 알 수 있듯이 해당 파티션뿐만 아니라 전체 파티션에 lock이 걸리는 걸 알 수 있었다. <리스트 19> 쿼리의 데이터는 p200909 파티션으로 들어감에도 불구하고 <리스트 17>의 쿼리가 끝날 때까지 대기한 후에 실행이 되었다. 따라서 파티셔닝 관리 작업 중 Add와 Drop 같이 순식간에 이뤄지는 종류를 제외하고는 항상 애플리케이션에 영향을 줄 수 있다는 점을 명시하고 작업해야 한다.
현재 테이블 파티셔닝 현황에 대해 조회해 보고 싶다면 Information 스키마의 Partitions 테이블을 참조하면 된다.

파티션 성능

MySQL은 Parallel Processing을 아직 지원하지 않는다. 그렇더라도 각각 파티션에 해당하는 작업을 여러 개의 프로세스로 돌리면 비슷한 효과를 얻을 수 있지 않을까 기대했었지만 앞에서 살펴본 바와 같이 그러한 결과는 얻을 수 없었다. delete 대신에 drop 명령어를 통한 성능 향상, 그리고 Partition Pruning을 통한 성능 향상 정도를 기대할 수 있겠다.
Explain Partitions 구문을 사용하면 실행계획에 파티션 정보들을 보여준다. <리스트 20>의 세션에서는 파티셔닝 컬럼인 term이 where 조건에서 변형되었기 때문에 Partition Prunining이 일어나지 않았다. 따라서 <리스트 21>과 같이 파티션 키 컬럼은 그대로 두고 비교하는 상수 값만 변경해야 원하는 속도를 얻을 수 있다.

파티션 제약사항

글 중간 중간에 MySQL 파티셔닝을 사용할 때 제약사항들을 언급했었는데 현재 MySQL 5.1.36 버전에서의 제약사항들을 간단히 정리해 보면 다음과 같다. 아직까지는 생각보다 많다.

– 모든 파티션은 동일한 스토리지 엔진 사용 모든 파티션은 동일한 스토리지 엔진을 사용해야 한다. 파티션별로 스토리지 엔진을 명시해도 에러가 나지 않는 것을 봐서는 향후에 파티션별로 다른 스토리지 엔진을 사용할 수 있게 되지 않을까라는 기대도 해본다. 명심할 것은 파티션별로 스토리지 엔진을 명시해도 에러만 나지 않을 뿐 실제로 다른 스토리지 엔진을 사용할 수 있는 게 아니란 사실이다.

– Merge, CSV, Blackhole은 파티셔닝이 불가능하다. – 파티셔닝을 테이블이나 인덱스만 할 수는 없다. 테이블과 인덱스를 항상 같이 파티셔닝해야 한다. – 파티션된 테이블은 foreign key를 지원하지 않는다. – 파티션된 테이블은 fulltext index를 지원하지 않는다. – 파티션된 테이블은 geometry 컬럼을 지원하지 않는다. – Key 파티셔닝을 제외하고는 파티셔닝에 키가 되는 컬럼은 반드시 integer 값이거나 integer로 변환 가능해야 한다. null은 포함될 수 있다. – 많은 테이블에 파티션을 사용하다 보면 스토리지 엔진으로부터 ‘Got error 24’와 같은 에러 메시지를 보게 될 것이다. 이때는 open_files_ limit 값을 늘려줘야 한다. – 한 테이블당 서브파티션(subpartition)을 포함해 파티션의 최대 개수는 1,024개이다.

효과적인 사용은 사용자의 몫

데이터가 점점 더 대용량화되는 가운데 MySQL도 늦게나마 이런 특징들을 지원하기 시작했다. 아직까지 부족한 점도 많지만 MySQL은 끊임없이 발전하고 있고 이를 잘 활용하기 위한 기법들이 많이 소개되고 있으므로 단순히 이를 Oracle과 같은 상용 DBMS와 비교하는 것은 별 의미가 없다. MySQL은 나름의 영역에서 다양한 기능과 좋은 성능을 내 주고 있으며 어떤 서비스에 어떻게 사용할지는 사용자들의 몫이다. MySQL과 같은 오픈소스 DBMS가 한층 더 좋은 모습으로 나타날수록 사용자 입장에서는 반갑고 고맙기 그지없을 뿐이다.

참고자료 1. http://dev.mysql.com/doc/refman/5.1/en/partitioning.html 2. http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning_with_dates.html 3. http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning.html 4. http://dev.mysql.com/tech-resources/articles/testing-partitions-large-db.html

테이블 필드 튜닝시 참고 쿼리

select * from 테이블명 procedure analyse();

Mysql Log-bin으로 특정 데이터베이스의 특정 테이블 파일 복구 하기

오만상 삽질 했다 -_-)!!! 써글

단계별로 하기~

※ 만약 해당 테이블의 데이터들이 HTML과 같은 데이터라면 이방법은 유용하지 못하다..

 

1) /usr/local/mysql/bin/mysqlbinlog -d [데이터베이스명] [log-bin파일명] > [저장할 파일명]

ex) /usr/local/mysql/bin/mysqlbinlog -d database_01 mysql-bin.00001 > restore_00001.sql

 

2) grep -i [테이블명] [저장된 파일명] > [다시 저장할 파일명]

ex) grep -i table_01 restore_00001.sql > re_restore_00001.sql

 

3) cat [다시 저장된 파일명] | sed ’s^[/*!*/]^^g’ >> [최종 복구파일명]

ex) cat re_restore_00001.sql | sed ’s^[/*!*/]^^g’ >> new_restore_00001.sql

 

4) /usr/local/mysql/bin/mysql -u root -p[패스워드] –database=[데이터베이스명] -f < [최종 복구파일명]

ex) /usr/local/mysql/bin/mysql -u root -p’12345’ –database=database_01 -f < new_restore_00001.sql

 

요렇게 하면 에러를 무시하면서 계속~~ 넣지~~

MySQL 실시간 복제[Replication] 기능

MySQL 실시간 복제 [Replication]

 

1. MASTER MYSQL

1) C:WINDOWSmy.ini)설정파일

 

#This File was made using the WinMySQLAdmin 1.4 Tool

#2005-08-09 오후 9:23:02

 

#Uncomment or Add only the keys that you know how works.

#Read the MySQL Manual for instructions

 

[mysqld]

basedir=C:/mysql

#bind-address=111.111.1.111

datadir=C:/mysql/data

server-id =1

log-bin = C:/mysql/data/replication.log <–바이너리 로그파일

binlog-do-db = gpsdb

binlog-do-db = test

#language=C:/mysql/share/your language directory

language=C:/mysql/share/korean

#slow query log#=

#tmpdir#=

#port=3306

#set-variable=key_buffer=16M

[WinMySQLadmin]

Server=C:/mysql/bin/mysqld-nt.exe

user=root

password=*******

 

2) 수정사항 설명

– log-bin : 이것은 바이너리 로그 file이 생성될 경로를 기록해 준다.

– binlog-do-db : 복제할 db이름을 지정해 준다. (위와 같이 원하는 db를 복수 선택 할 수 있다.)

– server-id : 서버 id는 (1 ~ 2^23-1)의 수중에 하나를 선택 할 수 있다.

 

2. SLAVE MYSQL

1) C:WINDOWSmy.ini)설정파일

 

#This File was made using the WinMySQLAdmin 1.4 Tool

#2005-10-26 오전 11:31:02

 

#Uncomment or Add only the keys that you know how works.

#Read the MySQL Manual for instructions

 

[mysqld]

basedir=C:/mysql

#bind-address=111.111.1.112

datadir=C:/mysql/data

language=C:/mysql/share/korean

server-id = 2

master-host = 111.111.1.111

master-port = 3306

master-user = root

master-password = ********

master-connect-retry =60

replicate-do-db = gpsdb

replicate-ignore-table=gpsdb.ttt

log-bin-index = C:/mysql/data/log-bin.index

log-bin = C:/mysql/data/bin.log

log-error = C:/mysql/data/error.log

relay-log = C:/mysql/data/relay.log

relay-log-info-file = C:/mysql/data/relay-log.info

relay-log-index = C:/mysql/data/relay-log.index

 

#language=C:/mysql/share/your language directory

#slow query log#=

#tmpdir#=

#port=3306

#set-variable=key_buffer=16M

[WinMySQLadmin]

Server=C:/mysql/bin/mysqld-nt.exe

user=root

password=*******

 

2) 수정사항 설명

– master-host : master호스트를 기록한다.

– master-user : master유저

– master-password : master유저 비밀번호

– master-port : master디비 포트

– master-connect-retry : master에 재 접속할 횟수

– replicate-do-db : replication할 디비명

– replicate-ignore-table : 특정 테이블은 replication을 하지 않게 지정하는데 사용된다.

– server-id : slave 아이디로 master이외의 unique한 다른 번호를 지정한다.

☞ 더 많은 옵션이 있지만 여기에서는 replication을 하기 위한 옵션만을 다루었다.

 

3. replication의 시작

1) mater db를 재시작 한다.

2) slave db를 재시작한다.

3) mater db에 업데이트를 실시한다.

4) slave db에 갱신이 반영되었는지 확인한다.

 

4. daisy-chain 식의 구성

MySQL은 고급 DBMS가 아닌 관계로 원하시는 기능들에 대해서 간단하게 세팅으로 구현되지는 않습니다. (MS-SQL 같은 경우는 Clustering을 구현하기 위해서 돈이 어마어마하게 듭니다)

 

Clustering의 핵심은 heartbeat 후의 상태 변화일 건데 그 부분을 스스로 처리해 주신다면 Active/Standby는 쉽게 구현될 수 있을 것이라고 생각합니다. 그걸 위의 daisy-chain 구성과 함께 한다면 Active/Active도 가능하겠지요.

 

일단 하나의 서버에 heart-beat를 체크하는 스크립트를 작성하고, 그 스크립트가 에러를 감지했을때 일련의 과정을 수행하게 하면 될 것 같습니다. ( 하지만, 그것이 에러가 났을 때 100% 정확하게 동작한다고 볼 수는 없을 것 같습니다. DB단 / OS단이 완전히 분리되어 있는 상황이니까요)

 

메뉴얼의 –log-slave-updates 설명부분을 올립니당.

 

–log-slave-updates

보통, 슬레이브에 마스터 서버로부터 받아진 업데이트들은 그것의 이진 로그에 기록되지 않습니다. 이 옵션은 슬레이브 자신의 이진 로그에 그것의 SQL 쓰레드에 의해 수행하였던 업데이트를 기록하는 것을 말합니다. 어떠한 영향을 가지고 있는 이 옵션에 대하여, 그 슬레이브도 또한 이진 로깅을 가능하게 하는 –log-bin 옵션과 함께 시작되어야 합니다. –log-slave-updates는 당신이 replication 서버들을 사슬로 묶기를 원할 때 사용됩니다. 예를 들면, 당신은 다음처럼 설치를 원할 수 있습니다 :

A->B->C

저것은 A 서버가 슬레이브 B를 위해 마스터로 쓰이고, B 서버가 슬레이브 C를 위해 마스터로 쓰입니다. 이 작업을 위해 B는 마스터와 슬레이브 모두가 되야 합니다. 당신은 A와 B 둘 다 이진 로깅이 가능하게 하는 –log-bin 옵션과 함께, 그리고 B를 –log-slave-updates옵션과 함께 시작해야합니다.

 

 

출처 : http://database.sarang.net/?inc=read&aid=12749&criteria=mysql&subcrit=&id=&limit=20&keyword=replication&page=5

원본 출처 : http://www.apmtim.com

 

이글은 장성윤님 홈페이지에서 소리 소문없이 훔쳐 온 것입니다. 많은 분들께 도움이 될 것 같아서 올려 둡니다. 물론 비슷한 글이 QnA 게시판에 보시면 있습니다만 따로 정리된 것이 좋겠지요. 🙂

 

MySQL 실시간 복제[Replication기능] V.0.1

 

글쓴날 : 2001년 9월 12일

글쓴이 : 장성윤

( http://www.apmtip.com http://www.techcenter.pe.kr )

최종수정 : 2001년 9월 17일

제목을 수정하였습니다.

Replication을 하기 위해서 항상 최신의 버젼을 유지할 것을 권고한다.

참고자료:

http://mysql.com/documentation/mysql/full/manual_toc.html#Replication

 

먼저 나의 테스트 환경은 이렇다

 

======================

master — 와우 리눅스 7.1 paran

메모리 : 196

MySQL버젼 : 3.23.41

 

slave — 원도2000

메모리 : 256

MySQL버젼 : 3.23.41

======================

master는 실제로 디비에 내용이 기록되는 호스트이고 , slave는 master 바이너리 로그를 사용해서

해당 디비를 갱신하게 된다.

테스트는 리눅스를 master로 하였고, 원도 mysql을 slave로 하였다.

 

먼저 호스트에 설치된 mysql의 디렉으로 가서 ./share/mysql방을 찾는다

======================================================================

-rw-r–r– 1 root root 2538 Sep 11 14:47 my-huge.cnf

-rw-r–r– 1 root root 2516 Sep 11 14:47 my-large.cnf

-rw-r–r– 1 root root 2500 Sep 11 14:47 my-medium.cnf

-rw-r–r– 1 root root 2215 Sep 11 14:47 my-small.cnf

======================================================================

위와 같은 파일이 있을 것이다.

해당 파일을 열어보면 맨 상단에 메모리사양이 나온다.하지만 나의 친절함으로 알아보자—-(:

 

====================================================================

파일명 설명

my-huge.cnf 메모리가 1G-2G사이일때 사용한다.

my-large.cnf 메모리가 512M일때 사용한다.

my-medium.cnf 메모리가 64~256M사이일때 사용한다.(필자는 이것을 사용)

my-small.cnf 메모리가 64M 보다 작을때 사용한다.

====================================================================

위에서 설명은 필자의 생각인 것이다.자신의 메모리가 해당이 않되면 알아서 판단하길 바란다.

 

일단 my-medium.cnf를 사용하기로 판단을 하고 /etc/my.cnf파일로 복사를 한다.

현재상태는 mysql데몬이 떠있는 상태이며, master설정부분이다.

 

vi /etc/my.cnf해서 열어서 [mysqld]부분을 보자

 

==================================vi /etc/my.cnf==========================================

# Example mysql config file for medium systems.

#

# This is for a system with little memory (32M – 64M) where MySQL plays

# a important part and systems up to 128M very MySQL is used together with

# other programs (like a web server)

# The following options will be passed to all MySQL clients

 

중략………………….

 

# The MySQL server

[mysqld]

port = 3306

socket = /tmp/mysql.sock

skip-locking

set-variable = key_buffer=16M

set-variable = max_allowed_packet=1M

set-variable = table_cache=64

set-variable = sort_buffer=512K

set-variable = net_buffer_length=8K

set-variable = myisam_sort_buffer_size=8M

>>>log-bin = /usr/local/mysql/logs/replication.log <–바이너리 로그파일

>>>binlog-do-db = test <—(디비명)

>>>binlog-do-db = edu <—(디비명)

>>>server-id = 1 <—Replication통신할 서버 아이디

 

이하 생략………………

==================================================================================

자세히 보면 자신의 my.cnf파일과 다른 부분이 >>>표시로 되어 있을 것이다.

그렇다. >>>부분의 설정으로 master부분은 끝난다.각각의 설정에 대해 알아보자

log-bin 이것은 바이너리 로그가 생성될 경로를 기록해 준다.

binlog-do-db 실시간 백업할 디비

server-id 서버 unique 한 번호(이것은 1 ~ 2^23-1까지 된다고 나와있다)

 

그리고 위에서 보듯이 binlog-do-db할 디비는 원하는만큼 설정이 가능하다.

 

그러면 slave인 원도에서 설정을 해 보자.(물론 다른 리눅스 박스를 slave로 설정하는 부분도 거의 동일하다

단지 원도와 리눅스라는 차이점이 있을 뿐이다)

mysql이 정상적으로 설치가 되었다면, WinMySQLadmin을 활성화 한다.

다음과 같이 한다.

시작 > 실행 > 찾아보기 > C:ysqlininmysqladmin.exe 을 선택하고 실행하면 WinMySQLadmin이 활성화 된다.

 

그러면 오른쪽 아래 부분에 신호등 표시로 나타난다. 이것을 클릭해서 show me하게되면 WinMySQLadmin이 나타나는데

여러 탭이 있을 것인데 여기서 my.ini Setup 탭을 선택한다.

참고로 리눅스에선 my.cnf이지만 윈도 에서는 my.ini파일이다. 이것은 mysql을 설치하면 WINNT방에 생성된다.

그러면 아까 master설정하고 동인한 부분이 보일것이다.

 

===============================win my.ini설정파일================================

#This File was made using the WinMySQLAdmin 1.3 Tool

#2001-09-11 오후 12:37:06

 

#Uncomment or Add only the keys that you know how works.

#Read the MySQL Manual for instructions

 

[mysqld]

>>>master-host = 192.168.0.120

>>>master-user = mysql최상위 관리자나 그에 동등한 권한을 가진 유저

>>>master-password = 비밀번호

>>>master-port = 3306

>>>master-connect-retry = 60

>>>replicate-do-db= edu

>>>replicate-ignore-table=edu.zetyx_board_tunning

>>>replicate-do-db= test

>>>server-id = 2

basedir=C:/mysql

#bind-address=192.168.0.110

datadir=C:/mysql/data

#language=C:/mysql/share/your language directory

#slow query log#=

#tmpdir#=

#port=3306

#set-variable=key_buffer=16M

이하생략….

===============================win my.ini설정파일================================

마찬가지로 >>> 부분이 추가된 부분이다.

설명은 아래와 같다

 

master-host master호스트를 기록한다.

master-user master유저

master-password master유저 비밀번호

master-port master디비 포트

master-connect-retry master에 재 접속할 횟수

replicate-do-db replication할 디비명

replicate-ignore-table 이것은 하나의 디비에 여러 테이블이 있을 경우 특정 테이블은 replication을 하지 않게 지정하는데 사용된다.

server-id slave 아이디로 master이외의 unique한 다른 번호를 지정한다.

 

더 많은 옵션이있다. 하지만 여기서는 단지 Replication하는데 중점을 두도록 한다.

여기까지가 Replication을 하기위한 설정의 끝이다.

 

그러면 이제부터 실시간 백업이 되게 해보자

1.master디비를 재시동한다.

2.slave 디비를 재시동한다.(윈도에서는 관리도구에서 서비스를 선택하면 mysql데몬을 다시 올려주면된다.)

3.master디비에 입력한다.

4.slave디비에 갱신이 되는지 확인한다.

 

slave에 갱신이 되었다면 성공한 것이다.

물론 위의방법은 새로운 디비에서 실시간 백업이 되게 한 것이다

기존 디비도 같은 방법으로 가능하다.

 

MySQL Engine 정리

아래 글들을 정리했음.
http://dev.mysql.com/tech-resources/articles/storage-engine/part_1.html
http://dev.mysql.com/tech-resources/articles/storage-engine/part_2.html
http://dev.mysql.com/tech-resources/articles/storage-engine/part_3.html
http://www.mysql.com/news-and-events/newsletter/2002-12/a0000000091.html

[MySQL Storage Engine]

MyISAM
DB는 디렉토리별로 생성/관리된다.
테이블은 디렉토리내 파일로 생성/관리되고, 하나의 테이블당 3개의 데이터파일로 구성된다.

dbakorea.frm : 테이블구조정보(스키마정보)
dbakorea.myd : 순수 데이터
dbakorea.myi : 인덱스 정보

테이블을 구성하는 row는 다음과 같이 3가지 형식으로 분류할 수 있다.

고정포맷(fixed row format)
컬럼타입으로 varchar, text, blob을 사용하지 않을때.
가능하다면 가급적 고정포맷을 사용하는 것이 좋다.
동적포맷보다 메모리사용이 적고, 인덱스파일크기도 작아진다. 당연히 속도또한 향상된다.
파일구조의 고정길이레코드가 고정포맷, 가변길이레코드가 동적포맷이라고 생각해주면 이해하기 쉬울 것이다.

동적포맷(dynamic row format)
컬럼타입으로 varchar, text, blob을 사용할때. 주의) varchar(3)보다 작다면 고정포맷이 사용됨
고정포맷에 비해 디스크사용에 있어 효율성을 가지나, 속도는 상대적으로 느리다.
테이블에 빈번한 레코드의 수정/삭제가 이루어지면 단편화가 유발하므로 주기적인 optimize table이 요구된다.
text, blob은 별도로 저장되므로, optimize table을 수행할 필요성이 없다.

압축표맷(compressed row format)
읽기전용이다. myisampack명령어로 만들 수 있다.
디스크공간을 적게 차지하므로 CD에 백업할때 사용하면 되겠다.

MyISAM은 동시성제어를 위해 테이블단위 락킹(table-level locking)을 사용한다.
참고로, 대부분의 상용 DBMS들은 행단위 락킹(row-level locking)을 사용한다.
행단위 락킹이 더 세밀하고 정밀한 제어가 가능한 반면, 테이블단위 락킹은 단순하다.

다음은 사용되는 3가지 락

READ LOCAL lock
query문(select)에서만 사용됨.
갱신작업들을 블럭. 다른 query문들은 블럭안됨.
insert문에서 .myd파일의 끝에 데이터를 추가하는 경우에는 블럭되지 않음.

READ, or shared locks
모든 갱신작업들(insert는 모두 적용됨)이 블럭됨. myisamcheck는 이 락을 사용.

WRITE, or exclusive locks
insert(몇몇 종류만), update, delete시 사용됨. 다른 모든 읽기작업/쓰기작업이 블럭됨.

인덱스 : key buffer에 캐싱되어 모든 놈들(MySQL 스레드들)이 공유
데이터 : OS의 캐싱에 의존.

주의) 캐싱에 대해
InnoDB가 인덱스/데이터 캐싱 모두를 관리하는 것에 비해(MySQL서버가 관리한다는 의미)
MyISAM은, 인덱스만 MySQL서버가 관리하고, 데이터는 관리하지 않는다.(데이터는 OS캐싱에 의존한다는 의미)
InnoDB는 innodb_buffer_pool_size, MyISAM은 key_buffer_size변수를 사용한다.
변수값보기: show variables
변수값설정: my.ini(윈도, Unix계열은 /etc/my.cnf)에 set-variable=key_buffer=16M 이런 식으로 설정

자주 사용되는 테이블들의 .myi파일크기를 합하면 대략적은 인덱스 캐싱크기를 구할 수 있다.

3가지 인덱스 사용가능: btree, rtree(지리학 데이터), fulltext
트랜잭션 지원안됨.
mysqldump(SQL문으로 생성), mysqlhotcopy(이진형식으로 생성)로 백업가능
MyISAM MERGE
테이블들을 union으로 묶은 일종의 뷰.
실제 데이터는 기반테이블들에 있음.
보통 히스토리데이터나 로그를 가지는 테이블들에서 사용됨.
오라클의 파티셔닝과 그 개념이 유사.
InnoDB
ACID 트랜잭션, multi-versioning, row-level locking, foreign key제약조건 지원됨.
크래쉬후 자동복구 지원
데이터와 인덱스가 모두 저장되는 테이블스페이스 개념이 사용됨.
오라클의 테이블스페이스와 같이 여러개의 파일들로 구성될 수 있다.
select는 락킹이 필요치 않으며, 갱신작업들은 행단위 락킹을 사용.
높은 동시성을 제공하지만, MyISAM에 비해 3배정도의 디스크 사용량을 요구함.
최적의 성능을 위해 많은 메모리가 InnoDB buffer pool에 할당되어야 함.
클러스터된 프라이머리키로 btree인덱스 사용
commit된 트랜잭션은 redo log에 기록되고, 이는 적정한 시간에 테이블스페이스에 기록된다.
mysqldump로 백업가능.
아래 질의문으로 InnoDB사용가능 여부를 알 수 있다.

show variables like ’have_innodb’

참고>
show variables는 서버변수값을 파악시 사용한다. 오라클에서의 show parameter와 유사하다.
특정변수값만을 알아낼때는 like를 사용해 해당 변수만을 지정하면 된다.
SQL문의 like문법이 적용되므로 %, _를 사용하여 특정영역의 변수들만을 지정할 수도 있다.
참고로 오라클의 경우 like를 지정하지 않고 그냥 패러미터를 지정할 수 있다.

mysql> show variables like ’have%’;
+——————+——-+
| Variable_name | Value |
+——————+——-+
| have_bdb | NO |
| have_crypt | NO |
| have_compress | YES |
| have_innodb | YES |
| have_isam | NO |
| have_raid | NO |
| have_symlink | YES |
| have_openssl | NO |
| have_query_cache | YES |
+——————+——-+
9 rows in set (0.00 sec)
SQL> show parameter timed

NAME TYPE VALUE
———————————— ———– ——————————
timed_os_statistics integer 0
timed_statistics boolean TRUE
SQL>
MEMORY(구명칭: HEAP)
모든 데이터들은 메모리에 저장됨.
다른 테이블타입들에 비해 속도가 월등히 빠르지만, 서버가 셧다운시 데이터는 모두 소실된다.
동등조건( ex) where a=10) 검색에 HASH기반 검색을 제공한다.
이는 범위검색시 인덱스가 사용되지 못한다는 것을 의미한다. varchar, blob, text컬럼 사용못함.
4.1버전부터는 트리기반 인덱스도 사용가능함.

Ex) create table lookup(id int, index using btree(id)) engine=memory;
MySQL Cluster
NDB로 칭한다. 나는 사용할 일이 없으므로 설명 생략
어떤 놈으로 사용해야 하나?

MyISAM
정적인 테이블, 로그 테이블
쓰기작업이 별로 없는 select 위주의 테이블.
current insert기능이 read시에 insert가 가능하게 하므로 로그 테이블에 사용될 수 있다.

InnoDB
민감한 정보를 갖는 테이블(회원테이블, 돈에 관련된 테이블)
갱신(읽기/쓰기) 위주의 트랜잭션이 요구되는 테이블.
인덱스가 많이 걸린 대량의 테이블은 이 놈을 사용하는 것이 좋다.

Memory(구명칭: Heap)
일시적으로만 사용되는 임시 테이블.
시스템의 메모리의

MySQL이 웹에서 자주 사용되므로 이와 관련해서 설명하면,
로깅이나 검색에서는 MyISAM을, 등록정보나 배너시스템에서는 InnoDB를,
임시테이블, 뉴스의 헤드라인, 로드가 많은 페이지의 데이터에 대해선 heap을 사용한다.

This article comes from dbakorea.pe.kr 강명규 (Leave this line as is)

 

자주쓰는 명령어 정리

root암호설정 – root로 로그인하여 해야함
% mysqladmin -u root password ’변경암호’
% mysqladmin -u root -p기존암호 password ’변경암호’

root암호변경설정
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
This is done with:
/usr/bin/mysqladmin -u root -p password ’new-password’
/usr/bin/mysqladmin -u root -h ns.dbakorea.pe.kr -p password ’new-password’

DB작업
DB생성: mysql> create database DB명 ( or % mysqladmin -u root -p create DB명 )
DB삭제: mysql> drop database DB명
DB사용: mysql> use DB명 (엄밀히 말하자면, 사용할 ’default database’를 선택하는 것이다.)
DB변경: mysql> alter database db명 DEFAULT CHARACTER SET charset (4.1이상에서만 available)

MySQL 연결
mysql -u 사용자 -p DB명 ( or % mysqladmin -u root -p drop DB명 )

데이터파일 실행(sql*loader기능)
mysql>load data infile ”데이터파일” into table 테이블명 ;
데이터파일에서 컬럼구분은 탭문자, Null값은 /n로 입력
데이터파일의 위치는 /home/kang/load.txt 와 같이 절대경로로 지정할것.

질의 파일 실행
쉘프롬프트상에서
mysql -u 사용자 -p DB명 < 질의파일 or mysql프롬프트상에서 mysql> source 질의파일

쉘프롬프트상에서 질의 실행
dbakorea@lion board]$ mysql mysql -u root -pxxxx -e
> ”INSERT INTO db VALUES(
> ’localhost’, ’aaa’, ’aaa’,
> ’Y’, ’Y’, ’Y’, ’Y’, ’Y’, ’Y’, ’Y’, ’Y’, ’Y’, ’Y’)”

사용자 생성 & 사용자에게 DB할당
shell> mysql –user=root -p mysql

mysql> INSERT INTO user VALUES(’localhost’,’사용자’,PASSWORD(’비밀번호’),’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’);
mysql> INSERT INTO user VALUES(’%’,’사용자’,PASSWORD(’비밀번호’),’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’);

mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES (’localhost’,’DB명’,’사용자’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’);
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES(’%’,’DB명’,’사용자’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’);

mysql> FLUSH PRIVILEGES; (or shell prompt: mysqladmin -u root -pxxxx reload)

CASE 2: GRANT명령을 이용한 사용자 생성(이 방법이 권장된다)
kang이라는 DB를 만들고, 이 DB를 아래에서 나열된 권한을 가진 kang이라는 사용자를 생성
* 권한종류는 mysql> help grant types 로 알 수 있다.

create database kang;
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX on kang.* to kang@localhost identified by ’kang’;
grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX on kang.* to kang@ ’%’ identified by ’kang’;

* 해당 DB에 모든 권한을 주려면 아래와 같이 생성한다.
grant ALL on kang.* to kang@localhost identified by ’kang’;
grant ALL on kang.* to kang@ ’%’ identified by ’kang’;

mysql> create database kang;
Query OK, 1 row affected (0.00 sec)

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX on kang.* to kang@localhost identified by ’kang’;
Query OK, 0 rows affected (0.00 sec)

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,INDEX on kang.* to kang@ ’%’ identified by ’kang’;
Query OK, 0 rows affected (0.01 sec)

mysql>

여러가지 명령정리
mysql> show variables; 서버의 variables(설정사항)출력
mysql> show variables like ’have_inno%’ 조건에 맞는 variables만 출력
mysql> show databases; database목록
mysql> show tables; 현재DB의 테이블목록(temporary table은 출력하지 않음)
mysql> show tables from db명; 지정된 db명이 소유한 테이블목록
mysql> show tables like ’mem%’; 조건에 맞는 테이블목록만 출력
mysql> show index from 테이블명; 인덱스 보기
mysql> show columns from 테이블명; 테이블구조(describe 테이블명, explain 테이블명)
mysql> show table status; 현재 DB의 테이블들의 상태(row수,table type,row길이,..)
mysql> show table status from db명; 지정된 DB의 테이블들의 상태(row수,table type,row길이,..)
mysql> show create table 테이블명; 해당 테이블 생성 SQL문 출력
mysql> rename table 테이블1 to 테이블2; 테이블명 변경(ALTER TABLE 테이블1 RENAME TO 테이블2)
mysql> rename table 테이블1 to 테이블2, 테이블3 to 테이블4; rename multiple tables
mysql> rename table db1명.테이블명 to db2명.테이블명; 테이블을 다른 DB로 이동
mysql> alter table 테이블명 add 컬럼명 데이터타입; 컬럼추가
mysql> alter table 테이블명 del 컬럼명; 컬럼제거
mysql> alter table 테이블명 modify 컬럼명 컬럼타입; 컬럼명에 지정된 컬럼타입의 변경
mysql> alter table 테이블명 change old컬럼명 new컬럼명 컬럼타입 컬럼명 변경
mysql> alter table 테이블명 type=innodb; 테이블type변경
mysql> create table 테이블명(..) type=heap min_rows=10000; 10000row를 수용할 수 있을 만큼 메모리할당(heap type이므로)
mysql> select version(); MySQL서버버전 출력
mysql> create table 테이블2 as select * from 테이블1; 테이블1과 동일한 테이블 생성(with 데이터, as는 생략가능)
mysql> create table 테이블2 as select * from 테이블1 where 1=2; 테이블1과 동일한 구조의 테이블 생성(without 데이터, 1=2는 0으로 할수도 있다.)
mysql> insert into 테이블2 select * from 테이블1; 테이블1의 데이터를 테이블2에 insert

테이블이 존재여부 파악
DROP TABLE IF EXISTS 테이블명;
CREATE TABLE 테이블명 (…);
프로그래밍 언어에서 COUNT(*)를 사용하여 질의가 성공하면 테이블이 존재함을 파악할 수 있다.
ISAM, MyISAM의 경우 COUNT(*)가 최적화되어 상관없으나, BDB, InnoDB의 경우 full scan이 발생하므로 사용하지 마라.
대신 select * from 테이블명 where 0; 을 사용하라. 질의가 성공하면 테이블이 존재하는 것이고, 아니면 존재하지 않는 것이다.

접속
mysql {-h 접속호스트} -u 사용자 -p 사용DB
-h로 다른 서버에 존재하는 MySQL접속시 다음과 같이 MySQL DB에 설정해줘야 한다.
mysql> INSERT INTO user VALUES(’접근을 허용할 호스트ip’,’사용자’,PASSWORD(’비밀번호’),’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’);
mysql> INSERT INTO db(Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES(’접근을 허용할 호스트ip’,’사용DB’,’사용자’,’Y’,’Y’,’Y’,’Y’,’Y’,’Y’);
mysql> FLUSH PRIVILEGES; or 쉴프롬프트상에서 % mysqladmin -u root -p flush-privileges

검색조건(where)
regular expression을 지원하다니 신기하군..
mysql> select * from work where 열명 regexp ”정규표현식”;

백업 & 복구
mysqldump {-h 호스트} -u 사용자 -p DB명 > 백업파일
mysql {-h 호스트} -u 사용자 -p DB명 < 백업파일 mysqldump -u root -p –opt db_dbakorea > dbakorea.sql
mysqldump -u root -p –opt db_board | mysql —host=remote-host -C database (상이한 머쉰)
mysql -u dbakorea -p db_dbakorea < dbakorea.sql mysqldump -u root -p –opt db_dbakorea | mysql —host=ns.dbakorea.pe.kr -C db_dbakorea 테이블 생성구문만을 화면에서 보려면 다음과 같이 –no-data를 사용한다. 테이블명을 생략하면 모든 테이블 출력 mysqldump -u 유저명 -p –no-data db명 테이블명 테이블 검사 isamchk 오라클 sysdate와 동일 insert into test values(’12’, now()); 유닉스 time()함수 리턴값 사용 FROM_UNIXTIME(954788684) UNIX_TIMESTAMP(”2001-04-04 :04:04:04”) MySQL 디폴트 DB&로그파일 위치 /var/lib/mysql /var/lib디렉토리는 여러 프로세스들이 사용하는 데이터를 저장하는 일종의 파일시스템상의 데이터베이스라고 볼 수 있다. replace 해당 레코드 존재하면 update하고, 존재하지 않는다면 insert한다.(insert문법과 동일) replace into test values(’maddog’,’kang myung gyu’)’ explain explain 질의문: 지정한 질의문이 어떻게 실행될 건지를 보여줌 mysql> explain select u.uid, u.name, a.name from sm_user u, sm_addr a where u.uid=a.uid;
+——-+——+—————–+—————–+———+——-+——+——-+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+——-+——+—————–+—————–+———+——-+——+——-+
| u | ALL | PRIMARY | NULL | NULL | NULL | 370 | |
| a | ref | sm_addr_uid_idx | sm_addr_uid_idx | 11 | u.uid | 11 | |
+——-+——+—————–+—————–+———+——-+——+——-+
2 rows in set (0.01 sec)

temporary table
크기가 큰 테이블에 있는 subset에 대한 질의라면 subset을 temporary table에 저장한 후 질의하는 것이 더 빠를 경우가 있다.
temporary table는 세션내에서만 유효하고(현재 사용자만이 볼수 있다는 뜻), 세션종료시 자동적으로 drop된다.

create temporary table (…);
create temporary table (…) type=heap; 디스크가 아닌 메모리에 테이블 생성

존재하는 permanent table의 테이블명과 동일하게 생성할 수 있으며,
temporary table은 permanent table보다 우선시되어 처리된다.
4.0.7의 감마버전에서 테스트하면 결과는 약간 달라진다. 버그인건지..

mysql> create table test (id varchar(10));
Query OK, 0 rows affected (0.01 sec)

mysql> insert into test values(’dbakorea’);
Query OK, 1 row affected (0.00 sec)

mysql> create temporary table test(id varchar(10));
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
Empty set (0.00 sec)

mysql> drop table test;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test;
+———-+
| id |
+———-+
| dbakorea |
+———-+
1 row in set (0.00 sec)

Table Type에 다른 Files on Disk

ISAM .frm (definition) .ISD (data) .ISM (indexes)
MyISAM .frm (definition) .MYD (data) .MYI (indexes)
MERGE .frm (definition) .MRG (list of constituent MyISAM table names)
HEAP .frm (definition)
BDB .frm (definition) .db (data and indexes)
InnoDB .frm (definition)

보통 mysqldump를 사용하여 백업을 수행하여 다른 DB서버에 데이터를 restore하면 된다.
MySQL은 별다른 작업없이 데이터파일을 단순히 복사(copy)하는 것만으로도 다른 서버에
DB을 이동시킬 수 있다. 하지만, 이런 방식이 지원되지 않는 table type도 있다.

ISAM: machine-dependent format하기때문에..
BDB : .db파일에 이미 테이블위치가 encode되어 있기때문에..
MyISAM, InnoDB, MERGE :가능(machine-independent format)

별다른 지정을 하지 않았다면 디폴트 TABLE type이 MyISAM이므로, 무난히 migration할 수 있다.
floating-point컬럼(FLOAT,DOUBLE)이 있다면 이러한 방식이 실패할 수 도 있다.

쉘에서는 mysql이 되는데 PHP에서 mysql.sock error를 내면서 MySQL이 안되는 경우
mysql.sock은 /tmp 아니면 /var/lib/mysql에 생기게 된다.
나의 경우, /var/lib/mysql에 mysql.sock파일이 있는데 PHP에서는 /tmp에서 찾으려하면서 에러를 발생했다.
/usr/bin/safe_mysqld파일에서 다음과 같이 수정한다.
주석(#)이 달린 것이 원래것이고 그 밑에 있는것이 수정한 것이다.

# MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/var/lib/mysql/mysql.sock}
MYSQL_UNIX_PORT=${MYSQL_UNIX_PORT:-/tmp/mysql.sock}

위와 같이 하니 /usr/bin/mysql이 /var/lib/mysql/mysql.sock에서 소켓파일을 찾으려 했다.
socket file을 지정하는 –socket이라는 옵션으로 다음과 같이 지정하면 된다.

mysql –socket=/tmp/mysql.sock -u dbakorea -p db_test

하지만 mysql실행시마다 이렇게 써줘야한다는 것이 상당히 귀찮다. 옵션이 바로 적용되게 설정하자.
mysql은 설정사항을 다음 3가지 파일에서 검색한다.

/etc/my.cnf global options(MySQL 전체적으로 사용되는 옵션 정의)
mysql-data-dir/my.cnf 특정 DB에 적용되는 option (/var/lib/mysql/my.cnf)
~/.my.cnf 사용자 각각의 설정(’~’문자는 사용자의 홈디렉토리는 의미)

/usr/share/mysql디렉토리에 예제가 있으므로 참고한다.
소켓파일의 지정은 다음줄을 넣어주면 된다.

socket = /tmp/mysql.sock

== /etc/my.cnf예 ==
# The following options will be passed to all MySQL clients
[client]
#password = your_password
port = 3306
socket = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock

MySQL에서 통계처리시
orderby, groupby 는 sort_buffer를 늘여준다.(show variables)

live table(smslog)에서 모든 질의를 처리하지 말고 summary table에 질의결과를 저장해 재질의 처리한다.
summary table이 heap-type table가 가능한지 확인할 것.

INSERT INTO tblTemp2 (fldID) SELECT tblTemp1.fldOrder_ID FROM tblTemp1 WHERE
tblTemp1.fldOrder_ID > 100;

join이 subselect보다 빠르다.
join시 사용되는 컬럼은 동일한 column type과 길이를 가져야만 최적의 속도를 보장한다.
즉, 동일 column type이지만 길이가 다르다면(char(11), char(10)), 동일한 컬럼도메인으로 변경해주는 것이 좋다.
where의 in은 optimize되어 있으므로 빠르다
insert,select는 동시에 수행가능하다.(어떻게?)
explain으로 질의과정 점검

varchar to/from char
conversion varchar를 char로 변경할 경우 모든 컬럼타입을 동시에 변경해야 한다.
반대의 경우, 하나만 char->charchar변경시 다른 모든 컬럼도 varchar로 변경됨
참.. 특이하구만..

mysql> CREATE TABLE chartbl (name VARCHAR(40), address VARCHAR(80));
Query OK, 0 rows affected (0.05 sec)

mysql> desc chartbl;
+———+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+——-+
| name | varchar(40) | YES | | NULL | |
| address | varchar(80) | YES | | NULL | |
+———+————-+——+—–+———+——-+
2 rows in set (0.03 sec)

mysql> alter table chartbl modify name char(40);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc chartbl;
+———+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+————-+——+—–+———+——-+
| name | varchar(40) | YES | | NULL | |
| address | varchar(80) | YES | | NULL | |
+———+————-+——+—–+———+——-+
2 rows in set (0.00 sec)

mysql> alter table chartbl modify name char(40), modify address char(80);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> desc chartbl;
+———+———-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+———+———-+——+—–+———+——-+
| name | char(40) | YES | | NULL | |
| address | char(80) | YES | | NULL | |
+———+———-+——+—–+———+——-+
2 rows in set (0.00 sec)

mysql>

”For each article, find the dealer(s) with the most expensive price.”

표준안
SELECT article, dealer, price
FROM shop s1
WHERE price=(SELECT MAX(s2.price)
FROM shop s2
WHERE s1.article = s2.article);

수정안(최적화)
CREATE TEMPORARY TABLE tmp (
article INT(4) UNSIGNED ZEROFILL DEFAULT ’0000’ NOT NULL,
price DOUBLE(16,2) DEFAULT ’0.00’ NOT NULL);

LOCK TABLES shop read;

INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article;

SELECT shop.article, dealer, shop.price FROM shop, tmp
WHERE shop.article=tmp.article AND shop.price=tmp.price;

UNLOCK TABLES;

DROP TABLE tmp;

==============================================================================
MySQL 특성정리
==============================================================================
primary key, foreign key지원
index 지원(15개컬럼, 256byte까지)
MySQL에서의 Stored Script개념 => SQL server language
commit-rollback개념 => lock tables(lock table test write -> 트랜잭션.. -> unlock tables)
컬럼명길이: 64자까지, 컬럼 Alias: 256자까지
not case-sensitive: keywords, functions, column, index명
case-sensitive: database, table, alias명
키워드,함수명은 대소문자구별이 없지만, db명과 table명은 Unix계열이라면 case-sensitive하다.
(이는 오브젝트명이 OS의 fs에 따라 저장되기 때문이다. 서버의 lower_case_table_names 변수를
1로 설정하면 오브젝트명은 모두 소문자로 저장되므로 유닉스-윈도간 호환성을 높일 수 있다.

지원되지 않는 부분:
Stored Procedure(5.0이상부터 지원된다고 함)
View(5.0이상부터 지원된다고 함)
Trigger(5.0이상부터 지원된다고 함)
subquery(4.1이상부터 지원된다고 함)
union, union all(4.0이상부터 지원됨)

[테이블 type에 따른 인덱스 특성]
Index Characteristic ISAM MyISAM HEAP BDB InnoDB
NULL values allowed No Yes As of 4.0.2 Yes Yes
Columns per index 16 16 16 16 16
Indexes per table 16 32 32 31 32
Maximum index row size (bytes) 256 500 500 500/1024 500/1024
Index column prefixes allowed Yes Yes Yes Yes No
BLOB/TEXT indexes allowed No Yes(255 bytes max) No Yes (255 bytes max) No

인덱스 생성
– alter table을 이용한 인덱스 생성이 더 flexible함
– 인덱스명은 생략가능

ALTER TABLE 테이블명 ADD INDEX 인덱스명 (인덱스컬럼);
ALTER TABLE 테이블명 ADD UNIQUE 인덱스명 (인덱스컬럼);
ALTER TABLE 테이블명 ADD PRIMARY KEY (인덱스컬럼);
ALTER TABLE 테이블명 ADD FULLTEXT (인덱스컬럼);

CREATE INDEX 인덱스명 ON 테이블명 (인덱스컬럼);
CREATE UNIQUE INDEX 인덱스명 ON 테이블명 (인덱스컬럼);
CREATE FULLTEXT INDEX 인덱스명 ON 테이블명 (인덱스컬럼);

unique인덱스와 primary key인덱스와의 차이
unique은 null허용하지만, primary key는 null허용 안함
unique은 하나의 테이블에 여러개 올 수 있지만, primary key는 하나만 존재

테이블생성시 지정
CREATE TABLE 테이블명
(
… column declarations …
INDEX 인덱스명 (인덱스컬럼),
UNIQUE 인덱스명 (인덱스컬럼),
PRIMARY KEY (인덱스컬럼),
FULLTEXT 인덱스명 (인덱스컬럼),

);

index prefix 생성
– 컬럼의 전체길이중 일부만 인덱스로 사용
– supported for ISAM, MyISAM, HEAP, and BDB tables, but not for InnoDB tables
– 지정되는 길이는 byte단위가 아닌 charater단위이므로, multi-byte character일 경우 주의
– blob, text 컬럼타입일 경우, index prefix 가 유용(255 길이까지 가능)

CREATE TABLE 테이블명
(
name CHAR(30) NOT NULL,
address CHAR(60) NOT NULL,
INDEX (name(10),address(10))
);

인덱스 삭제
DROP INDEX 인덱스명 ON 테이블명;
ALTER TABLE 테이블명 DROP INDEX 인덱스명;
ALTER TABLE 테이블명 DROP PRIMARY KEY;

outer join

[MySQL]
left outer joing : SELECT t1.*, t2.* FROM t1 LEFT OUTER JOIN t2 ON t1.i1 = t2.i2;
right outer joing: SELECT t1.*, t2.* FROM t1 RIGHT OUTER JOIN t2 ON t1.i1 = t2.i2;

[Oracle]
left outer joing : SELECT t1.*, t2.* FROM t1, t2 where t1.i1 = t2.i2(+);
right outer joing: SELECT t1.*, t2.* FROM t1, t2 where t1.i1(+) = t2.i2;

SELECT
student.name, student.student_id,
event.date, event.event_id, event.type
FROM
student, event
LEFT JOIN score ON student.student_id = score.student_id
AND event.event_id = score.event_id
WHERE
score.score IS NULL
ORDER BY
student.student_id, event.event_id;

:= 문장을 이용한 변수의 설정

현재 dbakorea의 데이터베이스강좌게시판에 등록된 총 게시물은 43개이다. 43개의 강좌를 읽은 수(hit수)는 각각 다르다.
평균 hit수를 구해 보자.

mysql> select @total_hit := sum(hit), @total_record := count(*) from zetyx_board_database;
+————————+—————————+
| @total_hit := sum(hit) | @total_record := count(*) |
+————————+—————————+
| 3705 | 43 |
+————————+—————————+
1 row in set (0.00 sec)

mysql> select @total_hit/@total_record as 평균HIT;
+—————–+
| 평균HIT |
+—————–+
| 86.162790697674 |
+—————–+
1 row in set (0.00 sec)

select substring(subject from 9) from zetyx_board_database where substring(subject, 1, 8) = ‘[ORACLE]’;

보통 상용DBMS들이 row-level locking을 지원한다. 쉽게 말해 레코드단위로 락킹한다는 말이다.
반면, MySQL의 MyISAM 테이블타입은 table-level locking을 사용한다.
쉽게 말하면, insert, update, delete작업은 전체 테이블에 락을 걸고 처리된다는 것이다.
row-level락보다 비효율적이지만,.. MySQL은 빠르기 때문에 이 단점이 상쇄된다.

Compressed MyISAM(packed MyISAM)
정적인 테이블데이터는 압축하여 20-60%정도의 공간을 절약할 수 있다.
Production데이터를 CD로 받아서 차후 디스크에 풀지 않고 CD자체로 바로 사용할 수도 있다.
gzip등으로 백업받으면 이를 푸는 과정이 필요할 것이다.
% myisampack dbakorea.myi

데이터베이스 게시판의 Merge Table에 좀 더 자세한 내용을 적어 두었다.

RAID Table
1개의 테이블은 OS상에 3개의 파일로 구성된다.
스키마파일(.frm), data파일(.myd), index파일(.myi)
MySQL의 RAID테이블은 데이터파일(.myd)을 여러개의 파일들로 구성하는 것이다.

create table raid_test (…)
type=myisam raid_type=striped raid_chunks=4 raid_chunsize=8

테이블을 4개의 데이터파일로 나누고, 8kb단위로(8kb stripe) 라운드로빈 방식으로 write가 이루어진다.

This article comes from dbakorea.pe.kr 강명규 (Leave this line as is)

join 번역 – DB사랑넷 펌

-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-*-
원본사이트: http://www.devshed.com/Server_Side/MySQL/Join/
* MySQL Table Joins
*(o) By W.J. Gilmore
*(o)| July 06, 1999
(o)| | 번역 : 원주희(lolol@shinan.hongik.ac.kr)
=======================================================================

[영어실력이 너무 부족하다보니 잘못된 부분이 있을 수도 있습니다.
고쳐야할 부분이 있으면 꼭 메일을 보내주세요.]

◆◆ 소개하기

SQL(Structured Query Language)의 가장 큰 특징 중의 하나는 여러개의 테이블을
연결시켜 데이터를 검색하거나 조작할 수 있는 기능이다. 이것은 데이터를 쉽고
빠르게 검색하고 불필요한 데이터를 줄여주는 장점이 있다. 다른 SQL 언어와 마찬
가지로 MySQL도 join명령어로 이 연산을 수행한다.

간단히 말하면 join은 두개 이상의 테이블로부터 필요한 데이터를 연결해 하나의
포괄적인 구조로 결합시키는 연산이다.

예를 들어, 한 컴퓨터 제조업자가 자신의 데이터를 능률적으로 관리하기 위한
데이터베이스가 필요하다고 하자. 이 데이터들은 주문, 고객, 생산과 같은 어떤
일관성있는 개념과 관련된 각각의 데이터들이 모여 다양하고 간결한 테이블들을
이룰 것이다. 테이블을 생성한 후에는, 다양한 예를 들어 데이터베이스에서 가장
많이 사용되는 join의 조작법에 대해 설명하겠다.

첫번째 테이블은 제조업자가 분류한 다양한 타입의 PC들의 데이터로 구성될 것이다.
———————————————-
mysql> create table pcs (
-> pid INT, // product id
-> spec char(3),
-> os char(10),
-> ram INT,
-> hd char(4)
-> );
———————————————–

두번째 테이블은 제조업자의 다양한 고객들에 관한 데이터로 이루어질 것이다.
———————————————–
mysql> create table clients (
-> name char(25),
-> cid char(8), // client id
-> email char(25),
-> tel char(10)
-> );
———————————————–

세번째 테이블은 주문 정보에 관한 데이타를 포함할 것이다.
———————————————–
mysql> create table orders (
-> order_date date,
-> pid INT,
-> cid char(8)
-> );
———————————————–

◆◆ 자료(Data) 삽입하기

각각의 테이블에 아래와 같이 자료를 삽입해 보자.

☆ pcs (테이블1)
+——+——+——-+——+——+
| pid | spec | os | ram | hd |
+——+——+——-+——+——+
| 1 | 386 | Linux | 64 | 3.1 |
| 2 | 386 | Linux | 128 | 4.2 |
| 3 | 486 | WinNT | 64 | 3.1 |
| 4 | 586 | Linux | 128 | 4.2 |
| 5 | 586 | Win98 | 128 | 6.4 |
+——+——+——-+——+——+

[삽입방법]
———————————————————–
mysql> INSERT INTO pcs (pid, spec, os, ram, hd)
-> VALUES (1, 386, Linux, 64, 3.1);
———————————————————–

☆ clients (테이블2)
+——–+———+—————————+————+
| name | cid | email | tel |
+——–+———+—————————+————+
| 원주희 | wjh-042 | haremoon@won.hongik.ac.kr | 123-456-78 |
| 홍길동 | hgd-043 | honggd@won.hongik.ac.kr | 421-555-34 |
| 이쁘니 | pty-042 | pretty@won.hongik.ac.kr | 459-555-32 |
| 못난이 | ugy-043 | ugly@won.hongik.ac.kr | 439-555-88 |
+——–+———+—————————+————+

[삽입방법]
———————————————————–
mysql> INSERT INTO clients (name, cid, email, tel)
-> VALUES (원주희, wjh-042,
-> haremoon@won.hongik.ac.kr, 123-456-7890);
———————————————————–

☆ orders (테이블3)
+————+——+———+
| order_date | pid | cid |
+————+——+———+
| 1999-12-05 | 2 | wjh-042 |
| 1999-12-04 | 3 | hgd-043 |
| 1999-12-04 | 1 | wjh-042 |
| 1999-12-05 | 2 | wjh-042 |
| 1999-12-12 | 5 | ugy-043 |
| 1999-12-05 | 5 | pty-042 |
+————+——+———+

[삽입방법]
———————————————————–
mysql> INSERT INTO orders (order_date, pid, cid)
-> VALUES (1999-12-05, 2, wjh-042);
———————————————————–

자, 이제부터 만들어진 3개의 테이블로부터 필요한 데이터만을 추출해 결합하는
방법에 대해 알아보자. 만일 당신의 상사가 특정한 날에 특정한 PC를 주문한 모든
고객의 이메일 목록을 원한다고 하자! 또는 특정한 고객에 의해 작성된 주문서에
있는 RAM의 양을 보고받기를 원한다고 하자. 이러한 모든 일들은 다양한 join문에
의해 쉽게 수행될 수 있다. 만들어진 테이블을 사용해 첫번째 join문을 생성해보고
작성해보자.

◆◆ The Cross Join

Cross Join은 가장 기본적인 join의 타입으로 한 테이블에 있는 각각의 열이 다른
테이블의 모든 열에 간단하게 매치되어 출력된다. 능률적이지는 않지만, 모든 join
의 공통된 특징을 나타내준다.

Cross Join의 간단한 예 :
———————————————-
mysql> SELECT * FROM pcs, clients;
———————————————-

매우 많은 열들이 출력될 것이다. 테이블1(pcs)에 있는 각각의 열이 테이블2(clients)
의 모든 열에 매치된다는 것을 기억하자. 따라서, 3 열을 가진 테이블1(pcs)과 4 열을
가진 테이블2(clients)를 포함한 join문은 총 12 열의 테이블을 만들것이다.

즉 cross-join은 테이블1에 있는 각각의 열들이 테이블2에 있는 모든열들을 한 번씩
교차해 출력한다고 기억하는 것이 쉬울 것같다.

첫번째 join을 성공적으로 수행했다면 다음의 예도 어렵지 않을 것이다.

아래의 예를 따라해 보고 어떤 결과가 출력될지 예상해보자.

—————————————————————————–
mysql> select c.name, o.cid from orders o, clients c where o.cid = wjh-042;
—————————————————————————–

+——–+———+
| name | cid |
+——–+———+
| 원주희 | wjh-042 |
| 원주희 | wjh-042 |
| 원주희 | wjh-042 |
| 홍길동 | wjh-042 |
| 홍길동 | wjh-042 |
| 홍길동 | wjh-042 |
| 이쁘니 | wjh-042 |
| 이쁘니 | wjh-042 |
| 이쁘니 | wjh-042 |
| 못난이 | wjh-042 |
| 못난이 | wjh-042 |
| 못난이 | wjh-042 |
+——–+———+

예상한 대로 결과가 출력되었나? clients 테이블에 있는 각각의 name이 orders테이
블의 wjh-042를 포함한 열마다 매치되어 출력되었다. 지금까지의 설명이 Cross
Join을 설명하는데 충분하지 않으므로 다른 질의를 사용해가며 JOIN을 활용해보기
바란다.

NOTE : 왜 테이블의 이름에 별명을 주어 사용할까? Aliases(별명)은 질의를 입력할
때 반복적인 키의 입력을 줄여주는 방법으로 사용되어진다. 따라서 열을 지정해 줄
때 반복적으로 clients를 한자 한자 입력하는 대신에, 질의내에 from clients c
를 지정해주고 c를 사용할 수 있다.

Cross Join이 테이블들을 연결해 주기는 하지만, 능률적이지는 못하다. 따라서
각각의 테이블들로 부터 우리가 원하는 데이타를 어떻게 하면 쉽게 선택할 수 있는지
계속 다음 장을 읽어보기 바란다.

◆◆ The Equi-join

Equi-join은 한 테이블에 있는 어떠한 값이 두번째(또는 다수의) 테이블내에 포함된
값에 일치 할 때 수행된다.

product id 가 1인 PC를 주문한 고객의 목록을 원한다고 가정해 보자.

——————————————————————-
mysql> select p.os, c.name from orders o, pcs p, clients c
-> where p.pid=o.pid and o.pid = 1 and o.cid=c.cid;
——————————————————————-

+——-+——–+
| os | name |
+——-+——–+
| Linux | 원주희 |
+——-+——–+

◆ Non-Equi-join

Equi-join은 다수의 테이블들 사이에서 일치하는 자료들만을 추출해낸다. 그러나
만일 일치하지 않은 자료들만을 추출해야 한다면…? 예를 들어, 당신의 상사가
주문한 pid가 제품의 pid보다 더 큰 order id의 모든 운영체제(OS)의 목록을
필요로 한다면 어떻게 할 것인가? 적당히 이름을 non-equi join라고 하겠다.

——————————————————————-
mysql> SELECT p.os, o.pid from orders o, pcs p where o.pid > p.pid;
——————————————————————-

+——-+——+
| os | pid |
+——-+——+
| Linux | 2 |
| Linux | 3 |
| Linux | 2 |
| Linux | 5 |
| Linux | 5 |
| Linux | 3 |
| Linux | 5 |
| Linux | 5 |
| WinNT | 5 |
| WinNT | 5 |
| Linux | 5 |
| Linux | 5 |
+——-+——+

orders 테이블의 pid가 pcs테이블의 pid보다 더 큰 모든 열들이 매치될 것이다.
주의깊게 살펴보면, 여러가지 제한을 준 간단한 cross-join 임을 파악할 수 있
을 것이다. 상사에게는 특별하게 유용하지 않을 지도 모르지만, 매우 유용한 기
능인 left join을 위한 준비 과정으로 생각하자. 자, 이제 다음장으로 가서
left join을 사용할 때 유용할 수있는 옵션들에 대해 집중적으로 알아보자.

◆◆ The Left Join

Left Join은 사용자가 어떠한 제한을 기반으로 관심있는 모든 종류의 자료를 추출
하게한다. 테이블 join중 가장 막강한 옵션으로, 테이블을 매우 쉽게 조작할 수
있게 한다.

만일 상사가 좀더 자세히, 자세히, 자세하게, 자세하게!를 외친다고 가정해보자.
left join이 우리의 문제를 해결해 줄 것이다.

——————————————————————-
mysql> select * from orders left join pcs on orders.pid = pcs.pid;
——————————————————————-

+————+——+———+——+——+——-+——+——+
| order_date | pid | cid | pid | spec | os | ram | hd |
+————+——+———+——+——+——-+——+——+
| 1999-12-05 | 2 | wjh-042 | 2 | 386 | Linux | 128 | 4.2 |
| 1999-12-04 | 3 | hgd-043 | 3 | 486 | WinNT | 64 | 3.1 |
| 1999-12-04 | 1 | wjh-042 | 1 | 386 | Linux | 64 | 3.1 |
| 1999-12-05 | 2 | wjh-042 | 2 | 386 | Linux | 128 | 4.2 |
| 1999-12-12 | 5 | ugy-043 | 5 | 586 | Win98 | 128 | 6.4 |
| 1999-12-05 | 5 | pty-042 | 5 | 586 | Win98 | 128 | 6.4 |
+————+——+———+——+——+——-+——+——+

고객이 주문한 모든 PC들의 목록을 추출해 낸다. 예를 들어, PHP3 또는 Perl
스크립트를 사용해 영수증을 출력하는데 사용할 수도 있다. 고객들에게 우리회
사로부터 구입한 모든 제품의 목록을 가끔씩 메일로 보내야 할 때에도
clients 테이블과 연결해 사용할 수 있을 것이다.

아래의 예에서 우리는 제품의 id(pid)가 3인 PC의 정보만을 볼 수 있다.

——————————————————————-
mysql> select * from orders left join pcs on pcs.pid=3 and pcs.pid=orders.pid;
——————————————————————-

+————+——+———+——+——+——-+——+——+
| order_date | pid | cid | pid | spec | os | ram | hd |
+————+——+———+——+——+——-+——+——+
| 1999-12-05 | 2 | wjh-042 | NULL | NULL | NULL | NULL | NULL |
| 1999-12-04 | 3 | hgd-043 | 3 | 486 | WinNT | 64 | 3.1 |
| 1999-12-04 | 1 | wjh-042 | NULL | NULL | NULL | NULL | NULL |
| 1999-12-05 | 2 | wjh-042 | NULL | NULL | NULL | NULL | NULL |
| 1999-12-12 | 5 | ugy-043 | NULL | NULL | NULL | NULL | NULL |
| 1999-12-05 | 5 | pty-042 | NULL | NULL | NULL | NULL | NULL |
+————+——+———+——+——+——-+——+——+

◆ The Using Clause

left join에 약간의 옵션을 주어 둘 이상의 테이블에 있는 동일한 컬럼을 조금 더
깊게 연관지을수도 있다. on과 using옵션이 사용되며, 아래의 예제를 참조하자.

——————————————————————–
## 원본의 예제는 아래와 같지만, 에러가 발생해 필자가 MySQL매뉴얼을 참조해
나름대로 수정을 했다.##
mysql> SELECT * from clients join on orders where clients.cid = orders.cid;
mysql> SELECT * from clients join on orders using (cid);
——————————————————————-

==> 수정한 예제

——————————————————————-
mysql> SELECT * from clients left join orders on clients.cid = orders.cid;
——————————————————————-

또는 아래와 같이 나타낼 수도 있다.

——————————————————————-
mysql> SELECT * from clients left join orders using (cid);
——————————————————————-

두 예제 모두 똑같은 결과가 출력될 것이다.

+——–+———+—————————+————+————+——+———+
| name | cid | email | tel | order_date | pid | cid |
+——–+———+—————————+————+————+——+———+
| 원주희 | wjh-042 | haremoon@won.hongik.ac.kr | 123-456-78 | 1999-12-05 | 2 | wjh-042 |
| 원주희 | wjh-042 | haremoon@won.hongik.ac.kr | 123-456-78 | 1999-12-04 | 1 | wjh-042 |
| 원주희 | wjh-042 | haremoon@won.hongik.ac.kr | 123-456-78 | 1999-12-05 | 2 | wjh-042 |
| 홍길동 | hgd-043 | honggd@won.hongik.ac.kr | 421-555-34 | 1999-12-04 | 3 | hgd-043 |
| 이쁘니 | pty-042 | pretty@won.hongik.ac.kr | 459-555-32 | 1999-12-05 | 5 | pty-042 |
| 못난이 | ugy-043 | ugly@won.hongik.ac.kr | 439-555-88 | 1999-12-12 | 5 | ugy-043 |
+——–+———+—————————+————+————+——+———+

다른 구문의 예를 적용해 가며 left join에 대해 이해하기 바란다. 공부를 하다보면
left join이 여러분의 개발활동에 매우 중요한 역할을 한 다는 것을 느낄 것이다.
테이블 join에 관한 정보를 좀더 깊게 교환하고 싶다면 http://www.mysql.com에 있는
다양한 토론 그룹을 체크해 보기 바란다.

◆◆ Self-joins

Self-join은 관리자가 하나의 테이블에 관련된 데이타를 집중시키는 막강한 방법을
제공해 준다. 사실, self-join은 그 자신의 테이블에 결합하는 것에 의해 수행된다.
개념의 이해를 위해 예를 들어 설명하겠다.

컴퓨터 워크스테이션을 만드는데 사용되는 하드웨어의 다양한 부품에 관한 정보를
가진 매우 큰 데이타베이스를 관리해야 한다고 가정하자. 워크스테이션은 데스크,
PC, 모니터, 키보드, 마우스등으로 이루어져 있다. 게다가, 데스크는 워크스테이션의
모두 다른 부분의 부모라고 생각될 수 있다. 우리는 각 워크스테이션의 레코드가
정확한 자료로 유지되기를 원할 것이며, 유일한 ID번호를 부여함으로써 워크스테이션
의 모든 부분을 구체적으로 관련시킬 것이다. 사실, 각 부분은 항목을 분명하게 해주는
유일한 ID번호와 그것의 부모(데스크) ID번호를 확인하기 위한, 두개의 ID 번호를
포함 것이다.

테이블이 아래와 같다고 가정하자.

mysql> select * from ws;
+———+———–+———–+
| uniq_id | name | parent_id |
+———+———–+———–+
| d001 | desktop | NULL |
| m4gg | monitor | d001 |
| k235 | keyboar | d001 |
| pc345 | 200mhz pc | d001 |
| d002 | desktop | NULL |
| m156 | monitor | d002 |
| k9334 | keyboar | d002 |
| pa556 | 350mhz pc | d002 |
+———+———–+———–+

desktop은 그와 관련된 모든 부분들의 부모와 같으므로 parent_id를 가지고 있지
않음을 주목하자. 지금부터 유용한 정보를 위한 질의를 시작할 것이다. self-join
의 사용법을 쉽게 설명하기 위해 테이블을 간단하게 만들었다.

——————————————————————-
mysql> select t1.*, t2.* from ws as t1, ws as t2;
——————————————————————-

어떻게 출력되는가? 이전처럼, 첫번째 테이블의 각 열들이 두번째 테이블에 있는
모든 열들에 매치되어 연결되 출력될 것이다. 우리에게 매우 유용하지는 않지만
다시 한번 시도해보고 확인해 보기바란다. 좀더 재미있는 예를 들어보겠다.

——————————————————————-
mysql> select parent.uniq_id, parent.name, child.uniq_id, child.name
-> from ws as parent, ws as child
-> where child.parent_id = parent.uniq_id and parent.uniq_id = d001;
——————————————————————-

흥미로운 결과가 출력될 것이다.
+———+———+———+———–+
| uniq_id | name | uniq_id | name |
+———+———+———+———–+
| d001 | desktop | m4gg | monitor |
| d001 | desktop | k235 | keyboar |
| d001 | desktop | pc345 | 200mhz pc |
+———+———+———+———–+

self-join은 테이블의 자료를 검증하는 방법으로도 사용된다. 테이블내에 있는
uniq_id컬럼은 테이블에서 유일해야 하며, 만일 데이타의 엔트리가 깊어 뜻하지
않게 같은 uniq_id를 가진 두개의 항목이 데이타베이스에 입력된다면 좋지 않은
결과가 생길것이다. 이럴 경우 정기적으로 self-join을 사용해 체크할 수 있다.
우리는 350mhz pc의 uniq_id가 m156(이 값은 워크스테이션 d002에 속한
모니터의 uniq_id 값이다.)이 되도록 변경했다고 가정하자.

테이블의 내용은 다음과 같이 변경한다.
——————————————————————-
mysql> update ws set uniq_id = m156 where name = 350mhz pc;
——————————————————————-

아래의 예를 참고해 ws테이블에 self-join을 적용해보자.

——————————————————————-
mysql> select parent.uniq_id, parent.name, child.uniq_id, child.name
-> from ws as parent, ws as child
-> where parent.uniq_id = child.uniq_id and parent.name <> child.name;
——————————————————————-

아래와 같이 출력될 것이다.
+———+———–+———+———–+
| uniq_id | name | uniq_id | name |
+———+———–+———+———–+
| m156 | 350mhz pc | m156 | monitor |
| m156 | monitor | m156 | 350mhz pc |
+———+———–+———+———–+

Table join은 데이터베이의 관리를 쉽게 도와줄것이다. 구문의 이해를 정확하게
하기위해서 예제에서 배운 명령을 다양하게 변화시켜 적용해보기 바란다.