Catatan Yang Mungkin Terlupakan

MySQL Cluster FailOver

1. Persiapan Instalasi.
Download packet yang dibutuhkan untuk server management dan node di web mysql.
Untuk Management Server :
– MySQL-Cluster-gpl-management-7.1.10-1.rhel5.i386.rpm
– MySQL-Cluster-gpl-tools-7.1.10-1.rhel5.i386.rpm

Untuk Mysql API Server :
– MySQL-Cluster-gpl-client-7.1.10-1.rhel5.i386.rpm
– MySQL-Cluster-gpl-server-7.1.10-1.rhel5.i386.rpm
– MySQL-Cluster-gpl-shared-7.1.10-1.rhel5.i386.rpm

Untuk node Server :
– MySQL-Cluster-gpl-client-7.1.10-1.rhel5.i386.rpm
– MySQL-Cluster-gpl-extra-7.1.10-1.rhel5.i386.rpm
– MySQL-Cluster-gpl-server-7.1.10-1.rhel5.i386.rpm
– MySQL-Cluster-gpl-storage-7.1.10-1.rhel5.i386.rpm
– MySQL-Cluster-gpl-tools-7.1.10-1.rhel5.i386.rpm

2. Proses Instalasi
Setelah semua packet didownload dan disimpan dalam 1 folder.

Untuk server management :

– cek apakah ada aplikasi mysql.server yang sudah terinstall dengan perintah : rpm -qa |grep mysql
– apabila mysql.server sudah terinstall hapus aplikasi tersebut dengan perintah : rpm -e mysql –nodeps atau yum remove mysql
– kemudian lakukan instalasi mysql-cluster management dengan perintah:

rpm -ivh MySQL-Cluster-gpl-management-7.1.10-1.rhel5.i386.rpm
 MySQL-Cluster-gpl-tools-7.1.10-1.rhel5.i386.rpm

– atau lakukan installasi dengan perintah rpm -ivh *.rpm
– buat direcory /var/lib/mysql-cluster
– rubah kepemikannya menjadi mysql:mysql
– buat file config.cnf di folder .mysql-cluster
– rubah file tersebut bisa di eksekusi dengan perintah chmod 755
– rubah kepemilikan menjadi mysql:mysql
– masukkan baris berikut untuk config.cnf
# Options affecting ndbd processes on all data nodes:

[NDBD DEFAULT]
 NoOfReplicas=2
 # Number of replicas
DataMemory=1024M
 #DataMemory=512M
 #DataMemory=1024M
 #DataMemory=256M
 #DataMemory=128M
 # How much memory to allocate for data storage
 # mysql> ALTER TABLE msg_inbox ENGINE=NDBCLUSTER;
 # ERROR 1114 (HY000): The table '#sql-e58_2' is full
 # mysql> ALTER TABLE msg_inbox ENGINE=NDBCLUSTER;
 # ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
 # Node 2: Data usage increased to 80%(13128 32K pages of total 16384)
 # Node 3: Data usage increased to 80%(13145 32K pages of total 16384)
IndexMemory=96M
 #IndexMemory=128M
 #IndexMemory=256M
 #IndexMemory=512M
 #IndexMemory=128M
 #IndexMemory=96M
 # How much memory to allocate for index storage
 # For DataMemory and IndexMemory, we have used the
 # default values. Since the "world" database takes up
 # only about 500KB, this should be more than enough for
 # this example Cluster setup.
 # ndb_mgm> Node 3: Forced node shutdown completed. Occured during startphase 5.
 # Caused by error 2815: 'File not found(Ndbd file system inconsistency error,
 # please report a bug). Ndbd file system error, restart node initial'.
 # Node 2: Forced node shutdown completed. Occured during startphase 4.
 # Caused by error 2308: 'Another node failed during system restart,
 # please investigate error(s) on other node(s)(Restart error). Temporary error, restart node'.
