====== Database High Availability on CentOS 7 ======
{{indexmenu_n>20}}
We are working on the translation of the Pandora FMS documentation. Sorry for any inconvenience.
CentOS 7 will soon reach its End of Life (**EOL**). This documentation is preserved for historical purposes.
===== High Availability in the Database =====
The objective of this section is to offer a complete solution for HA in Pandora FMS environments. This is the only HA model with official support for Pandora FMS. This solution is provided -pre-installed- **from OUM 724 to 769**. This system replaces DRBD and other HA systems recommended in the past (724 and earlier).
See "[[:en:documentation:pandorafms:complex_environments_and_optimization:06_ha#migration_of_ha_corosync-pacemaker_environments|Migration of HA Corosync-Pacemaker environments]]" to update the high availability database used in version 770.
This is the first implementation of Pandora FMS DB HA and the installation process is almost entirely manual, using the GNU/Linux console as **root**. In future versions we will provide [[:es:documentation:pandorafms:complex_environments_and_optimization:06_ha#configuracion_de_la_consola_pandora_fms|configuration from the graphical interface (GUI)]].
Pandora FMS is based on a MySQL database to configure and store data. A database failure can momentarily paralyze the monitoring tool. The Pandora FMS high availability database cluster allows you to easily deploy a robust and fault tolerant architecture.
This is an advanced feature that requires knowledge of GNU/Linux systems.
Cluster resources are managed with [[https://github.com/ClusterLabs/pacemaker|Pacemaker]], an advanced and scalable high availability cluster resource manager. [[http://corosync.github.io/corosync/|Corosync]] provides a closed process group communication model for creating replicated state machines. [[https://www.percona.com/|Percona]] was chosen as the default RDBMS for its scalability, availability, security, and backup features.
The active/passive [[https://dev.mysql.com/doc/refman/5.7/en/replication.html|replication]] is developed from a single master node (with writable permission) to any number of children ( read only). A virtual IP address always points to the current master. If the master node fails, one of the secondary nodes is promoted to master and the virtual IP address is updated accordingly.
The Pandora FMS HA database tool, ''pandora_ha'', monitors the cluster and makes sure that the Pandora FMS server is in continuous operation, restarting it if necessary. ''pandora_ha'' is in turn monitored by **systemd**.
It is recommended to keep a maximum of 15 days of data and events, to save for a longer time a [[:es:documentation:pandorafms:complex_environments_and_optimization:09_pandorafms_engineering#base_de_datos_historica|historical database]] must be configured. Also consult the topic "[[:en:documentation:pandorafms:installation:06_server_management|Management and administration of PFMS servers]]".
This example will set up a two-node cluster, with hosts node1 and node2. Hostnames, passwords, etc. will be changed. according to what you need to match the environment to be implemented.
Version 759 or earlier.
Commands that need to be executed on a node will be preceded by the hostname of that node:
node1#
Commands that need to be executed on all nodes will be preceded by the word **all**:
all#
There is an additional host, called **pandorafms**, where Pandora FMS is or will be installed.
When referring to **all** only refers to the Database nodes, the additional Pandora FMS node will always be referenced as **pandorafms** and is not part of **all**.
===== Prerequisites =====
CentOS 7 will soon reach its End of Life (**EOL**). This documentation is preserved for historical purposes.
CentOS version 7 must be installed on all hosts, and they must be able to resolve each other's hostnames.
node1# ping node2
PING node2 (192.168.0.2) 56(84) bytes of data.
node2# ping node1
PING node1 (192.168.0.1) 56(84) bytes of data.
pandorafms# ping node1
PING node1 (192.168.0.1) 56(84) bytes of data.
pandorafms# ping node2
PING node2 (192.168.0.2) 56(84) bytes of data.
An Open SSH server must be installed and running on each host. We **suppressed** the notice that Open SSH shows:
all# [ -f /etc/cron.hourly/motd_rebuild ] && rm -f /etc/cron.hourly/motd_rebuild
all# sed -i -e 's/^Banner.*g' /etc/ssh/sshd_config
all# systemctl restart sshd
The Pandora FMS HA database tool will not work correctly if Open SSH has a warning configured.
We generate new SSH authentication keys for each host and copy the public key for each of the hosts:
Keys can be generated for a non-**root** user for a later non-root cluster installation.
node1# echo -e "\n\n\n" | ssh-keygen -t rsa
node1# ssh-copy-id -p22 root@node2
node1# ssh node2
node2# echo -e "\n\n\n" | ssh-keygen -t rsa
node2# ssh-copy-id -p22 root@node1
node2# ssh node1
pandorafms# echo -e "\n\n\n" | ssh-keygen -t rsa
pandorafms# ssh-copy-id -p22 root@node1
pandorafms# ssh-copy-id -p22 root@node2
pandorafms# ssh node1
pandorafms# ssh node2
In the Pandora FMS node, we copy the key pair to ''/usr/share/httpd/.ssh/''. The Pandora FMS console needs to retrieve the cluster status:
pandorafms# cp -r /root/.ssh/ /usr/share/httpd/
pandorafms# chown -R apache:apache /usr/share/httpd/.ssh/
The following steps are required only if the nodes are running SSH on a non-standard port. You must replace 22 with the correct port number:
all# echo -e "Host node1\n Port 22">> /root/.ssh/config
all# echo -e "Host node2\n Port 22">> /root/.ssh/config
===== Installation of Percona =====
Install the necessary package:
all# yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
all# yum install -y Percona-Server-server-57 percona-xtrabackup-24
For more information regarding the Percona installation process, you can consult the official product documentation:
[[https://www.percona.com/doc/percona-server/5.7/installation/yum_repo.html|https://www.percona.com/doc/percona-server/5.7/installation/yum_repo.html]]
Once the packages are installed, make sure that the Percona service is disabled, as it will be managed by the cluster:
all# systemctl disable mysqld
If the system service is not disabled, the cluster resource manager will not work correctly.
Next, start the Percona server:
all# systemctl start mysqld
A new temporary password connected to ''/var/log/mysqld.log'' will be generated. Connect to the Percona server and change the **root** password:
all# mysql -uroot -p$(grep "temporary password" /var/log/mysqld.log | \
rev | cut -d' ' -f1 | rev)
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('Pandor4!');
mysql> UNINSTALL PLUGIN validate_password;
mysql> SET PASSWORD FOR 'root'@'localhost' = PASSWORD('pandora');
mysql> quit
Reinstall the server package with the **ha** option ( flag ):
pandorafms# ./pandora_server_installer --install --ha
Once the server is installed with the HA tools enabled, you will find the configuration generator for database replication in the path: ''/usr/share/pandora_server/util/myconfig_ha_gen.sh''
Example: ./myconfig_ha_gen.sh -i serverid [-l file_location] [-d database] [-b binlog] [-u dbuser] [-p dbpass] [-s poolsize] [-h help]
Mandatory parameters:
-i --serverid Set the server id for the database (Mandatory)
Optional parameters:
-l --location Set my.cnf custom location including filename. [ default value: /etc/my.cnf ] (optional)
-d --database Set the database to be replicated. [ default value: pandora ] (optional)
-b --binlog Set binlog file. [ default value: mysql-bin ] (optional)
-u --dbuser Set dbuser for mysql connection and backups. [ default value: root ] (optional)
-p --dbpass Set dbpassword for mysql connection and backups. [ default value: pandora ] (optional)
-s --poolsize Set innodb_buffer_pool_size static size in M (Megabytes) or G (Gigabytes). [ default value: autocalculated ] (optional)
-h --help Print help.
In the current case where the databases are not on the same server as the application, it will be necessary to copy the script to the nodes to be executed locally.
pandorafms# scp /usr/share/pandora_server/util/myconfig_ha_gen.sh root@node1:/root/
pandorafms# scp /usr/share/pandora_server/util/myconfig_ha_gen.sh root@node2:/root/
It will only be necessary to pass the **serverid** parameter (mandatory) in standard environments and some optional parameters for custom environments.
If the default or defined user does not connect to the database, the script will end with a connection error.
There is also the possibility of passing the database, the user and the password as arguments. If you don'tThe default ones will be used.
In this case, the script will be executed on both nodes only by passing the server id if we have the default credentials, otherwise define the necessary parameters.
node1# /root/myconfig_ha_gen.sh -i 1
node2# /root/myconfig_ha_gen.sh -i 2
Important: Each node must have a unique identifier
The Percona configuration file will be written to ''/etc/my.cnf'' where the server identifier and the recommended configuration for database replication will be defined.
You must restart the **mysqld** service to verify that the configuration has been applied correctly.
all# systemctl restart mysqld
===== Installation of Pandora FMS =====
==== New installation of Pandora FMS ====
[[:en:documentation:pandorafms:installation:01_installing|Install Pandora FMS]] in the newly created database. Stop the Pandora FMS server:
pandorafms# /etc/init.d/pandora_server stop
As of version NG 754, it has [[:en:documentation:pandorafms:installation:06_server_management|additional options in manual startup and shutdown]] of High Availability Environments (HA).
==== Existing Pandora FMS installation ====
Stop the Pandora FMS server:
pandorafms# /etc/init.d/pandora_server stop
Make a backup of the Pandora FMS database:
pandorafms# mysqldump -uroot -ppandora --databases pandora> /tmp/pandoradb.sql
pandorafms# scp /tmp/pandoradb.sql node1:/tmp/
Now load the information to the new database:
node1# mysql -uroot -ppandora pandora -e source "/tmp/pandoradb.sql"
===== Replication Settings =====
Grant the necessary privileges for replication to work on all databases:
all# mysql -uroot -ppandora
mysql> GRANT ALL ON pandora.* TO 'root'@'%' IDENTIFIED BY 'pandora';
mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE ON *.*
TO 'root'@'%' IDENTIFIED BY 'pandora';
mysql> GRANT REPLICATION CLIENT, REPLICATION SLAVE, SUPER, PROCESS, RELOAD ON *.*
TO 'root'@'localhost' IDENTIFIED BY 'pandora';
mysql> GRANT select ON mysql.user TO 'root'@'%' IDENTIFIED BY 'pandora';
mysql> FLUSH PRIVILEGES;
mysql> quit
Back up the database on the first node and write down the name and position of the master log file (in the example,
''mysql-bin.000001'' and ''785''):
node1# [ -e /root/pandoradb.bak ] && rm -rf /root/pandoradb.bak
node1# innobackupex --no-timestamp /root/pandoradb.bak/
node1# innobackupex --apply-log /root/pandoradb.bak/
node1# cat /root/pandoradb.bak/xtrabackup_binlog_info
mysql-bin.000001 785
Load the database on the second node and configure to replicate from the first node (you must set ''MASTER_LOG_FILE'' and ''MASTER_LOG_POS'' to the values from the previous step):
node2# systemctl stop mysqld
node1# rsync -avpP -e ssh /root/pandoradb.bak/ node2:/var/lib/mysql/
node2# chown -R mysql:mysql /var/lib/mysql
node2# chcon -R system_u:object_r:mysqld_db_t:s0 /var/lib/mysql
node2# systemctl start mysqld
node2# mysql -uroot -ppandora
mysql> CHANGE MASTER TO MASTER_HOST='node1',
MASTER_USER='root', MASTER_PASSWORD='pandora',
MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=785;
mysql> START SLAVE;
mysql> SHOW SLAVE STATUS \G
**************************** 1.row ******************** *******
Slave_IO_State: Waiting for master to send event
Master_Host: node1
Master_User: root
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000002
Read_Master_Log_Pos: 785
Relay_Log_File: node2-relay-bin.000003
Relay_Log_Pos: 998
Relay_Master_Log_File: mysql-bin.000002
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB: pandora
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 785
Relay_Log_Space: 1252
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_UUID: 580d8bb0-6991-11e8-9a22-16efadb2f150
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
mysql> QUIT
You must ensure that ''Slave_IO_Running'' and ''Slave_SQL_Running'' return ''Yes''. Other values may be different from the example.
It is recommended **not** to use the **root** user to carry out this process. It is advisable to give permissions to another user in charge of managing the database to avoid possible conflicts.
===== Two Node Cluster Configuration =====
Install the necessary packages:
all# yum install -y epel-release corosync ntp pacemaker pcs
all# systemctl enable ntpd
all# systemctl enable corosync
all# systemctl enable pcsd
all# cp /etc/corosync/corosync.conf.example /etc/corosync/corosync.conf
all# systemctl start ntpd
all# systemctl start corosync
all# systemctl start pcsd
Stop the Percona server:
node1# systemctl stop mysqld
node2# systemctl stop mysqld
==== Authentication of all nodes in the cluster ====
Create and start the cluster:
all# echo hapass | passwd hacluster --stdin
node1# pcs cluster auth -u hacluster -p hapass --force node1 node2
node1# pcs cluster setup --force --name pandoraha node1 node2
node1# pcs cluster start --all
node1# pcs cluster enable --all
node1# pcs property set stonith-enabled=false
node1# pcs property set no-quorum-policy=ignore
Check the status of the cluster:
node#1 pcs status
Cluster name: pandoraha
Stack: corosync
Current DC: node1 (version 1.1.18-11.el7_5.2-2b07d5c5a9) - partition with quorum
Last updated: Fri Jun 8 12:53:49 2018
Last change: Fri Jun 8 12:53:47 2018 by root via cibadmin on node1
2 nodes configured
0 resources configured
Online: [ node1 node2 ]
No resources
Daemon Status:
corosync: active/disabled
pacemaker: active/disabled
pcsd: active/enabled
Both nodes should be online
(''Online: [ node1 node2 ] '').
Other values may be different from the example.
==== Installing the Percona Pacemaker Replication Agent ====
It can be downloaded manually from [[https://pandorafms.com/library/pacemaker-replication-agent-for-mysql/|PFMS library]].
all# cd /usr/lib/ocf/resource.d/
all# mkdir percon
all# cd percona
all# curl -L -o pacemaker_mysql_replication.zip https://pandorafms.com/library/wp-content/uploads/2019/12/pacemaker_mysql_replication.zip
all# unzip pacemaker_mysql_replication.zip
all# rm -f pacemaker_mysql_replication.zip
all# chmod u+x mysql
Configure the cluster resources. Replace **< VIRT_IP >** with your preferred virtual IP address:
If the default password used in this guide for the database user **root** has been changed, you should update ''replication_passwd'' and ''test_passwd'' respectively.
The names of the cluster resources must be exactly those indicated in this guide ( ''pandoraip'' and ''pandoradb'')
node1# export VIP=''
node1# pcs resource create pandoradb ocf:percona:mysql config="/etc/my.cnf" \
pid="/var/run/mysqld/mysqld.pid" socket="/var/lib/mysql/mysql.sock" \
replication_user="root" replication_passwd="pandora" max_slave_lag="60" \
evict_outdated_slaves="false" binary="/usr/sbin/mysqld" datadir="/var/lib/mysql" \
test_user="root" test_passwd="pandora" op start interval="0" timeout="60s" \
op stop interval="0" timeout="60s" op promote timeout="120" op demote timeout="120" \
op monitor role="Master" timeout="30" interval="5" on-fail="restart" op monitor role="Slave" \
timeout="30" interval="10"
node1# pcs resource create pandoraip ocf:heartbeat:IPaddr2 ip=$VIP cidr_netmask=24 \
op monitor interval=20s
node1# pcs resource master master_pandoradb pandoradb meta master-max="1" \
master-node-max="1" clone-max="2" clone-node-max="1" notify="true" \
globally-unique="false" target-role="Master" is-managed="true"
noode1# pcs constraint colocation add master master_pandoradb with pandoraip
node1# pcs constraint order promote master_pandoradb then start pandoraip
Check the status of the cluster:
node1# pcs status
Cluster name: pandoraha
Stack: corosync
Current DC: node1 (version 1.1.18-11.el7_5.2-2b07d5c5a9) - partition with quorum
Last updated: Fri Jun 8 13:02:21 2018
Last change: Fri Jun 8 13:02:11 2018 by root via cibadmin on node1
2 nodes configured
3 resources configured
Online: [ node1 node2 ]
Full list of resources:
Master/Slave Set: master_pandoradb [pandoradb]
Masters: [ node1 ]
Slave: [ node2 ]
pandoraip (ocf::heartbeat:IPaddr2): Started node1
Daemon Status:
corosync: active/disabled
pacemaker: active/disabled
pcsd: active/enabled
Both nodes should be online
(''Online: [ node1 node2 ] '').
Other values may be different from the example.
===== Configuring the two-node cluster with "non-root" user =====
It will be done [[:en:documentation:pandorafms:complex_environments_and_optimization:06_ha#configuracion_del_cluster_de_dos_nodos|similar to the previous way]]. The user credentials, previously explained, must have been copied, and the following steps must be carried out:
# All nodes:
useradd
passwd
usermod -a -G haclient
# Enable PCS ACL system
pcs property set enable-acl=true --force
# create role
pcs acl role create description="RW role" write xpath /cib
# Create PCS user - Local user
pcs acl user create
# Login into PCS from ALL nodes
su -
pcs status
Username:
Password: *****
# Wait for 'Authorized' message, ignore output. Wait a second and retry 'pcs status' command
[[:en:documentation:start|Back to Pandora FMS Documentation Index]]