Mysql – MariaDB: point in time backup recovery with binary log using position

backupmariadbMySQLmysqldumprestore

Following the below sequence of commands i expected the recovery of backup and subsequent point in time recovery using binary log. However, the database was restored to backup only:

1) 

CREATE TABLE `CUSTOMERS` (
  `ID` int(11) NOT NULL,
  `NAME` varchar(20) NOT NULL,
  `AGE` int(11) NOT NULL,
  `ADDRESS` char(25) DEFAULT NULL,
  `SALARY` decimal(18,2) DEFAULT NULL,
  PRIMARY KEY (`ID`))

2) INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (70, 'Hardik70', 27, 'Bhopal', 8500.00 );

3) mysqldump db -uroot -ppass --master-data=2 > out.sql

4) INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES (80, 'Hardik80', 27, 'Bhopal', 8500.00 );

5) mysql -uroot -ppass < out.sql

6) 

mysqlbinlog --start-position=368315 /var/log/mysql/bin.123456 \
             | mysql -u root -p

/var/log/mysql/bin.123456 and 368315 was taken from out.sql because the dump was done using –master-data=2 option

i expected select * from CUSTOMERS; to return 

| 70 | Hardik70 |  27 | Bhopal    | 8500.00 |
| 80 | Hardik80 |  27 | Bhopal    | 8500.00 |

but only | 70 | Hardik70 | 27 | Bhopal | 8500.00 | is returned

How to do point-in-time backup recovery correctly?

Best Answer

The point in time recovery actually works, but you forget, that your restoration of the dump also gets written to the binary log and this undoes your point in time recovery.

What you should actually do, is to also note the binary log position before you restore your dump. Then you limit the point in time recovery with --end-position. Your workflow therefore is

  1. create table
  2. insert into table
  3. mysqldump --master-data=2
  4. insert into table
  5. show master status; /* note log file and position */
  6. mysql < out.sql
  7. mysqlbinlog --start-position=368315 --end-position=<note from above> binlog | mysql

Alternatively you can also not write the mysqldump to the binary log. The 6th step would then be 

mysql > set session sql_log_bin=0; 
mysql > source out.sql

You have to re-setup slaves then of course.

Related Question