[MySQL] 16. Replication

* replication은 asynchronous by default 이다.

* master의 binary log를 slave가 읽어가는 방식으로 동작한다.

* skip-networking 옵션이 master와 slave에 모두 꺼져 있어야 한다.

* master에서 event를 읽어오는 시점으로 event timestamp를 수정한다(show processlist의 Time column으로 확인 가능).

* master의 업데이트를 slave가 따라갈 때 까지 멈추는 방법
1. master에서 아래 명령 실행
mysql> flush tables with read lock;
mysql> show master status;
2. slave에서 아래 명령 실행
mysql> select master_pos_wait('log_name', log_pos);
3. master에서 아래 명령 실행
mysql> unlock tables;

* master-master replication을 할 때, 각 row가 쌓이는 순서에 상관없이 동작할 수 있어야 한다.

* master-master replication은 성능상 이득이 거의 없다. 왜냐하면 두 서버는 같은 개수의 update를 수행하고, 단지 lock이 좀 덜 일어날 뿐이기 때문이다.

* replication으로 성능을 올리는 방법
master에서는 update만 하고, slave에서는 read만 한다.
slave에 --skip-innodb, --low-priority-updates, --delay-key-write=ALL 옵션을 준다.
slave에 nontransactional MyISAM같은 테이블을 쓴다(InnoDB와 같은 테이블을 사용하지 않는다).

* binary logging 모드 확인 방법(master에 설정하면 slave는 따라간다)
mysql> show variables like 'binlog_format';

* How do I prevent GRANT and REVOKE statements from replicating to slave machines?
start the server with the --replicate-wild-ignore-table=mysql.%

* replication은 운영체제(Linux, Windows 등)와 아키텍쳐(32-bit, 64-bit)가 달라도 동작한다.

* replication의 사용 목적: scale-out solutions, data security, analytics, long-distance data distribution

* replication은 기본적으로 asynchronous이지만, semisynchronous모드(master의 update가 slave에 저장될 때 까지 block됨)도 지원한다.

* master는 단순히 binary log만 남길 뿐이고, slave에서 특정 DB나 table만 선택해서 가져올 수 있다.

* master/slave는 모두 고유한 server ID를 가져야 한다.

* slave는 master host name, log file name, position of log file을 알아야 한다(change master to 명령어로 변경 가능). 정보는 slave의 master.info 파일에 들어 있다.

* server ID와 binary logging 켜는 법(my.ini)
[mysqld]
log-bin=mysql-bin
server-id=1

* InnoDB를 쓸 때 설정해 주어야 할 것(?)(my.ini)
innodb_flush_log_at_trx_commit=1
sync_binlog=1

* slave에 binary logging을 켜면 데이터 백업, crash recovery, 복잡한 replication topology 구성에 사용할 수 있다.

* master.info 파일에 replication slave privilege를 가진 계정의 비밀번호가 그대로 노출되므로 계정을 하나 생성하는 것이 좋음
mysql> create user 'repl'@'%.mydomain.com' identified by 'slavepass';
mysql> grant replication slave on *.* to 'repl'@'%.mydomain.com';

* 테이블 insert를 막고 binary log의 파일명과 coordinate를 확인하기
mysql> flush tables with read lock;
mysql> show master status;
mysql> unlock tables;

* mysqldump로 data snapshot 만들기
shell> mysqldump --all-databases --master-data > dbdump.db
--master-data 옵션을 안주면 테이블을 잠그고 flush하고 다시 푸는 작업을 수동으로 해 주어야 함.

* raw data file로 snapshot을 slave에 전송할 때 storage engine이 cache information이나 logging update를 쓰는지 여부에 따라 과정이 복잡해질 수 있다.
또한, master와 slave의 ft_stopword_file, ft_min_word_len, ft_max_word_len 변수가 다른 경우 동작하지 않는다.
InnoDB를 사용하는 경우라면 mysqlbackup 명령어를 사용할 수도 있고, MySQL ENterprise Backup을 사용할 수도 있다.
InnoDB의 경우 cold backup(slow shutdown 후 백업하는 것)을 사용해야 하는데, slow shutdown이란 innodb_fast_shutdown 변수를 0으로 놓고 종료하는 것을 말한다.
MyISAM의 경우 간단히 replication 하고자 하는 테이블 파일만 복사하면 된다.
InnoDB의 경우 innodb_file_per_table 변수가 enable되어 있지 않다면 system tablespace란 파일 하나에 모든 테이블이 기록된다.

* raw data snapshot을 만들 때 포함하지 않아도 되는 것들
- mysql database와 관련된 파일
- master.info 파일
- master's binary log 파일
- relay log 파일(host_name-relay-bin.nnnnnn)

* raw data file 생성법(InnoDB 사용시)
1. acquire a read lock and get the amster's status
2. shell> mysqladmin shutdown
3. shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata
4. restart the master server

* raw data file 생성법(MyISAM 사용시)
1. acquire a read lock and get the master's status
2. shell> tar cf /tmp/db.tar ./data
shell> zip -r /tmp/db.zip ./data
shell> rsync --recursive ./data /tmp/dbdata
3. mysql> unlock tables;

* multi-master replication을 위해서는 slave_exec_mode=IDEMPOTENT 설정이 필요하다. 근데 현재 NDB를 제외한 다른 엔진은 이 옵션을 지원하지 않는다고 한다.

* SBR에서 unsafe statement를 replication하면 경고가 발생하는데, 이를 막기 위해 ER_BINLOG_UNSAFE_STATEMENT 변수가 사용된다(5.5.27 이상). 이것 때문에 디스크가 금방 고갈될 수도 있다.

* An INSERT into a table that has a composite primary key containing an AUTO_INCREMENT column that is not the first column of this composite key is unsafe.

* auto_increment_increment, auto_increment_offset 변수는 master-to-master replication에 사용된다.

* change master to 옵션으로 master.info 파일을 동적으로 수정할 수 있다.

* --report-host=host_name, --report-password=password, --report-port=slave_port_num, --report-user=user_name을 slave에 주면 master에서 --show-slave-auth-info 및 show slave hosts;명령으로 정보를 확인할 수 있다.

* show slave status; 명령으로 현재 slave 상태를 조회할 수 있다.

* show processlist;로 현재 프로세스 상태를 조회할 수 있다.

* show slave hosts; 명령으로 master에 연결된 slave의 상태를 조회할 수 있다.

* stop slave (stop slave io_thread; stop slave sql_thread)로 slave를 멈출 수 있다.

* start slave (start slave io_thread; start slave sql_thread)로 slave를 시작할 수 있다.

* --relay-log, --relay-log-index 옵션으로 relay log 관련 파일명을 바꿀 수 있다. 이 파일명에 디폴트로 호스트명이 들어가기 때문에, 지정해주는것이 좋다.

* --master-info-file, --relay-log-info-file로 slave status log 파일명을 바꿀 수 있다(디폴트: master.info, relay-log.info) -> show slave statue의 결과를 로깅하는 용도.

* master에서 --binlog-db-db/--binlog-ignore-db를 사용하는 것 보다는, slave에서 --replicate-do-db/--replicate-ignore-db를 사용하는 것이 더 바람직하다.

참고 링크:

http://dev.mysql.com/doc/refman/5.5/en/replication.html

https://www.digitalocean.com/community/articles/how-to-set-up-mysql-master-master-replication

http://www.howtoforge.com/mysql_master_master_replication