站長日誌

有在使用WordPress MultiSite (WP多用戶站點) 的站長都知道，其資料庫是一個痛！
因為他把用戶的資料表全部塞在同一個資料庫裏面，會造成管理時，光等phpmyadmin列出全部資料表，電腦就當機了！

以我來說，手上有大概一千多位用戶，但資料表加起來竟然有一萬三千八百多個！光是磁碟尋找檔案就耗很多時間了，更別談效率。

上圖13858是未使用multi-db之前通通擠在一起的資料表，經過multi-db有系統的規劃後，可以看到下面的列表他拆成256個資料庫，在分門別類把每個資料表移過去。
multi-db允許我們建立16/256/4096個資料庫，用戶建立時資料時會被散列納入相應的資料庫中，這樣就實現了用戶數量級的躍升。

譬如原先我們的是在一個資料庫擠好幾萬個資料表，幾千個用戶，但multi-db之後其承載能力，理論上獲得了×16、×256、×4096的倍率。
當然我們知道其實這不是簡單的乘數效應，因為系統的短板會在不同時期出現在不同的環節，比如blogs.dir的文件數理論極限，比如單一台主機不做負載均衡時單機的負載能力上限，
比如靜態文件請求產生的資源消耗等等。

但是，通過multi-db，我們就可以輕鬆跨越一個資料庫的海量資料表！
此外，對於有特殊需求的用戶，從資料庫穩定性考慮也可以為他們單獨建個別的資料庫。因為不同的資料可以存在於不同的資料庫內，這一切都是可設置的。

不過目前multi-db並非免費，必須至https://premium.wpmudev.org/project/multi-db/ 購買。

以下是安裝心得。

1.不需使用WP普通外掛的方式安裝，他是採檔案上傳後即可運行。
2.先建立你要的資料庫，舉例我這次採用切割成256個資料庫，所以先透過phpmyadmin建好256個資料庫，其建立規則為
CREATE DATABASE `資料庫名稱_00` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
一直到
CREATE DATABASE `資料庫名稱_ff` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
大概會長得像這樣
CREATE DATABASE `wpmudb_ff` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

由於是16進位，所以00(零零)~ff為256，可以自己寫個php 迴圈快速產生指令
for ($i=0;$i<=255;$i++){
echo “CREATE DATABASE `資料庫名稱_”.dechex($i).”` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
“;
}
還要加上主要網誌的資料庫
CREATE DATABASE `資料庫名稱_global` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

※注意，上述語法 0~e 只有一位，前面自行補上0

3.建立好資料庫後，開始要修改檔案了，先產生資料庫的管理權限，一樣產生256個
add_db_server(‘編號’, ‘dc1′, 1, 1,”,’網站IP’, ‘資料庫名稱_編號’, ‘資料庫帳號’, ‘資料庫密碼’);
大概會長得像這樣
add_db_server(’00’, ‘dc1′, 1, 1,”,’111.111.111.111’, ‘wpmudb_00’, ‘admin’, ‘123456’);
別忘了幫主要網誌資料庫也加上密碼
add_db_server(‘global’, ‘dc1′, 1, 1,”,’111.111.111.111’, ‘wpmudb_global’,  ‘admin’, ‘123456’);

將產生出來的add_db_server複製貼上至db-config.php
貼到
//————————————————————————//
//—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’);
這排下面

————-
4.其他細項修改
//—DB Scaling———————————————————–//
//————————————————————————//
// 16,256,4096
define (‘DB_SCALING’, ‘256’); #因為我用256 所以填256

//————————————————————————//
//—DC IPs—————————————————————//
//————————————————————————//
// Usage: add_dc_ip(IP, DC)
// EX: add_dc_ip(‘123.123.123.’, ‘dc1’);
add_dc_ip(‘111.111.111.111.’, ‘dc1’); #改成網站IP

//————————————————————————//
//—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(‘wpmudb_global’); #設定主要網站的資料庫名稱

5.修改move-blogs.php
//————————————————————————//
//—Config—————————————————————//
//————————————————————————//

///DB Settings
$dbname = “原本WP的資料庫名稱”; //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 = ‘新的資料庫名稱_’; //本範例應填上 wpmudb

//We need info to connect to the databases
$dbhost = ‘localhost’;
$dbuname = ‘資料庫帳號’;
$dbpass = ‘資料庫密碼’;

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

到這邊就改完了!!

開始準備上傳，
把db.php、db-config.php 上傳至/wp-content/裡面
把move-blogs.php、fix-db-encoding.php 上傳至/wp-content/script/裡面 script資料夾自己建

執行http://網址/wp-content/script/move-blogs.php 會看到下圖，因為我已經完成轉換所以右邊是顯示table in new db

如果設定正確，前面的資料庫也建立正確，此頁面不會出現任何ERROR，如果有ERROR的話 就照他的ERROR去解。
接著按最上面的5.To start the copy process click here 他就會開始跑了！我一萬三千八百多個大概要10分鐘才會結束。

6.最後
做完上述動作，以後系統都會以多資料庫的方式運行，即使增加新用戶也會自動歸類，不需要再做其他設定！非常簡單方便。
——————————————————————————————–

