Thursday, February 28, 2013

Install Multi Master Mysql Server

I/INTRO

As Previous document(http://conheotiensinh.blogspot.com/2009/09/cluster-linux-mail-server.html) I showed you how to do replicate mysql.In Document I will intro to you how to do setup multi master replicate mysql

II/SETUP

Step 1: Install 3 Databases with info:

Server 1:192.168.124.128
Server 2:192.168.124.136
Server 3:192.168.124.137(root Cluster)

Step 2:Install some packages all servers

[root@centos ~] yum install gcc44 gcc44-c++  rsync -y

Step 3:Lets check what kind of MySQL applications installed in the box and Remove it

[root@centos ~] rpm -qa | grep mysql

Step 4:Following files should exist if you download the correct version:


galera-23.2.2-1.rhel5.i386.rpm
MySQL-client-5.5.28-1.rhel5.i386.rpm
MySQL-server-5.5.28_wsrep_23.7-1.rhel5.i386.rpm
MySQL-shared-5.5.28-1.rhel5.i386.rpm

Step 5:Now, we need to install all files based on following sequence:


[root@centos galera] rpm -UVh galera-23.2.2-1.rhel5.i386.rpm
[root@centos galera] rpm -UVh MySQL-client-5.5.28-1.rhel5.i386.rpm
[root@centos galera] rpm -UVh MySQL-server-5.5.28_wsrep_23.7-1.rhel5.i386.rpm
[root@centos galera] rpm -UVh MySQL-shared-5.5.28-1.rhel5.i386.rpm


Step 6:Lets reset/create MySQL root password. In this example we will use simple root password as above, but I recommend you to use strong password:

[root@centos ~] mysqladmin -u root password 'root#@pass'

Step 7:To ease up MySQL administration, we will need to create /root/.my.cnf files and put following contents. This will automate your login as client:

[client]
user=root
password='root#@pass'

Step 8:Change the permission to 600 to make sure no one can open it other than root:

[root@centos ~] chmod 0600 /root/.my.cnf

Step 9:Run following commands inside MySQL. First command will delete empty username, 2nd is to create root@% user, 3rd is update root password for all hosts, 4th is create SST user which we will use to communicate between nodes and last command will grant SST user to dump databases:

mysql> DELETE FROM mysql.user WHERE user='';
mysql> GRANT USAGE ON *.* TO root@'%' IDENTIFIED BY 'root#@pass';
mysql> UPDATE mysql.user SET Password=PASSWORD('root#@pass') WHERE User='root';
mysql> GRANT USAGE ON *.* to sst@'%' IDENTIFIED BY 'sstpass123';
mysql> GRANT ALL PRIVILEGES on *.* to sst@'%';

Step 10: Lets locate libgalera and take note of the full path. We will use this as the communication provider for MySQL to communicate:

[root@centos galera] updatedb
[root@centos galera] locate libgalera
/usr/lib/galera/libgalera_smm.so

Step 11:Lets configure MySQL so it will know where and how to communicate with other nodes. Open /etc/mysql/conf.d/wsrep.conf (copy the directory /usr/share/doc/MySQL-server-5.5.28_wsrep_23.7/wsrep.cnf) with text editor and change value for following variables:

[root@centos galera] mkdir -p /etc/mysql/conf.d/
[root@centos galera] vi /etc/mysql/conf.d/wsrep.cnf

//change line 22 to

wsrep_provider=/usr/lib/galera/libgalera_smm.so

//line 50 change to 
wsrep_cluster_address="gcomm://" //if it cluster root wsrep_cluster_address="gcomm://192.168.124.137:4567"  // if it is other server

//change line 112 to  

wsrep_sst_method=rsync

//change 121 to 

wsrep_sst_auth=sst:sstpass123


Step 12:Make sure in your /etc/my.cnf (create this file it is not exist) to add following line:

!includedir /etc/mysql/conf.d/

Step 13:Restart the MySQL so it will run the latest configuration:

[root@centos galera] service mysql restart

Step 14:Make sure MySQL is run on port 3306 and wsrep is run on port 4567

tcp        0      0 0.0.0.0:3306                0.0.0.0:*                   LIST                                                                             EN      4787/mysqld
tcp        0      0 0.0.0.0:4567                0.0.0.0:*                   LIST                                                                             EN      4787/mysqld

Step 15:To check whether Galera is installed correctly and running properly, login into mysql and execute following command:

mysql> show status like 'wsrep%';

You will see similar output as below in all servers:

Server root:

mysql> show status like 'wsrep%';
+----------------------------+----------------------------------------------------------------+
| Variable_name              | Value                                                          |
+----------------------------+----------------------------------------------------------------+
| wsrep_local_state_uuid     | e0a16aa3-663e-11e2-0800-df7a96683b0b                           |
| wsrep_protocol_version     | 4                                                              |
| wsrep_last_committed       | 12                                                             |
| wsrep_replicated           | 0                                                              |
| wsrep_replicated_bytes     | 0                                                              |
| wsrep_received             | 11                                                             |
| wsrep_received_bytes       | 1520                                                           |
| wsrep_local_commits        | 0                                                              |
| wsrep_local_cert_failures  | 0                                                              |
| wsrep_local_bf_aborts      | 0                                                              |
| wsrep_local_replays        | 0                                                              |
| wsrep_local_send_queue     | 0                                                              |
| wsrep_local_send_queue_avg | 0.000000                                                       |
| wsrep_local_recv_queue     | 0                                                              |
| wsrep_local_recv_queue_avg | 0.111111                                                       |
| wsrep_flow_control_paused  | 0.000000                                                       |
| wsrep_flow_control_sent    | 0                                                              |
| wsrep_flow_control_recv    | 0                                                              |
| wsrep_cert_deps_distance   | 1.000000                                                       |
| wsrep_apply_oooe           | 0.000000                                                       |
| wsrep_apply_oool           | 0.000000                                                       |
| wsrep_apply_window         | 1.000000                                                       |
| wsrep_commit_oooe          | 0.000000                                                       |
| wsrep_commit_oool          | 0.000000                                                       |
| wsrep_commit_window        | 1.000000                                                       |
| wsrep_local_state          | 4                                                              |
| wsrep_local_state_comment  | Synced                                                         |
| wsrep_cert_index_size      | 3                                                              |
| wsrep_causal_reads         | 0                                                              |
| wsrep_incoming_addresses   | 192.168.124.128:3306,192.168.124.136:3306,192.168.124.137:3306 |
| wsrep_cluster_conf_id      | 5                                                              |
| wsrep_cluster_size         | 3                                                              |
| wsrep_cluster_state_uuid   | e0a16aa3-663e-11e2-0800-df7a96683b0b                           |
| wsrep_cluster_status       | Primary                                                        |
| wsrep_connected            | ON                                                             |
| wsrep_local_index          | 2                                                              |
| wsrep_provider_name        | Galera                                                         |
| wsrep_provider_vendor      | Codership Oy                              |
| wsrep_provider_version     | 23.2.2(r137)                                                   |
| wsrep_ready                | ON                                                             |
+----------------------------+----------------------------------------------------------------+
40 rows in set (0.03 sec)

2 Server 192.168.124.128 and 192.168.124.136

+----------------------------+----------------------------------------------------------------+
| Variable_name              | Value                                                          |
+----------------------------+----------------------------------------------------------------+
| wsrep_local_state_uuid     | e0a16aa3-663e-11e2-0800-df7a96683b0b                           |
| wsrep_protocol_version     | 4                                                              |
| wsrep_last_committed       | 12                                                             |
| wsrep_replicated           | 1                                                              |
| wsrep_replicated_bytes     | 156                                                            |
| wsrep_received             | 7                                                              |
| wsrep_received_bytes       | 1108                                                           |
| wsrep_local_commits        | 0                                                              |
| wsrep_local_cert_failures  | 0                                                              |
| wsrep_local_bf_aborts      | 0                                                              |
| wsrep_local_replays        | 0                                                              |
| wsrep_local_send_queue     | 0                                                              |
| wsrep_local_send_queue_avg | 0.000000                                                       |
| wsrep_local_recv_queue     | 0                                                              |
| wsrep_local_recv_queue_avg | 0.000000                                                       |
| wsrep_flow_control_paused  | 0.000000                                                       |
| wsrep_flow_control_sent    | 0                                                              |
| wsrep_flow_control_recv    | 0                                                              |
| wsrep_cert_deps_distance   | 1.000000                                                       |
| wsrep_apply_oooe           | 0.000000                                                       |
| wsrep_apply_oool           | 0.000000                                                       |
| wsrep_apply_window         | 1.000000                                                       |
| wsrep_commit_oooe          | 0.000000                                                       |
| wsrep_commit_oool          | 0.000000                                                       |
| wsrep_commit_window        | 1.000000                                                       |
| wsrep_local_state          | 4                                                              |
| wsrep_local_state_comment  | Synced                                                         |
| wsrep_cert_index_size      | 3                                                              |
| wsrep_causal_reads         | 0                                                              |
| wsrep_incoming_addresses   | 192.168.124.128:3306,192.168.124.136:3306,192.168.124.137:3306 |
| wsrep_cluster_conf_id      | 5                                                              |
| wsrep_cluster_size         | 3                                                              |
| wsrep_cluster_state_uuid   | e0a16aa3-663e-11e2-0800-df7a96683b0b                           |
| wsrep_cluster_status       | Primary                                                        |
| wsrep_connected            | ON                                                             |
| wsrep_local_index          | 1                                                              |
| wsrep_provider_name        | Galera                                                         |
| wsrep_provider_vendor      | Codership Oy                              |
| wsrep_provider_version     | 23.2.2(r137)                                                   |
| wsrep_ready                | ON                                                             |
+----------------------------+----------------------------------------------------------------+
40 rows in set (0.01 sec)


+----------------------------+----------------------------------------------------------------+
| Variable_name              | Value                                                          |
+----------------------------+----------------------------------------------------------------+
| wsrep_local_state_uuid     | e0a16aa3-663e-11e2-0800-df7a96683b0b                           |
| wsrep_protocol_version     | 4                                                              |
| wsrep_last_committed       | 12                                                             |
| wsrep_replicated           | 1                                                              |
| wsrep_replicated_bytes     | 148                                                            |
| wsrep_received             | 3                                                              |
| wsrep_received_bytes       | 436                                                            |
| wsrep_local_commits        | 0                                                              |
| wsrep_local_cert_failures  | 0                                                              |
| wsrep_local_bf_aborts      | 0                                                              |
| wsrep_local_replays        | 0                                                              |
| wsrep_local_send_queue     | 0                                                              |
| wsrep_local_send_queue_avg | 0.000000                                                       |
| wsrep_local_recv_queue     | 0                                                              |
| wsrep_local_recv_queue_avg | 0.000000                                                       |
| wsrep_flow_control_paused  | 0.000000                                                       |
| wsrep_flow_control_sent    | 0                                                              |
| wsrep_flow_control_recv    | 0                                                              |
| wsrep_cert_deps_distance   | 1.000000                                                       |
| wsrep_apply_oooe           | 0.000000                                                       |
| wsrep_apply_oool           | 0.000000                                                       |
| wsrep_apply_window         | 1.000000                                                       |
| wsrep_commit_oooe          | 0.000000                                                       |
| wsrep_commit_oool          | 0.000000                                                       |
| wsrep_commit_window        | 1.000000                                                       |
| wsrep_local_state          | 4                                                              |
| wsrep_local_state_comment  | Synced                                                         |
| wsrep_cert_index_size      | 3                                                              |
| wsrep_causal_reads         | 0                                                              |
| wsrep_incoming_addresses   | 192.168.124.128:3306,192.168.124.136:3306,192.168.124.137:3306 |
| wsrep_cluster_conf_id      | 5                                                              |
| wsrep_cluster_size         | 3                                                              |
| wsrep_cluster_state_uuid   | e0a16aa3-663e-11e2-0800-df7a96683b0b                           |
| wsrep_cluster_status       | Primary                                                        |
| wsrep_connected            | ON                                                             |
| wsrep_local_index          | 0                                                              |
| wsrep_provider_name        | Galera                                                         |
| wsrep_provider_vendor      | Codership Oy                              |
| wsrep_provider_version     | 23.2.2(r137)                                                   |
| wsrep_ready                | ON     



P/S;If you add more  Server Mysql Server to cluster  .This Server, just login into the box and repeat step 1 to 15.and register This server to cluster with command:

mysql> SET GLOBAL wsrep_cluster_address='gcomm://';


Thanks and Best Regards










Monday, December 26, 2011

Setting Up A High-Availability Load Balancer With HAProxy/Pfsense 2.0.1




I/Overview





II/Intro

As lastest document.I give you how to config haproxy on Pfsense 2.0.1.Now I will intro to you how to Setting Up A High-Availability Load Balancer With HAProxy/Pfsense 2.0.1

III/Setting

1/Install haproxy on pfsense as http://conheotiensinh.blogspot.com/2011/12/config-haproxy-with-pfsense-version-201.html
2/Configuring CARP firewall failover

2.1/On both machines, add a firewall to allow all traffic on the SYNC interface:

a. Browse to Firewall | Rules.
b. Click the SYNC Interface tab.
c. Click the "plus" button to add a new firewall rule.
d. Set Protocol to any.

e. Save the changes.
f. Apply changes, if necessary.

2.2/On the backup-pfsense machine, we need to enable CARP synchronization and
configure it as a backup only:

a. Browse to Firewall | Virtual IPs .
b. Click the CARP Settings tab.
c. Check Synchronize Enabled.
d. Set Synchronize Interface to SYNC.


e. Save the changes.
f. We have now finished configuring the backup firewall.

2.3/On the primary-pfsense machine, we need to enable CARP synchronization and
configure it to act as the primary firewall:

a. Br owse to Firewall | Virtual IPs .
b. Click the CARP Settings tab.
c. Check Synchronize Enabled.
d. Set Synchronize Interface to SYNC.

e. Check Synchronize rules
f. Check Synchronize nat
g. Check Synchronize Virtual IPs
h. Set Synchronize to IP to the IP address of backup-pfsense
i. Set Remote System Password to the password of backup-pfsense
j Save the changes

2.4/We must now configure a virtual IP address for the WAN interface on the primary-pfsense machine:

a. Browse to Firewall | Virtual IPs .
b. Click the Virtual IPs tab.
c. Click the "plus" button to add a new virtual IP.
d. Set the Type to CARP.
e. Set the Interface to WAN.
f. Set the IP Address to the single WAN address that will be used throughout
your systems, regardless of whether the primary or backup firewall is in
effect.
g. Create a Virtual IP Password.
h. Leave the VHID Group set to 1.
i. Leave the Advertising Frequency at 0.
j. Add a Description


k.Save the changes
l.Apply changes, if necessary

3/config Sync HAProxy configuration

3.1/On the backup-pfsense machine we need check Sync HAProxy configuration to backup CARP members via XMLRPC.

3.2/On the primary-pfsense machine,we need check Sync HAProxy configuration to backup CARP members via XMLRPC and setting for sync config HaProxy

Now we can config haproxy in primary-Pfsense and it auto sync to backup -Pfsense

Thanks and Best Regards

quan.hoa@conheotiensinh.co.cc

Friday, December 23, 2011

Config HAPROXY with PFSENSE version 2.0.1


Merry Christmas and Happy New Year





I/Intro

As the previous Document about Haproxy , I have explained how to configure a command line .Now with version 2.0.1 pfsense support config haproxy lastest version(1.4.8) via web interface( config easier).although it can not configure advanced features of haproxy through the web interface.

II/Install

1/Install pfsense 2.0.1
2/Install haproxy (The package is available to install from System -> Packages)
3/After Install done We configure Haproxy(Services -> Haproxy)click tab Setting and configure as image
4/Config with ip info:192.168.44.150(Haproxy),192.168.44.130(apache),192.168.44.131(nginx)

III/Config Haproxy

1/config Frontend for haproxy




2/Config Backend for haproxy

a/config server A

b/config server B

3/Check status HAPROXY with URL:http://192.168.44.150/haproxy?stats

NOTE: You must add a firewall rule permitting access to frontend!

Thanks and Best Regards

quan.hoa@conheotiensinh.co.cc

Monday, August 23, 2010

Query Recipient Windows Active Directory directly



I/Intro
As Document http://conheotiensinh.blogspot.com/2010/08/config-iredmail-as-mail-gatewayanti.html.I intro to you how to query recipients use Perl and after discusses with Zhang Huangbin and I promise with him.So today I will intro to you How to query recipient(real-time) Windows Active Directory directly.


II/Config

Step 1:Create /etc/postfix/ldap_user.cf with info:

server_host = 192.168.22.233 ##with 192.168.22.233 As Active Directory
search_base = dc=test, dc=vn
version = 3
query_filter = (&(objectclass=person)(mail=%s))
result_attribute = samaccountname
bind = yes
bind_dn = test\admin # Account use query Recipient.
bind_pw = 123 #pass use query Recipient

Step 2: change config in /etc/postfix/main.cf

relay_recipient_maps = hash:/etc/postfix/relay_recipients
To
relay_recipient_maps = ldap:/etc/postfix/ldap_user.cf

Step 3:restart Postfix

/etc/init.d/postfix restart


Thanks Zhang Huangbin for suggest and Contribute

Please let me know if you have question




Friday, August 20, 2010

Config Mail Gateway LINUX less than 5 minutes (Anti-spam, Mail Anti-virus,Greylisting).

I/Intro

As previous document I intro to you Iredmail as mail server in linux with full-featured (/Postfix/Dovecot/Amavisd/ClamAV/SpamAssassin/RoundCube/iRedAdmin/
postfixadmin).But With this document I will intro iredmail as mail gateway(because By default iredmail config /Amavisd/ClamAV/SpamAssassin/greylisting for anti spam and mail anti-virus).

II/Install Iredmail

Install iredmail as normal But only choose phpmyadmin(for managed greylisting easier) and Awstats

III/Remove some services not use

Because we setup iredmail as Mail gateway so We not use some services:dovecot,pysieved

IV/Change some config

1/Edit /etc/postfix/main.cf

mydestination =
local_recipient_maps =
local_transport =error:local mail delivery disabled

relay_recipient_maps = hash:/etc/postfix/relay_recipients

relay_domains = test.vn

transport_maps = hash:/etc/postfix/transport

comments all line with mysql_* or ldap_*

2/ edit /etc/postfix/master.cf

comment line

#local unix - n n - - local

3/edit /etc/postfix/transport

test.vn smtp:192.168.22.233 #with 192.168.22.233(IP mail server backend as exchange...)

4/create /etc/postfix/relay_recipients

u1@test.vn OK
u2@test.vn OK
ug@test.vn OK
u3@test.vn OK
u4@test.vn OK

5/hashing Databases

postmap /etc/postfix/transport
postmap /etc/postfix/relay_recipients

V/Populating relay_recipients from Active Directory


Note that this script requires perl and Net::LDAP(you need install perl-ldap by yum). However, this does NOT have to be on your email gateway.

Edit the script so that values below are correct:
$VALID = "/etc/postfix/relay_recipients";
$dc1="dc1.test.vn";
$dc2="dc2.test.vn";
$hqbase="cn=Users,dc=test,dc=vn";
$user="cn=user,cn=Users,dc=test,dc=vn";
$passwd="password";

If You have any problem script perl please send mail to quan.hoa@conheotiensinh.co.cc.I will edit for you

VI/Create Bash shell automatic update relay_recipients from Active directory

create bash shell /opt/ad.sh with info:

cd /etc/postfix ; ./getadsmtp.pl && /usr/sbin/postmap relay_recipients
/usr/sbin/postfix reload

use crontab run as your schedule

Beside you can use iredmail for multi domain(can get relay_recipients from Multi Active directory).

Thanks

quan.hoa@conheotiensinh.co.cc



Friday, August 13, 2010

Config cluster Load balancer layer 7 support SSL with Heatbeat,Nginx and Haproxy

I/INTRO

As Document http://conheotiensinh.blogspot.com/2010/06/setting-up-high-availability-load.html.I config Haproxy load balance HTTPS with stunnel(It very easy for config and deploy).But with stunnel you can't detech IP source access to haproxy and Performance can't same as Nginx.So I will intro to you 1 solution resove this problem with Nginx as SSL Reverse Proxy,Heartbeat as cluster and haproxy load balance.

Nginx [engine x] is a HTTP and reverse proxy server, as well as a mail proxy server written by Igor Sysoev. It has been running for more than five years on many heavily loaded Russian sites including Rambler (RamblerMedia.com). According to Netcraft nginx served or proxied 4.70% busiest sites in April 2010. Here are some of success stories: FastMail.FM, Wordpress.com,sourceforge.net....


II/INSTALL

Step 1:config IP As image


Step 2:install haproxy by rpm packet(if you need install haproxy with last version 1.4.8.Please contact me with email:quan.hoa@conheotiensinh.co.cc,I will give it to you) or you can download Haproxy from http://rpm.pbone.net/


Step 4:Install Nginx by rpm packet(if you need install nginx with last version 0.7.67.Please contact me with email:quan.hoa@conheotiensinh.co.cc,I will give it to you) or you can download Nginx from http://rpm.pbone.net/

III/CONFIG

Step 1:config haproxy on both Server .You config as image



Step 3:config Nginx on both serve as image


Step 4:access haproxy status pages with url https://192.168.129.133/haproxy?stats



Step 5:stop Server 1 and access haproxy status page:


if you have question.Please contact me with email quan.hoa@conheotiensinh.co.cc

Thanks

quan.hoa@conheotiensinh.co.cc

Friday, August 6, 2010

LOADBALANCE WITH MULTI PPPoE INTERFACE IN PFSENSE 2.0


I/Intro

Now pfsense release version 1.2.3 .It only support 1 interface use PPPoE.But with pfsense 2.0(Beta 4)you can use multi interface use PPPoE and Version 2.0 change config load balance outbound.So I will intro to you config multi interface use PPPoE.


II/Install Pfsense as previous Document

III/Config multi interface use PPPoE config as images


Note:you can create PPPoE Server for test this document with pfsense

IV/config load balance with pfsense

As previous version (1.2.3) you can config as http://conheotiensinh.blogspot.com/2009/10/multi-wan-load-balancing-outbound-use.html.But with pfsense version 2.0 load balance outbound change config.

step1:config gateway (system ->routing) config 2 gateway as images with 192.168.127.128 and 192.168.128.129 IP of PPPoe server (it will auto detect when connect via PPPoE)


step 2:config groups(system ->routing) change to Groups tab and create group as image


Step 3:view status of Gateway and Groups when 2 line connect


Step 4:Test disconnect 1 interface and view status
Step 5:create rule as image


Thanks

Conheotiensinh(quan.hoa@conheotiensinh.co.cc)