LockPagesInMainMemory=0
 # Node 3: Forced node shutdown completed.
 # Caused by error 2305: 'Node lost connection to other nodes
 # and can not form a unpartitioned cluster, please investigate if there are
 # error(s) on other node(s)(Arbitration error). Temporary error, restart node'.
MaxNoOfOrderedIndexes=512
 # MaxNoOfOrderedIndexes=1024
 # ndb_mgm> Node 3: Forced node shutdown completed.
 # Caused by error 2305: 'Node lost connection to other nodes
 # and can not form a unpartitioned cluster, please investigate if there are
 # error(s) on other node(s)(Arbitration error). Temporary error, restart node'.
TimeBetweenLocalCheckpoints=6
 NoOfFragmentLogFiles=32
 # mysql> ALTER TABLE log_event ENGINE=NDBCLUSTER;
 # ERROR 1297 (HY000): Got temporary error 410 'REDO log files overloaded (decrease
 # TimeBetweenLocalCheckpoints or increase NoOfFragmentLogFiles)' from NDBCLUSTER mysql>
HeartbeatIntervalDbDb=1024
 HeartbeatIntervalDbApi=1024
 # (one [ndbd] section per data node)
 # ndb_mgm> Node 3: Forced node shutdown completed.
 # Caused by error 2305: 'Node lost connection to other nodes
 # and can not form a unpartitioned cluster, please investigate if there are
 # error(s) on other node(s)(Arbitration error). Temporary error, restart node'.
LogLevelStartup=5
 LogLevelError=10
 # Log levels. Used to log messages for various events.
 # By default all loggings are disabled. For our reference we would set the log level to 5
 # for Startup messages and 10 for Error messages/warnings. Minimum level is 0 (disabled) and maximum # is 15.
# TCP/IP options:
 [TCP DEFAULT]
 SendBufferMemory= 8M
 portnumber=2202 # This the default; however, you can use any
 # port that is free for all the hosts in cluster
 # Note: It is recommended beginning with MySQL 5.0 that
 # you do not specify the portnumber at all and simply allow
 # the default value to be used instead
# Management process options:
[NDB_MGMD]
 hostname=192.168.0.227
 # Hostname or IP address of MGM node
 datadir=/var/lib/mysql-cluster
 # Directory for MGM node log files
# Options for data node "A":
 [NDBD]
 hostname=192.168.0.237
 # Hostname or IP address
 datadir=/var/lib/mysql-cluster
 # Directory for this data node's data files
# Options for data node "B":
 [NDBD]
 hostname=192.168.0.246
 # Hostname or IP address
 datadir=/var/lib/mysql-cluster
 # Directory for this data node's data files
# SQL node options:
 # [MYSQLD]
 # hostname=192.168.0.227
 # Hostname or IP address
 # (additional mysqld connections can be
 # specified for this node for various
 # purposes such as running ndb_restore)
[MYSQLD]
 hostname=192.168.0.230
 # Hostname or IP address
 # (additional mysqld connections can be
 # specified for this node for various
 # purposes such as running ndb_restore)
[MYSQLD]
 hostname=192.168.0.236
 # Hostname or IP address
 # (additional mysqld connections can be
 # specified for this node for various
 # purposes such as running ndb_restore)
[MYSQLD]
[MYSQLD]

 

– jalankan mysql-cluster management dengan perintah :

ndb_mgmd -f /var/lib/mysql-cluster/config.ini

– apabila nanti diperlukan untuk perubahan konfigurasi jalankan mysql-cluster management dengan perintah :

ndb_mgmd -f /var/lib/mysql-cluster/config.ini --reload

untuk mysql-cluster api node

– cek apakah ada aplikasi mysql.server yang sudah terinstall dengan perintah :

rpm -qa |grep mysql

– apabila mysql.server sudah terinstall hapus aplikasi tersebut dengan perintah :

rpm -e mysql --nodeps atau yum remove mysql

– kemudian lakukan instalasi mysql-cluster api node dengan perintah:

