mySQL 파일 RDS로 데이터 이관하기 & Access denied 오류 해결

 

1. 데이터 이관

현재 MySQL을 사용중이고, AWS로 테스트 서버를 만들 일이 생겨서 개발중인 DB를 RDS로 옮기고자 하였다. 방법은 여러가지가 있는데, 나는 RDS에 원격으로 접속하여 로컬에 있는 sql파일을 mysqldump 하고자 하였다. 명령어는 다음과 같이 입력하면 된다. (단, 접속할 시에는 퍼블릭 엑세스 가능성(Public accessibility) 을 Yes로 설정해야지만 원격으로 접속이 가능하다. )

mysqldump -h 원격지DB주소 -u 사용자이름 -p DB명 < DB.sql

 

2. Access Denied 오류 해결

위와 같은 명령어로 데이터 이관을 시도했으나 권한문제때문에 다음과 같은 오류가 출력되었다.

Access denied; you need (at least one of) the SUPER privilege(s) for this operation

AWS RDS 지침서에 따르면 다음과 같은 이유때문에 엑세스 거부 오류가 발생한다고 한다.

 

• 대상 RDS 인스턴스에 이진 로그가 활성화([백업 보존 기간] > 0)되어 있으며, mysqldump 파일이 트리거, 뷰, 함수 또는 이벤트와 같은 객체를 포함
• 가져온 mysqldump 파일이 DB 인스턴스에 존재하지 않는 DEFINER 속성 사용자로 객체를 생성하려고 시도했거나 사용자가 필요한 SUPER 사용자 권한을 가지고 있지 않은 속성 사용자를 생성하려고 시도
• 오류 메시지에서 참조된 라인에 대한 명령에 Amazon RDS DB 인스턴스에서 제공되지 않는 SUPER 권한이 필요

=> 따라서 오류를 해결하기위해, 다음과 같은 작업을 수행하였다.

 

 

(1)파라미터 그룹 생성 및 적용

 

먼저 RDS에 백업본을 저장하기 위한 권한을 줘야한다. RDS 메뉴에서 주기위해 [파라미터 그룹]을 생성한다.

 

파라미터 그룹 -> 파라미터 생성(Create ParameterGroup) 을 사용하여 파라미터 그룹을 생성할 수 있다. Group Name과 Description을 적당히 기입하여 파라미터 그룹을 만든다.

 

만들어진 파라미터 그룹을 생성하고 검색창에 log_bin_trust_function_creators 를 검색한다. 그냥 creators라고 검색해도 상관없다. 그러면 파라미터 옵션이 필터링 되는데, [Edit Parameters]를 선택하여 이 값을 1로 셋팅하여 편집을 완료한다.

 

파라미터 셋팅

 

파라미터 그룹을 추가했다면 그 후엔 추가한 파라미터 그룹을 DB 인스턴스에 적용한다. 대시보드에서 변경할 인스턴스를 클릭하고

Modify를 누르면 파라미터그룹등의 구성을 변경 할 수 있다.

새로 추가했던 새로운 파라미터 그룹을 선택하고 수정사항을 저장한다. 변경한 값을 DB 인스턴스에 적용하면 RDS가 알아서 즉시적용해주는데, 이 작업은 적용되기까지 몇 분정도 소요될 수 있다.

 

(2) DEFINIER= 구문제거

(1)을 시행해도 같은 오류가 번복되었기에 다른 오류 해결법을 찾아보았다. StackOverflow 로 검색해본결과 RDS에서 제공하는 MySQL 서버는 다른사용자의 DEFINER 구문을 허용하지 않는다고 한다. 아마도 dump한 db내에 있는 사용자와 RDS 인스턴스의 User가 다르기때문에 이 오류가 나타나지 않았나싶다. (조금 더 연구 필요.,) 이 구문은 sed 스크립트를 사용하여 파일에서 제거할 수 있다고 한다.

sed 's/\sDEFINER=`[^`]*`@`[^`]*`//g' -i oldfile.sql

위 구문을 이용하여 DEFINIER 구문을 제거해준뒤, 다시 mysqldump를 시도하니 RDS 에 DB백업이 정상적으로 되는 모습을 볼 수 있었다. 이제 DB 이관에는 성공했으니 다음 작업에서는 EC2와 연결하는 방법을 연구해봐야겠다.

 

3. 참고

https://aws.amazon.com/ko/premiumsupport/knowledge-center/mysqldump-error-rds-mysql-mariadb/

Amazon RDS for MySQL 또는 MariaDB의 Mysqldump 오류 해결

https://stackoverflow.com/questions/44015692/access-denied-you-need-at-least-one-of-the-super-privileges-for-this-operat

Access denied; you need (at least one of) the SUPER privilege(s) for this operation