MySQL Database How To Guide

Introduction

This document outlines the necessary steps to change from an internal Swivel database on a Primary/Secondary Master or Standalone Swivel server to a MySQL database on one or more servers. The MySQL database must be running for Swivel to start. On Swivel appliances, the initial configuration through the CMI sets up the MySQL configuration for MySQL Appliance Database Synchronisation see Getting Started Basic CMI configuration.

Prerequisites

Fully configured Swivel server (Ensure XML repositories and admin users are unique on multiple Swivel servers)

MySQL server or Swivel virtual or hardware appliance with MySQL

Baseline

Swivel 3.5

CMI 0.9w

MySQL Server 5.0.22

Webmin 1.430

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 severs 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 they do so at different times so as to avoid the same user being created at the same time on different cluster members. This can be done by setting only one active synchronisation, or setting different times for synchronisation.

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 will need to be downloaded see http://dev.mysql.com/downloads/connector/j/5.1.html

Copy the file to <path to apache Tomcat>/webapps/pinsafe/WEB-INF/lib

Ensure that the permissions, and any file or group ownership are correct, i.e. the same as the other files in the group.

Restart Tomcat

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 database config on a Swivel virtual or 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

Note: At this stage DO NOT set the database to MySQL5 but keep it as Internal

Create Local database admins on each Swivel server

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. See Repository

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. See also Migrate How to guide.

From the Swivel Administration console select Migration/Data then select the MySQL database and enter MIGRATE, click on apply. A message indicates the data has been successfully migrated.

The logs will indicate the following messages:

Database at jdbc:mysql://localhost/pinsafe_rep

Database com.mysql.jdbc.Driver loaded successfully.

The length of time for Migration will vary between install sites, but 500 users will approximately take 2 minutes.

Select the MySQL database

Select the MySQL database

From the Swivel Administration console select Database/General then select the MySQL database and click on apply.

The logs will indicate the following messages:

Database at jdbc:mysql://localhost/pinsafe_rep opened successfully.

Database com.mysql.jdbc.Driver loaded successfully.

Verify that the status page lists an Active database of MYSQL

Active database MySQL 5

Connecting Swivel to remote Swivel MySQL virtual or hardware appliances

Swivel can be configured to use a remote MySQL virtual or hardware appliance database.

Configure the Swivel application

Default MySQL 5 database config on a Swivel virtual or hardware appliance:

Identifier MySQL5

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

Driver: com.mysql.jdbc.Driver

URL: jdbc:mysql://remote_ip/pinsafe_rep

Username: pinsafe

Password: pinsafe

Verifying the Installation

Check status of servers, This can be done through the CMI.

MySQL testing through the CMI

How Do I tell if the virtual or hardware Swivel appliances are in synchronisation?

Swivel virtual or hardware Appliance version 2.0.14 shows the status as Synchronised.

Swivel virtual or hardware Appliance version 2.0.13 see the below:

On the Swivel virtual or hardware appliances CMI select the MySQL status option. The Primary Master and Standby Slave positions should be the same, the Standby Master and Primary Slave positions should be the same. For a DR virtual or hardware appliance, the Primary Master and DR Slave should be the same.

If the positions are not synchronised, verify the following:

• If the system has just been started allow time for the databases to synchronise
• Ensure all Slave IO Running are Yes
• Ensure all Slave SQL Running are Yes

In the example below the Swivel virtual or hardware appliances are in synchronisation, Note the Master/Master Slave/Slave positions do not need to be the same, only the Master/Slave and Master/Slave:

Primary Master Position : 98 matches the Standby slave Position : 98

Standby Master Position : 282991 matches the Standby slave Position 282991

MySQL testing through the Swivel Administration Console

Create a user on the Primary Master and verify that the user can be seen on the Standby Master. Create a user on the Standby Master and verify that the user appears on the Primary Master. Verify that the user appears on any DR virtual or hardware appliances.

Verify that the total numbers of users and locked accounts on the Primary Master and Standby Master are the same.

Note a user who becomes locked on a DR appliance will not be reflected on the Primary Master or Standby Master.

MySQL Command line Testing

Where possible the CMI should be used for testing. The information below is provided for those familiar with MySQL commands and their use.

