MySQL통신프로토콜 - 인증시의 패킷3

Client Authentication Packet

Client Authentication Packet에서는 클라이언트에서 서버로 유저명, 패스워드등의 인증정보를 통지한다. 

client flags는 클라이언트측에서 이용가능한 옵션을 통지한다. 

max packet size는 클라이언트⇔ 서버사이에서 주고 받는 패키의 최대크기를 통지한다. 

LONGTEXT형등의 큰 데이터를 주고 받기위해서이고  패킷헤더에서 관리하고 있는 3바이트제한(16MB)하고는 관계가 없다. 

큰 데이터를 주고 받기 위해서 필요한 경우에는 mysql의 접속 파라메터인 max_allowed_packet에 큰 수치를 설정하는 것이 정석이다.  이 부분에 반영된다. 

charset number는 클라이언트 측의 캐릭터셋을 서버에 통지한다. 

명령어 라인 툴인 mysql 디폴트에서는 0x08(latin1)이 된다. 

다만 Connector/J 라든지 skip-character-set-client-handshake을 지정한 상태로 mysqld를 기동한 경우에는 서버의 캐릭터셋에 맞추어지므로 Handshake Initialization Packet에서 보내지는 서버의 캐릭터셋하고 같은 번호가 세팅된다. 

계속해서 사용자명, 암호화된 패스워드, 초기 데이터베이스명(지정한 경우만)이 송신된다. 

유저명과 데이터베이스명은 「Null Terminated String」,패스워드는 「Length Coded String」표기가 된다. 

Null Terminated String이라는 것은 문장열 종단에 0x00(null)로 표시하는 것이다. 

문자열 자신에 null문자가 포함되는 경우는 사용할 수 없지만 유저명, 데이터베이스명에서는 그것이 쓰여질 이유가 없기 때문에 문제없다.  한편 Length Coded String은 선두 1바이트(또는 복수 바이트)로 문자열의 크기를 선언해 놓는 것이다. 이것은 Null Terminated String하고는 달리 예를 들어 null문자를 포함한 바이트열이 있어서 처리할 수 있게 된다. 

Client Authentication Packet

패킷헤더 4바이트

client_flags 4바이트 , 클라이언트에서 이용가능한 옵션

max_packet_size 4바이트

charset number 1바이트

filler 23바이트  ,   예약영역, 현재는 항상 0x00

유저명 n+1바이트, 유저명, 최후는 0x00.  따라서 유저명의 바이트수를 n바이트라고 하면 n+1이 된다. 

패스워드의 scramble크기 1바이트,  패스워드의 scramble크기를 16진수표기로 저장. 현재는 0x14(20)고정. 패스워드가 없는 경우에는 0x00고정

패스워드의 scramble  20바이트  패스워드의 암호화를 위한 문자열, 패스워드가 없는 경우에는 사용되지 않음. 

데이터베이스명 n+1 데이터베이스명. 최후에는 0x00.  데이터베이스명을 지정하지 않는 경우에는 사용되지 않음. 

MySQL통신프로토콜 - 인증시의 패킷2

Handshake Initialization Packet

클라이언트로가 서버에 접속요구를 했을 시점에 서버에서 클라이언트에 「Handshake Initialization Packet」이라는 패킷이 송신된다.

패킷 헤더, 서버가 처리가능한 프로토콜의 버전, 버전용의 문자열, 세션 식별ID, 암호화를 위한 문자열, 서버 캐릭터셋이 포함된다. 

프로토콜버전은 MySQL 4.1/5.0/5.1에서는 전부 0x0a이 된다. 

서버의 버전은 서버버전및 빌드옵션등의 문자열이 포함되어 있다. 

이것은 프로그램이 해석하기 위한 정보가 아니라 사람이 눈으로 보고 알 수 있는 정보를 제공하기 위해 준비되어있다.  명령어 라인툴 mysql에서 로그인하면 버전등의 정보가 표시되지만 이 같은 형태로 서버에서 보내어진 것을 표시하고 있는 것이다. 

Handshake Initialization Packet

*패킷헤더  :4바이트    ex>43 00 00 00

*프로토콜버전:  1바이트 4.1이후버전은 0x0a(10) ex>0a

*서버버전 : n+1바이트   바이트수는 바이너리에 의존, 종단은 0x00,

*스레드ID: 4바이트  MySQL에 접속원을 식별하기위한 ID ex>03 00 00 00 

*암호화  seed: 8바이트 패스워드 암호화를 위한 문자열 ex>33 7e 45 79 55 7a 28 54 

*filler: 1바이트  예약바이트 현재는 항상 0x00

*server capabilities: 2바이트 이용가능한옵션       

*캐릭터셋 번호: 1바이트  서버의 캐릭터셋ID(16진수) 

*server status: 2바이트  서버의 상태 ex>02 00    

*filler: 13바이트  예약바이트 현재는 전부 0x00

*난수문자열: 13바이트 난수문자열, 종단은 0x00

스레드ID는 세션번호를 말한다.  SHOW PROCESSLIST명령어등을 실행하면 그 mysqld에 접속해 있는 세션 일람을 번호로 보는게 가능한데 그걸 말한다. 

스레드ID는 클라이언트당 할당되고 그 이후에는 송수신 되지 않는다. 

예를 들어 TCP/IP접속이면 서버측은 스레드ID를 송수신 하지 않아도 클라이언트의 IP어드레스하고 포트번호로 클라이언트 식별이 가능하기 때문이다. 

암호화를 위한 문자열은 패스워드를 암호화하기 위해서 사용된다. 클라이언트에서 서버로 패스워드를 송신할 때 평문으로 주고 받는 것을 방지하기 위함이다. 

MySQL에서는 인증시에 패스워드만 암호화되어 주고 받게 되지만 그것은 사전에 암호화를 위한 문자열(공통키)를 서버에서 클라이언트에 보냈기 때문이라는 것을 알 수 있다. 

Server Capabilities라는 것은 서버가 처리가능한 옵션을 지정하고 있다.  예를 들어 4.0이전과 4.1이후 버전에서는 패스워드 송수신방법이 바뀌었기 때문에 4.1이후 환경에서는 old-password를 지정해서 mysqld를 기동하지 않는 한 4.0이전의 클라이언트에서의 접속은 되지 않는다. 

캐릭터셋번호는 mysqld의 default-character-set파라미터에서 설정한 캐릭터셋에 상당하는 번호이다.  utf8이면 0x53이다.  

Server Status는 자동커밋의 유무효라든지 풀스캔이 실행되었는지 아닌지에 대한 정보가 포함되어있다. 

   **SERVER_STATUS_IN_TRANS   트랜잭션제어중

       **SERVER_STATUS_AUTOCOMMIT   자동커밋모드가 유효

       **SERVER_MORE_RESULTS_EXISTS 결과셋이 계속되고 있음(SQL문을 한번에 여러번 실행하는 경우)

       **SERVER_QUERY_NO_GOOD_INDEX_USED 효과적인 인덱스가 사용되지 못했음.

       **SERVER_QUERY_NO_INDEX_USED 인덱스가 사용되지 못했음.

       **SERVER_STATUS_CURSOR_EXISTS 커서의 최종행에 도달해있지 않음(서버측 커서이용할 때만)

       **SERVER_STATUS_LAST_ROW_SENT 커서의 최종행에 도달했음(서버측 커서 이용할 때만)

      **SERVER_STATUS_DB_DROPPED 데이터베이스가 DROP되었음.

      **SERVER_STATUS_NO_BACKSLASH_ESCAPES escape기호로 0x5c(\)를 사용하지 않음. 

MySQL통신프로토콜 - 인증시의 패킷1

인증시의 패킷

인증시에는  다음과 같은 패킷을 주고 받는다. 

클라이언트                                                                서버

       ---------TCP또는 소켓통신------------------>

      <-------①Handshake Initialization Packet-----

       --------②Client Authentication Packet ------>

     <--------③Ok Packet 또는  ④Error Packet ----

※패킷헤더 

처음, 패킷헤더에 대해서 설명해 본다. 

패킷헤더라는 것은 모든 패킷의 선두에 할당되는 영역으로 4바이트로 구성된다. 

선두3바이트가 패킷의 길이(패킷헤더 길이는 빼고), 1바이트가 패킷의 연번이다. 

예를들어 Handshake Initialization Packet에서는 ox41 00 00 =65(10진수)이면 Handshake Initialization Packet의 총 바이트수가 65바이트라는 것을 의미한다. 