There are using WordPress MultiSite (WP multi-user site) owners are aware that their library is a pain!
Because when he stuffed all the user table in the same database which will cause management, light, etc. phpmyadmin all the information listed in the table, the computer crashed!

I, for one, have got probably more than a thousand users, but add up table yet there are more than thirteen thousand eight hundred! Find files on disk alone consume a lot of time, let alone efficient.

The figure 13858 is not used before the multi-db table all crowded together, after a multi-db systematic planning, you can see the list below him split into 256 databases in different categories each table move past.
multi-db allows us to establish 16/256/4096 a database, the user profile will be established when the hash included in the corresponding database, thus achieving the level of the number of users jumped.

For example, originally we are tens of thousands crowded table, thousands of users in a database, but after a multi-db its carrying capacity, theoretically gained × 16, × 256, × 4096 magnification.
Of course, we know that in fact this is not a simple multiplier effect, because the short-board system will appear at different times in different areas, such as the number of files blogs.dir theoretical limit, such as single hosts do load balancing load capacity stand-alone ceiling
For example, the static file requests generated resource consumption and so on.

However, by multi-db, we can easily span a massive database table!
In addition, for users with special needs, from the library to be built in consideration of the stability of individual repository for them separately. Because different materials can exist in different data library, all this can be set.

But the current multi-db is not free, you must purchase to https://premium.wpmudev.org/project/multi-db/.

The following are the installation experience.

1. WP without using ordinary plug mounted, he was taken to run after the file is uploaded.
2. First you have to establish a repository, for example I use this cut 256 database, so the first built 256 database through phpmyadmin, which establish rules
CREATE DATABASE `library name _00` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
until
CREATE DATABASE `library name _ff` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;
Probably it would look like this
CREATE DATABASE `wpmudb_ff` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

Because it is hexadecimal, so 00 (2003) ~ ff 256, can write a php loop quickly generate instruction
for ($ i = 0; $ i <= 255; $ i ++) {
echo “CREATE DATABASE` library name _ “dechex ($ i)” `DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci..;
“;
}
Plus major blog repository
CREATE DATABASE `library name _global` DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci;

※ Note that the above grammar 0 ~ e only one, in front of their own make up 0

3. After the establishment of a good database, start to modify the file, the first generation of database administrative privileges, the same generation 256
add_db_server ( ‘No’, ‘dc1’, 1, 1, ”, ‘site IP’, ‘Database name _ Number’, ‘database account’, ‘Database Password’);
Probably it would look like this
add_db_server (’00 ‘,’ dc1 ‘, 1, 1,’ ‘,’ 111.111.111.111 ‘,’ wpmudb_00 ‘,’ admin ‘,’ 123456 ‘);
Do not forget to help the main blog database also add password
add_db_server ( ‘global’, ‘dc1’, 1, 1, ”, ‘111.111.111.111’, ‘wpmudb_global’, ‘admin’, ‘123456’);

Will have to copy and paste out of add_db_server db-config.php
Paste
// ———————————————— ———————— //
// — 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 ‘);
This row below

————-
4. Other modifications breakdown
// — DB Scaling ——————————————- // —————-
// ———————————————— ———————— //
// 16,256,4096
define ( ‘DB_SCALING’, ‘256’); # 256 because I used it to fill 256

// ———————————————— ———————— //
// — DC IPs ——————————————- ——————– //
// ———————————————— ———————— //
// Usage: add_dc_ip (IP, DC)
// EX: add_dc_ip ( ‘123.123.123.’, ‘Dc1’);
add_dc_ip ( ‘111.111.111.111.’, ‘dc1’); # change website IP

// ———————————————— ———————— //
// — Global Tables ——————————————- ————- //
// ———————————————— ———————— //
// Do not include default global tables
// Leave off base prefix (eg: wp_)
// You do not 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 ( ‘wpmudb_global’); # set the main site database name

5. Modify the move-blogs.php
// ———————————————— ———————— //
// — Config ——————————————– ——————- //
// ———————————————— ———————— //

/// DB Settings
$ Dbname = “original WP repository name”; // This is your current database
$ Blog_table_prefix = ‘wp_’; // Prefix of your wpmu blog tables, most likely this will not need to be changed
$ Newdb_prefix = ‘_ new database name’; // This example should fill wpmudb

// We need info to connect to the databases
$ Dbhost = ‘localhost’;
$ Dbuname = ‘database account’;
$ Dbpass = ‘Database Password’;

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

To change over to the side !!

Begin uploading,
The db.php, db-config.php uploaded to / wp-content / inside
The move-blogs.php, fix-db-encoding.php uploaded to / wp-content / script / folder inside the script to build their own

Executive http: // URL /wp-content/script/move-blogs.php will see the following figure, because I have done so on the right is a graph showing the conversion table in new db

If set up properly, the front of the library also established correctly, this page will not be any ERROR, if ERROR, then did as he had gone to ERROR.
Followed by the top 5.To start the copy process click here he’ll start running! I was more than thirteen thousand eight hundred take about 10 minutes before the end.

6. Finally
To complete the operation, after the system will be in multi-mode operation of the database, even if the increase in new users will automatically categorize not need to do other settings! Very simple and convenient.