MySQL, a popular relational database management system, enables businesses and organizations to effectively store and manage their data. It is crucial to regularly perform backups of MySQL databases to prevent potential data loss caused by hardware failures, software glitches, or unexpected events. In this article, we will explore different types of database backups and provide instructions on how to schedule MySQL backups while securely storing backup files in AWS S3 file storage.

Types of MySQL backups 

Mysql database backups can be classified into different types based on the method and frequency of backup. Here are the most common types of database backups:
1. Full  backup
A full backup is a complete backup of all data in a database. Full backups usually take longer to perform and require more storage space than other backup types. However, they provide the most comprehensive backup of the database and can be used to restore the entire database in case of data loss.
To perform a full backup of a MySQL database, you can use the `mysqldump` command with the `–all-databases` option to dump all databases into a single file. Following is an example:

   mysqldump --all-databases > full_backup.sql
The previous command creates a full backup of all databases in the MySQL server and saves it to a file called `full_backup.sql`.
2. Cloud  backup
Cloud backup involves backing up a database to a cloud storage service such as Amazon S3, Microsoft Azure, or Google Cloud Storage. Cloud backups provide secure offsite storage and can be accessed from anywhere with an internet connection. However, they require a reliable internet connection and may incur additional costs for storage and data transfer.
To perform a cloud backup of a MySQL database, you can use the `mysqldump` command with a cloud storage provider’s command-line tool (such as the AWS CLI or Google Cloud SDK) to upload the backup file directly to the cloud storage. Following is an example using the AWS CLI:

   mysqldump --all-databases | gzip | aws s3 cp - s3://my-bucket/full_backup.sql.gz
The previous command creates a compressed backup of all databases in the MySQL server and uploads it to an S3 bucket called `my-bucket`.
3. Incremental backup
An incremental backup only backs up the changes made to a database since the last backup, whether it was a full backup or an incremental backup. Incremental backups are faster and require less storage space than full backups because they only save changes made since the last backup. However, restoring a database from incremental backups can be more complex than restoring from full backups.
To perform an incremental backup of a MySQL database, you can use the `mysqlbinlog` command to extract the binary log files that contain the changes made to the database since the last backup. Following is an example:

  mysqlbinlog mysql-bin.000001 > incremental_backup.sql

The previous command extracts the binary log file `mysql-bin.000001` and saves it to a file called `incremental_backup.sql`. You can then use the `mysql` command to apply the changes to the backup file.

4. Differential backup
A differential backup only backs up the changes made to a database since the last full backup. Differential backups are faster than full backups and easier to restore than incremental backups because they only save changes made since the last full backup. However, they require more storage space than incremental backups because they save all changes made since the last full backup.

5. Snapshot backup

A snapshot backup creates a point-in-time copy of a database or a storage volume. Snapshot backups are fast and efficient because they only copy the data that has changed since the last snapshot. However, they require specialized storage hardware and may not be suitable for all backup scenarios.
To perform a snapshot backup of a MySQL database, you can use the `LVM` (Logical Volume Manager) to create a snapshot of the database volume. Following is an example:

  lvcreate --snapshot --size 1G --name db_snapshot /dev/mysql/db

The previous command creates a snapshot of the `db` volume and saves it as `db_snapshot`. You can then use `rsync` or another file transfer tool to copy the snapshot to a backup location.

Scheduling database backups

One of the easiest and most common ways to schedule backups in Linux is using Cron jobs. Cron is a time-based job scheduler in Linux that allows you to schedule commands or scripts to run automatically at specific intervals. To schedule a backup using Cron, you need to create a backup script that includes the MySQL dump command to export all databases and save them to a file, and then add a Cron job to run the script at the desired interval. Additionally, you can add a shell script to upload the backup files to an AWS S3 bucket. By using both Cron jobs and shell scripts, you can automate the backup process and store the backups securely in the cloud.
Here’s a sample shell script that can backup all MySQL databases in a Linux server and upload them to an AWS S3 bucket:
#!/bin/bash

# Set backup directory and filename
BACKUP_DIR=/path/to/backup/directory
FILENAME=mysql-backup-$(date +%F-%H-%M-%S).sql.gz

# Set AWS S3 bucket name and credentials
S3_BUCKET=s3://your-bucket-name
AWS_ACCESS_KEY_ID=your-access-key
AWS_SECRET_ACCESS_KEY=your-secret-key

# Create backup directory if it doesn't exist
mkdir -p $BACKUP_DIR

# Export all MySQL databases and compress the backup file
mysqldump --all-databases | gzip > $BACKUP_DIR/$FILENAME

# Upload the backup file to AWS S3
aws s3 cp $BACKUP_DIR/$FILENAME $S3_BUCKET --region your-region --acl private --storage-class STANDARD_IA --access-key $AWS_ACCESS_KEY_ID --secret-key $AWS_SECRET_ACCESS_KEY

# Delete backup files older than 7 days
find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +7 -delete
The previous script exports all MySQL databases using the `mysqldump` command and saves the backup file to a specified directory with a timestamped filename. It then uploads the backup file to an AWS S3 bucket using the AWS CLI `aws s3 cp` command and deletes backup files older than 7 days using the `find` command.

Conclusion

Understanding the various types of MySQL backups, and their pros and cons empowers you to select the backup strategy that aligns with your needs. Furthermore, scheduling periodic backups and securely storing backup files in cloud-based file storage like AWS S3 is easily achievable.