MySQL HA + ProxySQL 환경에서 서비스 장애조치 구성

 

l  Version : MySQL, ProxySQL

 

MySQL HA + ProxySQL 환경에서 장애조치가 어떻게 동작하는지 알아본다. 실습 환경은 아래 포스트를 참고하여 구성할 수 있도록 한다.

 

l  ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에) : https://sungwookkang.com/1529

l  MySQL HA 환경에서 Orchestrator를 활용한 클러스터 리팩토링 및 자동 장애조치 구성 : https://sungwookkang.com/1533

 

실습에 사용되는 서버 구성이다.

Server Name IP OS Service Version
proxy-sql 172.30.1.49 Ubuntu 22.04.2 LTS ProxySQL version 2.4.2-0-g70336d4, codename Truls
mysql-master 172.30.1.97 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave1 172.30.1.10 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave2 172.30.1.13 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
proxy-orchestrator 172.30.1.24 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33, container.d

 

MySQL HA 기본 구성으로 1대의 마스터 서버와, 2대의 슬레이브 서버가 GTID 기반으로 복제 구성이 되어 있다. 그리고 서버 장애시 역할 변경을 위해 오케스트레이터로 자동 failover를 구성해 놓은 상태이다.

 

ProxySQL에서는 각 노드의 서비스 상태를 헬스 체크하여 서비스에 사용할 서버를 판단한다. 아래는 노드의 서비스 상태를 체크하는 모듈이다.

l  connect : 모든 노드(백엔드)에 연결하고 성공 또는 실패를 mysql_server_connect_log  테이블에 기록한다.

l  ping : 모든 노드 (백엔드)에 ping을 보내고 성공 또는 실패를 mysql_server_ping_log 테이블에 기록한다. mysql-monitor_ping_max_failures에 하트비트가 없으면 MySQL_Hostgroups_Manager에 신호를 보내 모든 연결을 끊는다.

l  replication lag : max_replication_lag가 0보다 큰 값으로 구성된 모든 노드(백엔드)에 대해 Seconds_Behind_Master를 확인하고 mysql_server_replication_lag_log 테이블에 확인을 기록한다. Seconds_Behind_Master > max_replication_lag인 경우 Seconds_Behind_Master < max_replication_lag까지 서버를 회피한다.

l  read only : mysql_replication_hostgroup 테이블의 호스트 그룹에 있는 모든 호스트에 대해 읽기 전용을 확인하고, 상태값은 mysql_server_read_only_log에 기록된다. read_only=1이면 호스트가 reader_hostgroup으로 복사/이동되고, read_only=0이면 호스트가 writer_hostgroup으로 복사/이동된다.

 

이번 포스트에서 다루는 ProxySQL 장애조치 시나리오는 두 가지이다.

l   시나리오 1 : slave 서버에 장애가 발생하여 장애가 발생한 서버로는 read 트래픽을 요청하지 않는다.

l   시나리오2 : master 서버에 장애가 발생하여 slave 서버중 하나가 master로 승격되고, ProxySQL의 호스트 그룹에서 Write가 가능한 호스트 그룹을 변경한다.

 

시나리오에 따른 실습을 하기 전에 현재 구성되어 있는 ProxySQL에 접속하여 현재의 상태를 살펴본다. proxy-sql 서버에서 아래와 같은 스크립트를 실행하여, ProxySQL 관리 모드로 접속한다.

mysql -uadmin -padmin -h 127.0.0.1 -P6032 --prompt='admin> '

 

 

현재 호스트 그룹에 등록된 서버들의 목록을 확인할 수 있다. hostgroup_id = 1 은 write가 가능한 그룹이며, hostgroup_id = 2는 읽기가 가능한 그룹이다.

select * from mysql_servers;

 

 

 

아래 스크립트는 등록된 서버들의 커넥션 로그를 확인한다. 만약 여기서 커넥션 오류로그가 있다면 해당 서버와의 통신에 문제가 발생한 것이다.

select * FROM monitor.mysql_server_connect_log order by time_start_us desc limit 10;

 

 

 

위의 커넥션 로그가 실제 쿼리가 호출될 때 확인된다면, 아래는 서비스 헬스 체크를 위한 핑 로그이다.

select * from monitor.mysql_server_ping_log order by time_start_us desc limit 10;

 

 

 

ProxySQL의 클라이언트용 포트로 접속하여 현재 상태에서 정상적으로 모든 서버가 응답하는지 테스트한다.

mysql -uubuntu -p123456 -h127.0.0.1 -P6033 -e "SELECT @@hostname";

 

 

 

아래 스크립트에서는 현재 연결된 커넥션 풀에 대한 상태를 확인할 수 있다. 실제 연결에 문제가 발생할 경우 stats_mysql_connection_pool의 status 컬럼에 상태가 변경되는 것을 확인할 수 있다.

select * from stats_mysql_connection_pool;

 

 

 

시나리오1.  slave 서비스 장애

slave 서버중 하나에 장애가 발생한 상황으로, 장애가 발생한 서버로는 읽기 요청을 하지 않도록 하여 서비스 장애를 예방한다.

 

mysql-slave2 서버에 접속하여, MySQL 서비스를 중지한다.

sudo systemctl stop mysql
 
sudo systemctl status mysql

 

 

서비스가 종료된 상태에서 ProxySQL 관리자의 mysql_servers 테이블 상태를 살펴보면 여전히 모든 서버가 ONLINE 상태로 보이는 것을 확인할 수 있다. 사실 이 테이블은 서버의 목록을 관리하는 것이지, 서버의 상태를 나타내지는 않는다. 다만 수동으로 status 값을 OFFLINE으로 변경하면, 서비스가 활성화 상태여도 해당 서버로 트래픽을 보내지 않는다.

select * from mysql_servers;

 

 

 

현재 실시간의 노드의 상태를 확인하려면 runtime_mysql_servers  테이블의 정보를 확인한다. 아래 스크립트를 사용하여 중지된 서버의 상태가 SHUNNED로 표시된 것을 확인할 수 있다.

select * from runtime_mysql_servers;

 

 

 

커넥션 풀 상태에서도 동일하게 SHUNNED으로 나타나는 것을 확인할 수 있다.

select * from stats_mysql_connection_pool;

 

 

 

커넥션 로그를 확인해보면 중지된 서버(mysql-slave2)의 커넥션 실패 로그를 확인할 수 있다.

select * FROM monitor.mysql_server_connect_log order by time_start_us desc limit 10;

 

 

 

핑 로그에서도 오류를 확인할 수 있다.

select * from monitor.mysql_server_ping_log order by time_start_us desc limit 10;

 

 

 

mysql-slave2 서버의 MySQL 서비스가 중지된 상태로, ProxySQL에서도 해당 서버의 문제점을 인지한 상태이다. 이제 클라이언트 연결을 통해서 실제 운영중인 서버로만 트래픽이 정상적으로 라우팅 되는지 확인한다. 테스트를 위해 proxy-sql 서버 콘솔에서 아래 스크립트를 여러 번 실행한다.

mysql -uubuntu -p123456 -h127.0.0.1 -P6033 -e "SELECT @@hostname";

 

 

문제의 서버를 제외하고 정상적인 서버로만 트래픽을 라우팅하는 것을 확인할 수 있다. ProxySQL에서는 이렇게 문제의 서버를 감지하여 트래픽을 보내지 않음으로써 서비스 장애를 예방할 수 있다.

 

다른 시나리오 테스트를 진행하기 위해 중지된 서버를 시작하여 클러스터를 원상 복구한다.

 

시나리오2. master 서버 장애

이번 시나리오는 master서버에 장애가 발생하여, 서비스가 다운되고, slave 서버 중 하나가 master로 승격되어 운영되는 환경이다. 이때 장애를 감지하고 자동으로 서버를 승격시키는 역할은 MySQL 오케스트레이터가 한다.

 

mysql-master 서버에 접속하여 MySQL 서비스를 중지한다. 오케스트레이터에서는 master 서버의 장애를 감지하고, 중지된 master 서버를 클러스터에서 격리한 후 slave 서버중 하나를 master로 승격여 클러스터 토폴로지를 재구성한다.

 

새로 구성된 클러스터를 살펴보면 이전 slave역할이었던 mysql-slave2(172.30.1.13)서버가 master로 승격된 것을 확인할 수 있다. 그리고 나머지 slave 서버가 새로운 master를 복제하고 있다.

 

 

ProxySQL에서도 커넥션 풀 로그를 확인해보면 기존의 master역할을 하던 mysql-master(172.30.1.97)에 문제가 있음을 인지한 것을 확인할 수 있다.

select * from stats_mysql_connection_pool;

 

 

 

실행중인 서버의 상태를 확인해봐도 문제를 인지한 것을 확인할 수 있다.

select * from runtime_mysql_servers;

 

 

 

proxy-sql에서 문제가 발생한 노드가 제외되고 서비스가 정상적으로 되는지 확인한다. 호스트 이름을 검색하는 쿼리를 실행해보면 문제없이 잘 작동한다

mysql -uubuntu -p123456 -h127.0.0.1 -P6033 -e "SELECT @@hostname";

 

 

하지만 현재 상태는 정상적인 서비스를 할 수 없는 큰 문제가 있다. 현재 클러스터 상태는 기존의 mysql-master (마스터 역할) 서버가 중지되고 mysql-slave2서버가 master 역할로 승격된 상태인데, mysql_servers 테이블의 정보에는 여전히 이전의 mysql-master 서버가 hostgroup_id=1에 등록되어 있다. 실질적으로 ProxySQL에서는 Write가 가능한 서버를 잘못 인식하고 있는 상태이다. 이 정보를 업데이트하지 않으면 write에 대해서는 계속해서 실패가 발생하게 된다.

이러한 서버 장애시 hostgroup_id를 자동으로 변경하기 위해 복제 호스트 그룹으로 관리할 수 있다. 단, 이렇게 하기 위해서는 역할에 따른 MySQL 서버의 Read only 속성값이 중요하다. master 역할 서버는 read_only =false로, slave 역할의 서버는 read_only=true 로 되어 있어야 한다. 그렇지 않으면 ProxySQL에서는 read_only=false 서버를 write가 가능한 서버로 인식하기 때문에 master-master구조로 인식되어 의도하지 않는 write 오류가 발생할 수 있다.

 

아래 스크립트는 write가 가능한 호스트들은 1 그룹으로, 읽기가 가능한 호스트들은 2 그룹으로 할당한다. 서버의 정보를 변경하였기에 해당 변경 사항이 런타임으로 적용될 수 있도록 LOAD 명령어도 함께 실행한다.

INSERT INTO mysql_replication_hostgroups VALUES (1,2,'read_only','1=write, 2=read');
 
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

 

 

 

