Data Science Lab

MySQL Master DB를 백업하여 Replication Slave DB 구성하기

·       Version : MySQL 5.7.5 Later

MySQL DB를 처음 구축할때 마스터DB와 슬레이브DB를 구성하는것이 아니라면 반드시 마스터 DB의 데이터를 백업하여 슬레이브DB를 구성 하여야 한다. 이때 특정 시점까지 (Point in Time)의 마스터 DB를 백업하면, 백업파일에는 백업이 완료된 시점의 바이너리 로그파일과 포지션 정보가 기록된다. 슬레이브DB에서는 마스터DB 백업 파일 이후로 발생된 데이터 변경사항을 전달받아 레플리케이션 한다.

InnoDB 스토리지 엔진은 트랜잭션을 지원하므로 특정 시점까지 백업한 후 백업 시점의 마지막 바이너리 로그 파일과 로그 포지션 정보로 마스터 DB와 슬레이브 DB를 연결하는 식으로 레플리케이션을 구성할 수 있다.

MySQL에서 데이터를 백업할 때 시점 백업(Point in Time)을 수행하는 방법에는 두 가지가 있다. 첫번째 방법은 mysqldump 명령을 사용할때 –single=transaction옵션을 사용하는 방법이고, 두번째 방법은 xtrabackup을 사용하는 것이다. 이번 포스트에서는 mysqldump만 다루도록 한다.

mysqldump를 사용한 시점 백업은 아래와 같다.

백업이 완료 되었으면 아래 명령을 사용하여 Slave DB에서 백업 파일을 복원한다.

슬레이브DB에 백업파일 복원이 완료 되었으면 백업파일에서 마스터DB의 로그 파일과 포지션 정보를 확인하여 레플리케이션 정보를 등록한다.

위 정보를 바탕으로 연결할 마스터DB의 정보를 입력하고 연결을 시작한다.

슬레이브DB 복제가 시작되었으면 show slave status 명령을 사용하여 슬레이브DB의 복제상태를 확인할 수 있으며 “Slave I/O thread”, “Slave SQL thread” 상태가 YES로 표시되면 정상 작동이며 , NO라고 표시될 경우 비정상 작동이라고 판단할 수 있다.

[참고자료]

·       mysqldump — A Database Backup Program : https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html#mysqldump-transaction-options

2021-05-28 / Sungwook Kang / http://sungwookkang.com

MySQL, MysqlDump, MySQL Replication, MySQL HA, Replication, MySQL복제, 마스터 슬레이브

MySQL InnoDB Buffer Pool Resizing Online

·       Version : MySQL 5.7.5, 8.0

MySQL 5.7.5 부터 InnoDB 버퍼풀의 크기를 서비스 가동중에도 동적으로 조절할 수 있다. 동적으로 버퍼풀 확대 또는 축소를 제공하기 위해 청크 크기를 정의하는 새로운 변수인 innodb_buffer_pool_chunk_size 가 도입되었으며, 이 변수는 동적이 아니며 잘못 구성되면 원하지 않는 상황이 발생할 수도 있다.

아래 그림은 innodb_buffer_pool_size, innodb_buffer_pool_instances , innodb_buffer_pool_chunk_size가 상호작용하는 방식을 나타낸것이다.

버퍼풀은 여러 인스턴스를 보유할 수 있으며 각 인스턴스는 청크로 분할된다. 인스턴스의 수는 1개 ~ 64개 까지 될 수 있으며 총 청크의 양은 1000개를 초과하지 않도록 해야한다. 따라서 3GB RAM이 있는 서버에서, 8개의 인스턴스가 있는 2GB의 버퍼 풀 및 기본값(128M)의 청크를 가지고 있는 경우 인스턴스당 2개의 청크를 얻게 된다. 이 뜻은 16개의 청크가 있음을 의미한다.

현재 설정되어있는 버퍼풀 크기를 확인하는 방법은 아래 스크립트를 사용한다.

버퍼 풀 크기를 조절하는 방법은 아래 스크립트를 실행한다.  이때 파라메터는 바이트 값이므로 설정시 주의 한다.

[참고자료]

·       InnoDB Buffer Pool Resizing: Chunk Change : https://www.percona.com/blog/2018/06/19/chunk-change-innodb-buffer-pool-resizing/

2020-10-20 / Sungwook Kang / http://sungwookkang.com

MySQL, Buffer Pool, innodb_buffer_pool_size, innodb_buffer_pool_instances , innodb_buffer_pool_chunk_size, 버퍼풀 사이즈 조절

MySQL MMM 구성 (Centos7 + MySQL 8.0)

·       Version : MySQL 8.0.XX, Centos 7

