미스터 역마살
[On-premise DB to AWS] Mysql to Mysql DB Migration 본문
728x90
반응형
Mysql DB 클라우드로 이관하기
기존 On-premise DB에서 클라우드(AWS)로 이관하는 방법은 여러가지가 있다. 그중 하나가 AWS에서 제공하는 DMS라는 기능을 이용하는 것인데, Mysql에서 Mysql로 동종DB간의 이행은 왠만하면 비용적인 측면때문에 DMS를 이용하지 않는다. DB가 호환이 안되는 부분이 없다보니 Dump를 이용해서 처리하려고 하는것이 일반적이다.
DB 이관을 어떻게 할지는 각 사이트별 상황과 여건을 고려하여 아래와 같이 결정 할 수있다.
AWS DMS
DMS를 활용하는 경우는 아래와 같다.
- 비용 측면 : 높은 비용 감당 가능
- 다운 타임 : 제로 다운 타임
- RDS와 On-premise 연결 가능 여부 : 가능하여야 함
- Target DB : RDS Mysql
절차는 아래와 같다.
1. Source & Target 생성
Source는 기존 On-premise DB로 할것이며, Target은 RDS 로 설정 하면 된다.
2. Source의 복제 인스턴스 생성
AWS내의 복제 인스턴스를 생성 하여 Source DB 정보를 실시간으로 가져올 수 있게 설정 한다.
3. Source & Target 엔드포인트 생성
Source & Target 각각의 엔드포인트를 설정 한다.
4. DMS Task 생성
주의 사항
- Target이나 Source에 AUTO_COMMIT 모드로 해야함
- 지속적 복제의 경우 binlog를 활성화 시켜 줘야 함
log-bin=mysql_bin
server-id=1
binlog_format=ROW
expire_logs_days=7
binlog_checksum=NONE
binlog_row_image=FULL
관련 사항은 아래 주소에서 확인 할 수 있다.
링크: https://docs.aws.amazon.com/ko_kr/dms/latest/userguide/CHAP_Source.MySQL.html
Dump → RDS
Dump를 활용하는 경우는 아래와 같다.
- 비용 측면 : 낮은 비용
- 다운 타임 : 다운 타임이 길어도 괜찮음
- RDS와 On-premise 연결 가능 여부 : 가능하여야 함
- DB를 내리고나서 이관이 가능해야한다.
- Target DB : RDS Mysql
이관 명령어
mysqldump -u tester --databases [DABASE 명] --single-transaction --compress --routines=0 --triggers=0 --events=0 --order-by-primary -p | mysql -u admin --port=3306 --host=[DB주소] -p유의 사항
- Amazon RDS 데이터베이스에서 저장 프로시저, 트리거, 함수 또는 이벤트를 수동으로 만들어야 한다. 복사 중인 데이터베이스에 이런 객체가 하나라도 있는 경우에는 mysqldump 명령과 함께 mysqldump 파라미터를 포함시켜
--routines=0 --triggers=0 --events=0를 실행할 때 이런 객체를 제외 한다. - sys, performance_schema 및 information_schema 스키마는 mysqldump 유틸리티에서 기본적으로 dump에서 제외한다. 즉, DB의 사용자와 권한들을 다시 설정해줘야 한다.
이관 스크립트
쉘 스크립트를 이용하여 이관 스크립트를 생성하였으며 쉘스크립트 실행으로 이관을 실행하였다.
#!/bin/bash
echo " "
echo "#############################################"
echo "Dump Migration Start"
echo "#############################################"
echo " "
echo "-Start Time"
date '+%F %r'
echo ""
echo ""
# Parameter Setting
SOURCE_DB_USER="[유저명]"
SORUCE_DB_PASSWD="[패스워드]"
SOURCE_DB="[DB명]"
TARGET_DB_USER="admin"
TARGET_DB_PASSWD="dusqhd10djr!!QQ"
TARGET_DB_IP="[IP주소]"
# Migration Start
mysqldump -u $SOURCE_DB_USER -p$SORUCE_DB_PASSWD --databases $SOURCE_DB --single-transaction --compress --routines=0 --triggers=0 --events=0 --order-by-primary | mysql -u $TARGET_DB_USER --port=3306 -p$TARGET_DB_PASSWD -h $TARGET_DB_IP
echo""
echo""
echo "-End Time"
date '+%F %r'
echo " "
echo "#############################################"
echo "Dump Migration End"
echo "#############################################"
echo " "
Dump → EC2 or S3 → RDS
Dump를 활용하는 경우는 아래와 같다.
- 비용 측면 : 낮은 비용
- 다운 타임 : 다운 타임이 길어도 괜찮음
- RDS와 On-premise 연결 가능 여부 : 불가능 함
- DB를 내리고나서 이관이 가능해야한다.
- Target DB : RDS Mysql
Dump파일을 바로 RDS에 이관할수 없을 경우 EC2나 S3와 같은 저장소를 경유해서 이관하게 된다.
절차는 아래와 같다.
1. On-prem Dump file 생성
mysqldump -u test_user --databases [DATABASE명] --single-transaction --compress --routines=0 --triggers=0 --events=0 --order-by-primary -p > dump.sql
2. Dump 파일 신규 중계 EC2에 옮기기
Dump 파일을 중계 EC2를 생성해서 FTP로 옮긴다.
3. 중계 EC2에 Mysql 설치
yum update -y
yum install mysql
4. RDS로 이관하기
mysql -uadmin -p -h[DB주소] < dump.sql
Dump → RDS (Replica)
Dump를 활용하는 경우는 아래와 같다.
- 비용 측면 : 낮은 비용
- 다운 타임 : 제로 다운 타임
- RDS와 On-premise 연결 가능 여부 : 가능
1. [On-premise] my.cnf 파일 수정
# binglog 설정
log-bin=mysql_bin
server-id=1
binlog_format=ROW
expire_logs_days=7
binlog_checksum=NONE
binlog_row_image=FULL
2. [On-premise] 기초 데이터 백업 생성
mysqldump -u test_user --databases [DATABASE명] --single-transaction --master-data=2 --compress --routines=0 --triggers=0 --events=0 --order-by-primary -p > replica_dump.sql
3. [On-premise] Replication User 생성 및 권한 부여
-- USER 생성
CREATE USER 'REPLICATION_USER'@'%' IDENTIFIED BY '[패스워드]';
-- 권한 부여
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'REPLICATION_USER'@'%';
4. [On-premise] dump에서 position 확인
less replica_dump.sql
-- MySQL dump 10.13 Distrib 5.7.35, for Linux (x86_64)
--
-- Host: localhost Database: MIG_TEST_DB
-- ------------------------------------------------------
-- Server version 5.7.35-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
## -------------------------------------------------------------------
# 아래의 파일명과 포지션 복사해놓기
## -------------------------------------------------------------------
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=1285;
--
-- Current Database: `MIG_TEST_DB`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `[DATABSE명]` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `[DATABSE명]`;
5. [On-premise] RDS로 기초 데이터 입력
mysql -u admin -p < replica_dump.sql
6. [RDS] external_master 실행
-- binlog 유지기간 늘리기
call mysql.rds_set_configuration('binlog retention hours', 24);
-- external master 실행
CALL mysql.rds_set_external_master ('[IP주소]', 3306, 'replication_user', '[패스워드]', 'mysql_bin.000001', 2859, 0);
7. [RDS] replication start 하기
-- replication 시작
CALL mysql.rds_start_replication;
-- replication 중지
CALL mysql.rds_stop_replication;
8. [RDS] 잘되는지 확인하기
SHOW SLAVE STATUS;
/* Slave_IO_Running, Slave_SQL_Running 두가지의 상태가 YES로 나와야 함 */
Name |Value |
-----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Slave_IO_State |Waiting for master to send event |
Master_Host |10.0.4.202 |
Master_User |replication_user |
Master_Port |3306 |
Connect_Retry |60 |
Master_Log_File |mysql_bin.000001 |
Read_Master_Log_Pos |3281 |
Relay_Log_File |relaylog.000005 |
Relay_Log_Pos |271 |
Relay_Master_Log_File |mysql_bin.000001 |
Slave_IO_Running |Yes |
Slave_SQL_Running |Yes |
Replicate_Do_DB | |
Replicate_Ignore_DB | |
Replicate_Do_Table | |
Replicate_Ignore_Table |innodb_memcache.cache_policies,innodb_memcache.config_options,mysql.plugin,mysql.rds_configuration,mysql.rds_history,mysql.rds_monitor,mysql.rds_replication_status,mysql.rds_sysinfo|
Replicate_Wild_Do_Table | |
Replicate_Wild_Ignore_Table | |
Last_Errno |0 |
Last_Error | |
Skip_Counter |0 |
Exec_Master_Log_Pos |3281 |
Relay_Log_Space |584 |
Until_Condition |None |
Until_Log_File | |
Until_Log_Pos |0 |
Master_SSL_Allowed |No |
Master_SSL_CA_File | |
Master_SSL_CA_Path | |
Master_SSL_Cert | |
Master_SSL_Cipher | |
참고 링크 : https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql_rds_set_external_master.html
Dump → EC2(Replica) → RDS(Replica)
Dump를 활용하는 경우는 아래와 같다.
- 비용 측면 : 낮은 비용
- 다운 타임 : 제로 다운 타임
- RDS와 On-premise 연결 : 불가능
STEP 01. On-premise → EC2 (Replica)
1. [Master] 서버 접속 후 유저 생성 및 권한 부여
/* 유저 생성 */
create user 'replication_user'@'%' identified by 'dusqhd10djr!!QQ';
/* 권한 생성 */
GRANT REPLICATION SLAVE ON *.* to 'replication_user'@'%';
flush privileges;
2. [Master] database 및 테이블 생성
/* DB 생성 */
create database replica_db;
use replica_db;
/* TABLE 생성 */
create table replication_tb(id INT AUTO_INCREMENT Primary key,name VARCHAR(30));
3. [Master] my.cnf 파일 설정
## 아래 설정 확인 및 추가
log-bin=mysql_bin
server-id=1
binlog-do-db=replica_db
log-slave-updates
4. [Master] mysql restart
service mysqld restart
5. [Master] 덤프 생성
mysqldump -u replication_user -p --single-transaction --master-data=2 --routines --triggers replica_db > replica_ec2_dump.sql
## 파일 확인
ls -al
6. [Master] binlog와 포지션 확인하기
less replica_ec2_dump.sql
-- MySQL dump 10.13 Distrib 5.7.35, for Linux (x86_64)
--
-- Host: localhost Database: MIG_TEST_DB
-- ------------------------------------------------------
-- Server version 5.7.35-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
## -------------------------------------------------------------------
# 아래의 파일명과 포지션 복사해놓기
## -------------------------------------------------------------------
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=1285;
--
-- Current Database: `MIG_TEST_DB`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `MIG_TEST_DB` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `MIG_TEST_DB`;
7. [Slave] my.cnf 파일 설정
log-bin=mysql_bin
server-id=2
read_only=1
8. [Slave] restart 하기
service mysqld restart
9. [Slave] Master db 정보 입력
CHANGE MASTER TO MASTER_HOST='[ip주소]'
, MASTER_PORT=[port]
, MASTER_USER='replication_user'
, MASTER_PASSWORD='[패스워드]'
, MASTER_LOG_FILE='mysql_bin.000007'
, MASTER_LOG_POS=150 ;
10. [Slave] start slave하기
start slave
11. [Slave] 결과 확인
show slave status \G;
*************************** 1. row ***************************
..생략...
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
..생략...
/*
둘다 YES가 나오면 성공이다.
*/예외 상황
- 만약 둘중 하나라도 NO가 나온 경우
Last_IO_Error: Fatal error: The SLAVE I/O thread stops because MASTER AND SLAVE have equal MySQL SERVER UUIDs; these UUIDs must be different FOR REPLICATION TO work.해당 에러는 Slave DB 서버의 auto.cnf 파일에 현재 서버의 UUID가 적혀있는데, 이 파일까지 Master DB 서버에서 함께 가져와서 발생하는 에러다.다음 명령어 들로 해결해보자
systemctl stop mysqld
rm -rf /var/lib/mysql/auto.cnf
systemctl start mysqld이후 다시 slave를 start하고 확인해보면 적용이 되어 있을 것이다.
- Duplicate 이슈
Last_Error: Error 'Duplicate key name 'oauth_users_name_domain'' on query. Default database: 'cyauth'. Query: 'create index oauth_users_name_domain on oauth_users( username , domain , userid )'이런 경우 slave stop을 하고 아래와 같이 해주면 된다.
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1;
/*
여기선 중복 에러 나는 것이 1개 이므로, 여러 개 일 경우 숫자를 크게
SET GLOBAL SQL_SLAVE_SKIP_COUNTER=100 ;
또는 위에 에러 번호가 1061 이였으므로, my.cnf 파일에 다음과 같이 설정 가능
my.cnf
slave-skip-errors = 1061,1062,1063 등등 스킵하고 싶은 에러번호 나열
*/
- AWS RDS 경우
AWS RDS의 EC2로 진행하는 경우 보안그룹에 각각의 Public ip를 열어 줘야 한다.
STEP 02. EC2 (Replica) → RDS (Replica)
1. [EC2] my.cnf 파일 수정
# binglog 설정
log-bin=mysql_bin
server-id=1
binlog_format=ROW
expire_logs_days=7
binlog_checksum=NONE
binlog_row_image=FULL
2. [EC2] 기초 데이터 백업 생성
mysqldump -u test_user --databases [DATABASE명] --single-transaction --master-data=2 --compress --routines=0 --triggers=0 --events=0 --order-by-primary -p > replica_dump.sql
3. [EC2] Replication User 생성 및 권한 부여
-- USER 생성
CREATE USER 'REPLICATION_USER'@'%' IDENTIFIED BY '[패스워드]';
-- 권한 부여
GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.* TO 'REPLICATION_USER'@'%';
4. [EC2] dump에서 position 확인
less replica_dump.sql
-- MySQL dump 10.13 Distrib 5.7.35, for Linux (x86_64)
--
-- Host: localhost Database: MIG_TEST_DB
-- ------------------------------------------------------
-- Server version 5.7.35-log
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Position to start replication or point-in-time recovery from
--
## -------------------------------------------------------------------
# 아래의 파일명과 포지션 복사해놓기
## -------------------------------------------------------------------
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql_bin.000001', MASTER_LOG_POS=1285;
--
-- Current Database: `MIG_TEST_DB`
--
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `[DATABSE명]` /*!40100 DEFAULT CHARACTER SET latin1 */;
USE `[DATABSE명]`;
5. [EC2] RDS로 기초 데이터 입력
mysql -u admin -p < replica_dump.sql
6. [RDS] external_master 실행
-- binlog 유지기간 늘리기
call mysql.rds_set_configuration('binlog retention hours', 24);
-- external master 실행
CALL mysql.rds_set_external_master ('[IP주소]', 3306, 'replication_user', '[패스워드]', 'mysql_bin.000001', 2859, 0);
7. [RDS] replication start 하기
-- replication 시작
CALL mysql.rds_start_replication;
-- replication 중지
CALL mysql.rds_stop_replication;
8. [RDS] 잘되는지 확인하기
SHOW SLAVE STATUS;
/* Slave_IO_Running, Slave_SQL_Running 두가지의 상태가 YES로 나와야 함 */
Name |Value |
-----------------------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
Slave_IO_State |Waiting for master to send event |
Master_Host |10.0.4.202 |
Master_User |replication_user |
Master_Port |3306 |
Connect_Retry |60 |
Master_Log_File |mysql_bin.000001 |
Read_Master_Log_Pos |3281 |
Relay_Log_File |relaylog.000005 |
Relay_Log_Pos |271 |
Relay_Master_Log_File |mysql_bin.000001 |
Slave_IO_Running |Yes |
Slave_SQL_Running |Yes |
Replicate_Do_DB | |
Replicate_Ignore_DB | |
Replicate_Do_Table | |
Replicate_Ignore_Table |innodb_memcache.cache_policies,innodb_memcache.config_options,mysql.plugin,mysql.rds_configuration,mysql.rds_history,mysql.rds_monitor,mysql.rds_replication_status,mysql.rds_sysinfo|
Replicate_Wild_Do_Table | |
Replicate_Wild_Ignore_Table | |
Last_Errno |0 |
Last_Error | |
Skip_Counter |0 |
Exec_Master_Log_Pos |3281 |
Relay_Log_Space |584 |
Until_Condition |None |
Until_Log_File | |
Until_Log_Pos |0 |
Master_SSL_Allowed |No |
Master_SSL_CA_File | |
Master_SSL_CA_Path | |
Master_SSL_Cert | |
Master_SSL_Cipher | |
이관 후 검증 쿼리
다음은 이관 후 가장 중요한 검증을 위한 쿼리다.
/* 테이블 개수 확인 */
SELECT COUNT(*)
FROM INFORMATION_SCHEMA.tables
WHERE table_schema in ('DB1','DB2');
/* 테이블 데이터 건수 확인 */
SELECT concat('select ''',table_name''' AS TB, count(*) AS CNT from ', table_schema,'.', table_name,';')
FROM information_schema.tables
WHERE table_schema in ('DB1','DB2');
/* 데이터베이스 사이즈 검증 */
-- DB 사이즈는 기존 DB의 변경이나 삭제 작업이 많은경우나, 통계정보가 정확하지 않은경우 차이가 날수 있다.
SELECT table_schema "Database"
, ROUND(SUM(data_length+index_length)/1024/1024,1) "MB"
FROM information_schema.TABLES
GROUP BY 1
;
/* 프로시저, 이벤트, 함수, 뷰, 트리거 검증 */
SHOW PROCEDURE STATUS WHERE DB = 'DB1'
SHOW FULL TABLES IN ebsesof WHERE TABLE_TYPE LIKE 'VIEW';
SHOW FUNCTION STATUS WHERE DB = 'DB1';
select EVENT_SCHEMA,EVENT_NAME,INTERVAL_FIELD
from information_schema.events;
select trigger_schema, trigger_name, action_statement
from information_schema.triggers
where table_schema in ('DB1','DB2');
/* 유저 및 권한 확인 */
SELECT user,host from user;
show grants [USER]@’%’
728x90
'IT > AWS' 카테고리의 다른 글
| AWS 네트워크 #08 - RDS (2/2) (0) | 2021.07.28 |
|---|---|
| AWS 네트워크 #08 - RDS (1/2) (0) | 2021.07.27 |
| AWS 네트워크 #07 - ELB (0) | 2021.07.26 |
| AWS 네트워크 #06 - bastion host를 통한 private ec2 접속 (1) | 2021.07.26 |
| AWS 네트워크 #05 - EC2 생성 (Batstion host, private ec2) (0) | 2021.07.25 |
'IT/AWS' Related Articles
more
Comments