Schedule an Auto-backup script of MariaDB all Database in Single File in Ubuntu 24.07 LTS

Here’s a step-by-step guide to schedule an auto-backup script for MariaDB on Ubuntu 24.07 LTS using cron:

Part 1: Install MariaDB on Ubuntu 24.04 LTS:

Step 1: Firewall Configuration (Optional)

sudo ufw status
sudo ufw allow ssh
sudo ufw allow 3306/tcp

sudo ufw enable

Step 2: Update System Packages
First, ensure your system is up to date.

sudo apt update
sudo apt upgrade -y

Step 3: Install MariaDB Server
Install the MariaDB server package.

sudo apt install mariadb-server -y

Verify the installation:

mariadb --version

Step 4: Secure MariaDB Installation
Run the security script to improve the security of your MariaDB installation.

sudo mysql_secure_installation

Set root password? (If not set already, set a strong password)
Remove anonymous users? (Yes)
Disallow root login remotely? (Yes)
Remove test database and access to it? (Yes)
Reload privilege tables now? (Yes)

Step 5: Enable and Start MariaDB Service
Ensure MariaDB is enabled and running.

sudo systemctl enable mariadb
sudo systemctl start mariadb

Step 6: Set root password.

mysql -u root -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'admin@123';
FLUSH PRIVILEGES;

Step 7: Create test database, table and insert some data for testing purpose.

mysql -u root -p

PASSWORD : admin@123

CREATE DATABASE test_db;

USE test_db;

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50),
    salary DECIMAL(10,2),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO employees (name, department, salary) VALUES
('Harish Kumar', 'HR', 50000.00),
('Purab Mahto', 'IT', 60000.00),
('Sanju Mehta', 'Finance', 55000.00),
('Dhruv', 'Marketing', 52000.00),
('Chirag Mahto', 'IT', 62000.00);

SELECT * FROM employees;

// second database and table

CREATE DATABASE test_db2;

USE test_db2;

CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(100) UNIQUE,
    city VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO customers (name, email, city) VALUES
('John Ram', 'john.ram@chirags.in', 'Mumbai'),
('Chirag Mahto', 'chirag.mahto@chirags.in', 'Delhi'),
('Arun Rahul', 'arun.rahul@chirags.in', 'Srinagar'),
('Dhruv Sanchit', 'dhruv.sanchit@chirags.in', 'Bangaluru'),
('Rahul Raj', 'rahul.raj@chirags.in', 'Chennai');

SELECT * FROM customers;

exit

---

Part 2: Backup Configuration.

Here’s a step-by-step guide to schedule a backup script for MariaDB on Ubuntu 24.04 LTS using cron.

1. Create a Backup Directory
   Create a directory to store backups:

sudo mkdir -p /var/backups/mariadb
sudo chown -R mysql:mysql /var/backups/mariadb
sudo chmod 700 /var/backups/mariadb

1. Create the Backup Script
   Create a script to perform the backup:

sudo nano /usr/local/bin/mariadb_backup.sh

Add the following content:

#!/bin/bash

# Variables
BACKUP_DIR="/var/backups/mariadb"
DATE=$(date +'%Y-%m-%d_%H-%M-%S')
BACKUP_FILE="$BACKUP_DIR/mariadb_backup_$DATE.sql"
USER="root"
PASSWORD="admin@123"

# Backup command
mysqldump -u $USER -p$PASSWORD --all-databases > $BACKUP_FILE

# Compress the backup
gzip $BACKUP_FILE

# Remove backups older than 7 days
find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +7 -exec rm {} \;

Replace your_password with your MariaDB root password.
Adjust -mtime +7 if you want to keep backups for more or fewer days.
Save and close the file.

3. Make the Script Executable

sudo chmod +x /usr/local/bin/mariadb_backup.sh

3. Schedule the Backup Script with Cron
   Open the cron editor:

sudo crontab -e

Add the following line to schedule a daily backup at 2:00 AM:

0 2 * * * /usr/local/bin/mariadb_backup.sh >> /var/log/mariadb_backup.log 2>&1

0 2 * * *: Run daily at 2:00 AM.

/var/log/mariadb_backup.log 2>&1: Log output and errors.
Save and close the file.

5. Restart and Verify Cron Job
   Restart cron job:

/etc/init.d/cron restart

Check if the cron job is scheduled:

sudo crontab -l

Ensure your script is listed.

6. Test the Backup Script Manually
   Run the script manually to verify it works:

sudo /usr/local/bin/mariadb_backup.sh

Check the backup directory:

ls -l /var/backups/mariadb

Now, you can unzip with “gunzip db_name.sql.gz” and check.

8. Verify Logs
   Check the log file for any errors:

sudo cat /var/log/mariadb_backup.log

Your MariaDB backup script is now scheduled and will run daily at 2:00 AM.

For any doubts and query, please write on YouTube video comments section.

Note : Flow the Process shown in video.

😉Please, Subscribe and like for more videos:
https://www.youtube.com/@chiragstutorial
💛Don’t forget to, 💘Follow, 💝Like, 💖Share 💙&, Comment

Thanks & Regards,
Chitt Ranjan Mahto “Chirag”

---

Note: All scripts used in this demo will be available in our website.
        Link will be available in description.