출처 : http://sdnkorea.com/blog/550

 

대부분의 비즈니스 전문가는 썬 마이크로시스템즈가 오픈 소스 데이터베이스 공급업체인 MySQL AB를 인수하기로 했다는 발표에 박수를 보냈습니다. 인수를 통해 MySQL이 썬의 관리 하에 들어오게 되었음은 분명하지만 개발자들은 이번 인수가 자신들에게 어떤 영향을 미치게 될지 궁금해 하고 있습니다.

개발자들은 개발할 때 쉽게 사용할 수 있고, 기업 수준에 따라 쉽게 확장할 수 있으며, 기술을 숙지하는 데 투자한 시간을 충분히 보상받을 수 있을 만큼 생명력이 긴 제품을 원하고 있습니다. 일부 MySQL 커뮤니티 회원은 두 회사가 합병된 뒤에도 이 뛰어난 오픈 소스 제품을 계속 이용할 수 있을지, 또한 이 커뮤니티가 앞으로도 활발한 활동을 보일 수 있을지에 대해 궁금해 하고 있습니다.


MySQL, 최고의 오픈 소스 옵션

MySQL은 오픈 소스 데이터베이스 시장을 이끌고 있으며 썬의 MySQL도 앞으로 오픈 소스 커뮤니티와 함께 할 것입니다. MySQL의 CEO인 마틴 미코스는 무료 오픈 소스 소프트웨어에 대한 썬의 약속으로 인수 합병이 성사될 수 있었다고 밝히고 있습니다. 조나단 슈왈츠는 자신의 블로그에 4년 동안의 준비 기간 동안 오픈 시스템에 대한 썬의 약속이 오픈 소스 소프트웨어로 확장되었고 썬이 MySQL 커뮤니티를 위한 홈베이스의 역할을 할 것임을 MySQL 경영진이 분명히 인식하게 되었다고 기술하고 있습니다. 자바 기술과 MySQL 모두 GPL 버전 2 라이센스를 적용받으며 향후에 GPL 버전 3 라이센스가 완성되면 이 라이센스의 적용을 받게 될 것입니다.

MySQL 개발자의 경우에는 거의 변화가 없을 것입니다. 그러나 MySQL Enterprise 고객의 경우 포춘 500대 기업의 지원을 통해 서비스 및 지원 수준이 대폭 향상될 것으로 기대할 수 있습니다. 그리고 MySQL이 보다 광범위하게 채택됨에 따라 모든 사용자가 더욱 빠른 제품 혁신과 더욱 정밀한 테스팅, 그리고 전반적으로 향상된 MySQL 제품을 기대할 수 있습니다.


MySQL
및 썬 소프트웨어 스택

오픈 소스에 대한 썬의 약속에 대해 회의적인 사람들은 다른 썬의 제품, 특히 그 자체가 오픈 소스 혼돈기를 겪었던 솔라리스 OS를 강제로 그러나 의식하기 어려울 정도로 매우 교묘히 채택해야 하는 상황을 우려할 수도 있습니다. 그러나 인텔, 마이크로소프트, 오라클 및 기타 경쟁업체와의 제휴를 포함한 썬의 움직임은 썬이 개발자에게 특정한 소프트웨어의 사용을 강제하기보다 시장에 적응하고 있음을 나타냅니다.

Linux 운영 체제, Apache 웹 서버, MySQL 데이터베이스 서버, PHP/Perl/Python 언어를 총칭하는 LAMP라는 약어는 오픈 소스 개발자가 선호하는 소프트웨어 스택을 설명합니다. 이 스택의 각 요소는 오픈 소스 및 소유권이 있는 경우 모두에 대해 대안이 존재하므로 LAMP는 의미 파악이 쉬운 약어로 그다지 유용하지 않습니다. 예를 들어 매일 50,000건의 MySQL 다운로드 중에서 대부분의 바이너리는 WAMP 개발자 등이 Windows 운영 체제용으로 사용할 것입니다. 썬에서 후원하는 오픈 소스 툴 및 제품, 가장 대표적으로 GlassFish 애플리케이션 서버 같은 제품에도 비슷한 수가 적용됩니다. 썬은 LAMP 개발자 간의 시장 점유율을 위해 적극적으로 경쟁하기로 결정한 한편 자사의 툴과 소프트웨어를 일반적인 플랫폼으로 포팅하고 있습니다.

썬 소프트웨어를 사용하는 개발자는 MySQL이 썬 소프트웨어 스택에서 PostgreSQL 및 JavaDB 등 다른 데이터베이스의 대안으로 사용될지에 대해 궁금증을 가지고 있을 것입니다. 썬은 개발자들이 Apache Derby 같은 데이터베이스 슬롯에 삽입하고자 하는 일반 관리 소프트웨어 가운데 어떠한 것도 지원할 의도가 전혀 없음을 다시 한 번 강조합니다. PostgreSQL 코어 팀의 조쉬 버커스에 따르면 썬은 PostgreSQL에 대한 지원을 중단하지 않을 것입니다. 제일의 MySQL 공급업체가 되는 것이 아니라 제일의 데이터센터 공급업체가 되는 것이 썬의 목표입니다. 데이터베이스의 세계에서는 '한 제품이 모든 용도를 만족시키기'란 불가능합니다. "

웹 애플리케이션 소프트웨어 스택에서 이미 MySQL을 사용하고 있었다면 운영 체제, 웹 서버 또는 언어에 상관없이 MySQL을 계속해서 무료로 커뮤니티 기반 데이터베이스로 사용할 수 있습니다. 기업 수준 성능으로 확장하려는 경우에는 썬에서 필요한 지원과 서비스를 제공합니다. 다른 데이터베이스를 사용하고 있는 경우에도 썬 제품과 함께 사용하는 데 문제가 없습니다.


MySQL
정보

웹 애플리케이션을 개발할 때 MySQL 또는 기타 썬 오퍼링을 사용해본 적이 없다면 이번이 재평가해볼 기회입니다.

한 제품이 모든 용도의 데이터베이스를 만족시킬 수는 없지만 MySQL의 인기는 이 제품이 그러한 이상에 얼마나 가까운지를 보여 줍니다. MySQL의 적응성 및 사용 편이성은 그 아키텍처와 확장성에 크게 힘입고 있습니다. 이 아키텍처는 MySQL이 현재까지 전세계적으로 1,100만 개 이상 설치되어 사용 중이고 1억 회 이상 다운로드되거나 배포된 이유를 잘 말해 줍니다. 이 글의 작성 시점에도 새로운 MySQL이 2초에 한 번씩 다운로드되고 있습니다.


수평 성능 확장 기능

여러 MySQL 사용자가 성공을 위해 채택하는 전략은 수평 성능 확장(Scale-Out)이라고 알려져 있습니다. 이 용어는 데이터를 여러 대의 물리적 서버에 복제하는 방식으로 데이터베이스의 워크로드를 상용 하드웨어 전체에 분리 및 분산시키는 아키텍처 설계 방식을 의미합니다. 이 전략은 수직 성능 확장(Scale-Up)이라고 하는, 적은 수의 단일 서버군에 CPU 전력, 스토리지, 메모리를 추가하는 방법과 대조됩니다. 수평 성능 확장 전략은 이 방식으로 높은 수준의 업타임을 확보한 트래픽 사용량이 높은 여러 MySQL 웹 사이트에서 보듯이 일반적으로 장애 허용 기능과 전반적인 비용 대비 성능이 우수합니다.

그림1은 수평 성능 확장 아키텍처를 보여 줍니다.

Array

그림 1. 수평 성능 확장 아키텍처
크게 보려면 여기를 클릭하십시오.

일 반적인 경우에 사용자는 고객이 사용하는 웹 또는 애플리케이션 서버에서 세션을 시작합니다. 이러한 서버는 로드 밸런서나 기타 요청을 전달하는 애플리케이션 로직을 통해 MySQL 데이터베이스 서버에 필요한 요청을 보냅니다. 요청으로 인해 서버가 데이터베이스에 데이터를 작성하는 경우 서버는 해당 데이터를 다른 MySQL 서버에 복제함으로써 향후의 쿼리에 대비해 데이터를 최신 상태로 유지합니다.

고가용성, 대용량 설정에서 MySQL 서버에 오류가 발생하면 일반적으로 오류가 발생한 서버를 오프라인 상태로 만든 다음 다른 서버로 스왑합니다. 몇 분 후에는 이전과 같이 데이터베이스를 새로운 서버로 복제할 수 있고 쿼리 서비스를 진행할 수 있습니다. 저비용의 상용 서버에서는 이러한 스왑 전략을 비용 효율적으로 구사할 수 있습니다.


