From Swivel Knowledgebase Wiki

Overview

This solution can be used on a PINsafe Active/Active pair where synchronizing the slaves on each has failed to make the databases synchronised. For further information also see MySQL Database How To Guide.

Prerequisites

Recent Backup of PINsafe Appliances (to restore in case of a problem);

PINsafe Active/Active Cluster using the MySQL database;

PINsafe DR node using MySQL database (optional depending on the circumstances);

Command line access to the PINsafe appliances;

Appliance downtime (for all appliances);

Ensure that MySQL communication is permitted, from each appliance, each way, test connectivity from each appliance in turn to the others using the mySQL replication ethernet port (default ETH1, 172.16.0.1, 172.16.0.2)

 telnet <IP Address> 3306

Network Requirements

In order to experience reliable replication across a network you need to ensure that the following conditions are satisfied in a production environment. This is a permanent requirement - not just to satisfy the resynchronisation instructions within this article:

Minimum of 120 k/s network bandwidth (equivalent to a 1 megabit connection);

Network Latency - should return below 500ms consistently.

Verifying Synchronisation

The following article explains how to verify if the databases are in synchronisation: Verifying the Installation

Synchronisation Commands

Primary/Standby Resync

This process allows the synchronisation of a Primary Master to a Standby Master or of a Standby Master to a Primary Master depending upon which set of data is taken as the valid data set.

Where MASTER_IP is the IP address of the PINsafe Primary Master, Example: 192.168.0.36

And SLAVE_IP is the IP address of the PINsafe Standby Master, Example: 192.168.0.37

Enter the following commands in turn on the Primary Master:

mysql -uadmin -plockbox -h SLAVE_IP -e"stop slave;"
mysql -uadmin -plockbox -h MASTER_IP -e"stop slave;" 
mysqldump --single-transaction --flush-logs pinsafe_rep > /tmp/master_dump.sql
mysql -uadmin -plockbox -h SLAVE_IP -D pinsafe_rep < /tmp/master_dump.sql
mysql -uadmin -plockbox -h SLAVE_IP -e"LOAD DATA FROM MASTER;"
mysql -uadmin -plockbox -h MASTER_IP -e"LOAD DATA FROM MASTER;" 
mysql -uadmin -plockbox -h SLAVE_IP -e"start slave;"
mysql -uadmin -plockbox -h MASTER_IP -e"start slave;"

Primary/DR Resync

Note: after changing the IP address of the Primary Master on the DR a restart of MySQL may be required, and this may solve the synchronisation issue without the below solution.

The same procedure may be used for DR nodes, albeit taking into consideration the IP of the DR node instead.

Where MASTER_IP is the IP address of the PINsafe Primary Master, Example: 192.168.0.36

And DR_IP is the IP address of the PINsafe DR (it's possible to have many DRs), Example: 192.168.0.38

Enter the following commands in turn on the Primary Master:

mysql -uadmin -plockbox -h DR_IP -e"stop slave;"
mysql -uadmin -plockbox -h MASTER_IP -e"stop slave;" 
mysqldump --single-transaction --flush-logs pinsafe_rep > /tmp/master_dump.sql
mysql -uadmin -plockbox -h DR_IP -D pinsafe_rep < /tmp/master_dump.sql
mysql -uadmin -plockbox -h DR_IP -e"LOAD DATA FROM MASTER;"
mysql -uadmin -plockbox -h DR_IP -e"start slave;"
mysql -uadmin -plockbox -h MASTER_IP -e"start slave;"

Verification

Ensure that the databases are synchronised by creating a user on each that should be visible on the other server.

Additional Options

Changing the Ethernet Replication Port

By default the PINsafe appliance is set to replicate data across ETH1 with an IP on the primary appliance of 172.16.0.1 and an IP on the standby appliance of 172.16.0.2. It is possible to change the Ethernet port across which the MySQL data is transferred. This is set through the CMI, for more information on the CMI see Getting Started Basic CMI configuration

To change the CMI Ethernet port, on the CMI menu select, Advanced Menu, Networking, IPs and Routing, Change Appliance IPs, MySQL Replication Interface.

Set the Ethernet port as required.

Known Issues

When using Checkpoint Firewall Appliances passing MySQL traffic between PINsafe servers the following error message may be seen:

"TCP packet out of state: First packet isn't SYN tcp_flags: PUSH-ACK"

This can be resolved by changing the firewall rule for the service from Any to MySQL, i.e. TCP port 3306.

Troubleshooting

Error Messages

ERROR 1218 (08S01) at line 1: Error connecting to master: Lost connection to MySQL: Lost connection to MySQL server during query

This can be seen on running the database sync commands. Verify that a network connection exists and that the IP addresses are connect. Verify that the /etc/my.cnf file has the IP address of the Primary Master.

[ERROR] Slave I/O thread: error connecting to master 'replication@192.168.0.36:3306': Error: 'Can't connect to MySQL server on '192.168.0.36' (4)' errno: 2003 retry-time: 60 retries: 86400 101020 14:36:27 InnoDB: Started; log sequence number 0 2972511 101020

[Note] Recovering after a crash using /var/lib/mysql/bin 101020 14:36:27 [Note] Starting crash recovery...

[Note] Crash recovery finished.

These errors can be seen on a slave with the incorrect Primary Master IP address in the /etc/my.cnf. This should be configured through the CMI

Error 1049 (42000): Unknown database 'pinsafe_rep'

The database pinsafe_rep does not exist. This could be that it has not been created or that it is another database name such as pinsafe. To create a pinsafe database at the command line of the appliance where the database does not exist, type

mysql

create database pinsafe_rep;

exit;

Then check the database set up on the PINsafe Administration console, go to Database/General/MySQL 5.

Change the URL to jdbc:mysql://localhost/pinsafe_rep

1218 (08S01): Error connecting to master: lost connect to MySQL server during query

ERROR 1218 (08S01) at line 1: Error connecting to master: Lost connection to MySQL server during query

There is an error connecting to the MySQL database, check the network connection, that a database exists and has the correct name and permissions are correct.

MySQL Appliance Database Synchronisation