CODEWITHSUNDEEP
mysqlcsvamazon-web-servicesamazon-s3amazon-rds
user15051990
user15051990

Reputation: 1895

Load data to amazon RDS/SQL from S3

I know this is not a programming question but I would really appreciate for the help.

I am trying to transfer files from s3 bucket to the Amazon RDS mysql database particularly to table say 'test'. I am doing this first time and don't know how to approach to it. I tried in the following way:

aws rds restore-db-instance-from-s3 ^
--allocated-storage 250 ^ 
--db-instance-identifier myidentifier ^
--db-instance-class db.m4.large ^
--engine mysql ^
--master-user-name masterawsuser ^
--master-user-password masteruserpassword ^
--s3-bucket-name mybucket ^
--s3-ingestion-role-arn arn:aws:iam::account-number:role/rolename ^
--s3-prefix bucketprefix ^
--source-engine mysql ^
--source-engine-version 5.6.27

The AWS Console is showing "incompatible restore" for "myidentifier". I don't know how to resolve it.

And I am wondering by the above code, will the data gets load in the desired table("test")?

Thanks

Upvotes: 0

Views: 1041

Answers (2)

Richard A Quadling
Richard A Quadling

Reputation: 3988

If it is JUST data you want to backup/restore, then you can use DMS.

You can do some of this manually ...

SELECT `addressID`,`thirdPartyID`,`companyID`,`house`,`street`,`town`,`postcode`,`state`,`county`,`countryID`,`location`,`formattedAddress`,`createdAt`,`updatedAt`
FROM `addresses`
INTO OUTFILE S3 "s3-eu-west-1://rds-migration-bucket/2025-02-05 13:36:44/addresses"
FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' MANIFEST ON OVERWRITE ON;

and then

LOAD DATA FROM S3 MANIFEST "s3-eu-west-1://rds-migration-bucket/2025-02-05 13:36:44/addresses.manifest"
REPLACE INTO TABLE `addresses`
CHARACTER SET utf8mb4
FIELDS TERMINATED BY '\t' ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n'
(`addressID`,`thirdPartyID`,`companyID`,`house`,`street`,`town`,`postcode`,`state`,`county`,`countryID`,`location`,`formattedAddress`,`createdAt`,`updatedAt`)
SET `locationWKB`=ST_PointFromText(`location`);

But one thing I'm having an issue with is AWS Zero-Downtime Restarts being kicked off for seemingly no reason.

It MAY be related to the export being in 6GB chunks and that having some sort of odd impact. The export doesn't have a native mechanism of chunking smaller files. So I'm going to do that and update the manifest file that's created.

Unsure of it making any difference and a pain if it doesn't.

Upvotes: 0

Mark Hayward
Mark Hayward

Reputation: 444

You are trying to restore SQL as an RDS instance. RDS instances are managed database servers which you then create databases within. You should create an RDS instance then load your SQL code using MySQL command line tools. If you are migrating from an existing database server also have a look at AWS DMS.

Upvotes: 1

Related Questions