Export RDS AWS Database to EC2 in the same VPC. Ruby on Rails App

A couple of days ago I had a requirement from my company. This was a new DevOps requirement I have never done it. We have a Ruby on Rails application running in an EC2 AWS machine. We managed to connect this EC2 with a AWS RDS database running in the same VPC and with the same security group.

Although, we recently decided to migrate our database to the EC2 locally and dont rely on AWS RDS anymore. Partly because of the cost, but most importantly for having complete control over it, for example, replicating the database instance ourselves in our environment. Thereafter, the challenge of this DevOps task was to backup the database, and migrate it to the EC2 running in the same VPC and Security group. This is important to remember since, if any of them were running in different VPC or Security group, the process will be a bit more complex. For more information about different scenarios, you can refer to this link.

I want to share the process of how we migrated the backup of the RDS into our EC2 to run locally. The steps I followed are:

Step 1: Be sure that both VPC and Security Group are common in the RDS database and in the EC2. For more information about VPCs you can refer to the following link. The picture to the left shows a general architecture of how AWS presents to the external world. Our RDS and EC2 should ideally be within the same subnet and same VPC.

Step 2: Confirm that you can have access to the RDS

If we set up the configuration in AWS in both the RDS and the EC2 we should be in our way to test if EC2 can connect to RDS. Assuming all the steps we passed through correctly, we should be able to do something like this in our EC2 terminal:

sudo mysql -h <rds host> -u <database user> -p

You can find the rds endpoint in the description of the RDS database. If the configuration of AWS of RDS and EC2 was correct, you should be able to connect to the RDS database and thereafter modify, create or whatever in the database.

Step 3: Export the database to your current EC2

Once we have confirmed the connection with our RDS, we can export the database we want. For this we need to type the following in the EC2 terminal:

mysqldump -h rds.host.name -u remote_user_name -p remote_db > dump.sql

This command will export the remote_db to the current directory of the EC2 in the form of an sql file, as explained in this link. It is possible that we get an error like the following:

mysqldump throws: Unknown table ‘COLUMN_STATISTICS’ in information_schema (1109)

This is due to a new flag that is enabled by default in mysqldump 8, as explained in this link. You can disable it by adding — column-statistics=0. The command will be something like:

mysqldump --column-statistics=0 --host=<server> --user=<user> --password=<password>

In the case you are using mysql workbench, you can set up the column exception following these steps.

Step 4: Importing the database sql file

If the steps were followed up to this point, we will have a .sql file in our EC2. The last step of the migration is to import the sql file to the current EC2 database. We need to go to the directory where the sql file was imported and run the following command:

mysql -u local_user_name -p local_db < dump.sql

The user should be the EC2 user and we have to select the local_db we want the sql file to be imported into. If we dont get errors we should have imported successfully and able to do mysql -u root -p and check the imported database.

Step 5(Optional):

This step is additional. As I mentioned at the beginning of this post, we were running ruby on rails app that was already communicating with the RDS through mysql2 gem. Now it needed to just read the local database. Well, I ran into an error after my import, it led me to reinstall mysql.

The problem presented as an error in the connection similar to this:

client.rb:90:in `connect’: Error in server handshake (Mysql2::Error::ConnectionError)

You have two options, first, try to install missing:

sudo apt-get install libmysqlclient-dev

Then try to connect, if you have another error similar to this:

mysql2.rb:36:in `require’: libmariadb.so.3: cannot open shared object file: No such file or directory

In this case, is better to uninstall reinstall mysql with the following commands. In my case, it fixed the problem.

The second option is to delete GemFile.lock and cache so you can reinstall your gems again.

If you are experienced with Ruby on Rails, not as me, you will be able to find the problem but in my case, it was enough for solving the problem. In the end I was able to access the same data and keep working normally.

BONUS:

If you want more tools for manipulating mysql tables backups locally before using mysqldump, you can refer to this youtube video:

As always, thanks for reading this input, to help the community. If you found it useful please clap it and share it. I would really really appreciate it.

…

If you want to know more about me and my projects follow me in my social media:

Website, LinkedIn, Gihub