MySQL commands for Master/Primary/DR are given below.

Use the status command to check the status of the MySQL servers. Each of the servers has a master database and a slave database to the other server.

 mysql
 show master status;
 show slave status\G;

The Slave IO state should be ‘Waiting for master to send event’ when it is idle. The values for the Position on the Primary Master should be the same as the Secondary Master Slave

The master status values for Position on the Primary Master should match that of the slave status Read_Master_Log_Position on the Secondary Master.

The master status values for Position on the Secondary Master should match that of the slave status status Read_Master_Log_Position on the Primary Master.

• On the Primary Master

 mysql> show master status;
 +------------+----------+--------------+------------------+
 | File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------+----------+--------------+------------------+
 | bin.000001 |      202 |              | mysql,test       |
 +------------+----------+--------------+------------------+
 1 row in set (0.00 sec)

• On the Secondary Master

 mysql> show slave status\G;
 *************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event
               Master_Host: 10.1.130.1
               Master_User: replication
               Master_Port: 3306
             Connect_Retry: 60
           Master_Log_File: bin.000001
       Read_Master_Log_Pos: 202
            Relay_Log_File: relay.000002
             Relay_Log_Pos: 229
     Relay_Master_Log_File: bin.000001
          Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
           Replicate_Do_DB: pinsafe_rep
       Replicate_Ignore_DB:
        Replicate_Do_Table:
    Replicate_Ignore_Table:
   Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
                Last_Errno: 0
                Last_Error:
              Skip_Counter: 0
       Exec_Master_Log_Pos: 202
           Relay_Log_Space: 229
           Until_Condition: None
            Until_Log_File:
             Until_Log_Pos: 0
        Master_SSL_Allowed: No
        Master_SSL_CA_File:
        Master_SSL_CA_Path:
           Master_SSL_Cert:
         Master_SSL_Cipher:
            Master_SSL_Key:
     Seconds_Behind_Master: 0
 1 row in set (0.00 sec)

 ERROR:
 No query specified

• On the secondary Master

 mysql> show master status;
 +------------+----------+--------------+------------------+
 | File       | Position | Binlog_Do_DB | Binlog_Ignore_DB |
 +------------+----------+--------------+------------------+
 | bin.000001 |      202 |              | mysql,test       |
 +------------+----------+--------------+------------------+
 1 row in set (0.00 sec)

• On The Primary Master

 mysql> show slave status\G;
 *************************** 1. row ***************************
            Slave_IO_State: Waiting for master to send event
               Master_Host: 10.1.130.2
               Master_User: replication
               Master_Port: 3306
             Connect_Retry: 60
           Master_Log_File: bin.000001
       Read_Master_Log_Pos: 202
            Relay_Log_File: relay.000002
             Relay_Log_Pos: 229
     Relay_Master_Log_File: bin.000001
          Slave_IO_Running: Yes
         Slave_SQL_Running: Yes
           Replicate_Do_DB: pinsafe_rep
       Replicate_Ignore_DB:
        Replicate_Do_Table:
    Replicate_Ignore_Table:
   Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
                Last_Errno: 0
                Last_Error:
              Skip_Counter: 0
       Exec_Master_Log_Pos: 202
           Relay_Log_Space: 229
           Until_Condition: None
            Until_Log_File:
             Until_Log_Pos: 0
        Master_SSL_Allowed: No
        Master_SSL_CA_File:
        Master_SSL_CA_Path:
           Master_SSL_Cert:
         Master_SSL_Cipher:
            Master_SSL_Key:
     Seconds_Behind_Master: 0
 1 row in set (0.00 sec)
 
 ERROR:
 No query specified

Troubleshooting

Verify the replication interface being used on the virtual or hardware 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.

If the the Swivel MySQL virtual or hardware appliances are out of synchronisation, then the following solution can be used: MySQL Appliance Database Synchronisation

Bin files are used as a transaction buffer to track record changes which are synced to the alternate server. If the server has become busier and you are suffering from connectivity issues between the two servers it would store up transactions for the amount of days set.

To manage these bin files, from within the mysql shell you can run the following commands:

SET GLOBAL expire_logs_days = 10;

