Wordpress multisite with different databases
How did I even get to the multisite mode? The thing is that if you create some more complex functionality on the engine using plug-ins, which will include the possibility of several diverse sites, its performance will be terrible.
Everywhere they add their js / css and where they are not needed. Thus, if you install several powerful heavy plug-ins and another + 10-15 additional smaller ones, you can see a huge number of scripts and styles everywhere on almost every page.
The solution to this was found - the multisite mode .
Connect it is not difficult and you can start using it in a couple of minutes.
')
To do this, add a line in wp-config.php
After that, go to Tools -> Network Installation.
It must be performed with plugins turned off.
You will be prompted to replace the data in wp-config and .htaccess and log in again on the site.
Site Modes only 2 - this is either a folder / or subdomains.
site.ru / site2
website2.site.ru
One table of general users - when registering for any of them, they will be able to enter all the rest as well, so they will not have to register several times.
Manage plugins and themes for all sites from the panel. You can add the necessary for all in one place and further activate them only on those where they are needed separately. It is also possible to activate them at once for the entire network.
Thus, it is possible to reduce the consumption of plug-ins by 50% or more. It seems to be all right, everything is already in order with the front-end, but the trouble is:
multisite uses one base for all sites - creating separate tables for each of them.
wp_
wp_2
wp_3
Now one base will increase several times from each new site.
For this we will use the multi-db plugin. Of course, there are others, such as HyperDB, SharDB, but I have not found any information about them. You can download multi-db from any link below.
cloud.mail.ru/public/KMNr/WDpoUeyUV
drive.google.com/open?id=0Bw4XcI3lCfQSUWZWLUUyUHZFVTA
He himself offers to first decide how many bases you need (16, 256, 4096).
I'll start with 16. Important sequence. First install multisite, I will start sharing databases, having 3 sites created - in order to test it afterwards.
This option allows you to place a specific site in a specific database. I don't need it. I will not touch it. It does not matter to me, in which, the main thing is that in any other, and not in one.
Run this sql query and then you will see them.
Since I'm on Localhoste, I don't need this. It will be suitable for root, but you may need to create new names and passwords, as well as give the necessary privileges if you make the site not for yourself alone, but, for example, for several people. Therefore, I will skip this paragraph.
db-config.php, db.php need to be placed in wp-content
move-blogs.php in any folder after wp-content, for example in the new / wp-content / scripts /
DB-config.php
Here you need to specify the number of databases, 16 in this case and server ip. At the end instead of the last digits put the point 127.0.0.
Further below in the first place we will add the global table add_global_table ('dbname_global');
After that, it will remain to set the login and password to log into mysql:
login root, password 1. Also here you need to specify these names that we registered at the beginning: dbname_0, dbname_1 ...
This is the most important part. It also forms the output of various errors.
$ dbname = "multi"; // multi is the name of my shared database that I want to split into pieces, I need to write without a prefix
$ blog_table_prefix = 'wp_'; // this is a prefix in the common database
$ newdb_prefix = 'dbname_'; //new name
Login, server and password, as well as the number of databases.
$ dbhost = 'localhost';
$ dbuname = 'root';
$ dbpass = '1';
$ db_scaling = '16';
Now it’s worth going to: localhost / wp-content / scripts / move-blogs.php
Click Click here .
We are waiting for the process to end, the link in the address bar should change. After that click clicking here to update the page. Status updated.
Now you can see that one global database has been created, which contains wp_users, etc.
The other two sites besides the main one scattered around other databases in a random order, for example, the second site got into dbname_c, and the third got into dbname_e.
Now you need to check what happened. You can try, for example, write a post to several sites and check where they will be displayed. And indeed, in the new and global entries appear.
Nothing is added to the old multi. Then, probably, that's all. So far, these are only the first opinions about this system; it is still early to draw conclusions.
The first is the inability to define roles for new registered participants. But for this there is a plugin . In addition, let us imagine a situation, you have two sites - site 1 (main) and site 2. A person registers on site 2 and he can now enter and be displayed on site 1, but you don’t see it from site console 1, despite that he is. You can see it either in the general network or on site 2, from which it made the registration.
If you want to register via wp-login, wp-admin, wp-register from site 2, then you will be transferred to site 1. As the multisite developers say, this is logical, since the account is created one for the entire network. But it can be inconvenient and not at all what you need. WooCommerce with its My Account page and shortcode [woocommerce_my_account] helps with normal registration, you can also use Login With Ajax.
Ps It turns out you can easily and combine styles / scripts in plug-ins and include only on certain pages. Multi-db laid out on githaba, did not immediately see.
premium.wpmudev.org/forums/topic/multi-db-plugin-conlict-with-woocomerce
premium.wpmudev.org/forums/topic/db-error-could-not-list-tables-1
github.com/wpmudev/multi-db
premium.wpmudev.org/forums/topic/re-multi-db-plugin
maxtop.org/wordpress-otklyuchenie-skriptov
maxtop.org/wordpress-obedinenie-css-fajlov-stilej-otklyuchenie-zagruzki-lishnix-css-fajlov
bavatuesdays.com/wpmu-multi-db-tutorial
www.inmotionhosting.com/support/website/wordpress/heartbeat-ajax-php-usage
Maybe someone else will have questions the same.
In the literal sense of consumption of plug-ins. Suppose you have one site that uses 20-30 plug-ins.
With multisite, you divide it into 2 pieces. Thus, each of its parts uses 10-15 plug-ins separately. For example:
site.ru is a bulletin board or catalog
site.ru / shop / or shop.sayt.ru is a shop inside the site.
site.ru uses some 10-15 plugins
site.ru / shop / uses other 10-15 plugins
At the same time, you do not need to worry about users and about the admin panel, since you manage these sites through the same admin panel and users are also shared.
This is a valid number of databases in the plugin. If you have a million sites in the multisite network - then they will be filled evenly across these databases, in this case it would be better to choose 4096 of course. For this, read the wp-forums. I have not tested a million sites.
But without this plugin, all these million sites will be contained in one single database.
The answer is above. You need to initially figure out how much you need them depending on the number of sites. Using multi-db is a step in optimizing the database for those who already have a lot of sites in multisite mode.
In order to save js / css to cut the base - there is no logic. The base slice is the second stage after js / css optimization. He has no relation to them.
When there are a lot of plug-ins on the site (considering that there is a lot of them in the site itself, there is a lot of template) then the question arises of how to differentiate all of this for different parts of the site. A large number of them acts badly on the admin panel, it begins to slow down.
Multisite and without a cut in the base solves the question of the plugins themselves so that there are fewer of them on each site or piece of the site.
Since the multi-db plugin itself is written off and is not supported - there is still another, the best solution and convenient - this is SharDB
Working with him is much easier than multi-db. And after a more thorough check, I discovered that multi-db is buggy with some plugins, and this shar-db does not cause any problems with them.
First you need to unpack the db-settings.php file to the root of the site so that wp-config.php is at the same level with it and connect it to it
Its settings
set is the name of the database containing all sites.
Next, copy the file shardb-admin.php to the folder with plugins, as usual, activate this plugin for the network.
Then in the admin go to the settings of the plugin. Network Management ---> Settings ---> SharDB Migration.
And here the first step is Migrate Global Tables. He will offer to write it in a separate table - in setglobal
Next, the first site
If the base was called “set”, then it offers to move it to sethome
Everything is copied normally, you can copy further all sites, "Migrate Sites"
And so on ... the numbering of sites and theirs tables are displayed on the screen. The names of the tables in which the plugin wants to write are also visible.
After everything is in place, you need to unpack the db.php file in the wp-content folder
Now, in the admin panel, you can see the status of those sites that I, for example, have not copied, and the desired tables with new names for each of them are visible there.
Connection failed - this message displays db.php. If this file is in your wp-content, by default it tries to connect to different databases separately, and not to one common one. If you move all other sites on the same principle - they will also work.
To copy the databases, you should not have the db.php file, otherwise the settings item in the admin panel (SharDB Migration) will not be available, after copying, you need to load it last. It serves as a simple link.
When creating a new subsite, you need to delete it for this time or rename the db.php file and then optionally separate it into another database using the same method.
This measure may not matter to you if you have a few sites. But the base of the engine itself tends to become clogged over time, and slow down the response from the site. If this happens for one site and one base, then having multisite mode it will be tenfold + in size since all these site tables will be in one place.
This is another possibility of the engine which in some cases can be used.
The second method with shardb is more preferable, added below.
Everywhere they add their js / css and where they are not needed. Thus, if you install several powerful heavy plug-ins and another + 10-15 additional smaller ones, you can see a huge number of scripts and styles everywhere on almost every page.
The solution to this was found - the multisite mode .
Connect it is not difficult and you can start using it in a couple of minutes.
')
To do this, add a line in wp-config.php
/* , . ! */ define('WP_ALLOW_MULTISITE', true); After that, go to Tools -> Network Installation.
It must be performed with plugins turned off.
You will be prompted to replace the data in wp-config and .htaccess and log in again on the site.
Multisite system
Site Modes only 2 - this is either a folder / or subdomains.
site.ru / site2
website2.site.ru
One table of general users - when registering for any of them, they will be able to enter all the rest as well, so they will not have to register several times.
Manage plugins and themes for all sites from the panel. You can add the necessary for all in one place and further activate them only on those where they are needed separately. It is also possible to activate them at once for the entire network.
Thus, it is possible to reduce the consumption of plug-ins by 50% or more. It seems to be all right, everything is already in order with the front-end, but the trouble is:
multisite uses one base for all sites - creating separate tables for each of them.
wp_
wp_2
wp_3
Now one base will increase several times from each new site.
Multisite c separate database for each site
For this we will use the multi-db plugin. Of course, there are others, such as HyperDB, SharDB, but I have not found any information about them. You can download multi-db from any link below.
cloud.mail.ru/public/KMNr/WDpoUeyUV
drive.google.com/open?id=0Bw4XcI3lCfQSUWZWLUUyUHZFVTA
He himself offers to first decide how many bases you need (16, 256, 4096).
I'll start with 16. Important sequence. First install multisite, I will start sharing databases, having 3 sites created - in order to test it afterwards.
Creating vip databases
This option allows you to place a specific site in a specific database. I don't need it. I will not touch it. It does not matter to me, in which, the main thing is that in any other, and not in one.
The first step is to prepare new bases.
CREATE DATABASE `dbname_0` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_1` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_2` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_3` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_4` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_5` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_6` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_7` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_8` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_9` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_a` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_b` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_c` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_d` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_e` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_f` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci; CREATE DATABASE `dbname_global` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Run this sql query and then you will see them.
Creating users and passwords in phpMyAdmin
Since I'm on Localhoste, I don't need this. It will be suitable for root, but you may need to create new names and passwords, as well as give the necessary privileges if you make the site not for yourself alone, but, for example, for several people. Therefore, I will skip this paragraph.
Unpacking files
db-config.php, db.php need to be placed in wp-content
move-blogs.php in any folder after wp-content, for example in the new / wp-content / scripts /
Configuring Plugins
DB-config.php
define ('DB_SCALING', '16'); //------------------------------------------------------------------------// //---DC IPs---------------------------------------------------------------// //------------------------------------------------------------------------// // Usage: add_dc_ip(IP, DC) // EX: add_dc_ip('123.123.123.', 'dc1'); add_dc_ip('127.0.0.', 'dc1');
Here you need to specify the number of databases, 16 in this case and server ip. At the end instead of the last digits put the point 127.0.0.
Further below in the first place we will add the global table add_global_table ('dbname_global');
//------------------------------------------------------------------------// //---Global Tables--------------------------------------------------------// //------------------------------------------------------------------------// // Do not include default global tables // Leave off base prefix (eg: wp_) // You don't really have to register these, they will work fine without. // However registering at least your busiest ones might shave a few milliseconds off by avoiding some regexes. // // Usage: add_global_table(TABLE_NAME) // EX: add_global_table('something'); add_global_table('dbname_global'); add_global_table('affiliatedata'); add_global_table('affiliatereferrers'); add_global_table('am_actions'); add_global_table('am_queue'); add_global_table('am_schedule'); add_global_table('autoblog'); add_global_table('bp_activity'); add_global_table('bp_activity_meta'); add_global_table('bp_friends'); add_global_table('bp_groups'); add_global_table('bp_groups_groupmeta'); add_global_table('bp_groups_members'); add_global_table('bp_messages_messages'); add_global_table('bp_messages_notices'); add_global_table('bp_messages_recipients'); add_global_table('bp_notifications'); add_global_table('bp_user_blogs'); add_global_table('bp_user_blogs_blogmeta'); add_global_table('bp_xprofile_data'); add_global_table('bp_xprofile_fields'); add_global_table('bp_xprofile_groups'); add_global_table('domain_mapping');
After that, it will remain to set the login and password to log into mysql:
//------------------------------------------------------------------------// //---DB Servers-----------------------------------------------------------// //------------------------------------------------------------------------// // Database servers grouped by dataset. // R can be 0 (no reads) or a positive integer indicating the order // in which to attempt communication (all locals, then all remotes) // // 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 // EX: add_db_server('global', 'dc1', 1, 1,'global.mysql.example.com:3509','global.mysql.example.lan:3509', 'global-db', 'globaluser', 'globalpassword'); add_db_server('global', 'dc1', 1, 1,'localhost','localhost', 'dbname_global', 'root', '1'); add_db_server('0', 'dc1', 1, 1,'localhost','localhost', 'dbname_0', 'root', '1'); add_db_server('1', 'dc1', 1, 1,'localhost','localhost', 'dbname_1', 'root', '1'); add_db_server('2', 'dc1', 1, 1,'localhost','localhost', 'dbname_2', 'root', '1'); add_db_server('3', 'dc1', 1, 1,'localhost','localhost', 'dbname_3', 'root', '1'); add_db_server('4', 'dc1', 1, 1,'localhost','localhost', 'dbname_4', 'root', '1'); add_db_server('5', 'dc1', 1, 1,'localhost','localhost', 'dbname_5', 'root', '1'); add_db_server('6', 'dc1', 1, 1,'localhost','localhost', 'dbname_6', 'root', '1'); add_db_server('7', 'dc1', 1, 1,'localhost','localhost', 'dbname_7', 'root', '1'); add_db_server('8', 'dc1', 1, 1,'localhost','localhost', 'dbname_8', 'root', '1'); add_db_server('9', 'dc1', 1, 1,'localhost','localhost', 'dbname_9', 'root', '1'); add_db_server('a', 'dc1', 1, 1,'localhost','localhost', 'dbname_a', 'root', '1'); add_db_server('b', 'dc1', 1, 1,'localhost','localhost', 'dbname_b', 'root', '1'); add_db_server('c', 'dc1', 1, 1,'localhost','localhost', 'dbname_c', 'root', '1'); add_db_server('d', 'dc1', 1, 1,'localhost','localhost', 'dbname_d', 'root', '1'); add_db_server('e', 'dc1', 1, 1,'localhost','localhost', 'dbname_e', 'root', '1'); add_db_server('f', 'dc1', 1, 1,'localhost','localhost', 'dbname_f', 'root', '1');
login root, password 1. Also here you need to specify these names that we registered at the beginning: dbname_0, dbname_1 ...
Configuring move-blogs.php
This is the most important part. It also forms the output of various errors.
$dbname = "multi"; //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 = 'dbname_'; //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'; $dbuname = 'root'; $dbpass = '1'; //How many db's are you moving into (16, 256, or 4096)? $db_scaling = '16';
$ dbname = "multi"; // multi is the name of my shared database that I want to split into pieces, I need to write without a prefix
$ blog_table_prefix = 'wp_'; // this is a prefix in the common database
$ newdb_prefix = 'dbname_'; //new name
Login, server and password, as well as the number of databases.
$ dbhost = 'localhost';
$ dbuname = 'root';
$ dbpass = '1';
$ db_scaling = '16';
Now it’s worth going to: localhost / wp-content / scripts / move-blogs.php
Click Click here .
We are waiting for the process to end, the link in the address bar should change. After that click clicking here to update the page. Status updated.
Now you can see that one global database has been created, which contains wp_users, etc.
The other two sites besides the main one scattered around other databases in a random order, for example, the second site got into dbname_c, and the third got into dbname_e.
Now you need to check what happened. You can try, for example, write a post to several sites and check where they will be displayed. And indeed, in the new and global entries appear.
Nothing is added to the old multi. Then, probably, that's all. So far, these are only the first opinions about this system; it is still early to draw conclusions.
Nuances of multisite
The first is the inability to define roles for new registered participants. But for this there is a plugin . In addition, let us imagine a situation, you have two sites - site 1 (main) and site 2. A person registers on site 2 and he can now enter and be displayed on site 1, but you don’t see it from site console 1, despite that he is. You can see it either in the general network or on site 2, from which it made the registration.
Registration Redirect
If you want to register via wp-login, wp-admin, wp-register from site 2, then you will be transferred to site 1. As the multisite developers say, this is logical, since the account is created one for the entire network. But it can be inconvenient and not at all what you need. WooCommerce with its My Account page and shortcode [woocommerce_my_account] helps with normal registration, you can also use Login With Ajax.
More on topic
Ps It turns out you can easily and combine styles / scripts in plug-ins and include only on certain pages. Multi-db laid out on githaba, did not immediately see.
premium.wpmudev.org/forums/topic/multi-db-plugin-conlict-with-woocomerce
premium.wpmudev.org/forums/topic/db-error-could-not-list-tables-1
github.com/wpmudev/multi-db
premium.wpmudev.org/forums/topic/re-multi-db-plugin
maxtop.org/wordpress-otklyuchenie-skriptov
maxtop.org/wordpress-obedinenie-css-fajlov-stilej-otklyuchenie-zagruzki-lishnix-css-fajlov
bavatuesdays.com/wpmu-multi-db-tutorial
www.inmotionhosting.com/support/website/wordpress/heartbeat-ajax-php-usage
Answers on questions
Maybe someone else will have questions the same.
By consumption is meant the consumption of space? Why only 50%?
In the literal sense of consumption of plug-ins. Suppose you have one site that uses 20-30 plug-ins.
With multisite, you divide it into 2 pieces. Thus, each of its parts uses 10-15 plug-ins separately. For example:
site.ru is a bulletin board or catalog
site.ru / shop / or shop.sayt.ru is a shop inside the site.
site.ru uses some 10-15 plugins
site.ru / shop / uses other 10-15 plugins
At the same time, you do not need to worry about users and about the admin panel, since you manage these sites through the same admin panel and users are also shared.
(16, 256, 4096) Brad.
This is a valid number of databases in the plugin. If you have a million sites in the multisite network - then they will be filled evenly across these databases, in this case it would be better to choose 4096 of course. For this, read the wp-forums. I have not tested a million sites.
But without this plugin, all these million sites will be contained in one single database.
define ('DB_SCALING', '16'); - And if then there will be 32 bases, then everything will go? :)
The answer is above. You need to initially figure out how much you need them depending on the number of sites. Using multi-db is a step in optimizing the database for those who already have a lot of sites in multisite mode.
Saved on css / js, and took the cut base. I do not see the logic. :)
In order to save js / css to cut the base - there is no logic. The base slice is the second stage after js / css optimization. He has no relation to them.
When there are a lot of plug-ins on the site (considering that there is a lot of them in the site itself, there is a lot of template) then the question arises of how to differentiate all of this for different parts of the site. A large number of them acts badly on the admin panel, it begins to slow down.
Multisite and without a cut in the base solves the question of the plugins themselves so that there are fewer of them on each site or piece of the site.
More good way
Since the multi-db plugin itself is written off and is not supported - there is still another, the best solution and convenient - this is SharDB
Working with him is much easier than multi-db. And after a more thorough check, I discovered that multi-db is buggy with some plugins, and this shar-db does not cause any problems with them.
First you need to unpack the db-settings.php file to the root of the site so that wp-config.php is at the same level with it and connect it to it
/* , . ! */ ... ... ... ... ... ... require_once('db-settings.php');
Its settings
// If you have multiple datacenters you can come up with your own datacenter // detection logic (php_uname?). This helps ensure the web servers try to // connect to the nearest database servers first, then distant ones. define( 'DATACENTER', '' ); function add_slave($read, $host, $lhost = '', $user = DB_USER, $password = DB_PASSWORD) { global $slaves; $slaves[] = compact('read', 'host', 'lhost', 'user', 'password'); } /* Add your configuration here */ // how many characters of hexidecimal hash $shardb_hash_length = 2; // what is the prefix of your blog database shards (everything before the hexidecimal hash) // $shardb_prefix = 'set'; // set a string to be used as an internal identifier for the dataset $shardb_dataset = 'abc'; // do you want to put your primary blog (blog_id 1) in its own 'home' database? $enable_home_db = true; // how many, if any, VIP databases do you have? $num_vipdbs = 5; // add this to set the write master read priority (default 1) $shardb_master_read = 99; // add this if all of your databases are on a local server // , $shardb_local_db = true; // use this function to add a read slave host add_slave($read_priority, $hostname, $local_hostname, $user, $password); // instructions for adding vip blogs at the bottom of this confg filei /* That's all, stop editing! Happy blogging. */
set is the name of the database containing all sites.
Next, copy the file shardb-admin.php to the folder with plugins, as usual, activate this plugin for the network.
Then in the admin go to the settings of the plugin. Network Management ---> Settings ---> SharDB Migration.
And here the first step is Migrate Global Tables. He will offer to write it in a separate table - in setglobal
Next, the first site
If the base was called “set”, then it offers to move it to sethome
Everything is copied normally, you can copy further all sites, "Migrate Sites"
And so on ... the numbering of sites and theirs tables are displayed on the screen. The names of the tables in which the plugin wants to write are also visible.
After everything is in place, you need to unpack the db.php file in the wp-content folder
Now, in the admin panel, you can see the status of those sites that I, for example, have not copied, and the desired tables with new names for each of them are visible there.
Connection failed - this message displays db.php. If this file is in your wp-content, by default it tries to connect to different databases separately, and not to one common one. If you move all other sites on the same principle - they will also work.
To copy the databases, you should not have the db.php file, otherwise the settings item in the admin panel (SharDB Migration) will not be available, after copying, you need to load it last. It serves as a simple link.
When creating a new subsite, you need to delete it for this time or rename the db.php file and then optionally separate it into another database using the same method.
This measure may not matter to you if you have a few sites. But the base of the engine itself tends to become clogged over time, and slow down the response from the site. If this happens for one site and one base, then having multisite mode it will be tenfold + in size since all these site tables will be in one place.
This is another possibility of the engine which in some cases can be used.
Source: https://habr.com/ru/post/306992/
All Articles