mysqldump를 사용하여 Amazon RDS for MySQL DB 인스턴스로 데이터를 가져올 때 1227 및 정의자 오류를 해결하려면 어떻게 해야 하나요?

Error: 1227 SQLSTATE: 42000 (ER_SPECIFIC_ACCESS_DENIED_ERROR) Access denied; you need (at least one of) the %s privilege(s) for this operation.

Definer error: example: /*!50003 CREATE*/ /*!50017 DEFINER=`root`@`localhost`*/ /*!50003 TRIGGER customer_update AFTER UPDATE ON `customer` FOR EACH ROW

간략한 설명

1227 오류는 데이터베이스에서 바이너리 로그가 활성화되고 mysqldump 파일이 저장된 객체(예: 트리거, 보기, 함수 또는 이벤트)를 포함하는 경우에 발생합니다. 자세한 내용은 이진 로그를 참조하십시오.

정의자 오류는 MySQL에서 데이터베이스 사용자로 객체를 생성하려 하지만, 데이터베이스 사용자가 대상 데이터베이스에 없으면 트리거됩니다. MySQL에서 localhost에 대한 사용자를 생성하려고 하면 이 작업은 Amazon RDS에서 허용되지 않으므로 비슷한 오류가 발생할 수 있습니다. 이는 Amazon RDS에는 슈퍼 사용자 허가가 없기 때문입니다.

해결 방법

오류 1227 해결

1.    DB 인스턴스를 생성한 사용자 지정 DB 파라미터 그룹에 대해 log_bin_trust_function_creators 파라미터 설정을true로 설정합니다.

2.    일반적으로 MySQL 덤프 파일에 있는 일부 명령(예: " SET @@SESSION.SQL_LOG_BIN= 0;")은 RDS에서 허용되지 않습니다. RDS 인스턴스에 대해 파일을 실행하기 전에 덤프 파일에서 이러한 줄을 삭제하거나 주석 처리해야 합니다.

정의자 오류 해결

정의자 오류는 여러 방법으로 해결될 수 있습니다.

• 정의자 줄 제거
• 정의자 사용자 이름 바꾸기
• 정의자 옵션이 없는 덤프 파일을 생성하거나 다시 생성

정의자 줄 제거

/*!50017 DEFINER=`root`@`localhost`*/

이제 줄에 다음과 유사한 출력이 표시됩니다.

/*!50003 CREATE*/ /*!50003 TRIGGER customer_update AFTER UPDATE ON `customer` FOR EACH ROW

정의자 사용자 이름 바꾸기

루트의 이름을 masteruser로, localhost를 %host로 변경합니다.

/*!50003 CREATE*/ /*!50017 DEFINER=`masteruser`@`%`*/ /*!50003 TRIGGER customer_update AFTER UPDATE ON `customer` FOR EACH ROW

참고: 모든 호스트에 대해 와일드카드로 %를 사용할 수 있습니다.

정의자 옵션 없이 덤프 파일을 생성하거나 다시 생성합니다.

MySQL 덤프 유틸리티는 DEFINER를 제거하는 옵션을 제공하지 않습니다. 일부 MySQL 클라이언트는 논리 백업을 생성할 때 정의자를 무시하는 옵션을 제공하지만, 기본적으로 이 옵션은 수행되지 않습니다. 선호하는 MySQL 클라이언트에 대한 설명서를 검토하여 DEFINER를 무시하는 옵션이 사용 가능한지 확인합니다. MySQL 명령줄 클라이언트에서는 정의자를 제외할 수 없습니다. 그러나 클라이언트에서 타사 도구를 함께 사용하여 DEFINER를 제거하거나 사용자 이름 및 호스트를 찾아서 대체할 수 있습니다

다음 예제는 Linux, macOS 또는 WSL(Windows Subsystem for Linux)에서 DEFINER를 제거하는 방법을 나타냅니다.

제거:

sed -i -e 's/DEFINER=`root`@`localhost`//g' dump.sql

찾은 후에 대체:

sed -i -e 's/DEFINER=`root`@`localhost`/DEFINER=`masteruser`@`%`/g' dump.sql

참고: masteruser 값을 Amazon RDS 마스터 사용자의 이름으로 대체합니다.

---

관련 정보

Amazon RDS의 MySQL

복제를 사용하여 MySQL DB 인스턴스에서 데이터 내보내기