The ROCKS Cluster uses MySQL for internal bookkeeping of the cluster configuration. But what happens if you want to run MySQL as part of a user-based application? Can this be done without tampering with or otherwise corrupting the ROCKS databases?
Yes it can.
ROCKS runs a MySQL binary (the foundation) generally found here (under the ROCKS directory tree):
/opt/rocks/bin
It’s related configuration file can be found here:
/opt/rocks/etc/my.cnf
And the data are stored here:
/var/opt/rocks/mysql
In addition, ROCKS runs this instance of MySQL on Port 40000 (not the conventional 3306). There should be a standard MySQL installation on the ROCKS head node that is not set to start by default. The binary, configuration, and database files for that instance can be found in the following standard locations (respectively):
/usr/bin
/etc/my.cnf
/var/lib/mysql
Starting this instance using the default /etc/my.cnf file should bind it to port 3306.
/etc/init.d/mysqld start
Use caution! This instance will have a BLANK root password, so you will want to fix that. Now, you can start MySQL normally, enter the command interface, and add databases or users as needed.
Then, you just need to grant access to the authorized users from the compute nodes. For example, imagine we create the database “testData”, the user “dataUser”, and we want to allow that user to connect from the compute nodes:
mysql --port 3306 -u root -p
mysql> CREATE DATABASE testData;
mysql> CREATE USER 'dataUser'@'%' IDENTIFIED BY 'somepass';
mysql> grant all on testData.* to dataUser;
mysql> flush privileges;
mysql> quit
Now let’s test from one of our compute nodes (call it, compute-0-1)
mysql -h myHeadNode -u testData --port=3306 -psomepass
This should allow you to connect.
Make sure to adjust your firewall or other security measures accordingly to prevent nefarious cracking attempts on your server!
Unfortunately, the current version of Rocks does not support a dedicated mysql server in that way you described in this post.
/etc/init.d/mysqld start
not works any more, because there is no such file any more — instead you can only launch a truncated version (they call it foundation-mysql) which bolsters the configuration of the cluster.
Thanks for adding. This howto specifically applies to Rocks 5.x and was written in 2014 (would likely work with 6.x, but cannot confirm). I would imagine much has changed with Rocks versions based on CentOS 7.x.