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.
An existing MariaDB can be present on premise or in another EC2 instance. Diagrammatically what we do is shown below.
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.
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.
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
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.
aws rds create-db-instance-read-replica \ --db-instance-identifier myreadreplica \ --source-db-instance-identifier mydbinstance
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
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.
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.
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