[DB] AWS RDS MySQL 5.7 to 8.0 마이그레이션

wujin·2023년 11월 14일

Database

운영되고 있던 시스템의 Database는 MySQL 5.7용 AWS RDS 서비스를 이용중이었다.

곧 MySQL 5.7용 AWS RDS의 표준 지원이 종료된다는 메일을 받기도 했고...
5.7은 현재로써 구버전이라 MySQL 8.0 버전으로 업그레이드를 하기로 하였다.

AWS RDS는 DB 인스턴스 수정을 통해 DB 엔진 버전 변경이 가능하여 아래 사진과 같이 버전 업그레이드를 진행하였다.

0. MySQL 8.0 업그레이드 과정

1. MySQL 8.0 유형의 파라미터 그룹을 미리 생성

2. DB 엔진 버전 업그레이드 전 RDS 스냅샷 생성

3. 아래 내용에서 언급될 CharacterSet, Collation를 쿼리로 변경

DB 레벨 CharacterSet, Collation 변경
foreign_key_checks 옵션 OFF (0)
Table, Column레벨 CharacterSet, Collation 변경
foreign_key_checks 옵션 ON (1)

4. AWS RDS 인스턴스 편집

DB 엔진 버전: 8.0.xx
파라미터 그룹 변경

5. 서버 레벨 CharacterSet, Collation 확인

6. Procedure, Function 확인 및 CharacterSet, Collation 변경

7. DataSource JDBC URL 파라미터 변경 및 서버에 적용

?characterEncoding=UTF-8&connectionCollation=utf8mb4_general_ci

8. 최종 테스트 진행

1. Parameter Group 변경

변경 전 (MySQL 5.7)

변경 후 (MySQL 8.0)

1) character_set, collation 변경

character_set
- character_set_client, character_set_connection, character_set_database, character_set_filesystem, character_set_results, character_set_server
- utf8 -> utf8mb4
- utf8(utf8mb3)은 MySQL 8.0에서 더 이상 지원되지 않아 utf8mb4로 변경해줘야한다.
collation
- collation_connection, collation_server
- utf8mb3_general_ci -> utf8mb4_general_ci
- character_set과 같은 이유로 변경

utf8mb4_unicode_ci방식이 utf8mb4_general_ci보다 더 복잡한 알고리즘을 사용하여 더 나은 정확도나 다국어 지원에 좋다.
복잡한 알고리즘때문에 속도 차이가 있지만 요새 하드웨어 성능이 많이 올라 큰 차이가 없다.

2) lower_case_table_names 변경

테이블명 대소문자 구분 여부
1(구분 안함) -> 0(구분 함, default value)
MySQL 8.0 버전은 쿼리 작성시 테이블명의 대소문자를 무조건 구분해야한다.
기존 시스템은 구분 안함으로 사용하였어서.. 대문자로 작성된 테이블명들 전부 수기로 변경해주었다...

2. DB 레벨에서 character, collation 변경

CHARACTER_SET : utf8 -> utf8mb4
COLLATION : utf8_general_ci -> utf8mb4_general_ci

-- 변경
ALTER DATABASE (DB NAME)
CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 확인
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA;

3. Table과 Column 레벨에서 CHARACTER, COLLATE 변경

CHARACTER: utf8 -> utf8mb4
COLLATE: utf8_general_ci -> utf8mb4_general_ci

-- table, column 모두 변경
ALTER TABLE (table_name)
CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;

-- 확인
SHOW TABLE STATUS FROM (table_name);

4. Procedure, Function DDL 변경

기존에 사용중인 프로시저나 함수의 DDL을 살펴보면 입출력값의 Character나 Collate가 utf8로 되어있다면 utf8mb4로 변경해주어야 함.

CHARACTER: utf8 -> utf8mb4
COLLATE: utf8_general_ci -> utf8mb4_general_ci