패킷의 내용은 전부 16진수로 표기한다. 

이 패킷길이처럼 수치를 16진수로 표현하는 게 있으면 문자열을 16진수로 표현하는 것이 있다. 

수치표현의 경우에는 전부 little endian이다. 

패킷번호는 00으로 시작해서 왔다갔다 할때마다 1개씩 증가한다. 

새로운 명령이 되면 또 00부터 번호가 할당된다.  클라이언트에서 1패킷보내면 서버에서 복수의 패킷이 되돌아오는 것도 있기 때문에 (검색할 때등) 그럴 때 사용하는 연번이다. 

3바이트는 24비트이기 때문에 다룰 수 있는 수치의 최대값은 16,777,215바이트(약 16MB)가 된다.  LONGTEXT형등의 거대한 필드에서는 열 크기가 이것을 넘는 경우도 있지만 그럴 때에는 다음처럼 2개이상의 패킷에 걸쳐서 송수신된다. 

FF FF FF 00 ...

FF FF FF 01 ...

13 01 00 02 ... 

패킷의 크기가 「0xFF FF FF」의 경우에는 그 패킷이 16MB이상의 되기 때문에 1패킷으로는 완결되지 못하고 후속의 패킷에 계속됨을 알려주고 있다.  이  경우에는 1개의 논리 패킷이 3개의 물리적인 패킷에 걸쳐져 있게 된다. 

MySQL통신프로토콜

여기에서는 MySQL 통신프로토콜에 대해서 좀더 알아보기로 한다. 

MySQL에서는 접속할 때라든지 SQL문실행을 할 때의 갖은 처리에 있어서 클라이언트⇔서버 사이에 MySQL독자의 규칙에 따른 패킷송수신을 한다. 

전제조건에 대해서

패킷의 내부라는 것을  보통 MySQL을 사용함에 있어서  의식하는 것은 거의 없다. 

그러나 기술적으로 흥미가 있는 사람은 적지 않을 것이다. 

또  PHP나 PERL등 각종 프로그래밍언어 전용의 컨넥터(DB접속용 드라이버 프로그램)를 개량하고 싶다라고 할 때에는  프로토콜에 대한  이해가 최소한 필요하다. 

MySQL의 C API를 사용하지 않고 드라이버를 만들 때에는 클라이언트측에서 통신내용을 직접 핸들링할 필요가 있기 때문이다. 

여기에서는 다음과 같은 처리를 할 때 ,  어떤 패킷이 송수신되는 것인지 소개해보겠다. 

1. 로그인할 때 

2. 보통 갱신계열의 SELECT문을 실행할 때

3. 보통 검색계열의 SQL문을 실행할 때 

4. Prepared Satement을 실행할 때 

5. 서버사이드 커서를 이용할 때 

예로써 다음과 같은 테이블, 레코드를  test데이터베이스에 만들어 놓았다는 것을 전제로 설명해보겠다. 

    *전제가 되는 테이블과 레코드예 

  CREATE TABLE tb11 ( col1 INTEGER PRIMARY KEY, col2 VARCHAR(10), col3 DATETIME);

  INSERT INTO tb11 VALUES(1, 'abc', NOW() );

  INSERT INTO tb11 VALUES(2, 'def', NOW() );

  INSERT INTO tb11 VALUES(3, 'abc', NOW() );

  INSERT INTO tb11 VALUES(4, 'xyz', NOW() );

 

DB설계할 때 주의점

DB설계에는 논리 설계와 물리 설계가 있다. 

논리설계에는 어떤 테이블, 칼럼, 키가 필요한가 고민한다. 

물리설계에서는 DB에 테이블을 작성할 때 부하의 집중정도를 생각한다거나 테이블 영역할당등을 견적을 내보곤 한다. 

I/O분산(물리설계)

MySQL의 경우, I/O분산시키는 방법에는 다음과 같은 것이 있다. 

1.  데이터베이스 디렉토리를 심볼릭링크로 한다. 

2. 로그파일위치를 지정한다. 

3.  MyISAM의 경우, 「DATA DIRECTORY」와 「INDEX DIRECTORY」키워드를 사용해서 

MYD파일과 MYI파일 위치를 변경한다. 

4. InnoDB의 경우 innodb_data_file_path로 데이터파일을 분산한다. 

5. InnoDB의 경우 innodb_log_group_home_dir로 REDO로그파일 저장위치를 변경한다. 

*심볼릭링크를 설정

MySQL에서는 데이터베이스는 디렉토리에 대응된다. Unix계열의 경우 이 디렉토리를 심볼릭링크로 해서 관리하는 게 가능하다. 이 작업을 하는데는 일단 MySQL서버를 중지시키는 게 안전하다. 

심볼릭링크의 설정방법은 보통 심볼릭링크 생성방법과 같다.  참고로 심볼릭링크로 되어있는 데이터베이스에 DROP DATABASE를 실행하면 실체가 되는 부분도 삭제되어버리므로 주의해야한다. 

윈도우에서 심볼릭링크

윈도우즈용 MySQL은 Windows98,FAT32이 주역이었던 시대에 만들어졌기 때문에 윈도우즈에서의 파일분산은 Unix계열하고는 조금 다른 방법으로 대응할 필요가 있다. 여기에서는 C:\mysql\data\test (test데이터베이스)의 내용을 D:\data\test에 배치하는 것을 전제로 설명한다. 

1. c:\mysql\data\test를 d:\data\test로 복사한다. 

2. c:\mysql\data\test.sym파일을 작성한다. 내용은 다음과 같이 실체가 있는 디렉토리 패스를 기술하는 것이다. 

   ⊙test.sym내용

  D:\data\test

이 기능은 윈도우즈에서 유효로 하기 위해서는 --symbolic-links옵션을 사용한다. 

이것으로 test데이터베이스 테이블은 모두 D:\data\test에 작성되게 된다. 

참고로 Unix계열에서 이 옵션은 다른 의미(MyISAM분산, INDEX DIRECTORY, DATA DIRECTORY유효화)가 된다. 

*로그파일 위치의 변경

MySQL에서는 로그파일의 출력위치가 지정가능하다.  이 기능으로 파일을 분산한다. 

특히 바이너리로그를 유효로 하고 있는 경우에는 그 위치를 고려하는 것이 좋다. 

*MyISAM파일의 분산

MyISAM테이블은 데이터(MYD)파일과 인덱스(MYI)파일과 테이블정의(frm)파일로 이루어져있다.  이것은 데이터베이스를 표시하는 디렉토리밑에 전부 올려져 있다. 

그러나 CREATE TABLE문에 키워드를 지정함으로써 MYD파일, MYI파일을 별도의 디렉토리에 배치하는 게 가능하다. 

   ⊙MYD파일, MYI파일을 별도의 디렉토리에 배치한다. 

  mysql> USE test;

  mysql>CREATE TABLE a ( i int) ENGINE=MYISAM DATA DIRECTORY='/disk1/data/' INDEX DIRECTORY ='/disk2/data/';

DATA DIRECTORY, INDEX DIRECTORY에는 저장위치 디렉토리를 절대패스로 저장한다. 

이 예에서는 datadir/test/a.MYD 파일은 /disk1/data/a.MYD파일에 심볼릭링크로 된다. 

이미 CREATE한 경우에는  mysqld을 일단 정지한 후 MYD파일과 MYI파일을 이동하고 심볼릭링크를 수작업으로 만든다.  ALTER TABLE에는 DATA DIRECTORY, INDEX DIRECTORY지정이 되지 않는다. 

참고로  심볼릭링크를 무효화하는 옵션 --skip-symbolic-links가 지정되어있는 경우는 이 기능은 동작하지 않으므로 주의해야한다. 

*InnoDB파일의 분산

InnoDB에서는 REDO로그파일을 쓰는 디렉토리를 저정하는 게 가능하다. 또 테이블 스페이스를 구성하는 각각의 데이터 파일을 풀 패스로 지정가능하다.  I/O분산을 하는 경우에는 이것도 고려해 두는 것이 좋을 듯 싶다. 

테이블설계(논리설계)

인덱스를 어떻게 만들까는 큰 고민이다. 

특히 InnoDB의 경우에는  primary, unique키가 없으면 행락(row lock)이 걸리지 않는다라든지 

대상이 되는 레코드이외에  스캔되는 레코드에 대해서 록이 걸리는 경우등 SQL를 사용할 때 주의가 필요하다. 

