MySQL Database

Introduction

This guide outlines how to integrate Swivel Secure Sentry with a MySQL Server database. By default, Swivel uses an internal MariaDB database, but for enterprise environments, it can be configured to use an external MySQL Server to store user data, handle record deletions, and support high availability.

Note

The external database must be running and accessible for the Swivel server to start once this configuration is active.

Prerequisites

  • Fully configured Swivel Secure appliance.

  • MySQL server.

Architecture

Information is taken from the repository data source and inserted into the MySQL database. Multiple Swivel servers can use the MySQL database for user authentication. The MySQL database is replicated to other Swivel servers and DR sites in real time. Issues may arise if the servers lose connectivity for long periods (several days or more).

Repository Synchronisation Considerations

Multiple Swivel servers can connect to a database. It is essential that when multiple Swivel servers are synchronising data from the same data source (e.g. Active Directory), they do so at different times to avoid the same user being created simultaneously on different cluster members. This can be achieved by:

  • Setting only one active synchronisation.

  • Setting different times for synchronisation on each node.

Installation

Installing a JDBC Driver

If the JDBC driver is not on the Swivel server (the MySQL driver is supplied on Swivel virtual or hardware appliances), it must be downloaded.

  1. Download the driver from the MySQL website.

  2. Copy the file to the following location: <path to apache Tomcat>/lib

  3. Ensure that permissions and file/group ownership are correct (matching other files in the group).

  4. Restart Tomcat.

Configuration

Configure the Swivel Database

Note

For their data sources, Master servers should run in Synchronised mode and slave servers should run in Slave mode.

The following parameters need to be set for the database:

Identifier

Database Name

Class

Swivel Java class used for database

Driver

Software driver used for communication with database

URL

Location of the database

Username

Database username

Password

Database Password

Default MySQL 5 configuration (Virtual/Hardware Appliance):

  • Identifier: MySQL5

  • Class: com.swiveltechnologies.pinsafe.user.database.MySQL5Database

  • Driver: com.mysql.jdbc.Driver

  • URL: jdbc:mysql://localhost/pinsafe_rep

  • Username: pinsafe

  • Password: pinsafe

MySQL Configuration Screen

Warning

At this stage, DO NOT set the database to MySQL5. Keep it set to Appliance Database.

Create Local Database Admins

Ensure that the name of each XML repository is unique to each Swivel server. Ensure that each Swivel server has a unique admin account name.

Migrate Data into MySQL Database

Migrate the users from the Primary Master into the MySQL database. This is normally carried out on a single server unless differing repositories are used.

  1. From the Swivel Administration console, select Migration > Data.

  2. Select the MySQL database.

  3. Enter MIGRATE into the text box.

  4. Click Apply.

Migration Successful

The logs should indicate the following messages:

Database at jdbc:mysql://<MySQL_Server_IP>/<Database_Name>
Database com.mysql.jdbc.Driver loaded successfully.

Note: The length of time for Migration will vary, but 500 users will take approximately 2 minutes.

Select the MySQL Database

Once migration is complete, you must switch Swivel to use the MySQL database.

  1. From the Swivel Administration console, select Database > General.

  2. Select the MySQL database from the list.

  3. Click Apply.

The logs will indicate the following messages:

Database at jdbc:mysql://<MySQL_Server_IP>/<Database_Name>
Database com.mysql.jdbc.Driver loaded successfully.

Verify that the status page lists an Active database of MySQL:

Active database: MySQL 5
Database Selection

Troubleshooting

General Connectivity Checks

  • Verify the replication interface IP addresses on both appliances.

  • Ping the replication interface from both Primary and Standby.

  • Telnet from Primary to Standby (and vice versa) using the replication interface on MySQL port 3306.

  • Check Swivel and Apache Tomcat logs.

Bin Files and Expiry

Bin files are used as a transaction buffer. If connectivity is lost, these can accumulate. To manage these, run the following in the MySQL shell:

SET GLOBAL expire_logs_days = 10;
SET GLOBAL max_binlog_size = 256000000;

Ensure the following is set in /etc/my.cnf:

max_binlog_size=256000000

Common Error Messages

User Permission Errors (During Migration)

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: INDEX command denied to user 'pinsafe'@'localhost' for table 'PINSAFEM'

  • Fix: The Swivel user lacks necessary permissions. Ensure the user has full rights (excluding Grant) on the database.

Incorrect Database Name

Incorrect database name 'pinsafe_rep '

  • Fix: A wrong database name was specified. Ensure no trailing spaces are present. The default is pinsafe_rep (or pinsafe in some legacy configs).

MySQL 5.5 Syntax Error

MySQLSyntaxErrorException: You have an error in your SQL syntax... near 'TYPE = INNODB'

  • Cause: TYPE = INNODB is deprecated in newer MySQL versions.

  • Fix: Use an earlier version of MySQL or wait for a Swivel patch supporting MySQL 5.5+.