Search Results

Search Results for 'MYSQL' : 2 POSTS

  1. 2011.01.26 Mysql Replication 으로 분산처리 하기
  2. 2010.10.25 MYSQL 쿼리시 ENUM의 ORDER BY

Mysql Replication 으로 분산처리 하기

MySQL Replication(복제)

MySQL Replication(복제)  개나 2 이상의 MySQL database server(slave) 하나의 MySQL database server(master) 부터 데이터를 복제해   있는 기능을 제공한다.

MySQL Replication 비동기 방식으로 처리된다 slave master로부터 데이터를 받아 복제하기 위해 항상 master 연결되어 있을 필요가 없다.

 

MySQL Replication Binary logging mechanism 사용하여 이뤄진다.

Master 서버는(MySQL 인스턴스) binary log 변경된 데이터 정보를 기록하며  log slave 읽어서 실행함으로써 복제가 된다.

 

Master에서 binary logging 활성화되면 Master 모든 데이터 구문이 bindary log 저장되며 slave bindary log 모든 내용을 복사해서 읽어온다.

따라서 slave log 파일내의 position 유지할 필요가 있다그래야 로그파일 전체를 처음

부터 읽지 않고 효과적으로 로그 파일을 운영할  있다.

여기서 position 로그파일내 위치를 의미하며 어느 부분부터 읽겠다는 것을 의미 한다.

 

Configuration 따라 다음과 같은 단위로 복제가 이뤄질  있다.

l  all database

l  selected database

l  selected tables within a database

 

 

 

Replication 구성  – Appendix A.


 

Replication Configuration

 

[ Master Configuration ]

1.  Replication User 생성
slave
 master 접속하여 데이터를 복제하기 위한 MySQL 계정이 필요하다. root 사용해도 상관 없지만 slave Replication 설정을 하면(slave configureation 참조) 계정 정보가 암호화되지 않은 텍스트 형태로 slave 서버의 master.info(mysql\data) 파일에 기록이 되기 때문에 보안상 root 기타 계정을 사용하는 것을 권하지 않는다따라서 복제를 위한 계정을 하나 생성한다.
 계정은 단지 REPLICATION SLAVE Privilege 있으면 되므로 다음과 같이 계정을 생성한다.(REPLICATION SLAVE Privilege 있으면 된다는 의미는 INSERT/UPDATE 등과 같은 Privilege 필요 없다는 의미이다따라서 복제 계정은 mysql query실행을  없다.)

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%' IDENTIFIED BY 'slavepass';

è  repl 계정이며 slavepass 계정의 비밀번호 이다.

è  %대신 IP주소를 넣으면  IP로부터 접속하는 slave 대해서만 접속을 허용하겠다는 의미(그냥 % 사용하자..!)

n  ) ….. ON *.* TO 'repl'@'1.1.1.2' IDENTIFIED BY 'slavepass';

 

2.  Configuration 설정(my.ini 또는 my.cfg)

[mysqld]

log-bin=mysql-bin

server-id=1

è  server-id 1~(2^32)-1내의 숫자중 아무것이나 설정해도 된다.

è  log-bin=mysql-bin는 바이너리 로그파일의 생성경로이며 log-bin만 기입시 기본 디렉토리에 생성된다.

3.  MySQL 데몬 재시작

 

4.  Master 정보 보기

mysql> FLSUSH TABLES WITH READ LOCCK;

mysql> SHOW MASTER STATUS;

+------------------+----------+--------------+------------------+

| File                | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+------------------+----------+--------------+------------------+

| mysql-bin.000001 |       98   |                 |                     |

+------------------+----------+--------------+------------------+

è  File : 로그 파일을 의미한다.

è  Position : 로그 파일내 읽을 위치

è  Binlog_Do_DB : binary log 파일(변경된 이벤트 정보가 쌓이는 파일)

è  Binlog_Ignore_DB : 복제 제외 정보

n  Binlog_Do_DB Binlog_Ignore_DB Slave 시작하기 전까지는 나타나지 않는다.


 

[ Slave Configuration ]

1.  Configuration 설정

[mysqld]

server-id=2

replicate-do-db=’database name’


# master-host=xxx.xxx.xxx.xxx  --> 마스터의 IP

master-user=xxxx  --> 리플리케이션용 ID

master-password=xxxx  --> 패스워드

master-port=3306  --> 접속할 포트번호 (일반적인 포트 3306)

è  slave server-id 정의한다 1~(2^32)-1내의 숫자중 아무것이나 설정해도 되나 Master와는 다르게 한다.

è  replicate-do-db: 복제할 데이터베이스를 의미한다.

