Point-in-Time Recovery in MySQL (PITR)

One of the major tasks in database administration (DBA) is data recovery. Let’s consider a scenario: We take a full backup every day at 10 PM, either manually or through a cron job. However, after 10 PM, due to a mistake, I deleted some rows from a table or even the entire table. How can we recover those deleted rows or tables?

Step 1. Check if the binary log is enabled or not. If your binary log is not enabled, it means that recovering your data will be difficult. By default, MySQL enables the binary log.

show variables like 'log_bin';

The value ‘ON’ means the binary log is enabled. You can check the binary log format.

show variables like "%binlog_format%";

show master status;

You can check to open the binary log file, log position, and date. The path to my binary log file is /var/lib/mysql/SHIVADBA-bin.000115.

First of all, create an SQL file using the mysqlbinlog command. Enter your start date and time, end date and time, your binary log file position, and the desired name for your SQL file when prompted.

mysqlbinlog SHIVADBA-bin.000115;

mysqlbinlog --start-datetime="2023-09-22 07:00:00" --stop-datetime="2023-09-22 09:30:00" binlog.000115 > backup_binlog.sql;

Next, store your full backup SQL, and then store your newly created SQL file, similar to how you import other SQL files.

mysql -u root -p your_database_name < backup_binlog.sql;

Thank you