Partitioning

[mistakenCynic]

Hi, is there any way you could help me devise a very basic configuration for partitioning? Basically, I have a Wordpress users table (k2bi_users) on one database (1757) and then I have all the other 70 or so Wordpress tables (k2bi_xxx) on another database (1893). How can I set up the configuration so that it calls on the 1757 database for the users table and the other database for all the other tables?

I know it's a strange setup, and it may sound like I'm an idiot, but this is exactly what I need. Nothing else. The reason being I have a unique user registration system that can only exist on an external database, but the connection is extremely slow. That is why I want all my other tables to be on the internal database.

Thanks so much for the help! I'll be eternally grateful to figure this out 👍 .

[JJJ]

Without knowing exactly what your setup looks like, here's a back-of-the-napkin guess with some very conservative settings that you'll likely need to tune once everything is confirmed to be connecting.

• This does not include any master/slave setups.
• I assume multisite, and that signups and registration log tables coexist with users, though that may not be the case for your installation type.
• Replace the database.location/name/username/password with your server settings.
• Put this in wp-config.php or, for bonus points, somewhere outside of your WordPress directory and include this from inside of wp-config.php directly.

<?php

// Define WPDB so sniffers don't complain
global $wpdb;

// Variable settings
$wpdb->charset                  = 'utf8mb4';
$wpdb->collate                  = 'utf8mb4_unicode_ci';
$wpdb->save_queries             = false;
$wpdb->persistent               = false;
$wpdb->max_connections          = 25;
$wpdb->check_tcp_responsiveness = true;

// Servers
ldb_add_db_server( 'sites',  '', '', 1, 1, 'sites.database.location',  '', 'sites.database.name',  'sites.database.username',  'sites.database.password'  );
ldb_add_db_server( 'users',  '', '', 1, 1, 'users.database.location',  '', 'users.database.name',  'users.database.username',  'users.database.password'  );
ldb_add_db_server( 'global', '', '', 1, 1, 'global.database.location', '', 'global.database.name', 'global.database.username', 'global.database.password' );

// Tables

/**
 * Multisite blog tables are "{$base_prefix}{$blog_id}_*"
 */
function k2bi_sites_callback( $query, $wpdb ) {
	if ( preg_match( "/^{$wpdb->base_prefix}\d+_/i", $wpdb->table ) ) {
		return 'sites';
	}
}

// Table filter callbacks
$wpdb->add_callback( 'k2bi_sites_callback' );

// Explicitly add the root site tables
ldb_add_db_table( 'sites', 'wp_options'            );
ldb_add_db_table( 'sites', 'wp_posts'              );
ldb_add_db_table( 'sites', 'wp_postmeta'           );
ldb_add_db_table( 'sites', 'wp_terms'              );
ldb_add_db_table( 'sites', 'wp_term_taxonomy'      );
ldb_add_db_table( 'sites', 'wp_term_relationships' );
ldb_add_db_table( 'sites', 'wp_termmeta'           );
ldb_add_db_table( 'sites', 'wp_links'              );
ldb_add_db_table( 'sites', 'wp_comments'           );
ldb_add_db_table( 'sites', 'wp_commentmeta'        );

// Global users tables
ldb_add_db_table( 'users', 'wp_users'            );
ldb_add_db_table( 'users', 'wp_usermeta'         );
ldb_add_db_table( 'users', 'wp_signups'          );
ldb_add_db_table( 'users', 'wp_registration_log' );

// Global multi-site tables
ldb_add_db_table( 'global', 'wp_site'           );
ldb_add_db_table( 'global', 'wp_sitemeta'       );
ldb_add_db_table( 'global', 'wp_blogs'          );
ldb_add_db_table( 'global', 'wp_blog_versions'  );

[JJJ]

@mistakenCynic Any luck? I'm going to close this to clean the issues up, but I'll still reply here if need be. 🎩

[GeneralCan]