rpm -ivh MySQL-Cluster-gpl-client-7.1.10-1.rhel5.i386.rpm
 MySQL-Cluster-gpl-server-7.1.10-1.rhel5.i386.rpm
 MySQL-Cluster-gpl-shared-7.1.10-1.rhel5.i386.rpm

– atau lakukan installasi dengan perintah rpm -ivh *.rpm
– ubah isi dari file my.cnf yang berada di folder /etc
– apabila tidak ada file my.cnf di folder /etc maka buat file my.cnf dah rubah agar file tersebut bisa dieksekusi
– isikan file my.cnf dengan baris berikut :

[mysqld]
 datadir=/var/lib/mysql
 socket=/var/lib/mysql/mysql.sock
 default-table-type=NDBCLUSTER
#Don't cache host names.
 skip-host-cache
 #Don't resolve hostnames. All hostnames are IP's or 'localhost'.
 skip-name-resolve
#user=mysql
 # Default to using old password format for compatibility with mysql 3.x
 # clients (those using the mysqlclient10 compatibility package).
 #old_passwords=1
ndbcluster
 # IP address of the cluster management node
 ndb-connectstring=192.168.0.227
 #default-storage-engine=NDBCLUSTER
 default-storage-engine=NDBCLUSTER
[mysql_cluster]
 # IP address of the cluster management node
 ndb-connectstring=192.168.0.227
[ndb_mgmd]
 config-file=/var/lib/mysql-cluster/config.cnf
#max_connections=341
 #query_cache_size=16M
 #thread_concurrency = 4
[mysqld_safe]
 log-error=/var/log/mysqld.log
 pid-file=/var/run/mysqld/mysqld.pid

– jalankan mysql server dengan perintah :

/etc/init.d/mysql start

untuk mysql-cluster data node

– cek apakah ada aplikasi mysql.server yang sudah terinstall dengan perintah : rpm -qa |grep mysql
– apabila mysql.server sudah terinstall hapus aplikasi tersebut dengan perintah : rpm -e mysql –nodeps atau yum remove mysql
– kemudian lakukan instalasi mysql-cluster data node dengan perintah :

rpm -ivh MySQL-Cluster-gpl-client-7.1.10-1.rhel5.i386.rpm
 MySQL-Cluster-gpl-extra-7.1.10-1.rhel5.i386.rpm
 MySQL-Cluster-gpl-server-7.1.10-1.rhel5.i386.rpm
 MySQL-Cluster-gpl-storage-7.1.10-1.rhel5.i386.rpm
 MySQL-Cluster-gpl-tools-7.1.10-1.rhel5.i386.rpm

– atau lakukan installasi dengan perintah rpm -ivh *.rpm
– untuk data node tidak diperlukan konfigurasi
– untuk menjalankan mysql-cluster node dengan perintah : ndbd –initial
– apabila untuk menjalankan ulang cukup dengan perintah : ndbd
– untuk mysql-cluster data node bisa dibuat jadi satu dengan mysql-cluster sql api.
– untuk memisahkannya bisa mematikan service mysql di server data node atau tidak melakukan installasi MySQL-Cluster-gpl-server-7.1.10-1.rhel5.i386.rpm

apabila semua mysql-cluster node sudah di instalasi lakukan pengecekan pada mysql-cluster di management server dengan perintah :

[root@mngt1 ~]# ndb_mgm
 -- NDB Cluster -- Management Client --
 ndb_mgm> show
 Connected to Management Server at: localhost:1186
 Cluster Configuration
 ---------------------
 [ndbd(NDB)] 2 node(s)
 id=2 @192.168.0.237 (mysql-5.1.51 ndb-7.1.10, Nodegroup: 0, Master)
 id=3 @192.168.0.246 (mysql-5.1.51 ndb-7.1.10, Nodegroup: 0)
[ndb_mgmd(MGM)] 1 node(s)
 id=1 @192.168.0.227 (mysql-5.1.51 ndb-7.1.10)