업무의 내용을 고려해서 인덱스작성을 고민해야한다. 

또  MySQL의 경우 필드수를 많이 사용할 때도 주의해야한다. 

MySQL4.1이상에서는 필드명은 utf8로 저장되어있다. 이것은 utf8이외의 캐릭터셋을 사용한 경우 필드명에 대해서 항상 문자코드변환이 이루어진다는 것을 의미한다. 5.1.12-beta의 경우에는 예를 들어 클라이언트가 latin1이고 필드의 캐릭터셋이 latin1 그리고 필드명이 알파벳문자라고 해도 이 utf8변환 로직을 사용하게 된다. 

SELECT로 취득하는 필드가 몇개 없다면 문제없지만 100개 정도되면  이 변환로직의 오버헤드는 결코 무시할 수 없다.  100개 이상의 필드를 작성해서 latin1, utf8의 경우를 비교해보면 알게된다. 

SELECT문을 실행하는 경우에는 모든 필드를 취득하는 것을 피하던지 테이블 필드를 50개이내로 하던지  모든 환경을 utf8로 하던지 해야할 것이다. 

MySQL기본구조 - 메모리할당

메모리 할당

복수의 스레드가 같은 메모리 영역에 액세스하는 경우(공유메모리, 글로벌 메모리라고 부름)하고 각 스레드가 독립된 메모리를 확보하는 경우가 있다. 

이런 메모리 할당은 서버변수와 불리는 변수(옵션)에 값을 지정하는것으로 설정가능하다. 

MySQL서버의 사용메모리양은 다음과 같이 계산이 가능하다. 

 글로벌 메모리 + 각 클라이언트스레드사용 메모리의 합계 

이것을 서버접속수를 감안한 식으로 변경하면 다음과 같이 된다. 

 

 글로벌 메모리 + 한개의 클라이언트용 스레드 사용 메모리 X 접속수

할당메모리를 지정하는 서버변수(옵션)에는 필요할 때 처음으로 메모리할당이 이루어지는 것도 있다. ( net_buffer_length, query_prealloc_size, thread_stack등은 스레드생성시에 항당 할당된다. 이같은 할당방법이 이루어지는 경우도 있다. )

그러나 빈번하게 사용될 것이 예상되는 메모리에서는  이 식에 미리 모든것을 넣어서 메모리 사용량을 계산한다. 

예를 들어 ORDER BY, GROUP BY,데이블결합은 빈번하게 이루어질 것이므로 sort_buffer_size라든지 join_buffer_size등은 높은 확률로 사용될 것이 예상된다.  따라서 메모리 계산식에는 이것들은 모두 고려한다. 

그런데 REPAIR TABLE은 지금 접속하고 있는 모든 클라이언트가 반드시 실행될 거라고는 도저히 생각되어지지 않고 1년에 몇번 셀 정도로 끝날 것이다. 따라서 myisam_sort_buffer_size는 메모리 사용량 계산에 넣지않는 경우도 있다.   걱정이 된다면 이 식에 계산된 값 + 알파정도..

mysqld옵션과 서버변수

MySQL서버(mysqld)에 부여하는 옵션을 나타낸다. 서식은 다음과 같다. 

 mysqld --option

이 mysqld옵션은 my.cnf에도 기술 가능하다. 명령어라인에 옵션을 지정하는 방법, my.cnf를 변경하는 방법 모두 mysqld정지와 재기동이 필요하다. 

또 옵션에는 서버 기동중에 SQL문으로 값을 변경할 수 있는 경우도 있다. 

이것은 서버변수라고 불리우는 경우도 있다.  예를 들어 값을 SQL문으로 변경하는경우에는 다음 처럼 한다.  다만 값을 부여할 때는 「K」「M」「G」단위는 사용할 수 없다. 

대신 산술연산자 「+」「-」「*」가 사용가능하다. 

>SET문으로 값을 변경하는 경우 

 mysql> SET GLOBAL sort_buffer_size=1024*1024;

 mysql> SET @@global.sort_buffer_size=1048576;

 mysql> SET SESSION sort_buffer_size=1048576;

 mysql> SET @@session.sort_buffer_size=1048576;

 mysql> SET LOCAL sort_buffer_size=1048576;

 mysql> SET @@local.sort_buffer_size=1048576;

 mysql> SET @@sort_buffer_size=1048576;

 mysql> SET sort_buffer_size=1048576; 

SET GLOBAL 변수명과 SET @@global.변수명은 같다. 

다음 기술은 SET SESSION 변수명과 같다. 

>SET문 사용법

SET @@session.변수명

SET 변수명

SET @@변수명

SET LOCAL 변수명

SET @@local.변수명

SET GLOBAL은 새롭게 접속해온 모든 클라이언트 스레드에 영향을 미친다.  일부는 현재 접속중의 다른 클라이언트 스레드에게도 영향을 미친다. (모든 변수가 영향을 받는 것은 아니다. )

SET SESSION는 SET문을 실행한 클라인트의 스레드만 영향을 미친다. 다른 스레드는 영향을 받지 않는다.  접속을 끊으면 설정은 없어진다. 

모든 옵션과 변수가 이런식으로 변경될 수 있는 것은 아니다. 또 GLOBAL,SESSION의 지정도 모두가 양쪽 다 지정될 수 있는 것은 아니다. 

현재 옵션이나 변수의 값을 확인하는 방법은 다음과 같다. 

>현재의 옵션과 변수의 값을 확인하는 방법

mysql> SHOW VARIABLES;

mysql> SELECT * FROM information_schema.GLOBAL_VARIABLES;

mysql> SELECT * FROM infromation_schema.SESSION_VARIABLES;

shell$ mysqladmin -uroot variables;

shell$ mysqld --verbose --help

나중에 mysqld옵션및 서버변수를 정리해 보겠다.  변수와 옵션이 스위치의 경우(bool형) 부여되는 값은 「1」「ON」「YES」「ENABLE」「TRUE」로 표현되지만  전부 「유효」라는 의미가 된다.  이 bool형 변수에 대해서 SET문으로 값을 정할 경우에 값「1」을 지정하면 정확하게 스위치 ON이 되지만 그 밖의 지정방법은 제대로 설정되지 않는 경우도 있다. 

MySQL기본구조 - 멀티스레드

MySQL서버는 처리를 복수의 스레드가 분담한다. 

*메인스레드 

*각 클라이언트에 대응하는 스레드

 하나의 클라이언트에 대해서 하나의 스레드를 갖는다.  현재는 SQL해석과 실행은 이 스레드 각각이 처리한다. 

*접속핸들(handle TCP/IP sockets)스레드

*시그널핸들러(interrupt)스레드

*shutdown스레드

 mysqld정지를 실행한다.  시그널핸들러에 의해 작성된다. 

*named pipe접속용 스레드

*shared memory접속용 스레드

*delayed스레드

 INSERT DELAYED에서 사용

*레플리케이션용 스레드

  마스터에서는 한개의 스레드(Binog dump스레드), 슬레이브에서는 두개의 스레드(I/O스레드, SQL스레드)가 생성된다. 

*InnoDB용 스레드

  I/O스레드(4개)

  watchmen스레드(2개)

 InnoDB마스터스레드

*bootstrap스레드

 권한 테이블의 초기작성시에 사용된다. 초기화를 끝내면 (정확히 표준입력에서 SQL문장 해석이 끝나면)종료된다. 

MySQL기본구조 - share/mysql/charsets/

share/mysql/charsets/

share/mysql/charsets/디렉토리에는 각 1바이트의 캐릭터셋에 따른 1바이트 문자의 소트순서를 정의한 파일이 있다.

캐릭터셋명.xml

이곳에는 대문자과 소문자가 혼재한 경우등의 소트순서를 정의하고 있다.

일본어같은 멀티바이트문자 캐릭터셋에 대해서는 이 파일정의하고는 관계가 없다.

share/mysql/charsets/Index.xml

share/mysql/charsets/Index.xml파일에는 각 캐릭터셋의 설명과 ID번호가 정의되어있다. 이 ID번호는 소스에 포함되어있는

번호하고 일치하므로 생각없이 변경해서는 않된다.

MySQL기본구조 - 에러메세지파일

에러메세지 파일

에러번호 1000이상의 에러 메세지는 다음의 파일에 보존된다. 

share/mysql/에러메세지언어명/errmsg.sys

이것은 바이너리 파일이지만  이 파일의 원본이 되는 텍스트 파일은 share/mysql/errmsg.txt이다.