스토리지 엔진 아키텍처

MySQL의 스토리지 엔진 아키텍처는 데이터베이스 세계에서 고유한 기능을 제공합니다. 그림 2는 흥미로운 착탈식 스토리지 엔진 레이어를 보여 줍니다.

Array


그림 2. 스토리지 엔진 아키텍처
크게 보려면 여기를 클릭하십시오.

MySQL 은 다른 데이터베이스 관리 시스템과 마찬가지로 스토리지 엔진 레이어 위에 자체 데이터베이스를 표시합니다. 여러 개의 커넥터를 사용하여 데이터베이스를 쿼리할 수 있습니다. 서버의 연결 풀은 인증을 제공하고 스레드, 연결, 메모리, 캐시를 관리합니다. 서버의 모듈은 쿼리 구문을 분석하고, 액세스 경로를 최적화하는 등의 작업을 합니다.

이 레이어 아래에서 여러 개의 스토리지 엔진을 아키텍처에 연결할 수 있습니다. 스토리지 엔진은 물리적 서버 수준에서 유지되는 기본 데이터와 관련된 작업을 수행하는 데이터베이스 서버의 구성요소입니다. 착탈형 스토리지 엔진 아키텍처를 사용하면 표준 관리 및 지원 서비스를 기본 스토리지 엔진 모두에 적용할 수 있습니다. 그러면 여러 데이터베이스를 단일 관리자의 제어 하에 관리하는 것과 비슷한 결과가 됩니다.

아카이브에 사용되는 스토리지 엔진 등 일부 스토리지 엔진의 경우에는 원래 트랜잭션이 불가능합니다. 이러한 스토리지 엔진은 매우 효율적으로 데이터를 삽입하고 읽을 수 있습니다. 어떤 스토리지 엔진은 트랜잭션 작업이 필요할 경우 효율성에 맞게 조정되지만 또 어떤 스토리지 엔진은 클러스터링을 통해 계속해서 고가용성을 제공합니다. 이러한 스토리지 엔진은 자신이 소유한 시스템의 기본적인 오버헤드를 방지합니다. 일반적으로 이러한 시스템은 테이블 아키텍처의 트랜잭션 오버헤드가 필요하지 않은 경우에도 이러한 오버헤드를 감수해야 합니다.

예를 들어 기본적으로 데이터를 조회해야 하는 사이트는 MySQL의 기본 엔진인 MyISAM 스토리지 엔진을 사용하여 트랜잭션 오버헤드를 방지할 수 있습니다. MyISAM 엔진은 고속 쿼리 및 삽입 기능을 제공하지만 트랜잭션이 불가능하며 테이블 수준의 잠금을 제공하고 인덱스 기능을 지원합니다. 기존의 데이터 웨어하우스에 적합한 엔진입니다.

MySQL 데이터베이스 서버는 여러 가지 내부 스토리지 엔진과 함께 제공됩니다. 외부 스토리지 엔진은 특정 제품 및 상황에 따라 성능이 최적화되도록 조정되며 독립 소프트웨어 공급업체나 MySQL 커뮤니티 모두에서 제공합니다.

여러 스토리지 엔진이 있으면 여러 데이터베이스의 기능이 제공됩니다. 다른 데이터베이스에서 MySQL로 마이그레이션하는 경우 SQL 코드가 같으므로 사용 중인 애플리케이션이 데이터베이스 서버와 상호 작용하는 방식을 변경할 필요가 없습니다. 그러나 내부 데이터베이스를 MySQL 서버로 마이그레이션하는 경우에는 애플리케이션의 요구에 맞는 스토리지 엔진을 선택할 수 있습니다.

예를 들어 트랜잭션 가능한 데이터베이스가 필요하다면 단일 구문을 사용하여 트랜잭션이 불가능한 시스템에서 트랜잭션이 가능한 시스템으로 데이터베이스를 변환할 수 있습니다. 그런 다음 데이터 웨어하우스가 필요하면 명령 하나로 강력한 데이터 검색에 적합하게 조정된 열 중심 데이터베이스를 소유할 수 있습니다. 주요 작업이 데이터를 캡처하고 저장하는 일이라면 아카이브 스토리지 엔진은 대용량 삽입 작업을 효율적으로 처리하고 공간을 적게 차지하도록 데이터를 압축하는 일에 맞게 설계됩니다.


고가용성

ISP 및 메인스트림 비즈니스는 MySQL 서버의 복제 기능이 고가용성 요구에 충분하다는 것을 알 수 있습니다.

고객은 가용성을 늘리기 위해 SAN(Storage Area Network) 기술을 추가하거나 LINBIT에 서 네트워크를 통해 차단 장치 복제를 배포하는 Linux 커널 모듈인 DRBD(Distributed Replicated Block Device)를 추가할 수 있습니다. 고가용성의 "99.999%"가 데이터 노드 간의 동기화된 복제, 자동 페일오버, 클러스터 간의 복제를 수행하는 MySQL 클러스터에 의해 제공됩니다.

MySQL의 기능은 트래픽이 가장 높은 사이트에 최고 수준의 성능을 제공하며 제품 개발에도 쉽게 사용할 수 있습니다. 이러한 특징 덕분에 많은 사용자가 사용하고 있습니다.


비용이 들지 않습니다.

MySQL 사용자의 25%가 개발자입니다. 여러 중소기업의 경우, 특히 사업 초창기에는 개발자가 데이터베이스 관리자이기도 합니다. 기업이 성장하면서 용량과 수익이 커짐에 따라 주요 작업자들은 데이터베이스 관리 이외의 활동에 더 많은 시간을 사용하게 됩니다. 높은 안정성과 인력 비용이 점점 중요해집니다. 이 시점에서 MySQL Enterprise를 구입하면 모니터링 기능 및 기타 지원되는 기능을 활용할 수 있기 때문에 경제적으로 효율적입니다.

Enterprise 버전이 업데이트를 더 자주 수행하지만 커뮤니티 버전의 MySQL과 MySQL Enterprise 버전은 그 기능이 동일합니다. 이러한 기능에는 GUI 관리 도구, 다른 서버의 데이터를 마이그레이션할 수 있는 마이그레이션 툴킷, JDBC 및 ODBC용 커넥터 등이 있습니다. 커뮤니티에서 문서 및 사용자 포럼에도 무료로 액세스할 수 있습니다. MySQL을 사용하여 무료로 개발할 수 있으며, 개발 성능을 수평으로 확장하거나 유료 버전의 MySQL로 이전할 수도 있습니다.


결론

썬 마이크로시스템즈는 Java 기술용 오픈 소스 모델, Solaris 운영 체제 및 기타 속성을 포괄함으로써 오픈 소스 커뮤니티에 세계 최대의 기업 공헌자가 되었습니다. 이미 세계 최대의 오픈 소스 데이터베이스인 MySQL은 썬 소프트웨어 스택의 중요한 틈새를 채워 줍니다. MySQL은 썬의 사용 범위와 자원을 기반으로 더욱 널리 사용될 것입니다. 그렇게 되면 썬, MySQL, 개발자 커뮤니티, 대기업 및 중소 기업 고객 모두에게 혜택이 돌아갈 것입니다.


자세한 정보

신고

'Database' 카테고리의 다른 글

MSSQL에서 LPAD 함수 만들기  (0) 2011.07.08
치환  (0) 2011.05.20
MySQL 이 개발자들에게 주는 의미  (0) 2008.04.22
초보자를 위한「MySQL 백업·복구」강좌  (2) 2008.04.18
MySQL 속에 이미지 저장하기  (0) 2008.04.05
MySQL 명령어들  (0) 2008.03.13

출처 : http://www.zdnet.co.kr/builder/dev/db/0,39031604,39129480,00.htm

 

데이터베이스 관리자와 개발자들은 정기적인 데이터 백업의 중요성을 잘 알 것이다. 만약 디스크나 서버가 고장날 경우 백업을 미리 해 뒀는지 여부는 1년치 업무결과를 잃어버리느냐 아니면 몇시간만에 정상으로 복귀하느냐를 결정한다.

하지만 다행스럽게도 MySQL 사용자는 백업과 복구를 할 수 있는 간편하게 할 수 있는 내장 전용 툴을 사용할 수 있다. 이 툴을 이용하면 이기종 플랫폼으로 MySQL 데이터베이스를 간편하게 옮길 수 있으며, 또 다른 포맷으로 레코드를 저장하거나 불러오기도 가능하다.

