MySQL Appliance Database Synchronisation

Overview

This solution can be used on a Swivel Active/Active pair where synchronizing has failed and can be seen by differing number of users in the status page of the Swivel Administration console. For further information also see MySQL Database How To Guide.

Prerequisites

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

• Swivel Active/Active Cluster using the MySQL database (MySQL sync is configured during the initial network setup);

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

• Command line access to the Swivel 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 using the CMI

To re-sync the MySQL database's, the first step is to do this via the CMI menu. Only re-syncing the DB's manually is advised if the synchronising via the menu's fails. Please see Synchronising Commands.

1. Log into the Primary CMI (i.e via Putty) and select "Database" ("MySQL" on v2.0.14 and below).

2. Select "Synchronisation Status".

3. If the "Appliance Database is not Synchronising", then you can follow the options on-screen to re-sync the DB's.

4. Note - from version 2.0.16 of the Appliance, you have two options: "Re-Sync DB" and "Force-Sync DB".

5. Selecting option 1 or 2 from the latter, you will have the option of which DB you want to sync the data across to. For example, when logged into the Primary Appliance, if you select "Database on Primary Appliance (xxx.xx.xx.xx)". This means all the data from the Primary will be copied across to the Standby.

6. After this is complete, the Synchronisation Status should now show "The Database is Synchronised".

Synchronisation Commands

Swivel appliance versions 2.0.14 onwards have a sync Db option within the CMI MySQL menu for databases that are out of synchronisation, after making and verifying a backup, this can be used to bring the databases into synchronisation. If this fails to work, then manual synchronisation can be attempted below.

These commands should be run through the command line accessed through the CMI rather than the MySQL command line.

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.

Resync using script

The following command will synchronise the Primary data to the Standby using the default ETH 1 IP replication addresses. If different IP addresses are used such as ETH0 or replicating from Standby to Master, then the addresses should be changed.

/usr/local/bin/sync_db.sh 172.16.0.1 172.16.0.2

Example:

 [admin@primary ~]# /usr/local/bin/sync_db.sh 172.16.0.1 172.16.0.2 
 Copying DB from 172.16.0.1 to 172.16.0.2

Resync using commands

This can be used alternatively to the script above and may be of use if a problem is encountered.

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

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

Please note that if you have a very old appliance, or have upgraded from one, the database name may be "pinsafe_rep", rather than "pinsafe". Check the URI in the configuration to be sure, and adjust as appropriate.

Enter the following commands in turn from the command line (not within MySQL), 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 > /tmp/master_dump.sql
mysql -uadmin -plockbox -h SLAVE_IP -D pinsafe < /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 Swivel Primary Master, Example: 192.168.0.36

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

Enter the following commands in turn from the command line (not within MySQL), 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 > /tmp/master_dump.sql
mysql -uadmin -plockbox -h DR_IP -D pinsafe < /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;

• Select a user, then click Policy and check the "PIN never expires" checkbox. This change should take affect on the alternate server;

• Check the User stats on the Status screen on the other server, they should match;

Note: For a DR appliance, replication is only one-way so changes will only replicate TO the DR server and not vice versa.

Additional Options

Changing the Ethernet Replication Port

By default the Swivel 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.

From appliance version 2.0.14 onwards this is under the MySQL menu.

Known Issues

When using Checkpoint Firewall Appliances passing MySQL traffic between Swivel 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

Verify the replication interface being used on the appliances. Is it the same on both appliances?

Does the other replication interface respond to a PING from both the Primary and the Standby.

Can you Telnet from the Primary to the Standby and the other way using the replication interface and MySQL port 3306.

Check the Swivel, Apache Tomcat logs.

Checking the MySQL settings

On each of the servers run the following commands from the command line accessed from the CMI, the IP addresses should reflect the IP addresses of the related server:

cat /var/lib/mysql/master.info

cat /etc/my.cnf | grep master-host

Example:

Primary

 [admin@primary ~]# cat /var/lib/mysql/master.info
 14
 bin.000103
 98
 172.16.0.2
 replication
 master
 3306
 60
 0
 [admin@primary ~]# cat /etc/my.cnf | grep master-host 
 master-host = 172.16.0.2

Standby

 [admin@standby ~]# cat /var/lib/mysql/master.info
 14
 bin.000112
 98
 172.16.0.1
 replication
 master
 3306
 60
 0
 [admin@standby ~]# cat /etc/my.cnf | grep master-host 
 master-host = 172.16.0.1

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. Look for the line master-host = 192.168.0.37

[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 Swivel 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. If the IP addresses of the appliances have been changed, then this error can be seen if MySQL has not been resatrted, restart MySQL on each of the appliances (service mysqld restart).

Copying DB from 192.168.0.100 to 192.168.0.101

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

ERROR 1218 (08S01) at line 1: Error connecting to master: Access denied for user 'replication'@'appliance.swivel.local' (using password: YES)

This can occur if the /var/lib/mysql.conf file does not have the correct entry for the other Swivel appliance. Check the Network settings on each appliance, and if correct, check the entry in /var/lib/mysql.conf and /etc/my.cnf. The values here need to be the values of the other servers IP address. Changing these requires a restart of MySQL. All information should be entered through the CMI rather than editing these files, and if the problem persists, contact Swivel support.

ERROR 1198 (HY000) at line 1: This operation cannot be performed with a running slave; run STOP SLAVE first

This error has been seen on attempting to change the IP address through the CMI and prevented the MySQL IP address being changed, necessitationg manual change of the MySQL details. Contact Swivel for support.