반응형

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

반응형

+ Recent posts