[MariaDB/MySQL] 저장 프로시저 DEFINER 일괄 변경하는 세 가지 방법

개요

MariaDB를 이용하여 프로젝트를 진행하던 중에, 개발하던 DB를 그대로 실 운영서버로 백업하고 복구하여 사용하려고 하는데, 저장 프로시저의 DEFINER 때문에 호출 오류가 발생하였습니다.

데이터베이스 오류
Error Number: 1449

The user specified as a definer ('root'@'172.16.112.%') does not exist

CALL USP_get_common()

저장 프로시저를 백업 받아서 DEFINER를 변경한 후에, 다시 정의하면 해당 저장 프로시저는 바로 이용할 수 있었으나, 이전한 데이터베이스에 저장 프로시저가 100개가 넘는 터라 일일이 DEFINER를 바꾸어 주는 것은 너무 고된 작업이라 일괄로 한꺼번에 처리할 수 있는 방법은 없는지 알아보고, 확인된 방법들을 정리해 둡니다.

방법 1 - mysqldump를 이용하는 방법

백업 및 복구시 사용하는 "mysqldump" 및 "mysql" 명령을 이용하여, 수정할 저장 프로시저가 있는 데이터베이스를 파일로 백업하고, 파일을 열어서 DEFINER를 수정한 후에 수정된 파일을 이용하여 데이터베이스를 복구하는 방법입니다.

"target" 데이터베이스를 다음과 같이 백업합니다.

mysqldump --add-drop-database --routines -uroot -ppassword -P3306 -h127.0.0.1 --databases target > target_routines.sql

백업한 "target_routines.sql" 파일을 편집기로 열어서 "CREATE DEFINER=`root`@`127.0.0.1`" 부분을 "CREATE OR REPLACE"로 모두 변경합니다.

"target" 데이터베이스를 다음과 같이 복구합니다.

mysql -uroot -ppassword -P3306 -h127.0.0.1 mysql < target_routines.sql

이 방법은 여러 과정을 거치기 때문에 번거롭기는 하나, 백업 받은 저장 프로시저들 중에서 원하는 형태로 일괄 변경할 수 있다는 장점이 있습니다. 예를 들면, 저장 프로시저의 특정 테이블 이름을 모두 다른 이름으로 일괄 변경하는 등의 작업에 아주 유용합니다.

방법 2 - 계정을 추가하는 방법

다음과 같이 문제가 되는 계정을 추가해 주는 방법입니다.

mysql -uroot -ppassword -P3306 -h127.0.0.1 -e "GRANT ALL ON target.* TO 'root'@'192.168.0.%' IDENTIFIED BY 'password'; FLUSH PRIVILEGES;" mysql

하지만, 이 방법은 개발할 때나 사용하던 불필요한 계정을 다른 서버에도 그대로 만들어야 한다는 점이 문제입니다.

방법 3 - mysql.proc 테이블에서 일괄 갱신하는 방법

mysql.proc 테이블을 이용할 경우, 다음과 같이 손쉽게 일괄 갱신할 수 있습니다.

UPDATE mysql.proc SET `definer` = 'root@192.168.0.%'
WHERE 1
    AND `definer` LIKE 'root@127.0.0.1'
    AND `db` LIKE 'target'

이미 데이터베이스 서버에 올라와 있는 저장 프로시저의 경우에는 mysql.proc 테이블에서 직접 갱신한 내용이 바로 반영되지 않습니다.
데이터베이스 서버를 재시작 (systemctl restart mariadb)해주어야만 합니다.

결론

개인적으로는 방법 2가 깔끔하고 좋습니다. 하지만, 사용하지 않는 불필요한 계정을 생성해 주어야 하다는 점이 문제입니다.

방법 1의 경우에는 DEFINER 외에도 저장 프로시저 내부 내용을 일괄적으로 변경해야할 필요가 있을 경우 유용합니다.

방법 3의 경우에는 이미 데이터베이스 서버에 올라와 있는 저장 프로시저의 경우에는 mysql.proc 테이블에서 직접 갱신한 내용이 바로 반영되지 않는 문제가 있지만, 데이터베이스 서버를 재시작해주면 됩니다만, 서버를 재시작한다는 것은 매우 큰 부담입니다. 아니면 변경된 데이터베이스만 백업 후, 삭제한 뒤 및 그대로 복구를 하면 됩니다.

상황에 맞게 위 방법들을 활용하여 처리하면 되겠습니다.

참고자료

• Change the stored procedure definer
• MySQL error 1449: The user specified as a definer does not exist