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

inchirags@gmail.com 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

*****************************************************************************************

https://www.youtube.com/watch?v=3YBXNxtmVi8

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

2. 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

3. 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 dept@192.168.224.129:/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

4. 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.* dept@192.168.224.129:/home/dept/mysql-bin-logs/

5. 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

6. 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

7. 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.