기존의 클러스터 구성으로 원상 복구하고, mysql-master (마스터 역할) 서버를 중지하였을 때, 오케스트레이터에서 자동 장애 조치가 활성화되어 서비스가 중지된 master 서버는 클러스터에서 분리하고, 정상적인 slave 서버중 하나를 master로 승격한다. 그리고 ProxySQL에서는 slave에서 master로 승격된 서버를 houstgroup_id=1로 자동 할당되는 것을 확인할 수 있다.

 

 

런타임 서버 상태 테이블에서도 장애가 발생한 서버는 SHUNNED로 변경되어 서비스에서 제외된 것을 확인할 수 있으며, 새로운 master 서버가 hostgroup_id=1 변경된 것을 확인할 수 있다.

 

 

 

이번 실습을 통해서 mysql_servers의 테이블은 호스트의 상태 값을 관리하는 테이블이 아닌 호스트 그룹을 관리하기 위한 서버 목록 용도로 사용되는 것을 알 수 있었으며, 실제 서비스들의 상태나 운영가능한 호스트들이 목록은 runtime_mysql_servers 테이블에서 확인 가능한 것을 알 수 있었다.

 

MySQL 오케스트레이터와 ProxySQL을 조합하여 사용할 경우, 오케스트레이터에서는 자동으로 장애를 감지하고 클러스터 토폴로지 재구성으로 빠르게 역할을 승격하여 안정적인 서비스를 유지할 수 있고, ProxySQL에서는 오케스트레이션에서 승격시킨 서버의 상태를 확인하여 호스트 그룹의 정보를 자동으로 변경함으로써, 만약의 상태에도 안정적인 서비스가 가능한 구성을 만들 수 있다는 것을 확인할 수 있다.

 

[참고자료]

l   Orchestrator : https://github.com/openark/orchestrator

l   Failure detection : https://github.com/openark/orchestrator/blob/master/docs/failure-detection.md

l   MySQL Orchestrator - HA(High Availability) - 2 - 리팩토링 Failover Automated Recovery : https://hoing.io/archives/91

l   Configuration: failure detection : https://github.com/openark/orchestrator/blob/master/docs/configuration-failure-detection.md

l   https://code.openark.org/blog/mysql/what-makes-a-mysql-server-failurerecovery-case : https://code.openark.org/blog/mysql/what-makes-a-mysql-server-failurerecovery-case

l   Orchestrator: MySQL Replication Topology Manager : https://www.percona.com/blog/orchestrator-mysql-replication-topology-manager/

l   Orchestrator and ProxySQL : https://www.percona.com/blog/orchestrator-and-proxysql/

 

 

 

 

 

2023-08-02 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, MySQL Replication, MySQL HA, MySQL복제 오케스트레이션, MySQL복제설치, MySQL MHA, MySQL Orchestrator , MySQL장애조치, MySQL고가용성

저작자표시 비영리 변경금지

'MySQL, MariaDB' 카테고리의 다른 글

MySQL/MariaDB 환경에서 다중 마스터 복제를 지원하는 Galera Cluster 알아보기  (0) 2023.08.07
ProxySQL 은 서비스에 필요한 설정값을 어디에 저장하고 재사용할까?  (0) 2023.08.04
MySQL HA 환경에서 Orchestrator를 활용한 클러스터 리팩토링 및 자동 장애조치 구성  (0) 2023.07.27
MySQL 고가용성 운영을 위한 Orchestrator 설치  (0) 2023.07.25
ProxySQL Internals 및 시스템 구성 둘러보기  (0) 2023.07.24

MySQL HA 환경에서 Orchestrator를 활용한 클러스터 리팩토링 및 자동 장애조치 구성

 

l  Version : MySQL, Orchestrator

 

MySQL HA 환경에서 Orchestrator(이하 ‘오케스트레이터’)를 활용하여 클러스터 리팩토링 및 장애조치 방법에 대해서 알아본다. 이번 실습을 진행하기 위해서는 MySQL HA 및 Orchestrator 구성이 완료되어 있어야 한다. 아래 링크를 참고하여 HA구성 및 Orchestrator를 구성할 수 있도록 한다.

l  ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에) : https://sungwookkang.com/1529

l  MySQL 고가용성 운영을 위한 Orchestrator 설치 : https://sungwookkang.com/1532

 

실습에 사용되는 서버 구성이다.

Server Name IP OS Service Version
proxy-sql 172.30.1.49 Ubuntu 22.04.2 LTS ProxySQL version 2.4.2-0-g70336d4, codename Truls
mysql-master 172.30.1.97 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave1 172.30.1.10 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave2 172.30.1.13 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
proxy-orchestrator 172.30.1.24 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33, container.d

 

MySQL HA 기본 구성으로 1대의 마스터 서버와, 2대의 슬레이브 서버가 GTID 기반으로 복제 구성이 되어 있다.

이번 포스트에서 4개의 시나리오를 구성해 보았으며, 각 시나리오별로 실습 과정을 다루었다.

시나리오1. Slave Refactoring

Slave간에 복제 토폴로지 리팩토링으로, 슬레이브1, 슬레이브2가 마스터에서 싱크하던 환경에서, 슬레이브2가 슬레이브1에서 복제를 싱크할 수 있도록 토폴로지를 변경한다.

 

 

시나리오2. Master Refactoring (Promote)

슬레이브 서버를 마스터로 승격시키고, 기존의 마스터를 슬레이브로 역할을 전환한다.

 

 

시나리오3. Master – Master Refactoring

슬레이브 서버중 하나를 마스터로 승격시켜 두 대의 마스터로 구성한다.

 

 

시나리오4. Master Failover

기존의 운영중이던 마스터 서버를 강제로 중지하여 장애 상황을 구현한 다음, 현재 슬레이브로 운영되던 서버중 하나가 새로운 마스터가 되어 장애조치 된다. 그리고 나머지 슬레이브 서버는 새로운 마스터 서버와 복제 싱크를 한다.

 

 

실습환경 살펴보기

오케스트레이터 대시보드에 접속하면 현재 구성되어 있는 클러스터 목록을 볼 수 있다. 여기에서 숫자 3과 1이 보이는데, 3은 현재 구성된 클러스터의 전체 서버 개수이며, 2는 슬레이브 서버 개수이다.

 

 

클러스터를 클릭해보면 현구 구성되어 있는 클러스터 토폴로지를 확인할 수 있다. 각 시나리오에 따라 서버의 역할이 변경되기 때문에, 현재 구성되어 있는 서버이름과 역할을 혼돈하지 않도록 주의한다.

 

 

시나리오1. Slave Refactoring

Slave간에 복제 토폴로지 리팩토링으로, 슬레이브1, 슬레이브2가 마스터에서 싱크하던 환경에서, 슬레이브2가 슬레이브1에서 복제를 싱크 할 수 있도록 토폴로지를 변경한다.

 

mysql-slave2(172.30.1.13)서버를 드래그하여 mysql-slave1(172.30.1.10) 서버에 드롭한다. 이때 드롭 할 위치가 중요하다. 서버의 중간쯤으로 이동하면 드롭하는 서버 정보에 relocate라고 표시가 되는데 이때 드롭한다.

 

 

Relocate에 대한 변경 사항에 대해서 경고 팝업이 나타나고 OK를 클릭하면 변경 사항이 적용된다.

 

 

클러스터 토폴로지 구조가 변경된 것을 확인할 수 있다.

 

 

mysql-slave2(172.30.1.13) 서버에 접속하여 호스트 이름 및 복제 상태를 조회해 보면, 현재 mysql-slave1(172.30.1.10) 서버를 마스터 호스트로 연결되어 있는 것을 확인할 수 있다.

select @@host;
show slave status\G;

 

 

 

시나리오를 처음 상태로 원상복구하려면 mysql-slave2(172.30.1.13)를 드래그하여 mysql-master(172.30.1.97)에 드롭 한다.

 

 

Relocate에 대한 변경 사항에 대해서 경고 팝업이 나타나고 OK를 클릭하면 변경 사항이 적용된다.

 

 

클러스터 토폴로지 구조가 변경된 것을 확인할 수 있다.

 

 

mysql-slave2(172.30.1.13) 서버에 접속하여 호스트 이름 및 복제 상태를 조회해 보면, 현재 mysql-master(172.30.1.97) 서버가 마스터 호스트로 연결되어 있는 것을 확인할 수 있다.

 

 

시나리오2. Master Refactoring (Promote)

슬레이브 서버를 마스터로 승격시키고, 기존의 마스터를 슬레이브로 역할을 전환한다.

 

 

mysql-slave1(172.30.1.10)을 드래그 하여 mysql-master(172.30.1.97)로 드롭 한다. 이때 드롭하는 위치를 현재 마스터보다 조금 앞으로 위치하여 PROMOTE AS MASTER가 표시될 때 드롭 한다. (마스터 보다 뒤에 할 경우 relocate로 표시된다.)

 

 

Takeover에 대한 경고 팝업이 나타나고 OK를 클릭하면 변경 사항이 적용된다.

 

 

변경사항을 적용하였을 때, 대시보드 화면이 바로 갱신이 안되는 경우가 있다. 이때 새로고침을 하면 변경사항이 정상적으로 나타난다. 현재 구성되어 있는 토폴로지 상태를 보면, mysql-slave1(172.30.1.10) 서버가 master 역할을 하며, mysql-master(172.30.1.97)서버가 slave 역할을 하는 것으로 역할 체인지가 된 것을 확인할 수 있다. 하지만 mysql-master(172.30.1.97) 서버를 보면 역할 변경 후 새로운 마스터에 대한 복제 구성이 되지 않아서 오류로 표시되고 있다.

 

 

복제 시작을 위해 slave역할의 mysql-master(172.30.1.97) 서버에서 메뉴 아이콘을 클릭하면 설정 팝업이 나타나는데, [Start replication]을 클릭한다.

 

 

복제가 정상적으로 구성되고 모든 클러스터가 정상으로 표시되는 것을 확인할 수 있다.

 

 

다음 실습을 준비하기 위해 원래 클러스터 토폴로지 구조로 원상복구를 한다. 원상복구 방법은 앞에서 했던 방법과 동일하게 진행한다.

 

 

시나리오3. Master – Master Refactoring

슬레이브 서버중 하나를 마스터로 승격시켜, 두 대의 마스터로 구성한다.

 

 

mysql-master(172.30.1.97) 서버를 드래그 하여 mysql-slave1(172.30.1.10)에 드롭 한다. 이때 MAKE CO MASTER가 표시되면 드롭 한다.

 

 

make-co-master에 대한 경고 팝업이 나타나고 OK를 클릭하면 변경 사항이 적용된다.

 

 

실행이 완료되면 클러스터 토폴로지 구성이 master – master 로 변경된 것을 확인할 수 있다.

 