SET GLOBAL max_binlog_size = 256000000;

You can also ensure that the following is set within the /etc/my.cnf file:

max_binlog_size=256000000

If the Slave IO Running : or Slave SQL Running : is set to No it may indicate that the appliances are not in synchhronisation, a communication issue, or the networkig is not cotrectly setup.

Error Messages

the MySQL Database is not Synchronising

This can be seen in the Administration Console for MySQL status. Viewing the details may show a Slave SQL Running : No as not running or the Db pointers not matching. This can be caused by no communication between virtual or hardware appliances for an extended period of time, such as more than a couple of days or after an IP address change. The data may be synchronised from one server to another using the sync Db option and then select the data source to synchronise from, then when complete allow a few minutes for the databases to recognise synchronisation. If this fails then follow the procedure for manually synchronising the databases, see MySQL Appliance Database Synchronisation

Error Connecting to Master

mysql> load data from master;

ERROR 1218 (08S01): Error connecting to master: Access denied for user 'replication'@'192.168.0.129' (using password: YES)

Change the Master SQL Server Slave IP addresses permitted to authenticate. This is done in the webmin

User Permission errors

The following may be seen when attempting to MIGRATE the database to MYSQL;

192.168.0.1 admin:Unable to open the database: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: INDEX command denied to user 'pinsafe'@'localhost' for table 'PINSAFEM'

192.168.0.1 admin:PINsafe data migration failed! com.swiveltechnologies.pinsafe.user.database.DatabaseException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: INDEX command denied to user 'pinsafe'@'localhost' for table 'PINSAFEM'

To change the Swivel user permissions see MySQL_permissions

PINsafe data migration failed! com.swiveltechnologies.pinsafe.server.user.database.DatabaseException: com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Incorrect database name 'pinsafe_rep '

Wrong database name has been specified. In this case the database should have been pinsafe

Exception occurred during database access, exception: com.swiveltechnologies.pinsafe.user.database.DatabaseException: java.sql.SQLException: Access denied for user 'pinsafe'@'localhost' (using password: YES)

The user pinsafe does not have access to the database, the incorrect database may be specified (default is pinsafe_rep) or the password may have been changed from the default value. Also see MySQL_permissions.

Exception occurred during database access, exception: com.swiveltechnologies.pinsafe.user.database.DatabaseException: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE = INNODB DEFAULT CHARSET = utf8 COLLATE = utf8_bin' at line 1

There is an issue with Swivel and MySQL 5.5. This will be resolved in a future release of Swivel; meanwhile, we recommend using an earlier version of MySQL.

 Appliance Database : Status
 
 The Appliance Database is not Synchronising
 
 1. Re-Sync DB (first option)
 2. Force-Sync DB (if all else fails)
 3. Start slave DB ( SLAVE NOT RUNNING )
 4. Refresh
 0. Exit
 
 DB SYNC ERROR
 You can only use option 2 (Force-Sync) pick source DB wisely..
 
 LAST_ERROR: _Error_'Table_'PINSAFEL'_already_exists'_on_query._Default_database:_'pinsafe_rep'._Query:_'CREATE_TABLE_PINSAFEL_(A_SMALLINT_NOT_NULL_AUTO_INCREMENT_PRIMARY_KEY,_B_VARCHAR(32)_NOT_NULL_UNIQUE)_ENGINE_=_INNODB_DEFAULT_CHARSET_=_utf8_COLLATE_=_utf8_bin

If this is seen on appliances 2.0.16 it may indicate that the option to Force-Sync DB (if all else fails) is required, usually from the Primary.

Configure the MySQL Database to allow MySQL connections

On the Webmin , select Servers/MySQL then user permissions. Create a user with the following settings:

Usename pinsafe, user configured to allow access from the Swivel server

Password password used to allow access from the Swivel server

Hosts IP address/hostname of the Swivel server

Permissions select all the permissions except Grant privileges

Click create to save the settings.

Known Issues and Limitations

Virtual or hardware appliance build 2.0.9 has a requirement to set Swivel user permissions, see troubleshooting above.

Additional Information

For assistance in the Swivel installation and configuration please firstly contact your reseller and then email Swivel Secure support at support@swivelsecure.com