Очистка mediawiki от паразитного контента

Коллега подкинул халтурку – вычистить любительского проекта на движке mediawiki вирусы и контент, добавленный спамерами и быдлохакерами.

История проста – сайт “Энциклопедии Подмосковья” на базе mediawiki несколько лет назад сломали (то ли через эксплойт, то ли через открытую кому попало регистрацию), потом боты надобавляли туда всякой ереси… а потом хостер отключил сайт за рассылку спама и вирусную активность.

Чисткой вирусов я решил не заниматься – смысл, если все равно движок будем обновлять до latest-версии вместе с переездом на VPS?

Занялся базой.

Разумеется, бэкап! 2.2 гектара sql-кода. Я малость прифигел, залил файл к себе на хост и полез разбираться:

Есть и другие таблицы с паразитными данными, но это самое объёмное.

Как видно, объемы паразитных данных таковы, что без автоматизации и анализа структуры базы не обойтись. Я пошел по пути наименьшего сопротивления и пошел читать доку Mediawiki: Руководство:Макет базы данных. А потом стал составлять запросы.

Начинается все, очевидно, с пользователей (user):

DELETE FROM `user` WHERE user_id > 8; 
OPTIMIZE TABLE `user`

Теперь удаляем из таблицы с ревизиями страниц (revision) только те ревизии, которые сделали пользователи, не найденные в таблице user (то есть сделанные спамерами, именно их мы удалили предыдущим запросом). Связь:  revision.rev_user -> user.user_id. Согласно документации, пользователь с rev_user  = 0 – системный, ревизии, сделанные от его лица надо отставить в неприкосновенности.

DELETE FROM `revision`​
WHERE `rev_user` != 0 AND `rev_user` NOT IN 
( SELECT u.user_id FROM user u ); 
OPTIMIZE TABLE `revision`​;

Теперь чистим таблицу заголовков страниц (page). Удаляем страницы, идентификаторы которых не найдены в таблице ревизий ( page.page_id <=> revision.rev_page ):

DELETE FROM page WHERE page_id NOT IN 
( SELECT r.rev_page FROM revision r ); 
OPTIMIZE TABLE page;

Теперь чистим таблицу с контентом страниц (`text`) , логика удаления аналогична предыдущей, только связь идёт по `text.old_id <=> revision.rev_text_id`.

DELETE FROM `text` WHERE old_id NOT IN
( SELECT r.rev_text_id FROM revision r 
GROUP BY rev_text_id ORDER BY rev_text_id );
OPTIMIZE TABLE `text`;

Это очень неторопливый запрос, у меня он отрабатывал часа полтора, хотя на SSD-дисках будет в разы быстрее. Думаю, его можно было бы соптмизировать через вставку в новую таблицу тех и только тех строк из старой, которые имеют ревизию, но я не стал проверять.

Теперь чистим таблицу `pagelinks` по связи `pagelinks.pl_from <=> page.page_id`

DELETE FROM pagelinks WHERE pl_from NOT IN
( SELECT p.page_id FROM page p );
OPTIMIZE TABLE pagelinks;

Чистим таблицу `recentchanges` по связи `по связи recentchanges.rc_user <=> user.user_id`

DELETE FROM recentchanges WHERE rc_user NOT IN
( SELECT u.user_id FROM `user` u );
OPTIMIZE TABLE recentchanges;

Чистим таблицу `watchlist` по связи `watchlist.wl_user <=> user.user_id`

DELETE FROM watchlist WHERE wl_user NOT IN
( SELECT u.user_id FROM user u );
OPTIMIZE TABLE watchlist;

Чистим таблицу externallinks по связи externallinks.el_from <=> page.page_id

DELETE FROM externallinks WHERE el_from NOT IN
( SELECT p.page_id FROM page p );
OPTIMIZE table externallinks;

Это тоже довольно долгий запрос, но с чистой text не сравнить.

Теперь чистим таблицу `imagelinks` по связи `page.page_id <=> imagelinks.il_from`

DELETE FROM imagelinks WHERE il_from NOT IN
( SELECT p.page_id FROM page p );
OPTIMIZE TABLE externallinks;

Таблицы кэшей и индексов можно просто обнулить:

TRUNCATE TABLE searchindex;
TRUNCATE TABLE objectcache;

После этих несложных манипуляций БД уже похожа на правду:

Или в SQL-дампе ~25 мегабайт. Придется еще подшлифовать ручками – удалить записи из page, text, revision, появившися, похоже, в результате взлома аккаунта админа.

В следующей серии – установка mediawiki на VPS и апгрейд имеющейся вики.