The Complete Guide to WordPress Multisite Database Management (2025 Edition)

WordPress Multisite is a powerful feature that lets you manage multiple websites from a single WordPress installation. However, its complex database structure requires special attention for optimal performance, security, and maintenance. This comprehensive guide covers everything you need to know about Multisite database management.

Understanding WordPress Multisite Database Structure

Core Multisite Tables

A standard Multisite installation has these global tables (shared across all sites):

wp_commentmeta
wp_comments
wp_links
wp_options
wp_postmeta
wp_posts
wp_term_relationships
wp_term_taxonomy
wp_terms

Plus these Multisite-specific tables:

wp_blogs          # Stores all sites in the network
wp_blog_versions  # Tracks site database versions
wp_registration_log # User registration log
wp_signups        # Pending site/user signups
wp_site           # Main site information
wp_sitemeta       # Network-wide settings
wp_usermeta       # Extended user data
wp_users          # All network users

Per-Site Tables

Each subsite gets its own set of tables with the site ID prefix:

wp_2_posts       # Site ID 2 posts
wp_3_options     # Site ID 3 options

Critical Multisite Database Management Tasks

1. Database Optimization

Recommended plugins:

• WP-Optimize
• Advanced Database Cleaner

Key operations:

-- Optimize all tables
OPTIMIZE TABLE wp_posts, wp_options, wp_2_posts;

-- Clean post revisions (run per site)
DELETE FROM wp_posts WHERE post_type = 'revision';
DELETE FROM wp_2_posts WHERE post_type = 'revision';

2. Backup Strategies

Approaches:

• Network-wide backups (all sites together)
• Per-site backups (individual site exports)
• Incremental backups (only changed data)

Top tools:

• UpdraftPlus Premium (Multisite support)
• BackupBuddy
• WP CLI (wp db export). Our YouTube channel; https://www.youtube.com/@easythemestore

Advanced Multisite Database Operations

1. Moving Sites Between Networks

Step-by-step process:

1. Export the site database tables
2. Export the site’s uploads folder
3. Import to new network
4. Update references in:

• • wp_blogs

  • wp_site

  • wp_sitemeta

2. Merging Multiple Single Sites into Multisite

1. Install Multisite on main site
2. Use WP Migrate DB Pro plugin
3. Map old site URLs to new Multisite paths
4. Update GUIDs in posts table

3. Database Sharding for Large Networks

For networks with 100+ sites:

1. Split user tables by range (wp_users_1_to_100)
2. Implement custom caching layers
3. Consider HyperDB for read/write splitting

Performance Optimization Techniques

1. Table Indexing

-- Add index to commonly queried columns
ALTER TABLE wp_posts ADD INDEX (post_author, post_status);
ALTER TABLE wp_2_options ADD INDEX (option_name);

2. Cache Configuration

Recommended cache plugins:

• Redis Object Cache
• Memcached
• WP Fastest Cache (Multisite)

wp-config.php settings:

define('WP_CACHE', true);
define('WP_REDIS_HOST', '127.0.0.1');
define('WP_REDIS_SELECTIVE_FLUSH', true);

3. Query Optimization

Problematic queries to avoid:

// Bad: No site ID specification
$posts = $wpdb->get_results("SELECT * FROM wp_posts");

// Good: Explicit site scope
$posts = $wpdb->get_results("SELECT * FROM wp_{$blog_id}_posts");

Security Best Practices

1. Database User Permissions

• Create separate DB users for:

  • Super Admin (full access)

  • Site Admins (only their site tables)

  • Backup accounts (read-only)

2. Sensitive Data Protection

Tables to encrypt:

• wp_users

• wp_usermeta

• wp_x_options (for sensitive site options)

Plugins:

• WP Encryption

• Shield Security

3. Regular Auditing

-- Check for admin users across all sites
SELECT * FROM wp_usermeta WHERE meta_key LIKE '%capabilities%' AND meta_value LIKE '%administrator%';

-- Find unused plugins
SELECT * FROM wp_sitemeta WHERE meta_key = 'active_sitewide_plugins';

Troubleshooting Common Issues

1. “Table Doesn’t Exist” Errors

Solutions:

• Verify wp_blogs entries match table prefixes
• Check DB_NAME in wp-config.php
• Run network repair: wp core repair-network

2. Slow Network Admin

Fix:

// Add to wp-config.php
define('WP_POST_REVISIONS', 3);
define('AUTOSAVE_INTERVAL', 120);
define('EMPTY_TRASH_DAYS', 7);

3. User Synchronization Problems

Debug:

// Check user roles across sites
$user = get_user_by('id', 1);
print_r(get_blogs_of_user($user->ID));

Essential WP-CLI Commands

# Export a specific site's data
wp db export --tables=wp_2_posts,wp_2_options

# Search/replace across all sites
wp search-replace 'old.com' 'new.com' --url=old.com --network

# List all sites
wp site list --field=url

# Clear all caches network-wide
wp cache flush --network

Monitoring and Maintenance Schedule

When to Consider Alternatives

Multisite may not be ideal if:

• Sites need different PHP/MySQL versions
• You require isolated security environments
• Sites have dramatically different traffic patterns

Alternatives:

• ManageWP (centralized single sites)
• InfiniteWP (batch management)
• MainWP (self-hosted management)

Final Thoughts

Proper Multisite database management requires:

1. Regular maintenance (optimization, backups)
2. Performance tuning (caching, indexing)
3. Security hardening (permissions, audits)
4. Careful monitoring (queries, resources)

For large networks (100+ sites), consider:

• Dedicated database servers
• Advanced sharding techniques
• Enterprise caching solutions