5 posts tagged with "mysql"

DBA는 아니지만 나중에 필요할 것 같기도 하고 실무 관점에서 직접 구성해보는게 좋을 것 같다는 생각에 DB 이중화를 해보기로 했다. 연습용 DB를 대상으로 하다보니 이중화 구성이 생각보다 간단했고 실제 운영을 하면서 마주칠 수 있는 복잡한 사례와 트러블슈팅을 통해 얻을 수 있는 노하우가 중요할 것 같다. 구성하고자 하는 최종 형태는 아래 그림과 같은 모습인데

우선 DB만 master과 slave로 분리하려면 작업을 크게 네가지 정도로 나눌 수 있을 것 같다.

(설정과 관련된 내용은 MySQL 5.5 공식문서에서 참고함)

1. Master / slave 설정 변경​

설정파일인 my.cnf에서 공통으로 변경해야 하는 내용은 server-id이다. Replication 구성할 master와 slave 들간의 server-id가 중복되어서는 안된다. 추가로 master에서는 log-bin 설정이 필요한데 바이너리 로그파일의 경로나 파일명을 지정하는 설정이다. 바이너리 로그는 DB에 가해지는 변경사항(CUD) 기록이 별도로 저장되는 파일을 의미하는데 master에서만 생성하도록 하는게 일반적이고 slave는 바이너리 로그를 받아와서 릴레이 로그로 저장하게 된다. Slave 입장에서 replication은 두 개의 thread로 동작되는데 하나는 바이너리 로그를 받아와서 릴레이 로그로 저장만 하는 I/O thread이고, 두번째는 릴레이 로그를 DB에 직접 반영하는 작업을 수행하는 SQL thread 이다. my.cnf 파일에서 추가된 내용은 아래와 같다.

# Master
[mysqld]
server-id = 1
log-bin = mysql-bin

# Slave1
[mysqld]
server-id = 2

바이너리 로그로 mysql-bin을 설정하면서 경로를 지정하지 않았기 때문에 실제 DB 파일이 저장되는 경로 (Ubuntu 14.04의 경우 /var/lib/mysql)에 mysql-bin.00000x 형태의 파일로 저장되게 된다.

2. Replication 권한 추가​

실제 replication을 수행할 계정이 필요한데 기존에 존재하는 계정을 이용한다면 권한만 추가해도 무방하고 신규 계정을 만들어서 처리해도 되겠다. 나의 경우엔 repl이라는 계정을 생성하기로 했다.

GRANT REPLICATION SLAVE ON *.* TO 'repl'@'192.168.100.0/ 255.255.255.0' IDENTIFIED BY 'test';

MySQL master에 접속해서 위와 같이 입력하면 192.168.100.0/24 대역에서 repl이라는 계정이 접근해 replication을 수행할 수 있다. 이미 있는 계정을 이용한다면 REPLICATION SLAVE 권한만 부여하면 되겠다.

3. DB dump / 적용​

Replication을 시작하기 전에 master와 slave를 동일한 상태로 만들어줘야 하기 때문에 master의 dump를 떠서 slave에 적용해야 한다. 실제 DB 파일을 그대로 복사하는 방법도 있는데 별도로 dump 파일을 만들기로 했다. 이 때 중요한게 dump 파일을 만든 이후에 master에 변경사항이 생기면 안되기 때문에 변경이 발생하지 않도록 조치해두어야 한다. MySQL 서비스를 중단하는 방법도 있지만 테이블에 lock을 거는 방법도 있길래 시도해봤다.

FLUSH TABLES WITH READ LOCK;

MySQL master에 접속해서 위의 쿼리를 실행하면 lock이 걸리는데 이후에 다른 세션에서 데이터를 변경하고 commit을 수행하면 pending 상태로 유지되기 때문에 실제 DB에는 변경이 발생하지 않는다. (DB 엔진 종류에 따라 다르다는 얘기도 있는데 InnoDB에서는 가능) 위의 쿼리를 실행한 세션을 종료하면 lock이 풀린다고 공식문서에 나와있어서 저 세션은 그대로 둔 상태로 dump를 뜬다. (root 계정을 사용했음)

