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

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