MySQL Point-in-Time Recovery (PITR) Using Percona XtraBackup and Binary Logs from a Full Backup Across Two Linux Servers

[email protected] Chirag's MySQL Tutorial https://www.chirags.in

MySQL Point-in-Time Recovery (PITR) Using Percona XtraBackup and Binary Logs from a Full Backup Across Two Linux Servers

What is Percona XtraBackup?

Percona XtraBackup is an open-source, free tool used for backing up MySQL and MariaDB databases, particularly those using the InnoDB and XtraDB storage engines. It allows for non-blocking, "hot" backups, meaning that the database can remain operational and process transactions while the backup is running. It offers various backup types, including full, incremental, compressed, and streaming backups

Step-by-step guide to set up MySQL Point-in-Time Recovery (PITR) Using Percona XtraBackup and Binary Logs from a Full Backup Across Two Linux Servers:

Environment

Role IP Address Description

Primary 192.168.224.128 Main MySQL Server

Recovery 192.168.224.129 Server for PITR

MySQL Version: Latest (assume 8.x)

MySQL User: root
Password: admin@123

1. Install MySQL on Both Servers (192.168.224.128, 192.168.224.129)

sudo apt update
sudo apt install mysql-server -y
sudo systemctl enable mysql
sudo systemctl start mysql
Set the root password:

sudo mysql_secure_installation
Password Authentication:

Run the following to change the plugin for root to mysql_native_password:

sudo mysql
Then in the MySQL prompt:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'admin@123';
FLUSH PRIVILEGES;
EXIT;
Now test login with:

mysql -u root -p
You should now be prompted for the password admin@123.

Optional: Disable auth_socket Plugin Permanently (Ubuntu Default Behavior)

To ensure this change sticks and works after reboots or package updates, make sure:

sudo systemctl restart mysql
Then confirm:

mysql -u root -p -e "SELECT user, plugin FROM mysql.user WHERE user='root';"
Result should be:

root mysql_native_password

1. Configure MySQL on Primary (192.168.224.128) for Backups and Binary Logging

Edit /etc/mysql/mysql.conf.d/mysqld.cnf:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
[mysqld]
server-id = 1
log-bin = /var/log/mysql/mysql-bin.log
binlog_format = ROW
expire_logs_days = 7
Restart MySQL:

sudo systemctl restart mysql

1. Take Physical Backup on Primary (192.168.224.128) (Using xtrabackup)

Create the Database and a Table:

`mysql -u root -p
CREATE DATABASE testdb;
USE testdb;

CREATE TABLE employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
position VARCHAR(50),
salary DECIMAL(10,2)
);
INSERT INTO employees (name, position, salary) VALUES
('Chirag', 'Manager', 75000.00),
('Sanju', 'Engineer', 65000.00),
('Kumar', 'Technician', 45000.00);`
Verify the Data:

SELECT * FROM employees;
exit;

Database Full Using xtrabackup (recommended for large DBs)

Install percona-xtrabackup 8.0:

Download and Install the Percona APT Repo Package

curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
chmod 0777 -R percona-release_latest.generic_all.deb
sudo apt install gnupg2 lsb-release ./percona-release_latest.generic_all.deb
sudo apt update
sudo percona-release setup pxb-80
Update the Package Index

sudo apt update
sudo apt install percona-xtrabackup-80
Backup:

xtrabackup --backup --target-dir=/backup --user=root --password=admin@123
Prepare:

xtrabackup --prepare --target-dir=/backup
Transfer:

rsync -av /backup [email protected]:/home/dept/
Add some more data:

mysql -u root -p
USE testdb;

INSERT INTO employees (name, position, salary) VALUES
('Arun', 'Developer', 55000.00),
('Eva', 'Designer', 48000.00);
Verify the Data:

SELECT * FROM employees;
exit

1. Copy Binary Logs from Primary Server to Recovery Server

List binlogs:

ls /var/log/mysql/mysql-bin.*
Copy the required binary logs to Recovery:

scp /var/log/mysql/mysql-bin.* [email protected]:/home/dept/mysql-bin-logs/

1. Setup Recovery Server

Install percona-xtrabackup 8.0:

Download and Install the Percona APT Repo Package

curl -O https://repo.percona.com/apt/percona-release_latest.generic_all.deb
chmod 0777 -R percona-release_latest.generic_all.deb
sudo apt install gnupg2 lsb-release ./percona-release_latest.generic_all.deb
sudo apt update
sudo percona-release setup pxb-80
Update the Package Index

sudo apt update
sudo apt install percona-xtrabackup-80
Stop MySQL:

sudo systemctl stop mysql
Restore Physical Backup using xtrabackup:

sudo rm -rf /var/lib/mysql/*
Move the files to /var/log/mysql with sudo:

sudo mv /home/dept/mysql-bin-logs/mysql-bin.* /var/log/mysql/
sudo chown mysql:mysql /var/log/mysql/mysql-bin.*
sudo xtrabackup --copy-back --target-dir=/home/dept/backup --datadir=/var/lib/mysql
chown -R mysql:mysql /var/lib/mysql
sudo systemctl start mysql

1. Apply Binary Logs for PITR

Find the exact timestamp or position

Open full_backup.sql and locate:

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=157;

mysqlbinlog --start-position=157 /var/log/mysql/mysql-bin.000002 | mysql -u root -p
Or to apply to a specific point in time:

mysqlbinlog --stop-datetime="2025-05-30 14:00:00" /var/log/mysql/mysql-bin.000002 | mysql -u root -p

You can combine multiple binlogs:

mysqlbinlog mysql-bin.000001 mysql-bin.000002 | mysql -u root -p

1. Verification

   Check tables and data

   Compare with Primary

   Confirm PITR state

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

Note : Flow the Process shown in video 📽️.

😉Please Subscribe for more videos:

https://www.youtube.com/@chiragstutorial

💛Don't forget to, 💘Follow, 💝Like, Share 💙&, Comment

Thanks & Regards,

Chitt Ranjan Mahto "Chirag"
https://www.chirags.in

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

Link will be available in description.