MySQL MMM(MySQL Multi-Master) 을 구성하는 방법에 대해서 살펴본다. 이번 포스트에서는 MMM 구성에 대해서만 다루므로 MySQL 설치 및 Master-Slave 구성은 다른 글을 참고할 수 있도록 한다.

MMM구성에 관한 글을 찾아보면 대부분 Centos6 기반의 환경에서 설치된 글을 많이 볼 수 있다. 필자의 경우 Centos7 환경에서 구성을 진행하였는데, Centos7 버전에서는 공식 가이드 문서에서 제공하는 코드와 조금 다르게 디렉터리 경로가 바뀐 부분이 있어 처음 설치시 오류가 발생하였다. 디렉터리 PATH에 대한 설정 값을 수정하고 정상 작동확인한 내용을 정리하였다.

서버 구성

·       Write VIP : 192.168.0.101

MNM 구성

·       MMM Monitor : MMM Agent 서버의 상태를 체크하고 상태에 따라 역할(reader, writer)을 변경,관리

·       MMM Agent : MMM 에서 reader, writer 역할을 하는 구성원

·       MMM VIP : 유동적으로 writer 및 마스터 역할을 변경할 수 있도록 writer에 구성하는 virtual IP

·       역할구성 : master역할을 할 수 있는 writer후보자들은는 두개로 구성하고, 나머지는 slave 역할만 하도록 구성

MMM동작 확인

·       sudo mmm_control show

·       sudo mmm_control checks all

·       Role Change (Run as Monitor)

MMM Troubleshooting

MMM 서비스 후 아래와 같이 ADMIN 이 OFFLINE로 표시한 경우 아래 명령어 실행

[참고자료]

·       https://mysql-mmm.org/mmm2_guide.html

2020-01-31 / Sungwook Kang / http://sungwookkang.com

MySQL, MMM, MySQL 복제, MySQL 이중화, MySQL Multi Master, 멀티마스터 복제, HA, Database 이중화, DBA

MySQL/MariaDB Single-pass, Two-pass Sort Algorithm

·       Version : MySQL 5.X Later,  MariaDB 5.X Later

MySQL/MariaDB에서는 정렬 알고리즘으로Single-pass 와 Two-pass  알고리즘을 사용한다. 그리고 정렬처리 방식으로 인덱스를 사용한 방식과 테이블을 사용하여 정렬하는 방식이 있다.

Single-pass 알고리즘은 Sort Buffer에 정렬 기준 칼럼을 포함한 SELECT에 포함된 컬럼의 데이터를 버퍼 메모리에 담아서 정렬을 수행하는 방식이다. Two-pass 알고리즘은 정렬 컬럼과  프라이머리 키 값만 Sort buffer에 담아서 정렬을 수행하고, 정렬된 순서대로 다시 프라이머리키로 테이블을 읽어서 SELECT 에 포함된 컬럼의 데이터를 가져오는 방식이다. Two-pass 알고리즘은 같은 테이블을 2번 읽어야 하기 때문에 매우 불합리해 보일 수 있으나, Sing-pass 알고리즘은 더 많은 소트 버퍼의 공간이 필요하다.

MySQL 5.0 및 MariaDB 5.X 버전부터는새로운 정렬 알고리즘은 Single-pass 방식이 사용되지만 아래와 같은 경우에는 Two-pass 알고리즘이 사용된다.

·       레코드의 크기가 max_length_for_sort_data 파라미터로 설정된 값보다 클 때

·       BLOB 이나 TEXT 타입의 컬럼이 SELECT 대상에 포함될때

싱글패스 알고리즘은 정렬 대상 건수가 작을때 빠른 성능을 보이며, 투 패스 알고리즘은 데이터 레코드의 건수가 많을 때 효율적이다. 쿼리에 ORDER BY가 사용되면 정렬을 하기 위해 아래 3가지 방식중 하나로 처리 된다. 옵티마이저는 가장 먼저 인덱스를 이용할 수 있을지 검토하고, 인덱스가 없을 경우 조건에 일치하는 레코드를 검색하여 버퍼에 저장하면서 정렬을 처리(Filesort)한다.

하나의 테이블로부터 SELECT해서 정렬을 하는 경우라면 임시 테이블이 필요하지 않다. 하지만 2개 이상의 테이블을 조인해서 그 결과를 정렬해야한다면 임시 테이블이 필요할 수도 있다. 드라이빙 테이블만 정렬된 경우에는 임시 테이블을 사용하지 않는다.

2019-09-20 / 강성욱 / http://sungwookkang.com

MySQL, MariaDB, Sort Buffer, Single-pass, Two-pass , Filesort