MySQL Database Export and Import

Overview

This document outlines how to create a copy of the PINsafe MySQL database and then export it or import it.

Prerequisites

Swivel 3.x

Swivel appliance 2.x

Symptoms

A copy of the MySQL database is required.

Solution

If the Swivel server is running the CMI, then a backup of the database can be made, copied across to another appliance and then restored.

If the CMI is not accessible then this can be done from the command line.

Creating a MySQL dump of the database

On the Swivel Primary Master which hold the PINsafe data, run the following from the command line to create a copy of the pinsafe_rep database called master_dump.sql in the tmp folder. This can be carried out on an active system.

mysqldump --single-transaction --flush-logs pinsafe_rep > /tmp/master_dump.sql

Copy MySQL dump to new appliance

Ensure Tomcat is powered off on both of the NEW Active/Active nodes

Copy the file into the /tmp folder. For guidance on copying files see Copying appliance files How to Guide

Importing a MySQL dump into the database

Note: this will overwrite existing data on the system. Run the following from the command line. It assumes the dump file is called master_dump.sql and is in the tmp folder, and will import the data into the pinsafe_rep database.

mysql -uadmin -plockbox -h localhost -D pinsafe_rep < /tmp/master_dump.sql

Synchronising files on appliances

To syncronise the new appliances (to ensure that the primary database is definitely replicated to the secondary), use the commands in the following article to get the databases syncronised.

MySQL Appliance Database Synchronisation

Then, start Tomcat on the Primary new appliance. If PINsafe is a new version then the databases will be upgraded. Once Tomcat is started on the Primary, you can start it on the Secondary.

restart Tomcat

To ensure that the target for the Swivel data is able to read it correctly restart Tomcat. If the Swivel application fails to start check the /var/logs/catalina.out file for any database errors, and if they exist see: PINsafe upgrade fails on MySQL appliance.

Troubleshooting

mysql -uadmin -plockbox -h localhost -D pinsafe_rep < /tmp/master_dump.sql ERROR 1045 (28000): Access denied for user 'admin'@'localhost' (using password: YES)

Admin user does not have sufficient privileges, try with the root user:

mysql -uroot -h localhost -D pinsafe_rep < /tmp/master_dump.sql

Swivel fails to start when installing to a different version

See PINsafe upgrade fails on MySQL appliance