Use Wordpress MultiSite (WPMS) with a remote database for each created site

Question

So, I am interested in really figuring out a way to use WPMS with different remote databases for each new sub-site added.

What I know. I know that a remote database can be on it's own server to allow remote site connections with the DB user's added privileges with IP binding to the DB as % (wildcard) or an IP address (if your servers are part of a network).

see: https://www.digitalocean.com/community/tutorials/how-to-set-up-a-remote-database-to-optimize-site-performance-with-mysql

Or

A quicker way for me would be to use GoPanel: https://gopanel.io/ It allows me to make and configure my MySQL databases on a Linux server.

And I have tried and tested with success doing this manually or, at least getting somewhat close using the plugin, Multi-DB (https://github.com/wpmudev/multi-db).

However, Multi-DB allows separating the create site's database BUT, ONLY within that single database server. However, I would like to make a database server for each new sub-site created.

So my plan of action would be to write a simple plugin with a few text fields (DB IP, DB User, DB Password) and have these fields show in the site options area of WPMS.

And so I am looking for directions on:

A) Location or hook to site options to add my fields.

B) Where and How I can change WordPress MS default logic on how sites are tied to the database/table after created. And replace them with my field options.

Please assist.

Answer 1

By the time plugins are loaded, it's too late to change databases.

You could add a series of switch/options in your wp-config.php

switch ( $ _SERVER['HTTP_HOST'] ) {
    case 'somedomain.com': 
        define('DB_NAME', 'zzzzzz');
        define('DB_USER', 'yyyyyyy');
        define('DB_PASSWORD', 'xxxxxx');
        define('DB_HOST', 'localhost');
        break;
    case 'anotherdomain.com': 
        define('DB_NAME', 'aaaaaa');
        define('DB_USER', 'bbbbb');
        define('DB_PASSWORD', 'cccccc');
        define('DB_HOST', 'localhost');
        break;
}

But this would then not be a managed like a multisite, and you could have a lot of unexpected results because the database and files may not match (if one instance changes the files from what another instance is expecting)

I don't think I'd want to try it.