Mediawiki

MediaWiki wurde erfolgreich installiert.

Hilfe zur Benutzung und Konfiguration der Wiki-Software findest du im Benutzerhandbuch.

Starthilfen

• Liste der Konfigurationsvariablen
• MediaWiki-FAQ
• Mailingliste neuer MediaWiki-Versionen
• Lokalisiere MediaWiki für deine Sprache

Extensions

• https://www.mediawiki.org/wiki/Extension:SSL_authentication

Deleting SPAM

• https://stackoverflow.com/questions/56699830/delete-mediawiki-spam-users-via-mysql/58305026#58305026

Useful SQL Queries

Effort

select 
   count(*),
   year(r.rev_timestamp) as 'year',
   month(r.rev_timestamp) as 'month',
   day(r.rev_timestamp) as 'day',
   hour(r.rev_timestamp) as 'hour',
   convert(u.user_name using utf8) as 'user'
   from revision r inner join user u on r.rev_user=u.user_id 
   where r.rev_timestamp>'202007' 
   group by 2,3,4,5,6
   order by 2 desc, 3 desc, 4 desc, 5 desc

Revsision by time

select r.rev_timestamp,convert(u.user_name using utf8) as t 
   from revision r inner join user u on r.rev_user=u.user_id 
   where r.rev_timestamp>'202008' 
   order by r.rev_timestamp desc;

Revision/changes by user

select count(*),u.user_id,convert(u.user_name using utf8) as username
from revision r
inner join user u
on r.rev_user=u.user_id
group by 2
order by 1 desc

externallinks

describe externallinks
Field	Type	Null	Key	Default	Extra
el_id	int(10) unsigned	NO	PRI	(null)	auto_increment
el_from	int(10) unsigned	NO	MUL	0	
el_to	blob	NO	MUL	(null)	
el_index	blob	NO	MUL	(null)

user

user details

select 
  convert(user_name using utf8) as name,
  convert(user_touched using utf8) as time,
  user_editcount 
from user 
order by 2 desc

name	time	user_editcount
Wf	20191009122047	689

pages by user

select count(*),u.user_id,convert(u.user_name using utf8) as username
from revision r
inner join user u
on r.rev_user=u.user_id
group by 2
order by 1 desc

remove invalid users

delete from user where not user_id in (1)

text table

utf8 content display

select 
  convert(old_text using utf8) as t 
from text

Pages / Revisions / Text / User

select 
  convert(u.user_name using utf8) as username,
  p.page_id,
  convert(p.page_title using utf8) as pagetitle,
  r.rev_user as userid,
  convert(t.old_text using utf8) as text
from page p
inner join revision r
  on p.page_id=r.rev_page
inner join user u
  on r.rev_user=u.user_id  
inner join text t
  on r.rev_text_id=t.old_id

Delete text from invalid users

use limit of count is high e.g. > 20.000

select count(*) as textcount from text where old_id in (select rev_text_id from revision where not rev_user in (1)); 
set autocommit=0;
start transaction;
delete from text where old_id in (select rev_text_id from revision where not rev_user in (1)) ; 
commit;

Count Delete Text not from users

select count(*) as textcount from text where old_id in (select rev_text_id from revision where not rev_user in (1));

delete
from text
where old_id in (select rev_text_id from revision where rev_user!=1)

optimize table to get back file space

optimize table text

root@io:/var/lib/mysql/mw1_23# ls -l
-rw-r----- 1 mysql mysql      8642 Oct 29 08:38 text.frm
-rw-r----- 1 mysql mysql    131072 Oct 29 08:38 text.ibd