정확히는 master-master 구조만 만들어진 상황으로, 완벽한 master-master 작동을 하는 것은 아니다. 환경은 구성되었지만 mysql-slave(172.30.1.10) 서버는 현재 Read Only 상태이다. [Set writeable]를 클릭하여 쓰기 상태로 변경해야 사용가능한 master-master 환경이 된다.

 

 

master-master 구조에서 master-salve로 원복하기 위해서는 mysql-master(172.30.1.97) 서버에서 설정 아이콘을 클릭한다. 팝업이 나타나면 Master 항목에서 [Reset replica]를 클릭한다.

 

 

이전과 같은 상태로 클러스터 토폴로지가 구성된 것을 확인할 수 있다.

 

 

시나리오4. Master Failover

기존의 운영중이던 마스터 서버를 강제로 중지하여 장애 상황을 구현한 다음, 현재 슬레이브로 운영되던 서버중 하나가 새로운 마스터가 되어 장애조치 된다. 그리고 나머지 슬레이브 서버는 새로운 마스터 서버와 복제 싱크를 한다.

 

 

현재 master역할을 하고 있는 mysql-master(172.30.1.97) 서버에 접속하여 MySQL 서비스를 강제로 중지한다.

hostname
 
sudo systemctl stop mysql

 

 

 

MySQL 서비스가 중지되고 오케스트레이터가 장애 상황을 인지하면 아래와 같이 대시보드 상태가 변경된다. 파란색 숫자3은 현재 구성되어 있는 클러스터 서버 개수이며, 빨간색 숫자2는 슬레이브 개수이며 복제 불가능을 표시하고 있다. 검은색 1은 응답 없는 서버 개수를 나타낸다.

 

 

해당 클러스터를 클릭해서 상세 대시보드를 살펴보면, 현재 mysql-master(172.30.1.97) 서버가 응답 없음을 나타내고 있으며, 슬레이브는 복제가 되지 않는 것을 확인할 수 있다. 우측의 느낌표를 클릭하면 각 노드의 에러 메시지를 상세하게 확인할 수 있다.

 

오류 메시지는 오케스트레이터의 로그 파일에서도 확인할 수 있다. 특별히 경로를 변경하지 않았으면 아래 경로에서 확인 가능하다. 만약 경로 확인이 필요하면 설정 파일에서 확인할 수 있다.

/tmp/recovery.log

 

 

[수동 Failover]

오케스트레이터의 장애조치 모드 기본값은 수동이다. 수동으로 장애조치는 관리자가 문제를 인지하고 직접 장애 조치를 진행해야 한다. 현재 응답이 없는 mysql-master(172.30.1.97) 서버에서 [Recover]를 클릭한다. Failover 가능한 서버 목록에서, mysql-salve1(172.30.1.10)서버로 장애조치를 진행한다.

 

 

장애조치를 실행하게 되면, 응답 없는 서버를 기존의 클러스터에 분리된다. 기존 서비스가 중지된 mysql-master 노드는 기존 클러스터에서 분리된 것을 확인할 수 있다.

 

 

클러스터를 클릭하여 상세 내용을 보면 mysql-slave1(172.30.1.10) 서버가 master역할을 하고, mysql-slave2(172.30.1.13) 서버가 slave역할을 하는 것을 확인할 수 있다.

 

 

mysql-master(172.30.1.97) 서버를 복구했다는 가정하에 mysql-master(172.30.1.97) 서비스를 다시 시작한다.

 

 

MySQL 서버가 정상적으로 실행되었으며 현재는 독립적으로 실행되고 있기 때문에, 기존의 복제 클러스터에 조인하기 위해서는 현재 master 역할로 운영중인 mysql-slave1(172.30.1.10) 서버로 복제를 연결한다.

 

 

복제가 성공적으로 연결되고, 복제 싱크가 완료되면 오케스트레이터 대시보드에서도 클러스터가 정상적으로 연결되어 운영되는 것을 확인할 수 있다.

 

 

[자동 failover]

기본 모드가 수종이기 때문에, 자동으로 장애조치를 하기 위해서는 파라메터 수정이 필요하다. 아래 경로의 설정파일에서 수정할 수 있다. 설정 파라메터의 종류가 많기 때문에 주의해서 살펴본다.

vi /usr/local/orchestrator/orchestrator.conf.json

 

 

변경전 변경후
"RecoveryPeriodBlockSeconds": 3600,
  "RecoveryIgnoreHostnameFilters": [],
  "RecoverMasterClusterFilters": [
    "_master_pattern_"
  ],
  "RecoverIntermediateMasterClusterFilters": [
    "_intermediate_master_pattern_"
  ],		 "RecoveryPeriodBlockSeconds": 10,
  "RecoveryIgnoreHostnameFilters": [],
  "RecoverMasterClusterFilters": [
    "*"
  ],
  "RecoverIntermediateMasterClusterFilters": [
    "*"
  ],

 

 

설정 변경을 완료하였으면, 오케스트레이터는 설정 파일에 대한 변경사항을 런타임중에 적용할 수 있다. 대시보드 상단 메뉴에서 [Home] - [Status]를 클릭한다. Status페이지에서 [Reload congifuration]을 클릭하여 설정을 로드 한다.

 

 

자동 장애조치 테스트를 위해 현재 master로 운영중인 서버의 MySQL을 강제로 중지한다.

 

 

Master서버가 중지되고 오케스트레이터에서 장애 상황을 인지한 다음 자동으로 장애조치가 완료되면 대시보드에서 아래와 같이 두 개의 클러스터를 볼 수 있다. 장애가 발생한 서버는 기존 클러스터에서 분리가 되었다.

 

 

각 클러스터를 클릭해서 상태를 살펴보면, 기존의 slave 서버중에 하나가 master로 승격되고, 나머지 슬레이브가 새로운 마스터에 조인되어 싱크되는 것을 확인할 수 있다.

 

분리된 장애 서버에서는 Recover를 클릭해도 클러스터에서 분리된 상태이기 때문에 failover할 수 있는 서버 목록을 찾을 수 없다고 표시된다.

 

 

지금까지 MySQL HA환경에서 오케스터레이터를 활용하여 토포로지 리팩토링 및 장애조치 하는 방법에 대해서 살펴보았다. 장애조치를 진행할 때 운영중인 서버중 하나가 랜덤하게(정확히는 씽크가 가장 빠른 서버) 마스터로 승격되는 것을 확인하였는데, 이 또한 파라메터를 수정하여 다양한 조건을 부여하여 우선순위조건을 조정할 수 있다. 앞애서도 살펴보았듯이 파라메터의 종류가 매우 많다. 상세한 내용은 다른 포스트에서 하나씩 살펴볼 예정이다.

 

 

 

[참고자료]

l   Orchestrator : https://github.com/openark/orchestrator

l   Failure detection : https://github.com/openark/orchestrator/blob/master/docs/failure-detection.md

l   MySQL Orchestrator - HA(High Availability) - 2 - 리팩토링 Failover Automated Recovery : https://hoing.io/archives/91

l   Configuration: failure detection : https://github.com/openark/orchestrator/blob/master/docs/configuration-failure-detection.md

l   https://code.openark.org/blog/mysql/what-makes-a-mysql-server-failurerecovery-case : https://code.openark.org/blog/mysql/what-makes-a-mysql-server-failurerecovery-case

l   Orchestrator: MySQL Replication Topology Manager : https://www.percona.com/blog/orchestrator-mysql-replication-topology-manager/

l   Orchestrator and ProxySQL : https://www.percona.com/blog/orchestrator-and-proxysql/

 

 

 

2023-07-27 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, MySQL Replication, MySQL HA, MySQL복제 오케스트레이션, MySQL복제설치, MySQL MHA, MySQL Orchestrator, MySQL장애조치, MySQL고가용성

 

저작자표시 비영리 변경금지

'MySQL, MariaDB' 카테고리의 다른 글

ProxySQL 은 서비스에 필요한 설정값을 어디에 저장하고 재사용할까?  (0) 2023.08.04
MySQL HA + ProxySQL 환경에서 서비스 장애조치 구성  (0) 2023.08.03
MySQL 고가용성 운영을 위한 Orchestrator 설치  (0) 2023.07.25
ProxySQL Internals 및 시스템 구성 둘러보기  (0) 2023.07.24
ProxySQL 쿼리룰 설정으로 Read, Write 부하 분산하기  (0) 2023.07.23

MySQL 고가용성 운영을 위한 Orchestrator 설치

 

l  Version : MySQL, Orchestrator

 

데이터베이스를 운영할 때 단일 장애 포인트(SPOF, Single Point Of Failure)를 예방하기 위해서 고가용성(HA, High-Available)은 매우 중요한 부분이다. 그렇기 때문에 클러스터링, 복제 등 고가용성으로 구성된 다수의 데이터베이스를 효율적으로 관리하기 위해 다양한 솔루션들이 존재한다.  

이번 포스트는 MySQL 및 MariaDB의 고가용성을 효율적으로 운영하기 위한 솔루션인 Orchestrator(이하 ‘오케스트레이터’)에 대해서 알아본다. 오케스트레이터는 MySQL 고가용성 및 복제 관리 도구로, 서비스로 실행되며 명령줄 액세스, HTTP API 및 웹 인터페이스를 제공한다.

 

l  Orchestrator : https://github.com/openark/orchestrator

 

오케스트레이터의 주요 기능으로는 아래와 같다.

l  Discovery : 토폴로지를 통해 지속적으로 클러스터의 복제 상태 및 구성과 같은 기본 MySQL 정보를 읽어 오케스트레이션에 매핑한다. 장애가 발생하였을 때 복제 문제를 포함하여 토폴로지의 자세한 정보를 시각화로 제공한다.

l  Refactoring : 복제 서버의 Binlog file:position, GTID, Pseudo GTID등 다양한 정보를 수집한다. 복제 토폴로지에서 리팩토링은 GUI에서 복제본을 다른 마스터 아래로 끌어서 놓는 방식으로 안전하게 진행할 수 있다. 오케스트레이터는 비정상적인 리팩토링 시도를 거부하며 다양한 명령줄 옵션을 통해 세밀한 제어가 가능하다.

l  Recovery : 전체적인 접근 방식을 사용하여 마스터 및 중간 마스터 오류를 감지한다. 토폴로지 자체에서 얻은 정보를 기반으로 다양한 장애 시나리오를 인식한다. 장애가 인식되면 자동 또는 수동 복구를 수행하도록 구성할 수 있다. 복구 프로세스는 오케스트레이터의 토폴로지 이해와 리팩토링 수행 능력을 활용하며, 구성이 아닌 상태를 기반으로 한다. 오케스트레이터는 자체 복구시 토폴로지를 조사/평가하여 최상의 복구 방법을 선택한다.

 

