Backup database of MariaDB / MySQL with mysqldump to S3 storage in Amazon Web Services

In AWS we are facing an issue with taking a backup of one database in the environment because AWS support complete instance snapshot/backup which will include all the backup of the databases on that instance.

Problem: If we have to do some deployment on one of the databases then we need to take the backup only that database. But in AWS we have to take complete instance backup which includes all backups but in the case of restoration, all other customers will be affected on that instance.

Solution:

We use traditional ways to take backup and store backup by sync command in AWS. We can also use the copy command to copy the backup to S3 Storage.

Note: This is used for small databases.

Following is the batch file used to take backup locally and copy to S3 storage:

set TIMESTAMP=%DATE:~6,4%%DATE:~3,2%%DATE:~0,2%
mysqldump.exe  --column-statistics=0  --routines -uadmin -padmin_12345 --host=mariadbaws.sntbrdmfktcl.ap-north-1.rds.amazonaws.com --port=3306 DemoDB > "C:\MariaDBBackup\DemoDB_%TIMESTAMP%.txt"
aws s3 sync c:\Mariadbbackup\ s3://testbackupsqlserver2023/

Note:

1. Install the AWS CLI and Mysql dump client to run mysqldump command
2. Configure the AWS CLI with a user that has MariaDB permission for backup.
3. Configure the location where you can take backup on the local system.
4. Create an S3 storage for backup and use AWS sync or cp command to place backup on S3 storage.