- 17 Oct 2020
- 2 Minutes to read
-
Print
-
DarkLight
-
PDF
Active/Passive Database Replication using built-in SecureCircle Database
- Updated on 17 Oct 2020
- 2 Minutes to read
-
Print
-
DarkLight
-
PDF
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
The final path element 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, the final path element is "server".
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'