데이터베이스 파일 복사
MySQL 데이터베이스 백업의 기본은 데이터베이스 파일 자체를 복사하는 것이다. MySQL은 이기종 플랫폼에서도 동일한 테이블 포맷을 사용하므로 MySQL 테이블과 인덱스 파일을 플랫폼을 넘나들며 쉽게 복사할 수 있다(물론 양쪽 플랫폼에 동일한 MySQL 버전이 설치돼 있어야 한다).

실제 복사하는 방법을 살펴 보자. 먼저 MySQL은 모든 데이터베이스를 data/ 디렉토리에 저장되며, 이는 다시 각 데이터베이스를 위한 서브디렉토리로 구분된다. 테이블과 테이블 인덱스는 파일로 저장되고, 파일명은 테이블명과 확장자를 합쳐서 붙여진다.

가장 쉬운 데이터베이스 복사 방법은 전체 data/ 디렉토리를 백업용 미디어에 복사하고, 언제든지 복구할 수 있도록 저장해 두는 것이다. 복사를 자동화하는 스크립트를 작성하는 것도 좋은 방법이다. 스크립트를 작성했으면 이를 crontab에 추가해 데이터베이스 업데이트 주기에 따라 매일 혹은 매주 단위로 실행되도록 설정할 수 있다.

한가지 주의할 것은 윈도우와 유닉스 플랫폼 사이에서 파일을 이동하는 경우다. 유닉스 파일명은 대소문자를 구분하지만 윈도우는 그렇지 않으므로, MySQL 테이블명에 대소문자가 뒤섞인 있으면 문제가 발생할 가능성이 있다(물론 테이블내의 데이터는 영향을 받지 않는다). 최악의 경우 데이블명과 코드를 수정하기 전까지 제대로 실행되지 않을 수도 있다. 따라서 최선의 방법은 테이블명을 소문자만으로 작성하는 것이다.

테이블을 텍스트 파일로 저장하기
파일복사 외에 MySQL에 기본 설치된 mysqldump 툴을 사용하는 방법도 있다. 이툴은 테이블과 데이터베이스는 물론 데이터베이스 전체를 텍스트 파일로 변환해 저장한다. 사용법도 매우 단순해, 내보낼 데이터베이스 이름을 인자로 다음과 같이 실행하면 된다.

$ mysqldump -u root -p secret stocksdb

mysqldump 툴은 MySQL 서버와 접속해 주어진 암호정보로 로그인한 후, 일련의 SQL 명령어를 이용해 테이블 구조와 데이터를 다음과 같은 형태로 출력한다.

--
-- Dumping data for table 'portfolio'
--
INSERT INTO portfolio VALUES (1,'DHDU',2589,77.56); INSERT INTO portfolio VALUES (2,'YHOO',3200,45.65); INSERT INTO portfolio VALUES (3,'WMT',100,53.29);

물론 이를 콘솔에 출력하는 것은 그리 유용하지 못하다. 여러분이 원하는 것은 이를 파일로 저장해 백업하고나 복구하는데 이용할 수 있어야 한다. 이를 위해서는 출력 형태를 파일로 다시 정해주기만 하면 된다.

$ mysqldump -u root -p secret stocksdb > stocksdb.sql [/code]

만약 특정 테이블만 백업하려면 데이터베이스 이름 뒤에 테이블 이름을 붙이면 된다. 다음은 stocksdb 데이터베이스에서 users 테이블에 저장된 구조와 데이터만을 추출하는 구문이다.

$ mysqldump -u root -p secret stocksdb users > users.sql

또한 시스템 내의 모든 데이터베이스를 저장하려면 다음과 같이 --all-databases 옵션을 이용한다. --all-databases 옵션을 사용하면, mysqldump 명령 결과에 각 데이터베이스를 초기화하는 CREATE DATABASE문이 포함된다. 이렇게 하면 데이터베이스 전체를 한 번에 복구하기가 더 쉬워진다.

$ mysqldump -u root -p secret --all-databases > backup.sql

테이블 구조만 백업하고자 하면 다음과 같이 --no-data 옵션을 사용하면 된다. 이 옵션은 애플리케이션을 처음 설치해서, 레코드가 없는 테이블의 복사본을 만들어야 하는 경우 대단히 유용하다.

$ mysqldump -u root -p secret --no-data stocksdb > stocksdb.sql

물론 테이블 구조는 제외하고 데이터만 백업하는 경우는 다음과 같은 구문을 이용하면 된다.

$ mysqldump -u root -p secret --no-create-info stocksdb > stocksdb.sql

텍스트 백업 파일로 MySQL 테이블 복구하기
지금까지 데이터베이스와 테이블 백업 방법을 살펴 봤다. 그러나 이것은 문제 해결의 절반에 불과하다. 나머지 절반은 실제 문제가 발생했을 때 이를 복구하는 방법을 아는 것이다. 다행스럽게도 MySQL은 복구과정도 손쉽다. mysqldump 명령의 결과물은 일련의 SQL 명령어로 구성돼 있기 때문에, MySQL 클라이언트가 간단하게 기존 데이터베이스 구조와 컨텐트를 재구축할 수 있다.
모든 데이터베이스를 mysqldump--all-databases 명령을 이용해 backup.sql 파일에 복사했다고 가정하면 다음과 같은 구문으로 이를 복구할 수 있다.

$ mysql -u root -p secret < backup.sql

--all-databases 옵션을 사용하지 않고 일부 테이블이나 데이터베이스를 선택적으로 백업한 경우는 MySQL 명령 실행시 데이터베이스의 위치를 알려주는 -D 옵션을 추가한다. 예를 들어 stock2 데이터베이스의 stocksdb.sql 파일에 저장된 테이블을 복구하는 구문이라면 다음과 같다.

$ mysql -u root -p secret -D stocks2 < stocksdb.sql

다른 포맷으로 내보내고 읽어들이기
MySQL 데이터베이스는 SQL 포맷 외에도 다양한 형식으로 저장할 수 있다. mysqldump 툴은 CSV나 사용자가 원하는 구분문자(delimiter)를 이용해 다양한 포맷으로 레코드를 저장할 수 있는데, 이를 위해서는 mysqldump 명령어에 --fields-terminated-by 인자만 추가하면 된다.

$ mysqldump -u root -p secret --no-create-info --tab=/tmp --fields-terminated-by=',' stocksdb

이와 같이 실행하면 mysqldump는 stocksdb 테이블에 있는 레코드의 각 필드를 쉼표로 분리해 /tmp 디렉토리에 저장한다. 별도의 라인 터미네이터를 사용하려면 --lines-terminated-by 인자를 사용해 특정 구분문자로 라인을 표시할 수 있다.
다른 데이터 포맷을 불러들이는 것도 가능하다. 예를 들어 다음과 같이 탭으로 분리한 테이블 레코드가 있다고 가정하자.

1 DHDU 2589 77.56
2 YHOO 3200 45.65
3 WMT 100 53.29

이 때 mysqlimport 툴을 이용하면 MySQL 데이터베이스 파일로 읽어들일 수 있다. 단 소스 파일의 베이스 이름이 데이터가 삽입될 위치를 결정한다는 점에 유의하자.

$ mysqlimport -u root -p secret --fields-terminated-by='t' test /tmp/portfolio.txt

백업을 할때 항상 두 카피 이상을 유지하도록 한다(하나는 다른 위치에). 또한 MySQL 매뉴얼 내용 가운데 다양한 데이터를 내보내고 불러들이는 방법을 참고해 여러분의 환경에 응용할 수 있는 가장 좋은 방법을 찾길 바란다.
무엇보다 필자는 여러분이 문제가 발생한 데이터베이스를 복구해야 하는 상황에 놓이지 않기를 진심으로 바란다. 그러나 효율적으로 복구할 수 있는 툴이 있고, 이를 이용하면 최악의 경우에도 사용자의 불편을 최소화할 수 있다는 사실만으로도 여러분은 좀더 쉽게 잠을 청할 수 있을 것이다. @

신고

'Database' 카테고리의 다른 글

치환  (0) 2011.05.20
MySQL 이 개발자들에게 주는 의미  (0) 2008.04.22
초보자를 위한「MySQL 백업·복구」강좌  (2) 2008.04.18
MySQL 속에 이미지 저장하기  (0) 2008.04.05
MySQL 명령어들  (0) 2008.03.13
MS SQL .ldf 파일 사이즈 조절하기  (0) 2008.03.12
  1. DyNast 2008.04.21 22:28 신고

    요즘 한창 MySql공부하고잇는데 좋은정보 얻어가요

    자주자주들릴깨요^^

  2. Digital Angel Master 2008.04.22 11:30 신고

    이왕이면 같이 공부합시다..

