前提：
移行元DB: RDS(MySQL8)
移行先DB: RDS(PostgreSQL13)
作業用EC2: Amazon Linux2 t3.medium
-- 1. EC2(Amazon Linux2)へクライアントインストール
sudo yum -y install jq
sudo yum -y remove postgresql mysql
-- MySQL 8クライアント
https://qiita.com/tamorieeeen/items/d9b2af588f1dfd43120d
sudo yum -y remove mariadb-libs
sudo yum -y localinstall https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
sudo yum-config-manager --disable mysql57-community
sudo yum-config-manager --enable mysql80-community
sudo yum info mysql-community-client
sudo yum -y install mysql-community-client
mysql --version
-- PostgreSQL 13クライアント
amazon-linux-extras list | grep postgresql
sudo amazon-linux-extras install postgresql13 -y
psql --version
-- 2. 移行元RDSインスタンス作成
aws rds create-db-parameter-group \
--db-parameter-group-name pg01 \
--db-parameter-group-family mysql8.0 \
--description pg01
aws rds modify-db-parameter-group \
--db-parameter-group-name pg01 \
--parameters ParameterName=binlog_checksum,ParameterValue=NONE,ApplyMethod=immediate
aws rds modify-db-parameter-group \
--db-parameter-group-name pg01 \
--parameters ParameterName=binlog_format,ParameterValue=ROW,ApplyMethod=immediate
aws rds modify-db-parameter-group \
--db-parameter-group-name pg01 \
--parameters ParameterName=log_bin_trust_function_creators,ParameterValue=1,ApplyMethod=immediate
aws rds create-db-instance \
--db-instance-identifier mysql01 \
--allocated-storage 20 \
--db-instance-class db.t3.micro \
--engine mysql \
--master-username root \
--master-user-password 'password' \
--no-multi-az \
--engine-version 8.0.25 \
--storage-type gp2 \
--no-publicly-accessible \
--no-enable-performance-insights \
--no-auto-minor-version-upgrade \
--db-parameter-group-name pg01
-- 3. 移行先RDSインスタンス作成
aws rds create-db-parameter-group \
--db-parameter-group-name pg02 \
--db-parameter-group-family postgres13 \
--description pg02
aws rds modify-db-parameter-group \
--db-parameter-group-name pg02 \
--parameters ParameterName=session_replication_role,ParameterValue=replica,ApplyMethod=immediate
aws rds create-db-instance \
--db-instance-identifier postgres01 \
--allocated-storage 20 \
--db-instance-class db.t3.micro \
--engine postgres \
--master-username postgres \
--master-user-password 'password' \
--no-multi-az \
--engine-version 13.3 \
--storage-type gp2 \
--no-publicly-accessible \
--no-enable-performance-insights \
--no-auto-minor-version-upgrade \
--db-parameter-group-name pg02
-- 4. テストデータ作成
mysql -h mysql01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -P 3306 -u root -p
drop database test;
create database test;
use test;
drop table tab1;
create table tab1(col1 int primary key,col2 varchar(10),col3 datetime);
insert into tab1 values(1,'AAAAAAAAAA',CURDATE());
select * from tab1;
drop table tab2;
create table tab2(col1 int auto_increment primary key ,col2 int);
insert into tab2(col2) values(1);
select * from tab2;
create table tab3(
col1 int primary key,
col2 bigint,
col3 decimal(10),
col4 decimal(10,2),
col5 varchar(10),
col6 char(10),
col7 date,
col8 datetime,
col9 timestamp,
col10 blob,
col11 text);
insert into tab3 values(1,1,1,1.00,'A','A',CURDATE(),CURDATE(),CURDATE(),'A','A');
select * from tab3;
show create table tab1;
show create table tab2;
show create table tab3;
create view view1 as select * from tab1;
show create view view1;
drop function func1;
delimiter //
create function func1()
returns int
deterministic
begin
return 0;
end//
delimiter ;
show create function func1\G
drop procedure proc1;
delimiter //
create procedure proc1()
begin
select 1;
end//
delimiter ;
show create procedure proc1\G
psql -h postgres01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -p 5432 -U postgres
\l
create database test;
\l
-- 5. EC2(Amazon Linux2)へSCTインストール
https://aws.amazon.com/jp/premiumsupport/knowledge-center/ec2-linux-2-install-gui/
https://s3.amazonaws.com/publicsctdownload/Fedora/aws-schema-conversion-tool-1.0.latest.zip
sudo yum -y install xdg-utils
sudo yum -y install libX11-1.6.7-3.amzn2.x86_64
sudo yum -y install 1:libglvnd-glx-1.0.0-1.amzn2.0.2.x86_64
sudo amazon-linux-extras install mate-desktop1.x -y
sudo bash -c 'echo PREFERRED=/usr/bin/mate-session > /etc/sysconfig/desktop'
sudo reboot
rpm -qlp aws-schema-conversion-tool-1.0.654-1.x86_64.rpm
sudo rpm -ivh aws-schema-conversion-tool-1.0.654-1.x86_64.rpm
/opt/aws-schema-conversion-tool/bin/AWSSchemaConversionTool
-- 6. JDBCドライバインストール
---- 6.1. MySQL
https://www.mysql.com/products/connector/
Platform Independent
展開して mysql-connector-java-8.0.26.jar をEC2インスタンスへアップロード
---- 6.2. PostgreSQL
https://jdbc.postgresql.org/download.html
postgresql-42.2.24.jar をEC2インスタンスへアップロード
---- 6.3. グローバル設定へのドライバパスの保存
sudo mkdir -p /usr/local/jdbc-drivers
cd /usr/local/jdbc-drivers
sudo mv ~ec2-user/*.jar .
AWS SCT で、[Settings] を選択し、[Global Settings] を選択します。
[Global settings] で、[Drivers] を選択します。JDBC ドライバへのファイルパスを追加します。
-- 7. AWS SCTでソース・スキーマをターゲット・データベースに移行
Convert schema → Apply to database
psql -h postgres01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -p 5432 -U postgres test
移行元DBの名前のスキーマに作成される
set search_path = 'test';
\d
\df+
aws dms create-replication-instance \
--replication-instance-identifier dms01 \
--allocated-storage 20 \
--replication-instance-class dms.t3.micro \
--no-multi-az \
--engine-version 3.4.5 \
--no-auto-minor-version-upgrade \
--no-publicly-accessible
aws dms describe-replication-instances
-- 9. ソースエンドポイント作成
aws dms create-endpoint \
--endpoint-identifier mysql01 \
--endpoint-type source \
--engine-name mysql \
--username root \
--password 'password' \
--server-name mysql01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com \
--port 3306 \
--ssl-mode none
aws dms describe-endpoints
-- 10. ターゲットエンドポイント作成
aws dms create-endpoint \
--endpoint-identifier postgres01 \
--endpoint-type target \
--engine-name postgres \
--username postgres \
--password 'password' \
--server-name postgres01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com \
--port 5432 \
--database-name test \
--ssl-mode none
aws dms describe-endpoints
-- 11. タスクの作成
vim tablemappings.json
{
"rules": [
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"schema-name": "test",
"table-name": "%",
"table-type": "table"
},
"rule-action": "include"
}
]
}
{
"TargetMetadata": {
"TargetSchema": "test",
"SupportLobs": true,
"FullLobMode": false,
"LobChunkSize": 64,
"LimitedSizeLobMode": true,
"LobMaxSize": 32,
"InlineLobMaxSize": 0,
"LoadMaxFileSize": 0,
"ParallelLoadThreads": 0,
"ParallelLoadBufferSize":0,
"ParallelLoadQueuesPerThread": 1,
"ParallelApplyThreads": 0,
"ParallelApplyBufferSize": 100,
"ParallelApplyQueuesPerThread": 1,
"BatchApplyEnabled": false,
"TaskRecoveryTableEnabled": false
},
"FullLoadSettings": {
"TargetTablePrepMode": "DO_NOTHING",
"CreatePkAfterFullLoad": false,
"StopTaskCachedChangesApplied": false,
"StopTaskCachedChangesNotApplied": false,
"MaxFullLoadSubTasks": 8,
"TransactionConsistencyTimeout": 600,
"CommitRate": 10000
},
"Logging": {
"EnableLogging": false
}
}
aws dms create-replication-task \
--replication-task-identifier dms01 \
--source-endpoint-arn arn:aws:dms:ap-northeast-1:999999999999:endpoint:BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB \
--target-endpoint-arn arn:aws:dms:ap-northeast-1:999999999999:endpoint:CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC \
--replication-instance-arn arn:aws:dms:ap-northeast-1:999999999999:rep:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA \
--migration-type full-load-and-cdc \
--table-mappings file://tablemappings.json \
--replication-task-settings file://settings.json
aws dms describe-replication-tasks
-- 12. データの移行
-- タスク開始
aws dms start-replication-task \
--replication-task-arn arn:aws:dms:ap-northeast-1:999999999999:task:DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD \
--start-replication-task-type start-replication
psql -h postgres01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -p 5432 -U postgres test
set search_path = 'test';
select * from tab1;
select * from tab2;
select * from tab3;
mysql -h mysql01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -P 3306 -u root -p test
insert into tab1 values(2,'AAAAAAAAAA',CURDATE());
select * from tab1;
insert into tab2(col2) values(2);
select * from tab2;
insert into tab3 values(2,1,1,1.00,'A','A',CURDATE(),CURDATE(),CURDATE(),'A','A');
select * from tab3;
-- タスク停止
aws dms stop-replication-task \
--replication-task-arn arn:aws:dms:ap-northeast-1:999999999999:task:DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
psql -h postgres01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -p 5432 -U postgres test
set search_path = 'test';
select * from tab1;
select * from tab2;
select * from tab3;
mysql -h mysql01.xxxxxxxxxxxx.ap-northeast-1.rds.amazonaws.com -P 3306 -u root -p test
insert into tab1 values(3,'AAAAAAAAAA',CURDATE());
select * from tab1;
insert into tab2(col2) values(3);
select * from tab2;
insert into tab3 values(3,1,1,1.00,'A','A',CURDATE(),CURDATE(),CURDATE(),'A','A');
select * from tab3;
-- タスク再開
aws dms start-replication-task \
--replication-task-arn arn:aws:dms:ap-northeast-1:999999999999:task:DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD \
--start-replication-task-type resume-processing
-- タスク停止
aws dms stop-replication-task \
--replication-task-arn arn:aws:dms:ap-northeast-1:999999999999:task:DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
-- 13. クリーンアップ
-- タスク削除
aws dms describe-replication-tasks
aws dms delete-replication-task \
--replication-task-arn arn:aws:dms:ap-northeast-1:999999999999:task:DDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD
-- ソースエンドポイント削除
-- ターゲットエンドポイント削除
aws dms describe-endpoints
aws dms delete-endpoint \
--endpoint-arn arn:aws:dms:ap-northeast-1:999999999999:endpoint:BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
aws dms delete-endpoint \
--endpoint-arn arn:aws:dms:ap-northeast-1:999999999999:endpoint:CCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCCC
aws dms describe-replication-instances
aws dms delete-replication-instance \
--replication-instance-arn arn:aws:dms:ap-northeast-1:999999999999:rep:AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
-- RDSインスタンス削除
aws rds delete-db-instance --db-instance-identifier mysql01 --skip-final-snapshot
aws rds delete-db-instance --db-instance-identifier postgres01 --skip-final-snapshot
-- パラメータグループの削除
aws rds describe-db-parameter-groups
aws rds delete-db-parameter-group --db-parameter-group-name pg01
aws rds delete-db-parameter-group --db-parameter-group-name pg02
