[ProxySQL] CONNECTING_CLIENT_STATE_SERVER_HANDSHAKE 오류

[ProxySQL] CONNECTING_CLIENT_STATE_SERVER_HANDSHAKE 오류

 

l  Version : ProxySQL 2.4.2

 

ProxySQL에서 백엔드 어플리케이션이 접속하는 포트로 로그인이 안되는 문제가 발생하였다. ProxySQL의 로그를 확인해 보니 아래와 같은 로그가 기록되어 있다.

MySQL_Session.cpp:5094:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE():[ERROR] ProxySQL Error: Access denied for user 'user'@'ip' (using password: YES) MySQL_Session.cpp:5094:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE():[ERROR] ProxySQL Error: Access denied for user 'user'@'ip' (using password: YES)MySQL_Session.cpp:5094:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE():[ERROR] ProxySQL Error: Access denied for user 'user'@'ip' (using password: YES) MySQL_Session.cpp:5094:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE():[ERROR] ProxySQL Error: Access denied for user 'user'@'ip' (using password: YES) MySQL_Session.cpp:5094:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE():[ERROR] ProxySQL Error: Access denied for user 'user'@'ip' (using password: YES) MySQL_Session.cpp:5094:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE():[ERROR] ProxySQL Error: Access denied for user 'user'@'ip' (using password: YES) MySQL_Session.cpp:5094:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE():[ERROR] ProxySQL Error: Access denied for user 'user'@'ip' (using password: YES) MySQL_Session.cpp:5094:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE():[ERROR] ProxySQL Error: Access denied for user 'user'@'ip' (using password: YES)

 

 

 

 

현재 시스템 구성은 MySQL PXC 환경으로 3개의 노드(Primary 1, Secondary 2)와 클러스터 상단에 쿼리 종류에 따라 로드 밸런서 및 커넥션 매지니지먼트를 위한 ProxySQL이 구성되어 있다. 문제는 관리자 포트인 6032로 접속하면 정상적으로 접속이 되며, 백엔드 포트인 6033 포트로 접속하면 오류 로그와 함께 접속이 되지 않는 것이다. 사실상 위 오류 로그만으로는 문제의 원인을 찾기가 쉽지 않았다. 단순히 사용자 패스워드 오류 메시지로만 표시되기 때문이다. 하지만 해당 문제가 발생할 때 패턴을 살펴보면, 신규 사용자를 생성한 경우에 가끔 발생한다는 것이었다. 그리고 여러 검색을 해본결과, 해당 문제는 특정 버전이 아닌, 오래전부터 다양한 버전에서 발생하는 문제였다.

 

ProxySQL은 SQLite를 기본 데이터 저장소로 사용하며 디스크로 데이터를 저장하면 SQLite로 저장이 된다. 이때 간혹 데이터 불일치가 발생하여 이와 같은 문제가 발생할 수 있다.

l  ProxySQL 은 서비스에 필요한 설정값을 어디에 저장하고 재사용할까? : https://sungwookkang.com/entry/ProxySQL-%EC%9D%80-%EC%84%9C%EB%B9%84%EC%8A%A4%EC%97%90-%ED%95%84%EC%9A%94%ED%95%9C-%EC%84%A4%EC%A0%95%EA%B0%92%EC%9D%84-%EC%96%B4%EB%94%94%EC%97%90-%EC%A0%80%EC%9E%A5%ED%95%98%EA%B3%A0-%EC%9E%AC%EC%82%AC%EC%9A%A9%ED%95%A0%EA%B9%8C

 

 

이 문제는 아래 순서로 재현이 가능하다.

#ProxySQL 관리자 모드 접속 mysql -uadmin -padmin -P6032 -h127.0.0.1   #신규 사용자 추가 insert into mysql_users (username, password,default_hostgroup) values ('admin','admin',0);   #변경 사항 메모리 로드 및 디스크에 변경 사항 저장 load mysql users to runtime; save mysql users to disk;   #관리자 모드로 재접속 mysql -uadmin -padmin -P6032 -h127.0.0.1   #반환되는 오류 메시지 mysql: [Warning] Using a password on the command line interface can be insecure. ERROR 1045 (28000): ProxySQL Error: Access denied for user 'admin'@'127.0.0.1' (using password: YES)   #ProxySQL 오류 로그 확인 tail -1 /var/lib/proxysql/proxysql.log   MySQL_Session.cpp:5439:handler___status_CONNECTING_CLIENT___STATE_SERVER_HANDSHAKE(): [ERROR] ProxySQL Error: Access denied for user 'admin'@'127.0.0.1' (using password: YES)

 

이 문제를 해결하기 위서는 ProxySQL에서 사용하는 SQLite의 데이터를 직접 수정해서 해결할 수 있다.

# Stop ProxySQL systemctl stop proxysql   # Backup the db file cd /var/lib/proxysql/ cp proxysql.db proxysql.db.1   # Open db file with sqlite3 sqlite3 proxysql.db     sqlite> select * from mysql_users; admin|*4ACFE3202A5FF5CF467898FC58AAB1D615029441|1|0|10|test|0|1|0|1|1|10000||     # Delete the user we created and verify it is gone sqlite> delete from mysql_users where username='admin';   sqlite> .quit     # Start ProxySQL systemctl start proxysql

 

 

위 방식은 직접 SQLite에 접속하여 문제의 계정을 삭제하고 서비스를 재시작 후 새롭게 계정을 또 다시 등록하고 해야한다. 하지만 SQLite를 직접 수정하는 것은 최대한 피하는 것이 좋다. 엉뚱한 데이터를 삭제하는 문제가 발생하면 더 큰 서비스 문제로 이어질 수 있기 때문이다. 또한, ProxySQL을 여러대 사용할 경우 매번 접속해서 계정을 삭제하고 만드는 일이 매우 불편할 수 있다.

 

다른 방법으로는 ProxySQL을 초기화 하고 미리 만들어둔 사용자 및 서버, 호스트 그룹등을 설정하는 스크립트를 재실행하여 새롭게 구성하거나 백업해둔 SQLite를 복원해서 사용하는 것이다.

#ProxySQL 초기화 proxysql –initial

 

 

 하지만 이 방법 또한 재구성 후 이니셜 작업을 해주어야 하는 불편함이 있기 때문에 나의 경우에는 proxysql.cnf 파일에 사용자, 서버, 호스트그룹, 쿼리룰 등 모두 세팅하여 서비스가 시작될 때 proxysql.cnf 파일의 정보를 사용하도록 하였다.

 

지금까지 설명한 방법중 어떠한 것을 도입하더라도 서비스 다운타임은 피할 수 없다. 그래서 설정 파일로 모두 관리한 다음 문제가 생겼을 때 빠르게 서비스를 재시작하거나, ProxySQL 클러스터 그룹을 생성하여 문제가 되는 노드는 빠르게 제거하고 다시 세팅할 수 있는 환경을 만들어 서비스 다운타임을 최화 하는 것이 중요할 듯 하다.

 

 

[참고자료]

l  https://kedar.nitty-witty.com/blog/can-not-connect-to-proxysql-reasons-and-fixtures

 

 

 

 

2023-12-11 / Sungwook Kang / http://sungwookkang.com

 

MySQL, ProxySQL, 프록시 SQL, 커넥션 오류, CONNECTING_CLIENT_STATE_SERVER_HANDSHAKE