Dual Cacti Database Sync

The problem with redundant Cacti setup is, once there is a change on primary node, you will need to perform the same work on backup node to maintain the system consistency across the two nodes. The objective of this setup is to enable whatever changes made on the primary node will be automatically propagated to the backup node. 

The idea to achieve this is by enabling MySQL database replication on primary node to the backup node.

First, edit the mysql configuration file (etc/my.cnf ) on primary Cacti.

bind-address=x.x.x.x

server-id=1
binlog-do-db=cacti
binlog-format=mixed
log-bin = /var/log/mysql/mysql-bin.log

innodb_flush_log_at_trx_commit=1
sync_binlog=1

 Proceed to restart the mysql process and create a credential used by backup Cacti node to replicate the database.

CREATE USER cactirep@<backup_node_IP>; GRANT REPLICATION SLAVE ON *.* TO cactirep@<backup_node_IP> IDENTIFIED BY '<a_password>';

At this phase, I would proceed to dump the cacti mysql database on Cacti. It is recommended to stop the poller cron job during this process.

mysqldump -u <mysql_user> -p --skip-lock-tables --single-transaction --flush-logs --hex-blob --master-data=2 cacti > ~/cacti_rep.sql

scp ~/cacti_rep.sql <backup_node_IP>:~/

Beside that, examine the head of the file and jot down the values for MASTER_LOG_FILE and MASTER_LOG_POS which will be needed later.

head dump.sql -n80 | grep "MASTER_LOG_POS"

Then, proceed to create a copy of primary Cacti directory and copy over to Cacti backup node to avoid any inconsistency.

cd /var/www/html
tar -zcvf cacti_dir.tar.gz <cacti_folder_name>

scp cacti_dir.tar.gz <backup_node_IP>:/var/www/html/

Now, all the needed files are on backup node. So proceed with the configuration on backup node.

Stop the poller cronjob and edit the mysql configuration file of backup node (/etc/my.cnf).

bind-address=y.y.y.y

server-id=2
binlog-format=mixed
relay-log=/var/log/mysql/mysql-relay-bin.log
log_bin=/var/log/mysql/mysql-bin.log
binlog_do_db=cacti
log-slave-updates = 1

replicate-ignore-table = cacti.poller_output
replicate-ignore-table = cacti.poller_command
replicate-ignore-table = cacti.poller_time
replicate-ignore-table = cacti.poller_reindex
replicate-ignore-table = cacti.user_log
replicate-ignore-table = cacti.poller
replicate-ignore-table = cacti.settings

 
Some of the tables are ignored from replicating as they make the poller misbehave. If you have plugins installed and facing issue after the database syncing, try to exclude the table from replication might solve the problem.


Restart the mysql service and proceed to replicate the Cacti database on backup node.

You might want to drop the existing database before starting the new replication.

drop database cacti;

create database cacti;

mysql -u <mysql_user> -p cacti < ~/cacti_rep.sql

Replace the backup Cacti directory with the primary copy. You might want perform backup first before proceed.

cd /var/www/html/

mv cacti cacti.bak

tar -xzvf cacti_rep.tar.gz

Login to the mysql console and do the following,

CHANGE MASTER TO MASTER_HOST='<primary_cacti_IP>',MASTER_USER='cactirep', MASTER_PASSWORD='<a_password>',  MASTER_LOG_FILE='<<value from above>>', MASTER_LOG_POS=<<value from above>>; 
START SLAVE;

Verify the backup node database status by

SHOW SLAVE STATUS \G

Enable back all the cronjob and you should see your Cacti run as before.