MySql Server Cluster (Maria+galera)

Add MariaDB Repositories
========================
Create a mariadb repository /etc/yum.repos.d/mariadb.repo using following content in your system. Below repository will work on CentOS 6.x systems, For other system use repository generation tool and add to your system.

Disable Selinux in redhat sever's.

For CentOS 6 – 64bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-amd64
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
For CentOS 6 – 32bit

[mariadb]
name = MariaDB
baseurl = http://yum.mariadb.org/5.5/centos6-x86
gpgkey=https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
gpgcheck=1
Install MariaDB and Galera
==========================
Before installing MariaDB Galera cluster packages, remove any existing MySQL or MariaDB packages installed on system. After that use following command to install on all nodes.

# yum install MariaDB-Galera-server MariaDB-client galera

Initial MariaDB Configuration
=============================
After successfully installing packages in above steps do the some initial MariaDB configurations. Use following command and follow the instructions on all nodes of cluster. If will prompt to set root account password also.

# mysql_secure_installation
# service mysql start
After that create a user in MariaDB on all nodes, which can access database from your network in cluster.

# mysql -u root -p

MariaDB [(none)]> GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;
MariaDB [(none)]> FLUSH PRIVILEGES;
MariaDB [(none)]> exit
and stop MariaDB service before starting cluster configuration

# service mysql stop
Setup Cluster Configuration on database1
========================================
Lets start setup MariaDB Galera cluster from database1 server. Edit MariaDB server configuration file and add following values under [mariadb] section.

[root@database1 ~]# vim /etc/my.cnf.d/server.cnf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.4,10.0.0.5
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.2'
wsrep_node_name='database1'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password
Start cluster using following command.

[root@database1 ~]# /etc/init.d/mysql bootstrap
Bootstrapping the clusterStarting MySQL.... SUCCESS!
If you get any problem during startup check MariaDB error log file /var/lib/mysql/<hostname>.err

Add database2 in MariaDB Cluster
================================
After successfully starting cluster on database1. Start configuration on database2. Edit MariaDB server configuration file and add following values under [mariadb] section. All the settings are similar to database1 except wsrep_node_address, wsrep_cluster_address and wsrep_node_name.

[root@database2 ~]# vim /etc/my.cnf.d/server.cnf

query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.2,10.0.0.5
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.4'
wsrep_node_name='database2'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password

Start cluster using following command.

