I got thousends of spam comments on my drupal 7 site. The mysql database file is grown to 1.9GB and the site is broken. How can i delete all comments (or even better: all unapproved comments) from the database dump file?

  • Drupal is a framework for building websites, it doesn't have tools for manipulating raw sql dumps. You'll probably want to write a script to identify what you consider to be spam, and remove it. This would be easier before the database is dumped, or after its restored, rather than trying to manipulate a text file - you might be able to find modules to help in those cases
    – Clive
    Oct 24, 2015 at 13:12
  • I think you missunderstood me: due to an "accident", in order to restore the site, i've got only the file structure and a database dump. Since i could not import the dump back again because of its size, i would like to truncate the comments BEFORE importing; but i dont know what to delete (so it's sql question but with an drupal background).
    – Madam Adam
    Oct 24, 2015 at 13:20
  • I understand what you asked :) The only Drupal-related part would be the layout of the table (comments are in the comment table, you can use the status column to determine whether they're approved or not). Drupal can't help you with actually making the change, since it has no tools for manipulating mysql dumps. Neither does MySQL, to manipulate the data with SQL it needs to be in a database. There may be tools out there to help (I don't know of any), or the other option is a complex regular expression. To the best of my knowledge there's nothing Drupal can do to help you with this
    – Clive
    Oct 24, 2015 at 13:32

1 Answer 1


From your dump file, I would cut the part that insert the data to the comment table. Note: Leave the part that create the table.

In another file paste the part that you cut from the original dump file, using a text editor that can handle to search using regular expressions (as Sublime Text 2). Create the regular expression that would give you as a result the complete row insert if that row has the status column value set to one. Once that the regular expression is working, now use the search an replace. Search all the matches and replace them with ''

When the search and replace is done, you can copy and paste to the sql tab if you are using phpMyAdmin, or even save the resulting file as .sql and import it.

Pretty much the laborious part would be to find the regular expression to use.

Hope this help!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.