[mysqld(API)] 4 node(s)
 id=4 @192.168.0.230 (mysql-5.1.51 ndb-7.1.10)
 id=5 @192.168.0.236 (mysql-5.1.51 ndb-7.1.10)
 id=6 (not connected, accepting connect from any host)
 id=7 (not connected, accepting connect from any host)

 

dengan demikian untuk mysql cluster sudah berjalan dengan sempurna.

kemudian untuk loadbalancingnya Fail-over :

salah satu kendala yang dihadapi adalah apabila salah satu server mati dan perlu dilakukan penggantian IP maka haltersebut akan menjadikan downtime di aplikasi yang semestinya tidak boleh terjadi.
karena untuk load balancing belum diperlukan secara maksimal maka kali ini saya hanya menggunakan server fail-over.
pada prinsipnya sebuah server fail over memiliki virtual ip yang dipakai bersama dan virtual ip tersebut yang dipakai oleh client sebagai IP targetnya. untuk instalasi mysql-cluster fail over di tempatkan pada mysql-cluster sql api.

kebutuhan instalasi :
– untuk instalasi fail-over dimulai dari
– instalasi epel
– download epel dari repositorinya :

wget http://download.fedoraproject.org/pub/epel/5/i386/epel-release-5-4.noarch.rpm

– instalasi epel dengan perintah :

rpm -ivh epel-release-5-4.noarch.rpm

– setelah instalasi kemudian lakukan instalasi heartbeat
– dengan perintah :

yum install heartbeat
[root@sql2 ~]# yum install heartbeat
 epel 100% |=========================| 3.7 kB 00:00
 26e1b90e227c9f61c5a2529c2 100% |=========================| 3.0 MB 02:00
 Setting up Install Process
 Parsing package install arguments
 Resolving Dependencies
 --> Running transaction check
 ---> Package heartbeat.i386 0:2.1.4-11.el5 set to be updated
 --> Processing Dependency: PyXML for package: heartbeat
 --> Processing Dependency: libstonith.so.1 for package: heartbeat
 --> Processing Dependency: libpils.so.1 for package: heartbeat
 --> Processing Dependency: libltdl.so.3 for package: heartbeat
 --> Processing Dependency: libnet.so.1 for package: heartbeat
 --> Running transaction check
 ---> Package heartbeat-stonith.i386 0:2.1.4-11.el5 set to be updated
 --> Processing Dependency: libopenhpi.so.2 for package: heartbeat-stonith
 --> Processing Dependency: net-snmp-libs for package: heartbeat-stonith
 --> Processing Dependency: libnetsnmp.so.10 for package: heartbeat-stonith
 ---> Package libnet.i386 0:1.1.5-1.el5 set to be updated
 ---> Package libtool-ltdl.i386 0:1.5.22-7.el5_4 set to be updated
 ---> Package PyXML.i386 0:0.8.4-4.el5_4.2 set to be updated
 ---> Package heartbeat-pils.i386 0:2.1.4-11.el5 set to be updated
 --> Running transaction check
 ---> Package net-snmp-libs.i386 1:5.3.2.2-9.el5_5.1 set to be updated
 ---> Package openhpi-libs.i386 0:2.14.0-5.el5 set to be updated
 --> Finished Dependency Resolution
Dependencies Resolved
=============================================================================
 Package Arch Version Repository Size
 =============================================================================
 Installing:
 heartbeat i386 2.1.4-11.el5 epel 1.6 M
 Installing for dependencies:
 PyXML i386 0.8.4-4.el5_4.2 base 1.0 M
 heartbeat-pils i386 2.1.4-11.el5 epel 90 k
 heartbeat-stonith i386 2.1.4-11.el5 epel 175 k
 libnet i386 1.1.5-1.el5 epel 58 k
 libtool-ltdl i386 1.5.22-7.el5_4 base 37 k
 net-snmp-libs i386 1:5.3.2.2-9.el5_5.1 base 1.3 M
 openhpi-libs i386 2.14.0-5.el5 base 157 k
Transaction Summary
 =============================================================================
 Install 8 Package(s)
 Update 0 Package(s)
 Remove 0 Package(s)
