MySQL backup and recovery for web services

This document explains how to manually back up and restore the MySQL database of a pre-configured web service. The resource names and configurations used in this tutorial are placeholders created for demonstration purposes.

Basic information

• Estimated time required: 15 minutes
• User environment
  • Recommended operating systems: MacOS, Ubuntu
  • Region: kr-central-2

About scenario

This scenario guides you through the process of backing up and restoring a pre-configured MySQL database for a web service. By following these steps, you will learn how to perform MySQL database backup and restoration, and connect the restored database to an application server to recover a stable service environment.

Before you start

Before starting this tutorial, you need to have a VM-based web service prepared. For instructions on setting up a web service environment, refer to the VM-based web service configuration document.

Step-by-Step Guide

Step 1. Manual MySQL Backup

Manually back up your MySQL database. For more details on setting up automatic backups, refer to the MySQL automatic backup setup document.

1. Navigate to Data Store > MySQL in the KakaoCloud Console.

2. Go to the details page of the tutorial-mysql database created in the VM-based web service configuration tutorial.

3. Select the Backup menu at the top of the MySQL details page.

4. In the Backup menu, click the [Create Backup] button and enter the required information.

   Field	Value
   Selected instance group name	tutorial-mysql
   Backup name	tutorial-mysql-backup-manual-1

5. Check the backup list and ensure that the backup status is Succeeded.

Step 2. Restore MySQL from Backup

In this step, you will restore the MySQL database using the previously created backup.

1. From the backup list, click the [More] icon next to tutorial-mysql-backup-manual-1 created in Step 1, and select Restore.

2. Enter the required restoration details and click the [Create] button.

   Category	Field	Value
   Basic settings	Instance group name	tutorial-mysql-01
   	Description	(Optional)
   MySQL settings	Engine version	8.0.34
   	MySQL username	admin
   	MySQL password	root1234
   Instance availability		Single (Primary instance)
   Instance type		m2a.large
   Storage settings	Primary storage type	SSD
   	Primary storage size	100GB
   	Log storage type	SSD
   	Log storage size	100GB
   Network settings	VPC	tutorial
   	Subnet	tutorial_{VPC_ID}_sn_5
   Automatic backup	Automatic backup option	Disabled

3. Once the restoration is complete, ensure that the instance group status is Available.

Step 3. Update the Application Server

Update the application server to use the restored MySQL database.

1. Connect to the application server, mapped to the appropriate port of the Bastion host, as outlined in the VM-based web service configuration.

   # tutorial-app-1
   ssh -i ${PRIVATE_KEY} .pem ubuntu@${BASTION_PUBLIC_IP} -p 10002
   
   # tutorial-app-2
   ssh -i ${PRIVATE_KEY}.pem ubuntu@${BASTION_PUBLIC_IP} -p 10003

   Environment Variable	Description
   PRIVATE_KEY🖌︎	Key file name
   BASTION_PUBLIC_IP🖌︎	Go to Virtual Machine > Instance, select the Bastion instance, and check the Network tab.

2. Navigate to the directory where the project is located.

   cd ~/kakaocloud-tutorials

3. Stop and remove the running application server using the following commands.

   sudo docker stop kakaocloud-library-server
   sudo docker rm kakaocloud-library-server

4. Enter the restored MySQL endpoint, admin account, and password into the environment variables, then restart the application server.

   sudo docker run -it \
   -e MYSQL_HOST=${MYSQL_HOST} \
   -e DB_USERNAME=${DB_USERNAME} \
   -e DB_PASSWORD=${DB_PASSWORD} \
   -p 8080:8080 \
   --name kakaocloud-library-server -d \
   $(sudo docker build -q -f ./server/deploy/Dockerfile ./server)

   Environment Variable	Value	Description
   MYSQL_HOST🖌︎	MYSQL_ENDPOINT	Go to MySQL > Instance Group, select the tutorial-mysql-01 group, and check the endpoint at the top for the MySQL host address.
   DB_USERNAME🖌︎	admin	Database username
   DB_PASSWORD🖌︎	root1234	Database password

5. Perform Step 3 on both application servers.

Step 4. Verify service connection

If the service is functioning correctly, the "KakaoCloud Library" service page will be displayed.

Access