오케스트레이터는 다양한 인터페이스를 지원하는데 명령줄 인터페이스(CLI, Command Line Interface)를 활용하여 디버그 메시지 등을 수집하고 자동화된 스크립팅을 제어할 수 있다. 그 외에도 다양한 Web API (HTTP Get access) 및 웹 인터페이스를 제공한다.

l  Highly available

l  Controlled master takeovers

l  Manual failovers

l  Failover auditing

l  Audited operations

l  Pseudo-GTID

l  Datacenter/physical location awareness

l  MySQL-Pool association

l  HTTP security/authentication methods

 

 

 

Orchestrator를 사용하려면 우선 MySQL복제 환경이 구성되어 있어야 한다. 오케스트레이터 구성에서 GTID 사용을 권장하고 있으나, Binlog position 사용시 Pseudo GTID를 사용하면 된다. MySQL 복제 구성은 아래 링크를 참고한다.

l  ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에) : https://sungwookkang.com/1529

 

오케스트레이터를 구성할 서버 정보들이다. 위 링크에서 구성한 환경을 기반으로 진행한다.

Server Name IP OS Service Version
proxy-sql 172.30.1.49 Ubuntu 22.04.2 LTS ProxySQL version 2.4.2-0-g70336d4, codename Truls
mysql-master 172.30.1.97 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave1 172.30.1.10 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave2 172.30.1.13 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
proxy-orchestrator 172.30.1.24 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33, container.d

 

proxy-orcherator서버에는 오케스트레이터가 설치되어 운영된다. 오케스트레이터에서 필요한 정보를 저장하기 위해 오케스트레이터용 리포지토리 MySQL을 설치한다.

sudo apt-get install mysql-server

 

MySQL설치가 완료되면 오케스트레이터가 사용할 계정 및 데이터 베이스를 생성한다. 테스트 용도이기 때문에 편의상 전체 권한을 부여한다. 실제 업무에서 사용할 때에는 보안 정책에 따라 오케스트레이터에 필요한 권한만 할당한다.

--데이터베이스 생성
CREATE DATABASE IF NOT EXISTS orchestrator;
 
--사용자 생성
CREATE USER 'orchestrator'@'%' IDENTIFIED BY 'orchestrator';
--GRANT SUPER, PROCESS, REPLICATION SLAVE, RELOAD ON *.* TO 'orchestrator'@'%';
--GRANT SELECT ON mysql.slave_master_info TO 'orchestrator'@'%';
 
GRANT ALL PRIVILEGES ON *.* TO 'orchestrator'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

 

 

이번 포스트에서는 컨테이너로 빌드된 오케스트레이터를 설치하기 때문에, 컨테이너를 실행할 수 있는 도커 패키지를 설치한다.

--시스템 패키지 업데이트
sudo apt-get update
 
--필요한 패키지 설치
sudo apt-get install apt-transport-https ca-certificates curl gnupg-agent software-properties-common
 
--Docker의 공식 GPG키를 추가
curl -fsSL https://download.docker.com/linux/ubuntu/gpg | sudo apt-key add -
 
--Docker의 공식 apt 저장소를 추가
sudo add-apt-repository "deb [arch=amd64] https://download.docker.com/linux/ubuntu $(lsb_release -cs) stable"
 
--시스템 패키지 업데이트
sudo apt-get update
 
--Docker 설치
sudo apt-get install docker-ce docker-ce-cli containerd.io
 
--Docker 서비스 실행 확인
sudo systemctl status docker

 

 

 

도커 설치가 완료되었으면, 도커 허브에서 오케스트레이터 컨테이너를 다운로드 한다.

ü  https://hub.docker.com/r/openarkcode/orchestrator

 

 

아래 스크립트를 실행하면 도커 이미지가 다운로드 되며, 현재 호스트에 저장되어 있는 도커 이미지를 확인할 수 있다.

sudo docker pull openarkcode/orchestrator
sudo docker images

 

 

 

아래 스크립트를 사용하여 도커를 실행한다.

sudo docker run -d --name orchestrator -p 3000:3000 openarkcode/orchestrator

 

 

 

오케스트레이터 서비스가 정상적으로 실행되었으면, 웹 브라우저를 사용하여 오케스트레이터 대시보드로 접속한다.

http://172.30.1.24:3000/

 

 

상단의 [Cluster] – [Discover]에서 MySQL 클러스터를 등록할 수 있다. 기본값으로 호스트 이름으로 등록하게 되어 있다. 호스트 이름으로 MySQL 서버들을 검색하려면 proxy-orchstrator (오케스트레이터의 호스트 서버) 서버의 /etc/hosts 파일에 호스트 정보를 등록한다.

 

 

서버 이름이 아닌 서버IP로 클러스터 서버를 찾기 위해서는 아래와 같이 설정 파일을 수정하여 사용할 수 있다. 기본적으로 설정 파일은 아래 경로에 있다.

/usr/local/orchestrator/

 

컨테이너 환경에서는 컨테이너 내부에서 파일 수정을 하는 것은 좋은 방법은 아니지만 실습 편의상 컨테이너 내부 쉘로 접근해서 수정하였다. 실제 서비스 환경에서는 컨테이너 특성상 데이터가 휘발되기 때문에 반드시 설정파일 따로 보관할 수 있도록 한다. 아래 스크립트는 실행 중인 컨테이너의 ID를 확인하고, 해당 컨테이너 쉘로 접근한다.

sudo docker ps -a
sudo docker exec -it 93c5bfafa537 /bin/bash

 

 

 

설정파일이 위치한 경로로 이동하여 설정 파일을 수정한다.

cd /usr/local/orchestrator/
sudo cp orchestrator-sample.conf.json orchestrator.conf.json
vi orchestrator.conf.json

 

 

오케스트레이터 서버가 MySQL 클러스터 서버들에게 접속할 수 있는 계정과 비밀번호를 입력한다. 사용자 환경에 맞게 수정하여 사용한다.

"MySQLTopologyUser": "orc_client_user",
"MySQLTopologyPassword": "orc_client_password",

 

오케스트레이터 서버가 orchester 데이터베이스 접속할 수 있는 계정과 비밀번호를 입력한다. 오케스트레이터용 리포지토리 MySQL 설치 단계에서 생성한 계정을 입력하도록 한다.

"MySQLOrchestratorHost": "127.0.0.1",
"MySQLOrchestratorPort": 3306,
"MySQLOrchestratorDatabase": "orchestrator",
"MySQLOrchestratorUser": " orchestrator ",
"MySQLOrchestratorPassword": " orchestrator ",

 

 

호스트 이름 대신 IP로 검색할 수 있도록 옵션을 수정한다.

변경 전 "HostnameResolveMethod": "default",
"MySQLHostnameResolveMethod": "@@hostname",
변경 후 "HostnameResolveMethod": "none",
"MySQLHostnameResolveMethod": "",

 

GTID를 사용하지 않고 binlog position을 사용할 경우 failover를 위해서 PeseudoGTID를 활성화 하기위해 아래 파리메터를 입력한다. 해당 파라메터는 설정 파일에 없으므로 직접 추가해야 한다.

"AutoPseudoGTID": true,

 

 

각종 로그파일은 아래 경로에서 확인할 수 있다. 필요시 사용자 경로에 맞게 수정하여 사용할 수 있다.

"OnFailureDetectionProcesses": [
"echo 'Detected {failureType} on {failureCluster}. Affected replicas: {countSlaves}' >> /tmp/recovery.log"
  ],
"PreGracefulTakeoverProcesses": [
"echo 'Planned takeover about to take place on {failureCluster}. Master will switch to read_only' >> /tmp/recovery.log"
  ],
"PreFailoverProcesses": [
"echo 'Will recover from {failureType} on {failureCluster}' >> /tmp/recovery.log"
  ],
"PostFailoverProcesses": [
"echo '(for all types) Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Successor: {successorHost}:{successorPort}' >> /tmp/recovery.log"
  ],
"PostUnsuccessfulFailoverProcesses": [],
"PostMasterFailoverProcesses": [
"echo 'Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Promoted: {successorHost}:{successorPort}' >> /tmp/recovery.log"
  ],
"PostIntermediateMasterFailoverProcesses": [
"echo 'Recovered from {failureType} on {failureCluster}. Failed: {failedHost}:{failedPort}; Successor: {successorHost}:{successorPort}' >> /tmp/recovery.log"
  ],
"PostGracefulTakeoverProcesses": [
"echo 'Planned takeover complete' >> /tmp/recovery.log"
  ],

 

설정값 변경이 완료되었으면 해당 사항을 저장하고 오케스트레이터 서비스를 재시작한다. 오케스트레이터 대시보드에 접속하여 IP로 Discover를 등록할 수 있다. 서버는 master 역할 서버만 입력한다.

 

 

정상적으로 등록이 완료되면 아래와 같이 등록된 서버가 나타난다.

 

 

해당 서버를 클릭하면 현재 구성된 토폴로지 및 상태를 나타내는 대시보드를 볼 수 있다,

 

 

대시보드에 나타난 서버들을 클릭해보면 현재 상태의 상세 정보를 확인할 수 있으며, 일부 제어를 할 수 있는 메뉴가 나타난다.

 

 

 

 

[참고자료]

l  Orchestrator : https://github.com/openark/orchestrator

l  MySQL Orchestrator - HA(High Availability) - 1 - 기능 설명 및 설치 : https://hoing.io/archives/72

l  https://code.openark.org/blog/mysql/what-makes-a-mysql-server-failurerecovery-case : https://code.openark.org/blog/mysql/what-makes-a-mysql-server-failurerecovery-case

l  Orchestrator: MySQL Replication Topology Manager : https://www.percona.com/blog/orchestrator-mysql-replication-topology-manager/

l  Orchestrator and ProxySQL : https://www.percona.com/blog/orchestrator-and-proxysql/

 

 

 

 

2023-07-25 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, MySQL Replication, MySQL HA, MySQL복제 오케스트레이션, MySQL복제설치, MySQL MHA, MySQL Orchestrator

저작자표시 비영리 변경금지

'MySQL, MariaDB' 카테고리의 다른 글

MySQL HA + ProxySQL 환경에서 서비스 장애조치 구성  (0) 2023.08.03
MySQL HA 환경에서 Orchestrator를 활용한 클러스터 리팩토링 및 자동 장애조치 구성  (0) 2023.07.27
ProxySQL Internals 및 시스템 구성 둘러보기  (0) 2023.07.24
ProxySQL 쿼리룰 설정으로 Read, Write 부하 분산하기  (0) 2023.07.23
ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에)  (0) 2023.07.22

ProxySQL Internals 및 시스템 구성 둘러보기

 

l  Version : ProxySQL

 

