This page presents the procedure to setup the OphidiaDB for single-node and multi-node Ophidia deployment. The same procedure can be applied when starting from sources or binary packages.
Note
Before proceeding with the following steps, check that you have the required pre-requisites installed. In particular MySQL server should be installed and running. Additionally, make sure the Ophidia analytics Framework source code or binary package is available on the system where the OphidiaDB is going to be setup.
This setup can be used in case of basic single-node Ophidia deployment (see single-node setup). If you need to setup a multi-node deployment, you can skip this section.
Create the OphidiaDB and the oph_dimensions databases in the MySQL environment:
mysql> CREATE DATABASE ophidiadb;
mysql> CREATE DATABASE oph_dimensions;
Load the OphidiaDB schema and default parameters:
mysql -u root -p ophidiadb < /usr/local/ophidia/oph-cluster/oph-analytics-framework/etc/ophidiadb.sql
Note
If the databases are loaded by a MySQL user other than ‘root’ (e.g. the user ‘ophidia’), then grant privileges to access the databases for that user:
mysql > GRANT ALL PRIVILEGES ON `ophidiadb`.* TO 'ophidia'
mysql > GRANT ALL PRIVILEGES ON `oph_dimensions`.* TO 'ophidia'
In order to properly configure the OphidiaDB installation, launch the following queries.
This first query registers the available analytics node in the OphidiaDB (you should set cores and memory value accordingly with your host resources).
mysql > use ophidiadb;
mysql > INSERT INTO host (hostname, cores, memory) VALUES ('127.0.0.1',1,1);
then insert the I/O server available (using the MySQL user and password):
mysql > INSERT INTO dbmsinstance (idhost, login, password, port) VALUES (1, '<user>', '<password>', 3306);
mysql > INSERT INTO dbmsinstance (idhost, login, password, port, ioservertype) VALUES (1, '<user>', '<password>', 65000, 'ophidiaio_memory');
The first line adds a MySQL I/O server, while the second one an Ophidia I/O server (for in-memory analytics).
Finally, create the default host partition (called “main”) with the analytics node just registered.
mysql > INSERT INTO hostpartition (partitionname) VALUES ('main');
mysql > INSERT INTO hashost (idhostpartition, idhost) VALUES (1,1);
Note
If the version of MySQL server used is 5.7 (or later), than you also need to disable ‘ONLY_FULL_GROUP_BY’ option:
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
This setup must be used in case of multi-node Ophidia deployment (see multi-node setup). If you need to setup a single-node deployment, refer to the previous section.
On the Server node, create the OphidiaDB and the oph_dimensions databases in the MySQL environment:
mysql> CREATE DATABASE ophidiadb;
mysql> CREATE DATABASE oph_dimensions;
Load the OphidiaDB schema and default parameters:
mysql -u root -p ophidiadb < /usr/local/ophidia/oph-cluster/oph-analytics-framework/etc/ophidiadb.sql
Then grant privileges to access the databases to each Framework node; if the databases are loaded by a MySQL user other than ‘root’ (e.g. the user ‘ophidia’) you can run the following (for each Framework node IP addess or by providing a network mask or wildcard characters):
mysql > GRANT ALL PRIVILEGES ON `ophidiadb`.* TO 'ophidia'@'<IP address>'
mysql > GRANT ALL PRIVILEGES ON `oph_dimensions`.* TO 'ophidia'@'<IP address>'
In order to properly configure the OphidiaDB installation, launch the following queries.
This first query registers the available analytics nodes in the OphidiaDB and it should be run for each host (you should set cores and memory value accordingly with your host resources).
mysql > use ophidiadb;
mysql > INSERT INTO host (hostname, cores, memory) VALUES ('<IP address node1>',1,1);
mysql > INSERT INTO host (hostname, cores, memory) VALUES ('<IP address node2>',1,1);
...
then insert the I/O servers available (using the MySQL user and password). The first line adds a MySQL I/O server, while the second one an Ophidia I/O server (for in-memory analytics):
mysql > INSERT INTO dbmsinstance (idhost, login, password, port) VALUES (1, '<user>', '<password>', 3306);
mysql > INSERT INTO dbmsinstance (idhost, login, password, port, ioservertype) VALUES (1, '<user>', '<password>', 65000, 'ophidiaio_memory');
mysql > INSERT INTO dbmsinstance (idhost, login, password, port) VALUES (2, '<user>', '<password>', 3306);
mysql > INSERT INTO dbmsinstance (idhost, login, password, port, ioservertype) VALUES (2, '<user>', '<password>', 65000, 'ophidiaio_memory');
...
Finally, create the default host partition (called “main”) with the analytics nodes just registered.
mysql > INSERT INTO hostpartition (partitionname) VALUES ('main');
mysql > INSERT INTO hashost (idhostpartition, idhost) VALUES (1,1);
mysql > INSERT INTO hashost (idhostpartition, idhost) VALUES (1,2);
...
Note
If the version of MySQL server used is 5.7 (or later), than you also need to disable ‘ONLY_FULL_GROUP_BY’ option:
mysql > SET GLOBAL sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''));
In case of GSI support, add the Distinguished Name (DN) associated with the host where Ophidia Server is running in table “user”. For instance, append a new row as follows
mysql > INSERT INTO user ('username') VALUES ('/C=XX/O=XX/OU=Host/L=XX/CN=YY');
where YY has to be the hostname.