Active/Passive Database Replication using built-in SecureCircle Database

The SecureCircle Server can be deployed with a built-in MySQL-based DB. MySQL DB replication can be used to quickly implement an Active/Passive setup. The following MySQL articles are used in this article.

• https://dev.mysql.com/doc/refman/5.7/en/replication-howto-masterbaseconfig.html
• https://dev.mysql.com/doc/refman/5.7/en/replication-setup-slaves.html

Steps

On Master (Active SecureCircle Server)

Note: requires server recycle/downtime

docker-compose.yml

The "command" for the securecircle-db service should show the following:

command: --character-set-server=utf8 --collation-server=utf8_unicode_ci ${DB_EXTRA_OPTIONS}

Add a "ports:" entry to the securecircle-db service as follows:

ports:
- 3306:3306

Full securecircle-db service parameters in docker-compose.yml

securecircle-db:
restart: always
image: securecircle-db:2.7.1
command: --character-set-server=utf8 --collation-server=utf8_unicode_ci ${DB_EXTRA_OPTIONS}
environment:
MYSQL_ROOT_PASSWORD: ${DB_PASSWORD}
volumes:
- securecircle-db:/var/lib/mysql
networks:
securecircle:
aliases:
- db
expose:
- '3306'
ports:
- 3306:3306

.env

The "DB_EXTRA_OPTIONS" variable should be set as follows:

DB_EXTRA_OPTIONS=--log-bin=mysql-bin --server-id=1 --binlog-ignore-db=Logging

Execute the following:

docker-compose down && docker-compose up -d
On Slave (Passive SecureCircle Server)
Copy docker-compose.yml and .env from master (or clone master VM) and initialize SecureCircle Server as normal.

Execute the following to stop the SecureCircle Server:

docker-compose stop

Create a new directory outside of the current working directory in which the docker-compose.yml file exists. For example, if docker-compose.yml is in a directory named /opt/securecircle/server, create a directory named /opt/secuirecircle/db-replica.

Create a file named docker-compose.yml in the new directory with the following contents:

version: '3'
volumes:
securecircle-db: null
services:
securecircle-db-replica:
restart: always
image: dev.securecircle.io:5043/securecircle-db:2.7.1
command: --character-set-server=utf8 --collation-server=utf8_unicode_ci ${DB_EXTRA_OPTIONS}
environment:
MYSQL_ROOT_PASSWORD: ${DB_PASSWORD}
volumes:
- securecircle-db:/var/lib/mysql
expose:
- '3306'

Copy the file named .env from the original SecureCircle Server directory to the new directory. Replace the DB_EXTRA_OPTIONS variable value with the following:

DB_EXTRA_OPTIONS=--server-id=2 --relay-log=replica2-bin-log --replicate-ignore-db=Logging

Remove the initialized DB volume (it will be re-created in the next step) using the following command, replacing "SERVER" with the name of the final path element noted from before.

docker volume rm SERVER_securecircle-db

From within the new directory, execute the following, replacing "SERVER" with the name of the final path element noted from before.

docker-compose -p SERVER up -d

From within the new directory, execute the following commands to configure replication, replacing "SERVER" with the name of the final path element noted from before, replacing "USERNAME" with the DB username as configured in the .env file, replacing "PASSWORD" with the DB password as configured in the .env file, replacing "IP_OF_MASTER" with the IP/hostname of the master (Active) SecureCircle Server, replacing "MASTER_USERNAME" with the master (Active) SecureCircle Server DB password, and replacing "MASTER_PASSWORD" with the master (Active) SecureCircle Server DB password:

docker-compose -p SERVER exec securecircle-db mysql -uUSERNAME -pPASSWORD -e 'CHANGE MASTER TO MASTER_HOST="IP_OF_MASTER",MASTER_USER="MASTER_USERNAME",MASTER_PASSWORD="MASTER_PASSWORD"'

Finally, from within the new directoy, execute the following command to start the replication, replacing "SERVER" with the name of the final path element noted from before, replacing "USERNAME" with the DB username as configured in the .env file, and replacing "PASSWORD" with the DB password as configured in the .env file:

docker-compose -p SERVER exec securecircle-db mysql -uUSERNAME -pPASSWORD -e 'START SLAVE'

The following command can be used to check on the status of the replication. Replace "SERVER" with the name of the final path element noted from before, replace "USERNAME" with the DB username as configured in the .env file, and replace "PASSWORD" with the DB password as configured in the .env file:

docker-compose -p SERVER exec securecircle-db mysql -uUSERNAME -pPASSWORD -e 'SHOW SLAVE STATUS \G'