Tech Tip: How to backup and restore NFA 9.3.2+ databases

Document created by sigju01 Employee on Dec 28, 2015Last modified by sigju01 Employee on Jan 11, 2016
Version 10Show Document
  • View in full screen mode

Summary:

These are the best practices on how to backup and restore NFA 9.3.2+ databases

With the introduction of NFA 9.3.2+ NFA development has converted the databases to use the InnoDB Storage Engine. It provides the standard ACID-compliant transaction features, along with foreign key support (Declarative Referential Integrity).

THIS IS VERY IMPORTANT, IF THIS BACKUP/RESTORE IS NOT DONE PROPERLY YOU WILL HAVE TO REINSTALL NFA 9.3.2+ TO GET A CLEAN MySQL 5.6 DATABASE.

 

Background: 

In Previous versions of NFA (9.3.1 and below) we used MySQL 5.1 or 5.6 WITHOUT InnoDB. To backup and restore the databases was a much simpler / fail safe task. Whereas you use to be able to delete the contents of a database by stopping MySQL and deleting everything in the <installdir/mysql[51]/data> directory folders, you can no longer do that. Those folders should never be touched manually. That means you cannot rename folders and copy contents into and out of folders anymore either.

 

The reason being is because of the newly added InnoDB Storage Engine.

 

For example if you were to stop the MySQL service and remove the contents of the <installdir/mysql[51]/data/reporter> directory in NFA 9.3.1 and below, the database would be completely gone.

In NFA 9.3.2+ however, with the introduction of InnoDB, if you were to stop the MySQL service and remove the contents of the <installdir/mysql/data/reporter> directory there would still be remnants of that old reporter database in the caching files / logs. This would cause an issue when restoring another “reporter” database dump to an empty folder (“reporter” database). The following would NOT be the best practice to delete a database but if you already deleted the contents in the database folder or the whole folder itself we can add the reporter folder back manually and keep it empty and do a drop on the database which will remove it fully:

 

     1.   Type “mysql” in a command prompt.

     2.   Type “drop database reporter;” in the command prompt.

 

This will properly delete all of the caching statistics and table space data which cannot be deleted manually.  This will allow a MySQL restore to proceed without issue.

**FOR THE SIMPLEST WAY TO BACKUP/REPLACE/RESTORE A DATABASE SEE BELOW**

Environment: 

Network Flow Analysis 9.3.2+

 

Instructions:

Backup:

     1.   Open a command prompt.

     2.   Type: mysqldump “databaseName” > X:/databaseName.sql

     3.   Confirm there are no errors and the cursor returns and that the .sql file has the contents you need.

Restore:

     1.   Open a command prompt.

     2.   First as discussed in detail above we must Drop the original database / tablespace / ibd cache files.

     3.   Type: mysql

     4.   Type: drop database “databaseName”;

     5.   If the database dropped without errors then proceed to the restore in the next step.

     6.   Type: exit (to exit MySQL and return to command line)

     7.   Type: mysql “databaseName” < X:/databaseName.sql

     8.   Confirm there are no errors and the MySQL prompt should return and database should have the contents you need.

 

Additional Information:

  • TEC1235538 If you get an error on a MySQLDump in NFA 9.3.0+ like this:

 

mysqldump: Couldn't execute 'SET OPTION SQL_QUOTE_SHOW_CREATE=1': 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 'OPTION SQL_QUOTE_SHOW_CREATE=1' at line 1

 

  • TEC1056445 If you get an error on a MySql Restore in NFA 9.3.0+ like this:

 

ERROR 1813 (HY000) at line 25: Tablespace for table '`databaseName`.`tablename`' exists. Please DISCARD the tablespace before IMPORT.

 

 

 

KB Article ID : TEC1029127

Attachments

    Outcomes