1. replication with mariaDB - MariaDB와 MySQL의 Replication은 다르다!

인프런 - 따라하며 배우는 MySQL on Docker의 내용을 보고 

주로 사용하고 결정한 MariaDB를 설치 후 my.cnf 및 sql query를 동일하게 입력하면서

연속으로 발생되는 에러들에 정신없이 헤메다 제대로, 기본만이라도 알아보자란 마음으로 정리한다.

 

*** MariaDB와 mysql은 돌아가는 flow는 비슷할지 몰라도 환경 변수나 option 등 세세한 부분의 많은 것들이 다르다

     꼭! 메뉴얼을 보고 확인하자!

 

*** 사용자와 폴더 권한이 매우 중요하다.

    - docker에 mysql 그룹과 사용자 ID를 local host의 그룹과 사용자 ID와 맞추자

      예) 1001:1001 mysql:mysql

    - 파일 권한에 있어서 모든 폴더와 파일은 777, 755로 설정하되 my.cnf는 꼭 655로 맞추자

 

*** github 저장소

https://github.com/bluebamus/mariadb-replication_prometheus-grafana

GitHub - bluebamus/mariadb-replication_prometheus-grafana: A docker-compose project for mysql replication and monitoring solutio

 

1. SEMI-SYNC 복제 구성

- ASYNC 방식의 경우 MASTER 장애시 슬레이브로 FAIL-OVER되는 과정에서 동기화가 되지 못한 트랜잭션이 있다면 해당 트랜잭션은 유실될 우려가 있다.

- SEMI-SYNC 방식은 이 점을 보완한 것으로 SLAVE의 ACK를 받은 후 Application에게 COMMIT 을 보낸다.

- SEMI-SYNC 방식은 이 점을 보완한 것이며 COMMIT 후 OK메시지를 받았다는 것은 적어도 SLAVE의 RELAY-LOG에 반영되었다는 것을 의미한다.

 

2. GTID (Global Transaction IDentifier)

- MySQL 데이터베이스에서 커밋되는 각 트랜잭션과 함께 생성되고 트랜잭션에 연결되는 고유한 식별자

- 이 식별자는 복제의 Master 서버 뿐만 아니라 복제 대상에 속한 Replica(slave) 서버에서 고유한 식별자

- GTID 구성 : 

GTID = source_id:transaction_id

- server_id : source_id 는 originating 서버를 식별 하는데 사용되며 일반적으로 server_uuid가 해당 용도로 사용됨

- server_uuid 확인 방법 :

mysql > select @@server_uuid;

- GTID 사용에 따른 특징 :

    - 이전 방법 : master 와 slave 간 복제 시작, 중지의 기준이 되었던 binlog 파일명과 pos 정보 기준    - GTID 사용 :        - CHANGE MASTER TO 의 MASTER_LOG_FILE, MASTER_LOG_POS 의 사용하지 않아도 됨        - GTID 정보만으로 master - slave 간의 일관성 확인이 쉽게 수행됨

        - slave에 반영된 gtid 트랜잭션이 mysql.gtid_executed 로 관리되기 때문에 중복 수행 안됨

        - global.gtid_executed 확인 방법 :

mysql> select @@global.gtid_executed;

mysql> select * from mysql.gtid_executed;

        - 트랜잭션이 커밋되면 GTID를 할당 받고 binlog 에 기록됨

        - 할당된 GTID 는 gtid_executed system variable과 mysql.gtid_executed에 저장됨

 

- gtid_mode에서 설정할 수 있는 모드 4가지 (mariadb : gtid_strict_mode)

	Master	Slave
OFF	트랜잭션에 Global Trx ID 값을  부여하지 않음	복제된 트랜잭션에 Global Trx ID가  부여되어 있으면 처리불가
OFF_PERMISSIVE		복제된 트랜잭션이  어떤 방식이든지 처리 가능  (Anonymous / GTID 둘다 처리 가능)
ON_PERMISSIVE	트랜잭션에 Global Trx ID 값을 부여
ON		복제된 트랜잭션에 반드시 Global Trx ID가 부여되어 있어야 처리 가능

* MSR(Multi Source Replication) 환경에서 복제 되는 Slave 인스턴스에 MSR 채널별 다른 복제 방법을 사용시 에러 발생

    - off_permissive 모드 설정시, Binlog Position 방식과 GTID 방식의 마스터 Source를 같이 혼용(Mixed) 하여 설정 가능

 

 

3. Binary log

    - binary log에는 데이터베이스에 대한 모든 변경 사항(데이터 및 구조)과 각 명령문 실행 시간이 기록되어 있음

    - binary log 는 InnoDB 같은 스토리지 엔진에서 기록하는 Redo Log 와는 다른 별도의 로그임

    - binary log 는 일련의 binary log 파일 집합과 index 파일로 구성됨

    - 블로그 참고 : http://cloudrain21.com/mariadb-binary-log

