Automatically import database from remote AWS RDS instance to local MySQL server

5 min read. Jun 4, 2021

Background

If you're a developer, then you might have come across times when you need to import the database from a remote server to your local machine or vice versa. Well, it is quite time consuming, isn't it? You need to SSH into the remote server, dump the database, download it into your machine and finally import it. Personally, I try to automate the boring and repetitive tasks as much as possible. For the last few months, the monotonous job was bothering me so I decided to come up with a solution to do the job in a single command.

In the following article, I will walk you through the bash script that will automatically import the database from remote the server to your local machine (or you could do the other way around as well).

Problem-solving approach

Usually, I prefer to break down the problem into multiple steps and solve it. So, here's the breakdown of the solution:

  1. SSH into remote server
  2. Dump MySQL database
  3. Download it into local machine
  4. Import into local MySQL server

To get started, let's first create a bash script named dbimport (feel free to use any name) and paste the following at the top.

#!/bin/bash

For those who are not familiar with bash script, this statement is used to specify the path to the bash interpreter.

Step 1: Write a command to SSH into remote server

Most of the AWS RDS instances are configured to be accessible only from a particular server for security reasons. Therefore, we need to SSH into the remote server (having access to RDS) at first.

The following command will let us do so:

#!/bin/bash
</br>
ssh -i your_private_key username@hostname

Step 2: Dump MySQL database on remote server

The next step is to connect to the AWS RDS instance (MySQL server) from the remote server and run a command to dump MySQL database. Since we are going to wrap everything in a bash script, we need to find a way to run commands on an interactive program (our remote server). In bash programming, Here document exactly let us do so.

The basic syntax of Here document is:

command {`<<`} delimiter
</br> command1
</br>command2
</br>..
</br>delimiter

Now, the combined script to SSH into server and run command to connect to MySQL server and dump database can be written as below:

#!/bin/bash
</br> ssh -i your_private_key username@hostname /bin/bash {`<<`} EOF
</br>
mysqldump --user=your_remote_mysql_username --password=your_remote_mysql_password --host=your_remote_rds_hostname database_name_on_server {`>`} databse_name.sql --single-transaction --no-tablespaces
</br>
EOF

As you can see, I've used --single-transaction and --no-tablespaces options in the mysqldump command. According to MySQL documentation, --single-transaction option allows a consistent dump without locking the entire database in transactional tables such as InnoDB. Also, starting MySQL version 5.7, --single-transaction and --no-tablespaces options are required if the database user doesn't have LOCK TABLES and PROCESS privileges respectively.

Step 3: Download the dumped database from remote server into local machine

The next step is to download the database from the remote server into local machine. SCP is a handy program which is based on the SSH protocol that allows to transfer file between two machines.

Now, the script after adding command to download the dumped MySQL database from the remote server looks as below:

#!/bin/bash
</br> ssh -i your_private_key username@hostname /bin/bash {`<<`} EOF
</br> mysqldump --user=your_remote_mysql_username --password=your_remote_mysql_password --host=your_remote_rds_hostname database_name_on_server {`>`} databse_name.sql --single-transaction --no-tablespaces
</br> EOF
</br>
scp -i your_private_key username@hostname:databse_name.sql local_path
</br>

Depending on the size of your database, it might take from a few seconds to minutes to download the database from remote server to local machine.

Step 4: Import the database into local MySQL server

Finally, the last step is to import the downloaded database into the local MySQL server. We can do that by adding the mysql command at the end of the script as below.

#!/bin/bash
</br> ssh -i your_private_key username@hostname /bin/bash {`<<`} EOF
</br> mysqldump --user=your_remote_mysql_username --password=your_remote_mysql_password --host=your_remote_rds_hostname database_name_on_server {`>`} databse_name.sql --single-transaction --no-tablespaces
</br> EOF
</br> scp -i your_private_key username@hostname:databse_name.sql local_path
</br>
mysql --user=your_local_mysql_username --password=your_local_mysql_password --host=localhost your_local_database {`<`} path/to/database.sql

How to execute the script?

First of all, we need to make the bash script executable by running the command chmod u+x filename. In our case (filename is dbimport), the following command will do the job:

chmod u+x dbimport

chmod is used for setting permissions in Unix-like operating systems and the above command will set execute permission to the owner of the file. If you have created the file as superuser, then you have to prepend the above command with sudo.

Finally, the file can be executed by running below command:

./dbimport

Security concerns

If you have followed till now, you might have noticed the warning in your shell after running the above script:

[Warning] Using a password on the command line interface can be insecure.

The warning is the result of passing password as command line arguments for mysqldump and mysql commands. In Linux, the process command line arguments are publicly available and any other users (or program) could sniff them when the program is running. Though, it could not be a potential vulnerability in some cases, however, it is good to follow the best practices as much as possible.

You can follow this StackOverflow QA which has a good coverage on how to securely pass password to mysql command line.

Wrapping up

The process of dumping a database from the remote server and importing in your local machine can be a tedious task. And, shell scripts really shine in jobs like this. If you need to import the database from a remote server to your local machine quite too often then the above script will save you a lot of time.

For the sake of brevity, I have not added any error handling on the script however, you can feel free to do so. If you tweak a bit and change the order of the above script, then it can also be used to upload a database to a remote MySQL server.

You can find the above script in my GitHub repository as well.

If you have any questions, feel free to get in touch with me.

Shell programming, Bash, Linux