Pandora:Documentation en:Percona Cluster

1 Introduction

This guide’s purpose is to have a working system with an architecture similar to the picture below:

We will have 3 nodes, which all have Percona Cluster installed, and they are all connected. Two of the nodes will have HAProxy and Keepalived installed, otherwise when node 1 fails, the whole cluster will fail. The first node has to be started with bootstrap.

node #1
hostname: percona1
IP: 192.168.70.71

node #2
hostname: percona2
IP: 192.168.70.72

node #3
hostname: percona3
IP: 192.168.70.73

The picture below shows that the system uses two HAProxy servers and keepalived with Virtual IP-addresses. Keepalived is needed for load balancing and high availability. This system ensures that when the first node fails, the others still keep working.

The top part of the architecture is meant for applications, in this case Pandora FMS. We will have a Pandora FMS server, console and agents set up to work with the Percona Cluster.

1.1 Percona introduction

Percona XtraDB Cluster is open-source, free MySQL High Availability software. It integrates Percona Server and Percona XtraBackup with the Galera library of MySQL high availability solutions in a single package which enables you to create a cost-effective MySQL high availability cluster.

The main benefit of using Percona cluster would be the performance gains. Although these gains are not linear, for example 3-node cluster would not result in 3 times higher performance. One reason being that in a cluster one node must also distribute tasks.

The Cluster consists of Nodes. Recommended configuration is to have at least 3 nodes, but you can make it running with 2 nodes as well.

Each Node is regular MySQL / Percona Server setup. The point is that you can convert your existing MySQL / Percona Server into Node and roll Cluster using it as a base. Or otherwise – you can detach Node from Cluster and use it as just a regular server.

Each Node contains the full copy of data. That defines XtraDB Cluster behavior in many ways. And obviously there are benefits and drawbacks.

Percona XtraDB Cluster provides:

• We have successfully performed rolling restarts and upgrades of our production cluster without stopping production traffic.
• Synchronous replication. Transaction either committed on all nodes or none.
• Multi-master replication. You can write to any node.
• Parallel applying events on slave. Real “parallel replication”.
• Automatic node provisioning.
• Data consistency. No more unsynchronized slaves.

Percona XtraDB Cluster drawbacks:

• Overhead of joining new node. The new node has to copy full dataset from one of existing nodes. If it is 100GB, it copies 100GB.
• This can’t be used as an effective write scaling solution. There might be some improvements in write throughput when you run write traffic to 2 nodes vs all traffic to 1 node, but you can’t expect a lot. All writes still have to go on all nodes.
• You have several duplicates of data, for 3 nodes – 3 duplicates.

2 Percona installation

Prerequisites:

• CentOS installation
• Firewall has been set up to allow connecting to ports 3306, 4444, 4567 and 4568 (or disabled)
• Delete any existing mysql installations
• SELinux is disabled - you can use command

echo 0 >/selinux/enforce 

or check status with command

sestatus

---

Check the IP of your nodes with:

ifconfig

Here are the examples:

node #1
hostname: percona1
IP: 192.168.70.71

node #2
hostname: percona2
IP: 192.168.70.72

node #3
hostname: percona3
IP: 192.168.70.73

---

2.1 Installing the required packages

Firstly we need to install the required packages (Yum, Percona RPM and Percona XtraDB Cluster).

Insert command for installing repository with yum package:

yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm

Enter Yes whenever asked. Install XtraDB Cluster package:

yum install Percona-XtraDB-Cluster-56 

Insert command to enable the repository:

rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.x86_64.rpm

---

2.2 Setting up the nodes

Now we are going to set up the configuration file. Individual nodes should be configured to be able to bootstrap the cluster.

2.2.1 Node #1

Open up terminal and open my.cnf file:

nano /etc/my.cnf

Set up my.cnf with this configuration (with your own IP’s) in node1 then save and exit.

Configuration should look like this:

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains the IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #1 address
wsrep_node_address=192.168.70.71

# SST method
wsrep_sst_method=xtrabackup-v2

# Cluster name
wsrep_cluster_name=my_centos_cluster

# Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"

After setting up my.cnf, enter this command:

mysql_install_db --datadir=/var/lib/mysql --user=mysql

Now start node1 with this command:

/etc/init.d/mysql bootstrap-pxc 

or

service mysql bootstrap-pxc

This command will start the cluster with initial wsrep_cluster_address set to gcomm://. This way the cluster will be bootstrapped and in case the node or MySQL have to be restarted later, there would be no need to change the configuration file.

The output should look like this:

Bootstrapping PXC (Percona XtraDB Cluster)Starting MySQL (Percona XtraDB Cluster). SUCCESS!

