Automate Database Backups with Shell Scripts

In this guide, you will learn how to automate database backups using shell scripts, compress the backups, and securely store them on AWS S3. Automating backups is an essential practice for any database administrator or developer to ensure data safety and disaster recovery. This project will guide you through setting up a reliable backup process for a MySQL database.

You will start by preparing the environment and writing a shell script to perform a database dump. Next, you'll compress the backup file to save storage space and upload it to AWS S3 for secure cloud storage. Once the script is complete, you will schedule it to run automatically using cron jobs, ensuring backups are performed consistently. To optimize storage usage, you'll also add a cleanup step to remove old backups that are no longer needed.

Setting Up the Environment and Database

Before automating the database backups, it is important to ensure your environment is properly set up, the necessary tools are installed, and a sample database is available for testing. This part will guide you through installing MySQL, creating a sample database, setting up a backup directory, and preparing your system for the automation process.

Install MySQL Server

If MySQL is not already installed on your system, you need to install it. MySQL is a widely-used open-source relational database management system that is perfect for learning and practicing database backups. Follow these steps based on your operating system:

On Ubuntu/Debian:
Update your package list and install the MySQL server.

sudo apt update  
sudo apt install mysql-server  

Once installed, run the MySQL security script to set up a secure installation:

sudo mysql_secure_installation  

This will prompt you to set the root password, remove insecure default settings, and tighten up access permissions. Follow the prompts for a secure configuration.

On RHEL/CentOS:
Install MySQL using yum and start the service:

sudo yum install mysql-server  
sudo systemctl start mysqld  
sudo systemctl enable mysqld  

After installation, confirm MySQL is running by checking its status:

sudo systemctl status mysql

Set Up a Sample Database

To test the backup automation, create a sample database and a user with limited privileges to access it. Log into the MySQL command-line client as the root user:

mysql -u root -p  

Enter the MySQL root password you set during installation. Create a new database called backup_demo:

CREATE DATABASE backup_demo; 

Create a user specifically for performing backups. Assign appropriate privileges to this user:

CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'password123';  
GRANT ALL PRIVILEGES ON backup_demo.* TO 'backup_user'@'localhost';  
FLUSH PRIVILEGES;  
EXIT;  

Verify the new user can access the database:

mysql -u backup_user -p'password123' -e "SHOW DATABASES;"  

The backup_demo database should appear in the list of databases.

Add Sample Data to the Database

To simulate a real database backup, add some sample tables and data to the backup_demo database. Log in as the backup user:

mysql -u backup_user -p'password123' backup_demo  

Run the following SQL commands to create a table and insert data:

CREATE TABLE employees (  
    id INT AUTO_INCREMENT PRIMARY KEY,  
    name VARCHAR(100) NOT NULL,  
    position VARCHAR(100),  
    hire_date DATE  
);  

INSERT INTO employees (name, position, hire_date) VALUES  
('John Doe', 'Software Engineer', '2024-01-15'),  
('Jane Smith', 'Data Analyst', '2023-11-12'),  
('Alice Johnson', 'Project Manager', '2022-08-30'); 

Exit MySQL and confirm the data has been added:

mysql -u backup_user -p'password123' -e "SELECT * FROM employees;" backup_demo  

Step 4: Prepare a Directory for Scripts and Backups

Organize your workspace by creating a dedicated directory to store your shell script and backups:

Create a directory in your home folder:

mkdir -p ~/db_backups/scripts  

Verify the structure:

ls -R ~/db_backups 

At this point, your environment is ready for the next steps. You have MySQL installed, a sample database with data, and a dedicated directory for scripts and backups. Next part, you will write a shell script to back up this database and compress the backup file to save space.