출처 : http://libphp.com/main/data/view.php?no=3203&back=L21haW4vZGF0YS9zZWFyY2gucGhwP2NhdDE9NyZmaXJzdD0xJm9yZGVyPSZzY19hbmQ9b3Imc2NfYmFuPSZzY193b3JkPQ%3D%3D






MySQL 속에 이미지 저장하기



원본 사이트 : http://webdev.berber.co.il/articles/MySqlandImages.php3
이글은 위의 페이지에 있는 글을 번역해 본 것으로 약간의 오역이 있을수도 있습니다.
또한 의미를 파악하는데는 문제가 없겠지만, 원본을 참조해 보시기를 권해드리고 싶습니다.

때때로 이미지를 데이터베이스에 저장하는 것이 파일로 저장하는 것보다 더 편리할때가 있습니다.
MySQL과 PHP는 이런 일을 매우 쉽게 만들어줍니다.
이 글에서 저는 MySQL 데이터베이스에 이미지를 저장하는 방법과 나중에 그것을 드러내 보이는 방법을 설명하고자 합니다.

데이터베이스 설정하기.
~~~~~~~~~~~~~~~~~~~~~~
어떤 보통의 텍스트나 정수영역과 이미지가 저장되어지기 위해 필요한 영역 사이의 차이점은 그 영역속에 지니게 되는데 필요한 데이터의 양입니다.
MySQL은 커다란 양의 데이터를 지니기위해 특별한 영역을 사용합니다.
이런 영역들은 BLOBs(BLOB)라고 알려져 있습니다.

MySQL 사이트에서는 BLOB를 아래와 같이 정의하고 있습니다.

BLOB는 데이터의 가변적인 양을 지니고 있을 수 있는 거대한 바이너리 객체이다.

TINYBLOB, BLOB, MEDIUMBLOB와 LONGBLOB의 네가지 BLOB의 형태는 단지 그들이 지니고 있을 수 있는 값의 최대 길이에서만 다를뿐입니다.

MySQL BLOB에 대한 더 많은 정보는 아래에서 확인해 보십시오.

http://www.mysql.com/Manual_chapter/manual_Reference.html#BLOB

이미지를 지니게 될 기본적인 테이블을 생성하기 위해 다음의 구문을 사용하십시오.

CREATE TABLE Images( PicNum int NOT NULL AUTO_INCREMENT PRIMARY KEY, Image BLOB );

업로드 스크립트 설정하기.
~~~~~~~~~~~~~~~~~~~~~~~~~
파일을 업로드하기 위한 예는 Berber(29/06/99)의 File Uploading(http://webdev.berber.co.il/articles/FileUpload.php3)에서 볼 수 있을 것입니다.

지금 우리에게 필요한 것은 MySQL에 파일을 넣고 그것을 얻는 PHP 스크립트입니다.

다음의 스크립트는 단지 그것 뿐입니다. 스크립트에서 저는 파일필드의 이름을 Picture인 것으로 가정하고 있습니다.

If($Picture != "none")
{
$PSize = filesize($Picture);
$mysqlPicture = addslashes(fread(fopen($Picture, "r"), $PSize));
mysql_connect($host, $username, $password) or die("SQL 서버에 접속할 수 없습니다.");
@mysql_select_db($db) or die("데이터베이스를 선택할 수 없습니다.")
mysql_query("INSERT INTO Image (Image) VALUES '($mysqlPicture)'") or die("쿼리를 수행할 수 없습니다.");
}
else
{
echo "어떤 그림도 업로드하지 않으셨습니다.";
}

이것이 데이터베이스에 이미지를 넣기 위해 필요한 모든 것입니다.
어떤경우에는 여러분이 MySQL에 이미지를 넣으려고 할 때 에러가 발생할 지도모르는데, 그런 경우에는 여러분의 MySQL 버전이 허락하는 최대 패킷의 크기를 확인해 보는 것이 좋습니다.
그런 경우는 매우 적을 테지만, 여러분은 MySQL의 에러 로그내에서 이 에러에 대한 것을 볼 수 있습니다.

위의 파일에서 우리가 한 것은,
1. 만약, If($Picture != "none")으로 파일이 업로드되었는지 확인하고,
2. MySQL내에서 에러들을 피하기 위해 그림의 스트림에 addslashes()를 주고,
3. MySQL에 접속하여 데이터베이스를 선택하고 이미지를 넣도록 한 것입니다.

이미지 나타내기.
~~~~~~~~~~~~~~~~
이제 우리는 이미지를 데이터베이스에 넣는 법을 알았습니다. 우리는 그것들을 얻고 보이는 방법을 만들어 낼 필요가 있습니다. 이것은 이미지를 넣는 것보다 더 복잡하지만, 만약 여러분이 이 단계를 따른다면 이것을 만들어내고 실행하는데 시간이 걸리지 않을 것입니다.

그림을 보이는 데에는 보내어질 헤더가 필요하기 때문에 우리는 단지 하나의 그림외에는 더 볼 수밖에 없는 그런 상태에 봉착하게 될 수도 있습니다.

한번 헤더를 보내게 되면 더 이상의 헤더를 보낼 수 없게 됩니다.

이것이 트릭이 필요한 부분입니다.
시스템을 약간 속이기 위해 우리는 두개의 파일을 사용합니다.
첫번째 파일은? 우리가 보이고자 하는 그림이 어디에 있는지를 알고 있는 HTML 템플릿 파일입니다.
이것은 우리가 보이고자 하는 하는 < IMG > 태그를 포함하고 있는 HTML 파일로서, 일반적인 PHP 파일입니다.

두번째 파일은 < IMG > 태그의 SRC 속성에 직접 데이터베이스로 부터 실제 파일스트림을 제공하기 위해 불리워지는 파일입니다.

첫번째 형태는 보이는 것처럼 매우 간단한 스크립트입니다.

mysql_connect($host, $username, $password) or die("SQL 서버에 접속할 수 없습니다.");
@mysql_select_db($db) or die("데이터베이스를 선택할 수 없습니다.");
$result=mysql_query("select * from Images") or die("쿼리를 실행할 수 없습니다.");
while($row=mysql_fetch_object($result))
{
echo " SRC="SecondType.php3?PicNum=$row->PicNum">";
}

그 HTML이 보여지는 동안, 두번째의 SecondType.php3 파일은 우리가 보이고자 하는 각 이미지를 위해 불리워집니다. 이 스크립트는 우리가 이미지를 가져오고 보이는 것을 가능케 하는 Picture ID(PicNum)을 가지고서 불리워 집니다.

SecondType.php3 파일은 이와 같습니다.

$result=mysql_query("select * from Images where PicNum=$PicNum") or die("쿼리를 실행할 수 없습니다.");
$row=mysql_fetch_object($result);
Header("Content-type:image/gif");
echo $row->Image;

이것이 이미지와 MySQL 뒤의 모든 이론입니다. 이 예의 스크립트들은 기본적인 것입니다. 이제 여러분은 썸네일(손톱조각그림)을 포함시키고, 다양한 위치에 이미지를 설정할 수 있게 향상시킬 수 있습니다. ALT 영역을 지닐 수 있도록 데이터베이스를 향상시킬 수도 있습니다. 이미지를 데이터베이스에 넣기 전에 그것의 폭과 높이를 확인하고, 그것의 테이블 등등의 것 역시 갖고 계십시요.
신고
출처 : http://dualpage.muz.ro/

--from http://blogbridge.naver.com/post/postXMLList.jsp?blogId=amhengersa

# 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이라는 사용자를 생성

create database kang;

grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on kang.* to kang@localhost identified by 'kang';

grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 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 on kang.* to kang@localhost identified by 'kang';
Query OK, 0 rows affected (0.00 sec)

mysql> grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP 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가 이루어진다.
신고
자료 출처 : http://cafe.naver.com/servermaster.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=1496

mssql은 복구를 위해 실제데이터(확장자 mdf)와 트랜잭션로그데이터(확장자 ldf)를 기록합니다.

mdf는 말그대로 디비에 저장되는 실데이터를 말하며, ldf는 트랜잭션로그, 즉 이러한 데이터를 이용한 읽기, 수정, 삭제등의 모든 로그를 기록합니다.

그 래서 특정데이터에 대해 입출력이나 업데이트가 반복되는 경우 비정상적으로 ldf파일이 커지는 경우가 발생하게 됩니다. 이러한 일은 정상적인 억세스로 일어날 수도 있으나 잘못된 프로그래밍 또는 오류로 인한 무한루프에 의해 급증하는 경우도 발생하게 됩니다.

하드디스크가 매우 여유롭다면 몰라도 이런일로 하디디스크 풀이 나는 장애가 발생된다면 정말 난감해집니다.

사실 ldf파일이 없이도 mdf만으로 디비를 복구하는 방법도 있습니다만 제대로? MS의 정책에 따라 완벽한 복구를 위해서 ldf를 버리지 말고 용량을 잘 조절해서 사용하는 방법을 말씀 드리겠습니다.

일단 EM환경보다는 제가 사용하기 편리한 쿼리분석기에서의 작업을 기준으로 설명 드리겠습니다.

일단 쿼리분석기를 실행합니다.

### 트랜잭션로그를 백업할 디비를 지정하여 줍니다.

use testdb? -- 저는 testDB를 지정한다고 가정하였습니다.

### 로그파일의 정보를 확인합니다.

dbcc loginfo

### 현재 지정된 디비가 사용하는 mdf 및 ldf파일의 경로, 이름 및 크기를 확인합니다.

exec sp_helpfile

### 위에서 정해준 디비의 로그를 백업해 줍니다.

backup Log testdb to disk='d:dbbackuptemptestdb.bak'

go

### 트랜잭션 로그파일을 최소의 단위로 축소합니다.

backup log testdb with truncate_only

### 트랜잭션 로그파일을 삭제합니다.

backup log testdb with no_log

### 트랜잭션 로그파일을 10메가로 생성합니다.

dbcc shrinkfile (testdb_log, 10)

### mdf와 ldf파일이 제대로 잘 리사이징 되었는지 확인합니다.

exec sp_helpfile

모든 작업이 잘 마무리 되었다면, 이제는 갑작스런 트랜잭션로그의 증가로 문제가 되는 것을 방지하기 위해 트랜잭션로그파일의 최대크기를 지정해놓는 방법도 좋습니다.

alter database testdb

modify file ( name = testdb_log, maxsize = 200 mb )

go

위 의 과정을 진행하시면 트랜잭션로그는 위에서 지정한데로 200메가를 한계치로 생성 삭제 됩니다. 트랜잭션로그의 용량은 데이터의 중요도 및 규모에 따라 정책적으로 유지하셔야 하는 부분입니다. 200메가는 예제로 적어놓은 사이즈입니다.

또한 위의 과정 중 축소와 삭제는 둘 중 원하시는 방법을 선택적으로 사용하시면 됩니다.

모쪼록 성공하시길 바랍니다.
신고

<< MySQL 백업 & 복구하기 >>

History :
2007/09/28 : MySQL 입력시 한글 깨지는것 해결 방법
2001/10/30 : 문서 최초 작성
##############################################################################

백업을 하기 위해서는 mysqldump 명령어를 사용합니다.
그리고, 복구를 하기 위해서는 mysql 명령어를 사용합니다.
이 명령어의 path 를 잡아주세요.!!

형식은 다음과 같습니다.
(참고: [] 는 선택 항목입니다.)

user> mysqldump [-h 호스트] [-u 사용자] [-p] DB명 [table명]> 백업파일
user> mysql [-h 호스트] [-u 사용자] [-p] DB명 < 백업파일

예제 1) root 라는 사용자가 mydb 를 백업하는 명령입니다.
이렇게 하시면 mydb.sql 파일에 sql문으로 저장이 됩니다.
user> mysqldump -u root -p mydb > mydb.sql