To check mysql status, first enter:

mysql -u root

then enter this command:

show status like 'wsrep%';

The output should look something like this:

+----------------------------+--------------------------------------+
| Variable_name           | Value                                
+----------------------------+--------------------------------------+
| wsrep_local_state_uuid    | c2883338-834d-11e2-0800-03c9c68e41ec 
...
| wsrep_local_state        | 4                                    
| wsrep_local_state_comment| Synced                               
...
| wsrep_cluster_size       | 1                                    
| wsrep_cluster_status     | Primary                              
| wsrep_connected          | ON                                   
...
| wsrep_ready             | ON                                   
+----------------------------+--------------------------------------+
40 rows in set (0.01 sec)

(Optional) To add password for root, use this command:

UPDATE mysql.user SET password=PASSWORD("yourpassword") where user='root';

then:

FLUSH PRIVILEGES;

In order to perform successful State Snapshot Transfer using XtraBackup new user needs to be set up with proper privileges with these commands:

CREATE USER 'sstuser'@'localhost' IDENTIFIED BY 's3cret';

then:

GRANT RELOAD, LOCK TABLES, REPLICATION CLIENT ON *.* TO 'sstuser'@'localhost';

and also:

FLUSH PRIVILEGES;

Exit mysql with:

exit

When the first node is finished, you will need to configure the other nodes.

2.2.2 Node #2

Now we need to stop node1 with:

service mysql stop

Next step is to open node2 terminal and modify my.cnf with this command:

nano /etc/my.cnf

Paste this configuration to node2 my.cnf (with your own IP’s) then save and exit.

Configuration should look like this:

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #2 address
wsrep_node_address=192.168.70.72

# Cluster name
wsrep_cluster_name=my_centos_cluster

# SST method
wsrep_sst_method=xtrabackup-v2

#Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"

After setting up node2 my.cnf, enter this command:

mysql_install_db --datadir=/var/lib/mysql --user=mysql

If node2 is configured correctly, then we need to start Mysql.

Start mysql with this command:

service mysql start

The output should be like this:

Starting MySQL (Percona XtraDB Cluster)...                 [  OK  ]

This means that everything is correct and working and we can continue to configure node3.

---

2.2.3 Node #3

Now we are going to configure node3. Stop node2 mysql with:

service mysql stop

Open node3 terminal and open my.cnf with:

nano /etc/my.cnf

Paste this configuration to node3 my.cnf (with your own IP’s) then save and exit.

Configuration should look like this:

[mysqld]

datadir=/var/lib/mysql
user=mysql

# Path to Galera library
wsrep_provider=/usr/lib64/libgalera_smm.so

# Cluster connection URL contains IPs of node#1, node#2 and node#3
wsrep_cluster_address=gcomm://192.168.70.71,192.168.70.72,192.168.70.73

# In order for Galera to work correctly binlog format should be ROW
binlog_format=ROW

# MyISAM storage engine has only experimental support
default_storage_engine=InnoDB

# This changes how InnoDB autoincrement locks are managed and is a requirement for Galera
innodb_autoinc_lock_mode=2

# Node #3 address
wsrep_node_address=192.168.70.73

# Cluster name
wsrep_cluster_name=my_centos_cluster

# SST method
wsrep_sst_method=xtrabackup-v2

#Authentication for SST method
wsrep_sst_auth="sstuser:s3cret"

Start node3 mysql with this command

service mysql start

The output should look like this

Starting MySQL (Percona XtraDB Cluster)...                 [  OK  ]

If the output looks like that, then you can continue with the steps. But first stop node3 mysql with this command:

service mysql stop

---

2.3 Starting the Server

If all the nodes work, then we need to stop them all and start them in the correct order. For this, follow these steps:

Now start node1 with this command:

/etc/init.d/mysql bootstrap-pxc

After bootstrapping node1, start node2 and node3 with this command:

service mysql start

After the server has been started, it should receive the state snapshot transfer automatically on nodes 2 and 3.

Now check status in mysql (node1, node2 or node3):

mysql -u root 

or if you set up a password then:

mysql -u root -p

then check status:

show status like 'wsrep%';

Look for this line to see how many nodes are connected to cluster:

| wsrep_cluster_size         | 3                    |

To test the cluster, follow these steps:

Create database on node2 mysql ( connect with mysql -u root or if you set up a password then mysql -u root -p ). Command for database:

CREATE DATABASE percona; 

Output: Query OK, 1 row affected (x sec) Create example table on node3 ( connect with mysql -u root or if you set up a password then mysql -u root -p ). First command for example table:

USE percona;

Output: Database changed

Second command:

CREATE TABLE example (node_id INT PRIMARY KEY, node_name VARCHAR(30));

Output: Query OK, 0 rows affected (x sec) Inserting records on node1 ( connect with mysql -u root or if you set up a password then mysql -u root -p ). Command for inserting records:

INSERT INTO percona.example VALUES (1, 'percona1');

Output: Query OK, 1 row affected (x sec) Retrieving all the rows from that table on node2: Command:

SELECT * FROM percona.example;

Output:

+-----------+------------------+
| node_id   | node_name        |
+-----------+------------------+
|       1   |   percona1       |
+-----------+------------------+
1 row in set (0.00 sec)

This example shows that all nodes in the cluster are synchronized and working as intended.

3 Setting up Pandora Console

Now that you have all the nodes configured, you should start setting up the Pandora Console. Follow these steps:

Install Pandora FMS server and console using this guide: http://wiki.pandorafms.com/index.php?title=Pandora:Documentation_en:Installing#Installation_in_Red_Hat_Enterprise_Linux_.2F_Fedora_.2F_CentOS Open Terminal, log into Pandora server using command

ssh [email protected]<pandora_server_ip>

Connect to MySQL using command

mysql -u root -p 

Enter this command to grant all privileges:

grant all privileges on pandoraname.* ‘pandora’@<node #1 ip> identified by ‘pandora’;  

Run <Pandora FMS server IP>/pandora_console/install.php Use guide: http://wiki.pandorafms.com/index.php?title=Pandora:Documentation_en:Installing#Console_Initial_Configuration

Please note that apache may be under different names, in our case it was httpd. Few examples: apache, apached, http, httpd, apache2

Now we need to configure the config.php and pandora_server.conf files to work with the nodes:

Configure config.php with

nano /var/www/html/pandora_console/include/config.php

Change dbuser to root, dbpass to pandora (default) and dbhost to node #1 ip. Configure pandora_server.conf with:

nano /etc/pandora/pandora_server.conf 

Change dbuser to root, dbpass to pandora (default) and dbhost to node #1 ip. Start all nodes from 1 to 3, bootstraping first, then restart pandora_server with command:

service pandora_server restart  

4 Setting up HAProxy

HAProxy is a free, very fast and reliable solution offering high availability, load balancing, and proxying for TCP and HTTP-based applications. High availability is required to keep the cluster running even if the bootstrapping node fails. Load balancing makes sure that workloads are distributed equally.

The picture above shows how HAProxy communicates with the nodes

Prerequisites:

At least 2 nodes with:

• SELinux's enforcing “permissive”

To do that you need to modify your SELinux config with

nano /etc/selinux/config 

and change SELINUX=disabled to SELINUX=permissive and reboot

• iptables disabled

To disable iptables, use command

service iptables stop

• ntpd ON

To enable ntpd, use command

service ntpd start

4.1 Install the HAProxy software

Execute this command on all individual nodes to install HAProxy software:

yum -y install haproxy

4.2 Configure HAProxy

In this part we will configure the HAproxy configuration files to work with the nodes. Simply follow these steps:

Make a backup of the configuration by executing this command

mv /etc/haproxy/haproxy.cfg /etc/haproxy/haproxy.cfg.orig

Now make a new configuration file

nano /etc/haproxy/haproxy.cfg

Paste the lines below and set your own parameters (hostname and IP’s of Node1, Node2 and Node3 )

global
 log 127.0.0.1   local0
 log 127.0.0.1   local1 notice
 maxconn 4096
 uid 188
 gid 188
 daemon
 #debug
 #quiet

defaults
 log     global
 mode    http
 option  tcplog
 option  dontlognull
 retries 3
 option redispatch
 maxconn 2000
 contimeout      5000
 clitimeout      50000
 srvtimeout      50000

listen mysql-cluster 0.0.0.0:33306
 mode    tcp
 balance roundrobin
 option  httpchk

 server percona1 192.168.70.71:3306 check port 9200 inter 12000 rise 3 fall 3
 server percona2 192.168.70.72:3306 check port 9200 inter 12000 rise 3 fall 3
 server percona3 192.168.70.73:3306 check port 9200 inter 12000 rise 3 fall 3

listen mysql-cluster-failover :43306
 mode    tcp
 balance leastconn
 option  httpchk
 server  percona1 192.168.70.71:3306 check port 9200
 server  percona2 192.168.70.72:3306 check port 9200 backup
 server  percona3 192.168.70.73:3306 check port 9200 backup

With this configuration HAProxy will load balance between three nodes.

Save and exit the configuration file