5. MySQL 5.7의 내장 함수 'password()'가 8.0에서 deprecated

아래와 같이 old_password() function을 생성하여 사용 예정

DELIMITER //
CREATE DEFINER=`db_name`@`%` FUNCTION `db_name`.`old_password`(pwd TEXT) RETURNS varchar(41) CHARSET utf8mb4
	NO SQL
	COMMENT 'password() 함수는 MySQL8.0에서 deprecated 되었음.'
BEGIN
	DECLARE result VARCHAR(41);
	SET result = CONCAT('*', UPPER(SHA1(UNHEX(SHA1(pwd)))));
	RETURN result;
END;
DELIMITER ;

6. illegal mix of collations Error 해결방법

connection_collation과 DB collation 불일치로 생기는 에러
업그레이드할 DB(MySQL 8.0.34)의 collation은 utf8mb4_general_ci로 설정되어 있음.
해당 에러가 발생한다면 아래와 같이 jdbc url parameter 옵션을 설정해줘야 함.

?characterEncoding=UTF-8&connectionCollation=utf8mb4_general_ci

7. 문법 오류

MySQL5.7에서는 정상적으로 작동하지만 8.0버전에서는 문법 오류 발생

1. group by 절에 정렬 조건 작성 불가**

SELECT *
FROM table
GROUP BY column DESC

-- 아래와 같이 ORDER BY절 필요
SELECT *
FROM table t
GROUP BY t.column
ORDER BY t.column DESC

2. NULLIF(expr1, expr2)

두 인자 expr1의 타입과 expr2타입이 일치해야함

-- t.date는 date타입, ''는 text타입으로 불일치
SELECT NULLIF(t.date, '') FROM table t

-- IFNULL(), CONCAT()는 타입이 일치하지 않아도 문법 오류가 발생하지 않음
SELECT IFNULL(t1.date. '') FROM table t1;
SELECT CONCAT(t2.date. 'text') FROM table t2;

8. 사용자 인증 방식 변경

MySQL 5.7 기본 인증 방식: Native Authentication
MySQL 8.0 기본 인증 방식: Caching SHA-2 Authentication
필자는 정상적으로 접근이 가능하여 따로 작업하지 않음.

9. 외래키 이름의 길이

MySQL 8.0에서는 외래키의 제약조건이 64글자로 제한된다.
현재 사용중인 DB에서 64글자 이상의 외래키 제약조건 이름은 없는 것으로 확인.

-- 확인 쿼리
SELECT TABLE_NAME, CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE REFERENCED_TABLE_NAME IS NOT NULL
AND CHAR_LENGTH(CONSTRAINT_NAME) >= 64;

10. Index

MySQL 5.x에서 사용되던 인덱스가 있다면 MySQL 8.0로 업그레이드 후 성능 테스트를 수행해야 한다.

옵티마이저가 변경되었기 때문에 같은 쿼리라도 실행계획이 달라질 수 있다.

MySQL 5.x에서는 성능 향상에 도움이 됐지만 MySQL 8.x에서는 오히려 성능 저하를 유발할 수도 있다.

테스트 결과 지금까지는 성능이 저하된 쿼리는 없는 것으로 확인.

11. 파티션을 위한 공용 테이블스페이스

MySQL 8.x에서는 파티션의 각 테이블스페이스를 공용 테이블스페이스에 저장할 수 없다. 그래서 파티션의 테이블스페이스가 공용 테이블스페이스에 저장된 것이 있는지 먼저 확인하고, 있다면 ALTER TABLE ... REORGANIZE 명령을 실행해 개별 테이블스페이스를 사용하도록 변경해야 한다.

아래 쿼리로 공용 테이블스페이스에 저장된 게 없는 것으로 확인.

SELECT table_name, partition_name, subpartition_name, tablespace_name 
FROM information_schema.partitions 
WHERE table_schema = 'your_database_name'
AND tablespace_name = 'innodb_system';