이번 포스트에서는 ProxySQL의 인터널 구조와 설정 파일의 구조에 대해서 살펴본다. 상세한 인터널 구조를 모두 설명하지는 않으며 전체적인 구조 및 특징만 소개하도록 한다. 세부적인 내용은 다른 포스트에서 하나씩 다뤄볼 예정이다.

 

ProxySQL이 시작되면 즉시 새 프로세스를 생성한다. 상위 프로세스는 엔젤 프로세스(와치독과 비슷한 의미로 해석됨)로 작동하고 ProxySQL이 크래시 되었을 때 수 밀리초 내에 서비스를 다시 시작한다. MySQL에 비유하면 mysqld_safe와 매우 유사하다.

ProxySQL에는 기본적으로 두 개의 포트가 필요하다. 트래픽을 수신하는 6033 포트와 ProxySQL을 관리하기 위한 게이트웨이 역할을 하는 6032포트이다. 명령줄 관리 인터페이스(CLI)는 MySQL 클라이언트에서 액세스할 수 있다. 대부분의 구성은 SQL을 사용하여 수행된다. ProxySQL이 구성을 저장하는 데 사용하는 기본 데이터베이스는 SQLite이지만 가능한 한 MySQL에 가까운 환경을 만들기 위해 노력한 흔적을 볼 수 있다. 그 예로 일부 MySQL 구문을 사용하여 ProxySQL 구성을 확인하거나(SHOW GLOBAL VARIABLES) 새 구성을 설정 (SET GLOBAL variable_name = value) 할 수 있다. 백엔드 구성은 테이블에 저장된다. 모든 변경 사항은 SQL(INSERT, UPDATE, DELETE)을 통해 이루어진다.

 

 

l  Query processor/rules engine : ProxySQL은 MySQL 프로토콜(쿼리)을 해석한다. 규칙 엔진은 들어오는 트래픽을 일치시키고 쿼리를 캐시할지 또는 호스트 그룹 대상에 쿼리를 차단, 경로 재지정, 쿼리 재작성 또는 미러링 할지 여부를 정의한다.

l  User authentication : 기본 데이터베이스에 대한 사용자 자격 증명이 해시되어 프록시에 저장된다.

l  Hostgroup manager : 트래픽을 보낼 서버 그룹을 관리하고 해당 상태를 추적한다.

l  Connection pool : 백엔드 데이터베이스에 대한 연결을 관리한다. 연결 풀은 백엔드를 향해 설정되며 모든 애플리케이션에서 공유/재사용된다.

l  Monitoring : 백엔드를 모니터링하고 지표를 수집한다. 모니터링을 하면서 응답하지 않는 호스트 또는 복제 지연에 대해 필요에 따라 트래픽을 제어한다.

 

Query Processor는 SQL 트래픽을 분석하고 이해할 수 있으므로 광범위한 라우팅을 수행할 수 있다. 스키마, 사용자, 호스트, 포트 또는 정규 표현식과 같은 다양한 속성으로 쿼리를 일치시킬 수 있다. 이렇게 하면 변경하려는 쿼리와 관련하여 상당한 유연성을 얻을 수 있다. 예를들어 쿼리캐시는 쿼리 대기 시간을 줄일 수 있도록 정의된 시간(TTL) 동안 캐시할 수 있다. 이러한 기능은 일반적으로 응용 프로그램에 추가 복잡성을 도입하는 외부 캐싱 계층의 필요성을 제거할 수도 있다. 또한 MySQL 쿼리 캐시(결국 8.0에서 제거됨)보다 훨씬 나은 솔루션일 수도 있다. 그리고 사용자 정의에 따라 쿼리를 제한할 수도 있다. 예를들어 성능에 영향을 미치는 무거운 쿼리들을 초당 한 번 또는 분당 한 번만 실행되도록 할 수 커스텀하게 설정할 수 있다. 쿼리 미러링기능도 있지만 아직은 모든 트래픽이 미러되는 것은 아니기 때문에 큰 기대를 할 정도는 아니다. 그러나 특정 상황에서 매우 유용하게 활용하 수 있다. 주어진 쿼리에 대해 프로덕션 호스트뿐만 아니라 별도의 위치로 보내서 슬로우 로그에서 캡처하거나 일부 디버깅을 수행할 수 있다. 쿼리 재작성을 통해 쿼리를 즉석에서 재작성할 수 있다. 간단한 FORCE INDEX로 문제를 해결할 수 있지만 앱 수정, 테스트, 새 코드 롤 등의 시간 소모적인 프로세스를 거치지 않고는 문제를 추가할 수 없는 상황에서, ProxySQL에서 쿼리를 다시 작성하면서 인덱스 힌트 등을 추가할 수 있다.

 

ProxySQL은 매우 복잡한 구조를 가진것 처럼 보이지만, 시스템을 구성하고 사용하는데 있어서는 4개의 계층(Runtime, Memory, Disk, Configuration file)을 제공하여 쉽게 사용할 수 있다. 특징으로는 설정에 대해서 즉시 업데이트가 되고, 데몬의 재시작 없이 적용할 수 있다. 또한 잘못 구성되었을 경우에도 쉽게 롤백 할 수 있다.

 

RUNTIME

Runtime은 ProxySQL 기동 및 운영에 사용된다. 요청을 처리하는 스레드에서 사용하는 ProxySQL의 메모리 내 데이터 구조를 나타낸다. 여기에는 사용된 전역 변수의 값, 호스트 그룹으로 그룹화된 백엔드 서버 목록 또는 프록시에 연결할 수 있는 MySQL 사용자 목록이 포함된다. 운영자는 RUNTIME 구성 섹션의 내용을 직접 수정할 수 없다. 하위 레이어를 수정하고 그 내용을 적용시킬 수 있다.

 

MEMORY

MEMORY(메인이라고도 함)는 MySQL 호환 인터페이스를 통해 외부에 노출되는 메모리 내 SQLite3 데이터베이스를 나타낸다. 사용자는 이 인터페이스에 MySQL 클라이언트를 연결하고 다른 테이블과 데이터베이스를 쿼리할 수 있다. 이 인터페이스를 통해 사용할 수 있는 구성 테이블은 다음과 같다.

l   mysql_servers : ProxySQL에서 중계할 서버들의 목록을 관리한다. 중계 대상 서버를 백엔드 서버라고 부른다.

l   mysql_users : ProxySQL에 연결할 수 있는 사용자 및 자격 증명 목록으로, ProxySQL은 이러한 자격 증명을 사용하여 백엔드 서버에도 연결한다.

l   mysql_query_rules : 다른 백엔드 서버로 트래픽을 라우팅하기 위한 규칙 목록으로 이러한 규칙으로 인해 쿼리를 다시 작성하거나 결과를 캐싱할 수도 있다.

l   global_variables : 런타임에 조정할 수 있는 프록시 전체에서 사용되는 전역 변수 목록이다. 아래 그림은 글로벌 전역 변수 예시이다.

 

 

l   mysql_collations : 프록시가 작업할 수 있는 MySQL 데이터 정렬 목록으로 클라이언트 라이브러리에서 직접 추출된다.

l   debug_levels (디버그 빌드에서만 사용 가능) : ProxySQL이 세부 수준과 함께 내보내는 디버그 문의 유형 목록이다. 이를 통해 다양한 문제를 디버깅하기 위해 로그에 어떤 종류의 명령문이 있는지 런타임에 쉽게 구성할 수 있다. 성능에 영향을 줄 수 있으므로 디버그 빌드에서만 사용할 수 있다.

 

DISK

DISK는 기본 위치가 $(DATADIR)/proxysql.db인 온디스크 SQLite3 데이터베이스를 나타낸다. ProxySQL 자체 데이터베이스가 실제로 동작하는 곳의 이름이 memory인 이유는, 실제로 in-memory 형태의 SQLite3 데이터베이스가 상주하고 있기 때문이다. ProxySQL 서비스를 다시 시작하면 유지되지 않은 메모리 내 구성이 손실되므로 구성을 DISK에 유지하는 것이 중요하다.

 

CONFIG

CONFIG 파일은 전형적인 구성 파일이다. Memory에서 운영중인 설정 파일을 덤프할 수 있다. 서비스를 시작하는 동안 ProxySQL은 구성 파일(있는 경우)을 읽어, 지정된 경로에서 내부 데이터베이스 파일을 찾으려고 시도한다. 지정된 경로에 데이터베이스 파일을 발견되면 ProxySQL은 디스크의 데이터베이스를 사용하여 메모리 구성을 초기화한다. 즉, 디스크 구성이 MEMORY에 로드되고 RUNTIME에도 전파된다. 데이터베이스 파일을 찾을 수 없는 경우(예: ProxySQL이 처음으로 실행 중이거나 데이터베이스가 삭제된 경우) 구성 파일의 정보가 MEMORY에 로드되고 DISK 데이터베이스에 저장되며 RUNTIME에도 전파된다.

l   Initial startup (옵션 –initial) : ProxySQL을 처음 시작하는 동안 메모리 및 런타임 구성이 구성 파일에서 채워지고 그 후 모든 구성이 ProxySQL의 내부 데이터베이스에 저장된다. –initial 옵션은 SQLite 데이터베이스 파일을 원래 상태(즉, 구성 파일에 정의된 상태)로 재설정하고 기존 SQLite 데이터베이스 파일의 이름을 바꾸는 플래그 와 함께 proxysql을 실행하여 초기 구성을 강제로 다시 발생시킬 수 있다. 롤백이 필요한 경우 사용한다.

l   Reload startup (옵션 –reload flag) : ProxySQL 바이너리가 --reload플래그와 함께 실행되면 구성 파일의 구성을 데이터베이스 파일의 내용과 병합하려고 시도한다. 그런 다음 새로 병합된 구성을 사용하여 시작된다. 충돌이 있을 때 ProxySQL이 두 구성 소스를 성공적으로 병합할 것이라는 보장은 없으며 사용자는 항상 병합이 예상대로 실행되었는지 확인해야 한다.

l   Runtime ProxySQL Configuration : 런타임 시 구성  설정은 ProxySQL(기본적으로 6032)의 ProxySQL 관리 포트를 통해 수행된다. MySQL 호환 클라이언트로 연결하면 다양한 기본 ProxySQL 구성 및 통계 테이블을 쿼리하기 위한 표준 MySQL 스타일 인터페이스가 표시된다.

 

 

계층 간에 구성을 이동하기 위해 관리 인터페이스를 통해 사용할 수 있는 다양한 관리 명령 세트가 있다. 각 레이어간 이동 및 명령어은 아래 링크를 참고한다.

l   Moving config between layers : https://github.com/sysown/proxysql/blob/master/doc/configuration_system.md

 

 

[참고자료]

l   Database load balancing for MySQL and MariaDB with ProxySQL : https://severalnines.com/resources/whitepapers/database-load-balancing-for-mysql-and-mariadb-with-proxysql/

