KB Article #180417

Database cluster setup for API Portal in HA deploy

This is a best practice guide for API Portal High Availability (HA) setup with clustered database. For database we are going to use and configure Galera Cluster with three nodes. It is recommended to have at least three nodes to prevent split-brain conditions. We are going to have three Galera MySQL nodes – db1, db2, db3 (IP address, host name). According to Galera requirements and recommendations for server hardware, each node needs:

  • 2GHz single core CPU
  • 100 Mbps network connectivity
  • 2048MB RAM

For software, each node in the cluster requires:

  • Linux or FreeBSD;
  • MySQL or MariaDB server with wsrep API patch;
  • Galera Replication Plugin.

For host OS we are going to use CentOS 7.
Galera Cluster documentation: http://galeracluster.com/

Problem

Configuring clustered database for API Portal HA setup.

Resolution

Install and configure Galera

  1. Install Galera for MySQL on each node
    yum install galera-3 mysql-wsrep-5.7
    You have to add the Galera repo to be able to install the packages.
    Note! There is detailed documentation how to install Galera Cluster for MySQL, there are two main options – to use Galera repo and install the packages through the OS package manager or to download and install them manually. Follow the steps in the Galera doc.
  2. The package manager will install some necessary dependencies like mysql-wsrep-client/server/common-5.7
  3. Install rsync
    yum install rsync
  4. Enable mysqld
    systemctl enable mysqld
  5. Configure root password for mysql - run mysql_secure_installation
  6. Ports 3306, 4567, 4567, 4444 should be opened for all other nodes and the Load Balancer.
    Check Galera doc on how to deal with firewall, SeLinux and iptables
  7. After the installation make initial node configuration as described in the official doc of Galera Cluster – edit the /etc/my.cnf file with configuration like this.
    Example:
    [mysqld]
    datadir=/var/lib/mysql
    socket=/var/lib/mysql/mysql.sock
    binlog_format=ROW
    bind-address=0.0.0.0
    default_storage_engine=innodb
    innodb_autoinc_lock_mode=2
    innodb_flush_log_at_trx_commit=0
    innodb_buffer_pool_size=122M
    wsrep_provider=/usr/lib64/galera-3/libgalera_smm.so
    wsrep_provider_options="gcache.size=500M; gcache.page_size=500M; gmcast.segment=1; gcs.max_packet_size=1048576; evs.send_window=512; evs.user_send_window=256; gcs.fc_limit=128;" wsrep_cluster_name="apiportal"
    wsrep_cluster_address="gcomm://db1,db2,db3"
    wsrep_sst_method=rsync wsrep_node_name="DB1"
    wsrep_node_address="db1"
    binlog_row_event_max_size=2M
    binlog_cache_size=2M
    symbolic-links=0
    log-error=/var/log/mysqld.log
    pid-file=/var/run/mysqld/mysqld.pid
  8. Configure my.cnf on all Galera nodes – wsrep_node_address is the IP address/host of the node on which we are configuring the file, wsrep_node_name should be unique for all nodes. Set gmcast.segment to integer value (e.x. 1), same value for all nodes, wsrep_cluster_name has to be the same in all nodes to have a successful connection. The wsrep_provider location may differ from the path in the example.
    Note: the gmcast.segment option should be a different value when configuring a multi datacenter cluster, then in the one datacenter you should use one value (for example 1) and for the other datacenter another value (like 2), different value per datacenter.
  9. Configure some settings at the TCP level for better performance (with the command sysctl –w)
    net.core.rmem_max=16777216
    net.core.wmem_max=16777216
    net.core.rmem_default=16777216
    net.core.wmem_default=16777216
    net.ipv4.tcp_rmem=”4096 87380 16777216”
    net.ipv4.tcp_wmem=”4096 65536 16777216”
    net.ipv4.tcp_slow_start_after_idle=0
  10. Starting the first cluster node
    By default, nodes do not start as part of the Primary Component. Instead, they assume that the Primary Component exists already somewhere in the cluster. When nodes start, they attempt to establish network connectivity with the other nodes in the cluster. For each node they find, they check whether or not it is a part of the Primary Component. When they find the Primary Component, they request a state transfer to bring the local database into sync with the cluster. There is no Primary Component when the cluster starts. In order to initialize it, you need to explicitly tell one node to do so with the --wsrep-new-cluster argument. By convention, the node you use to initialize the Primary Component is called the first node, given that it is the first that becomes operational.

    Bear in mind, the first node is only “first” in that it initializes the Primary Component. This node can fall behind and leave the cluster without necessarily affecting the Primary Component!

    For CentOS 7 and Galera Cluster 5.7 the command for starting the first node is:
    /usr/bin/mysqld_bootstrap
    Check Galera doc for the starting command for other Galera Cluster versions and OS distributions.

    Note: mysqld_bootstrap or an equivalent command when initializing the Primary Component. Do not use it when you want the node to connect to an existing cluster!

    After successful start of the cluster access MySQL to verify the cluster is up:
    SHOW STATUS LIKE 'wsrep_cluster_size';
    +--------------------+-------+
    | Variable_name | Value |
    +--------------------+-------+
    | wsrep_cluster_size | 1 |
    +--------------------+-------+
  11. Add nodes to the cluster
    When you start the first node you initialize a new cluster. Once this is done, the procedure for adding all the other nodes is the same. To add a node to an existing cluster, launch mysqld as you would normally. On db2 execute:
    systemctl start mysqld

    When the database server initializes as a new node, it connects to the cluster members as defined by the wsrep_cluster_address parameter. Using this parameter, it automatically retrieves the cluster map and connects to all other available nodes.

    We can check the size of the cluster to verify:
    SHOW STATUS LIKE 'wsrep_cluster_size';
    +--------------------+-------+
    | Variable_name | Value |
    +--------------------+-------+
    | wsrep_cluster_size | 2 |
    +--------------------+-------+

    Execute (start as normal MySQL) on all other nodes to add them to the cluster. If there any errors they will be in the MySQL log file.
  12. Galera Arbitrator
    The recommended deployment of Galera Cluster is that you use a minimum of three instances. Three nodes, three datacenters and so on. In the event that the expense of adding resources, such as a third datacenter, is too costly, you can use Galera Arbitrator.
    Galera Arbitrator is a member of the cluster that participates in voting, but not in the actual replication.
    Note: Warning: While Galera Arbitrator does not participate in replication, it does receive the same data as all other nodes. You must secure its network connection.
    If one datacenter fails or loses WAN connection, the node that sees the arbitrator, and by extension sees clients, continues operation.
  13. Install Galera Arbitrator
    For CentOS 7 by installing Galera Cluster (see step 1) you have installed Galera arbitrator too.
    There are several ways of starting the arbitrator (check the doc if you want to explore them).
  14. Starting Galera Arbitrator as a Service
    On another machine running CentOS 7 (ABR) we have installed Galera Cluster packages (check step 1). ABR should be visible to all other nodes and all nodes have to see ABR (open ports)
    To configuring the arbitrator you need to edit /etc/sysconfig/garb file (on CentOS).
    Example:
    # A space-separated list of node addresses (address[:port]) in the cluster
    GALERA_NODES="db1:4567 db2:4567 db3:4567"
    # Galera cluster name, should be the same as on the rest of the nodes.
    GALERA_GROUP=”apiportal”
    # Log file for garbd. Optional, by default logs to syslog
    LOG_FILE=”/var/log/garbd.log”



    Note: You must create the log file manually and go give it write permissions before starting the arbitrator.
    Note: Delete all the lines starting with # (comment lines)

    Once you have the service configuration file with the right settings, you can start the garb service:
    systemctl start garb
    This starts Galera Arbitrator as a service. It uses the parameters set in the configuration file. Verify the starting with:
    systemctl status garb.
  15. Galera Cluster Load Balancer – LB_DB_Cluster
    The load balancer between API Portal instances and Galera Cluster could be typical load balancer like HAProxy, Nginx and other. Also there is custom solution from Galera – Galera Load Balancer. Check the Galera documentation for more details.
  16. After installing and configuring Galera Cluster and LB_DB_Cluster when installing API Portal instance use LB_DB_Cluster as an entry point for the database.


Install API Portal

  1. Start the installation on the first API Portal node
  2. For database settings enter the host address of the load balancer in front of Galera Cluster.
  3. All the nodes should have network connectivity to any other nodes (db and API Portal nodes).
  4. During the installation you will be asked if this is going to be HA setup with DB replication/cluster. Answer Y.
  5. Follow API Portal post install steps (installing easy blog/discuss). After changing the Joomla admin password on the first node – it will be applicable in all nodes / you will use the same password.
  6. When the installation on the first node is done go on the next one – do the following steps for each API Portal instance you are about to install.
  7. Use the LB_DB_Cluster for the DB details on install for each API Portal node.
  8. When API Portal installation starts it will check the connection do the database (in our case through the Load Balancer) and if the connection is successful for any other node except for the first one it will prompt you that there is already a deployed DB schema on the DB server, you will have to answer with Y/N if this is a API Portal HA setup. In our case it is so answer with Y.
  9. The installation will continue as normal. Follow the post install steps for each node (easy blog and discuss installation).