A shell script is a collection of commands to perform a specific job. MySQL is a relational database management system widely used on Linux systems. Amazon S3 is a cloud storage device provided by Amazon Web Services. It’s a good practice for the system administrator to back up databases at regular intervals and store them in a remote location like Amazon S3.
This tutorial contains a shell script that creates MySQL databases backup and uploads them to Amazon S3 buckets. You can also use this shell script to back up MariaDB or Amazon Aurora (MySQL compatible) databases.
Use the below step-by-step tutorial to back up the MySQL databases and upload them to the Amazon S3 bucket.
In order to use this script, the system must have AWS CLI installed.
https://tecadmin.net/installing-aws-cli-in-linux/
Login to AWS Management Console and create a new s3 bucket.
Alternatively, you can also create s3 bucket via AWS CLI. The command will be like:
aws s3api create-bucket --bucket s3-bucket-name --region us-east-1
Just replace the bucket name and region.
Copy the below shell script to a file like db-backup.sh. This script uses mysqldump command to create databases backups. Then use gzip command to archive backup files and finally use aws command to upload backup files to Amazon S3 bucket.
Create a file like /backup/scripts/s3-backup-mysql.sh in edit your favorite text editor. Then add the below content:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
#!/usr/bin/env bash
#########################################################################
#########################################################################
###
#### Author: Rahul Kumar
##### Website: https://tecadmin.net
####
#########################################################################
#########################################################################
# Set the folder name formate with date (2022-05-28)
DATE_FORMAT
=$
(
date
+
"%Y-%m-%d"
)
# MySQL server credentials
MYSQL_HOST
="localhost"
MYSQL_PORT
="3306"
MYSQL_USER
="user"
MYSQL_PASSWORD
="password"
# Path to local backup directory
LOCAL_BACKUP_DIR
="/backup/dbbackup"
# Set s3 bucket name and directory path
S3_BUCKET_NAME
="s3-bucket-name"
S3_BUCKET_PATH
="backups/db-backup"
# Number of days to store local backup files
BACKUP_RETAIN_DAYS
=30
# Use a single database or space separated database's names
DATABASES
="DB1 DB2 DB3"
##### Do not change below this line
mkdir
-
p
$
{
LOCAL_BACKUP_DIR
}
/$
{
DATE_FORMAT
}
LOCAL_DIR
=$
{
LOCAL_BACKUP_DIR
}
/$
{
DATE_FORMAT
}
REMOTE_DIR
=s3
:
//$
{
S3_BUCKET_NAME
}
/$
{
S3_BUCKET_PATH
}
for
db
in
$
DATABASES
;
do
mysqldump
\
-
h
$
{
MYSQL_HOST
}
\
-
P
$
{
MYSQL_PORT
}
\
-
u
$
{
MYSQL_USER
}
\
-
p
$
{
MYSQL_PASSWORD
}
\
--
single
-transaction
$
{
db
}
|
gzip
-
9
>
$
{
LOCAL_DIR
}
/$
{
db
}
-$
{
DATE_FORMAT
}
.sql
.gz
aws
s3
cp
$
{
LOCAL_DIR
}
/$
{
db
}
-$
{
DATE_FORMAT
}
.sql
.gz
$
{
REMOTE_DIR
}
/$
{
DATE_FORMAT
}
/done
DBDELDATE
=`
date
+
"${DATE_FORMAT}"
--
date
="${BACKUP_RETAIN_DAYS} days ago"
`
if
[
!
-
z
$
{
LOCAL_BACKUP_DIR
}
]
;
then
cd
$
{
LOCAL_BACKUP_DIR
}
if
[
!
-
z
$
{
DBDELDATE
}
]
&& [ -d ${DBDELDATE} ];
then
rm
-
rf
$
{
DBDELDATE
}
fi
fi
## Script ends here
Update all the necessary variables as per your system environment.
Set the execute (x) permission on script:
chmod +x s3-backup-mysql.sh
Then run the backup script.
./s3-backup-mysql.sh
Schedule the shell script using crontab to run on a daily basis.
crontab -e
Add the below settings to end of the file:
# Run daily @ 2am 0 2 * * * /backup/scripts/s3-backup-mysql.sh > /dev/null 2>&1
Save the file and close it.
This tutorial provides you with a shell script to back up MySQL databases and upload them to the Amazon S3 bucket. That could be helpful for you to automate database backups and save a copy on cloud storage.
With high quality products and considerate service, we will work together with you to enhance your business and improve the efficiency. Please don't hesitate to contact us to get more details of mysql backup to s3.