MariaDB: point in time backup recovery with binary log using position 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 &lt; 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?
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


create table
insert into table
mysqldump --master-data=2
insert into table
show master status; /* note log file and position */
mysql &lt; out.sql
mysqlbinlog --start-position=368315 --end-position=&lt;note from above&gt; binlog | mysql


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

mysql &gt; set session sql_log_bin=0; 
mysql &gt; source out.sql


You have to re-setup slaves then of course.