Total download size: 4.4 M
 Is this ok [y/N]: y
 Downloading Packages:
 (1/8): heartbeat-2.1.4-11 100% |=========================| 1.0 MB 01:51
 http://ftp.riken.jp/Linux/fedora/epel/5/i386/heartbeat-2.1.4-11.el5.i386.rpm: [E rrno 4] Socket Error: timed out
 Trying other mirror.
 (1/8): heartbeat-2.1.4-11 100% |=========================| 1.6 MB 01:08
 (2/8): heartbeat-pils-2.1 100% |=========================| 90 kB 00:03
 (3/8): PyXML-0.8.4-4.el5_ 100% |=========================| 1.0 MB 00:59
 (4/8): openhpi-libs-2.14. 100% |=========================| 157 kB 00:20
 (5/8): net-snmp-libs-5.3. 100% |=========================| 1.3 MB 00:57
 (6/8): libtool-ltdl-1.5.2 100% |=========================| 37 kB 00:00
 (7/8): libnet-1.1.5-1.el5 100% |=========================| 58 kB 00:02
 (8/8): heartbeat-stonith- 100% |=========================| 175 kB 00:05
 warning: rpmts_HdrFromFdno: Header V3 DSA signature: NOKEY, key ID 217521f6
 Importing GPG key 0x217521F6 "Fedora EPEL " from /etc/pk i/rpm-gpg/RPM-GPG-KEY-EPEL
 Is this ok [y/N]: y
 warning: rpmts_HdrFromFdno: Header V3 DSA signature: NOKEY, key ID e8562897
 Importing GPG key 0xE8562897 "CentOS-5 Key (CentOS 5 Official Signing Key) " from http://mirror.centos.org/centos/RPM-GPG-KEY-CentOS-5
 Is this ok [y/N]: y
 Running rpm_check_debug
 Running Transaction Test
 Finished Transaction Test
 Transaction Test Succeeded
 Running Transaction
 Installing: libtool-ltdl ######################### [1/8]
 Installing: heartbeat-pils ######################### [2/8]
 Installing: net-snmp-libs ######################### [3/8]
 Installing: openhpi-libs ######################### [4/8]
 Installing: heartbeat-stonith ######################### [5/8]
 Installing: PyXML ######################### [6/8]
 Installing: libnet ######################### [7/8]
 Installing: heartbeat ######################### [8/8]
Installed: heartbeat.i386 0:2.1.4-11.el5
 Dependency Installed: PyXML.i386 0:0.8.4-4.el5_4.2 heartbeat-pils.i386 0:2.1.4-1 1.el5 heartbeat-stonith.i386 0:2.1.4-11.el5 libnet.i386 0:1.1.5-1.el5 libtool-lt dl.i386 0:1.5.22-7.el5_4 net-snmp-libs.i386 1:5.3.2.2-9.el5_5.1 openhpi-libs.i38 6 0:2.14.0-5.el5
 Complete!

– setelah hearbeat terinstall dengan sempurna kemudian install mon
– instalasi mon dengan perintah ;