MySQL서버는 기동시에 --language에서 지정된 문자열에 따라서 errmsg.sys파일을 읽어들인다. 

share/mysql/language에서의 지정/errmsg.sys

표준은 language=english이다.

MySQL기본구조 - frm파일

frm파일

테이블의 정의정보는 .frm(Format) 파일에 기록된다. 

.frm파일은 sql/table.cc의 open_binary_frm()에서 읽혀진다. 

.frm파일의 최초의 2바이트 [0xfe01]은 보통의 frm파일이라는 것을 나타낸다. 

.frm파일의 3바이트째는 frm파일의 버전을 나타낸다. 

 .frm파일의 내용

 shell$ hexdump -c data/mysql/user.frm | head 

 00000000  fe 01 09 09 03 00 00 10 01 00 00 30 00 00 e7 00  | .........0.... |

MySQL버전 3.22 ~ 4.0의 경우, 3바이트째는 [0x07], 버전 4.1~5.1에서 작성한 테이블은 [0x09]가 된다. 

또, frm버전은 SHOW TABLE STATUS의 Version에서도 확인가능하다. 

 SHOW TABLE SATUS실행결과

mysql> SHOW TABLE STATUS LIKE 'user'\G

***************************** 1. row ***************************

Name: user

Engine: MyISAM

Version: 10

Row_format: Dynamic

Rows: 4

Avg_row_length: 48

Data_length: 240

Max_data_length: 281474976710655

Index_length: 2048

Data_free: 48

Auto_increment: NULL

Create_time: 2008-12-09 22:02:16

Update_time: 2008-12-10 22:02:16

Check_time: NULL

Collation: utf8_bin

Checksum: NULL

Create_options:

Comment: Users and global privileges

1 row in set (0.00 sec)

.frm파일과 SHOW TABLE STATUS실행결과에서는 frm파일의 버전값이 다르다. 

그 차이는 헤더파일을 확인하면 알수 있다.  frm파일버전은 헤더파일에는 다음과 같이 기술되어있다. 

헤더파일(mysql_version.h, sql/unireg.h)

 #define DOT_FRM_VERSION 6

 #define FRM_VER DOT_FRM_VERSION

 #define FRM_VER_TRUE_VARCHAR (FRM_VER+4) /* 10 */

SHOW TABLE STATUS에서 보여지는 버전은 DOT_FRM_VERSION을 기본으로  가산된 값을 표시하고 있고 frm파일의 3바이트째는 [그 가산값 -1]이 된다.

VIEW테이블인 경우에는 .frm최초의 바이트는 다음과 같이 TYPE=VIEW부터 시작된다. 

.frm파일의 내용(view테이블의 경우)

shell$ hexdump -C data/mysql/v.frm | head

00000000 54 59 50 45 3d 56 49 45 57 0a 71 75 65 72 79 3d  | TYPE=VIEW.query=|

   

MySQL기본구조3

파일의 배치

MySQL서버등의 명령어를 인스톨한 곳을 「basedir」, MySQL데이터를 저장하는 곳을 「datadir」라고 부른다.  basedir, datadir 둘다 MySQL서버 옵션으로 존재하고 변경가능하다. 

MySQL서버가 사용하는 파일은 다음과 같다. 

  [basedir] -----

                      + share/mysql/charsets/

                      + share/mysql/english/

  [datadir] -----

                      +mysql/

[datadir]/mysql/은 권한테이블이 저장되어있는 중요한 디렉토리이다. 이 디렉토리 아래에 결정된 파일이 없으면 서버는 기동되지 않는다. 

[datadir]/ 이하는 작성된 디렉토리는 서버가 「데이터베이스」라고 인식한다. 

예를 들어 「test」라는 데이터베이스가 존재한다고 하면 즉 [datadir]/test/ 디렉토리가 존재하는 것을 말한다. 

예를 들어 「test」데이터베이스에 「a」라는 테이블을 작성하면 [datadir]/test/a.frm파일이 작성된다.  frm파일은  테이블정의가 기록되는 파일이다. 

데이터와 인덱스등은 MyISAM, InnoDB, NDB라는 스토리지엔진의 차이에 따라 작성하는 룰도 달라진다. 

사용되는 빈도가 높은 MyISAM의 경우에 [datadir]/test/a.MYD, [datadir]/test/a.MYI파일이 작성된다. 

「mysql」데이터베이스 아래의 권한테이블은 MyISAM이지 않으면 않된다. 

MySQL기본구조2

MySQL서버의 기본구조

MySQL서버의 기본구조는 다음과 같다.

1. 하나의 프로세스가  접속대기, 데이터관리, 쿼리 처리를 전부 실행한다. (NDB는 제외)

2. 하나의 프로세스가 전부 데이터베이스를 관리

3. 멀티 쓰레드를 사용해서 처리를 분담. 접속대기와 I/O처리등  자식 쓰레드를 작성해서 분담한다. 

4. 하나의 클라이언트에 하나의 자식 쓰레드를 생성. 클라이언트가 서버에 접속하면 그 클라이언트를 위해 한개의 쓰레드가 생성된다.

5. 데이터 보존형식이 여러개 존재한다.  이것은 테이블단위로 임의 선택,혼재 가능하다. 

 이것을  「스토리지엔진」,「테이블형」이라고 불린다.  스토리지엔진에는 MyISAM, InnoDB, NDB, MEMORY(HEAP)등이 있다. 

6.클라이언트인증은 패스워드 인증. 인증에는 사용자명, 호스트명(IP address),패스워드가 사용된다. 

7. 오퍼레이팅 시스템의 계정하고 MySQL서버관리 유저에는 관련성이 없다. 

  ( 즉. "OS 계정은 이것이니까 접속유저는 이렇게 하지 않으면 안돼!", "OS계정은 이것이니까 사용하는 데이터베이스는 이것이 된다." 라는 건 없다. )

8.버전4.1이상에서는 클라이언트하고 서버가 사용하는 캐릭터셋(문자코드)가 틀린경우에는 서버가 문자코드변환을 실행한다. 캐릭터셋은 mysqld프로세스 단위, 데이터베이스 단위, 테이블 단위, 필드단위로 지정가능하다.  혼용도 가능하다. 

통신형태

서버와 클라이언트간의 통신방법은 아래와 같다. 

• TCP/IP
• 소켓파일(Unix계열)
• named pip(NT계열)
• shared memory(NT계열)

Unix계열 운영체제에서는 특히 지정이 없는 한 localhost서버에 대한 접속은 소켓파일로 실행된다.  다만 예외는 존재한다. Connector/J는 localhost서버에 TCP/IP접속한다. 

윈도우즈에서는 통상 TCP/IP가 사용된다.  named pipe, shared memory를 사용하는 경우에는 클라이언트 명령에 지정할 필요가 있다. 

MySQL에는 「localhost」을 지정하는 경우와 「127.0.0.1」을 지정한 경우에 다른 취급을 하게된다.  클라이언트가 127.0.01의 서버에 접속하려한다면 TCP/IP를 사용한다. 

통신방법지시예

shell$ mysql -hlocalhost     <--소켓통신

shell$ mysql -h127.0.0.1     <--TCP/IP통신

shell$ mysql -hlocalhost -S/tmp/mysql.socket  <--소켓통신 파일지정

shell$ mysql -hlocalhost --protocal=pipe   <--named pipe로 접속

shell$ mysql -hlocalhost --pipe   <-- named pipe로 접속

shell$ mysql -hlocalhost --protocal=memory --shared-memory-base-name=MySQL <--shared memory로 접속

MySQL기본구조

MySQL은 클라이언트 서버 모델을 사용하고 있다.  서버 본체에 해당하는 것이 mysqld(윈도우즈의 경우에는 mysqd.exe, mysqld-debug.exe, mysqld-nt.exe, mysqld-max-nt.exe, mysqld-max.exe)이다.

서버기동에 도움을 주는 툴로써는 mysqld_safe, mysql.server, mysqlmanager, mysqld_multi가 있다. 

유지보수용 클라이언트로써는 mysqladmin, mysqldump, mysqlimport,myisamchk, mysqlbinlog등이 있다. 

클라이언트 명령어 대표가 mysql명령어이다. (오라클의 sqlplus )

MySQL은 이러한 명령어가  포함되어서 배포된다. 

설정파일 my.cnf(my.ini) -4

각 개발언어에서 my.cnf을 읽어들임

