Amazon RDS – MySQL DB Export Import

Spread the love

Amazon RDS MySQL offers straightforward methods of importing knowledge into the DB and exporting knowledge from the DB. After we’re capable of efficiently hook up with the MySQL database we will use CLI instruments to run the import and export instructions to get the info from different sources out and in of the RDS database. Under are the eventualities to think about when deciding on the strategy to the import the info into the Amazon RDS – MySQL database.

From an Current MySQL database

An current MySQL DB may be current on premise or in one other EC2 occasion. Diagrammatically what we do is proven under.

 on_premise.jpg

Making a backup from On-Premise DB

As a primary step we create a backup of the on-premise database utilizing the under command.

mysqldump -u consumer -p[user_password] [database_name] > backupfile.sql

A file with identify backupfile.sql is created which accommodates the desk construction together with the info for use.

Storing the backup file in S3.

Add the backup file created above to a pre-decided Amazon S3 bucket in the identical area the place the goal RDS MySQL DB database is current.

Import knowledge from Amazon S3 to RDS- MySQL database

You should utilize the next Amazon CLI command to import the info from S3 to MySQL DB.


aws rds restore-db-instance-from-s3   
--allocated-storage 125  
--db-instance-identifier tddbidentifier 
--db-instance-class db.m4.small 
--engine mysql 
--master-user-name masterawsuser 
--master-user-password masteruserpassword 
--s3-bucket-name tpbucket 
--s3-ingestion-role-arn arn:aws:iam::account-number:function/rolename 
--s3-prefix bucketprefix 
--source-engine mysql 
--source-engine-version 5.6.27

From One other RDS-MySQL Occasion

There could also be eventualities once you need knowledge from an current RDS MYSQL DB to be taken into one other RDS MYSQL DB. For instance, to cerate a Catastrophe restoration DB or create a DB just for enterprise reporting and so forth. In such situation, we create learn replicas that are a duplicate of their supply DB after which promote that learn reproduction to a brand new DB occasion. They’re used to stop direct heavy learn from the unique supply DB after we need to copy the info.

create a read-replica

 

aws rds create-db-instance-read-replica 
    --db-instance-identifier myreadreplica 
    --source-db-instance-identifier mydbinstance

Promote a Learn reproduction to DB Occasion

Now as we’ve got the reproduction, we will market it to a standalone DB occasion. It will serve our finish want of importing knowledge from o RDS – Mysql DB to a brand new one. The next command is used to finish the promotion of a learn reproduction to a db occasion.

 

aws rds create-db-instance-read-replica 
    --db-instance-identifier readreplica_name 
    --region target_region_name
    --db-subnet-group-name subnet_name 
    --source-db-instance-identifier arn:aws:rds:region_name:11323467889012:db:mysql_instance1

From Any Database

With the intention to import knowledge from every other database to Amazon RDS – MySQL, we’ve got to make use of the amazon Knowledge Migration Service additionally known as Amazon DMS. It makes use of Schema conversion instrument to translate the prevailing knowledge base to a the MYSQL platform. The under diagram explains the general course of. Additionally it really works on the same precept of replication as described within the earlier part.

 amazon_dms.jpg

Exporting Knowledge from MySQL

Exporting of information from Amazon RDS Mysql DB is a straight forwards course of the place it really works on the identical replication precept we’ve got seen above. Under are the steps to hold out the export course of.

  • Begin the occasion of MySQL working exterior to Amazon RDS.
  • Designate the MySQL DB occasion to be the replication supply.
  • Use mysqldump to switch the database from the Amazon RDS occasion to the occasion exterior to Amazon RDS.

Under is the code for the mysqldum command

 

 
mysqldump -h RDS occasion endpoint 
    -u consumer 
    -p password 
    --port=3306 
    --single-transaction 
    --routines 
    --triggers 
    --databases  database database2 
    --compress  
    --compact | mysql 
        -h MySQL host 
        -u grasp consumer 
        -p password 
        --port 3306