aaawwwww man! this is awesome, it's exactly what I was looking for.
@JJJ you had mentioned that what you wrote up-top was assuming a multi-site setup, (correct me if I'm wrong) I could also apply this to a setup with multiple sites on one database server (separate schemas) that share one user-base from an external database. thanks!

[JJJ]

@GeneralCan Similar, yes.

You'll want to set these constants in your various wp-config.php files first:

https://codex.wordpress.org/Editing_wp-config.php#Custom_User_and_Usermeta_Tables

Without those set, each installation will want to use it's own database tables, with their own prefixes.

Then you'll need to modify the callback with the proper regular expression to match & route your queries to their appropriate databases & servers. What's included above is a good start, and might actually work at first, but I'd suggest you probably invent something more specific to your site & server groupings.

Across WordPress.org, we usually name clusters after airport codes (lax.db, dal.db, etc...)

[GeneralCan]

hey @JJJ question for you.
I'm having an issue where the plugin tables are not showing up inside the $wpdb-tables array.
I can see them inside $wpdb->ludicrous_tables though.

this is what I have inside db-config.php:

global $prefix;
global $database;
global $wpdb;

// Users
ldb_add_db_server( 'users', '', '', 1, 1, 'DatabaseConnectionAddress', '', 'wn_userdata', 'Username', 'password'  );

// Write
ldb_add_db_server( 'site', '', '', 1, 1, 'DatabaseConnectionAddress', '', 'wp'.$database, 'Username', 'Password' );

function wn_site_callback( $query, $wpdb ) {
	global $prefix;
	if ( preg_match( "/^$prefix.*/", $wpdb->table ) ) {
		return 'site';
	}
}

$wpdb->add_callback( 'wn_site_callback' );

// Wordpress Tables
ldb_add_db_table( 'site', $prefix.'options'            );
ldb_add_db_table( 'site', $prefix.'posts'              );
ldb_add_db_table( 'site', $prefix.'postmeta'           );
ldb_add_db_table( 'site', $prefix.'terms'              );
ldb_add_db_table( 'site', $prefix.'term_taxonomy'      );
ldb_add_db_table( 'site', $prefix.'term_relationships' );
ldb_add_db_table( 'site', $prefix.'termmeta'           );
ldb_add_db_table( 'site', $prefix.'links'              );
ldb_add_db_table( 'site', $prefix.'comments'           );
ldb_add_db_table( 'site', $prefix.'commentmeta'        );

// wordfence tables
ldb_add_db_table('site', $prefix.'wfBadLeechers');
ldb_add_db_table('site', $prefix.'wfBlockedIPLog');
ldb_add_db_table('site', $prefix.'wfBlocks');
ldb_add_db_table('site', $prefix.'wfBlocksAdv');
ldb_add_db_table('site', $prefix.'wfConfig');
ldb_add_db_table('site', $prefix.'wfCrawlers');
ldb_add_db_table('site', $prefix.'wfFileMods');
ldb_add_db_table('site', $prefix.'wfHits');
ldb_add_db_table('site', $prefix.'wfHoover');
ldb_add_db_table('site', $prefix.'wfIssues');
ldb_add_db_table('site', $prefix.'wfKnownFileList');
ldb_add_db_table('site', $prefix.'wfLeechers');
ldb_add_db_table('site', $prefix.'wfLockedOut');
ldb_add_db_table('site', $prefix.'wfLocs');
ldb_add_db_table('site', $prefix.'wfLogins');
ldb_add_db_table('site', $prefix.'wfNet404s');
ldb_add_db_table('site', $prefix.'wfNotifications');
ldb_add_db_table('site', $prefix.'wfPendingIssues');
ldb_add_db_table('site', $prefix.'wfReverseCache');
ldb_add_db_table('site', $prefix.'wfScanners');
ldb_add_db_table('site', $prefix.'wfSNIPCache');
ldb_add_db_table('site', $prefix.'wfStatus');
ldb_add_db_table('site', $prefix.'wfThrottleLog');
ldb_add_db_table('site', $prefix.'wfVulnScanners');

// woocommerce tables
ldb_add_db_table('site', $prefix.'woocommerce_api_keys');
ldb_add_db_table('site', $prefix.'woocommerce_attribute_taxonomies');
ldb_add_db_table('site', $prefix.'woocommerce_bundled_itemmeta');
ldb_add_db_table('site', $prefix.'woocommerce_bundled_items');
ldb_add_db_table('site', $prefix.'woocommerce_downloadable_product_permissions');
ldb_add_db_table('site', $prefix.'woocommerce_log');
ldb_add_db_table('site', $prefix.'woocommerce_order_itemmeta');
ldb_add_db_table('site', $prefix.'woocommerce_order_items');
ldb_add_db_table('site', $prefix.'woocommerce_payment_tokenmeta');
ldb_add_db_table('site', $prefix.'woocommerce_payment_tokens');
ldb_add_db_table('site', $prefix.'woocommerce_sessions');
ldb_add_db_table('site', $prefix.'woocommerce_shipping_zone_locations');
ldb_add_db_table('site', $prefix.'woocommerce_shipping_zone_methods');
ldb_add_db_table('site', $prefix.'woocommerce_shipping_zones');
ldb_add_db_table('site', $prefix.'woocommerce_tax_rate_locations');
ldb_add_db_table('site', $prefix.'woocommerce_tax_rates');

// Global users tables
ldb_add_db_table( 'users', 'wn_users'            );
ldb_add_db_table( 'users', 'wn_usermeta'         );

so what I'm seeing is that wordpress 'sees' all it's tables fine, however, plugins like woocommerce and wordfence cannot 'see' their tables.

The sites share the user base, so that's awesome, but that's about it.

any help would be greatly appreciated!

Thanks!

[JJJ]

Maybe a typo in your regular expression?

"/^$prefix.*/"

Should maybe be...

"/^$prefix*/"

This assumes that $prefix doesn't include a period as a prefix delineator.