MySQL is having a good features as binlog files.When ever the transaction happens in the server this binlog files have the transaction details.
Here am going to explain how to restore records from mysqlbinlog file.
For example:
First check bin log is enabled or not.
login to mysql,check it by,
mysql>show binary logs ;
+------------------+-----------+
| Log_name | File_size |
+------------------+-----------+
| mysql-bin.000001 | 25071488 |
| mysql-bin.000002 | 150 |
| mysql-bin.000003 | 131262 |
| mysql-bin.000004 | 463122 |
| mysql-bin.000005 | 1040619 |
+------------------+-----------+
5 rows in set (0.00 sec)
So binlog is enabled in this server.If not enabled do the below,
Remove "#" for log-bin=mysql-bin from my.cnf file
Restart mysql.
Now,I have a database name "fruits" inside I have table name is "apple" it contains 15 records as shown below.
mysql>use fruits;
Database changed
mysql> show tables;
+------------------+
| Tables_in_fruits |
+------------------+
| apple |
| banana |
| mango |
| orange |
+------------------+
mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
| 1 | abc | 1389335150 |
| 2 | abc | 1389335163 |
| 3 | abc | 1389335165 |
| 4 | prs | 1389335167 |
| 5 | abc | 1389335167 |
| 6 | abc | 1389335169 |
| 7 | xyz | 1389335176 |
| 8 | xyz | 1389335178 |
| 9 | KLM | 1389335181 |
| 10 | KLM | 1389335315 |
| 11 | xyz | 1389335327 |
| 12 | RES | 1389335329 |
| 13 | xyz | 1389335331 |
| 14 | RES | 1389335333 |
| 15 | RES | 1389335335 |
+-----+----------+------------+
15 rows in set (0.00 sec)
I am taking a backup of this apple table now,
root@knowmysql]# mysqldump -u root -p fruits apple > fruits_apple.sql
After taking backup I am inserting some rows in the apple table like below,
mysql>insert into apple (id,username,timestamp) values('16','ILK','1389335345');
Query OK, 1 row affected (0.00 sec)
mysql> insert into apple (id,username,timestamp) values('17','bcd','1389335355');
Query OK, 1 row affected (0.00 sec)
mysql> insert into apple (id,username,timestamp) values('18','brd','1389335375');
Query OK, 1 row affected (0.00 sec)
mysql> insert into apple (id,username,timestamp) values('19','elf','1389335395');
Query OK, 1 row affected (0.00 sec)
mysql> insert into apple (id,username,timestamp) values('20','OPQ','1389335413');
Query OK, 1 row affected (0.01 sec)
Now totally we have 20 rows in the table.
mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
| 1 | abc | 1389335150 |
| 2 | abc | 1389335163 |
| 3 | abc | 1389335165 |
| 4 | prs | 1389335167 |
| 5 | abc | 1389335167 |
| 6 | abc | 1389335169 |
| 7 | xyz | 1389335176 |
| 8 | xyz | 1389335178 |
| 9 | KLM | 1389335181 |
| 10 | KLM | 1389335315 |
| 11 | xyz | 1389335327 |
| 12 | RES | 1389335329 |
| 13 | xyz | 1389335331 |
| 14 | RES | 1389335333 |
| 15 | RES | 1389335335 |
| 16 | ILK | 1389335345 |
| 17 | bcd | 1389335355 |
| 18 | brd | 1389335375 |
| 19 | elf | 1389335395 |
| 20 | OPQ | 1389335413 |
+-----+----------+------------+
20 rows in set (0.00 sec)
Now am deleting last five records from apple table.
mysql>delete from apple where id in('16','17','18','19','20');
Query OK, 3 rows affected (0.00 sec)
Now this table is having 15 records,
mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
| 1 | abc | 1389335150 |
| 2 | abc | 1389335163 |
| 3 | abc | 1389335165 |
| 4 | prs | 1389335167 |
| 5 | abc | 1389335167 |
| 6 | abc | 1389335169 |
| 7 | xyz | 1389335176 |
| 8 | xyz | 1389335178 |
| 9 | KLM | 1389335181 |
| 10 | KLM | 1389335315 |
| 11 | xyz | 1389335327 |
| 12 | RES | 1389335329 |
| 13 | xyz | 1389335331 |
| 14 | RES | 1389335333 |
| 15 | RES | 1389335335 |
| +-----+----------+------------+
15 rows in set (0.00 sec)
Now I am going to restore the deleted records from binlog,
Take a backp of apple table now and drop it from the server.
root@test]# mysqldump -u root -p fruits apple > fruits_apple11.sql
mysql>drop table apple;
mysql> show tables;
+------------------+
| Tables_in_fruits |
+------------------+
| banana |
| mango |
| orange |
+------------------+
apple table dropped.
Import our old backup inside the fruit databases.
root@test]#mysql -u root -p fruits < fruits_apple.sql
Now the table is having only 15 records.
mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
| 1 | abc | 1389335150 |
| 2 | abc | 1389335163 |
| 3 | abc | 1389335165 |
| 4 | prs | 1389335167 |
| 5 | abc | 1389335167 |
| 6 | abc | 1389335169 |
| 7 | xyz | 1389335176 |
| 8 | xyz | 1389335178 |
| 9 | KLM | 1389335181 |
| 10 | KLM | 1389335315 |
| 11 | xyz | 1389335327 |
| 12 | RES | 1389335329 |
| 13 | xyz | 1389335331 |
| 14 | RES | 1389335333 |
| 15 | RES | 1389335335 |
+-----+----------+------------+
15 rows in set (0.00 sec)
Now check the last binlog file from the data directory.(Binlog default location is mysql data directory)
Here am using row format for binlog in my.cnf.For row format below format is used to view the binlog file.
root@knowmysql]#mysqlbinlog -v --base64-output=decode-rows /var/lib/mysqldata/mysql-bin.000005|less
From binlog file search at which position the deleted records were inserted.
I have found the binlog position that insertion happens in the server for id 16,17,18,19,20 in the apple table.
starting position is, 941107 and ending position is 942025.
Now am going to restore from binlog,
root@test]#mysqlbinlog --start-position=941107 --stop-position=942025 /var/lib/mysqldata/mysql-bin.000005 |mysql -u root -p fruits
Records were restored,Now check the records in apple table.
mysql> select id,username,timestamp from apple;
+-----+----------+------------+
| id | username | timestamp |
+-----+----------+------------+
| 1 | abc | 1389335150 |
| 2 | abc | 1389335163 |
| 3 | abc | 1389335165 |
| 4 | prs | 1389335167 |
| 5 | abc | 1389335167 |
| 6 | abc | 1389335169 |
| 7 | xyz | 1389335176 |
| 8 | xyz | 1389335178 |
| 9 | KLM | 1389335181 |
| 10 | KLM | 1389335315 |
| 11 | xyz | 1389335327 |
| 12 | RES | 1389335329 |
| 13 | xyz | 1389335331 |
| 14 | RES | 1389335333 |
| 15 | RES | 1389335335 |
| 16 | ILK | 1389335345 |
| 17 | bcd | 1389335355 |
| 18 | brd | 1389335375 |
| 19 | elf | 1389335395 |
| 20 | OPQ | 1389335413 |
+-----+----------+------------+
20 rows in set (0.00 sec)
Now Apple table is having 20 records and restored the deleted 17,18,19 records from binlog file.
We can restore data from mysqlbinlog by date and time like below.
mysqlbinlog --start-datetime="2014-01-15 10:00:00" \ --stop-datetime="2014-01-15 10:01:04 \ /var/lib/mysqldata/mysql-bin.000005 > /data/apple.sql
mysqlbinlog --start-position=941327 /var/lib/mysqldata/mysql-bin.000005 |mysql -u root -p tablename
This way is little bit tough but we can restore all the records without missing any record.
I hope it will help you.
Thank you.