mysqldump -u root -p --all-databases > dump.db

--master-data 옵션을 사용하면 slave가 연결할 master 정보까지 dump에 반영해서 적용시 자동으로 지정가능하다고는 하는데 직접 master 지정까지 해보기 위해 사용하지는 않았다. 생성된 dump가 어느 지점까지의 데이터인지 알아야하기 때문에 master에서 추가로 아래의 쿼리를 수행한다.

SHOW MASTER STATUS;

이 쿼리를 실행하면 사용중인 바이너리 로그와 마지막 position 값을 얻을 수 있다. (아래 그림 참조)

Slave에서 사용해야 하므로 별도로 기록해두고 lock을 푼다.

UNLOCK TABLES;

이제 master는 동작하던대로 계속 작업을 수행하게 될테니 dump를 slave에 복사하고 dump를 적용하면 세번째 과정은 완료된다.

mysql -u root -p < dump.db

4. Master 지정과 replication 시작​

Master와 slave가 동일한 상태가 되었으므로 (master에 변경사항이 발생하고 있더라도 어느 지점까지 가져왔는지 이미 기록해두었기 때문에 문제가 없다) slave에서 replication 작업을 수행할 master를 지정하고 replication 작업을 시작하는 일만 남았다. Slave에 접속해서 아래의 쿼리를 수행하면 master가 지정된다.

CHANGE MASTER TO
master_host = '192.168.100.52',
master_user = 'repl',
master_password = 'test',
master_log_file = 'mysql-bin.000001',
master_log_pos = 730;

Master 접근에 대한 기본 정보와 과정3에서 별도로 기록해둔 바이너리 로그 파일을 master_log_file에 position을 master_log_pos 항목에 지정하면 되겠다. 이 과정까지 문제없이 진행되었다면 아래의 쿼리로 replication을 시작할 수 있다.

START SLAVE;

Slave가 정상적으로 실행되었는지 여부는 slave status에서 Slave_IO_Running과 Slave_SQL_Running 항목이 Yes인지 Last_Errno 등의 오류 정보 필드에 유효한 내용이 있는지를 보고 판단할 수 있다.

빌드와 배포를 자동화하는 작업을 진행하는 도중에 Database에 data를 쓰고 업데이트 하는 기능이 필요하게 되었다. 이 경우 여러가지 방법이 있을 수 있지만 최대한 간단하게, 추가 개발없이 진행하고 싶었다. (Shell script에서)

1. 과거에 사용했던 방법들

간단히 Java로 DB와 connection을 맺고 CRUD를 하는 모듈을 개발해서 사용했던 적이 있다. Jar로 묶어 놓으면 script에서는 jar에 parameter만 몇 개 넘겨서 실행하면 가능했다. 하지만 난 그 당시의 소스를 가지고 있지 않고 간단하더라도 개발을 추가로 하기가 귀찮았다. 시간도 없었고.

두번째로 groovy script를 사용했었다. Groovy 자체가 java와 유사한 부분이 많기 때문에 작성이 어렵지 않았고 그렇게 만들어진 groovy script를 shell에서 호출하는 것도 어렵지는 않다. 하지만 역시 추가로 script를 구현하는게 번거롭고 어딘가에 script를 보관하고 사용해야 한다는게 마음에 걸렸다. 난 단지 하나의 shell script로만 해당 기능을 구현하고 싶었다.

마지막으로 사용해봤던 방법은 REST API를 구현하고 shell script에서는 curl을 이용해 REST API를 호출하는 방법인데, 꽤 괜찮은 방법이 아닌가 생각했었지만 역시 REST API를 구현해야 하는 부담이 있었다. 하지만 대규모 개발이 아니라면 node.js로 충분히 할만하다. (간단하면 개발하는데 그리 오래 걸리지도 않는다)