n  2 이상의 데이터 베이스 복제를 원하면 replicate-do-db  추가한다.

 

2.  database dump

복제할 데이터베이스를 master로부터 dump하여 넣는다.

 

3.  CHANGE MASTER TO

Master 연결하기 위한 정보를 다음과 같이 설정한다.

mysql> CHANGE MASTER TO

MASTER_HOST='Master server host name or Master server IP',

MASTER_USER='replication user',

MASTER_PASSWORD='replication password',

MASTER_LOG_FILE='Log File name',

MASTER_LOG_POS=position;

è  MASTER_HOST: Master 서버의 정보를 입력한다.

è  MASTER_USER: replication 위해 생성한 계정 ID

è  MASTER_PASSWORD: replication 위해 생성한 계정 비밀번호

è  MASTER_LOG_FILE: SHOW MASTER STATUS에서 보이는 로그 파일 

è  MASTER_LOG_POS: SHOW MASTER STATUS에서 보이는 position

n  SHOW MASTER STATUS master에서 실행해야 한다.(master 설정 참고)

 

4.  MySQL 데몬 재시작

 

 Slave 실행이 되면(MySQL 데몬 시작 또는 slave start) master 접속하기 위한 정보를 master.info(mysql\data)에서 읽어 온다만일 master.info 아무런 정보가 없으면 my.ini 참고하여 master.info 연결정보를 기록한다.

여기서 주의할 점은 이미 master.info 정보가 있으면 my.ini 참조하지 않으므로 my.ini정보를 수정해도 master 연결시 반영되지 않는다.

그러나 CHANGE MASTER TO 이용하면 master.info 바로 변경한다.

따라서 master 연결정보는 my.ini 설정하는  보다는 CHANGE MASTER TO 이용하여 설정하는게 낫다.

 

다음과 같은 option CHANGE MASTER TO에서 사용된다.

master-host

master-user

master-password

master-port

master-connect-retry

master-ssl

master-ssl-ca

master-ssl-capath

master-ssl-cert

master-ssl-cipher

master-ssl-key

 

 

 

[양방향 동기화 처리]

Master서버에서 Slave 구현하고자 한다면 다음과 같은 방법으로 처리

1. 현재 Slave서버에 replication 계정 생성

2. 현재 Slave my.ini 변경

log-bin=mysql-bin 추가

3. 현재 SLAVE 서버 데몬 재시작

4. 현재 Master my.ini 변경

replicate-do-db=’database name’추가

4. 현재 Master에서 CHANGE MASTER TO 실행

5. 현재 Master 서버 데몬 재시작

 

 


 

Replication Monitoring

//ON Master

mysql> SHOW PROCESSLIST\G

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

Id: 11

User: repl

Host: 192.168.1.22:3556

db: NULL

Command: Binlog Dump

Time: 21960

State: Has sent all binlog to slave; waiting for binlog to be updated

Info: NULL

Slave 192.168.1.22 repl계정으로 thread11 연결되어 있음을 보여준다.

 

 

//On Slave

mysql> show processlist\G

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

     Id: 1

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 23049

  State: Waiting for master to send event

   Info: NULL

*************************** 2. row ***************************

     Id: 2

   User: system user

   Host:

     db: NULL

Command: Connect

   Time: 4294967289

  State: Has read all relay log; waiting for the slave I/O thread to update it

   Info: NULL

Id 1: Master 서버와 통신하기 위한 I/O Thread

Id 2: update 내용을 처리하기 위한 SQL Thread

 2개의 Thread 오류가 발생하면 안된다.

 

 

mysql> show slave status\G;

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

             Slave_IO_State: Waiting for master to send event

                Master_Host: 192.168.1.14

                Master_User: repl

                Master_Port: 3306

              Connect_Retry: 60

            Master_Log_File: mysql-bin.000004

        Read_Master_Log_Pos: 9187563

             Relay_Log_File: shin-relay-bin.000013

              Relay_Log_Pos: 9187700

      Relay_Master_Log_File: mysql-bin.000004

           Slave_IO_Running: Yes

          Slave_SQL_Running: Yes

            Replicate_Do_DB: ipm3

        Replicate_Ignore_DB:

         Replicate_Do_Table:

     Replicate_Ignore_Table:

    Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

                 Last_Errno: 0

                 Last_Error:

               Skip_Counter: 0

        Exec_Master_Log_Pos: 9187563

            Relay_Log_Space: 9187700

            Until_Condition: None

             Until_Log_File:

              Until_Log_Pos: 0

         Master_SSL_Allowed: No

         Master_SSL_CA_File:

         Master_SSL_CA_Path:

            Master_SSL_Cert:

          Master_SSL_Cipher:

             Master_SSL_Key:

      Seconds_Behind_Master: 0

