All Categories
Database Backup
Hot Backups in MySQL Using Percona XtraBackup

Hot Backups in MySQL Using Percona XtraBackup

Perform hot backups of your MySQL databases using Percona XtraBackup without affecting your database's availability.

Introduction

In this guide, we will go through the process of creating hot backups of a MySQL database using Percona XtraBackup. Hot backups are beneficial in environments where database availability is critical, and for large-scale, high-transaction systems.

Prerequisites

  • A running MySQL server with data you want to back up.
  • Percona XtraBackup installed on the server. Installation instructions can be found on the Percona website.
  • Permissions to run the xtrabackup command on your server and access the MySQL server.
  • A .my.cnf file containing your MySQL login credentials. This file should be saved in a secure location and have restrictive permissions (readable only by the user running the script).

Step-by-Step Guide

 

Step 1: Create your MySQL configuration file, .my.cnf:

This file will include your MySQL user and password. Create the .my.cnf file in your home directory and update the user and password fields as needed.

[client]
user=root
password=yourpassword
 

To protect this file from being accessed by other users, modify its permissions so that only your user can read it:

chmod 600 ~/.my.cnf
 

Step 2: Create the backup script:

Now we are going to create a bash script to perform the backup. You can create a new file named backup_script.sh and add the following code:

#!/bin/bash

BACKUP_DIR=/mnt/hot-backups
DATABASE=mydatabase
CONFIG=~/.my.cnf

# Create backup directory if it does not exist
mkdir -p ${BACKUP_DIR}

# Start backup with Percona XtraBackup
xtrabackup --backup --defaults-file=${CONFIG} --databases=${DATABASE} --target-dir=${BACKUP_DIR}/${DATABASE}_$(date +%Y%m%d%H%M%S)

# Check the exit status of the xtrabackup command and exit with it
exit_status=$?
if [ ${exit_status} -eq 0 ]; then
  echo "Backup for ${DATABASE} is completed."
else
  echo "Backup for ${DATABASE} failed with exit code ${exit_status}."
fi

exit ${exit_status}

Replace mydatabase with the name of your MySQL database. If you stored the .my.cnf file in another location, replace ~/.my.cnf with the full path to your file.

 

Step 3: Grant execute permissions to the script:

You can make the script executable by running the following command:

chmod +x backup_script.sh
 

Step 4: Run the backup script:

You can now run your script:

./backup_script.sh

Conclusion

You have successfully created a hot backup of a MySQL database using Percona XtraBackup.

Remember, it's crucial to regularly verify your backups and ensure they can be restored correctly.

Did this answer your question?
😞
😐
🤩

Last updated on August 4, 2023

FAQ Restore PlanetScale / Vitess MySQL Backups