예제 2-1) 백업된 mydb.sql 을 newdb 에 입력하는 방법
user> mysql -u dbakorea -p newdb < mydb.sql

예제 2-2) 한글이 깨지는 경우 문자셋을 설정하여 입력하는 방법
1. 테이블 문자셋 확인
2. 이력 파일의 문자셋 확인
3. 문자셋 지정 입력

user> mysql -u dbakorea -p --default-character-set=euckr newdb < mydb.sql

##############################################################################

<< 형화가 사용하는 프로그램 >>

###################
# 사용하기전에 할일
###################

1. 작업을할 디렉토리(dir/)를 만들고 cgi 파일을 만든 디렉토리에 복사한다.
앞으로 작업디렉토리를 dir 이라 하겠다.
2. dir 밑에 temp 디렉토리를 만들고 권한을 700 으로 준다.

user> mkdir -p dir/temp
user> chmod 700 temp

###########
# 백업하기
###########

user> chmod 700 mysqldump.cgi
user> perl mysqldump.cgi

db_list 파일이 생성 되었는지 확인한다.

################
# DB 생성하기
################
dbinput_list 파일은 mysqldump.cgi 에 의해 만들어진 db_list 파일을
복사해서 사용하면 된다. 생성이 필요없는 DB는 지우면 된다.
(주의: 처음 3줄은 삭제를 하지 말아라.)

user> chmod 700 mysqldbcreate.cgi
user> perl mysqldbcreate.cgi

################
# DB에 입력하기
################
dbinput_list 파일에 입력할 DB 를 추가한다.
(주의: mysql DB 는 빼는게 좋다.)

user> chmod 700 mysqlinput.cgi
user> perl mysqlinput.cgi

####################
# 사용되는 파일들
####################

======================== mysqldump.cgi ===============================
#!/usr/bin/perl

# Linux에 있는 모든 DB를 DB_name.dump 파일에 dump한다.
# 1999,12,2

# $mysql_path = "/usr/local/bin"; # FreeBSD
$mysql_path = "/usr/local/mysql/bin";
$passwd = ""; # root 비번을 입력한다.
$host = ""; # 접속할 호스트 (외부에 DB가 있는 경우만 사용한다.)

if( $passwd ne '){
$passwd = " -p$passwd";
}
if( $host ne '){
$host = " -h $host";
}

`$mysql_path/mysqlshow -uroot $passwd $host > db_list`;

open( IN, 'db_list' );
$start = 0;
while( <IN> ) {
if( $start ne 3 ) {
$start ++;
# print "start:$_n";
next;
}
elsif ( $_ !~ / / ) { # Blank
next;
}
chop();
( $a, $db, $c ) = split( /|/, $_);
$db =~ s/ //g;

# 각 DB의 dump파일을 생셩한다.
`$mysql_path/mysqldump -uroot $passwd $host $db > temp/$db.dump`;
print "$db Dumpedn";
}
close( IN );

======================== /mysqldump.cgi ===============================

======================== mysqldbcreate.cgi ===============================
#!/usr/bin/perl

# dbinput_list에 있는 모든 DB를 생성한다.
# 2000. 4. 5

# dbinput_list 파일은 mysqldump.cgi 에 의해 만들어진 db_list 파일을
# 복사해서 사용하면 된다. 생성이 필요없는 DB는 지우면 된다.
# (주의: 처음 3줄은 삭제를 하지 말아라.)

# $mysql_path = "/usr/local/bin"; # FreeBSD
$mysql_path = "/usr/local/mysql/bin";
$passwd = ""; # root 비번을 입력한다.
$host = ""; # 접속할 호스트 (외부에 DB가 있는 경우만 사용한다.)

if( $passwd ne '){
$passwd = " -p$passwd";
}
if( $host ne '){
$host = " -h $host";
}

open( IN, 'dbinput_list' );
$start = 0;
while( <IN> ) {
if( $start ne 3 ) {
$start ++;
next;
}
elsif ( $_ !~ / / ) { # Blank
next;
}
chop();
( $a, $db, $c ) = split( /|/, $_);
$db =~ s/ //g;

# 각 DB를 생성한다.
`$mysql_path/mysqladmin -uroot $passwd $host create $db`;
print "$db Createdn";
}
close( IN );

# 설정을 다시 읽는다.
`$mysql_path/msyqladmin -uroot $passwd $host reload`

======================== /mysqldbcreate.cgi ===============================

======================== mysqlinput.cgi ===============================
#!/usr/bin/perl

# dbinput_list에 있는 모든 DB를 각 DB에 입력한다.
# 2000. 4. 3

# $mysql_path = "/usr/local/bin"; # FreeBSD
$mysql_path = "/usr/local/mysql/bin";
$passwd = ""; # root 비번을 입력한다.
$host = ""; # 접속할 호스트 (외부에 DB가 있는 경우만 사용한다.)

if( $passwd ne '){
$passwd = " -p$passwd";
}
if( $host ne '){
$host = " -h $host";
}

