Cleanup Wiki from Mass Spam

Wiki Cleanup from Mass Spam

recently I recognized, the wiki was filled with many (>2000) new pages, all containing spam. Also I noticed there were about 20000 users registered, all spammers or without contributions.

I decided to allow edit only for registered users and deactivated automatic registering. So I put this in LocalSettings.php:

$wgGroupPermissions['*']['edit'] = false;
$wgGroupPermissions['*']['createaccount'] = false;

Since I found no useful plugin to remove that many users and pages I did some research on the database table level. Mediawiki provided a useful [database schema] for this purpose.

My mediawiki database is mysql database wikidb. User is joachim. For ease of use of the mysql command I created this .my.cnf file

[mysql]
password=my_password
user=joachim

Since I am the only user with valuable content and my user id is 1, I came up with these sql commands to clean up. Worked well as far as I can tell:

mysql -D wikidb -e "delete from watchlist where wl_user <> 1"
mysql -D wikidb -e "delete from ipblocks"
mysql -D wikidb -e "delete from logging where log_user <> 1"
mysql -D wikidb -e "delete from recentchanges where rc_user <> 1"
mysql -D wikidb -e "delete from user where user_id <> 1"
mysql -D wikidb -e "delete from watchlist where wl_user <> 1"
mysql -D wikidb -e "delete from text where old_id = (select ar_text_id from archive where ar_user <> 1)"
mysql -D wikidb -e "delete from archive where ar_user <> 1"
mysql -D wikidb -e "delete from text where old_id = (select rev_text_id from revision where rev_user <> 1)"
mysql -D wikidb -e "delete from revision where rev_user <> 1"
mysql -D wikidb -e "delete from page where not exists (select 1 from revision r where page.page_id = r.rev_page)"

Only trace left of spammers are log entries from my earlier manual cleanup attempts. Can live with that :)