[root@sql2 ~]# yum install mon
 Setting up Install Process
 Parsing package install arguments
 Resolving Dependencies
 --> Running transaction check
 ---> Package mon.i386 0:1.2.0-6.el5 set to be updated
 --> Processing Dependency: perl(Time::Period) for package: mon
 --> Processing Dependency: fping for package: mon
 --> Processing Dependency: perl(Net::Telnet) for package: mon
 --> Processing Dependency: perl(Authen::PAM) for package: mon
 --> Proessing Dependency: perl(Net::LDAP) for package: mon
 --> Processing Dependency: perl(SNMP) for package: mon
 --> Processing Dependency: perl(Statistics::Descriptive) for package: mon
 --> Processing Dependency: perl(SNMP) >= 1.8 for package: mon
 --> Processing Dependency: perl(Mon::Client) for package: mon
 --> Processing Dependency: perl(Net::SNPP) for package: mon
 --> Running transaction check
 ---> Package perl-Mon.noarch 0:0.11-1.el5 set to be updated
 --> Processing Dependency: perl(Convert::BER) for package: perl-Mon
 ---> Package perl-Authen-PAM.i386 0:0.16-8.el5 set to be updated
 ---> Package net-snmp-perl.i386 1:5.3.2.2-9.el5_5.1 set to be updated
 --> Processing Dependency: net-snmp = 1:5.3.2.2-9.el5_5.1 for package: net-snmp-perl
 --> Processing Dependency: libsensors.so.3 for package: net-snmp-perl
 ---> Package fping.i386 0:2.4b2-7.el5 set to be updated
 ---> Package perl-LDAP.noarch 1:0.33-3.fc6 set to be updated
 --> Processing Dependency: perl(Convert::ASN1) for package: perl-LDAP
 --> Processing Dependency: perl(XML::SAX::Base) for package: perl-LDAP
 ---> Package perl-Time-Period.noarch 0:1.20-2.el5 set to be updated
 ---> Package perl-Net-Telnet.noarch 0:3.03-5 set to be updated
 ---> Package perl-Statistics-Descriptive.noarch 0:2.6-2.el5.1 set to be updated
 ---> Package perl-Net-SNPP.noarch 0:1.17-6.el5 set to be updated
 --> Running transaction check
 ---> Package net-snmp.i386 1:5.3.2.2-9.el5_5.1 set to be updated
 ---> Package lm_sensors.i386 0:2.10.7-9.el5 set to be updated
 ---> Package perl-Convert-ASN1.noarch 0:0.20-1.1 set to be updated
 ---> Package perl-XML-SAX.noarch 0:0.14-8 set to be updated
 --> Processing Dependency: perl(XML::NamespaceSupport) for package: perl-XML-SAX
 ---> Package perl-Convert-BER.noarch 0:1.3101-2.el5 set to be updated
 --> Running transaction check
 ---> Package perl-XML-NamespaceSupport.noarch 0:1.09-1.2.1 set to be updated
 --> Finished Dependency Resolution
Dependencies Resolved
=============================================================================
 Package Arch Version Repository Size
 =============================================================================
 Installing:
 mon i386 1.2.0-6.el5 epel 355 k
 Installing for dependencies:
 fping i386 2.4b2-7.el5 epel 30 k
 lm_sensors i386 2.10.7-9.el5 base 511 k
 net-snmp i386 1:5.3.2.2-9.el5_5.1 base 697 k
 net-snmp-perl i386 1:5.3.2.2-9.el5_5.1 base 198 k
 perl-Authen-PAM i386 0.16-8.el5 epel 36 k
 perl-Convert-ASN1 noarch 0.20-1.1 base 42 k
 perl-Convert-BER noarch 1.3101-2.el5 epel 33 k
 perl-LDAP noarch 1:0.33-3.fc6 base 316 k
 perl-Mon noarch 0.11-1.el5 epel 36 k
 perl-Net-SNPP noarch 1.17-6.el5 epel 27 k
 perl-Net-Telnet noarch 3.03-5 base 56 k
 perl-Statistics-Descriptive noarch 2.6-2.el5.1 epel 21 k
 perl-Time-Period noarch 1.20-2.el5 epel 14 k
 perl-XML-NamespaceSupport noarch 1.09-1.2.1 base 15 k
 perl-XML-SAX noarch 0.14-8 base 77 k
Transaction Summary
 =============================================================================
 Install 16 Package(s)
 Update 0 Package(s)
 Remove 0 Package(s)