open( IN, 'dbinput_list' );
$start = 0;
while( <IN> ) {
if( $start ne 3 ) {
$start ++;
next;
}
elsif ( $_ !~ / / ) { # Blank
next;
}
chop();
( $a, $db, $c ) = split( /|/, $_);
$db =~ s/ //g;

# 각 DB에 dump파일을 입력한다.
`$mysql_path/mysql -uroot $passwd $host $db < temp/$db.dump`;
print "$db Insertedn";
}
close( IN );

======================== /mysqlinput.cgi ===============================
신고
PostgreSQL + unixODBC on console HOWTO

쓴 사람 : 임 유 빈
쓴 사람 집 :
http://www.dalmuri.net/~purewell
처음 쓴 날 : 2003년 2월 14일


이 문서는 GPL 사용권을 따릅니다.

차례
0. 누구를 위한 것인가
1. PostgreSQL 설치하기
2. unixODBC 설치하기
3. 맺음말


0. 누구를 위한 것인가
이 문서는 PostgreSQL과 unixODBC를 XWindow를 쓰지 않고 연결 및 설정하기 위한 일종의 삽질 기록이다. (보라~ 2월 14일에 애인이랑 놀러가지 않고 검은 화면에 하얀 글씨만 봤다!)


이 문서는 PostgreSQL을 예로 들었지만, ODBC를 지원하는 모든 DBMS를 쓸 수 있다.

약 간의 사족을 붙이자면 PostgreSQL은 ORDBMS로 성장한 Database Management System이다. PostgreSQL은 소스가 공개되어 있으며, BSD 라이센스를 따르고 있다. unixODBC는 M$가 지원하고 있는 ODBC(Open Data Base Connectivity)를 다른 OS에서도 쓸 수 있도록 삽질한 것이다. 이 역시 소스가 공개되어 있으며 LGPL을 따르고 있다. ODBC는 DBMS에 관계없이 동일한 Interface를 제공하기 때문에 C/C++/Python 등의 개발자들이 OS에 맞춰 소스를 고칠 필요가 없으며 추후에 DBMS를 다른 상품으로 교체할 때도 프로그램을 재컴파일할 필요가 없는 장점을 제공한다.

1. PostgreSQL 설치하기
본인은 RedHat 8.0을 썼으며, 당연히 컴파일하기 귀차니즘 압박으로 RPM 버전을 깔아쓴다. 이미 많은 PostgreSQL 소스로 설치하는 문서가 인터넷에 널려 있으니 그런 것은 알아서 찾아 쓰기 바란다. Smile


http://www.postgresql.org : PostgreSQL 공식 홈페이지 (소스를 구할 수 있다)
http://database.sarang.net : PostgreSQL 소스 설치 문서가 잘 정리 되어 있다.

설치를 위해 사용한 RPM은 다음과 같다.

postgresql-server-7.2.3-5.80.rpm : 인터넷 서버로 동작하게 해주는 도구들
postgresql-libs-7.2.3-5.80.rpm : 클라이언트용 공유 라이브러리
postgresql-devel-7.2.3-5.80.rpm : 개발을 위한 라이브러리 및 헤더 파일
postgresql-odbc-7.2.3-5.80.rpm : ODBC 드라이버
postgresql-7.2.3-5.80.rpm : 핵심 파일들


RPM을 설치한다. 물론 현재 권한은 root이다.







코드:
$ rpm -Uvh postgresql*.rpm

RPM의 스크립트가 알아서 기본 관리 유저(postgres)와 데이터베이스(template1)을 만들어줄 것이다.

이제 인터넷 서버가 작동하도록 PostgreSQL의 환경설정을 바꾸자. 아쉽게도 /etc에서 죽어라고 postgresql.conf를 찾아봤자 찾을 수 없다. Sad RPM으로 설치된 PostgreSQL의 설정파일은 /var/lib/pgsql/data 에 존재한다.







코드:
$ vi /var/lib/pgsql/data/postgresql.conf

여기서 tcpip_socket 값을 찾자. 아마 그 값은 false로 설정되어 있을 것이다. 이것을 true로 바꾸자. (없다면 추가하라!)

tcpip_socket=true

인증 방식도 바꿔야한다. 같은 위치에 있는 pg_hba.conf 파일을 열어보면 주석이 몽창 있을 것이다. 맨 아래에 다음의 줄을 넣어주자.







코드:
host all 0.0.0.0 0.0.0.0 password

