Tungsten Replicator: vBulletin Suite v4.1.6 configured for MariaDB MySQL Master/Slave Replication
Last article involved setting up WordPress with HyperDB plugin for Tungsten Replicator master/slave MariaDB MySQL replication. This time, we’ll look at configuring vBulletin Suite v4.1.6 for Tungsten Replicator master/slave MariaDB MySQL replication.
Both .101 master (default 3306 port) and .101 local slave (3309 port) use MariaDB 5.2.7 MySQL servers.
- First I uploaded a vBulletin v4.1.6 upload/ files and copied them to public web root at /html
- Create vBulletin database called vb416
- Edit /includes config.php with master and slave MySQL server’s settings
First I uploaded a vBulletin v4.1.6 upload/ files and copied them to public web root at /html
[root@centos6 vbzip]# cd upload/ [root@centos6 upload]# cp -a * ../../html/ [root@centos6 upload]# cd ../../html/ |
Create vBulletin database called vb416
[root@centos6 html]# mysqladmin create vb416 |
Check vB database named vb416 got replicated to MySQL slave data directory at /var/lib/mysql2
[root@centos6 includes]# ls -alh /var/lib/{mysql,mysql2} /var/lib/mysql: total 2.2G drwxr-xr-x. 10 mysql mysql 4.0K Sep 20 10:32 . drwxr-xr-x. 41 root root 4.0K Sep 19 08:37 .. -rw-rw----. 1 mysql mysql 16K Sep 19 06:32 aria_log.00000001 -rw-rw----. 1 mysql mysql 52 Sep 19 06:32 aria_log_control -rw-rw----. 1 mysql mysql 155 Sep 16 14:19 centos6.err -rw-rw----. 1 mysql mysql 5 Sep 20 10:25 centos6.localdomain.pid -rw-rw----. 1 mysql mysql 5 Sep 18 05:57 centos6.pid -rw-rw----. 1 mysql mysql 2.0G Sep 20 10:32 ibdata1 -rw-rw----. 1 mysql mysql 17K Aug 10 01:52 ibdata1.gz -rw-rw----. 1 mysql mysql 64M Sep 20 10:32 ib_logfile0 -rw-rw----. 1 mysql mysql 16K Aug 10 01:52 ib_logfile0.gz -rw-rw----. 1 mysql mysql 64M Sep 18 05:57 ib_logfile1 -rw-rw----. 1 mysql mysql 5.1K Aug 10 01:25 ib_logfile1.gz drwx------. 2 mysql mysql 4.0K Sep 16 14:19 mysql -rw-rw----. 1 mysql mysql 125 Sep 17 15:50 mysql-bin.000001 -rw-rw----. 1 mysql mysql 665 Sep 17 16:42 mysql-bin.000002 -rw-rw----. 1 mysql mysql 259 Sep 18 05:49 mysql-bin.000003 -rw-rw----. 1 mysql mysql 259 Sep 18 05:57 mysql-bin.000004 -rw-rw----. 1 mysql mysql 1.2K Sep 18 06:37 mysql-bin.000005 -rw-rw----. 1 mysql mysql 1.1K Sep 18 08:55 mysql-bin.000006 -rw-rw----. 1 mysql mysql 1.4K Sep 18 15:52 mysql-bin.000007 -rw-rw----. 1 mysql mysql 414 Sep 18 15:52 mysql-bin.000008 -rw-rw----. 1 mysql mysql 125 Sep 18 18:23 mysql-bin.000009 -rw-rw----. 1 mysql mysql 125 Sep 18 18:26 mysql-bin.000010 -rw-rw----. 1 mysql mysql 125 Sep 18 19:30 mysql-bin.000011 -rw-rw----. 1 mysql mysql 125 Sep 18 20:04 mysql-bin.000012 -rw-rw----. 1 mysql mysql 125 Sep 18 20:34 mysql-bin.000013 -rw-rw----. 1 mysql mysql 125 Sep 18 20:39 mysql-bin.000014 -rw-rw----. 1 mysql mysql 106 Sep 18 20:45 mysql-bin.000015 -rw-rw----. 1 mysql mysql 529 Sep 19 05:52 mysql-bin.000016 -rw-rw----. 1 mysql mysql 125 Sep 19 06:04 mysql-bin.000017 -rw-rw----. 1 mysql mysql 125 Sep 19 06:22 mysql-bin.000018 -rw-rw----. 1 mysql mysql 125 Sep 19 06:32 mysql-bin.000019 -rw-rw----. 1 mysql mysql 106 Sep 19 06:37 mysql-bin.000020 -rw-rw----. 1 mysql mysql 106 Sep 19 08:09 mysql-bin.000021 -rw-rw----. 1 mysql mysql 638 Sep 19 08:55 mysql-bin.000022 -rw-rw----. 1 mysql mysql 616 Sep 20 10:32 mysql-bin.000023 -rw-rw----. 1 mysql mysql 437 Sep 20 10:25 mysql-bin.index srwxrwxrwx. 1 mysql mysql 0 Sep 20 10:25 mysql.sock -rw-r--r--. 1 root root 13 Sep 16 14:19 mysql_upgrade_info drwx------. 2 mysql mysql 4.0K Aug 10 01:25 test drwx------. 2 mysql mysql 4.0K Sep 18 07:04 test2 drwx------. 2 mysql mysql 4.0K Sep 18 15:37 test3 drwx------. 2 mysql mysql 4.0K Sep 19 08:55 testlocalslavedb drwx------. 2 mysql mysql 4.0K Sep 18 06:04 tungsten drwx------. 2 mysql mysql 4.0K Sep 18 06:37 tungsten_rep1 drwx------. 2 mysql mysql 4.0K Sep 20 10:32 vb416 /var/lib/mysql2: total 385M drwxr-xr-x. 9 mysql mysql 4.0K Sep 20 10:32 . drwxr-xr-x. 41 root root 4.0K Sep 19 08:37 .. -rw-rw----. 1 mysql mysql 16K Sep 19 08:47 aria_log.00000001 -rw-rw----. 1 mysql mysql 52 Sep 19 08:47 aria_log_control -rw-rw----. 1 mysql mysql 0 Sep 19 08:37 centos6.err -rw-rw----. 1 mysql mysql 5 Sep 20 10:25 centos6.localdomain.pid -rw-r-----. 1 mysql mysql 5.0K Sep 20 10:25 error.log -rw-rw----. 1 mysql mysql 256M Sep 20 10:32 ibdata1 -rw-rw----. 1 mysql mysql 64M Sep 20 10:32 ib_logfile0 -rw-rw----. 1 mysql mysql 64M Sep 19 08:47 ib_logfile1 drwx------. 2 mysql mysql 4.0K Sep 19 08:37 mysql -rw-rw----. 1 mysql mysql 20K Sep 19 08:37 mysql-bin.000001 -rw-rw----. 1 mysql mysql 701K Sep 19 08:37 mysql-bin.000002 -rw-rw----. 1 mysql mysql 336 Sep 19 08:47 mysql-bin.000003 -rw-rw----. 1 mysql mysql 336 Sep 20 10:27 mysql-bin.000004 -rw-rw----. 1 mysql mysql 76 Sep 20 10:25 mysql-bin.index srwxrwxrwx. 1 mysql mysql 0 Sep 20 10:25 mysql.sock drwx------. 2 mysql mysql 4.0K Sep 19 08:37 test drwx------. 2 mysql mysql 4.0K Sep 19 08:50 test2 drwx------. 2 mysql mysql 4.0K Sep 19 08:50 test3 drwx------. 2 mysql mysql 4.0K Sep 19 08:55 testlocalslavedb drwx------. 2 mysql mysql 4.0K Sep 19 08:47 tungsten_rep1 drwx------. 2 mysql mysql 4.0K Sep 20 10:32 vb416 |
Edit /includes/config.php with master and slave MySQL server’s settings
[root@centos6 html]# cd includes/ [root@centos6 includes]# cp config.php.new config.php |
Now to setup vB config.php to use slave server for offloading vB search/read related queries.
change from
// ****** MASTER DATABASE SERVER NAME AND PORT ****** // This is the hostname or IP address and port of the database server. // If you are unsure of what to put here, leave the default values. // // Note: If you are using IIS 7+ and MySQL is on the same machine, you // need to use 127.0.0.1 instead of localhost $config['MasterServer']['servername'] = 'localhost'; $config['MasterServer']['port'] = 3306; // ****** MASTER DATABASE USERNAME & PASSWORD ****** // This is the username and password you use to access MySQL. // These must be obtained through your webhost. $config['MasterServer']['username'] = 'root'; $config['MasterServer']['password'] = ''; // ****** MASTER DATABASE PERSISTENT CONNECTIONS ****** // This option allows you to turn persistent connections to MySQL on or off. // The difference in performance is negligible for all but the largest boards. // If you are unsure what this should be, leave it off. (0 = off; 1 = on) $config['MasterServer']['usepconnect'] = 0; // ****** SLAVE DATABASE CONFIGURATION ****** // If you have multiple database backends, this is the information for your slave // server. If you are not 100% sure you need to fill in this information, // do not change any of the values here. $config['SlaveServer']['servername'] = ''; $config['SlaveServer']['port'] = 3306; $config['SlaveServer']['username'] = ''; $config['SlaveServer']['password'] = ''; $config['SlaveServer']['usepconnect'] = 0; |
change to
// ****** MASTER DATABASE SERVER NAME AND PORT ****** // This is the hostname or IP address and port of the database server. // If you are unsure of what to put here, leave the default values. // // Note: If you are using IIS 7+ and MySQL is on the same machine, you // need to use 127.0.0.1 instead of localhost $config['MasterServer']['servername'] = 'localhost'; $config['MasterServer']['port'] = 3306; // ****** MASTER DATABASE USERNAME & PASSWORD ****** // This is the username and password you use to access MySQL. // These must be obtained through your webhost. $config['MasterServer']['username'] = 'root'; $config['MasterServer']['password'] = ''; // ****** MASTER DATABASE PERSISTENT CONNECTIONS ****** // This option allows you to turn persistent connections to MySQL on or off. // The difference in performance is negligible for all but the largest boards. // If you are unsure what this should be, leave it off. (0 = off; 1 = on) $config['MasterServer']['usepconnect'] = 0; // ****** SLAVE DATABASE CONFIGURATION ****** // If you have multiple database backends, this is the information for your slave // server. If you are not 100% sure you need to fill in this information, // do not change any of the values here. $config['SlaveServer']['servername'] = 'localhost'; $config['SlaveServer']['port'] = 3309; $config['SlaveServer']['username'] = 'root'; $config['SlaveServer']['password'] = ''; $config['SlaveServer']['usepconnect'] = 0; |
