How do I replicate additional non-Bright databases?

This article is being updated. Please be aware the content herein, not limited to version numbers and slight syntax changes, may not match the output from the most recent versions of Bright. This notation will be removed when the content has been updated.

Sometimes the cluster administrator may arrange for more databases to be configured for a non-Bright application. These are independent of Bright control, but just happen to use MySQL running on the head node, like Bright does.

The administrator would typically like to have database replication for these additional databases too.

Automatic replication of additional databases can be carried out in Bright 7.0 and newer versions as follows:

1. Copy /cm/local/apps/cluster-tools/ha/conf/extradbclone.xml.template to /cm/local/apps/cluster-tools/ha/conf/extradbclone.xml

# cp /cm/local/apps/cluster-tools/ha/conf/extradbclone.xml.template /cm/local/apps/cluster-tools/ha/conf/extradbclone.xml

2. Edit /cm/local/apps/cluster-tools/ha/conf/extradbclone.xml and add the additional databases as follows:

[...]
<dbclone>
    <db name="testDB" username="test" userpass="Ch@ngeMe" />
    <db name="testDB2" username="test" userpass="Ch@ngeMe" />
</dbclone>
[...]

3. Add the following “/cm/local/apps/cmd/scripts/cm-update-mycnf.sh” script on both head nodes and run it to update my.cnf

#!/bin/bash

OS=`grep PRETTY /etc/os-release | awk -F"=" '{print $2}'`
if [[ $OS =~ .*"RHEL".* ]]; then
    mycnf=/etc/my.cnf
elif [[ $OS =~ .*"Ubuntu".* ]]; then
    mycnf=/etc/mysql/my.cnf
fi

extradbcloneopenstack=/cm/local/apps/cluster-tools/ha/conf/extradbclone.xml
xmllint=/usr/bin/xmllint

# additional openstack dbs for replication
if [[ -e "$extradbcloneopenstack" ]];then
  xmlgood=0
  if [[ -x $xmllint ]];then
    $xmllint $extradbcloneopenstack 1>/dev/null 2>&1
    xmlgood=$?
  fi
  if [[ $xmlgood -eq 0 ]];then
    OLDIFS=$IFS
    IFS=$'\n'
    extradbs=($(sed -e 's/<!--.*-->//g' -e '/<!--/,/-->/{//!d}' $extradbcloneopenstack | awk '/<db name=/'))
    IFS=$OLDIFS
    if [[ -f $mycnf ]];then
      content=`cat $mycnf`
      for db in "${extradbs[@]}"; do
        dbname=$(echo $db |cut -d' ' -f2 | sed 's/name=//' | sed 's/"//g')
        if [[ ! $content =~ "replicate-do-db=$dbname" ]]; then
          perl -pi.bak -e 's/(log-bin=mysql-bin)/$1\nreplicate-do-db='$dbname'/' $mycnf;
        fi
        if [[ ! $content =~ "binlog-do-db=$dbname" ]]; then
          perl -pi.bak -e 's/(log-bin=mysql-bin)/$1\nbinlog-do-db='$dbname'/' $mycnf;
        fi
      done
    fi
  fi
fi

4. Make sure that the extra database(s) and user(s) is created on both head nodes

mysql> create database testDB;
mysql> create database testDB2;
mysql> create user 'test'@'localhost' identified by 'Ch@ngeMe';
mysql> grant all privileges on testDB.* to 'test'@'localhost';
mysql> grant all privileges on testDB2.* to 'test'@'localhost';

5. Restart MySQL service on both head nodes:

  a. for RHEL 7/8 

    # systemctl restart mariadb

  b. for Ubuntu 20/22

    # systemctl restart mysql

6. Run cmha dbreclone <passive> to clone the additional databases

# cmha dbreclone <passive>
===========================================================================
The contents of the MySQL database from this node adel-b92-u2004-11-24-a
will be cloned to the node adel-b92-u2004-11-24-b.
Please confirm, to continue with this operation.
===========================================================================

Continue(c)/Exit(e)? c
Stopping cmdaemon on head nodes.................. [ OK ]
Cloning cmdaemon database........................ [ OK ]
Checking database consistency after clone........ [ OK ]
Cloning workload manager databases............... [ OK ]
Cloning additional databases..................... [ OK ]
Starting mysql replication....................... [ OK ]
Starting cmdaemon on primary head node........... [ OK ]
Starting cmdaemon on secondary head node......... [ OK ]
Enable required services......................... [ OK ]