my.cnf파일의 해석의 제어, my.cnf파일의 group지정을 각 개발언어에서 어떻게 코딩하는지 간단하게 적어본다. 

1.C의 경우

  c에서는 mysql_options()함수를 사용해서 my.cnf파일을 읽어들인다. 

• mysql_options(mysql, MYSQL_READ_DEFAULT_FILE, 파일명) :  my.cnf파일을 지정 (--defaults-file=하고 같이 지정한 파일만 읽어들임)
• mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP,  group명):읽어들일 group지정

2.Connector/ODBC (MyODBC)의 경우

 윈도우즈용의 ODBC는 DSN지정항목에 Read Options From my.cnf옵션이 존재한다. 

  이것을 체크해두면 my.ini을 읽어들이도록 동작한다.  읽어들여지는 group은 [odbc]이다.

 Unix계열 ODBC는 odbc.ini파일에 DSN정의를 적지만 그곳에 option=키워드를 지정하게되면 my.cnf을 읽어들이게 된다.  읽어들여지는 group은 [odbc]이다.

     odbc.ini예

     [DSN명]

     option= 65536

    option에 지정하는 값에 대해서는 아래의 매뉴얼을 참조할 것.

     ( Connecter/ODBC Connection Parameters : http://dev.mysql.com/doc/refman/5.1/en/myodbc-configuration-connection-parameters.html )

3.Connector/J의 경우

 Connector/J 3.1은 my.cnf파일을 읽어들이는 기능을 가지고 있지 않다. 

4.Perl DBD-mysql의 경우

 Perl DBD-mysql 3.003에서는 DSN정의에 다음의 키워드를 지정하는 것으로 my.cnf파일을 제어한다.

   

•  mysql_read_default_file= : my.cnf파일을 지정 (--defaults-file=하고 같이 지정한 파일만 읽어들임)
  
• mysql_read_default_group= : 읽어들일 group을 지정

5.PHP mysqli의 경우

PHP mysqli에서는 options()메소드를 사용해서 my.cnf 파일을 제어한다. 

• $mysqli->options(MYSQLI_READ_DEFAULT_FILE, 파일명) : my.cnf파일을 지정 (--defaults-file=하고 같이 지정한 파일만 읽어들임)
• $mysqli->options(MYSQLI_READ_DEFAULT_GROUP, group명): 읽어들일 group을 지정

6.PHP PDO MySQL의 경우

PDO MySQL의 경우에는 드라이버 옵션에서 my.cnf을 읽어들이도록 지시한다. 

$dbh = new PDO( DSN, 사용자명, 패스워드,

                   array(PDO::MYSQL_ATTR_READ_DEFAULT_FILE=>파일명,

                              PDO::MYSQL_ATTR_READ_DEFAULT_GROUP=>group명) );

7.Ruby(MySQL/Ruby)의 경우

 Ruby의 libmysqlclient 래퍼인터페이스 MySQL/Ruby 2.7.2(http://www.tmtm.org/mysql/ruby/)에서는 Mysql#options()메소드를 사용해서 my.cnf파일을 제어한다. 

• Mysql#options(Mysql::READ_DEFAULT_FILE, 파일명):my.cnf파일을 지정 (--defaults-file=하고 같이 지정한 파일만 읽어들임)
• Mysql#options(Mysql::READ_DEFAULT_GROUP, group명): 읽어들일 group지정

설정파일 my.cnf(my.ini) -3

각 명령어가 읽어들이는 group

MySQL 5.1.12-beta에서 제공되는 각 명령어(스크립트)가 사용하는 group을 소개한다. 

덧붙여「-」는 my.cnf설정을 읽어들이지 않는 다는 의미이다.

MySQL명령어군이 읽어들이는 group

1. charset2html   : -

2. comp_err : -

3. innochecksum : -

4. msql2mysql : -

5.my_print_defaults  : 지정된 group

6.myisam_ftdump : -

7.myisamchk : myisamchk

8.myisamlog : -

9.myisampack : myisampack

10.mysql : mysql, client

11.mysql_client_test : server, embedded, mysql_client_test_SERVER

12.mysql_client_test_embeded : server, embedded, mysql_client_test_SERVER

13.mysql_config: -

14.mysql_convert_table_format: -

15.mysql_create_system_tables: -

16.mysql_explain_log : -

17.mysql_find_rows : -

18.mysql_fix_extensions : -

19.mysql_fix_privilege_tables :  mysql_install_db, mysql_fix_privilege_tables

20.mysql_install_db: mysqld, mysql_install_db

21.mysql_secure_installation: mysql, client

22.mysql_setpermission : perl, client (다만 ~/.my.cnf파일만 읽어들임)

23.mysql_tzinfo_to_sql: -

24.mysql_upgrade: mysql_upgrade

25.mysql_waitpid : -

26.mysql_zap: -

27.mysqlaccess: -

28.mysqladmin : mysqladmin, client

29.mysqlbinlog : mysqlbinlog, client

30.mysqlsub : -

31.mysqlcheck: mysqlcheck, client

32.mysqld : mysqld, server, mysqld-5.1

33.mysqld_multi: mysqld

34.mysqld_safe: mysqld, server, mysqld_safe, safe_mysqld

35.mysqldump : mysqldump, client

36.mysqldumpslow: mysqld

37.mysqlhotcopy : mysqlhotcopy, client

38.mysqlimport : mysqlimport, client

39.mysqlmanager : manager( 다만 Unix계열에서는 /etc/my.cnf, 윈도우즈에서는 mysqlmanager.exe가 존재하는 디렉토리 \my.ini만 읽어들인다. )

40.mysqlshow : mysqlshow, client

41.mysqlslap : mysqlslap, client

42.mysqltest: mysqltest, client

43.mysqltest_embedded: mysqltest, client

44.ndb_config: mysql_cluster

45.ndb_cpcd : ndb_cpcd

46.ndb_delete_all : mysql_cluster

47.ndb_desc : mysql_cluster

48.ndb_drop_index : mysql_cluster

49.ndb_drop_table : mysql_cluster

50.ndb_error_reporter : mysql_cluster (ndb_config를 호출함)

51.ndb_mgm: mysql_cluster, ndb_mgm

52.ndb_mgmd: mysql_cluster, ndb_mgmd 

53.ndb_print_backup_file: -

54.ndb_print_schema_file: -

55.ndb_print_sys_file: -

56.ndb_restore : mysql_cluster, ndb_restor

57.ndb_select_all : mysql_cluster

58.ndb_select_count: mysql_cluster

59.ndb_show_tables: mysql_cluster

60.ndb_size.pl: -

61.ndb_test_platform : -

62.ndb_waiter: mysql_cluster

63.ndbd: mysql_cluster, ndbd

64.perror : -

65.replace: -

66.resolve_stack_dump: -

67.resolveip : resolveip, client

설정파일 my.cnf(my.ini) 2

libmysqlclient가 읽어들이는 환경변수

libmysqlclient는 my.cnf이외에 환경변수에서 값을 읽어들일 수 있다. 

libmysqlclient는 다음과 같은 환경변수를 지원한다. (sql-common/client.c, libmysql/libmysql.c)

my.cnf하고 환경변수에서 같은 파라미터를 사용했을 경우는 my.cnf의 값을 채용한다. 

(my.cnf > 환경변수 > 표준치)

MYSQL_DEBUG 디버그모드. --debug=옵션에 해당

MYSQL_PWD 접속하기 필요한 패스워드 --password=옵션에 해당

USER --user=옵션이 생략되었을 경우, 이 값을 유저명으로 사용함

my.cnf의 내용기술

my.cnf(my.ini)파일은 다음의 룰에 따라서 기술한다.  덧붙여말하면 옵션명에 포함되는 "-"(마이너스)를 "_"(언더스코어)라고 해도 인식한다.  my.cnf안에는 옵션명에 포함되어 있는 "-"하고 "_"는 같은 값이다. 

 my.cnf기술 룰

#문자로 시작되는 행    코멘트행. 행의 도중에 있는 경우에는 #다음은 코멘트가 된다. 

;문자로 시작되는 행   코멘트행 

[group]  그룹명의 지정. 다음의 그룹 시작까지 하나의 그룹이 된다. 그룹내에 옵션을 기술할 것

opt_name 옵션. 명령어라인 인수에서는 --opt_name하고 같음. "--"을 빼고 기술할 것

opt_name=값  값을 지정하는 옵션

!include  추가로 읽어들일 파일을 지정   예> !include /etc/mysql/local_setting.cnf

!includedir 지정된 디렉토리 이하의 파일을 추가 설정파일로 읽어들임  예>!includedir /etc/mysql/conf.d 

      

 

[client] group

my.cnf의 [client] group에 기술되는 옵션은 my.cnf을 사용하는 많은 어플리케이션, 명령어에 사용된다. (전부는 아니다!)  예를 들면 mysql명령어는 [client] group과 [mysql] group을 사용한다. 

[client]하고 [mysql] group의 내용은 my.cnf파일내에 기술된 순서로 읽어들여진다. 

어느 group이 먼저 읽어들여지는가 결정되어있지 않고 그냥 적혀진 순서대로 따른다. 

(mysys/default.c)

주의점은 아래에 있다. 

1.[client] group은 모든 명령어, 어플리케이션이 사용하지는 않는다. 

2.모든 명령어, 어플리케이션이 지원하지 않는 옵션을 적으면 에러로 정지하는 명령어가 생길 수 있다. 

PHP-mysqli, MySQL/Ruby, Perl DBD-mysql, MyODBC(Connector/ODBC)는 libmysqlclient(libmysql.dll)을 사용하고 있지만 my.cnf파일을 읽어들이는 코딩 또는 설정을 하지 않는 한 my.cnf을 읽어들이지 않는다. 

my.cnf을 읽어들이지 않는다는 것은 [client] group에 옵션을 적어두어도 먹히지 않는다. 

또 명령어중에는 my.cnf는 읽어들이지만 [client] group을 읽지않는 것도 존재한다. 

또 Connector/J는 my.cnf을 읽어들이지 않는다. 

my.cnf를 다루는 옵션

libmysqlclient를 사용한 명령어와 mysqld(MySQL서버)는 다음의 옵션을 지원한다. 

이것은 my.cnf 취득을 제어하는 옵션이다. 

my.cnf취득 제어 옵션

--no-defaults
my.cnf 파일을 읽어들이지 않음

--print-defaults
설정된 옵션을 표시

--defaults-file=
설정된 파일만 my.cnf옵션파일로써 읽어들임

--defaults-extra-file=
지정된 파일을 추가로 읽어들임

--defaults-group-suffix=
지정된 suffix을 붙인 group을 읽어들임. 예를 들면 mysq --defaults-group-suffix=_my라고 하면 mysql명령어는 [client_my], [mysql_my] group 기술을 읽어들임

설정파일 - my.cnf(my.ini)

MySQL에서는 mysqld과 기타 명령어 옵션을 my.cnf또는 my.ini에 기술하는 것이 가능하다. 

(기술해놓지 않아도 명령어군은 동작한다.)  커맨드라인에서 매번 옵션을 지정하는 것은 귀찮지만 그 설정을 파일에 적어두면 일일이 옵션 지정할 필요가 없어진다. 

따라서 my.cnf에는 여러가지 기술을 하게 된다. 

Unix계열 OS에서는 보통 /etc/my.cnf이다.  윈도우즈에서는 c:\my.ini가 많이 사용된다. 

다만  위 기술장소 이외에 있는 파일도 읽어들일 수 있게 되어있다. 

여기에서는 my.cnf의 기술, 읽어들이는 순서, 읽어들여지는 그룹, 서포트하고 있는 옵션에 대해서 정리해본다. 

my.cnf읽는 순서

--default-file=옵션을 사용하고 있는 경우에는(만약 옵션이 이용가능하다면) 지정된 파일만 읽으므로 주의하길 바란다.  이것 이외에는 보통 여러개의 my.cnf파일을 읽어들인다. 

그 기능은 libmysqlclient(libmysql.dll)이 갖추고 있다. 

 여러개의 파일에 같은 옵션이 기술되어 있는 경우에는 나중에 읽어들인 값으로 대체된다. 

또 파일은 전부 존재하지 않으면 안된다. 

파일을 읽는 순서, 검색경로는 버전이 올라가면 변경될 가능성은 있다. 

다음은 각각의 my.cnf가 읽혀지는 순서이다. 

#Unix계열 운영체제 경우 읽는 순서(5.0/5.1)

1. /etc/my.cnf

2. /etc/mysql/my.cnf( 5.1.15-beta이상은 유효. 5.0.45에서는 읽어들이지 않음)

3. $MYSQL_HOME환경변수에 지정된 디렉토리/my.cnf 

4.--defaults-extra-file옵션에 지정된 파일

5.$HOME/.my.cnf(명령어를 실행하는 계정의 홈 디렉토리)

6.DEFAULT_SYSCONFDIR/my.cnf

 (DEFAULT_SYSCONFDIR은 ./configure --sysconfdir=에서 지정한 디렉토리 5.0.21이상)

#윈도우즈에서 읽는 순서(5.0/5.1)

 my.ini하고 my.cnf의 양 파일이 같은 디렉토리에 있는 경우에는 my.ini , my.cnf순으로 읽어들여진다. 

1.c:\my.{ini,cnf}

2.%WINDIR%\my.{ini,cnf}

3.%System%\my.{ini,cnf} (%System%은 kernel32.dll이 존재하는 디렉토리)

4.MYSQL_HOME환경변수에 지정된 디렉토리\my.{ini,cnf}

5.명령어가 존재하는 디렉토리의 한단계위의 디렉토리\my.{ini,cnf}

6.--defaults-extra-file=옵션에 지정된 파일

7.DEFAULT_SYSCONFDIR\my.{ini,cnf}

(DEFAULT_SYSCONFDIR은 컴파일할 때 "/D DEFAULT_SYSCONFDIR="처럼 지정된 디렉토리. 현재 바이너리 배포판은 무효. 5.0.21이상 )

버전 4.1에서는 $MYSQL_HOME환경변수는 읽어들이지 않는다.  그 대신에 datadir/my.cnf(datadir는 컴파일할 때 지정한 디렉토리)를 읽어들인다. 

5.0.21미만에서는 --sysconfdir(DEFAULT_SYSCONFDIR)에서 지정된 디렉토리에서는 찾지 않는다. 

libmysqlclient가 지원하는 옵션 

C언어로 작성된 libmysqlclient에는 my.cnf을 읽어들이는 기능이 있다. libmysqlclient는 my.cnf에 써져 있는 다음의 옵션을 지원한다. (sql-common/client.c에 기술)

compress  통신을 gzip압축한다.  zlib를 지원하면 사용가능

connect-timeout= 접속 타임아웃(초)

host= 호스트 지정

max-allowed-packet=  1개의 통신패킷의 최대 사이즈(바이트)

password= 접속할때의 패스워드

pipe 접속에  named pipe를 사용(윈도우즈만)

port= 서버의 포트

protocol=  [tcp|socket|pipe|memory]

secure-auth  4.1이상  클라이언트 인증에 한정

shared-memory-base-name=  shared memory이름

socket= 소켓파일명

user= 접속유저명

timeout=  connect-timeout과 동일

ssl-ca= CA의 CERTIFICATE 파일지정

ssl-capath= CA의 CERTIFICATE 파일이 존재하는 디렉토리 지정

ssl-cert=  CERTIFICATE파일지정

ssl-cipher= Cipher지정

ssl-key=  SSL키 파일의 지정

character-sets-dir=  charsets/디렉토리 지정

default-character-set=  character set이름 지정

database= 현재 데이터베이스 명

init-command= 서버에 접속후 자동적으로 실행하는 SQL문

disable-local-infile  LOCAL INFILE문 무효

local-infile  LOCAL INFILE 유효

interactive-timeout=  인터랙티브모드 타임아웃(초)

report-data-truncation  DataTruncation을 붙인다. 

report-found-rows  UPDATE실행할 때  mysql_info()가 변경한 레코드수가 아니라 발견한 레코드 수를 리턴하게 함.

multi-queries 클라이언트가  <;>로 연결된 복수의 SQL문을 한번에 보낼 수 있도록 함

multi-results  Result Set를 Multi로 클라이언트가 얻을 수 있도록 함

multi-statements multi-queries와 동일

enable-reads-from-master READ FROM MASTER를 유효로 한다. 

debug[=d:t:o,/tmp/client.trace] 디버그 모드 ./configure --with-debug로 유효화

MySQL의 업그레이드

MySQL의 업그레이드는 단순하다.  순서는 아래와 같다.

1. MySQL서버를 정지한다.

2.바이너리를 교체한다. 

3.메이저 버전업의 경우에는 mysql_fix_privilege_tables 명령어(또는 mysql_upgrade명령어)를 실행한다. 

4.replication이나 NDB를 사용하고 있는 경우에는 전 서버의 버전을 맞추어놓는다. 

mysql_fix_privilege_tables명령어 옵션

 --verbose 상세표시.  이것을 붙여서 사용하는 것을 추천한다.  붙이지 않으면 변경되었나 확인불가

 --bindir= MySQL명령어위치 

 --basedir= MySQL인스톨 디렉토리의 지정

 --user= MySQL서버에 접속할 때 사용하는 사용자명

 --password= MySQL서버에 접속할 때 사용하는 패스워드

 --host= MySQL서버 지정

 --port= MySQL서버 포트 지정

 --socket= MySQL서버의 소켓파일 지정

 --database= 변경하는 데이터베이스 지정. 보통은 mysql  (이 옵션을 사용하는 경우는 드물듯.)

 --sql 메세지에는 SQL문장만 표시

 --no-defaults  my.cnf무시.  반드시 처음에 오는 인수여야한다. 

 --defaults-file= 지정한 my.cnf만을 읽어들임. 반드시 처음에 오는 인수여야한다. 

 --defaults-extra-file=  지정한 my.cnf을 추가해서 읽어들임.  반드시 처음에 오는 인수여야한다. 

위의 명령어를 쓸 때는 아래의 내용을 주의해야 한다. 

  1. my.cnf [mysql_install_db]그룹을 읽어들인다. 

  2. my.cnf [mysql_fix_privilege_tables]그룹을 읽어들인다.

  3. mysql, my_print_defaults 명령어를 호출한다.  (basedir, bindir를 단서로 찾는다)

  4. mysql_fix_privilege_tables.sql파일을 호출한다. (basedir를 단서로 찾는다)

  5. mysql_fix_privilege_tables.sql파일은 보통 share/mysql/디렉토리에 존재한다. (sql문이 있다.)

이 밖에 주의해야 할 사항은 버전간의 언어처리나 미묘한 Storage Engine의 변경이다. 

윈도우즈에 있어서 mysql_fix_privilege_tables

 mysql_fix_privilege_tables은 쉘 스크립트이다.  따라서 윈도우즈에서는 동작하지 않는다. 

윈도우즈에서는 SQL문을 실행해서 권한 테이블을 변경한다. 

   

  윈도우즈에서 MySQL업그레이드 

 dos>cd c:\mysql

 dos>bin\mysql --force mysql <>

Storage Engine변경에 있어서 주의할점

 마이너 버전업의 경우에는 미묘하게 Storage Engine에 수정이 가해진 경우가 있다. 

현재 아래의 내용을 주의.

   

   4.1이하에서 5.0이상으로 변경하는 경우

 1.TEXT필드 인덱스가 바뀌었다.

    >CHECK TABLE [FOR UPGRADE]를 실행해서 에러가 나오는 지 확인(에러가 없는 경우도 있다. )

    >에러가 나오면  MyISAM테이블의 경우 REPAIR TABLE를 실행.  InnoDB테이블의 경우에는 테이블 데이터의 dump와 import를 실시

 2.DECIMAL 내부처리가 바뀌었다.

    >CHECK TABLE[FOR UPGRADE]를 실행해서 에러가 나오는 지 확인.

 3.MyISAM테이블의 RAID옵션이 없어졌다. 

    >테이블 데이터를 dump한 다음에 업그레이드 그리고 데이터를 import한다. 

mysql_upgrade명령어/mysqlcheck 명령어

 MySQL 5.0.19부터 mysql_upgrade라는 명령어가 추가되었다.(5.0.19 ~5.0.22는 쉘 스크립트, 5.0.23부터는 c로 작성되었다.)   이것은 내부에서 다음과 같은 처리를 하는 명령어이다. 

 mysql_upgrade명령어의 내부처리

shell$> mysqlcheck --check-upgrade --all-databases --auto-repair

shell$> mysql_fix_privilege_tables --silent 

mysqlcheck옵션은 다음과 같다. 

1. --check-upgrade

      CHECK TABLE FOR UPGRADE를 실행(FOR UPGRADE는 MySQL 5.0.19에서 추가됨)

          >MySQL 4.1과 5.0에서는 InnoDB, MyISAM에서 TEXT 필드의 인덱스순서를 확인한다. 

          >MySQL 5.0.3과 5.0.5에서는 DECIMAL 저장방식이 바뀌었으므로 확인한다. 

2. --auto-repaire

       테이블 파손의 경우에 REPAIR TABLE를 실행한다. 

MySQL 4.1에서 5.0이상으로 업그레이드 할 경우에는 mysql_upgrade명령어를 실행하면 되는 것로 되어있다.  또 mysql_upgrade는 my.cnf에서의 [mysql_upgrade]그룹을 읽어들이는 것을 주의하길 바란다. 

MySQL 5.0.24/5.1.12-beta의 경우는 mysql_upgrade종료시 데이터 디렉토리에 mysql_upgrade_info라는 파일이 생성된다. 따라서 데이터 디렉토리에 대한 작성권한을 가진 계정으로 mysql_upgrade를 실행해야한다. 

    

 

MySQL레이아웃

MySQL AB 다운로드 사이트에서 입수가능한 Unix계열 OS용 .tar.gz바이너리 파일의 레이아웃은 다음과 같다.

1.  .tar.gz바이너리 파일 레이아웃

/usr/local/mysql/
         ├bin/                                       <- 명령어군          

         ├data/                                     <- data dir          

         ├include/                                <- include파일          

         ├lib/                                        <- 라이브러리          

         ├scripts/                           <- mysql_install_db스크립트가 있는 곳 

         ├share/mysql/charsets/     <- charsets 디렉토리    

         ├share/mysql/언어명/        <- 에러 메세지    

         ├share/*.sql                           <- mysql권한테이블등 작성sql 

/tmp/mysql.sock                           <- 소켓파일 

/etc/my.cnf                                     <- my.cnf파일 

2. 소스를 컴파일 했을 경우 파일 레이아웃

/usr/local/
        ├bin/                               <- 명령어군

        ├libexec/                         <- mysqld가 있는 곳       

        ├var/                               <- data dir

        ├include/mysql/            <- include파일

        ├lib/mysql/                    <-라이브러리

        ├share/mysql/               <-share 디렉토리 

/tmp/mysql.sock                   <- 소켓파일 

/etc/my.cnf                            <- my.cnf파일

3.Windows경우 레이아웃(mysql-noinstall-*.zip바이너리 파일)

c:\mysql\

         ├bin\                   <- 명령어군

         ├data\                <- data dir

         ├include\           <- include파일    

         ├lib\                   <- 라이브러리     

         ├scripts\           <- bash,perl 스크립트   

         ├share\             <- share디렉토리  

c:\my.{ini,cnf}            <- my.cnf파일

%WINDIR%\my.{ini,cnf}         <- my.cnf파일

MySQL의 도입2

윈도우의 경우

windows Installer형식(.msi)하고 zip압축형식 파일로 배포되어지지만, mysql-noinstall-XXXXX.zip형식(예를 들면 mysql-noinstall-5.1.12-beta-win32.zip)을 사용하는 것을 추천한다.   압축을 풀어 생긴 디렉토리를 c:\mysql로 변경한다. 

권한 데이터베이스는  이미 초기상태에 포함되어있다.  생성된 디렉토리를 c:\mysql로 했을 경우 , c:\mysql\data가  그렇다. 

또 Windows용 MySQL서버명령어(mysqld-nt.exe, mysqld-max-nt.exe)에는 Windows Service Control Manager(SCM)에 MySQL서비스를 추가, 삭제하는 옵션이 있다. 

  MySQL인스톨예(Windows경우)

   c:\Document and Settings\>cd \mysql\bin 

   c:\mysql\bin> mysqld-max-nt --install 

 SCM에 서비스 추가,삭제 사용방법은 다음과 같다. 

 

  1.SCM에 서비스 추가 

 mysqld-max-nt --install [서비스명][옵션]

  2.SCM에서 서비스 삭제

 mysqld-max-nt --remove [서비스명]

[서비스명]을 생략하면 MySQL이 서비스명이 된다. 

[옵션]에는 mysqld에 부여하고 싶은 옵션을 1개만 지정가능하다. 

빈번하게 지정하는 옵션은 다음과 같다.

  --defaults-file=

  --defaults-extra-file=

--defaults-file=은, 여기에서 지정한 파일만을 옵션설정파일로 읽어들인다. 

--defaults-extra-file=은 여기에서 지정한 파일을 다른 옵션설정파일과 같이 읽어들인다. 

또 인스톨러가 있는 패키지를 사용했을 경우, 자동적으로 SCM(Service Control Manager)에 서비스가 추가된다.  이럴 때 주의할 점은,  서비스에 추가된 mysqld에는 --defaults-file=옵션이 추가되어 지정한 파일이외의 my.cnf를 전혀 참조하지 않는다는 것이다. 

인스톨러에서 MySQL바이너리 패키지를 인스톨한 다음 c:\my.ini를 편집해도 그 내용이 무시된다. 

 3.인스톨러가 SCM에 등록하는 서비스 예

  c:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld-max-nt.exe --defaults-file="C:/Program Files/MySQL/MySQL Server 5.1/my.ini"

인스톨후 설정

 인스톨한 다음에는 최소한 my.cnf파일에 default-character-set=옵션을 지정해서 서버 character set를 지정해 두어야 한다. 

잠시 쉬어가기

회사 건물 휴게실에서 찍어 본 롯뽄기 힐즈 그리고 자그마하게 보이는 후지산...

MySQL의 도입

MySQL인스톨방법에 대해서 정리해 보겠다. 

MySQL인스톨은 정말 쉽다.  자세한 부분은 매뉴얼이나 다른 서적을 참고하기 바란다. 

인스톨

 MySQL인스톨방법을 Unix계열과 Windows로 나눠서 설명해보겠다. 

 1.Unix계열

  각 플랫폼용 바이너리가 .tar.gz형태로 배포되어 있으므로 그것을 사용하는 것이 간단하다.

 .tar.gz를 /usr/local/아래에 전개해 /usr/local/mysql로 rename하던지 symbolic link을 만든다.

   >MySQL인스톨 예

    root@shell# cd /usr/local

    root@shell# tar zxvf mysql-5.1.12-beta -linux-i686-glibc23.tar.gz

    root@shell# ln -s mysql-5.1.12-beta-linux-i686-glibc23 mysql 

 만약에 한번도 MySQL서버를 사용하지 않았던 환경이라면 권한 데이터베이스를 만들필요가 있다.

   

   >권한 데이터베이스작성예

    root@shell# groupadd mysql

    root@shell# useradd -g mysql -d /usr/local/mysql/data -s /bin/false mysql

    root@shell# cd /usr/local/mysql

    root@shell# ./scripts/mysql_install_db --verbose --user=mysql --datadir=/usr/local/mysql/data 

이 예에서는 MySQL서버를 mysql 유저로 동작시키려고 하고 있으므로 useradd로 OS의 사용자를 추가한다.  그 후에는 mysql_install_db를 실행한다. 

 mysql_install_db의 --datadir은 데이터베이스를 보관하는 디렉토리지정, --user는 데이터베이스 디렉토리의 소유자를 지정한다.  --verbose를 붙이면 실제로 테이블이 생성되었는지 아닌지 알 수 있다.  mysql_install_db는 옵션 설정 파일인 my.cnf내부의 [mysqld],[mysql_install_db]을 참조하므로 주의해야한다.  참조하지 않게 하려면 mysql_install_db의 최초인수에 --no-defaults옵션을 지정하면 된다. 

덧붙여 말하면 , 각 디스트리뷰터가 작성하고 있는 MySQL패키지도입은 그다지 추천하지 않는다. 컴파일할 때의 옵션도 가지가지이고 버전관리도 엉성한게 많고 경우에 따라서는 파일배치가 MySQL표준하고는 동떨어진 것도 있으므로 혼란의 원인이 되기 때문이다. 

 > mysql_install_db옵션

    --no-defaults   my.cnf를 읽지 않는다. 반드시 최초의 인수로 지정되어야 한다. 

    --defaults-file= 지정한 my.cnf를 참조한다. 반드시 최초의 인수로 지정되어야 한다. 

    --force  에러가 있어도 처리를 진행한다. 

    --basedir=  base dir를 지정

    --datadir=  data dir를 지정 

    --skip-name-resolve  호스트명이 아닌 IP어드레스로 권한테이블에 기록한다. 

   --verbose 자세한 출력 

MySQL개요

MySQL은 오픈소스 RDBMS이다.  개발, 서포트, 배포는 기업(MySQL AB)이 관리한다.

MySQL에서는 복수의 데이터저장형식을 가지고 있어 목적(기능,용도)에 따라서 지정할 수 있다.

이러한 기능을 실현한 것이 Multi Storage Engine이다.

처리의 분산은 Multi Thread기법을 이용한다.  또 복수의 character set을 지원하고 있어서 문자코드자동변환기능도 갖춰져있다. 

여러개의 라이센스형태가 있는데 크게 나누면 상용라이센스와 오픈소스 소프트웨어 라이센스가 있다.  

MySQL이 인터넷 상에서 등장한 것은 1995년이다. 지금보다 시스템 리소스가 열악한 환경에서 개발되어서 그런지 심플하고 다루기 쉬운 구조를 지니고 있다.  

초기단계부터 사용해온 사용자중에는 이런 부분에 매력을 느낀 사람도 많다고 들었다. 

최근의 MySQL은 기능 추가확장의 경향이지만 심플한 특징은 지금도 살아있는 느낌이다. 

특히 웹 어플리케이션에서는 LAMP(Linux, Apache, MySQL, PHP/Perl/Python) 조합으로 구축되어지는 경우도(Drupal, Xoops, Mediawiki등등) 많기때문에 MySQL을 사용하는 사람들도 꽤 있으리라 본다. 

SQL사용방법이나 문법해설은 MySQL 매뉴얼에 잘 써 있으므로 이 블로그에는 기능, 구조 부터 구축, 운용면에서 필요한 정보를 적어보려 한다. 

[정보입수처]

MySQL 정보를 입수하려면, MySQL AB사이트가 가장 확실하다. 

http://www.mysql.com

소스코드와 바이너리의 다운로드도 가능하다. 

MySQL은 버전이 바뀌면 이전하고 다른 동작을 하거나 다른 로직이 되어있는 경우가 많다.

그렇기 때문에 옛날 버전의 매뉴얼을 참조하고 있으면 실제로 적용되지 않는 부분도 있게 된다. 

[라이센스]

기본적으로 라이센스에는 상용(유료)하고 GNU General Public Licence version2(GPL v2)가 있다고 생각하면 간단하지만 판매방식에 따라서 이름과 내용등이 바뀌는 경우도 있다. 

상용라이센스와 최근 수년간 몇번인가 바뀌었다. 

판매제품의 부품용도라면 사용라이센스를 선택해야지 싶다.  이런 경우에는 MySQL AB나 파트너기업에서 라이센스를 구입하게 된다. 

상용이용이라고 해도 GPL v2라도 괜찮다면  소스코드나 바이너리를 다운로드해서 그냥 사용해도 무방하다.  특별한 신청은 필요없다. 

GPL v2에 적용되는 것들은 항상 소스코드하고 바이너리를 제공한다. 

GPL v2조항에 따라서 이용, 배포, 변경이 가능하다. 

현재 비상용의 경우  서버, 클라이언트, 클라이언트 라이브러리도 GPL v2다.

자세한 내용은 아래의 URL을 참조.

http://dev.mysql.com/doc/refman/5.1/en/gpl-license.html

 

MySQL분석 시작!

MySQL서버의 기본적인 개념과 구조 , 그리고 구축과 운용에 관한 여러가지 이야기를 써보려한다.

운용과 구축에 대해서는 , 기초를 확실히 이해 해두면 앞으로 버전업그레이드에도 대응이 될 뿐아니라 , 혹시 많은 변화가 있다고 해도 응용력이 생긴다 볼 수 있다.

이런저런 의미로써 기초적인 것부터 시작하려 한다.

그렇지만 다루는 토픽에는 조금 매니아적인 부분도 써나가려 한다.

이것은 최근의 MySQL기능의 추가도 그렇지만 MySQL사용자층도 두터워졌다고 생각때문이다.

오픈소스 소프트웨어사용의 즐거움은 소스를 보고,소스를 수정가능하다라는 부분도 있으니
그 부분도 이야기 하고 싶다.

MySQL AB회사가 Sun Microsystems에게 매수된 기사가 이 글을 쓰게되 결정적인 기회라고 할 까?

열심히 써 보고 싶다.