Total download size: 2.4 M
 Is this ok [y/N]: y
 Downloading Packages:
 (1/16): perl-Net-SNPP-1.1 100% |=========================| 27 kB 00:00
 (2/16): perl-Convert-BER- 100% |=========================| 33 kB 00:03
 (3/16): perl-XML-SAX-0.14 100% |=========================| 77 kB 00:01
 (4/16): perl-Statistics-D 100% |=========================| 21 kB 00:00
 (5/16): perl-Net-Telnet-3 100% |=========================| 56 kB 00:00
 (6/16): perl-Convert-ASN1 100% |=========================| 42 kB 00:00
 (7/16): perl-Time-Period- 100% |=========================| 14 kB 00:00
 (8/16): perl-LDAP-0.33-3. 100% |=========================| 316 kB 00:06
 (9/16): perl-XML-Namespac 100% |=========================| 15 kB 00:00
 (10/16): mon-1.2.0-6.el5. 100% |=========================| 355 kB 00:12
 (11/16): fping-2.4b2-7.el 100% |=========================| 30 kB 00:01
 (12/16): net-snmp-perl-5. 100% |=========================| 198 kB 00:05
 (13/16): perl-Authen-PAM- 100% |=========================| 36 kB 00:00
 (14/16): lm_sensors-2.10. 100% |=========================| 511 kB 00:11
 (15/16): perl-Mon-0.11-1. 100% |=========================| 36 kB 00:00
 (16/16): net-snmp-5.3.2.2 100% |=========================| 697 kB 00:14
 Running rpm_check_debug
 Running Transaction Test
 Finished Transaction Test
 Transaction Test Succeeded
 Running Transaction
 Installing: lm_sensors ####################### [ 1/16]
 Installing: net-snmp ####################### [ 2/16]
 Installing: net-snmp-perl ####################### [ 3/16]
 Installing: perl-Net-SNPP ####################### [ 4/16]
 Installing: perl-Convert-BER ####################### [ 5/16]
 Installing: perl-Mon ####################### [ 6/16]
 Installing: perl-Statistics-Descriptive ####################### [ 7/16]
 Installing: perl-Net-Telnet ####################### [ 8/16]
 Installing: perl-Convert-ASN1 ####################### [ 9/16]
 Installing: perl-Time-Period ####################### [10/16]
 Installing: perl-XML-NamespaceSupport ####################### [11/16]
 Installing: perl-XML-SAX ####################### [12/16]
 Installing: perl-LDAP ####################### [13/16]
 Installing: fping ####################### [14/16]
 Installing: perl-Authen-PAM ####################### [15/16]
 Installing: mon ####################### [16/16]
Installed: mon.i386 0:1.2.0-6.el5
 Dependency Installed: fping.i386 0:2.4b2-7.el5 lm_sensors.i386 0:2.10.7-9.el5 ne t-snmp.i386 1:5.3.2.2-9.el5_5.1 net-snmp-perl.i386 1:5.3.2.2-9.el5_5.1 perl-Auth en-PAM.i386 0:0.16-8.el5 perl-Convert-ASN1.noarch 0:0.20-1.1 perl-Convert-BER.no arch 0:1.3101-2.el5 perl-LDAP.noarch 1:0.33-3.fc6 perl-Mon.noarch 0:0.11-1.el5 p erl-Net-SNPP.noarch 0:1.17-6.el5 perl-Net-Telnet.noarch 0:3.03-5 perl-Statistics -Descriptive.noarch 0:2.6-2.el5.1 perl-Time-Period.noarch 0:1.20-2.el5 perl-XML- NamespaceSupport.noarch 0:1.09-1.2.1 perl-XML-SAX.noarch 0:0.14-8
 Complete!

– untuk konfigurasi hearbeat

 sql1.subekti.web.id 192.168.0.230
 sql2.subekti.web.id 192.168.0.236

The master keeps the original IP – for now – so all applications still connect to the same machine.
Check this other tutorial about replication here.
• Firewalls updates
Apply the master’s rules on the slave ie 192.168.0.5 identical to 192.168.0.2 so each single program can connect to the slave if requested.
This method could be used to implement load-balancing in a quick and easy way.
Allow all traffic between master and slave. This is important to get Heartbeat and Mon to check services, as well as the Mysql replication protocol.
• Disable Selinux on the 2 machines
It creates problems with heartbeat
In /etc/selinux/config
set “SELINUX=disabled”
Changes need unfortunately a server reboot to be applied

