# Migrate Local MySQL to RDS

We'll be migrating a locally hosted MySQL database to Amazon RDS. Effectively upgrading the monolithic architecture to a multi-tiered architecture.

* Scenario: Evolve the architecture of a Ghost or WordPress blog with a local MySQL database running on a single EC2 instance by utilizing Amazon RDS: MySQL.
    

### Why use AWS RDS?

> Amazon Relational Database Service is a collection of managed services that makes it easy to set up, operate, and scale databases in the cloud.  Utilizing RDS will allow us to remove the admin tasks of managing our database.

Choose from popular engines such as:

```plaintext
MySQL
MariaDB
PostgreSQL
Oracle
SQL Server
Aurora: MySQL / PostgreSQL
```

**Prerequisites**

* EC2 Instance Running Ghost or WordPress blog with local MySQL DB
    
* RDS Instance
    

---

### **Creating the RDS DB**

* In your AWS Account Navigate to RDS dashboard and click Create Database:
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690918432928/8324f1ca-c36a-4ff3-bd2f-4a31062d0e35.png align="center")

* Select Standard Create
    
* Select MySQL
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690918465473/5173bfd4-234d-40e6-a6e8-bb7b37030f46.png align="center")

* We could utilize Multi-AZ, and provisioned IOPS; however, we will be running this database under the free tier option (no bells and whistles):
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690918477299/8c54e563-d5fc-4c3e-ad16-ff8d9910592c.png align="center")

* Keep note of your RDS Username / Password as we will utilize those credentials in the upcoming steps.
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690918492939/123ae501-d251-4f42-a321-f8c4d0b7829c.png align="center")

* Keep all defaults: The instance class should be listed as db.t3.micro
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690918543522/ff510662-e826-4529-a0b4-c6bc4770e256.png align="center")

* Select General Purpose SSD - I've allocated the minimum amount for both storage and max storage threshold.
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690918558366/b7a6990b-24ce-45b9-b091-1226f244a157.png align="center")

* Ensure to select Connect to an EC2 compute resource and select the EC2 instance hosting your blog.
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690918577827/ffba8ad5-6ade-4746-9ed2-c8d56db99d3a.png align="center")

* Keep default options:
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690918657782/eaf61416-23ae-44f0-8e29-42d3dd2e5dcc.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690918676798/7cc25954-e46e-4091-8be5-e2898240cbb9.png align="center")

* Everything looks good! We wont need enhanced monitoring - Lets go ahead and create the database.
    

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690918685683/71ab6ec1-b9a8-46c9-a376-1ce7a36e49ae.png align="center")

* After database creation, be sure to gather the RDS Endpoint (you can find the RDS Endpoint by clicking on the RDS database you've just created)
    

Your endpoint should look similar to this:

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690918725390/d0ca0036-4803-468f-a425-95eedd7e6130.png align="center")

* SSH into your EC2 instance (I'll be using EC2 Instance Connect) and run the following commands
    

(You can connect to your instance via the EC2 Dashboard):

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690919946436/69ff4dab-7bbc-4687-ac91-d6f1a1618a26.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690919959550/e4cbccf2-f97d-4000-8680-5c575f133241.png align="center")

---

### MySQL Example Commands

The following is an example of MySQL commands we'll be using as well as showing what I'll be migrating.

```bash
mysql
```

![](https://www.theawsdev.com/content/images/2022/10/image-69.png align="left")

```bash
show databases;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690920012332/123e3af1-2aaf-45ce-933d-82b51d8d96ca.png align="center")

* The name of the database i'll be migrating is "theawsdev\_com"
    

```bash
use YOURDATABASENAME;
```

![](https://www.theawsdev.com/content/images/2022/10/image-70.png align="left")

```bash
show tables;
```

![](https://www.theawsdev.com/content/images/2022/10/image-56.png align="left")

```bash
exit
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690920252528/0b540ca9-dcbe-4c94-8f58-92f9e346d497.png align="center")

End of Example

---

### Backup Local MySQL Database

We will perform the database backup via the mysqldump command:

* ```bash
        # Change DATABASENAME to your database - I will be naming the dump file database_backup.sql
        
        mysqldump -u USERNAME -p DATABASENAME > database_backup.sql
    ```
    
* You won't have a confirmation that the backup succeeded, but running the list command will show that we have a new file called database\_backup.sql.
    

```bash
ls -la
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690920175202/7f56ac4b-000b-4953-8ac5-f206563b49da.png align="center")

* Connect to the RDS Database instance
    

```bash
# Change RDSDATABASENAME / RDSUSERNAME with the appropriate RDS credentials, you will be prompted for a password

mysql -h RDSDATABASENAME -P 3306 -u RDSUSERNAME -p
```

```bash
show databases;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690920062415/cf8d7de5-4656-49fe-b438-ac1420282b3f.png align="center")

* Create an empty database for our import
    

```bash
#change DATABASENAME to a specified name

create database DATABASENAME;
```

```bash
quit;
```

![Created db theawsdev2_com](https://cdn.hashnode.com/res/hashnode/image/upload/v1690920370103/6bcd65af-137c-4c12-b2c8-0063e7d0eadd.png align="center")

* Now lets import our backup file to our empty database:
    

```bash
#Change RDSENDPOINT / RDSUSERNAME / DATABASENAME to the appropriate values, you will be prompted for a password.

mysql -h RDSENDPOINT -u RDSUSERNAME -p DATABASENAME < database_backup.sql
```

* You may have a delay depending on the size of the backup and there will be no confirmation as to the success of the import.
    
* Let's connect back to the RDS database and check if our import was successful:
    

```bash
#Change RDSDATABASENAME / RDSUSERNAME with the appropriate RDS credentials, you will be prompted for a password

mysql -h RDSDATABASENAME -P 3306 -u RDSUSERNAME -p
```

* Check database tables once more:
    

```bash
show databases;

#I will be selecting the newly created "theawsdev2_com"

use DATABASENAME;

show tables;
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690920436281/4bd1a7a8-9afe-4ce9-a767-dfffc004236d.png align="center")

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690920453362/aba3eb0a-a08f-4433-9bcb-05e6b20cf0c8.png align="center")

* I show 61 rows in our new database which coincides with 61 rows in our previous database example - SUCCESS! We have migrated our local data to RDS.
    
* Now all we will need to do is update our blog config files to point to our RDS DB.
    

---

### Update Configuration to Point to RDS DB

* If you are running WordPress I advise reviewing this documentation to update your configuration file:
    

[Deploy WordPress with Amazon RDS](https://aws.amazon.com/getting-started/hands-on/deploy-wordpress-with-amazon-rds/module-four/)

Since I am running Ghost, the configuration file is found at /var/www/SITENAME

```bash
cd /var/www/SITENAME

nano config.production.json
```

![](https://cdn.hashnode.com/res/hashnode/image/upload/v1690920544295/9b242c28-5445-4e3a-abc9-d8bbb19161a1.png align="center")

* The configuration file database section will need to be updated with these new values:
    

```bash
"database": {
  "client": "mysql",
  "connection": {
    "host": "RDS_ENDPOINT",
    "port": 3306,
    "user": "RDS_database_username",
    "password": "RDS_database_password",
    "database": "RDS_database_name",
    "ssl": "Amazon RDS"
  }
}
```

* After updating this section in our configuration file we can do the following commands to save and exit Nano.
    

```bash
CTRL + X
Y
Enter
```

We have now completed the migration of our local DB to RDS! Our blog is now utilizing AWS Managed Relational Database Service.

Thank you for joining me on this adventure - until next time!