{"id":2690,"date":"2024-11-04T04:02:36","date_gmt":"2024-11-04T04:02:36","guid":{"rendered":"https:\/\/bynatree.com\/?p=2690"},"modified":"2024-11-04T04:02:36","modified_gmt":"2024-11-04T04:02:36","slug":"protect-your-data-with-point-in-time-recovery-pitr-in-mysql","status":"publish","type":"post","link":"https:\/\/divaind.com\/ie1\/2024\/11\/04\/protect-your-data-with-point-in-time-recovery-pitr-in-mysql\/","title":{"rendered":"Protect Your Data with Point-In-Time Recovery (PITR) in MySQL"},"content":{"rendered":"

Point-in-time recovery (PITR)<\/b> helps to recover data from unintentional deletions and modifications.<\/span><\/p><\/blockquote>\n

Ways to recover the data<\/b><\/p>\n

    \n
  1. Recover the complete database, backup or export it using a previous timestamp and then restore or import it into a new database. This is commonly used to recover from data corruption issues as we need to restore the database to a point in time before the damage happens.<\/span><\/li>\n
  2. Restore a portion of the database, run an old read with a query-condition and timestamp from the past, then move the results back into the active database. This is commonly used in operation on an active database. Like this, if we mistakenly remove a row or wrongly alter a portion of data, we may restore it using this approach.<\/li>\n<\/ol>\n

    In this blog I will focus on second type, here is an example to recover the table from unintentional delete of a record.<\/span><\/p>\n

    For that I have created table customers and inserted some records checks status of the master to identify binlog position before deleting records.
    \n<\/span><\/p>\n

    CREATE TABLE customers (\n\u00a0 \u00a0 customer_id INT AUTO_INCREMENT PRIMARY KEY,\n\u00a0 \u00a0 name VARCHAR(100) NOT NULL,\n\u00a0 \u00a0 email VARCHAR(100) NOT NULL UNIQUE,\n\u00a0 \u00a0 created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP\n);\n\n-- Insert into customers\nINSERT INTO customers (name, email) VALUES ('John Doe', 'johndoe@example.com');\nINSERT INTO customers (name, email) VALUES ('Jane Smith', 'janesmith@example.com');\n\nmysql> select * from customers;\n+-------------+------------+-----------------------+---------------------+\n| customer_id | name \u00a0 \u00a0 \u00a0 | email \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | created_at\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n+-------------+------------+-----------------------+---------------------+\n| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1 | John Doe \u00a0 | johndoe@example.com \u00a0 | 2024-10-18 12:13:28 |\n| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 2 | Jane Smith | janesmith@example.com | 2024-10-18 12:13:29 |\n+-------------+------------+-----------------------+---------------------+\n2 rows in set (0.00 sec)\n\n\u00a0mysql> show master status;\n+---------------+----------+--------------+------------------+-------------------+\n| File\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |\n+---------------+----------+--------------+------------------+-------------------+\n| binlog.000026 | \u00a0 \u00a0 1194 |\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n+---------------+----------+--------------+------------------+-------------------+\n\nmysql> DELETE FROM customers WHERE customer_id = 1;\nQuery OK, 1 row affected (0.01 sec\n\nmysql> select * from customers;\n+-------------+------------+-----------------------+---------------------+\n| customer_id | name \u00a0 \u00a0 \u00a0 | email \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | created_at\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n+-------------+------------+-----------------------+---------------------+\n| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 2 | Jane Smith | janesmith@example.com | 2024-10-18 12:13:29 |\n+-------------+------------+-----------------------+---------------------+\n1 row in set (0.00 sec)<\/pre>\n
    Steps for Point-in-time Recovery<\/span><\/h3>\n
    Pre-requisites for point in time recovery
    \n<\/span><\/h4>\n
    Edit the MySQL configuration file (my.cnf) to enable binary logging and set up a server ID. server-id is necessary for replication, and binary logging must be enabled to record the changes needed for PITR.<\/span><\/p>\n
    # include server-id files from the config directory log_bin_trust_function_creators = ON \nlocal_infile = ON \ninnodb_buffer_pool_size = 2147483648  \nbind-address =192.168.42.128 \nserver-id = 1 \nlog_bin = ON<\/pre>\n
    Restart the MySQL service to apply the changes
    \n<\/span><\/h4>\n
    [root@Master etc]# systemctl stop mysqld.service\n[root@Master etc]# systemctl start mysqld.service\n[root@Master etc]# systemctl status mysqld.service\n\u25cf mysqld.service - MySQL 8.0 database server\n\u00a0 Loaded: loaded (\/usr\/lib\/systemd\/system\/mysqld.service; enabled; vendor preset: disabled)\n\u00a0 Active: active (running) since Fri 2024-10-18 11:48:11 EDT; 17min ago\n\u00a0 Process: 1948 ExecStartPost=\/usr\/libexec\/mysql-check-upgrade (code=exited, status=0\/SUCCESS)\n\u00a0 Process: 1006 ExecStartPre=\/usr\/libexec\/mysql-prepare-db-dir mysqld.service (code=exited, status=0\/SUCCESS)\n\u00a0 Process: 954 ExecStartPre=\/usr\/libexec\/mysql-check-socket (code=exited, status=0\/SUCCESS)\nMain PID: 1064 (mysqld)\n\u00a0 Status: \"Server is operational\"\n\u00a0 \u00a0 Tasks: 38 (limit: 10936)\n\u00a0 Memory: 492.9M\n\u00a0 CGroup: \/system.slice\/mysqld.service\n\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u2514\u25001064 \/usr\/libexec\/mysqld --basedir=\/usr\n\nOct 18 11:47:57 Master systemd[1]: Starting MySQL 8.0 database server...\nOct 18 11:48:11 Master systemd[1]: Started MySQL 8.0 database server.\n<\/pre>\n
    Verify the parameters<\/p>\n
    mysql> show master status; \n+---------------+----------+--------------+------------------+-------------------+ \n| File\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | \n+---------------+----------+--------------+------------------+-------------------+ \n| binlog.000026 | \u00a0 \u00a0 1526 |\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | \n+---------------+----------+--------------+------------------+-------------------+ \n\nmysql> SHOW VARIABLES LIKE 'log_bin'; \n+---------------+-------+ \n| Variable_name | Value | \n+---------------+-------+ \n| log_bin \u00a0 \u00a0 \u00a0 | ON\u00a0 \u00a0 | \n+---------------+-------+ \n1 row in set (0.34 sec) \n\nmysql> SHOW VARIABLES LIKE 'binlog_format'; \n+---------------+-------+ \n| Variable_name | Value | \n+---------------+-------+ \n| binlog_format | ROW \u00a0 | \n+---------------+-------+ \n1 row in set (0.01 sec)\n<\/pre>\n
    Take a Backup of the Database Using the mysqldump command for creating a logical backup of the database
    \n<\/span><\/h4>\n
    [root@Master centos]# mysqldump -u root -p --all-databases > \/home\/centos\/backup.sql<\/pre>\n
    -h specifies the MySQL host.<\/span><\/p>\n
    -u specifies the MySQL user.<\/span><\/p>\n
    -p prompts for the password.<\/span><\/p>\n
    Identify Binary Log Files<\/span><\/h4>\n
    Listing the binary log files to identify which logs need to be used for recovery. Checking on a path \/var\/lib\/mysql\/
    \n<\/span><\/p>\n
    [root@Master mysql]# cat \/var\/lib\/mysql\/binlog.0000*<\/pre>\n
    Analyse the Binary Log in human-readable format using mysqlbinlog, this command shows the contents of the binary log, including SQL statements logged during transactions.
    \n<\/span><\/p>\n
    root@Master mysql]# sudo mysqlbinlog \/var\/lib\/mysql\/binlog.000026<\/pre>\n
    Recover Data <\/span><\/h4>\n
    Using Binary Logs to recover up to a specific point, using the mysqlbinlog command with specific positions<\/span><\/p>\n
    –start-position and –stop-position define the range of the binary log that we want to use.<\/span><\/p>\n
    –skip-gtids skips GTIDs (Global Transaction IDs) if this is enabled in the configuration.<\/span><\/p>\n
    [root@Master mysql]# mysqlbinlog \/var\/lib\/mysql\/binlog.0000* --start-position=235 --stop-position=1194 --skip-gtids > \/home\/centos\/pitr.sql<\/pre>\n
    Apply the Recovered SQL Statements<\/span><\/h4>\n
    Once we have extracted the SQL statements, apply them in our MySQL database<\/span><\/p>\n
    For Restoring the SQL transactions between the positions defined in the binary log, effectively recovering the database to the desired point in time.
    \n<\/span><\/p>\n
    [root@Master mysql]# mysql -u root -p testdb < \/home\/centos\/pitr.sql\n\n[root@Master mysql]# mysql -u root -p\nEnter password:\nWelcome to the MySQL monitor.\u00a0 Commands end with ; or g.\nYour MySQL connection id is 25\nServer version: 8.0.26 Source distribution\n\nCopyright (c) 2000, 2021, Oracle and\/or its affiliates.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or 'h' for help. Type 'c' to clear the current input statement.\n\nmysql> use testdb;\nReading table information for completion of table and column names\nYou can turn off this feature to get a quicker startup with -A\n\nDatabase changed\nmysql> select * from customers;\n+-------------+------------+-----------------------+---------------------+\n| customer_id | name \u00a0 \u00a0 \u00a0 | email \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | created_at\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |\n+-------------+------------+-----------------------+---------------------+\n| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 1 | John Doe \u00a0 | johndoe@example.com \u00a0 | 2024-10-18 12:13:28 |\n| \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 2 | Jane Smith | janesmith@example.com | 2024-10-18 12:13:29 |\n+-------------+------------+-----------------------+---------------------+\n2 rows in set (0.00 sec)<\/pre>\n
    Conclusion:<\/span><\/h3>\n
    By following these steps, we can restore the MySQL database to a specific point in time using binary logs. This is useful in scenarios where we want to undo unintended operations or recover from a crash without losing all the transactions after the last backup.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"
    Point-in-time recovery (PITR) helps to recover data from unintentional deletions and modifications. Ways to recover the data Recover the complete database, backup or export it using a previous timestamp and then restore or import it into a new database. This is commonly used to recover from data corruption issues as we need to restore the…<\/p>\n","protected":false},"author":1,"featured_media":2692,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[24],"tags":[117,118,213,214,259],"class_list":["post-2690","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-mysql","tag-database-pitr-techniques","tag-database-recovery-mysql","tag-mysql-pitr-guide","tag-mysql-pitr-tutorial","tag-point-in-time-recovery-mysql","category-24","description-off"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2690","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/comments?post=2690"}],"version-history":[{"count":0,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/posts\/2690\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media\/2692"}],"wp:attachment":[{"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/media?parent=2690"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/categories?post=2690"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/divaind.com\/ie1\/wp-json\/wp\/v2\/tags?post=2690"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}