sequel pro - caching_sha2_password 오류

오류상황

Docker에 있는 Mysql 을 연결하려고 할 때 아래와 같은 메시지가 뜨며 Test connenction 실패

 

오류내용

Unable to connect to host 127.0.0.1, or the request timed out.

Be sure that the address is correct and that you have the necessary privileges, or try increasing the connection timeout (currently 10 seconds).

MySQL said: Authentication plugin 'caching_sha2_password' cannot be loaded: dlopen(/usr/local/lib/plugin/caching_sha2_password.so, 0x0002): tried: '/usr/local/lib/plugin/caching_sha2_password.so' (no such file), '/System/Volumes/Preboot/Cryptexes/OS/usr/local/lib/plugin/caching_sha2_password.so' (no such file), '/usr/local/lib/plugin/caching_sha2_password.so' (no such file)

원인 찾기

MySQL 8.0 이후부터 기본 인증 플러그인인 caching_sha2_password를 사용합니다.

하지만 sequel pro는 2018년부터 업그레이드가 중단되어 이 방식을 지원하지 않아 발생하는 문제입니다.

 

공식문서에서 찾아보니 caching_sha2_password 대신 mysql_native_password 를 사용하면 된다고 합니다.

[mysqld]
default_authentication_plugin=mysql_native_password

mysqld를 변경하는 방법은

1. (Docker)MySQL 이미지 - etc/my.cnf 에서 위 값 변경 후 재부팅
2. (Docker인 경우)Docker-compose에 아래 명령어를 추가 후 컨테이너 내렸다가 다시 올리기

command:
  - --default-authentication-plugin=mysql_native_password

 

위 방법으로 mysqld를 바꿔봤지만 sequel 에서 여전히 커넥션이 안되었습니다.

그래서 계정을 확인해보니 mysqld는 잘 변경 된 것 같지만, root 계정이 기존 방식을 계속 사용하는 되어 안되었던 것을 알 수 있습니다.

root 계정 설정은 저장후 local에 계속 있다보니 아래의 명령어를 통해서 root 계정의 인증 방식을 변경해줘야 합니다.

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'password';
ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

위의 localhost의 접근은 socker 연결시 필요한 부분이고,

일반 혹은 Docker로 접근은 TCP/IP방식을 사용하므로 %를 사용하기 때문에 아래 명령어를 변경해주어야 정상적으로 접근이 가능합니다.

 

테스트를 해보니 맨 처음 [mysqld]를 변경하지 않아도 이 root 계정 변경 명령어만으로도 sequel로 정상 접근되는 것을 확인했습니다.

결론

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'password';

로 계정의 로그인 방식을 8.0 이전방식으로 변경

추가생각

dev 환경과 local 환경은 다르지만, 계정의 인증 방식을 변경하는 명령어가 마음에 걸렸습니다.

8.0이상부터 바뀐 방식이 좀더 secure한 방식인데다, db환경을 각각 다르게 가져가는 것이 필요한 것인가 생각이 들었습니다.

 

맥에서는 DB툴이 많지 않은데다 이전에 DBeaver를 사용하다가 몇 번 재부팅된 경험이 있어서 사용하지 않았는데,

이 두가지 이유 때문에 DBeaver를 사용하기로 하였습니다.(sequel pro는 삭제했습니다.)