Tungsten Replicator: MariaDB MySQL Master/Slave Replication setup with HyperDB plugin for WordPress
Setting up a single server for Tungsten Replicator master and local slave MySQL replication has made it alot easier to just fire up the one server to continue testing Tungsten Replicator’s features. This time, I’ll install a WordPress v3.2.1 and install HyperDB wordpress plugin to configure the WordPress installation to use Tungsten Replicator master/slave MySQL replication to improve scalability.
Both .101 master (default 3306 port) and .101 local slave (3309 port) use MariaDB 5.2.7 MySQL servers.
- Download WordPress v3.2.1 zip file and following the 5min WordPress installation guide. Installing wordpress to 192.168.56.101/wp
- Create new wordpress database called wp321
- Edited wp-config.php with Tungsten Replicator .101 master database settings
- Download HyperDB wordpress plugin for MySQL replication
- Configured HyperDB’s /wp/db-config.php defining the Tungsten Replicator .101 master server on default 3306 port and the .101 local slave on 3309 port in read only mode.
- If i only defined the .101 local slave and not the .101 master, when i tried to add a comment to test blog post, the comment wouldn’t hit the database at all. Once .101 master was defined, adding a comment worked and checking both master and slave wp321 databases showed the comment was added and replicated properly.
More details below:
Create wp321 database:
[root@centos6 wpzip]# mysqladmin create wp321 |
After WordPress v3.2.1 test install on .101 master. Check MySQL data directories for .101 master (/var/lib/mysql) and .101 local slave (/var/lib/mysql2) to make sure wp321 database are replicated.
[root@centos6 wp]# ls -alh /var/lib/{mysql/wp321,mysql2/wp321} /var/lib/mysql2/wp321: total 620K drwx------. 2 mysql mysql 4.0K Sep 20 15:15 . drwxr-xr-x. 11 mysql mysql 4.0K Sep 20 15:09 .. -rw-rw----. 1 mysql mysql 65 Sep 20 15:09 db.opt -rw-rw----. 1 mysql mysql 8.5K Sep 20 15:15 wp_commentmeta.frm -rw-rw----. 1 mysql mysql 0 Sep 20 15:15 wp_commentmeta.MYD -rw-rw----. 1 mysql mysql 4.0K Sep 20 15:15 wp_commentmeta.MYI -rw-rw----. 1 mysql mysql 14K Sep 20 15:15 wp_comments.frm -rw-rw----. 1 mysql mysql 232 Sep 20 15:15 wp_comments.MYD -rw-rw----. 1 mysql mysql 7.0K Sep 20 15:15 wp_comments.MYI -rw-rw----. 1 mysql mysql 13K Sep 20 15:15 wp_links.frm -rw-rw----. 1 mysql mysql 540 Sep 20 15:15 wp_links.MYD -rw-rw----. 1 mysql mysql 3.0K Sep 20 15:15 wp_links.MYI -rw-rw----. 1 mysql mysql 8.6K Sep 20 15:15 wp_options.frm -rw-rw----. 1 mysql mysql 333K Sep 20 15:15 wp_options.MYD -rw-rw----. 1 mysql mysql 14K Sep 20 15:15 wp_options.MYI -rw-rw----. 1 mysql mysql 8.5K Sep 20 15:15 wp_postmeta.frm -rw-rw----. 1 mysql mysql 52 Sep 20 15:15 wp_postmeta.MYD -rw-rw----. 1 mysql mysql 10K Sep 20 15:15 wp_postmeta.MYI -rw-rw----. 1 mysql mysql 14K Sep 20 15:15 wp_posts.frm -rw-rw----. 1 mysql mysql 1.5K Sep 20 15:15 wp_posts.MYD -rw-rw----. 1 mysql mysql 11K Sep 20 15:15 wp_posts.MYI -rw-rw----. 1 mysql mysql 8.5K Sep 20 15:15 wp_term_relationships.frm -rw-rw----. 1 mysql mysql 168 Sep 20 15:15 wp_term_relationships.MYD -rw-rw----. 1 mysql mysql 3.0K Sep 20 15:15 wp_term_relationships.MYI -rw-rw----. 1 mysql mysql 8.5K Sep 20 15:15 wp_terms.frm -rw-rw----. 1 mysql mysql 72 Sep 20 15:15 wp_terms.MYD -rw-rw----. 1 mysql mysql 11K Sep 20 15:15 wp_terms.MYI -rw-rw----. 1 mysql mysql 8.6K Sep 20 15:15 wp_term_taxonomy.frm -rw-rw----. 1 mysql mysql 84 Sep 20 15:15 wp_term_taxonomy.MYD -rw-rw----. 1 mysql mysql 4.0K Sep 20 15:15 wp_term_taxonomy.MYI -rw-rw----. 1 mysql mysql 8.5K Sep 20 15:15 wp_usermeta.frm -rw-rw----. 1 mysql mysql 700 Sep 20 15:15 wp_usermeta.MYD -rw-rw----. 1 mysql mysql 10K Sep 20 15:15 wp_usermeta.MYI -rw-rw----. 1 mysql mysql 8.8K Sep 20 15:15 wp_users.frm -rw-rw----. 1 mysql mysql 96 Sep 20 15:15 wp_users.MYD -rw-rw----. 1 mysql mysql 4.0K Sep 20 15:15 wp_users.MYI /var/lib/mysql/wp321: total 620K drwx------. 2 mysql mysql 4.0K Sep 20 15:15 . drwxr-xr-x. 12 mysql mysql 4.0K Sep 20 15:15 .. -rw-rw----. 1 mysql mysql 65 Sep 20 15:09 db.opt -rw-rw----. 1 mysql mysql 8.5K Sep 20 15:15 wp_commentmeta.frm -rw-rw----. 1 mysql mysql 0 Sep 20 15:15 wp_commentmeta.MYD -rw-rw----. 1 mysql mysql 4.0K Sep 20 15:15 wp_commentmeta.MYI -rw-rw----. 1 mysql mysql 14K Sep 20 15:15 wp_comments.frm -rw-rw----. 1 mysql mysql 232 Sep 20 15:15 wp_comments.MYD -rw-rw----. 1 mysql mysql 7.0K Sep 20 15:15 wp_comments.MYI -rw-rw----. 1 mysql mysql 13K Sep 20 15:15 wp_links.frm -rw-rw----. 1 mysql mysql 540 Sep 20 15:15 wp_links.MYD -rw-rw----. 1 mysql mysql 3.0K Sep 20 15:15 wp_links.MYI -rw-rw----. 1 mysql mysql 8.6K Sep 20 15:15 wp_options.frm -rw-rw----. 1 mysql mysql 333K Sep 20 15:15 wp_options.MYD -rw-rw----. 1 mysql mysql 14K Sep 20 15:15 wp_options.MYI -rw-rw----. 1 mysql mysql 8.5K Sep 20 15:15 wp_postmeta.frm -rw-rw----. 1 mysql mysql 52 Sep 20 15:15 wp_postmeta.MYD -rw-rw----. 1 mysql mysql 10K Sep 20 15:15 wp_postmeta.MYI -rw-rw----. 1 mysql mysql 14K Sep 20 15:15 wp_posts.frm -rw-rw----. 1 mysql mysql 1.5K Sep 20 15:15 wp_posts.MYD -rw-rw----. 1 mysql mysql 11K Sep 20 15:15 wp_posts.MYI -rw-rw----. 1 mysql mysql 8.5K Sep 20 15:15 wp_term_relationships.frm -rw-rw----. 1 mysql mysql 168 Sep 20 15:15 wp_term_relationships.MYD -rw-rw----. 1 mysql mysql 3.0K Sep 20 15:15 wp_term_relationships.MYI -rw-rw----. 1 mysql mysql 8.5K Sep 20 15:15 wp_terms.frm -rw-rw----. 1 mysql mysql 72 Sep 20 15:15 wp_terms.MYD -rw-rw----. 1 mysql mysql 11K Sep 20 15:15 wp_terms.MYI -rw-rw----. 1 mysql mysql 8.6K Sep 20 15:15 wp_term_taxonomy.frm -rw-rw----. 1 mysql mysql 84 Sep 20 15:15 wp_term_taxonomy.MYD -rw-rw----. 1 mysql mysql 4.0K Sep 20 15:15 wp_term_taxonomy.MYI -rw-rw----. 1 mysql mysql 8.5K Sep 20 15:15 wp_usermeta.frm -rw-rw----. 1 mysql mysql 700 Sep 20 15:15 wp_usermeta.MYD -rw-rw----. 1 mysql mysql 10K Sep 20 15:15 wp_usermeta.MYI -rw-rw----. 1 mysql mysql 8.8K Sep 20 15:15 wp_users.frm -rw-rw----. 1 mysql mysql 96 Sep 20 15:15 wp_users.MYD -rw-rw----. 1 mysql mysql 4.0K Sep 20 15:15 wp_users.MYI |
check current status of .101 master and .101 local slave
[tungsten@centos6 root]$ trepctl -service rep1 status Processing status command... NAME VALUE ---- ----- appliedLastEventId : mysql-bin.000025:0000000003508342;209 appliedLastSeqno : 4259 appliedLatency : 0.457 clusterName : default currentEventId : mysql-bin.000025:0000000003508342 currentTimeMillis : 1316495761179 dataServerHost : 192.168.56.101 extensions : host : null latestEpochNumber : 3413 masterConnectUri : thl://192.168.56.102 masterListenUri : thl://192.168.56.101:2112/ maximumStoredSeqNo : 4259 minimumStoredSeqNo : 0 offlineRequests : NONE pendingError : NONE pendingErrorCode : NONE pendingErrorEventId : NONE pendingErrorSeqno : -1 pendingExceptionMessage: NONE resourcePrecedence : 99 rmiPort : -1 role : master seqnoType : java.lang.Long serviceName : rep1 serviceType : local simpleServiceName : rep1 siteName : default sourceId : 192.168.56.101 state : ONLINE timeInStateSeconds : 6935.154 uptimeSeconds : 6938.703 Finished status command... [tungsten@centos6 root]$ bash /opt/tungsten2/tungsten/tungsten-replicator/bin/trepctl -port 10500 -service rep1 status Processing status command... NAME VALUE ---- ----- appliedLastEventId : mysql-bin.000025:0000000003508342;209 appliedLastSeqno : 4259 appliedLatency : 1.043 clusterName : default currentEventId : NONE currentTimeMillis : 1316495765285 dataServerHost : 127.0.0.1 extensions : host : null latestEpochNumber : 3413 masterConnectUri : thl://192.168.56.101:2112/ masterListenUri : thl://127.0.0.1:22000/ maximumStoredSeqNo : 4259 minimumStoredSeqNo : 0 offlineRequests : NONE pendingError : NONE pendingErrorCode : NONE pendingErrorEventId : NONE pendingErrorSeqno : -1 pendingExceptionMessage: NONE resourcePrecedence : 99 rmiPort : -1 role : slave seqnoType : java.lang.Long serviceName : rep1 serviceType : local simpleServiceName : rep1 siteName : default sourceId : 127.0.0.1 state : ONLINE timeInStateSeconds : 6936.587 uptimeSeconds : 6938.285 Finished status command... |
Setup Tungsten Replicator MySQL master/slave replication for WordPress
- Install HyperDB wordpress plugin
- Edit /wp/db-config.php
change from
/** Sample Configuration 1: Using the Default Server **/ /** NOTE: THIS IS ACTIVE BY DEFAULT. COMMENT IT OUT. **/ /** * This is the most basic way to add a server to HyperDB using only the * required parameters: host, user, password, name. * This adds the DB defined in wp-config.php as a read/write server for * the 'global' dataset. (Every table is in 'global' by default.) */ $wpdb->add_database(array( 'host' => DB_HOST, // If port is other than 3306, use host:port. 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, )); /** * This adds the same server again, only this time it is configured as a slave. * The last three parameters are set to the defaults but are shown for clarity. */ $wpdb->add_database(array( 'host' => DB_HOST, // If port is other than 3306, use host:port. 'user' => DB_USER, 'password' => DB_PASSWORD, 'name' => DB_NAME, 'write' => 0, 'read' => 1, 'dataset' => 'global', 'timeout' => 0.2, )); |
change to below making sure to define master and slave as read only
/** Sample Configuration 1: Using the Default Server **/ /** NOTE: THIS IS ACTIVE BY DEFAULT. COMMENT IT OUT. **/ /** * This is the most basic way to add a server to HyperDB using only the * required parameters: host, user, password, name. * This adds the DB defined in wp-config.php as a read/write server for * the 'global' dataset. (Every table is in 'global' by default.) */ $wpdb->add_database(array( 'host' => 'localhost', // If port is other than 3306, use host:port. 'user' => 'root', 'password' => '', 'name' => 'wp321', )); /** * This adds the same server again, only this time it is configured as a slave. * The last three parameters are set to the defaults but are shown for clarity. */ $wpdb->add_database(array( 'host' => '127.0.0.1:3309', // If port is other than 3306, use host:port. 'user' => 'root', 'password' => '', 'name' => 'wp321', 'write' => 0, 'read' => 1, 'dataset' => 'global', 'timeout' => 0.2, )); |