Configuration files are located in /etc/ha.d/. You need to create those 3 files on the two servers:

cat > /etc/ha.d/authkeys
 auth 1
 1 crc
Set appropriate rights to the file
 chmod 600 /etc/ha.d/authkeys
cat > /etc/ha.d/haresources
 ssql1.subekti.web.id 192.168.0.229 mysql mon
haresources must contain the virtual IP.
cat > /etc/ha.d/ha.cf
 logfile /var/log/ha-log
 keepalive 2
 # the time to wait before declaring a node to be dead
 deadtime 10
 # this is the time before heartbeat will start the resources the first time it starts ie mon,mysql...
 initdead 20
 bcast eth0
 node sql1.subekti.web.id
 node sql2.subekti.web.id
 # Set this to "on" only if you have a multi-master setup
 auto_failback off
 # will ping the default gateway to check the network connectivity
 ping 192.168.0.40
 respawn hacluster /usr/lib/heartbeat/ipfail

 

Note /etc/hosts file on BOTH machines should contain entries for the 2 hosts

192.168.0.230 sql1.subekti.web.id
192.168.0.236 sql2.subekti.web.id

– konfigurasi mon

cat > mon.cf
 cfbasedir = /etc/mon
 pidfile = /var/run/mon.pid
 statedir = /var/lib/mon/state.d
 logdir = /var/lib/mon/log.d
 dtlogfile = /var/lib/mon/log.d/downtime.log
 alertdir = /usr/lib/mon/alert.d
 mondir = /usr/lib/mon/mon.d
 maxprocs = 20
 histlength = 100
 randstart = 60s
 authtype = pam
 userfile = /etc/mon/userfile
 # Virtual IP
 hostgroup mysql_servers 192.168.0.229
 watch mysql_servers
 mysql
 ## Mon will do a test on Mysql port every 60 seconds
 interval 1m
 monitor mysql.monitor
 period wd {Mon-Sun}
 alert bring-ha-down.alert
 alert mail.alert -S "Host1 MYSQL is down" admin@example.com
 upalert mail.alert -S "Host1 MYSQL server is back up" admin@example.com
 alertevery 600s
 ## Sends an alert after 3 failures
 ## ie 2mn
 alertafter 3

– Create a script to bring heartbeat down if Mysql service becomes unavailable

cat > /usr/local/mon/alert.d/bring-ha-down.alert
 /etc/rc.d/init.d/heartbeat stop

– Change the script’s name to activate mysql mode by default

cd mon.d
 mv msql-mysql.monitor mysql.monitor

– Create a user authorized to access the test database

mysql> GRANT ALL PRIVILEGES ON test.* TO alive@'%' IDENTIFIED BY 'mypassword';

– Edit mysql.server accordingly and add a line to connect to the ‘test’ database

$options{database} ||= "test";

– Make it executable

chmod 755 /etc/rc.d/init.d/mon

– Run Applications
Make sure Mysql is running on the 2 boxes and replication is up-to-date before doing anything else.
Change the Master’s IP to 192.168.0.4 in the config files and restart the 2 servers (master first).
Fire up Mysql and Heartbeat on the master

/etc/rc.d/init/mysqld start
 /etc/rc.d/init.d/heartbeat start

– # You can check hearbeat logs in /var/log/ha-log
The virtual IP will be assigned to eth0:0 after a few seconds
This can be checked with `ifconfig`

– If you experience problems, check messages for Mon in /var/log/messages as well
Do the same on the slave

You can then try to turn the machine off, disconnect the cable or shutdown Mysql;
The virtual IP will migrate to the second server after 2mn (this time can be reduced in mon.cf 1mn and 3 checks.
It is better to give some time in case Mysql becomes unresponsive for a short period of time while there are traffic spikes)

– source : http://www.netexpertise.eu/en/mysql/mysql-failover.html

Leave a Reply