Amazon RDS - MariaDB Data Import

Amazon RDS MariaDB provides easy ways of importing data into the DB and exporting data from the DB. After we are able to successfully connect to the MariaDB database we can use CLI tools to run the import and export commands to get the data from other sources in and out of the RDS database.

Below are the scenarios to consider when deciding on the approach to the import the data into the Amazon RDS- MariaDB database.

From an Existing MariaDB database

An existing MariaDB can be present on premise or in another EC2 instance. Diagrammatically what we do is shown below.

on_premise.jpg

Creating a backup from On-Premise DB

As a first step we create a backup of the on-premise database using the below command. MariaDB being a clone of MySQL, can use nearly all the same commands as MySQL.

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

A file with name backupfile.sql is cerated which contains the table structure along with the data to be used.

Storing the backup file in S3.

Upload the backup file created above to a pre-decided Amazon S3 bucket in the same region where the target RDS MariaDB database is present. You can follow this link to learn about how to upload.

Import data from Amazon S3 to RDS- MariaDB database

You can use the following Amazon CLI command to import the data from S3 to MariaDB 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:role/rolename \
--s3-prefix bucketprefix \
--source-engine mysql \
--source-engine-version 5.6.27

From Another RDS- MariaDB Instance

There may be scenarios when you want data from an existing RDS MariaDB DB to be taken into another RDS MariaDB. For example, to cerate a Disaster recovery DB or create a DB only for business reporting etc. In such scenario, we create read replicas which are a copy of their source DB and then promote that read replica to a new DB instance. They are used to prevent direct heavy read from the original source DB when we want to copy the data.

create a read-replica

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

Promote a Read replica to DB Instance

Now as we have the replica, we can promote it to a standalone DB instance. This will serve our end need of importing data from o RDS – MariaDB DB to a new one. The following command is used to complete the promotion of a read replica to a db instance.

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

In order to import data from any other database to Amazon RDS – MariaDB, we have to use the amazon Data Migration Service also called Amazon DMS. It uses Schema conversion tool to translate the existing data base to a the MYSQL platform. The below diagram explains the overall process. Also it works on the similar principle of replication as described in the previous section.

amazon_dms.jpg

Exporting Data from MariaDB

Exporting of data from Amazon RDS Mysql DB is a straight forwards process where it works on the same replication principle we have seen above. Below are the steps to carry out the export process.

  • Start the instance of MariaDB running external to Amazon RDS.
  • Designate the MariaDB DB instance to be the replication source.
  • Use mysqldump to transfer the database from the Amazon RDS instance to the instance external to Amazon RDS.

Below is the code for mysqldump command to transfer the data

 
mysqldump -h RDS instance endpoint \
    -u user \
    -p password \
    --port=3306 \
    --single-transaction \
    --routines \
    --triggers \
    --databases  database database2 \
    --compress  \
    --compact | mysql \
        -h MariaDB host \
        -u master user \
        -p password \
        --port 3306
Advertisements