l   Multi layer configuration system : https://proxysql.com/documentation/configuring-proxysql/

l   Configuration system : https://github.com/sysown/proxysql/blob/master/doc/configuration_system.md  

 

 

 

 

2023-07-24 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, MySQL Replication, MySQL HA, Replication, MySQL복제, 마스터 슬레이브, ProxySQL설치, MySQL복제설치, 쿼리룰설정 ,ProxySQL Internals, ProxySQL Architecture, ProxySQL 아키텍처, ProxySQL 인터널스

저작자표시 비영리 변경금지

'MySQL, MariaDB' 카테고리의 다른 글

MySQL HA 환경에서 Orchestrator를 활용한 클러스터 리팩토링 및 자동 장애조치 구성  (0) 2023.07.27
MySQL 고가용성 운영을 위한 Orchestrator 설치  (0) 2023.07.25
ProxySQL 쿼리룰 설정으로 Read, Write 부하 분산하기  (0) 2023.07.23
ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에)  (0) 2023.07.22
ProxySQL 이란 무엇인가  (0) 2023.07.20

ProxySQL 쿼리룰 설정으로 Read, Write 부하 분산하기

 

l  Version : ProxySQL, MySQL 8.X

 

ProxySQL은 MySQL 및 포크된 데이터베이스(Percona Server 및 MariaDB)를 위한 고성능, 고가용성을 제공하는 프록시이다.

l  ProxySQL이란 무엇인가 : https://sungwookkang.com/1528

 

ProxySQL을 구성할 때 MySQL 서버 환경이 복제 구성이 되어 있으면 Write, Read를 효과적으로 분산하여, ProxySQL장점을 조금 더 효율적으로 활용할 수 있다. MySQL설치, 복제구성, ProxySQL 설치는 아래 링크를 참고한다.

l  ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에) : https://sungwookkang.com/1529

 

이번 포스트에서는 ProxySQL + MySQL Replication 환경에서, 쿼리 타입(INSET UPDATE, DELET, SELECT)에 따라 Write, Read를 분기하여 호출하는 방법에 대해서 알아본다. 실습에 사용된 서버 및 환경은 아래와 같다.

Server Name IP OS Service Version
proxy-sql 172.30.1.49 Ubuntu 22.04.2 LTS ProxySQL version 2.4.2-0-g70336d4, codename Truls
mysql-master 172.30.1.97 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave1 172.30.1.10 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave2 172.30.1.13 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33

 

이전의 글 (https://sungwookkang.com/1529)에서 ProxySQL을 구성할 때 서버를 등록하는 과정에서, 모든 서버를 동일한 그룹으로 지정하였다. 쿼리 타입에 따라 각기 다른 서버로 호출하기 위해서는 서버 특성에 따라 그룹 할당이 필요 하다. 이번 포스트에서는 1대의 Write서버와 2대의 Read서버로 구성하는 그룹으로 구성한다. proxy-sql 서버에서 아래 스크립트를 사용하여 데이터베이스에 접속한다.

mysql -uadmin -padmin -h 127.0.0.1 -P6032 --prompt='Admin> '

 

proxy-sql 데이터베이스에 접근이 되었으면, 아래 스크립트를 사용하여, write및 read 서버를 등록한다. 이때 필요한 그룹을 지정할 수 있도록 한다.

l  Write : hostgroup_id = 1

l  Read : hostgroup_id = 2

--기존 서버 목록 삭제
DELETE FROM mysql_servers;
 
--master (write) 추가
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.30.1.97', 3306);
 
--slave (read) 추가
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '172.30.1.10', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (2, '172.30.1.13', 3306);
 
--추가된 서버 확인
select * from mysql_servers;
 
-- 변경사항 반영
LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

 

 

사용자 기본 호스트 그룹을 지정한다. hostgroup_id = 1을 기본으로 지정하였다.

-- hostgroup_id를 기본 1로 지정
UPDATE mysql_users SET default_hostgroup = 1;
 
-- 변경사항 반영
LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

 

 

쿼리 타입에 따라 각 그룹으로 분기할 룰을 지정한다. SELECT쿼리는 hostgroup_id = 2의 서버로 호출하고, 나머지 쿼리는 hostgroup_id = 1 서버로 호출한다. 이때 동일 호스트 그룹에 2대 이상의 서버가 있으면 ProxySQL에서 라운드로빈으로 로드밸런싱을 한다.

-- 기존 쿼리 룰 삭제
DELETE FROM mysql_query_rules;
 
-- SELECT로 시작하는 쿼리는 hostgroup=2(slave)로 라우팅
INSERT INTO mysql_query_rules (rule_id, active, apply, match_digest, destination_hostgroup) VALUES (1, 1, 1, '^SELECT', 2);
 
-- 변경사항 반영
LOAD MYSQL QUERY RULES TO RUNTIME;
SAVE MYSQL QUERY RULES TO DISK;

 

 

 

쿼리 룰 설정이 완료되었으면, 쿼리를 실행하고 정상적으로 분기되는지 확인이 필요하다. 그 전에 각 서버에 general_log를 활성화하여, 서버로 호출되는 쿼리를 기록할 수 있도록 한다. 아래 스크립트는 로그를 테이블 및 파일에 동시에 기록한다.

mysql-master mysql-slave1 mysql-slave2
set global log_output = 'TABLE,FILE';
set global general_log = on;
show variables like '%general%';
show variables LIKE '%log_output%';

 

 

 

모든 설정이 완료되었으면, 클라이언트 도구(ex : MySQL Workbench)를 사용하여, proxy-sql 서버에 접속한 다음, INSERT 및 SELECT 쿼리를 호출 한다. 여러번 호출하여 라운드로빈이 발생하였을 때 각 서버로 유입 될 수 있도록 한다.

proxy-sql
#proxy-sql
select * from testdb.tbl_a;
 
insert into testdb.tbl_a (name) values ('insert test for proxy5');
 
select * from testdb.tbl_a;

 

 

 

mysql-master 서버에서 아래 스크립트를 사용하여, 제네럴로그를 살펴본다. argument컬럼이 BLOB 타입이어서, 문자형식으로 캐스팅하였다. 쿼리 결과를 살펴보면 INSERT 쿼리만 기록된 것을 확인할 수 있다.

mysql-master
#mysql-mster
SELECT
                  event_time, user_host, thread_id, server_id, command_type, argument,
                  CAST(argument as char(100)) as 'cast_arg'
FROM mysql.general_log
WHERE argument LIKE 'select%'
                  or argument LIKE 'insert%'
ORDER BY event_time DESC LIMIT 5;

 

 

 

mysql-slave1 서버에서 아래 스크립트를 사용하여, 제네럴로그를 살펴본다. argument컬럼이 BLOB 타입이어서, 문자형식으로 캐스팅하였다. 쿼리 결과를 살펴보면 INSERT 쿼리만 기록된 것을 확인할 수 있다.

mysql-slave1
#mysql-slave1
SELECT
                  event_time, user_host, thread_id, server_id, command_type, argument,
                  CAST(argument as char(100)) as 'cast_arg'
FROM mysql.general_log
WHERE argument LIKE 'select%'
                  or argument LIKE 'insert%'
ORDER BY event_time DESC LIMIT 5;

 

 

 

mysql-slave2 서버에서 아래 스크립트를 사용하여, 제네럴로그를 살펴본다. argument컬럼이 BLOB 타입이어서, 문자형식으로 캐스팅하였다. 쿼리 결과를 살펴보면 INSERT 쿼리만 기록된 것을 확인할 수 있다.

mysql-slave2
#mysql-slave2
SELECT
                  event_time, user_host, thread_id, server_id, command_type, argument,
                  CAST(argument as char(100)) as 'cast_arg'
FROM mysql.general_log
WHERE argument LIKE 'select%'
                  or argument LIKE 'insert%'
ORDER BY event_time DESC LIMIT 5;

 

 

 

지금까지 ProxySQL에서 쿼리 타입에 따른 룰을 지정하여, Write 및 Read를 분기하는 방법에 대해서 알아보았다. 실제 현업에 사용할 때에는 로그 기록에 따른 오버헤드가 발생할 수 있으므로 사용자 환경에 따라 제네럴로그 기록을 OFF 또는 적절히 조절할 수 있도록 한다. 대용량 트래픽 환경에서 Write 및 Read 쿼리를 분기 및 분산함으로써 DB 서버의 오버헤드를 분산하여 유용하게 활용할 수 있으리라 생각한다.

 

 

2023-07-23 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, MySQL Replication, MySQL HA, Replication, MySQL복제, 마스터 슬레이브, ProxySQL설치, MySQL복제설치, 쿼리룰설정

저작자표시 비영리 변경금지

'MySQL, MariaDB' 카테고리의 다른 글

MySQL 고가용성 운영을 위한 Orchestrator 설치  (0) 2023.07.25
ProxySQL Internals 및 시스템 구성 둘러보기  (0) 2023.07.24
ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에)  (0) 2023.07.22
ProxySQL 이란 무엇인가  (0) 2023.07.20
MySQL Master DB를 백업하여 Replication Slave DB 구성하기  (0) 2021.05.29

ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에)

 

l  Version : ProxySQL, MySQL 8.X

 

ProxySQL은 MySQL 및 포크된 데이터베이스(Percona Server 및 MariaDB)를 위한 고성능, 고가용성을 제공하는 프록시이다.

l  ProxySQL이란 무엇인가 : https://sungwookkang.com/1528

 

이번 포스트는 ProxySQL을 구성하는 방법에 대해서 다뤄본다. 이번 실습에서 구성되는 ProxySQL의 아키텍처는 아래 그림과 같다. 모든 클라이언트 연결은 ProxySQL로 연결이 되고, ProxySQL에서는 쿼리 타입에 따라 Write와 Read를 분산하여 요청한다. MySQL 구성은 Master에서 Slave1, Slave2로 GTID를 사용하여 복제를 구성한다. DB 클러스터에 보면 Hostgroup라는 것을 볼 수 있는데, 이는 ProxySQL에서 Failover를 감지하여 역할을 변경할 때, 그룹단위로 이동할 수 있도록 MySQL을 역할에 따라 그룹을 만들었다.

 

GTID
Global Transaction Identifier의 약자로 MySQL 복제에서 서버의 각 트랜잭션을 구분하는 고유한 식별자이다. GTID는 모든 트랜잭션과 1:1 관계이며, GTID를 활용하면 복제본으로 장애 조치, 계층적 복제, 특정 시점으로 백업 복구하는 등의 작업을 더 쉽게 구현할 수 있으며, 오류 발생 빈도도 줄일 수 있다.

 

 

ProxySQL을 구현하기 위해서는 MySQL 복제 환경이 필요하다. MySQL 설치부터 복제 구성, ProxySQL 구성까지 따라할 수 있도록 문서를 작성하였다.

 

