MediaWiki Database Maintenance — Maintenance Scripts, update.php, and Rebuilding Indexes

In this tutorial, you will learn about MediaWiki Database Maintenance. We cover key concepts, practical examples, and best practices to help you master this topic.

Database maintenance in MediaWiki involves running CLI scripts from the maintenance/ directory — updating the database schema with update.php, rebuilding search indexes, repairing corrupted tables, and keeping the database optimized, the same routines Wikipedia administrators run to keep the world's largest wiki operational.

What You'll Learn

• Understanding the maintenance directory
• Running update.php for schema changes
• Rebuilding search indexes and link tables
• Repairing database corruption
• Optimizing database performance
• Scheduling regular maintenance tasks

Why It Matters

Databases degrade over time. Search indexes grow stale. Link tables get out of sync. The database schema changes when you upgrade MediaWiki or install extensions. Running maintenance scripts fixes these issues automatically. Skipping maintenance leads to slow searches, broken category listings, missing page histories, and eventually wiki downtime. Regular maintenance is what keeps a wiki fast and reliable.

Real-World Use

A DodaTech wiki administrator runs a maintenance schedule every Sunday at 3 AM. The scripts rebuild the search index, refresh link tables, update category counts, and optimize database tables. The Process takes 5 minutes. On Monday morning, the wiki is fully optimized for the work week. No users ever notice the maintenance running.

Learning Path

flowchart LR
  A["33: Import & Export"] --> B["34: REST API"]
  B --> C["35: Database Maintenance"]
  C:::current
  D["36: Logging & Monitoring"]
  E["37: Backup & Restore"]
  F["38: Performance Tuning"]

  C --> D --> E --> F

  classDef current fill#38bdf8,color#0f172a,stroke-width:2px

The Maintenance Directory

All maintenance scripts are in the maintenance/ directory of your MediaWiki installation:

cd /opt/lampp/htdocs/mediawiki/maintenance
ls -la | head -20

Key scripts:

update.php           — Run after every upgrade
rebuildrecentchanges.php — Rebuild RecentChanges
refreshLinks.php     — Update link tables
rebuildImages.php    — Regenerate image thumbnails
rebuildAll.php       — Run all rebuild operations
deleteArchivedFiles.php — Clean up deleted files
nukePage.php         — Delete a page permanently
removeInvalidUsers.php — Clean up invalid user accounts

Running update.php

update.php is the most important maintenance script. Run it after:

• Upgrading MediaWiki to a new version
• Installing or upgrading an extension
• Changing wiki configuration that affects the database

cd /opt/lampp/htdocs/mediawiki
php maintenance/update.php

What update.php Does

• Creates new database tables
• Alters existing tables (adds/modifies columns)
• Runs data migrations
• Updates schema version numbers
• Reports any errors during the process

Update Modes

# Quick check (read-only, no changes)
php maintenance/update.php --check

# Force update even if already up to date
php maintenance/update.php --force

# Skip core updates, only update extensions
php maintenance/update.php --extonly

# Run only specific extensions
php maintenance/update.php --skip=ExtensionName

Handling Update Errors

If update.php fails:

1. Read the error message carefully
2. Check if the error is from an extension or core
3. If from an extension, disable it temporarily:
   • Comment out wfLoadExtension('ProblemExtension')
   • Run update.php again
   • Re-enable after fixing the issue
4. If from core, restore the backup and check for known issues

Rebuilding Link Tables

Link tables cache relationships between pages. They can become out of sync.

refreshLinks.php

Rebuilds all link tables:

php maintenance/refreshLinks.php

This updates:

• Category links (which pages are in which categories)
• Template links (which pages use which templates)
• Image links (which pages use which images)
• Language links (interlanguage links)
• External links (external URLs on pages)

Running on Specific Pages

# Rebuild links for a single page
php maintenance/refreshLinks.php --title="DodaBrowser"

# Rebuild links for multiple pages
php maintenance/refreshLinks.php --namespace=0

When to Run

• After importing many pages
• When category listings seem incomplete
• When "What links here" shows wrong results
• Monthly as preventive maintenance

Rebuilding Recent Changes

rebuildrecentchanges.php regenerates the RecentChanges table:

php maintenance/rebuildrecentchanges.php

Use when:

• RecentChanges shows incorrect data
• After importing pages with old timestamps
• After a database crash or restoration

Rebuilding Search Index

MediaWiki uses search index tables for full-text search. Rebuild them when search results are incomplete.

# Rebuild search index
php maintenance/rebuildtextindex.php

For wikis using the default MySQL fulltext search, this script:

1. Drops the existing search index
2. Recreates it
3. Rebuilds the index from all current page content

# Alternative: rebuild search index page by page
php maintenance/rebuildall.php --search

Database Optimization

analyze.php

Analyzes table statistics for the query optimizer:

