MySQL Database Export and Import
From Swivel Knowledgebase Wiki
Contents |
Overview
This document outlines how to create a copy of the PINsafe MySQL database and then export it or import it.
Prerequisites
PINsafe 3.x
PINsafe appliance 2.x
Symptoms
A copy of the MySQL database is required.
Solution
If the PINsafe server is running the CMI, then a backup of the database can be made, copied accross 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 PINsafe 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.
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