[root@database2 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

Add database3 in MariaDB Cluster
================================
This server is optional, If you want only two server in cluster, you can ignore this step, but you need to remove third server ip from database1/database2 configuration files. To add this server make changes same as database2.

[root@database3 ~]# vim /etc/my.cnf.d/server.cnf
query_cache_size=0
binlog_format=ROW
default_storage_engine=innodb
innodb_autoinc_lock_mode=2
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_address=gcomm://10.0.0.2,10.0.0.4
wsrep_cluster_name='cluster1'
wsrep_node_address='10.0.0.5'
wsrep_node_name='database3'
wsrep_sst_method=rsync
wsrep_sst_auth=root:password
Start cluster using following command.

[root@db3 ~]# /etc/init.d/mysql start
Starting MySQL..... SUCCESS!

Enable up/Down arrow in powershell

For this, you need PSReadline. First, install PsGet if you don’t have it:To install it just run the following URL in powershell.

(new-object Net.WebClient).DownloadString("http://psget.net/GetPsGet.ps1 ' ') | iex
Then, install PSReadline:

install-module PSReadline
Import PSReadline after loading the persistent history:

Import-Module PSReadLine
And you will be able to recall previous commands with up arrow key. Add the following to have partial history search with up/down arrow key:

Set-PSReadlineKeyHandler -Key UpArrow -Function HistorySearchBackward
Set-PSReadlineKeyHandler -Key DownArrow -Function HistorySearchForward
Lastly, to enable bash style completion:

Set-PSReadlineKeyHandler -Key Tab -Function Complete

History Command in PowerShell

To add the a command to a new or existing profile
On the computer that hosts the data warehouse management server,

click Start, point to Programs, point to Windows PowerShell 1.0, right-click Windows PowerShell, and then click Run as administrator.
At the Windows PowerShell prompt, type the following command, and then press ENTER:
>>Test-path $profile
If the results of the previous command are false, go to step 4. If the results are true, go to step 5.
Type the following command, and then press ENTER.
New-item –type file –force $profile
Type the following command, and then press ENTER.
Notepad $profile

Add the following Content into profile file.

===================

Set-Location C:\
$a = Get-Date
“Date: ” + $a.ToShortDateString()
“Time: ” + $a.ToShortTimeString()
$MaximumHistoryCount = 1KB

if (!(Test-Path ~\PowerShell -PathType Container))
{ New-Item ~\PowerShell -ItemType Directory
}

function bye
{ Get-History -Count 1KB |Export-CSV ~\PowerShell\history.csv
exit
}

if (Test-path ~\PowerShell\History.csv)
{ Import-CSV ~\PowerShell\History.csv |Add-History
}

====================

 

PS> Get-History |Get-Member -MemberType Property
TypeName: Microsoft.PowerShell.Commands.HistoryInfo

Name MemberType Definition
---- ---------- ----------
CommandLine Property System.String CommandLine {get;}
EndExecutionTime Property System.DateTime EndExecutionTime {get;}
ExecutionStatus Property System.Management.Automation.Runspaces.Pip...
Id Property System.Int64 Id {get;}
StartExecutionTime Property System.DateTime StartExecutionTime {get;}

This means that you can find out when something was executed (e.g. which session it happened in) using the following command:

PS> ghy |ft id,endexecutiontime,commandline -auto

Id EndExecutionTime CommandLine
-- ---------------- -----------
612 6/29/2006 5:39:34 AM gcm export-csv |fl *
613 6/30/2006 6:51:16 PM ipconfig
614 6/30/2006 8:51:38 PM cd C:\kits

 

Create a Windows PowerShell Profile

To add the a command to a new or existing profile
On the computer that hosts the data warehouse management server, click Start, point to Programs, point to Windows PowerShell 1.0, right-click Windows PowerShell, and then click Run as administrator.
At the Windows PowerShell prompt, type the following command, and then press ENTER:
Test-path $profile
If the results of the previous command are false, go to step 4. If the results are true, go to step 5.
Type the following command, and then press ENTER.
New-item –type file –force $profile
Type the following command, and then press ENTER.
Notepad $profile
In the profile, type the command you need ,If you are adding this command to an existing profile, add it on a new line at the end of the profile.
On the menu bar, click File, and then click Exit.
In Notepad, click Save.
Type the following commands, and then press ENTER after each command.
Set-ExecutionPolicy RemoteSigned
. $profile

Apache load balancing using mod_jk

Considering you have two tomcat server's and both are configured and port 8009 is listened by ajp in tomcat.

Download the module from http://tomcat.apache.org/download-connectors.cgi

Sample Version http://apache.mirrors.hoobly.com/tomcat/tomcat-connectors/jk/tomcat-connectors-1.2.40-src.tar.gz

#tar -xvf tomcat-connectors-1.2.37-src.tar

# cd tomcat-connectors-1.2.32-src/native/

# which usr/sbin/apxs

# ./configure --with-apxs=/usr/sbin/apxs --enable-api-compatibility

# make

# make install

after completed this activity you will get mod_jk.so file in /usr/lib64/httpd/modules/mod_jk.so

or else copy the modules to apache's module directory.

 

if get it , going well

Installation part has been completed, let's start configuration part

4. Open httpd.conf file and add end of line.

# vi /etc/httpd/conf/httpd.conf

JkWorkersFile "/etc/httpd/conf/worker.properties"
JkLogFile "/var/log/httpd/mod_jk.log"
JkRequestLogFormat "%w %V %T"
JkOptions +ForwardKeySize +ForwardURICompat -ForwardDirectories
JkLogLevel info
JkLogStampFormat "[%a %b %d %H:%M:%S %Y]"

The below two lines in the virtualhost.

JkMount / loadbalancer
JkMount /status status

Content of the worker.properties

cat /etc/httpd/conf/worker.properties
worker.list=loadbalancer,status

worker.template.type=ajp13
worker.template.connection_pool_size=50
worker.template.socket_timeout=1200

worker.node2.reference=worker.template
worker.node1.port=8009
worker.node1.host=54.86.231.61
worker.node1.type=ajp13
worker.node2.jvm_route=node1

worker.node2.port=8009
worker.node2.host=54.86.17.252
worker.node2.type=ajp13
worker.node2.jvm_route=node2

worker.loadbalancer.type=lb
worker.loadbalancer.balance_workers=node1,node2
#worker.loadbalancer.sticky_session=TRUE

to check the status

worker.status.type=status

 

 

 

Tomcat-Static-Unicast-Clustering

Tomcat needs to be configured to allow for setup of cluster of two nodes over unicast. Following is section of my ${LIFERAY_HOME}/tomcat-6.0.32/conf/server.xml on server1 (replace node1 with node2 and swap location of IP_ADDRESSES and change unique_id to anything 16 bit long other than{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2}, on server.xml in server2) which allowed for this. IP_ADDRESSES here refer to private ip addresses of server1 and server2 respectively.

================================

<Engine name="Catalina" defaultHost="localhost" jvmRoute="node1">

<Cluster className="org.apache.catalina.ha.tcp.SimpleTcpCluster" channelSendOptions="6" channelStartOptions="3">

<Manager className="org.apache.catalina.ha.session.DeltaManager" expireSessionsOnShutdown="false" notifyListenersOnReplication="true" />

<Channel className="org.apache.catalina.tribes.group.GroupChannel">

<Receiver className="org.apache.catalina.tribes.transport.nio.NioReceiver"
autoBind="0" selectorTimeout="5000" maxThreads="6"
address="IP_ADDRESS_SERVER1" port="4444" />
<Sender className="org.apache.catalina.tribes.transport.ReplicationTransmitter">
<Transport className="org.apache.catalina.tribes.transport.nio.PooledParallelSender"
timeout="60000"
keepAliveTime="10"
keepAliveCount="0"
/>
</Sender>
<Interceptor className="org.apache.catalina.tribes.group.interceptors.TcpPingInterceptor" staticOnly="true"/>
<Interceptor className="org.apache.catalina.tribes.group.interceptors.TcpFailureDetector"/>
<Interceptor className="org.apache.catalina.tribes.group.interceptors.StaticMembershipInterceptor">
<Member className="org.apache.catalina.tribes.membership.StaticMember"
host="IP_ADDRESS_SERVER2"
port="4444"
uniqueId="{0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,2}"/>
</Interceptor>
</Channel>
<Valve className="org.apache.catalina.ha.tcp.ReplicationValve" filter="" />
<Valve className="org.apache.catalina.ha.session.JvmRouteBinderValve" />
<ClusterListener className="org.apache.catalina.ha.session.JvmRouteSessionIDBinderListener"/>
<ClusterListener className="org.apache.catalina.ha.session.ClusterSessionListener"/>
</Cluster>

=================================

Apache load balancer

An add-in module that acts as a software load balancer and ensures that traffic is split across back-end servers or workers to reduce latencies and give users a better experience.

mod_proxy_balancer distributes requests to multiple worker processes running on back-end servers to let multiple resources service incoming traffic and processing. It ensures efficient utilization of the back-end workers to prevent any single worker from getting overloaded.

When you configure mod_proxy_balancer, you can choose among three load-balancing algorithms: Request Counting, Weighted Traffic Counting, and Pending Request Counting, which we'll discuss in detail in a moment. The best algorithm to use depends on the individual use case; if you are not sure which to try first, go with Pending Request Counting.

The add-in also supports session stickyness, meaning you can optionally ensure that all the requests from a particular IP address or in a particular session goes to the same back-end server. The easiest way to achieve stickyness is to use cookies, either inserted by the Apache web server or by the back-end servers.

A general configuration for load balancing defined in /etc/httpd/httpd.conf would look like this:

<Proxy balancer://A_name_signifying_your_app>
BalancerMember http://ip_address:port/ loadfactor=appropriate_load_factor # Balancer member 1
BalancerMember http://ip_address:port/ loadfactor=appropriate_load_factor # Balancer member 2
ProxySet lbmethod=the_Load_Balancing_algorithm
</Proxy>
You can specify anything for a name, but it's good to choose one that's significant. BalancerMember specifies a back-end worker's IP address and port number. A worker can be a back-end HTTP server or anything that can serve HTTP traffic. You can omit the port number if you use the web server's default port of 80. You can define as many BalancerMembers as you want; the optimal number depends on the capabilities of each server and the incoming traffic load. The loadfactor variable specifies the load that a back-end worker can take. Depending upon the algorithm, this can represent a number of requests or a number of bytes. lbmethod specifies the algorithm to be used for load balancing.

 

Let's look at how to configure each of the three options.
Get an Open Source Support Quote

Request Counting
With this algorithm, incoming requests are distributed among back-end workers in such a way that each back end gets a proportional number of requests defined in the configuration by the loadfactor variable. For example, consider this Apache config snippet:
<Proxy balancer://myapp>
BalancerMember http://192.168.10.11/ loadfactor=1 # Balancer member 1
BalancerMember http://192.168.10.10/ loadfactor=3 # Balancer member 2
ProxySet lbmethod=byrequests
</Proxy>
In this example, one request out of every four will be sent to 192.168.10.11, while three will be sent to 192.168.10.10. This might be an appropriate configuration for a site with two servers, one of which is more powerful than the other.

 

Weighted Traffic Counting Algorithm
The Weighted Traffic Counting algorithm is similar to Request Counting algorithm, with a minor difference: Weighted Traffic Counting considers the number of bytes instead of number of requests. In the configuration example below, the number of bytes processed by 192.168.10.10 will be three times that of 192.168.10.11.
<Proxy balancer://myapp>
BalancerMember http://192.168.10.11/ loadfactor=1 # Balancer member 1
BalancerMember http://192.168.10.10/ loadfactor=3 # Balancer member 2
ProxySet lbmethod=bytraffic
</Proxy>
Pending Request Counting Algorithm
The Pending Request Counting algorithm is the latest and most sophisticated algorithm provided by Apache for load balancing. It is available from Apache 2.2.10 onward.

 

In this algorithm, the scheduler keeps track of the number of requests that are assigned to each back-end worker at any given time. Each new incoming request will be sent to the back end that has least number of pending requests – in other words, to the back-end worker that is relatively least loaded. This helps keep the request queues even among the back-end workers, and each request generally goes to the worker that can process it the fastest.

 

If two workers are equally lightly loaded, the scheduler uses the Request Counting algorithm to break the tie.
<Proxy balancer://myapp>
BalancerMember http://192.168.10.11/ # Balancer member 1
BalancerMember http://192.168.10.10/ # Balancer member 2
ProxySet lbmethod=bybusyness
</Proxy>
Enable the Balancer Manager
Sometimes you may need to change your load balancing configuration, but that may not be easy to do without affecting the running server. For such situations, the Balancer Manager module provides a web interface to change the status of back-end workers on the fly. You can use Balancer Manager to put a worker in offline mode or change its loadfactor. You must have mod_status installed in order to use Balance Manager. A sample config, which should be defined in /etc/httpd/httpd.conf, might look like:

 

<Location /balancer-manager>

SetHandler balancer-manager

Order Deny,Allow
Deny from all
Allow from .test.com
</Location>
Once you add directives like those above to httpd.conf and restart Apache you can open the Balancer Manager by pointing a browser at http://test.com/balancer-manager.

 

<VirtualHost *:80>
ProxyRequests off

ServerName domain.com

<Proxy balancer://mycluster>
# WebHead1
BalancerMember http://10.176.42.144:80
# WebHead2
BalancerMember http://10.176.42.148:80

# Security "technically we aren't blocking
# anyone but this the place to make those
# chages
Order Deny,Allow
Deny from none
Allow from all

# Load Balancer Settings
# We will be configuring a simple Round
# Robin style load balancer. This means
# that all webheads take an equal share of
# of the load.
ProxySet lbmethod=byrequests

</Proxy>

# balancer-manager
# This tool is built into the mod_proxy_balancer
# module and will allow you to do some simple
# modifications to the balanced group via a gui
# web interface.
<Location /balancer-manager>
SetHandler balancer-manager

# I recommend locking this one down to your
# your office
Order deny,allow
Allow from all
</Location>

# Point of Balance
# This setting will allow to explicitly name the
# the location in the site that we want to be
# balanced, in this example we will balance "/"
# or everything in the site.
ProxyPass /balancer-manager !
ProxyPass / balancer://mycluster/

</VirtualHost>

 

========================

Enable proxy_module, proxy_balancer_module and proxy_http_module in httpd.conf of Apache web server
LoadModule proxy_module modules/mod_proxy.so
LoadModule proxy_balancer_module modules/mod_proxy_balancer.so
LoadModule proxy_http_module modules/mod_proxy_http.so
Add proxy pass along with balancer name for application context root. In this example, I have proxy path as examples and balancer name as mycluster. Very important to include stickysession as not having this option will distribute same request to multiple tomcat server and you will have session expiry issues in application.

<IfModule proxy_module>
ProxyRequests Off
ProxyPass /examples balancer://mycluster stickysession=JSESSIONID
ProxyPassReverse /examples balancer://mycluster stickysession=JSESSIONID
<Proxy balancer://mycluster>
BalancerMember http://localhost:8080/examples route=server1
BalancerMember http://localhost:8090/examples route=server2
</Proxy>
</IfModule>
As you can see in above configuration, I have added route in BalancerMember so route value can be appended to session ID. Now, let’s configure Apache to print JSESSIONID in access logs.

Add following in LogFormat directive
%{JSESSIONID}C
Ex:

LogFormat "%h %l %u %t \"%r\" %>s %b \"%{Referer}i\" \"%{User-Agent}i\"\"%{JSESSIONID}C\"" combined
Restart Apache Web Server