Slave_IO_State: 현재 Slave 상태를 나타낸다.(Appendix B 참고)

Slave_IO_Running: I/O Thread 상태

Slave_SQL_Running: SQL Thread 상태

Last Error: 최근에 발생한 오류정상인 경우  값은 없다.

Seconds_BeHind_Master:  값이 크면 클수록 Master로부터 복제할  없는 데이터가 많음을 나타낸다.

 

 

mysql> stop slave

mysql> start slave

MySQL 데몬(서비스)  시작하면 slave 자동으로 시작된다.(my.ini 옵션 skip-slave-start 있으면 자동 시작 안한다.)



[기타 알아두어야 할 내용]

1. 안정성을 위해서 두대의 DB서버의 버전을 동일하게 맞추어 주는것이 좋다.

2. 버전이 다른경우 높은 버전은 Slave 만 가능하다.

3. 서버는 Master 를 먼저 시작한 후 Slave를 시작시킨다.

4. Master의 status 에 지정된 File 이외의 로그파일은 삭제해도 무방하다.

5. Master의 status의 Position과 Slave status의 Read_Master_Log_Pos는 동일해야 한다.

6. 서버환경, 계정이 바뀐후에는 데이터 디렉토리안의 master.info를 변경하거나 제거한 후 재시작을 해야한다.

7. Master의 Replication로그나 Slave의 relay-bin로그는 vi로는 읽을수 없으며 아래와 같은 방법으로 변환후에 읽을 수 있다.


# mysqlbinlog KRDAC1FLD001-relay-bin.000002 > log.sql

8. Insert 이후 Slave 동기화 도중 데이터 조회가 이루어 져야 하는 상황에는 해당 경우만 Master DB에서 보게 하거나 process 종료 후 sleep 타임을 잠시주어 해결하면 된다



1. 구성도



MySQL 리플리케이션은 이중화의 역할 보다는 부하 분산의 역할을 한다고 볼 수 있다. 이 구성은 데이터베이스의 데이터를 갱신(입력/수정/삭제 등) 및 조회(검색 등)하는 비율에 따라 도입 유무를 판단 할 수 있다. 물론, MySQL 리플리케이션 도입에 가장 이상적인 서비스는 대형 포털 사이트와 같은 조회형 서비스에 적합하다.


2. 슬레이브 서버 별로 데이터 분산



MySQL 리플리케이션에서 슬레이브 서버마다 데이터를 동일하게 가져갈 수 있지만 이처럼 각각의 데이타를 가지고 있을 수 있다. 이와 같이 데이터를 나눠 주는 이유는 부하 분산의 차원이다.


3. 이중마스터 서버



MySQL 리플리케이션에 이중 마스터 서버 구성으로 하는 이유는 슬레이브 서버로 인한 마스터의 서버의 부하를 분산하는 차원이다. 여기서 마스터2 서버는 각 슬레이브에 대해서 마스터 서버의 역할을 수행하긴 하나 마스터1 서버에 대해서는 슬레이블 서버의 역할을 하고 있다.


4. 장애 대처



MySQL 정상적인 서비스가 진행 중이다. 평시에는 슬레이브 서버 중 하나를 백업용으로 활용하는 것도 좋다.


5. 장애복구



MySQL 리플리케이션 서비스 중에 마스터 서버에 장애가 발생하였다면, 슬레이브 서버 중 하나를 마스터 서버로 전환을 하여 서비스를 정상적으로 복구 할 수 있다. 추후 장애가난 마스터 서버가 정상화 되면 슬레이브 서버 중 하나로 역할을 수행하게 만든다.



출처 : http://hanaduri.egloos.com/2389708


RSS :
Response
0 Trackbacks 0 Comments

MYSQL 쿼리시 ENUM의 ORDER BY

SQL 컬럼 형식이 ENUM 일때 ORDER BY 의 순서는

필드 내용의 오름차순, 내림차순이 아닌 ENUM 형식의 선택값의 순서에 따라 정렬된다

필드 종류
varchar(10)
enum('standard','reserve')


SELECT
 * 
FROM `test` 
ORDER BY `test`.`varchar` ASC 
LIMIT 0 , 30

varchar  enum
reserve reserve
standard2 standard


SELECT * 
FROM `test` 
ORDER BY `test`.`enum` ASC 
LIMIT 0 , 30

varchar enum 
standard standard
reserve2 reserve

RSS :
Response
0 Trackbacks 0 Comments