Drawing the world RSS FEED

이 문제로 많은 고민을 했었는데

MySQL은 SQL Server나 Oracle과 다르게 SP가 처음 한번만 컴파일 되고 재사용 되는것이 아니다.

http://www.joinfu.com/2010/05/mysql-stored-procedures-aint-all-that/

위 링크를 참고하면 커넥션 별로 컴파일이 관리되는데 말인데

즉 어플단에서 쿼리로 실행하나 SP로 실행시키나 컴파일하고 실행하는 과정에서 성능상 이득을 취하긴 어렵다는것!

물론 SP를 사용함으로서 프로그램과 데이터 조작과의 관계를 분리 할 수 있고(추후 SQL 튜닝 및 스키마 수정에 유리)

SP실행 권한만 줌으로서 인젝션과 같은 공격에서 상대적으로 안전하며

짧은 요청 문장으로 인한 트래픽 감소

트리거를 사용하지 않고도 데이터의 참조 무결성 유지

http://ko.wikipedia.org/wiki/%EC%A0%80%EC%9E%A5_%ED%94%84%EB%A1%9C%EC%8B%9C%EC%A0%80

등의 장점은 여전히 남아 있다.

그러면 컴파일 된 프로시저를 그냥 아깝게 버리느냐... 하면

mysql-proxy나 sqlrelay 등을 사용해서 Connection Pool을 구축한다면 커넥션을 한번만 맺고 재사용하기에 극복 할 수 있으니

MySQL을 사용하면 SP는 사용하면 안된다 하지 말고 커넥션 풀을 구축해서 사용하면 커넥션 비용도 감소하고 SP 컴파일도 재사용 가능하니 일석이조의 효과가!!

http://sqlrelay.sourceforge.net/

http://dev.mysql.com/downloads/mysql-proxy/

MySQL에서 복제시 binlog_format 에 따라 동기화가 되지 않을 수 도 있다.

5.1 버전대에 추가된 기능으로 기존에는 Statement로 동작한다.

binlog_format 는 설정 파일 혹은 글로벌 변수로 변경 가능하고 값은

Statement, Row, MIXED 세가지가 들어갈 수 있는데

Statement 는 마스터에 들어온 쿼리를 쿼리문으로 기록하기 때문에

- 쿼리는 짧고 데이터 변경은 많은 종류가 많이 실행될 때 유리할 수 있으며

- 일반적으로 로그 양이 작고(master - slave간 회선이 느릴때 유리)

- 나중에 열어봤을때 알아보기가 상대적으로 쉽다 (일부 쿼리만 사용 가능)

- 버젼이 서로 달라도 사용 가능

-*항상 일관된 값이 나오지 않는 함수의 실행을 보장 할 수 없다. ( NOW()는 정상 작동하지만 SYSDATE()는 동기화 되지않을 수 있다.)

- 상대적으로 느리다(보통 쿼리의 실행이 데이터 변경보다 크기 때문에)

ROW는 변경된 데이터를 기록하는 방식으로

Statement 와 장단점이 반대라 볼 수 있으며 SYSDATE() 의 경우도 일관되게 복제된다.

MIXED는 서로의 장점을 취하는 방법으로

기본적으로는 Statement 필요에 따라 ROW로 기록하는 방식

운영 중간에 설정을 바꿀 수 있으나

임시테이블 사용중, 트리거나 프로시저에서 변경 시도, NDB가 활성화 되어 있을때는

변경이 불가하다.

잘 모르겠으면 MIXED나 ROW로 설정하는 편이 동기화 오류로 고생하는 일이 없을것 같다.

master와 slave의 연결을 끊었다 이었다 하는 식으로 데이터 동기화를 지연 시키면서

now() 함수와 sysdate() 함수로 데이터를 넣을 때 결과다

왼쪽 master 오른쪽 slave

1 = MIXED,        now()

2 = MIXED,        sysdate()

3 = MIXED,        sysdate()

4 = Statement ,  sysdate()

5 = Statement ,  now()

6 = ROW,          now()

7 = ROW,          sysdate()

http://www.mysqlkorea.com/sub.html?mcode=develop&scode=51ha&m_no=22902&cat1=1106&cat2=1287&cat3=1377&lang=k

Mysql과 MariaDB에 핫백업(Online Backup)을 지원하는 툴 중 무료에 쓸만한 툴은 Percona의 Xtrabackup이 제일 유명할 것이다.

하지만 한글로 정리된 자료 중 예와 함께 정리된 자료를 찾기 힘들어 이렇게 정리한다.

1) 백업 수행 계정

우선 백업하는 OS 계정은 root 혹은 mysql로 하는 것이 정신 건강상 편하다(보안을 위해 가능한 mysql계정으로)

설치 방법에 따라 다르지만 ubuntu에서 apt-get으로 설치했다고 하면 아마도 다음과 같이 소유권이 생성되어 있을 것이다.

여기서 소유권이 모두 접근 가능해야 하기 때문에 권한 설정을 잘 해 놓아야 하기 때문에 다른계정은 설정이 쉽지 않다.

(dba그룹을 만들고 참여하게 한다든지 방법이 없지는 않다.)

2) 모든 DB 백업

ex)

innobackupex  <백업경로>

위와 같이 하면 백업경로 아래에 시간 폴더가 생성되며 백업이 저장된다

(os계정 로그인에 관리자 권한이 없다면 "--user <관리자계정> --password <암호>"를 추가하자)

3) 로그 백업 (로그를 기존 백업에 수정된 내용이 반영됨)

ex)

innobackupex --apply-log <백업경로>/<시각폴더>

4) 모든 DB 복원

데이터 폴더 이름을 바꿔놓고(만약을 위해) 새로 생성후(빈 폴더) 권한을 바꿔주는 것이 포인트!

ex)

service mysql stop
mv /var/lib/mysql /var/lib/mysql_old
mkdir /var/lib/mysql
chown mysql:mysql /var/lib/mysql
innobackupex --copy-back <백업경로>
chown -R mysql:mysql /var/lib/mysql
service mysql start

5) 특정 DB복원

Database파일이 손상되었다면 그대로, Drop 하였다면 같은 이름으로 생성한(show databases에서 보이도록) 상태에서

ex)

service mysql stop
mv /var/lib/mysql/<대상디비명> /var/lib/mysql/<대상디비명>_old
cp -r <백업경로>/<대상디비명> /var/lib/mysql/<대상디비명>
chown -R mysql:mysql /var/lib/mysql/<대상디비명>
service mysql start

아마 여기까지 했다면 Database에 접속은 되지만 Table Select가 안될 것이다

찾아보면 서버에서 갖고 있는 테이블 정보와 매칭이 안되서라고 하니 다음을 수행시키자

확인해 보면 아마 복원 되어 있을 것이다

6) 특정 Table 복원

맹락은 특정 Database복원과 같다

show tables로 보이도록 만들어 두고

ex)

service mysql stop
mv /var/lib/mysql/<대상디비명>/<대상테이블명> /var/lib/mysql/<대상디비명>/<대상테이블명>_old
cp -r <백업경로>/<대상디비명>/<대상테이블명> /var/lib/mysql/<대상디비명>/<대상테이블명>
chown -R mysql:mysql /var/lib/mysql/<대상디비명>/<대상테이블명>
service mysql start

아마 여기까지 했다면 Database에 접속은 되지만 Table Select가 안될 것이다

mysqlcheck --all-databases

확인해 보면 아마 복원 되어 있을 것이다