2.6. Databases

2.6.1. Running NetSpyGlass with embedded database HSQLDB

This is the default, that is, configuration file samples included with the package are preconfigured to use embedded database HSQLDB. This database stores data in files on the file system, the default directory is ${home}/db (where ${home} refers to the value of the parameter home in the config file). If you installed using rpm or deb package, this will be /opt/netspyglass/home/db.

Configuration parameter that determines what database is used by the server is ui.storage.connectionUrl. In case of HSQLDB it looks like this:

ui {
    storage.connectionUrl = "hsqldb:file://"${home}"/db/nw2.db"

There are no other configuration parameters for HSQLDB, it works pretty much “out of the box”.

2.6.2. Running NetSpyGlass with MySQL

To connect to MySQL, the server needs to have information about the host, port, user name and password used to establish connection. These parameters are defined in the config as follows:

ui {
    storage.connectionUrl = "mysql://mysql_database_host:3306/nw2mon"
    storage.username = "nw2"
    storage.password = "nw2pass"

Here:

  • the host is mysql_database_host
  • port is 3306 (this is the default)
  • user name is “nw2”
  • password is “nw2pass”

Alternatively you can provide user name and password as pat of the connection url :

ui {
    storage.connectionUrl = "mysql://mysql_database_host:3306/nw2mon?user=nw2&password=nw2pass"

Remove or comment out parameters ui.storage.username and ui.storage.password if you use this connection url format.

Database “nw2mon” must exist before you start the server, however the server will create all tables.

You can create the database and database user like this. First, connect to mysql server as root:

mysql -u root -p -h localhost
mysql>

Then create database nw2mon, grant access to user nw2 with password nw2pass:

mysql> create database nw2mon;
Query OK, 1 row affected (0.00 sec)

mysql> create user 'nw2'@'%' identified by 'nw2pass';
Query OK, 0 rows affected (0.00 sec)

mysql> grant all privileges on *.* to 'nw2'@'%';
Query OK, 0 rows affected (0.00 sec)

After this you can start NetSpyGlass server.

2.6.3. How to migrate from embedded HSQLDB database to MySQL

Our default configuration file is set up to use HSQLDB because it does not have any external dependencies and does not require any special setup or configuration. This database, however, does not scale well when number of devices, views and other objects grows beyond certain point. Server takes long time to start and some operations can be slow. For large installations we recommend migrating to MySQL.

Here we assume that NetSpyGlass has been working for a while with embedded HSQLDB database and you want to copy your existing devices, views and other objects to MySQL. This also includes map layouts for all users. The goal of this migration is to copy devices while preserving their IDs in order to maintain continuity of the monitoring data we have collected while working with HSQLDB.

Note

If you do not care for preserving monitoring data, you can simply change database configuration in the config file nw2.conf to point at the MySQL database server and let NetSpyGlass recreate all device and view objects from configuration. Since these will be brand new objects, they will have different IDs and access to old monitoring data will be lost. This, however, is the simplest way to switch to MySQL since it does not require any special actions from the administrator.

2.6.3.1. Prerequisites

You are going to need access to MySQL server with a database user account that can create and drop databases and tables. It is possible to work with an account that has limited access to a database that has already been created but in this case the account needs privileges to be able to create and drop tables.

Database must be created before starting migration; the tables will be created in the process.

We are going to assume that database account is called “nw2” and database name is “nw2mon”.

Note

Our migration scripts preserve device discovery data only from the latest generation, that is, only one, the latest discovery. Discovery data collected on previous discovery runs is not migrated to the new database. However this does not affect monitoring data which is stored in a separate Time Series Database and is not subject to this migration. All monitoring data is preserved and remains accessible after migration.

2.6.3.2. Migration Process

  • stop NetSpyGlass server:

    vadim@hub:~$ sudo service netspyglass stop
    
  • Export the database using script export_db.sh. This script stores data it reads from the database in multiple files that it creates in a directory you pass to it as a second command line argument. The directory can be located anywhere, such as in your home directory. Here is how this looks like:

    vadim@hub:~$ pwd
    /home/vadim
    vadim@hub:~$ mkdir db_backup
    vadim@hub:~$ /opt/netspyglass/current/bin/export_db.sh /opt/netspyglass/home/nw2.conf db_backup/
    
    Reading data from database jdbc:hsqldb:file:///opt/netspyglass/home/db/nw2.db;readonly=true;hsqldb.lock_file=false;hsqldb.log_data=false
    Connection to "User=sa, Catalog=PUBLIC, URL=jdbc:hsqldb:file:///opt/netspyglass/home/db/nw2.db;readonly=true;hsqldb.lock_file=false;hsqldb.log_data=false" successful
    Variable generation defined with value '25'.
    It can be used using $[generation]
    SQL execution time: 0.01s
    13 table(s) exported to /home/vadim/db_backup
    
    SQL execution time: 0.33s
    XML export initialized
    Result of the next SELECT statement will be written to /home/vadim/db_backup/area_generations.xml
    SQL execution time: 0s
    Exporting data successful (1 rows)
    Data written to file: /home/vadim/db_backup/area_generations.xml
    

    As you can see in this example, you do not need to be root or even the user used to run the server (by default nw2) to export the database. As the result, you get bunch of files in the directory db_backup:

    vadim@hub:~$ ls db_backup/
    api_access_tokens.xml              devices_r17_c8.data    event_log_r14_c2.data  event_log_r25_c3.data   interfaces_r13_c2.data  interfaces_r24_c6.data  interfaces_r6_c2.data
    area_generations.xml               devices_r18_c8.data    event_log_r14_c3.data  event_log_r2_c2.data    interfaces_r13_c6.data  interfaces_r25_c2.data  interfaces_r6_c6.data
    areas_views.xml                    devices_r19_c8.data    event_log_r15_c2.data  event_log_r2_c3.data    interfaces_r14_c2.data  interfaces_r25_c6.data  interfaces_r7_c2.data
    areas.xml                          devices_r1_c8.data     event_log_r15_c3.data  event_log_r3_c2.data    interfaces_r14_c6.data  interfaces_r26_c2.data  interfaces_r7_c6.data
    
  • At this point the old HSQLDB database is intact and can be used again if you decide to revert back to it. It still makes sense to make backup copy of it:

    $ tar cf old_database_backup.tar /opt/netspyglass/home/db
    
  • Edit configuration file /opt/netspyglass/home/nw2.conf and switch to MySQL:

   ui {
       storage.connectionUrl = "mysql://mysql_database_host:3306/nw2mon"
       storage.username = "nw2"
       storage.password = "nw2pass"

- save configuration file but *do not start the server yet*. The database in MySQL must
  exist but must remain empty before we run the import script.

   .. note::

       If you start the server now, it will create tables in the database and add records for
       the devices and views. You can't import your old data into the database if this happens
       because it will cause ID collisions. If you start the server at this point, stop it and
       drop the database in MySQL, then create the database again and proceed with the procedure.

- Verify that the database exists but has no tables using mysql command line tool::

       vadim@hub:~$ mysql -h localhost -u nw2 -p nw2mon
       Enter password:
       mysql> show tables;
       Empty set (0.00 sec)

       mysql>

- Run import script to import saved database data::

       vadim@hub:~$ /opt/netspyglass/current/bin/import_db.sh /opt/netspyglass/home/nw2.conf  db_backup/
       Will copy data to the database jdbc:mysql://localhost:3306/nw2mon
       Connection to "User=nw2, Database=nw2mon, URL=jdbc:mysql://localhost:3306/nw2mon" successful
       Batch updating started.
       All following statements will be added to the driver's internal batch queue until WBENDBATCH is executed.
       SQL execution time: 0.01s
       Table 'api_access_tokens' created
       SQL execution time: 0.09s
       Index 'FK_bqsksrt0hrymg2nw3sipptfs1' created
       SQL execution time: 0.16s
       Table 'areas' created
       SQL execution time: 0.11s

        . . . . . more output  . . . . .

       Connection to "User=nw2, Database=nw2mon, URL=jdbc:mysql://localhost:3306/nw2mon" successful
       Importing file '/home/vadim/db_backup/msd.xml' into table msd
       46 row(s) inserted
       0 row(s) updated

       SQL execution time: 0.24s

       1 statement executed.
       Execution time: 0.24s
       vadim@hub:~$

  Depending on the server performance and volume of data copied from HSQLDB database, this may take up to
  a couple of minutes.

- At this point tables should be created in MySQL database. Verify this:
        mysql> show tables;
        +-------------------------------+
        | Tables_in_nw2mon              |
        +-------------------------------+
        | api_access_tokens             |
        | area_generations              |
        | areas                         |
        | areas_views                   |
        | cluster_interfaces            |
        | cluster_interfaces_interfaces |
        | clusters                      |
        | clusters_devices              |
        | con_test                      |
        | device_discovery_data         |
        | device_generations            |
        | devices                       |
        | event_log                     |
        | graph_links                   |
        | interfaces                    |
        | key_value_store               |
        | maps                          |
        | msd                           |
        | nw2                           |
        | user                          |
        | variable_registry             |
        | views                         |
        | wap_data                      |
        +-------------------------------+
        23 rows in set (0.00 sec)

        mysql>

- We can check if our devices are there:
        mysql> select id,address,name from devices;
        +----+-------------+---------------------+
        | id | address     | name                |
        +----+-------------+---------------------+
        |  1 | 10.0.14.40  | 10.0.14.40          |
        |  2 | 10.0.14.131 | 10.0.14.131         |
        |  3 | 10.0.14.122 | 10.0.14.122         |
        |  4 | 10.0.14.15  | nas1                |
        |  5 | 10.0.14.227 | 10.0.14.227         |
        |  6 | 10.0.14.226 | 10.0.14.226         |
        |  7 | 10.0.14.125 | 10.0.14.125         |

  Device ids should be the same as they were in the old database. You can verify this by checkng
  discovery reports::

       vadim@hub:~$ head -6 /opt/netspyglass/home/reports/my_test_network/discovery/0/nodes/nas1
       Discovery report for nas1

       Report generated 2015-03-24T07:00:20.584-07:00

       Database Id: 4
       Generation = 25

  Report says database Id of this device is "4", which agrees with the output of the "select ... from devices"
  command.

- This is it, now you can start the server and check if everything is ok in the UI::

   vadim@hub:~$ sudo service netspyglass start