MariaDB - binary log - Rain.i

 

4. Transaction Isolation

    - 트랜잭션이 중요시 여기는 ACID 설명 및 Transaction Isolation 관계

        - 참고 : https://velog.io/@lsb156/Transaction-Isolation-Level#transaction-isolation

Transaction Isolation Level 정리

 

5. 테스트에 사용한 my.cnf (최적화 파일 아님)

    - my.cnf - master

[client]
socket                      = /var/lib/mysql/mysql.sock

[mysqld]
port		                = 3306
user	                	= mysql

#max_allowed_packet         = 1G
max_allowed_packet          = 256M
event_scheduler             = on

datadir                     = /var/lib/mysql
socket                      = /var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links              = 0

log-error                   = /var/log/mysql/mysqld.log
pid-file                    = /var/run/mysqld/mysqld.pid

log_bin                     = /var/log/mysql/mysql-bin/mariadb-binary-log
relay-log                   = /var/log/mysql/mysql-relay/mariadb-relay-log

expire_logs_days = 10 

# replication 

server-id                   = 100
report_host                 = mariadb-001
binlog_format               = MIXED   #ROW / MIXED

sync_binlog                 = 1

gtid_strict_mode            = on
log_slave_updates           = on
rpl_semi_sync_master_enabled= 1
rpl_semi_sync_slave_enabled = 1
rpl_semi_sync_master_timeout= 10000
rpl_semi_sync_master_wait_point=after_sync

#slave-skip-errors = all
#slave_skip_errors=1032

#transaction-isolation = READ COMMITTED

[mysqld_safe]
pid-file                    = /var/run/mysqld/mysqld.pid
socket                      = /var/lib/mysql/mysql.sock
nice                        = 0

    - my.cnf - slave : ready-only만 차이가 있다

[client]
socket                      = /var/lib/mysql/mysql.sock

[mysqld]
port		                = 3306
user	                	= mysql

#max_allowed_packet         = 1G
max_allowed_packet          = 256M
event_scheduler             = on

datadir                     = /var/lib/mysql
socket                      = /var/lib/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links              = 0

log-error                   = /var/log/mysql/mysqld.log
pid-file                    = /var/run/mysqld/mysqld.pid

log_bin                     = /var/log/mysql/mysql-bin/mariadb-binary-log
relay-log                   = /var/log/mysql/mysql-relay/mariadb-relay-log

expire_logs_days = 10 

# replication 

read_only

server-id                   = 200
report_host                 = mariadb-002
binlog_format               = MIXED   #ROW / MIXED

sync_binlog                 = 1

gtid_strict_mode            = on
log_slave_updates           = on
rpl_semi_sync_master_enabled= 1
rpl_semi_sync_slave_enabled = 1
rpl_semi_sync_master_timeout= 10000
rpl_semi_sync_master_wait_point=after_sync

#slave-skip-errors = all
#slave_skip_errors=1032

#transaction-isolation = READ COMMITTED

[mysqld_safe]
pid-file                    = /var/run/mysqld/mysqld.pid
socket                      = /var/lib/mysql/mysql.sock
nice                        = 0

6. query

    - master

MariaDB [mysql]> CREATE USER 'repl'@'%' IDENTIFIED BY 'repl';

MariaDB [mysql]> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';

MariaDB [mysql]> show master status\G
*************************** 1. row ***************************
            File: mariadb-binary-log.000001
        Position: 642
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.000 sec)

* 기록해 둬야 할 정보 : File: mariadb-binary-log.000001,  Position: 642

 

    - slave

CHANGE MASTER TO MASTER_HOST='mariadb-001',MASTER_USER='repl',MASTER_PASSWORD='repl',MASTER_LOG_FILE='mariadb-binary-log.000001',MASTER_LOG_POS=337;

show slave status\G

 

7.  MariaDB bin log 삭제, 위치 변경 등

    - 블로그 참고 : https://riorio.tistory.com/86

mariaDB bin log 위치 변경

 

 

* 오케스트레이션, sqlproxy, 모니터링은 다음 포스팅으로 넘기도록 하겠다.

* 3일동안 종일 검색을 하고 테스트를 했는데 정작 결론은 단순한 설정과 쿼리가 다였고

  기본적인 문제 중 하나는 권한과 소유자였다. 그것도 ID.... 과정대비 결과가 심플하다. 

 

reference : 

https://www.slideshare.net/neoclova/maxscale-249945769

Maxscale_메뉴얼

https://open-dba.gitbooks.io/mariadb-tips/content/bcf5_c81c_ad6c_c131.html

복제구성 · MariaDB Tips

https://iwindfree.wordpress.com/2019/10/19/mariadb-replication-%ea%b5%ac%ec%84%b1%ed%95%98%ea%b8%b0/

MariaDB Replication Part1

https://iwindfree.wordpress.com/2019/10/19/mariadb-replicaton-part2/

MariaDB Replication Part2