2011-09-08, 05:01 AM
Keep in mind the following is only likely to be useful to Linux, Mac, and UNIX users!
Case: Friend of a Friend accidentally deleted a user in the ACP. They did NOT delete the user's posts (thankfully!!)
I knew I could extract the user from a database backup and restore their reputations, pms, and their entry in the user table. However the database backup was 144MB and caused major memory issues trying to open in any editor.
Now, I thought to myself, there HAS to be a better way than trying to trim all the content in the backup prior to the start of the PMs section than in gedit or Bluefish. I figured there should be some way with tail or another Linux util. Turns out I was right.
Here it is for tail:
tail -n +123 < /path/to/file > /path/to/outfile
Where 123 is the line number, /path/to/file is your sql backup and /path/to/outfile is where you want to save it.
And here it is for sed:
sed -n '123,$p' < /path/to/file > /path/to/outfile
Where 123 is the line number, /path/to/file is your sql backup and /path/to/outfile is where you want to save it.
Now... how about that line number? How did I get it? Thats an easy one. I used fgrep of course:
fgrep -Rn mybb_private /path/to/file
Where /path/to/file is of course your sql backup dump.
I took the line number of the first line this fgrep command spit out, which happens to be the line:
-- Table structure for table
This allowed me to trim a 144MB sql file down to a manageable 8.2MB sql file. Make sure you choose a different filename so you don't overwrite your backup!
I tested both of these so that I could keep them for future use. On my system the tail method was significantly faster, and they both produced identical 8.2MB output files. So, I recommend using tail. However if you're more comfortable with sed, by all means.
Sed is much more powerful in the long run, however I just don't care in this particular case.
Another way this could be used is to do both tail AND head with the start-1 and end+1 line numbers of the posts table in order to cut it out. I'll let you craft those commands yourself
Case: Friend of a Friend accidentally deleted a user in the ACP. They did NOT delete the user's posts (thankfully!!)
I knew I could extract the user from a database backup and restore their reputations, pms, and their entry in the user table. However the database backup was 144MB and caused major memory issues trying to open in any editor.
Now, I thought to myself, there HAS to be a better way than trying to trim all the content in the backup prior to the start of the PMs section than in gedit or Bluefish. I figured there should be some way with tail or another Linux util. Turns out I was right.
Here it is for tail:
tail -n +123 < /path/to/file > /path/to/outfile
Where 123 is the line number, /path/to/file is your sql backup and /path/to/outfile is where you want to save it.
And here it is for sed:
sed -n '123,$p' < /path/to/file > /path/to/outfile
Where 123 is the line number, /path/to/file is your sql backup and /path/to/outfile is where you want to save it.
Now... how about that line number? How did I get it? Thats an easy one. I used fgrep of course:
fgrep -Rn mybb_private /path/to/file
Where /path/to/file is of course your sql backup dump.
I took the line number of the first line this fgrep command spit out, which happens to be the line:
-- Table structure for table
mybb_privatemessagesThis allowed me to trim a 144MB sql file down to a manageable 8.2MB sql file. Make sure you choose a different filename so you don't overwrite your backup!
I tested both of these so that I could keep them for future use. On my system the tail method was significantly faster, and they both produced identical 8.2MB output files. So, I recommend using tail. However if you're more comfortable with sed, by all means.
Sed is much more powerful in the long run, however I just don't care in this particular case.Another way this could be used is to do both tail AND head with the start-1 and end+1 line numbers of the posts table in order to cut it out. I'll let you craft those commands yourself