Automated MySQL Backups and Restore on Debian/Ubuntu

In this tutorial, you will learn how to create automated MySQL backups on a Debian/Ubuntu server using a script and schedule them with cron jobs. The backups will be stored in a named folder, and we will also cover how to restore a backup. The backup files will be named based on the date and time.

What You Need

A Debian / Ubuntu server with MySQL or MariaDB installed.
SSH access to your server.

Step-by-Step Guide

Step 1: SSH Access to Your Server

Connect to your Debian / Ubuntu server via SSH using the following command, replacing username and server-IP with your own information:

1ssh username@server-IP

Enter your SSH password when prompted.

Step 2: Create a Backup Script

Create a backup script that will automatically back up your MySQL databases. Create a file named backup.sh and add the following content:

 1#!/bin/bash
 2
 3# Define the backup directory
 4backup_dir="/path/to/backup/folder"
 5
 6# Define MySQL credentials
 7mysql_user="root"
 8mysql_password="your-password"
 9
10# List of databases to back up
11databases=("mydb1" "mydb2")
12
13# Current date and time
14current_datetime=$(date +"%Y%m%d%H%M%S")
15
16# Loop through databases and create backups
17for db in "${databases[@]}"; do
18  mysqldump -u "$mysql_user" -p"$mysql_password" "$db" > "$backup_dir/$db-$current_datetime.sql"
19done
20
21# Compress the backup files
22tar -czvf "$backup_dir/backups-$current_datetime.tar.gz" -C "$backup_dir" .
23
24# Remove individual SQL backup files
25rm -f "$backup_dir"/*.sql

Make sure to replace /path/to/backup/folder, your-password, and the database names in the databases array with your actual information.

Step 3: Make the Script Executable

Make the script executable with the following command:

1chmod +x backup.sh

Step 4: Schedule Backups with Cron Jobs

Now, let's create cron jobs to schedule regular backups. Use the crontab -e command to edit your cron jobs:

1crontab -e

Add the following lines to create two example cron jobs. These examples schedule daily and weekly backups:

1# Daily backup at midnight (00:00)
20 0 * * * /path/to/backup.sh
3
4# Weekly backup on Sunday at 2:00 AM
50 2 * * 0 /path/to/backup.sh

Replace /path/to/backup.sh with the actual path to your backup script.

Step 5: Restore MySQL Backup

To restore a MySQL backup, navigate to the folder where your backup is stored and use the following command:

1mysql -u root -p mydb < mydb-YYYYMMDDHHMMSS.sql

Replace mydb-YYYYMMDDHHMMSS.sql with the actual filename of your backup. You will be prompted for the MySQL password.

Step 6: Summary

You have successfully set up automated MySQL backups using a script and scheduled them with cron jobs. Remember to customize the script with your database names, paths, and credentials. Automated backups help protect your data and ensure you can quickly restore it when needed.

Conclusion

Regular automated backups are crucial for data protection and disaster recovery. This tutorial enables you to create and schedule MySQL backups with ease. Adjust the cron job schedules according to your backup frequency requirements.