[시스템 구성]

실습 환경을 만들기 위해서는 전체 4대의 서버가 필요 하다. 시스템 사양은 고사양일 필요는 없지만, 너무 낮은 사양의 경우 서비스가 정상적으로 실행되지 않을 수 있으므로 적절히 선택하도록 한다.

l  Virtual Box

l  CPU : 2 Core

l  Memory : 4 GB

Server Name IP OS Service Version
proxy-sql 172.30.1.49 Ubuntu 22.04.2 LTS ProxySQL version 2.4.2-0-g70336d4, codename Truls
mysql-master 172.30.1.97 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave1 172.30.1.10 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33
mysql-slave2 172.30.1.13 Ubuntu 22.04.2 LTS mysql  Ver 8.0.33

 

OS의 패키지 버전 정보를 업데이트를 진행한 다음 패키지 업그레이드를 진행한다. 그리고 실습을 원활히 할 수 있도록 방화벽은 중지하도록 한다. 실제 업무 환경에서는 보안 정책에 따라 방화벽 설정을 할 수 있도록 한다.

--package update and upgrade
sudo apt-get update
sudo apt-get upgrade
 
--disable firewall
sudo ufw disable

 

 

[MySQL 설치]

MySQL 설치를 진행한다. 편의상 패키지로 설치를 진행하였다. 실제 업무에서는 필요한 버전을 선택하여 설치할 수 있도록 한다.

mysql-master mysql-slave1 mysql-slave2
sudo apt-get install mysql-server sudo apt-get install mysql-server sudo apt-get install mysql-server

 

MySQL 설치가 완료되었으면 외부에서 mysql에 접속할 수 있도록 my.cnf 수정해야 한다. 일반적으로 my.cnf파일은 아래와 같은 위치에 있지만, 사용자 환경에 따라 커스텀한 경로에 저장된 경우도 있다.

l   /etc/mysql/my.cnf

l   /etc/mysql/mysql.conf.d/mysqld.cnf

 

my.cnf 파일을 오픈하여, bind-address 부분을 주석 처리한다. 이 부분은 로컬 호스트(localhost, 127.0.0.1)에서 들어오는 접속을 바인딩한다는 뜻으로 해당 설정 때문에 외부에서 접근을 하지 못한다.

sudo vi /etc/mysql/my.cnf
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

 

 

bind-address에 대한 주석 처리가 완료되었으면 mysql 서비스를 재시작해서 변경 사항을 적용할 수 있도록 한다.

sudo service mysql restart

 

 

실습에서 사용할 mysql 사용자 계정을 생성한다.

sudo mysql

 

mysql >create user mysqluser@'%' identified by 'yourpassword' ;
mysql >grant all privileges on *.* to mysqluser@'%' with grant option;
mysql >flush privileges;

 

MySQL워크 벤치 등을 사용하여 외부에서 생성한 계정으로 접근이 되는지 확인한다.

 

[MySQL 복제 구성]

MySQL복제를 구성하기 위해서는 master서버와 Slave서버에 파라미터 설정을 진행해야 한다. 파라미터 설정에서 servier-id의 값이 중복되지 않도록 주의한다. my.cnf 파일을 오픈하여 아래 내용을 추가한다.

mysql-master mysql-slave1 mysql-slave2
server-id=1
log-bin=binlog
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON		 server-id=2
log-bin=binlog
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON		 server-id=3
log-bin=binlog
gtid-mode=ON
enforce-gtid-consistency=ON
log_slave_updates=ON

 

 

파라메터 수정이 완료되었으면, my.cnf파일을 저장하고, 모든 서버의 mysql서비스를 재시작하여 gtid_mode가 ON인지 확인한다.

sudo service mysql restart

 

show variables like '%gtid_mode%'

 

 

mysql-master에 접속하여 테스트용 데이터베이스를 생성한다.

mysql-master
create database testdb default character set utf8mb4;
show databases;

 

 

mysql-master에서 데이터베이스 백업을 진행한다.  mysqldump 명령을 사용하였고, 복제 구성을 위한 백업이므로 필요한 옵션을 추가하였다. 아래 스크립트에서 백업 경로는 적절하게 수정하여 사용할 수 있도록 한다.

mysql-master
mysqldump -u mysqluser -p -v --databases testdb --quick --single-transaction --routines --set-gtid-purged=ON --triggers --extended-insert --master-data=2 > /home/dev/db_backup/testdb.sql

 

백업이 완료되었으면 슬레이브를 구성할 서버에 백업 파일을 복사한다. 파일을 복사하는 방법은 다양하게 있으며 이번 실습에서는 scp명령을 사용하였다.

mysql-master
--copy mysql-master to myslq-slave1
sudo scp testdb.sql dev@172.30.1.10:/home/dev/db_backup
 
--copy mysql-master to mysql-slave2
sudo scp testdb.sql dev@172.30.1.13:/home/dev/db_backup

 

 

mysql-slave1, mysql-slave2 서버에서 데이터베이스를 복원한다. 아래 스크립트로 복원할 수 있다.

mysql-slave1 mysql-slave2
mysql -u mysqluser -p testdb < testdb.sql mysql -u mysqluser -p testdb < testdb.sql

 

복원 과정에서 아래와 같은 오류 메시지가 발생한다면, mysql에 접속하여 reset master 명령을 실행하여 해결할 있다. 이 오류는 각 서버마다 uuid 로 gtid 를 생성하는데 master 의 것을 가져와서 slave 에 반영하려 했기 때문에 발생한 것이다.

 

mysql> reset master;

 

복원이 정상적으로 되었는지 mysql-slave1, mysql-slave2 서버에 접속하여 확인한다. 그리고 각 슬레이브 서버에서 마스터 서버로 복제 연결을 진행한다.

mysql-slave1 mysql-slave2
change master to master_host = '172.30.1.97',
master_user='mysqluser', master_password=’password',
master_auto_position =1;
 
start slave;		 change master to master_host = '172.30.1.97',
master_user='mysqluser', master_password=’password',
master_auto_position =1;
 
start slave;

 

아래 스크립트를 사용하여 정상적으로 복제가 연결되었지 확인한다.

mysql-slave1 mysql-slave2
show slave status; show slave status;

 

 

 

mysql-master에 데이터를 입력하면 mysql-slave1, mysql-slave2에 데이터 변경이 정상적으로 반영되는 것을 확인할 수 있다.

 

[ProxySQL 설치]

ProxySQL 깃헙 저장소에서 ProxySQL 파일을 다운로드한다. 아래 스크립트를 실행하면 다운로드 후 자동으로 설치가 진행된다.

sudo wget https://github.com/sysown/proxysql/releases/download/v2.4.2/proxysql_2.4.2-ubuntu20_amd64.deb
 
sudo dpkg -i proxysql_2.2.0-ubuntu20_amd64.deb

 

 

 

설치가 완료되었으면, 현재 설치되어 있는 ProxySQL의 버전을 확인한다.

proxysql --version

 

 

 

ProxySQL 서비스를 실행 및 종료하기 위해서는 아래 스크립트를 사용한다.

--실행
sudo service proxysql start
--종료
sudo service proxysql stop

 

 

ProxySQL 서비스를 초기화는 아래 명령어를 사용할 수 있다.

sudo service proxysql-initial start

 

ProxySQL 서비스가 시작되었으면, 이제 ProxySQL에 접속해서 여러가지 세팅을 진행한다. 우선 ProxySQL에 접속하기 위해서는 MySQL Client가 필요하다. 사용자 환경에 따라 MySQL 또는 MariaDB 클라이언트를 설치한다.

sudo apt install mysql-client-core-8.0     # version 8.0.33-0ubuntu0.22.04.2
sudo apt install mariadb-client-core-10.6  # version 1:10.6.12-0ubuntu0.22.04.1

 

 

ProxySQL에 접속한다. 아래 스크립트를 proxy-sql 서버에서 실행한다.

mysql -uadmin -padmin -h 127.0.0.1 -P6032 --prompt='admin> '

 

 

ProxySQL을 운영하기 위해 필요한 데이터베이스도 확인해볼 수 있다.

show databases;

 

 

 

ProxySQL에서 MySQL서버를 모니터링 할 수 있는 계정을 설정한다. 이 설정은 ProxySQL이 MySQL로 접속할 수 있는 계정이어야 한다. 사용자 환경에 따라 username과 passwrd를 변경하여 사용한다.

UPDATE global_variables SET variable_value = 'username' WHERE variable_name = 'mysql-monitor_username';
UPDATE global_variables SET variable_value = 'password' WHERE variable_name = 'mysql-monitor_password';

 

모니터링 계정이 추가 되었는지 확인한다.

SELECT * FROM global_variables WHERE variable_name LIKE 'mysql-monitor_%';

 

 

 

변경 사항 저장 및 즉시 적용을 위해 아래 스크립트를 실행한다.

LOAD MYSQL VARIABLES TO RUNTIME;
SAVE MYSQL VARIABLES TO DISK;

 

 

ProxySQL에서 연결할 MySQL 서버 정보를 추가한다. 현재는 구성된 모든 서버를 호스트그룹 1로 구성하였다.

INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.30.1.97', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.30.1.10', 3306);
INSERT INTO mysql_servers (hostgroup_id, hostname, port) VALUES (1, '172.30.1.13', 3306);

 

추가된 MySQL 목록을 확인한다.

SELECT * FROM mysql_servers;

 

 

변경 사항 저장 및 즉시 적용을 위해 아래 스크립트를 실행한다. 위에 사용한 설정 저장 및 적용 스크립트와 명령이 다르므로 주의한다.

LOAD MYSQL SERVERS TO RUNTIME;
SAVE MYSQL SERVERS TO DISK;

 

 

아래 스크립트를 실행하면, 추가한 MySQL서버들에 대해서 연결 성공 여부 로그를 확인할 수 있다. 바로 실행하면 현재까지 연결된 적이 없기 때문에 로그가 남지 않는다. 하지만 연결 테스트를 진행 후 확인해보면 커넥션 로그가 남아있는 것을 확인할 수 있다.

SELECT * FROM monitor.mysql_server_connect_log ORDER BY time_start_us DESC LIMIT 10;

 

 

 

모니터링을 위해 ProxySQL와 MySQL사이의 핑 로그도 확인할 수 있다.

SELECT * FROM monitor.mysql_server_ping_log ORDER BY time_start_us DESC LIMIT 10;

 

 

이제는 외부 클라이언트가 ProxySQL에 접속할 계정 생성한다. 이 계정을 사용하여 외부 어플리케이션들이 ProxySQL로 접속하게 된다.

INSERT INTO mysql_users (username, password, default_hostgroup) VALUES ('ubuntu', '123456', 1);

 

 

