This guide will walk you through the process of installing Percona XtraBackup and automate MySQL 8 backups using Percona XtraBackup using cron jobs.
Percona XtraBackup is an open-source tool that provides hot backups for MySQL-based servers, that keeps your database fully available during planned maintenance windows. It’s highly efficient for taking consistent backups of your MySQL 8 databases, especially in production environments.
Percona XtraBackup (PXB) is a 100% open source backup solution with commercial support available for organizations who want to benefit from comprehensive, responsive, and cost-flexible database support for MySQL.
Automate MySQL 8 Backups Using Percona XtraBackup
Prerequisites
- A Ubuntu 24.04 installed dedicated server or KVM VPS running MySQL 8.
- Root or sudo access to the server.
- Adequate storage for backups.
- Basic knowledge of command-line operations.
Step 1: Install Percona XtraBackup
Add the Percona Repository
sudo apt-get install wget gnupg2 -y
wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
To enable the Percona Distribution for MySQL 8.0 repository use:
percona-release setup pdps8.0
Install Percona XtraBackup
sudo apt-get install percona-xtrabackup-80 -y
Verify Installation
Ensure that Percona XtraBackup is installed correctly by checking its version:
xtrabackup --version
Output:
xtrabackup version 8.0.35-31 based on MySQL server 8.0.35 Linux (x86_64) (revision id: 55ec21d7)
Step 2: Create a Backup Directory
Create a directory where your backups will be stored:
sudo mkdir -p /var/backups/mysql
sudo chown -R mysql:mysql /var/backups/mysql
Step 3: Creating a Backup User in MySQL
For security reasons, it's recommended to create a dedicated MySQL user specifically for backups rather than using the root user.
Log into MySQL as the Root User
sudo mysql -u root -p
Create the Backup User
Replace backup_user with your desired username and strong_password with a strong, unique password.
CREATE USER 'backup_user'@'localhost' IDENTIFIED BY 'strong_password';
Grant the Necessary Privileges
The backup user needs specific privileges to perform backups. Grant these privileges with the following command:
GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT, SHOW DATABASES, SHOW VIEW, EVENT, TRIGGER, SELECT, BACKUP_ADMIN ON *.* TO 'backup_user'@'localhost';
Apply the changes:
FLUSH PRIVILEGES;
Exit MySQL
EXIT;
Step 4: Perform a Full Backup
Before automating, test a manual backup to ensure everything is set up correctly.
xtrabackup --backup --target-dir=/var/backups/mysql/full --user=<mysql_user> --password=<mysql_password>
Replace <mysql_user> and <mysql_password> with your MySQL credentials.
Step 5: Automate Backups with Cron
To automate the backup process, you can use cron jobs. The following steps will schedule daily full backups.
Create a script to handle the backup process:
sudo nano /usr/local/bin/mysql_backup.sh
Add the following content to the script:
#!/bin/bash
BACKUP_DIR="/var/backups/mysql/full/$(date +%F)"
MYSQL_USER="<mysql_user>"
MYSQL_PASSWORD="<mysql_password>"
# Create backup directory
mkdir -p $BACKUP_DIR
# Run backup
xtrabackup --backup --target-dir=$BACKUP_DIR --user=$MYSQL_USER --password=$MYSQL_PASSWORD
# Compress the backup
tar -czf $BACKUP_DIR.tar.gz -C $BACKUP_DIR .
# Remove uncompressed files
rm -rf $BACKUP_DIR
# Optional: Remove old backups (older than 7 days)
find /var/backups/mysql/ -type f -name "*.tar.gz" -mtime +7 -exec rm {} \;
Replace <mysql_user> and <mysql_password> with your MySQL credentials.
Save and close the file.
Make the Script Executable
sudo chmod +x /usr/local/bin/mysql_backup.sh
Schedule the Cron Job. Open the cron tab:
sudo crontab -e
Add the following line to schedule the backup at 2 AM daily:
0 2 * * * /usr/local/bin/mysql_backup.sh >> /var/log/mysql_backup.log 2>&1
This will redirect any output or errors to /var/log/mysql_backup.log.
Step 6: Verify Automation
After the scheduled time, check the backup directory and logs to ensure that the backup process is working correctly:
ls /var/backups/mysql/
You should see compressed backup files with a date-stamped filename.
Conclusion
We’ve successfully seen automate MySQL 8 backups using Percona XtraBackup using cron jobs. This setup ensures that your database backups are performed regularly and without manual intervention, keeping your data safe and recoverable in case of any failures.
