[NCP - CloudDB for MySQL 데이터 마이그레이션] use. mysqldump

안녕하세요 이번글에서는 Naver Cloud의 CloudDB for MySQL서비스에서의 데이터 마이그레이션 작업을 진행해보겠습니다. 실제 사이트에서 작업중 일어난 에러들도 함께 정리해보았습니다.
해결방법은 제 개인적인 생각으로 진행했으므로 최선의 방법이 아닐 수 있습니다

 

테스트 환경

기존 DB : CloudDB for MySQL -> AS-IS
대상 DB : CloudDB for MySQL -> TO-BE
마이그레이션을 위한 임시 서버 생성 -> VM 인스턴스 (mig)

 

Export

※ 에러 1번
TABLE1 테이블이 오랜 미관리로 인해 손상되어 있어 dump불가하며 조회시 없다고 나옵니다.

[root@mig ~]# mysqldump -h <AS-IS도메인> -u <DB 유저> -p <DB 유저 패스워드> --routines --triggers --single-transaction <database이름> > dump.sql
Enter password:
Error: Couldn't read status information for table TABLE1 ()
mysqldump: Couldn't execute 'show create table `TABLE1`': Table 'kim.table1' doesn't exist (1146)


MySQL [(none)]> select * from information_schema.tables where table_name='TABLE1';
Empty set (0.00 sec)

해결방법
해당 테이블 제외하고 export 진행하였습니다. 정확한 원인 파악을 위해서는 네이버클라우드에서 관리를 하기때문에 네이버클라우드에 문의가 필요해보입니다.

[root@mig ~]# mysqldump -h <AS-IS도메인> -u <DB 유저> -p <DB 유저 패스워드> --routines --triggers --single-transaction <database이름> --ignore-table=<database이름>.table1 > dump.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.

※ 에러 2번
네트워크 read, write 타임아웃 시간이 짧게 설정되어 export 도중에 에러와 함께 끊기는 상황 발생

[root@mig ~]# mysqldump -h <AS-IS도메인> -u <DB 유저> -p <DB 유저 패스워드> --routines --triggers --single-transaction <database이름> --ignore-table=<database이름>.table1 > dump.sql

mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Error 2013: Lost connection to MySQL server during query when dumping table `gb0120` at row: 167770

해결방법
아래 두개의 파라미터 해당 값을 임시로 늘려줍니다.

MySQL [(none)]> show variables like 'net_read_timeout%';
+------------------------+--------+
| Variable_name | Value |
+------------------------+-------+
| net_read_timeout | 30 |
+------------------------+-------+
1 row in set (0.01 sec)

MySQL [(none)]> show variables like 'net_write_timeout%';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| net_write_timeout | 60 |
+------------------------+---------+
1 row in set (0.00 sec)


※ CDB는 콘솔에서 config 추가해주면 됩니다.
MySQL [(none)]> set global net_read_timeout = 10800;
MySQL [(none)]> set global net_write_timeout = 10800;

 

Import

※ 에러 1번
아래 에러는 GTID(global transaction identifier)와 관련되어 있습니다.
네이버 클라우드(Ncloud) Cloud DB for MySQL 상품은 GTID(Global Transaction IDentifier)를 사용하는데, 보통의 mysql db 복구(Restore)는 GTID를 사용하지 않는 방법이기 때문에 백업(Backup) 단계에서 [–set-gtid-purged=OFF] 옵션을 추가해야 하는데 이 옵션을 사용하지 않았기 때문입니다.

[root@mig ~]# mysql -h <TO-BE도메인> -u <DB 유저> -p <DB 유저 패스워드> < dump.sql
Enter password:ERROR 1227 (42000) at line 18: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

해결방법 1
export시 –set-gtid-purged=OFF 옵션을 함께 사용하는 것을 import시 에러를 예방 할 수 있습니다.

[root@mig ~]# mysqldump -h <AS-IS도메인> -u <DB 유저> -p <DB 유저 패스워드> --routines --triggers --single-transaction <database이름> --ignore-table=<database이름>.table1 –set-gtid-purged=OFF > dump.sql

해결방법2
이미 export를 하였거나 위 해결방법1과 같이 다시 export하기 힘든 상황에서는, vi에디터를 이용하여 dump파일에 있는 GTID 관련 내용을 삭제하고 복구하시면 문제가 해결됩니다.
백업 파일 상단과 하단에 각각 아래와 같은 코드가 포함되어 있는데 이 내용을 삭제하거나 주석처리한 후에 복구를 시도하면 문제없이 복구가 가능합니다.

##백업 파일 상단
SET @MYSQLDUMP_TEMP_LOG_BIN = @@SESSION.SQL_LOG_BIN;
SET @@SESSION.SQL_LOG_BIN= 0;

##백업 파일 하단
SET @@GLOBAL.GTID_PURGED='2*********b:***, 2*********';

※ 에러 2번
MySQL은 보안 강화를 위해 Trigger, Stored Routines (Procedures and Functions), View, Event를 처음 생성한 계정을 [DEFINER]로 명시해 둠으로써 다른 계정으로 접근하지 못하도록 하는 것이 기본 설정이 되어 있어 에러 발생

[root@mig ~]# mysql -h <TO-BE도메인> -u <DB 유저> -p <DB 유저 패스워드> < dump.sql
ERROR 1227 (42000) at line 59599: Access denied; you need (at least one of) the SUPER privilege(s) for this operation

해결방법 1
DB 복구를 시도할 때는 [DEFINER] 관련 내용을 삭제하거나 동일한 계정 또는 SUPER privilege를 가진 계정으로 복구해야 합니다. CDB는 설치형DB와 달리 권한에 ALL이 없습니다. CDB에서는 DDL권한을 주어도 작업시 실패합니다. 따라서 [DEFINER] 관련 내용을 공백처리하여 진행하였습니다. 공백처리시 기본 유저로 들어간다고 합니다.

※ 해당 명령어 실행시 덤프파일 사이즈와 비슷한 임시 파일이 생성되었다가, 완료시 삭제됩니다. 필히 작업하는 공간의 여유공간을 확인 후 진행하시기 바랍니다.

[root@mig ~]# sed -i 's/DEFINER=[^*]*\*/\*/g' dump.sql

해결방법 2
dump파일에 기재된 [DEFINER] 관련 동일 계정으로 import를 합니다.

[root@mig ~]# mysql -h <TO-BE도메인> -u <DEFINER 유저> -p <DEFINER 유저 패스워드>  < dump.sql

 

※ DEFINER 유저 찾는방법

덤프파일 vi로 까보면 아래와 같이 definer가 명시되어 있음!!

...생략

/*!50003 SET character_set_results = *** ;

/*!50003 SET collation_connection  = *** ;

/*!50003 SET @saved_sql_mode       = *** ;

/*!50003 SET sql_mode              = ***;

DELIMITER ;;

CREATE DEFINER=`<유저명>`@`%` *****

...생략

위와 같은 방법으로 import까지 마치면 데이터 마이그레이션을 정상적으로 마칠 수 있습니다!


참고

https://docs.3rdeyesys.com/database/ncloud_database_cdb_mysql_restore_error_1227_troubleshooting.html#%ED%95%B4%EA%B2%B0-%EB%B0%A9%EB%B2%95-2

MySQL 복구 시 ERROR 1227 (42000) 문제 원인과 해결방법