외부 접근용 계정을 확인한다.

select * from mysql_users;

 

 

변경 사항 저장 및 즉시 적용을 위해 아래 스크립트를 실행한다. 위에 사용한 설정 저장 및 적용 스크립트와 명령이 다르므로 주의한다.

LOAD MYSQL USERS TO RUNTIME;
SAVE MYSQL USERS TO DISK;

 

proxy-sql 서버에서 아래 명령으로 ProxySQL에 접속하여, 연결 테스트 및 현재 그룹에 연결된 서버들에게 요청이 잘 되는지 확인한다. 여러 번 호출해보면 등록된 MySQL 서버로 분산되어 호출되어 호스트 이름이 변경되어 호출되는 것을 확인할 수 잇다.

mysql -uubuntu -p123456 -h127.0.0.1 -P6033 -e "SELECT @@hostname";

 

Tip : MySQL 8 버전대에서는 패스워드 문제로 일부 연결 발생할 수 있다고 한다. 이런 경우 아래와 같은 명령을 사용할 수 있다.
 
mysql --default-auth=mysql_native_password -uubuntu -p123456 -h 127.0.0.1 -P6033 -e "SELECT @@hostname";

 

 

 

 

 

지금까지의 ProxySQL 서버의 설정을 파일로 백업할 경우 아래 명령어를 사용할 수 있다. 실습에서는 서버의 대수가 적고 룰도 간단해서 백업이 필요하지 않을 수도 있지만, 실제 업무에서 사용할 때에는 서버의 대수도 많고 동일한 환경을 유지하기 위해서 반드시 설정 백업을 할 수 있도록 한다.

--ProxySQL설정 백업
SELECT CONFIG INTO OUTFILE /tmp/backup.cfg;
SAVE CONFIG TO FILE  /tmp/backup.cfg;

 

백업된 파일 내용을 살펴보면 아래와 같은 내용들이 포함된 것을 확인할 수 있다.

--백업된 설정 내용 조회 (/etc/proxysql.cnf)
SELECT CONFIG FILE;

 

 

Tip. MySQL8 버전부터는 계정 비밀번호 정책이 변경되었기 때문에 아직 ProxySQL에서도 문제가 발생하곤 한다. 이 때 아래 링크를 참고하여 본인 서버에 발생하는 케이스를 해결하는데 도움이 될 수 있도록 한다.
l   ProxySQL-2 cannot connect to PXC using the user with a caching_sha2_password plugin. : https://github.com/sysown/proxysql/issues/2580
l   Access denied while connecting via proxysql to MySQL pxc : https://github.com/sysown/proxysql/issues/3672
l   Access denied after redeployed proxysql : https://github.com/sysown/proxysql/issues/2424
l   access proxysql 6033 using mysql client sometimes success，sometimes failed？ : https://github.com/sysown/proxysql/issues/3185
l   Why ERROR 1045 (28000): Access denied in ProxySQL server? : https://devpress.csdn.net/mysqldb/63036be47e668234661985e8.html

 

 

 

2023-07-22 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, MySQL Replication, MySQL HA, Replication, MySQL복제, 마스터 슬레이브, ProxySQL설치, MySQL복제설치

저작자표시 비영리 변경금지

'MySQL, MariaDB' 카테고리의 다른 글

ProxySQL Internals 및 시스템 구성 둘러보기  (0) 2023.07.24
ProxySQL 쿼리룰 설정으로 Read, Write 부하 분산하기  (0) 2023.07.23
ProxySQL 이란 무엇인가  (0) 2023.07.20
MySQL Master DB를 백업하여 Replication Slave DB 구성하기  (0) 2021.05.29
MySQL InnoDB Buffer Pool Resizing Online  (0) 2020.10.21

ProxySQL 이란 무엇인가

 

l  Version : ProxySQL

 

ProxySQL은 MySQL 및 포크된 데이터베이스(Percona Server 및 MariaDB)를 위한 고성능, 고가용성을 제공하는 프록시이다. 글을 쓰는 현재(2023년 7월)는 GPL 라이선스로 공개되어 있어 누구나 사용할 수 있다. ProxySQL에 대한 자세한 정보는 아래 링크를 참고한다.

l  공식 사이트 주소 - https://www.proxysql.com/

l  ProxySQL 설치 및 사용 가이드 - https://github.com/sysown/proxysql/wiki

l  ProxySQL 도커 이미지 - https://hub.docker.com/r/proxysql/proxysql

 

[Proxy서버는 무엇일까]

프록시 서버는 리소스를 요청하는 클라이언트와 해당 리소스를 제공하는 서버 사이에 중개자 역할을 한다. 프록시 서버에 요청된 내용들은 캐시를 이용하여 저장하고, 캐시 정보를 요구하는 요청에 대해서는 원격 서버에 접속하여 데이터를 가져올 필요가 없다. 즉 전송시간을 절약할 수 있다. 또한 불필요하게 외부와의 연결을 하지 않아도 된다는 장점과 외부의 트래픽을 줄이게 됨으로써 네트워크 병목 현상을 방지할 수 있다.

l  Proxy Server : https://en.wikipedia.org/wiki/Proxy_server

 

 

[ProxySQL을 사용하는 이유는 무엇일까]

서비스 운영시 MySQL서버를 여러대로 구성해야하는 경우가 있다. (예, Replication, Sharding 등) 이때 어플리케이션 수가 증가할수록 MySQL 서버와 맺게 되는 커넥션수가 급격하게 늘어난다. 예를들어 MySQL 서버가 30대, 어플리케이션 서버가 200대이고, 각 서버별로 커넥션을 10개씩 유지한다면 30 * 200 * 10 = 60000 커넥션이 발생한다. 이런 이슈를 피하기 위해 중간에 미들웨어 개념으로 프록시 역할을 해주는 것이 ProxySQL이다. ProxySQL은 효율적인 커넥션 관리뿐만 아니라 라우팅 기능 등 여러 기능을 제공한다.

 

 

[ProxySQL의 주요기능]

l  Connection Multiplexing : ProxySQL의 멀티플렉싱은 여러 어플리케이션들이 동일한 데이터베이스에 대한 연결을 재사용할 수 있도록 하는 기능이다. MySQL은 "스레드 풀" 구현이 아닌 "연결당 스레드"를 사용기 때문에, 커넥션수가 증가함에 따라 (유휴 상태에서도) 점차 성능이 저하된다. ProxySQL은 "스레드 풀"을 사용하고 다중화를 통해 데이터베이스 커넥션을 할당하고 관리하는 리소스의 수를 줄여 성능을 크게 향상시킨다.

l  Query Routing : ProxySQL 쿼리 규칙을 정의하여 쓰기를 기본으로 라우팅하고, 읽기를 복제본에 분산할 수 있다. 매우 세분화된 기준으로 즉시 쿼리를 다시 작성한다.

l  Query Cache : ProxySQL 쿼리 캐싱은 요청 결과를 캐시하며, 동일한 쿼리가 실행되었을 때 캐시의 결과를 재사용하여 반환한다.

o   https://proxysql.com/documentation/Query-Cache/

l  Monitoring : ProxySQL 에 연결된 서버의 상태를 핑으로 모니터링 한다. 핑 주기 설정 및 응답이 없을 때 재시도하며, 재시도 횟수 초과시, 응답 없는 서버로 판단하여 프록시 서버 목록에서 제거한다.

l  Scheduler : 정해진 시간에 스크립트를 실행해주는 기능을 담당한다.

 

[Recvoery를 위한 orchestrator]

ProxySQL은 Recovery 기능을 제공하지는 않는다. 그래서 MySQL 서버 장애시DB 클러스터에서는 Slave DB (Read only)로 운영되던 데이터베이스를 Master DB로 승격을 시켜야 하는데, 이 기능을 제공하지는 않는다. 이러한 단점을 극복하기 위해 MySQL 고가용성 솔루션인 오케스트레이터(orchestrator)를 함께 사용한다.

 

오케스트레이터는 Replication으로 구성된 DB의 구조를 파악하고 스케줄러가 DB 설정을 변경할 수 있어 기존의Slave DB를 Master로 승격시킬 수 있다. 그리고 ProxySQL이 인지할 수 있도록 스케줄러가 ProxySQL의 설정 파일을 업데이트 한다. 이러한 방식으로 Recovery를 구성할 수 있다.

 

 

 

[참고자료]

l   공식 사이트 주소 - https://www.proxysql.com/

l   ProxySQL 설치 및 사용 가이드 - https://github.com/sysown/proxysql/wiki

l   ProxySQL 도커 이미지 - https://hub.docker.com/r/proxysql/proxysql

l   MySQL high availability & autofailover with ProxySQL & Orchestrator - Codemotion Milan 2018 : https://speakerdeck.com/thijsferyn/mysql-high-availability-and-autofailover-with-proxysql-and-orchestrator-codemotion-milan-2018

l   https://www.linkedin.com/pulse/iac-ha-db-architecture-aws-kuiyang-edwin-wang/

 

 

 

2023-07-20 / Sungwook Kang / http://sungwookkang.com

 

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

저작자표시 비영리 변경금지

'MySQL, MariaDB' 카테고리의 다른 글

ProxySQL 쿼리룰 설정으로 Read, Write 부하 분산하기  (0) 2023.07.23
ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에)  (0) 2023.07.22
MySQL Master DB를 백업하여 Replication Slave DB 구성하기  (0) 2021.05.29
MySQL InnoDB Buffer Pool Resizing Online  (0) 2020.10.21
MySQL MMM 구성 (Centos7 + MySQL 8.0)  (1) 2020.02.01

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를 사용한 시점 백업은 아래와 같다.

mysqldump --all-databases --master-data –single-transaction > all_databases.sql

 

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

mysql -uroot -p ‘xxxx’ < all_databases.sql

 

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

head -30 all_databases.sql

 

 

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

--연결설정
mysql>CHANGE MASTER TO
MASTER_HOST=’1.2.3.4’,
MASTER_USER=’replication_user’,
MASTER_PASSWORD=’replication_password’,
MASTER_PORT=3306,
MASTER_LOG_FILE=’mysql-bin.000108’,
MASTER_LOG_POS=384679355;


--연결시작
mysql> start lave

 

슬레이브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, MariaDB' 카테고리의 다른 글

ProxySQL 설치 (MySQL 설치부터, 복제 구성, ProxySQL 설정까지 한번에)  (0) 2023.07.22
ProxySQL 이란 무엇인가  (0) 2023.07.20
MySQL InnoDB Buffer Pool Resizing Online  (0) 2020.10.21
MySQL MMM 구성 (Centos7 + MySQL 8.0)  (1) 2020.02.01
MySQL/MariaDB Single-pass, Two-pass Sort Algorithm  (0) 2019.09.21

+ Recent posts

티스토리툴바