View source for MySQL Appliance Database Synchronisation

{{Template:default}}
{{Template:how}}

= 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: [http://kb.swivelsecure.com/wiki/index.php/MySQL_Database_How_To_Guide#Verifying_the_Installation 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 [https://kb.swivelsecure.com/wiki/index.php/MySQL_Appliance_Database_Synchronisation#Synchronisation_Commands 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

Enter the following commands in turn from the [[Command_Line_Access_How_to_guide | 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_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 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_Access_How_to_guide | 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_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;

* 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;

<span style="color:red;">Note: For a DR appliance, replication is only one-way so changes will only replicate TO the DR server and not vice versa.</span>


= 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.