2. Command line에서 query 실행하기

그동안 사용할 일이 없었기 때문에 MySQL client 실행옵션 중에 -e가 있다는 걸 이번에 알았다. -e 옵션은 뒤에 오는 query 문을 실행해주는 역할을 하는데 이 옵션만 사용하더라도 단 한 줄의 명령으로 원하는 기능을 수행하게 만들 수 있다.

3. Password prompt

한 줄의 명령으로 원하는 기능에 근접했지만 항상 password prompt가 뜨는 문제에 봉착했다. expect를 사용하거나 하면 되겠지만 그것보다 간단한 방법을 원했다. 그리고 찾은게

mysql -uuser_id -ppassword

-p 옵션에 password를 붙여쓰면 따로 묻지 않는다. 그 사이에 공백이 있다면 password prompt를 띄우라는 걸로 인식하고 뒤에 있는 password를 사용하려는 DB schema로 인식한다.

4. Inserted id를 알고 싶어요

또 다른 문제. DB에 insert 하고 특정 작업 이후에 insert된 레코드를 update 해야 했는데 id 이외에 유일한 값을 특정하기가 어려웠다. 강제로라도 유일한 값을 만들수는 있을 것 같았는데 그것을 위해 DB 구조를 변경해야 할 것 같아서 마음에 들지 않았다. 그래서 생각한 방법은 insert 이후에 아래의 query를 바로 실행하는 것이었다.

select last_insert_id();

마지막으로 추가된 id를 알려준다.

5. Shell script에서 text로 된 query 결과를 parsing 해야 하는가?

위의 문제를 해결하니 다른 문제가 등장했다. MySQL client에서 직접 select query를 실행하면 결과를 mysql만의 형태로 돌려준다는 것이었다. Json이나 xml과 같은 정규화된 문자열이 아니라 특수문자로 column의 경계가 그려진 문자열들. 처음엔 이것을 parsing 해야 하나 생각했는데 말도 안되는거라 생각했다. 의미도 없고. 그래서 찾은게 -s와 -N 옵션인데, -s는 silent를 -N은 column name 출력을 하지 않는다. 어차피 마지막에 추가된 id만 알면 되기 때문에 column은 단 하나뿐이고 진정 내가 원하는 것은 그 column의 값일 뿐이다. 두 옵션을 사용해 query를 실행하면 id만 달랑 떨어진다.

6. 결과

mysql -uuser_id -ppassword db_schema -s -N -e "insert_query_with_last_insert_id"

위의 형태로 실행하면 password를 묻지도 않고 insert를 한 후 추가된 id만 받을 수 있다. 단 한줄만으로.

Password가 공개될 여지가 있는데 insert 하려는 값을 parameter로 받는 별도의 script 파일로 만들어서 읽기권한을 제어하는 방법을 사용하는게 어떨까 생각한다. 그게 간단하니까.

Java sql package 안에 ResultSetMetaData 라는 클래스가 존재한다.

ResultSet 으로부터 metadata 정보를 저장해놓고 사용할 수 있는 용도인데 정의된 함수 사용 중에 이상한 현상이 발견되어서 다른 함수를 사용하는 것으로 변경하게 되었다.

사용중인 DB는 MySQL로 테스트용으로는 5.5.28, 실제 DB는 5.0.67 이었는데 MySQL version 차이에 따라 함수 호출 결과가 달랐는데

정확히는 getColumnName 함수에서 이상한 현상을 발견할 수 있었다.

View table의 metadata를 읽어서 column name을 보면 AS로 이름을 변경한 column name이 원래 이름으로 나타나는 것이 아닌가.

그것도 테스트했던 상위 버전의 MySQL에서는 문제가 없었고 5.0.67 에서만 말썽이었다.

결국 호출하는 함수를 getColumnName에서 getColumnLabel로 변경하는 것으로 해결했다.

