{"id":13454,"date":"2023-12-25T07:00:56","date_gmt":"2023-12-25T14:00:56","guid":{"rendered":"https:\/\/www.mysqltutorial.org\/?page_id=13454"},"modified":"2023-12-25T07:01:30","modified_gmt":"2023-12-25T14:01:30","slug":"mysql-point-in-time-recovery","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-point-in-time-recovery\/","title":{"rendered":"MySQL Point-in-time Recovery"},"content":{"rendered":"\n<p><strong>Summary<\/strong>: in this tutorial, you will learn about the MySQL Point-in-time recovery concept and how to restore a database to a specific time in the past.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Introduction to MySQL point-in-time recovery<\/h2>\n\n\n\n<p>The point-in-time recovery allows you to restore a MySQL database to a specific time in the past. The point-in-time recovery relies on two key components:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Full backup: This serves as the foundation for recovery, providing the starting state of the database.<\/li>\n\n\n\n<li>Binary logs: These binary log files record all changes made to the database, allowing you to replay those changes to a desired point.<\/li>\n<\/ul>\n\n\n\n<p>If you don&#8217;t have a full backup or binary logs enabled, you cannot carry the point-in-time recovery.<\/p>\n\n\n\n<p>We&#8217;ll illustrate how to perform the point-in-time recovery in MySQL to recover a database to a specific point in time.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Checking binary log status and taking a full backup of a database<\/h2>\n\n\n\n<p>First, open Command Prompt on Windows or Terminal on Unix-like systems.<\/p>\n\n\n\n<p>Next, <a href=\"https:\/\/www.mysqltutorial.org\/getting-started-with-mysql\/connect-to-mysql-server\/\">connect to the MySQL Server<\/a>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-1\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">mysql -u root -p<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-1\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Then, check if the binary log is enabled by showing the value of the <code>log_bin<\/code> variable:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-2\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">show<\/span> <span class=\"hljs-keyword\">global<\/span> <span class=\"hljs-keyword\">variables<\/span> <span class=\"hljs-keyword\">like<\/span> <span class=\"hljs-string\">'log_bin'<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-2\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-3\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">\n+<span class=\"hljs-comment\">---------------+-------+<\/span>\n| Variable_name | Value |\n+<span class=\"hljs-comment\">---------------+-------+<\/span>\n| log_bin       | ON    |\n+<span class=\"hljs-comment\">---------------+-------+<\/span>\n1 row in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.01<\/span> sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-3\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output indicates that the binary log is currently enabled. If it is not, you need to enable the binary log.<\/p>\n\n\n\n<p>After that, exit the mysql program:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-4\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">exit<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-4\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Creating a new sample database &amp; taking a full backup<\/h2>\n\n\n\n<p>First, reconnect to the MySQL server database:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">mysql -u root -p<\/code><\/span><\/pre>\n\n\n<p>Second, <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-create-database\/\">create a new database<\/a> called <code>mydb<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">CREATE DATABASE mydb;<\/code><\/span><\/pre>\n\n\n<p>Third, change the current database to <code>mydb<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-5\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\"><span class=\"hljs-keyword\">USE<\/span> <span class=\"hljs-title\">mydb<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-5\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Fourth, <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-create-table\/\">create a new table<\/a> called <code>contacts<\/code> in the <code>mydb<\/code> database:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-6\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\">CREATE TABLE <span class=\"hljs-keyword\">IF<\/span> NOT EXISTS contacts (\r\n    id INT AUTO_INCREMENT PRIMARY KEY,\r\n    first_name VARCHAR(<span class=\"hljs-number\">255<\/span>) NOT <span class=\"hljs-keyword\">NULL<\/span>,\r\n    last_name VARCHAR(<span class=\"hljs-number\">255<\/span>) NOT <span class=\"hljs-keyword\">NULL<\/span>,\r\n    email VARCHAR(<span class=\"hljs-number\">255<\/span>) UNIQUE NOT <span class=\"hljs-keyword\">NULL<\/span>\r\n);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-6\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Fifth, <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-insert-multiple-rows\/\">insert three rows<\/a> into the <code>contacts<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-7\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">INSERT INTO contacts (first_name, last_name, email) \nVALUES\r\n    (<span class=\"hljs-string\">'John'<\/span>, <span class=\"hljs-string\">'Doe'<\/span>, <span class=\"hljs-string\">'john.doe@example.com'<\/span>),\r\n    (<span class=\"hljs-string\">'Jane'<\/span>, <span class=\"hljs-string\">'Smith'<\/span>, <span class=\"hljs-string\">'jane.smith@example.com'<\/span>),\r\n    (<span class=\"hljs-string\">'Bob'<\/span>, <span class=\"hljs-string\">'Johnson'<\/span>, <span class=\"hljs-string\">'bob.johnson@example.com'<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-7\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Sixth, exit the <code>mysql<\/code> program:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-8\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\"><span class=\"hljs-keyword\">exit<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-8\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Finally, <a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-backup-a-database\/\">take a full backup<\/a> of the <code>mydb<\/code> database and store the dump file in the <code>~\/backup\/<\/code> directory:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-9\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">mysql -u root -p &gt; ~<span class=\"hljs-regexp\">\/backup\/my<\/span>db.sql<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-9\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Making changes to the database<\/h2>\n\n\n\n<p>First, reconnect to the MySQL server:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-10\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">mysql -u root -p<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-10\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, change the current database to <code>mydb<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-11\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">USE<\/span> mydb;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-11\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Third, insert a new row into the <code>mydb<\/code> database:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-12\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">INSERT INTO contacts(first_name, last_name, email)\nVALUES(<span class=\"hljs-string\">'Bob'<\/span>,<span class=\"hljs-string\">'Climo'<\/span>, <span class=\"hljs-string\">'bob.climo@example.com'<\/span>);<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-12\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Fourth, delete a contact with the id 1 but forget to add a <code><a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-where\/\">WHERE<\/a><\/code> clause, hence, the statement deletes all rows from the <code>contacts<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-13\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">DELETE<\/span> <span class=\"hljs-keyword\">FROM<\/span> contacts;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-13\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Since we delete all rows from the <code>contacts<\/code> table unintentionally, we want to recover the <code>contacts<\/code> table.<\/p>\n\n\n\n<p>Fifth, show the current position of the binary log:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-14\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">SHOW<\/span> <span class=\"hljs-keyword\">MASTER<\/span> <span class=\"hljs-keyword\">STATUS<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-14\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-15\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">\r+<span class=\"hljs-comment\">---------------+----------+--------------+------------------+-------------------+<\/span>\r\n| File          | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |\r\n+<span class=\"hljs-comment\">---------------+----------+--------------+------------------+-------------------+<\/span>\r\n| binlog.000001 |     4952 |              |                  |                   |\r\n+<span class=\"hljs-comment\">---------------+----------+--------------+------------------+-------------------+<\/span>\r\n1 row in <span class=\"hljs-keyword\">set<\/span> (<span class=\"hljs-number\">0.00<\/span> sec)\r\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-15\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The current binary log file is <code>binlog.000001<\/code>.<\/p>\n\n\n\n<p>Sixth, exit the <code>mysql<\/code> program:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-16\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">exit<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-16\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Seventh, check the time when we delete all rows from the <code>contacts<\/code> table in the binary log file using the <code>mysqlbinlog<\/code> utility program:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-17\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"> mysqlbinlog  <span class=\"hljs-comment\">--verbose \/var\/lib\/mysql\/binlog.000001 | grep -i -C 10 \"delete table\"<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-17\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output looks like this:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-18\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-comment\">--<\/span>\r\n<span class=\"hljs-comment\">#231225 20:26:10 server id 1  end_log_pos 4921 CRC32 0x53432937         Delete_rows: table id 494 flags: STMT_END_F<\/span>\r\n\r\nBINLOG '\r\n8oKJZRMBAAAARAAAAHISAAAAAO4BAAAAAAEABG15ZGIACGNvbnRhY3RzAAQDDw8PBvwD\/AP8AwAB\r\nAQACA\/z\/ADxQW1A=\r\n8oKJZSABAAAAxwAAADkTAAAAAO4BAAAAAAEAAgAE\/wABAAAABABKb2huAwBEb2UUAGpvaG4uZG9l\r\nQGV4YW1wbGUuY29tAAIAAAAEAEphbmUFAFNtaXRoFgBqYW5lLnNtaXRoQGV4YW1wbGUuY29tAAMA\r\nAAADAEJvYgcASm9obnNvbhcAYm9iLmpvaG5zb25AZXhhbXBsZS5jb20ABAAAAAMAQm9iBQBDbGlt\r\nbxUAYm9iLmNsaW1vQGV4YW1wbGUuY29tNylDUw==\r\n'<span class=\"hljs-comment\">\/*!*\/<\/span>;\r\n<span class=\"hljs-comment\">### DELETE FROM `mydb`.`contacts`<\/span>\r\n<span class=\"hljs-comment\">### WHERE<\/span>\r\n<span class=\"hljs-comment\">###   @1=1<\/span>\r\n<span class=\"hljs-comment\">###   @2='John'<\/span>\r\n<span class=\"hljs-comment\">###   @3='Doe'<\/span>\r\n<span class=\"hljs-comment\">###   @4='john.doe@example.com'<\/span>\r\n<span class=\"hljs-comment\">### DELETE FROM `mydb`.`contacts`<\/span>\r\n<span class=\"hljs-comment\">### WHERE<\/span>\r\n<span class=\"hljs-comment\">###   @1=2<\/span>\r\n<span class=\"hljs-comment\">###   @2='Jane'<\/span>\r\n<span class=\"hljs-comment\">###   @3='Smith'<\/span>\r\n<span class=\"hljs-comment\">###   @4='jane.smith@example.com'<\/span>\r\n<span class=\"hljs-comment\">### DELETE FROM `mydb`.`contacts`<\/span>\r\n<span class=\"hljs-comment\">### WHERE<\/span>\r\n<span class=\"hljs-comment\">###   @1=3<\/span>\r\n<span class=\"hljs-comment\">###   @2='Bob'<\/span>\r\n<span class=\"hljs-comment\">###   @3='Johnson'<\/span>\r\n<span class=\"hljs-comment\">###   @4='bob.johnson@example.com'<\/span>\r\n<span class=\"hljs-comment\">### DELETE FROM `mydb`.`contacts`<\/span>\r\n<span class=\"hljs-comment\">### WHERE<\/span>\r\n<span class=\"hljs-comment\">###   @1=4<\/span>\r\n<span class=\"hljs-comment\">###   @2='Bob'<\/span>\r\n<span class=\"hljs-comment\">###   @3='Climo'<\/span>\r\n<span class=\"hljs-comment\">###   @4='bob.climo@example.com'<\/span>\r\n<span class=\"hljs-comment\"># at 4921<\/span>\r\n<span class=\"hljs-comment\">#231225 20:26:10 server id 1  end_log_pos 4952 CRC32 0x634fce0a         Xid = 3194<\/span>\r\n<span class=\"hljs-keyword\">COMMIT<\/span><span class=\"hljs-comment\">\/*!*\/<\/span>;\r\n<span class=\"hljs-keyword\">SET<\/span> @@SESSION.GTID_NEXT= <span class=\"hljs-string\">'AUTOMATIC'<\/span> <span class=\"hljs-comment\">\/* added by mysqlbinlog *\/<\/span> <span class=\"hljs-comment\">\/*!*\/<\/span>;\r\nDELIMITER ;\r\n<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-18\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output shows that at <code>2023-12-25 20:26:10<\/code> we delete all rows from the <code>contacts<\/code> table. Therefore, we need to recover the database up to <code>2023-12-25 20:26:10<\/code>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Performing a point-in-time recovery<\/h2>\n\n\n\n<p>First, reconnect to the MySQL server:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-19\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">mysql -u root -p<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-19\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Second, <a href=\"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-drop-database\/\">drop<\/a> the <code>mydb<\/code> database and recreate it:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-20\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\"><span class=\"hljs-keyword\">DROP<\/span> <span class=\"hljs-keyword\">DATABASE<\/span> mydb;\n<span class=\"hljs-keyword\">CREATE<\/span> <span class=\"hljs-keyword\">DATABASE<\/span> mydb;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-20\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Third, exit mysql program:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-21\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">exit<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-21\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Fourth, <a href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-backup-a-database\/\">restore<\/a> the <code>mydb<\/code> database from the full backup:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-22\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">mysql -u root -p mydb &lt; ~\/<span class=\"hljs-keyword\">backup<\/span>\/mydb.sql<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-22\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Fifth, recover the rows from the <code>contacts<\/code> table from the binary log file using the <code>mysqlbinlog<\/code> utility program:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-23\" data-shcb-language-name=\"SQL (Structured Query Language)\" data-shcb-language-slug=\"sql\"><span><code class=\"hljs language-sql\">mysqlbinlog <span class=\"hljs-comment\">--stop-datetime=\"2023-12-25 20:26:10\" --verbose \/var\/lib\/mysql\/binlog.000001 | mysql -u root -p <\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-23\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">SQL (Structured Query Language)<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">sql<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>This command reads the MySQL binary log file (<code>\/var\/lib\/mysql\/binlog.000001<\/code>), stopping at a specified datetime (<code>2023-12-25 20:26:10<\/code>), displays the contents with additional verbosity, and then pipes that output to the <code>mysql<\/code> command, which executes the SQL statements recorded in the binary log on a MySQL server, using the specified user (<code>root<\/code>) and prompting for a password.<\/p>\n\n\n\n<p>Let&#8217;s breakd down the command:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>mysqlbinlog:<\/strong>\n<ul class=\"wp-block-list\">\n<li><code>mysqlbinlog<\/code> is a command-line utility provided by MySQL for processing and displaying the contents of binary log files.<\/li>\n\n\n\n<li><code>--stop-datetime=\"2023-12-25 20:26:10\"<\/code>: This option specifies a datetime value and <code>mysqlbinlog<\/code> will process the binary log until it reaches the specified datetime. In this case, it stops processing at <code>December 25, 2023, 20:26:10<\/code>.<\/li>\n\n\n\n<li><code>--verbose<\/code>: This option causes <code>mysqlbinlog<\/code> to display additional information along with the actual log contents. It can be useful for debugging and understanding the log entries.<\/li>\n\n\n\n<li><code>\/var\/lib\/mysql\/binlog.000001<\/code>: This is the path to the binary log file that <code>mysqlbinlog<\/code> will process.<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>| (pipe):<\/strong>\n<ul class=\"wp-block-list\">\n<li>The pipe symbol (<code>|<\/code>) is used to redirect the output of the first command (<code>mysqlbinlog<\/code>) to the input of the second command (<code>mysql<\/code>).<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>mysql:<\/strong>\n<ul class=\"wp-block-list\">\n<li><code>mysql<\/code> is another command-line utility used to execute SQL statements based on the output of <code>mysqlbinlog<\/code>.<\/li>\n\n\n\n<li><code>-u root -p<\/code>: These are options for specifying the MySQL user (<code>root<\/code> in this case) and prompting for a password (<code>-p<\/code>).<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<p>Sixth, connect to MySQL server:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">mysql -u root -p<\/code><\/span><\/pre>\n\n\n<p>Seventh, switch the current database to <code>mydb<\/code>:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-24\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\"><span class=\"hljs-keyword\">use<\/span> <span class=\"hljs-title\">mydb<\/span>;<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-24\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>Finally, retrieve data from the <code>contacts<\/code> table:<\/p>\n\n\n<pre class=\"wp-block-code\"><span><code class=\"hljs\">SELECT * FROM contacts;<\/code><\/span><\/pre>\n\n\n<p>Output:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-25\" data-shcb-language-name=\"JavaScript\" data-shcb-language-slug=\"javascript\"><span><code class=\"hljs language-javascript\">\r\n+----+------------+-----------+-------------------------+\r\n| id | first_name | last_name | email                   |\r\n+----+------------+-----------+-------------------------+\r\n|  <span class=\"hljs-number\">1<\/span> | John       | Doe       | john.doe@example.com    |\r\n|  <span class=\"hljs-number\">2<\/span> | Jane       | Smith     | jane.smith@example.com  |\r\n|  <span class=\"hljs-number\">3<\/span> | Bob        | Johnson   | bob.johnson@example.com |\r\n|  <span class=\"hljs-number\">4<\/span> | Bob        | Climo     | bob.climo@example.com   |\r\n+----+------------+-----------+-------------------------+\r\n<span class=\"hljs-number\">4<\/span> rows <span class=\"hljs-keyword\">in<\/span> <span class=\"hljs-keyword\">set<\/span> (0.00 sec)<\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-25\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">JavaScript<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">javascript<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<p>The output indicates that we have successfully recover the rows from the <code>contacts<\/code> table right before we issued the <code>DELETE<\/code> statement that removed all the rows from the table.<\/p>\n\n\n\n<p>Finally, exit the mysql program:<\/p>\n\n\n<pre class=\"wp-block-code\" aria-describedby=\"shcb-language-26\" data-shcb-language-name=\"PHP\" data-shcb-language-slug=\"php\"><span><code class=\"hljs language-php\"><span class=\"hljs-keyword\">exit<\/span><\/code><\/span><small class=\"shcb-language\" id=\"shcb-language-26\"><span class=\"shcb-language__label\">Code language:<\/span> <span class=\"shcb-language__name\">PHP<\/span> <span class=\"shcb-language__paren\">(<\/span><span class=\"shcb-language__slug\">php<\/span><span class=\"shcb-language__paren\">)<\/span><\/small><\/pre>\n\n\n<h2 class=\"wp-block-heading\">Summary<\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use point-in-time recovery to restore a MySQL database to a specific time in the past.<\/li>\n\n\n\n<li>Restore a database from a full backup and restore to a specific time from the binary log log to perform a point-in-time recovery.<\/li>\n<\/ul>\n<div class=\"helpful-block-content\" data-title=\"\">\n\t<header>\n\t\t<div class=\"wth-question\">Was this tutorial helpful? <\/div>\n\t\t<div class=\"wth-thumbs\">\n\t\t\t<button\n\t\t\t\tdata-post=\"13454\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-point-in-time-recovery\/\"\n\t\t\t\tdata-post-title=\"MySQL Point-in-time Recovery\"\n\t\t\t\tdata-response=\"1\"\n\t\t\t\tclass=\"wth-btn-rounded wth-yes-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t\tclass=\"feather feather-thumbs-up block w-full h-full\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M14 9V5a3 3 0 0 0-3-3l-4 9v11h11.28a2 2 0 0 0 2-1.7l1.38-9a2 2 0 0 0-2-2.3zM7 22H4a2 2 0 0 1-2-2v-7a2 2 0 0 1 2-2h3\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t<button\n\t\t\t\tdata-response=\"0\"\n\t\t\t\tdata-post=\"13454\"\n\t\t\t\tdata-post-url=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-point-in-time-recovery\/\"\n\t\t\t\tdata-post-title=\"MySQL Point-in-time Recovery\"\n\t\t\t\tclass=\"wth-btn-rounded wth-no-btn\"\n\t\t\t>\n\t\t\t\t<svg\n\t\t\t\t\txmlns=\"http:\/\/www.w3.org\/2000\/svg\"\n\t\t\t\t\tviewBox=\"0 0 24 24\"\n\t\t\t\t\tfill=\"none\"\n\t\t\t\t\tstroke=\"currentColor\"\n\t\t\t\t\tstroke-width=\"2\"\n\t\t\t\t\tstroke-linecap=\"round\"\n\t\t\t\t\tstroke-linejoin=\"round\"\n\t\t\t\t>\n\t\t\t\t\t<path\n\t\t\t\t\t\td=\"M10 15v4a3 3 0 0 0 3 3l4-9V2H5.72a2 2 0 0 0-2 1.7l-1.38 9a2 2 0 0 0 2 2.3zm7-13h2.67A2.31 2.31 0 0 1 22 4v7a2.31 2.31 0 0 1-2.33 2H17\"\n\t\t\t\t\t><\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t<span class=\"sr-only\"> No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t<div class=\"wth-form hidden\">\n\t\t<div class=\"wth-form-wrapper\">\n\t\t\t<div class=\"wth-title\"><\/div>\n\t\t\t\n\t\t\t<textarea class=\"wth-message\"><\/textarea>\n\n\t\t\t<button class=\"btn btn-primary wth-btn-submit\">Send<\/button>\n\t\t\t<button class=\"btn wth-btn-cancel\">Cancel<\/button>\n\t\t\n\t\t<\/div>\n\t<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"<p>Summary: in this tutorial, you will learn about the MySQL Point-in-time recovery concept and how to restore a database to a specific time in the past. Introduction to MySQL point-in-time recovery The point-in-time recovery allows you to restore a MySQL database to a specific time in the past. The point-in-time recovery relies on two key [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"parent":441,"menu_order":50,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-13454","page","type-page","status-publish","hentry"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.5 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>MySQL Point-in-time Recovery<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-point-in-time-recovery\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"MySQL Point-in-time Recovery\" \/>\n<meta property=\"og:description\" content=\"Summary: in this tutorial, you will learn about the MySQL Point-in-time recovery concept and how to restore a database to a specific time in the past. Introduction to MySQL point-in-time recovery The point-in-time recovery allows you to restore a MySQL database to a specific time in the past. The point-in-time recovery relies on two key [&hellip;]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-point-in-time-recovery\/\" \/>\n<meta property=\"og:site_name\" content=\"MySQL Tutorial\" \/>\n<meta property=\"article:modified_time\" content=\"2023-12-25T14:01:30+00:00\" \/>\n<meta name=\"twitter:label1\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data1\" content=\"4 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-point-in-time-recovery\\\/\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-point-in-time-recovery\\\/\",\"name\":\"MySQL Point-in-time Recovery\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\"},\"datePublished\":\"2023-12-25T14:00:56+00:00\",\"dateModified\":\"2023-12-25T14:01:30+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-point-in-time-recovery\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-point-in-time-recovery\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/mysql-point-in-time-recovery\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"MySQL Administration\",\"item\":\"https:\\\/\\\/www.mysqltutorial.org\\\/mysql-administration\\\/\"},{\"@type\":\"ListItem\",\"position\":3,\"name\":\"MySQL Point-in-time Recovery\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.mysqltutorial.org\\\/#website\",\"url\":\"https:\\\/\\\/www.mysqltutorial.org\\\/\",\"name\":\"MySQL Tutorial\",\"description\":\"A comprehensive MySQL Tutorial\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.mysqltutorial.org\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"MySQL Point-in-time Recovery","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-point-in-time-recovery\/","og_locale":"en_US","og_type":"article","og_title":"MySQL Point-in-time Recovery","og_description":"Summary: in this tutorial, you will learn about the MySQL Point-in-time recovery concept and how to restore a database to a specific time in the past. Introduction to MySQL point-in-time recovery The point-in-time recovery allows you to restore a MySQL database to a specific time in the past. The point-in-time recovery relies on two key [&hellip;]","og_url":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-point-in-time-recovery\/","og_site_name":"MySQL Tutorial","article_modified_time":"2023-12-25T14:01:30+00:00","twitter_misc":{"Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-point-in-time-recovery\/","url":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-point-in-time-recovery\/","name":"MySQL Point-in-time Recovery","isPartOf":{"@id":"https:\/\/www.mysqltutorial.org\/#website"},"datePublished":"2023-12-25T14:00:56+00:00","dateModified":"2023-12-25T14:01:30+00:00","breadcrumb":{"@id":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-point-in-time-recovery\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-point-in-time-recovery\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.mysqltutorial.org\/mysql-administration\/mysql-point-in-time-recovery\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/www.mysqltutorial.org\/"},{"@type":"ListItem","position":2,"name":"MySQL Administration","item":"https:\/\/www.mysqltutorial.org\/mysql-administration\/"},{"@type":"ListItem","position":3,"name":"MySQL Point-in-time Recovery"}]},{"@type":"WebSite","@id":"https:\/\/www.mysqltutorial.org\/#website","url":"https:\/\/www.mysqltutorial.org\/","name":"MySQL Tutorial","description":"A comprehensive MySQL Tutorial","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.mysqltutorial.org\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"}]}},"_links":{"self":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13454","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/comments?post=13454"}],"version-history":[{"count":5,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13454\/revisions"}],"predecessor-version":[{"id":13467,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/13454\/revisions\/13467"}],"up":[{"embeddable":true,"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/pages\/441"}],"wp:attachment":[{"href":"https:\/\/www.mysqltutorial.org\/wp-json\/wp\/v2\/media?parent=13454"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}