0

So I have a binary log that's taking forever to restore to because of some poorly written code that updates a table every second or so for every user that's currently logged in. Of course the restore process is restoring every one of these lines and making the script take way longer than it needs to.

What would be the quickest way to update this file to remove that specific query from all of my binary logs? I have this PHP script that I tested and it worked on a smaller file, but preg_replace seemed to have some limits (or maybe just PHP as a whole) when it came to the file size.

Here's my PHP line:

file_put_contents("/logs/restore.sql",preg_replace("/use(?:.|\n)+?update `notifi(?:.|\n)+?COMMIT\/\*\!\*\/;/mui",'',file_get_contents("/logs/restorex.sql")));

The Regex in there seemed to be working great with the smaller file, it's just that I'm not as good with any other languages (PHP is my main language) to know what I should be using, or how to implement that Regex into something else.

Brian Leishman
  • 8,155
  • 11
  • 57
  • 93
  • **grep** would be quicker - use the _-v_ flag to indicate you would like to keep only those lines without the content you have identified. – user2182349 Jul 06 '16 at 01:55
  • Do you think you could help me with that? I'm sort of a bash noob and I'm not sure where to start with the examples I can find – Brian Leishman Jul 06 '16 at 01:59
  • Could you post a tiny bit of test data? Perhaps two lines that you don't want to match and one that should? – user2182349 Jul 06 '16 at 02:00
  • Here, I have an actual excerpt of the file with some working Regex http://regexr.com/3dol4 – Brian Leishman Jul 06 '16 at 02:01
  • Hmm, I got it to work with `pcregrep -M -v "^SET(.|\n)+?(update.+?notifi|update.+?users.+?set.+?DateTimeOnline)(.|\n)+?COMMIT.+?$" results.sql > temp` but now I'm having other unrelated issues with that file – Brian Leishman Jul 06 '16 at 02:40
  • 1
    Tweaking the output of `mysqlbinlog` is not a task to be undertaken lightly. – Michael - sqlbot Jul 06 '16 at 04:59
  • How big is `notifications`? What is the value of `innodb_buffer_pool_size`? How much RAM? (I am leading toward making the query run faster.) – Rick James Jul 10 '16 at 16:14

1 Answers1

2

If you are willing to throw away all data in that table,

 ALTER TABLE notifications ENGINE=BLACKHOLE;

Then the queries would zip by because they would do nothing.

Rick James
  • 135,179
  • 13
  • 127
  • 222
  • This is incredible, I didn't even think that something like this would exist. I'll definitely make a note of this for next time I have to restore from binlogs – Brian Leishman Jul 11 '16 at 12:31