getColumnLabel은 AS로 이름을 변경하지 않은 column의 경우 getColumnName과 결과가 동일하다고 문서에 되어있으니 사용해도 무방할 듯 싶었고

사실 이런 경우 뿐만이 아니라 일반적인 경우에도 getColumnLabel을 사용하는게 정확한 방법일 것 같다.

그런데 왠지 column 이름을 얻을 때 getColumnLabel 보다 getColumnName을 호출하고 싶어진다.

이런게 함수 명명의 중요성?

MySQL 관리도구 중에 많이 사용하던게 sqlyog 이었다.

원래 free 였는데 어느 순간 Webyog 이라는 이름의 페이지에서 상용으로 판매하기 시작했고 30일짜리 trial을 쓸 수 밖에 없는 상황이었다.

그렇게 이런저런 툴을 전전하다 알게 된 사실.

SQLyog이 community edition이 있단다.

Code google에서 호스팅하는 프로젝트로...

http://code.google.com/p/sqlyog/

신난다.

근데 맥용은 없는건가?

국내에서는 많이 사용하지 않는 Open source RDBMS인 PostgreSQL을 잠깐 사용해 볼 일이 생겼다.

설치는 어렵지 않았는데, 테이블 생성할 때 까지 몇가지 생소한게 있었다.

1. 접근 권한

PostgreSQL이 설치된 장비에서 관리도구로 접속하는건 문제가 없는데 다른 장비, PC에서 접속하는건 불가능했다.

이유는 pg_hba.conf 라는 설정파일에 존재하는 접근 권한이 기본은 다른 장비에 대해서는 막고 있기 때문이었는데 접속하려는 장비의 IP 대역이나 고정된 IP 값으로 풀어줘야 한다.

pg_hba.conf 파일 위치도 처음엔 몰라서 헤맸는데 설치 경로의 data directory 안에 존재한다.

OS X에서 설치했을 땐 data directory의 owner와 group이 PostgreSQL에서 관리되는 계정과 그룹이라 sudo 권한으로 vi 편집기로 수정해서 사용할 수 밖에 없었는데, 기본 제공되는 관리도구인 pgAdmin이 보여준 몇 가지 문제점 때문이기도 했다.

2. Auto increment

MySQL에서는 column 생성할 때 auto increment 속성 지정이 가능했던 것으로 기억한다. 속성을 지정하면 따로 값을 insert 하지 않아도 초기값부터 증분만큼 자동 증가하게 되어있는데 PostgreSQL은 아무리 뒤져도 그런 속성이나 혹시나 해서 찾아본 data type에도 존재하지 않았다.

PostgreSQL에서는 auto increment 하기 위한 sequence 라는 것을 먼저 정의하고 column 생성시 그 sequence를 nextval 이라는 내장 함수로 호출하도록 해야 한다.

결론

잠깐 살펴봤을 때 PostgreSQL은 전체적으로 관리나 DB 설계 측면에서 좀 더 세밀한 설정과 구축을 하도록 권유하는 느낌이다.

PostgreSQL과 MySQL의 장단점, 뭐가 좋으냐 나쁘냐를 두고 인터넷 여기저기에서 말이 많은데, 결국은 일정과 규모에 따라서 나에게 익숙하거나 trouble shooting에 필요할지 모를 reference가 많으냐 적으냐에 따라서 골라쓰면 되지 않을까? 라는 생각을 잠깐 해본다. DB만 전문적으로 하시는 분들은 뭣도 모르는 소리라고 할지 모르겠지만 나 같은 초보자야 뭐.

Updated : 2013-04-16

serial과 bigserial type은 true type은 아니고 각각 4byte와 8byte data 저장이 가능하다. PostgreSQL Document에 따르면 다른 RDBMS의 auto_increment와 유사한 속성을 가지는데 아래의 두 query가 동일한 의미를 갖는다고 되어있다.

CREATE TABLE tablename (
    colname SERIAL
);

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;

댓글을 달아주신 분의 말씀처럼 auto_increment 대신 사용이 가능하다.