Execute these two commands to start server

service haproxy start        

and

chkconfig haproxy on

5 Make the two HAProxy nodes redundant with Keepalived

5.1 Installing the latest version of Keepalived

The latest version of keepalived will be installed on your system with the use of this command:

yum install -y keepalived

5.2 Configuring Keepalived

On both two nodes create the configuration file by following these steps:

Rename original configuration file with a command (for backup):

mv /etc/keepalived/keepalived.conf /etc/keepalived/keepalived.conf.backup

Create a new file with a command:

nano /etc/keepalived/keepalived.conf

Insert this configuration to created file, replace the Virtual IP address, the email address, the SMTP's IP address and set the OTHER nodes IP address with your values:

global_defs {
notification_email {
[email protected]
}
notification_email_from [email protected]
smtp_server 192.168.70.1
smtp_connect_timeout 30
router_id LVS_DEVEL
}
vrrp_script chk_haproxy {
script "killall -0 haproxy"
interval 1
}
vrrp_instance VI_1 {
interface eth0
state MASTER
 smtp_alert
 virtual_router_id 51
 priority 101 # on the nodes considered slaves, change 101 -> 100
 unicast_peer {
    192.168.70.72 # this is the other node's IP address
 }
 advert_int 1
   authentication {
      auth_type PASS
        auth_pass 1111
 }
# check every second
# add 2 points of prio if OK
# 101 on master, 100 on slaves
 virtual_ipaddress {
    192.168.70.54
 } 
}
track_script {
  chk_haproxy
}

In order to be able to bind on a IP which is not yet defined on the system, we need to enable non local binding at the kernel level:

Open sysctl.conf with command:

nano /etc/sysctl.conf

Add a line to the bottom:

net.ipv4.ip_nonlocal_bind = 1

To enable it, use this command:

sysctl -p

Now start keepalive using these commands:

service keepalived start

and

chkconfig keepalived on

You can use this command to check which node the Virtual IP is associated with:

ip addr sh eth0

If done correctly the output should look similar to this:

2: eth0: <BROADCAST,MULTICAST,UP,LOWER_UP> mtu 1500 qdisc pfifo_fast state UP qlen 1000
   link/ether 44:37:e6:dd:fa:27 brd ff:ff:ff:ff:ff:ff
   inet 192.168.70.71/24 brd 192.168.70.255 scope global eth0
   inet 192.168.70.54/32 scope global eth0
   inet6 fe80::4637:e6ff:fedd:fa27/64 scope link
      valid_lft forever preferred_lft forever

Now you should have an architecture like in the picture below:

6 Avoid a problem of 'too many open files'

To avoid this error, open the limits configuration file

nano /etc/security/limits.conf

Add these two lines below at the end then save and exit

* soft nofile 60000
* hard nofile 60000

Open sysctl.conf

nano /etc/sysctl.conf

then add the following line:

fs.file-max = 900000

After that enter command to enable it:

sysctl -p

7 Optimization

This part is optional, but recommended. For optimal cluster work you should optimize your configuration files, we added these lines to our /etc/my.cnf file:

innodb_io_capacity = 100
innodb_flush_log_at_trx_commit = 0
innodb_flush_method = O_DIRECT
key_buffer = 300M
innodb_buffer_pool_size  = 600M
innodb_log_buffer_size =32M
max_allowed_packet = 64M
bind-address            = 0.0.0.0
innodb_lock_wait_timeout = 30
query_cache_size = 128M
query_cache_limit = 2M
join_buffer_size = 16M
log_warnings
back_log=100
max_connections=500
innodb_file_per_table

The result were as follows:

Prior to optimization:

 -> Current rate: 0.82 modules/sec
 -> Current rate: 0.69 modules/sec
 -> Current rate: 0.60 modules/sec
 -> Current rate: 0.80 modules/sec
 -> Current rate: 0.81 modules/sec
 -> Current rate: 0.56 modules/sec

After optimization:

 -> Current rate: 62.44 modules/sec
 -> Current rate: 61.49 modules/sec
 -> Current rate: 74.81 modules/sec
 -> Current rate: 73.90 modules/sec
 -> Current rate: 73.22 modules/sec
 -> Current rate: 77.00 modules/sec
 -> Current rate: 72.77 modules/sec
 -> Current rate: 77.10 modules/sec
 -> Current rate: 72.44 modules/sec
 -> Current rate: 69.99 modules/sec
 -> Current rate: 70.81 modules/sec

Using this configuration should improve performance noticeably although considering you might be running on different hardware, improvements might not be as big as ours. Tinkering with these setting might result in even higher performance gains.