I accidentally executed a sql script that dropped all the tables of my database. The good thing is I have a master slave replication set-up but my slave hasn't been updated for the past 7 days.
So I was thinking if I can rollback the binlogs position to undo the changes made by the script.
Running SHOW BINARY LOGS; gave me:
"Log_name" "File_size"
"mst-bin.000001" "1073883720"
"mst-bin.000002" "1073742741"
"mst-bin.000003" "1073743121"
"mst-bin.000004" "1073744456"
"mst-bin.000005" "1073742926"
"mst-bin.000006" "1073744758"
"mst-bin.000007" "1073741932"
"mst-bin.000008" "1073742250"
"mst-bin.000009" "1073741889"
"mst-bin.000010" "1073745312"
"mst-bin.000011" "1073742485"
"mst-bin.000012" "1073743060"
"mst-bin.000013" "316454512"
"mst-bin.000014" "1073744018"
"mst-bin.000015" "1073741967"
"mst-bin.000016" "1073741933"
"mst-bin.000017" "1073742186"
"mst-bin.000018" "828840943" // i believe this is the affected binlog
What are my options here? After running the script I immediately disabled the database server to freeze everything.
I tried ./mysqlbinlog.exe --start-datetime="2017-02-21 12:01:57" ../data/mst-bin.000018 which is the time the script got executed and I saw everything the script executed.
The last position right before the script started executing drops has this info:
# at 828841094
#170221 12:06:15 server id 1 end_log_pos 828841169 Query thread_id=66 exec_time=0 error_code=0
The Point-in-Time (Incremental) Recovery Using the Binary Log worked for me.
First I looked for the position of the binlog right before the accident. Which I found was: 828841094. Then I executed this command:
./mysqlbinlog.exe --stop-position=828841094 ../data/mst-bin.000001 ../data/mst-bin.000002 ../data/mst-bin.000003 ...so on ../data/mst-bin.000018 | ./mysql.exe --binary-mode --binary-mode -u root -p
The --stop-position will only apply to the last bin file on the list.
And it replayed all the queries which took a lot of time but at least I recovered the data.
Thanks to @eggyal for the suggestion.