How do you use point-in-time recovery with an example?

Point-in-time recovery (PITR) is a technique used in MySQL to restore the database to a specific moment in time. This is particularly useful in scenarios where you need to recover from accidental data loss or corruption. The process involves taking a base backup and then applying the binary logs to reach the desired point in time.

Example of Point-in-Time Recovery

Here’s a step-by-step example demonstrating how to perform point-in-time recovery in MySQL.

-- Step 1: Take a Full Backup
mysqldump -u root -p --all-databases --single-transaction > full_backup.sql

-- Step 2: Enable Binary Logging
SET GLOBAL log_bin = 'mysql-bin';
SET GLOBAL expire_logs_days = 7;  -- Specify the retention period for binary logs

-- Insert and modify some data; make some changes that may need recovery

-- Step 3: Backup the Binary Logs
-- Binary logs will automatically be saved in the directory specified in my.cnf

-- Step 4: Check the Binary Log Files
SHOW BINARY LOGS;

-- Step 5: Use the Full Backup and Apply Binary Logs
mysql -u root -p < full_backup.sql

-- Note: To apply binary logs, you can use the following command
mysqlbinlog mysql-bin.000001 --start-datetime="2023-10-25 12:00:00" --stop-datetime="2023-10-25 12:30:00" | mysql -u root -p

After running these commands, your database will be restored to the state it was in at the specified time. Make sure to adjust the datetimes based on your needs.