Multi-Database Step by step Installation

Step by step installation Guide for Multi DB

Introduction

When I first read about Multi DB I was lured by the fact that it could streamline and break my 10,000 Table Single wp-freeblog Database into many such as 16, 256, 4096, databases, “yeah I thought, Millions of Blogs!”.

Whooa down, lets get real you need to be realistic about where you will be in the Future I personally think 256 Databases is a good starting Place lets face it if you get that many you will probably be making some OK money or at least be Making enough to keep developing your Platform Ok pic a number

1. 16 Databases Up to 10,000 BLOGS
2. 256 Databases Up to 50,000 to 100,000 Blogs
3. 4096 Database Up to 4,000,000 Blogs

Step 1

The first thing to do is try to create your databases now this is either Straight Forward or not depending on your server restrictions, if there are restrictions then you will possibly need to change hosts or get the server Folk to CREATE the databases and GRANT PRIVILEGES to your account for these Databases

How I Create Databases thru the Command Line. (Building This now)

Go Now and Create your Databases done then Come Back here when you have got this sorted, As we cannot move on untill its done.

[ad#post-ad]

Step 2

Get the latest Multi DB Plugin  HERE and Unpack it on your own Local Machine

This is what should be inside  see the contents of this Folder minus the Zip folder,  the install notes here will only help you  put the files and Folders in the right directories which is a little dangerous Initially,  I actually Broke My site by uploading the Wrong file out of sequence so listen up

Below is the contents of the Multi DB Plugin

Step 3

OK  So you Now have your down Load  its unzipped on your local machine we are cranking

Now you need to take it easy here no rush load these files into  the appropriate folders

Firstly create a folder Called  wp-content/scripts/ then load the following files

list-hashes.php – goes in /wp-content/scripts/
move-blogs.php – goes in /wp-content/scripts/

Add these files into these directories

db.php - goes in   /wp-content
db-config.php – goes in /wp-content/

Make sure that you do not FTP upload these yet, we need to groom them first!

Step 4

Ok lets establish which files need be edited

You do not need to edit the files below

• db.php
• list-hashes
• server-ip.php

The files you will need to edit

• move-blogs.php
• db-config.php

Step 5

Lets Get editing this is the area we need to take care saves a lot of time

1. move-blogs.php – which sits in /wp-content/scripts/move-blogs.php

The only parts you will need to edit of  move-blogs.php are below

$dbname ( your_current _database )
$blog_table_prefix ( wp_ %99 chance this wont need changing )
$newdb_prefix ( mynewprefix_ this is the prefix to your new 16, 256, 4096 databases )

Then you need to change the database connection details such as below

$dbhost = ‘localhost‘;     this is probably fine no need to change
$dbuname = ‘username‘;  this is your Business
$dbpass = ‘password‘;  this is your Business

$db_scaling = ‘256‘; this is where you put the amount of Databases

Here is part of the move-blogs.php file

/*
Plugin Name: Multi-DB (Conversion Tool)
Plugin URI: http://XXXXXXXXXXXXXX/project/Multiple-Databases
Description:
Author: Ron Dillehay - FanMail to rdillehay@hotmail.com (no tech support please) - Modified slightly by Andrew Billits (Incsub)
Version: 2.6.0
Author URI:
Descriptiom:
This script is to move blogs from a wordpress mu single database setup, to a wordpress mu multiple database setup using an MD5 hash to establish blog routing
Use this script at your own risk. My test setup uses php 5, mysql 5, and wordpress mu 1.2.5a
*/

//————————————————————————//
//—Config—————————————————————//
//————————————————————————//

///DB Settings
$dbname = “your_current _database“; //This is your current database
$blog_table_prefix = ‘wp_‘; //Prefix of your wpmu blog tables, most likely this won’t need to be changed
$newdb_prefix = ‘mynewprefix_‘; //This is the prefix of the db’s you’re moving your tables into – we assume they are all the same, if not, you’re in trouble

//We need info to connect to the databases
$dbhost = ‘localhost‘; this is probably fine
$dbuname = ‘username‘;
$dbpass = ‘password‘;

//How many db’s are you moving into (16, 256, or 4096)?
$db_scaling = ‘256‘;

//————————————————————————//
?>

Step 6

We have to edit and  correct the move-blogs.php (which will throw you if your not ready)

Note**  there is a typo in this file which still hasn’t been corrected YET its to do with line 83 and line 85 instead of a hyphen - there is an underscore  _  between move-blogs.php

This is the original part  of move-blogs.php file that is incorrect

//////////////////////////////////////////////////////////////////////////////////////////////

line 79 echo “<table align=’center’ class=’stats’><tr><td colspan=’4′ align=’center’><br /><ol>
line 80   <li>Tested on PHP 5 & MySQL 5</li>
line 81   <li>Make sure all of your new db’s exist (green text next to table name in db column below)</li>
line 82   <li>In the status section, each table should show <i>not in new db (unless you’ve already run this script)   </i></li>
line 83 <li>To start the copy process <a href=’move_blogs.php?table=copy’>click here</a> </li>
line 84  <li>Be patient, depending on how many blogs you have, this could take a while</li>
line 85 <li>Once completed, refresh this page by <a href=’move_blogs.php‘>clicking here</a></li>
line 86  <li>Check to make sure that in the status section all tables say <i>table in new db</i></li>
line 87  <li>Rejoice, I probably just saved you hours of your life!</li>
line 88  <li>If this didn’t work, don’t blame me.  Sometimes life is just like that..</li></ol>
line 89  </td></tr><tr><td class=’head’ width=’25%’>table name</td><td class=’head’ width=’25%’>table info</td><td class=’head’ width=’25%’>new db</td><td class=’head’ width=’25%’>status</td></tr>”;

/////////////////////////////////////////////////////////////////////////////////////////////

This is the corrected original file move-blogs.php

line 79 echo “<table align=’center’ class=’stats’><tr><td colspan=’4′ align=’center’><br /><ol>
line 80   <li>Tested on PHP 5 & MySQL 5</li>
line 81   <li>Make sure all of your new db’s exist (green text next to table name in db column below)</li>
line 82   <li>In the status section, each table should show <i>not in new db (unless you’ve already run this script)   </i></li>
line 83 <li>To start the copy process <a href=’move-blogs.php?table=copy’>click here</a> </li>
line 84  <li>Be patient, depending on how many blogs you have, this could take a while</li>
line 85 <li>Once completed, refresh this page by <a href=’move-blogs.php‘>clicking here</a></li>
line 86  <li>Check to make sure that in the status section all tables say <i>table in new db</i></li>
line 87  <li>Rejoice, I probably just saved you hours of your life!</li>
line 88  <li>If this didn’t work, don’t blame me.  Sometimes life is just like that..</li></ol>
line 89  </td></tr><tr><td class=’head’ width=’25%’>table name</td><td class=’head’ width=’25%’>table info</td><td class=’head’ width=’25%’>new db</td><td class=’head’ width=’25%’>status</td></tr>”;

//////////////////////////////////////////////////////////////////////////////////////////////

When you get this working Its a thing of Beauty and Saves you Heaps of time

Thats it for now do not FTP this file  ACROSS TILL I say !!!

Step7

Now the  Next Most Important Part which is setting Up the db-config.php

Multi DB Configuratrion this is based on my set up so you may have less Global tables to add or more depending on your Installed plugins

There are Four areas of editing here

DB SCALING >>> THIS IS WHERE WE STIPULATE THE AMOUNT OF DATABASES YOU HAVE

////////////////————————–////////////////

//   E.G  16, 256, 4096
define (‘DB_SCALING’, ’256′);

DC IPs  >>> THIS IS THE SERVER IP ADDRESS OF YOUR WEBSITE HOST

////////////////————————–////////////////

//    Usage: add_dc_ip(IP, DC)
//    EX: add_dc_ip(’123.123.123.’, ‘dc1′);
add_dc_ip(’127.127.127.25′, ‘dc1′);  this is where you
GLOBAL TABLES >>> THESE ARE YOUR GLOBAL TABLES

These are an Example of my global tables you must use your own as they will be different

///////////////--------------------------////////////////

// Do not include default global tables PREFIX as in  Leave off base prefix (eg: wp_)

//    Usage: add_global_table(TABLE_NAME)
//    EX: add_global_table(‘something‘);
add_global_table(‘blogs’);
add_global_table(‘blogs_cets_topics’);
add_global_table(‘blogs_cets_topics_relationships’);
add_global_table(‘blog_activity’);
add_global_table(‘blog_types’);
add_global_table(‘blog_versions’);
add_global_table(‘comment_activity’);
add_global_table(‘mass_mailer’);
add_global_table(‘post_activity’);
add_global_table(‘registration_log’);
add_global_table(‘signups’);
add_global_table(‘wsignup_blog_types’);
add_global_table(‘site’);
add_global_table(‘sitecategories’);
add_global_table(‘sitemeta’);
add_global_table(‘spam’);
add_global_table(‘support_faq’);
add_global_table(‘support_faq_cats’);
add_global_table(‘support_tickets’);
add_global_table(‘support_tickets_cats’);
add_global_table(‘support_tickets_messages’);
add_global_table(‘wusermeta’);
add_global_table(‘users’);
add_global_table(‘user_activity’);

Go in thru your server end and get into the Database that way

You can get all the global Tables from your Current single WPMU Database wp_global be as thorough as you can take your Time and get it right

These are the CONNECTION DETAILS FOR LOCATION OF DATABASE  these need be correct

Multi DB Configuratrion Go here and have another Lookat my config file

Create your Database SQL HERE this is a great tool the easy way to create the databases

// Usage: add_db_server(DS, DC, READ, WRITE, HOST, LAN_HOST, NAME, USER, PASS)
// EX: add_db_server('global', 'dc1', 1, 1,'global.mysql.example.com:3509','global.mysql.example.lan:3509', 'global-db', 'globaluser', 'globalpassword');
//
// Note: you can also place this section in a file called db-list.php in wp-content

add_db_server('global', 'dc1', 1, 1,'127.0.0.1','63.247.133.25', 'freeblog_global', 'someuser', 'xxxx');

add_db_server(’00′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_00′, ‘someuser’, ‘xxxx’);
add_db_server(’01′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_01′, ‘someuser’, ‘xxxx’);
add_db_server(’02′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_02′, ‘someuser’, ‘xxxx’);
add_db_server(’03′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_03′, ‘someuser’, ‘xxxx’);
add_db_server(’04′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_04′, ‘someuser’, ‘xxxx’);
add_db_server(’05′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_05′, ‘someuser’, ‘xxxx’);
add_db_server(’06′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_06′, ‘someuser’, ‘xxxx’);
add_db_server(’07′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_07′, ‘someuser’, ‘xxxx’);
add_db_server(’08′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_08′, ‘someuser’, ‘xxxx’);
add_db_server(’09′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_09′, ‘someuser’, ‘xxxx’);
add_db_server(’0a’, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_0a’, ‘someuser’, ‘xxxx’);
add_db_server(’0b’, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_0b’, ‘someuser’, ‘xxxx’);
add_db_server(’0c’, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_0c’, ‘someuser’, ‘xxxx’);
add_db_server(’0d’, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_0d’, ‘someuser’, ‘xxxx’);
add_db_server(’0e’, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_0e’, ‘someuser’, ‘xxxx’);
add_db_server(’0f’, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_0f’, ‘someuser’, ‘xxxx’);
add_db_server(’10′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_10′, ‘someuser’, ‘xxxx’);
add_db_server(’11′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_11′, ‘someuser’, ‘xxxx’);
add_db_server(’12′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_12′, ‘someuser’, ‘xxxx’);
add_db_server(’13′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_13′, ‘someuser’, ‘xxxx’);
add_db_server(’14′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_14′, ‘someuser’, ‘xxxx’);
add_db_server(’15′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_15′, ‘someuser’, ‘xxxx’);
add_db_server(’16′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_16′, ‘someuser’, ‘xxxx’);
add_db_server(’17′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_17′, ‘someuser’, ‘xxxx’);
add_db_server(’18′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_18′, ‘someuser’, ‘xxxx’);
add_db_server(’19′, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_19′, ‘someuser’, ‘xxxx’);
add_db_server(’1a’, ‘dc1′, 1, 1,’127.0.0.1′,’63.247.133.25′, ‘freeblog_1a’, ‘someuser’, ‘xxxx’);

OK you done yet does your Multi DB Configuratrion look like this great lets get to the exciting bit, I bet there is a little bit of paranoia  in the back of your mind follow it and double check!

Step 8

So here we are standing at the Abyss ready to take the Plunge Ok lets do it…

FTP move-blogs.php into wp-content/scripts/move-blogs.php

then Open a browser and navigate to www.yoursite.com/wp-content/scripts/move-blogs.php

you should see this Image below.

OK no time for hesitation go to step 4 which says..

4. To start the Copy process click here this is Just a dummy link on this site for your benefit

Ok sit Back its going to take a wee while depending on the size of your database and you will see some error output but do not Panic its Part of the process

see image below

after process is completed refresh your Browser and you should see this BELOW

Go get your self a drink and feel happy with yourself you just moved all of your blogs in to  256 Databases (or how ever many you where going for)sit back and revel for a second you did good

Oh and thank you Andrew for you great script

Step 9

Ok Now is when you put or FTP the rest of your files across

db.php - goes in   /wp-content
db-config.php – goes in /wp-content/

these files will take over from your DB CONFIG FILES by default you do not need to change any thing in wp-config.php

Well actually I did just to prove that I was using the new Multi database setup  by commenting out the second line defining the Database name //define(‘DB_NAME’, ‘myold_singledatabase’);

define('WP_CACHE', true); //Added by WP-Cache Manager
//define('DB_NAME', 'myold_singledatabase');    I commented this line out
define('DB_USER', 'username');     // Your MySQL username
define('DB_PASSWORD', 'Password'); // ...and password
define('DB_HOST', 'localhost');    // 99% chance you won't need to change this value
define('DB_CHARSET', 'utf8');
define('DB_COLLATE', '');
define('VHOST', 'yes');
$base = '/';

Also there is one other part in this config which you just leave alone I’ve seen a few people get hung up on it so don’t!

define( "WP_USE_MULTIPLE_DB", false );  no need to touch this at all

Now also Something else is how the hell do you find where blogs are in 256 Databases easy

First thing is to get the Blog Id and enter it here using this MD5 Hash tool say if i put  blog id  2 into the submit box  the result is this

c81e728d9d4c2f636f067f89cc14862c The relevant parts of this result are the first 2 or three Numbers

Now go to database c8 and I bet your blog Id 2 tables are there, Magic aye and much faster then tryng to skim 10000 tables

So good Luck if you dont uderstand this or follow it Im sorry let me know where the problem is and I try to clarify it

Regards Bill Bailey