How to Set Up a WordPress Multisite with Multiple Databases for Different Sites?

When managing multiple websites using WordPress Multisite, the typical setup involves using a single database that stores data for all the sites in the network. However, there are cases where separating the databases for each site may be beneficial. This approach can help improve performance, enhance security, and make data management more efficient.

In this guide, we’ll walk you through how to set up a WordPress Multisite network with multiple databases, where each site gets its own isolated database. We will use website1.com and website2.com as examples of how to configure separate databases for each site in the network.

Why Use Multiple Databases in WordPress Multisite?

By default, WordPress Multisite uses one database to store data for all the sites on the network. But in certain scenarios, having separate databases for each site makes more sense:

Improved Performance: Sites with high traffic or complex functionality can benefit from having their own database, isolating them from other sites and reducing server load.
Enhanced Security: Separating databases ensures that sensitive data from one site is not easily accessible from another site, adding a layer of protection.
Data Compliance: In some cases, regulatory requirements (such as GDPR) may necessitate keeping data from different sites in separate databases.

With multiple databases, you gain greater control over each site’s data, making it easier to scale your network, optimize performance, and enhance security.

Before You Start: Do You Need to Create Databases First?

Yes, you must create the databases before creating the sites on your WordPress Multisite network. For each site that will have its own database, you’ll need to create the corresponding database in your hosting environment. Here’s why:

Database Creation: WordPress will need to know where to connect for each site in your network. Since you’re setting up multiple databases, you must manually create them before configuring the multisite network.
Database Credentials: You’ll need to provide WordPress with the database details (name, user, password) for each site in the network. WordPress will connect to these databases based on the site that’s being accessed.

Step-by-Step Guide: Setting Up a Multisite with Multiple Databases

We’ll walk you through setting up a WordPress Multisite network with two sites: website1.com and website2.com. Each site will have its own separate database for better performance and security.

1. Create the Databases

First, create the databases for both websites. You can do this through your hosting control panel (such as cPanel or phpMyAdmin) or by using a database management tool.

website1.com: Create a database named website1_db.
website2.com: Create a database named website2_db.

Make sure to create the corresponding database user and assign appropriate permissions for each database.

2. Set Up WordPress Multisite

Once the databases are ready, follow these steps to set up WordPress Multisite:

Install WordPress as usual, if you haven’t done so already.
Enable Multisite by editing the wp-config.php file, adding the following line:

  define('WP_ALLOW_MULTISITE', true);

After enabling Multisite, you can configure your network by going to Tools > Network Setup in the WordPress dashboard. Here, you can choose between subdomains (site1.example.com) or subdirectories (example.com/site1) for your network.

3. Configure the `wp-config.php` File for Multiple Databases

Now, we need to configure the database settings in the wp-config.php file so that WordPress connects to the correct database for each site.

Here’s how to configure the wp-config.php file for website1.com and website2.com:

// ** Database Configuration for Website 1 (website1.com) ** //
define('DB_NAME', 'website1_db');               // Database name for website1.com
define('DB_USER', 'website1_db_user');           // Database username for website1.com
define('DB_PASSWORD', 'website1_db_password');   // Database password for website1.com
define('DB_HOST', 'localhost');                  // Database host for website1.com (use 'localhost' or your custom DB host)

// ** Database Configuration for Website 2 (website2.com) ** //
define('WEBSITE2_DB_NAME', 'website2_db');       // Database name for website2.com
define('WEBSITE2_DB_USER', 'website2_db_user');  // Database username for website2.com
define('WEBSITE2_DB_PASSWORD', 'website2_db_password'); // Database password for website2.com
define('WEBSITE2_DB_HOST', 'localhost');         // Database host for website2.com (use 'localhost' or your custom DB host)

4. Switch Database Connections Based on the Site

Next, you need to ensure that WordPress connects to the correct database depending on which site is being accessed in the network. You can achieve this by dynamically switching the database connection based on the current site.

Here’s how you can switch the database connections in the functions.php file:

function switch_db_connection() {
    global $wpdb;

    // Get the current site ID
    $current_site_id = get_current_blog_id(); 

    // Switch database connections based on the site ID
    if ($current_site_id == 1) {
        // Connect to the database for website1.com
        $wpdb = new wpdb('website1_db_user', 'website1_db_password', 'website1_db', 'localhost');
    } elseif ($current_site_id == 2) {
        // Connect to the database for website2.com
        $wpdb = new wpdb('website2_db_user', 'website2_db_password', 'website2_db', 'localhost');
    } else {
        // Default database connection for other sites
        $wpdb = new wpdb('default_db_user', 'default_db_password', 'default_db', 'localhost');
    }
}
add_action('wp', 'switch_db_connection');

Explanation of the Code:

get_current_blog_id(): This function retrieves the current site ID in a WordPress Multisite network. Each site in the network has a unique ID.
Database Connection: Based on the site ID, the code switches the $wpdb object to the appropriate database connection. For example, if the site ID is 1, it will connect to the database for website1.com; if the site ID is 2, it will connect to website2.com.
wpdb Object: The $wpdb object is the core class in WordPress for interacting with the database. By redefining this object, we ensure that WordPress queries the correct database for the current site.

Additional Considerations

Performance: Keep in mind that managing multiple databases increases server load. Ensure your hosting environment can handle the additional resources required for each database.
Backup Strategy: Since each site has its own database, you’ll need to back up each database separately. Regular backups are crucial for preventing data loss.
Plugin Compatibility: Some plugins may not be compatible with a multi-database setup. Always test plugins before deploying them across your network.
Database User Permissions: Ensure that each database user has the correct permissions to manage the respective databases, and avoid giving excessive privileges to any user.

Conclusion

Setting up WordPress Multisite with multiple databases allows you to isolate your sites, providing better performance, security, and data management. By configuring each site (like website1.com and website2.com) to connect to its own database, you can ensure that your network is scalable, secure, and more optimized for high-traffic websites.

By following the steps outlined above, you’ll be able to create a WordPress Multisite network with separate databases for each site, giving you more flexibility and control over your network.

If you have any questions or need further help with this setup, feel free to reach out!