php maintenance/analyze.php

This runs ANALYZE TABLE on all MediaWiki tables, updating index statistics so the database makes better query plans.

purgeDeletedArchives.php

Clean up deleted page archives:

php maintenance/purgeDeletedArchives.php

Removes archived revisions that are older than a configured threshold.

cleanupInvalidUsers.php

Remove invalid user entries:

php maintenance/cleanupInvalidUsers.php

Manual Database Optimization

For direct database maintenance:

# Connect to MySQL
mysql -u wiki_user -p dodatech_wiki

Check Table Health

CHECK TABLE page, revision, categorylinks, templatelinks;

Repair Corrupted Tables

REPAIR TABLE page, revision;

Optimize Tables

OPTIMIZE TABLE page, revision, text, categorylinks;

Table Sizes

Check which tables use the most space:

SELECT table_name, ROUND(((data_length + index_length) / 1024 / 1024), 2) AS 'Size (MB)'
FROM information_schema.tables
WHERE table_schema = 'dodatech_wiki'
ORDER BY (data_length + index_length) DESC;

Scheduling Regular Maintenance

Create a maintenance cron script:

#!/bin/bash
# /usr/local/bin/wiki-maintenance.sh

MW_DIR="/opt/lampp/htdocs/mediawiki"
LOG_FILE="/var/log/wiki-maintenance.log"

echo "=== Wiki Maintenance: $(date) ===" >> $LOG_FILE

cd $MW_DIR

# Run update.php (safe to run repeatedly)
php maintenance/update.php --quick >> $LOG_FILE 2>&1

# Rebuild link tables
php maintenance/refreshLinks.php >> $LOG_FILE 2>&1

# Rebuild recent changes
php maintenance/rebuildrecentchanges.php >> $LOG_FILE 2>&1

# Analyze tables
php maintenance/analyze.php >> $LOG_FILE 2>&1

echo "=== Complete: $(date) ===" >> $LOG_FILE

Cron Schedule

# Run maintenance every Sunday at 3 AM
0 3 * * 0 /usr/local/bin/wiki-maintenance.sh

# Run update check daily (read-only)
0 2 * * * cd /opt/lampp/htdocs/mediawiki && php maintenance/update.php --check > /dev/null 2>&1

Troubleshooting Database Issues

Slow Queries

Enable slow query logging in MySQL:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 2;

Check the slow query log for problematic queries.

Table Corruption

Symptoms:

• "Table ... is marked as crashed" errors
• Missing pages in category listings
• Search returns incomplete results

Fix:

# Check all tables
php maintenance/checkDatabase.php

# Repair specific tables
mysqlcheck -u wiki_user -p --repair dodatech_wiki

Connection Issues

# Test database connection
php maintenance/sql.php --query "SELECT 1"

# Check MySQL status
mysqladmin -u wiki_user -p status

What You Learned

• maintenance/ directory contains all database maintenance scripts
• update.php must run after every upgrade and extension install
• refreshLinks.php rebuilds all link tables
• rebuildtextindex.php rebuilds the search index
• Database optimization includes ANALYZE, CHECK, and OPTIMIZE
• Scheduled Cron Jobs automate regular maintenance
• Table corruption is fixable with CHECK and REPAIR

In the next lesson, you'll learn about logging and monitoring.

Common Mistakes

Practice Questions

1. When should you run update.php, and what happens if you skip it?
2. What does refreshLinks.php do, and when would you need to run it?
3. How would you set up a scheduled maintenance routine for a production wiki?
4. Challenge: Build a complete maintenance schedule. Set up a cron job that runs weekly maintenance including update.php check, refreshLinks, rebuildrecentchanges, and analyze. Create a log file that records each run with timestamps. Add a monitoring check that verifies the maintenance script ran within the last 7 days and alerts you if it did not. Test each script individually on your wiki. Document the maintenance schedule and include the exact commands, cron syntax, and expected duration for each task.

FAQ

Mini Project

Goal: Implement a comprehensive database maintenance system.

1. List all available maintenance scripts in the maintenance directory
2. Run update.php --check to verify database schema is current
3. Run analyze.php to update table statistics
4. Run refreshLinks.php for a single namespace
5. Check table health using CHECK TABLE SQL command
6. Create a maintenance script that runs weekly:
   • Logs start time
   • Runs refreshLinks
   • Runs rebuildrecentchanges
   • Runs analyze
   • Logs end time
7. Set up a cron job for the maintenance script
8. Verify the cron job ran by checking the log file
9. Create a "Maintenance Log" page on the wiki documenting the schedule

What's Next

Database maintenance keeps the backend healthy. Now let's look at logging and monitoring for understanding what happens on your wiki.

Continue to Lesson 36: Logging & Monitoring — learn about log types, log search, and Special:Log for monitoring wiki activity.