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 인터널스

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복제설치, 쿼리룰설정

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 successsometimes 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복제설치

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 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복제, 마스터 슬레이브

+ Recent posts