(pg_hba.conf 파일에 대한 설정 방법은 http://database.sarang.net 에서 찾도록 하자 Very Happy)

이제 서버를 구동해보자.







코드:
$ /etc/rc.d/init.d/postgresql restart

구동에 문제가 생겼다면 인터넷에서 해결책을 찾길 바란다. 구동에 문제가 없다면 포트가 열렸는지 확인한다. 기본 포트 값은 5432이다.







코드:
$ netstat -a
Active Internet connections (servers and established)
Proto Recv-Q Send-Q Local Address Foreign Address State
(중략)
tcp 0 0 *:postgres *:* LISTEN
(하략)

별 문제 없다면 위와 같이 나올 것이다.

2. unixODBC 설치하기
역시 RedHat 8.0에서 쓴 것이며, 시디롬 뒤져보면 있다. 현재 나와 있는 것은 Update한 패키지이므로 버전 번호가 다를 수 있지만 큰 문제는 아니다.


http://www.unixodbc.org : UNIXODBC 소스를 얻을 수 있는 웹사이트

소 스를 컴파일한다면 말리지 않겠다. GNU의 Autotools로 ./configure;make;make install로 간단히 설치할 수 있으니 말이다. 다만 설정파일의 위치가 바뀌고 실행 파일 위치가 바뀌므로 ./configure 할 때, 알맞은 옵션을 주기 바란다.

본인은 아래의 RPM을 설치했다.

unixODBC-2.2.2-3.rpm : unixODBC 핵심 파일
unixODBC-devel-2.2.2-3.rpm : C/C++ 따위로 개발하기 위한 헤더/라이브러리


역시 root 권한으로 RPM을 설치한다.

$ rpm -Uvh unixODBC*.rpm

아마 unixODBC에서 무슨 QT 패키지를 요구할 것이다. 환경설정을 위한 실행파일이 X-Window용으로 개발됐기 때문이다. Sad 뒤에 --nodeps를 집어넣어서 의존성을 무시하고 설치한다. Smile

$ rpm -Uvh unixODBC*.rpm --nodeps

/etc에 두개의 ODBC 환경설정 파일이 생성된다. 일단 드라이버를 인식시켜야 한다. 만약 RPM 사용자라면 설치와 동시에 odbcinst.ini 파일에 PostgreSQL용 드라이버가 설치되었을 것이다.

아래는 본인의 odbcinst.ini 이다.







코드:
---------------------------------------- odbcinst.ini
[PostgreSQL]
Description = ODBC for PostgreSQL
Driver = /usr/lib/libodbcpsql.so
Setup = /usr/lib/libodbcpsqlS.so
FileUsage = 1
----------------------------------------

위에서 Driver는 ODBC와 DBMS를 연결해주는 파일이다. RPM으로 postgresql-odbc.*.rpm을 깔았다면 위의 경로에 파일이 있을 것이다. Smile

Setup은 GUI환경에서 DBMS환경설정을 위한 것이다. 슬프게도 이 문서는 X-Window가 깔려있지 않은 환경의 사용자를 위한 생쑈 하우투이기에 안 써도 무관하다. 만약 RPM으로 설치했다면 역시 파일의 위치는 위와 같다.

ODBC엔 그게 두가지 접근방식을 제공한다. User DSN과 System DSN이다. Windows에 보면 File DSN가 존재하는데 결국 접속하는 방법을 적어놓은 Text 파일을 로드하는 것이다.

Windows에서 regedit.exe를 실행해서 HKEY_LOCAL_MACHINESoftwareODBCODBCINST.INI라는 키가 있다. 왠지 어디서 많이 본 것 같지 않은가? Smile 위에서 우리가 드라이버를 설정한 /etc/odbcinst.ini 파일의 내용이 거기에 들어 있다. 또한 ODBC.INI 키가 존재한다. Smile 뭔가 감이 오기 시작하는가? 여기에 System DSN 설정 내용이 담기게 된다. /etc/odbc.ini처럼 말이다. (레지스트리는 Windows 3.1 시절에 설정파일을 담아놓던 ini 파일을 시스템 내부로 옮기는 것을 모토로 하고 있기 때문이다~)

만약 레지스트리에 PostgreSQL로 세팅된 것이 있다면 그것을 Export해서 알맞게 수정해서 /etc/odbc.ini에 옮겨 넣으면 System DSN이 생성되며, ~/.odbc.ini에 옮겨 넣으면 User DSN이 생성된다.

아래는 본인의 odbc.ini 파일이다.







코드:
---------------------------------------- odbc.ini
[ODBC Data Sources]
MyDBMS = My Database Management System[MyDBMS]
Driver=/usr/lib/libodbcpsql.so
Fetch=100
CommLog=0
Debug=0
Optimizer=1
Ksqo=1
UniqueIndex=1
UseDeclareFetch=0
UnknownSizes=0
TextAsLongVarchar=1
UnknownsAsLongVarchar=0
BoolsAsChar=1
Parse=0
CancelAsFreeStmt=0
MaxVarcharSize=254
MaxLongVarcharSize=8190
ExtraSysTablePrefixes=dd_;
Description=My Database Management System
Database=template1
Servername=localhost
Port=5432
Username=
Password=
ReadOnly=0
ShowOidColumn=0
FakeOidIndex=0
RowVersioning=0
ShowSystemTables=0
Protocol=6.4
ConnSettings=
DisallowPremature=0
UpdatableCursors=1
LFConversion=1
TrueIsMinus1=0
BI=0
----------------------------------------

길어지니까 왠지 모르게 뒷골이 땡겨온다. Sad 사실 저 위의 값이 모두 다 쓰일 필요는 없다. 대부분 Default 값이기 때문이다.

먼저 [ODBC Data Sources] 영역이 필요하다. ODBC는 제일 먼저 이 부분을 검색하며 이 부분에서 어떠한 DSN이 있는지 알아낸다. 형식은 "[DSN 이름] = [설명]"이다.

다음에 [ODBC Data Sources]에서 정의한 DSN 이름으로 속성을 나열한다. Very Happy 여기에 쓰이는 속성 중에서 중요한 것만 언급하겠다. (쓰지 않은 값은 모두 기본값 처리된다)

Driver : 여기에 쓰일 드라이버 파일을 명시한다. odbcinst.ini 파일에 명시된 파일명을 쓴다.
Description : 이 DSN이 무엇인가를 간단히 설명하는 문자열이다.
Database : 쓰일 Database 이름이다. 처음 만들었다면 template1 밖에 없을 것이고, 만약 만든 것이 있다면 기본적으로 찾을 Database이름을 적자.
ServerName : 혹은 Host로도 쓰이는 것으로 서버의 주소이다. IP든 도메인 네임이든 모든 것을 다 쓸 수 있다.
Port : DBMS와 대화할 포트번호를 적으면 된다. 안 적으면 PostgreSQL의 기본 포트인 5432가 될 것이다.
Username : 기본적으로 접속할 때 쓰일 사용자 이름이다.
Password : 기복적으로 접속할 때 쓰일 암호이다. 될 수 있으면 적지 말자.


자, 이제 제대로 돌아가는지 실험을 해보자. unixODBC의 기본 테스터기로 odbctest가 존재한다.







코드:
$ odbctest
odbctest: error while loading shared libraries: libqt-mt.so.3: cannot open shared object file: No such file or directory

아뿔싸... GUI환경에서 돌아가는 것이었다. Sad 그럼 isql을 사용해보자.







코드:
$ isql MyDBMS
+---------------------------------------+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+---------------------------------------+
SQL>_

저렇게 뜨면 성공한 것이다.

3. 맺음말
본 인이 unixODBC를 쓰게 된 것은, Linux용으로 서버를 개발하고 있는데 어떤 DBMS가 좋을지 판단이 안 서서였다. 그때 당시 Oracle 설치할 줄도 몰랐고, 미래를 위해 잘 설계를 해야했고 프로젝트 기간 안에 끝내야했기 때문에 DBMS의 선택을 추후로 미룰 수 있도록 ODBC를 선택하게 됐다. 물론 JDBC도 있지만 설치 및 C/C++과의 연동에 대한 문서를 찾을 수 없어서 ODBC를 선택하게 됐다. 일단 PostgreSQL로 ANSI표준안에 맞게 테이블을 생성해서 프로젝트를 진행하였다. 지금도 별일 없기 때문에 PostgreSQL로 돌리고 있으나, 만약 DBMS를 바꾸거나 확장하는 일이 있더라도 소스를 고칠 필요도, 재컴파일 할 필요도 없기 때문에 Smile 좋은 것 같다.


본 인도 리눅스나 DBMS 등등의 상세한 부분은 모른다. 다만 프로젝트를 하면서 unixODBC를 X-Window가 없는 환경에서 써봤으며, 잘 실행되길래 문서화해서 무릇 같은 삽질을 하는 리돌이, 리순이들에게 약간의 도움이 되길 바랄 뿐이다
신고

웹 개발시 SQL Injection 취약점 주의!

[입력날짜: 2006-04-13]

사용자 입력값이 SQL injection 발생시키지 않도록 수정

다수 사이트, SQL Injection 취약점 공격에 무방비 상태


웹 취약점을 이용한 공격이 식을 줄 모르고 있다. 공격의 목적은 게임정보 탈취와 궁극적으로는 이를 이용한 게임아이템 거래로 돈을 벌기 위한 것이다.


정보통신부와 KISA는 이에 대한 대책으로 중국에서 들어오는 IP들을 차단하고 있고 자금 여력이 없는 중소기업의 웹사이트 관리자에게 공개 웹방화벽 구축을 권유하고 있으며 개인 PC의 보안패치율을 높이기 위해 각종 캠패인을 벌이고 있다.


KISA 에서 운영하는 ‘보호나라’에서는 대국민 서비스로 원격 PC점검을 해주고 있고 대기업을 중심으로 보안솔루션 도입과 시스템 구축이 한창이다. 하지만 이러한 노력에도 불구하고 해킹 관제 서비스를 실시하고 있는 업체에서는 하루에도 2백~3백건의 해킹이 이루어지고 있고 이를 통한 악성코드 유포가 지속적으로 이루어지고 있다고 지적하고 있다.


KISA 관계자는 “지난해 정부기관 사이트와 주요 웹사이트를 대상으로 대대적인 웹사이트 모의 해킹을 실시한 결과 대부분의 사이트들이 SQL Injection 취약점 공격에 맥없이 뚫렸다”며 “SQL Injection 취약점 공격에 대한 대비책을 개발자들이 기본적으로 알고 있어야한다”고 강조했다.


< 보안뉴스>는 얼마전부터 웹개발자들이 기술,정신적으로 보안강화에?더욱 노력해야 한다는?기사와 관련정보를 제공해 오고 있다. 이번에는?SQL Injection 취약점에 대해 알아보자.?자료 제공은 KISA 해킹대응팀이다.


<기획3> 악의적인 명령어 주입 공격(SQL Injection)


현 재 대부분의 웹 사이트들은 사용자로부터 입력받은 값을 이용해 데이터 베이스 접근을 위한 SQL Query를 만들고 있다. 사용자 로그인 과정을 예로 들면, 사용자가 유효한 계정과 패스워드를 입력했는지 확인하기 위해 사용자 계정과 패스워드에 관한 SQL Query문을 만든다. 이때 SQL injection 기법을 통해서 정상적인 SQL query를 변조할 수 있도록 조작된 사용자 이름과 패스워드를 보내 정상적인 동작을 방해할 수 있다. 이러한 비정상적인 SQL Query를 이용해 다음과 같은 공격이 가능하다.


-사용자 인증을 비정상적으로 통과할 수 있다.


-데이터베이스에 저장된 데이터를 임의로 열람할 수 있다.


-데이터베이스의 시스템 명령을 이용하여 시스템 조작이 가능하다.


이러한 취약점을 ‘SQL Injection 취약점’이라고 하며, 사용자가 데이터 입력이 가능한 수많은 웹 페이지 상에 이러한 취약점이 존재할 수 있다.


■위협 사례


(1) 사용자 인증 공격


아래의 그림과 같이 인증을 처리하는 모듈이 입력 값에 대해 적절히 검사하지 않았을 때 공격자는 비정상적인 SQL Query를 삽입 할 수 있고 이를 이용해 사용중인 데이터베이스에 영향을 줄 수 있다.



다음은 SQL 구문을 이용하여 인증을 처리하는 일반적인 웹 페이지 구조를 나타낸다.


$row = mysql_query (" SELECT 신청인명, 접수번호 from USER_TABLE where 신청인명='첫번째입력값' and 접수번호='두번째입력값' ");


if ( $row == 1 )

// 인증 성공 루틴

else

// 인증 실패 루틴


이 스크립트에 공격자가 test라는 신청인명을 입력하고 인터넷접수번호 대신 A' or 'A'='A 이란 값을 입력하면 아래와 같은 SQL Query가 완성된다.


SELECT 신청인명,접수번호 FROM user_table WHERE 신청인명=‘test?? AND 접수번호=??A' OR 'A'='A'


이 경우 구문의 WHERE 절은 ??참 AND 거짓 OR 참??의 WHERE 절이 생성되며 무조건 참이 되어 SQL 구문은 올바른 입력 값으로 처리하게 되며 공격자는 웹 인증 페이지를 쉽게 통과할 수 있게 된다.


(2) MS-SQL상에서의 시스템 명령어 실행


MS-SQL 데이터베이스를 사용하는 경우를 예를 들어 보자. 만약 데이터베이스 접근 권한이 시스템 권한을 사용하고 있다면 MS-SQL에서 기본적으로 제공하고 있는 xp_cmdshell이라는 Stored Procedure를 이용하여? 시스템 명령어를 실행할 수 있다.


예로 위의 인증 페이지에서 신청인명에 test, 접수번호에 ??; exec master..xp_cmdshell ??ping 10.10.1.2'-- 값을 입력했다고 가정하면 SQL Query는 다음과 같이 완성될 것이다.


SELECT 신청인명, 접수번호 from USER_TABLE where 신청인명='test' and 접수번호='; exec master..xp_cmdshell 'ping 10.10.1.2'--


이 SQL Query는 SELECT Query와 xp_cmdshell Query를? SQL Query가 순차적으로 실행되게 되며, 마지막의 -- 문자는 이후의 모든 문자열을 주석 처리하여 문장을 완성시켜 준다.


(3) 취약성 판단


-검색어 필드 및 로그인ID, PASSWD 필드에 큰따옴표("), 작은따옴표('), 세미콜론(;) 등을 입력한 후, DB error가 일어나는지 확인하자.


-로그인 모듈 점검


MS SQL인 경우: ID 필드에 ['or 1=1 ;--], 비밀번호 필드에는 아무 값이나 입력한 후 로그인을 시도한다.


Oracle인 경우: ID 필드에 ['or 1=1 --], 비밀번호 필드에는 아무 값이나 입력한 후 로그인을 시도한다.


-기타


ID 필드에 ['or '='], 비밀번호 필드에 ['or '=']을 입력한 후 로그인을 시도한다.


※ 위 예제 이외에도 다양한 방법이 가능하기 때문에, 로그인 및 사용자 입력 값을 사용하는 소스에서 DB Query 생성 방식을 직접 점검해야 한다.


■ 보호 대책


(1) 일반 대책


-데이터베이스와 연동을 하는 스크립트의 모든 파라미터들을 점검하여 사용자의 입력 값이 SQL injection을 발생시키지 않도록 수정한다.


-사용자 입력이 SQL injection을 발생시키지 않도록 사용자 입력 시 특수문자(' " / ; : Space -- +등)가 포함되어 있는지 검사하여 허용되지 않은 문자열이나 문자가 포함된 경우에는 에러로 처리한다.


-SQL 서버의 에러 메시지를 사용자에게 보여주지 않도록 설정한다. 공격자는 리턴 되는 에러 메시지에 대한 분석을 통하여 공격에 성공할 수 있는 SQL Injection 스트링을 알아낼 수 있다. 따라서 SQL 서버의 에러 메시지를 외부에 제공하지 않도록 한다.


- 웹 애플리케이션이 사용하는 데이터베이스 사용자의 권한을 제한한다. 가능하면 일반 사용자 권한으로는 모든 system stored procedures에 접근하지 못하도록 하여 웹 애플리케이션의 SQL Injection 취약점을 이용하여 데이터베이스 전체에 대한 제어권을 얻거나 데이터베이스를 운용중인 서버에 대한 접근이 불가능하도록 한다.


-php.ini 설정 변경


: php.ini 설정 중 magic_quotes_gpc 값을 On으로 설정한다.

; Magic quotes

;


; Magic quotes for incoming GET/POST/Cookie data.

magic_quotes_gpc = On? ; Off에서 On으로 변경한다.


; Magic quotes for runtime-generated data, e.g. data from SQL, from exec(), etc.

magic_quotes_runtime = Off


; Use Sybase-style magic quotes (escape ' with ' instead of ').

magic_quotes_sybase = Off


■ 개발 언어별 대책


-사용자로부터 입력받은 변수로 SQL 쿼리 구문을 생성하는 CGI는 입력받은 변수를 체크하거나 변경하는 로직을 포함하고 있어야 한다.


-입력받은 변수와 데이터 베이스 필드의 데이터형을 일치 시켜야 하고, 사용 중인 SQL 구문을 변경시킬 수 있는 특수문자가 포함되어 있는지 체크해야 한다.


-검색 부분과 같이 클라이언트로부터 생성된 SQL 구문을 받는 부분이 있다면 이를 제거해야 한다.


□ ASP


-취약한 SQL Injection 예제


prodId = Request.QueryString("productId")


Set conn = server.createObject("ADODB.Connection")

Set rs = server.createObject("ADODB.Recordset")


query = "select prodName from products where id = " & prodId


conn.Open "Provider=SQLOLEDB; Data Source=(local);

Initial Catalog=productDB; User Id=dbid; Password="

rs.activeConnection = conn

rs.open query


If not rs.eof Then

response.write "제품명" & rs.fields("prodName").value

Else

response.write "제품이 없습니다"

End If


-안전한 SQL Injection 예제


prodId = Request.QueryString("productId")

prodId = replace(prodId, "'", "'")' 특수문자 제거

prodId = replace(prodId, ";", "")

set conn = server.createObject("ADODB.Connection")

set rs = server.createObject("ADODB.Recordset")

query = "select prodName from products where id = " & prodId

conn.Open "Provider=SQLOLEDB; Data Source=(local);

Initial Catalog=productDB; User Id=dbid; Password="

rs.activeConnection = conn

rs.open query

If not rs.eof Then

response.write "제품명" & rs.fields("prodName").value

Else

response.write "제품이 없습니다"

End If


□ PHP

-addslashes() 함수 사용

: 사용자가 입력하는 값들($_GET, $_POST)을 모두 addslashes() 함수를 이용하여 처리하여 준다.


addslashes()


용도 : DB Query와 같이 인용된 부분앞에 역슬래쉬를 붙여서 반환한다. 해당 문자에는 작은 따옴표, 큰 따옴표, 역슬래쉬, NULL이 있다. SQL Injection 공격을 위해서 사용한다.


- 적용 가능한 PHP : PHP 3 이상


-취약한 SQL Injection 예제


$query = "SELECT id, password, username FROM user_table WHERE id='$id'";// 사용자로부터 입력받은 id 값을 사용자 table에서 조회

$result = OCIParse($conn, $query);

if (!OCIExecute($result))

echo "<META http-equiv="refresh" content="0;URL=http://victim.com">";// 메인 페이지로 redirect


OCIFetchInto($result, &$rows);


... 중략 ...


-안전한 SQL Injection 예제


$query = sprintf("SELECT id,password,username FROM user_table WHERE id='%s';",addslashes($id));

// id변수를 문자형으로 받고, id변수의 특수문자를 일반문자로 변환한다.


// @ 로 php 에러 메시지를 막는다.

$result = @OCIParse($conn, $query);


if (!@OCIExecute($result))

error("SQL 구문 에러");

exit;


@OCIFetchInto($result,&$rows);


... 중략 ...


□ JSP


-취약한 SQL Injection 예제


String sql="SELECT*FROM user_table"+"WHERE id=" + response.getParameter("id")

+ " AND password = " + response.getParameter("password");


Class.forName("org.gjt.mm.mysql.Driver");

conn = DriverManager.getConnection(DB_URL, DB_USER, DB_PASSWORD);


stmt = conn.createStatement();

rs = stmt.executeQuery(query);


while(rs.next())


-안전한 SQL Injection 예제


String sql = "SELECT*FROM user_table"+"WHERE id = ?"+"AND password = ?";

ResultSet rs = null;

PreparedStatement pstmt = null;

try

conn = DBManager.getConnection();

pstmt = conn.prepareStatement(sql);


pstmt.setString(1, request.getParameter("id"));

pstmt.setString(2, request.getParameter("password"));


rs = pstmt.executeQuery();



[길민권 기자(boannews@infothe.com)]


<저작권자: 보안뉴스(www.boannews.com). 무단전재-재배포금지.>


출처: http://www.boannews.com/media/view.htm?idx=1942&kind=2

신고

+ Recent posts