2.7. Databases¶
2.7.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.7.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.7.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.7.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.7.3.2. Migration Process¶
stop NetSpyGlass server:
vadim@hub:~$ sudo service netspyglass stopExport 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.xmlAs 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.dataAt 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/dbEdit 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