This is an example of what can be achieved on Nimbus Research Cloud. The detailed configuration for various VM flavours and OS configurations might differ. Nimbus users are responsible for managing their VMs and installing tools. We believe that this step-by-step example might be helpful for users wanting to use MySQL.

Running mysql on nimbus

Step 1- Installation

Install mySQL on Nimbus with:

sudo apt-get update && sudo apt-get install -y mysql-server

Install mySQL on Zeus/other platform with Conda- install miniconda in /group/PROJECT/USER directory with Miniconda.sh downloaded from the internet https://docs.conda.io/en/latest/miniconda.html. Then use:

conda create -c conda-forge -n mysql mysql 
conda activate mysql

Step 2- edit configuration

Nimbus config file

On Nimbus you need to edit /etc/mysql.conf.d/mysqld.cnf to comment out the following line so it looks like this:

#bind-address		= 127.0.0.1

Nimbus security groups

Then, go to the 'security groups' section on the Nimbus Openstack webpage (where you created your instance). You will need to edit your custom security group to open the mySQL port to the IP address(es) that will be accessing the database. It is very important to restrict the IP addresses for security reasons:

Specifications: Direction: ingress Ether Type: IPv4 IP protocol: TCP Port Range: 3306 Remote IP prefix: 146.118.0.0/16 (this is for the Pawsey range of IPs)

If you need to add an additional specific IP address, you can add another rule with the following specifications

Direction: ingress Ether Type: IPv4 IP protocol: TCP Port Range: 3306 Remote IP prefix: <YOUR_SPECIFIC_IP_ADDRESS>

Under no circumstances should you have the Remote IP prefix: 0.0.0.0/00

Create the user and database on Nimbus

Run the following, filling in the database name, user and password you want. You might need to prepend sudo to the mysql command.

mysql -u root -p CREATE DATABASE DATABASE_NAME; 
CREATE USER 'CHOSEN_USERNAME'@'localhost' IDENTIFIED BY 'SECURE_PASSWORD'; 
GRANT ALL PRIVILEGES ON cromwell.* TO 'CHOSEN_USERNAME'@'localhost' WITH GRANT OPTION; 
CREATE USER 'CHOSEN_USERNAME'@'%' IDENTIFIED BY 'SECURE_PASSWORD'; 
GRANT ALL PRIVILEGES ON cromwell.* TO 'CHOSEN_USERNAME'@'%' WITH GRANT OPTION;

Edit the Zeus config file

On Zeus/other system you need to edit the ~/my.cnf file to include the following:

[client]
user=CHOSEN_USERNAME
password="SECURE_PASSWORD"

See https://dev.mysql.com/doc/refman/5.7/en/option-files.html for more configuration options.

Step 3- check it's working

Use the following command to test the connection works:

mysql -h YOUR_NIMBUS_IP eg 111.111.11.111 -D DATABASE_NAME -u CHOSEN_USERNAME -p

It will ask you for your password. You should be able to view the database eg:

mysql> show tables;
+----------------------------------+
| Tables_in_cromwell               |
+----------------------------------+
| CALL_CACHING_AGGREGATION_ENTRY   |
| CALL_CACHING_DETRITUS_ENTRY      |
| CALL_CACHING_ENTRY               |
| CALL_CACHING_HASH_ENTRY          |
| CALL_CACHING_SIMPLETON_ENTRY     |
| CUSTOM_LABEL_ENTRY               |
| DATABASECHANGELOG                |
| DATABASECHANGELOGLOCK            |
| DOCKER_HASH_STORE_ENTRY          |
| JOB_KEY_VALUE_ENTRY              |
| JOB_STORE_ENTRY                  |
| JOB_STORE_SIMPLETON_ENTRY        |
| METADATA_ENTRY                   |
| SQLMETADATADATABASECHANGELOG     |
| SQLMETADATADATABASECHANGELOGLOCK |
| SUB_WORKFLOW_STORE_ENTRY         |
| SUMMARY_STATUS_ENTRY             |
| WORKFLOW_METADATA_SUMMARY_ENTRY  |
| WORKFLOW_STORE_ENTRY             |
+----------------------------------+
19 rows in set (0.56 sec)

mysql> describe CALL_CACHING_ENTRY;
+---------------------------+--------------+------+-----+---------+----------------+
| Field                     | Type         | Null | Key | Default | Extra          |
+---------------------------+--------------+------+-----+---------+----------------+
| CALL_CACHING_ENTRY_ID     | int(11)      | NO   | PRI | NULL    | auto_increment |
| WORKFLOW_EXECUTION_UUID   | varchar(255) | YES  | MUL | NULL    |                |
| CALL_FULLY_QUALIFIED_NAME | varchar(255) | YES  |     | NULL    |                |
| JOB_INDEX                 | int(11)      | YES  |     | NULL    |                |
| RETURN_CODE               | int(11)      | YES  |     | NULL    |                |
| ALLOW_RESULT_REUSE        | tinyint(4)   | YES  |     | 1       |                |
| JOB_ATTEMPT               | int(11)      | YES  |     | NULL    |                |
+---------------------------+--------------+------+-----+---------+----------------+
7 rows in set (1.80 sec)

mysql> select * from CALL_CACHING_ENTRY;
+-----------------------+--------------------------------------+---------------------------+-----------+-------------+--------------------+-------------+
| CALL_CACHING_ENTRY_ID | WORKFLOW_EXECUTION_UUID              | CALL_FULLY_QUALIFIED_NAME | JOB_INDEX | RETURN_CODE | ALLOW_RESULT_REUSE | JOB_ATTEMPT |
+-----------------------+--------------------------------------+---------------------------+-----------+-------------+--------------------+-------------+
|                     1 | 2770f27b-ff2b-40f9-ab9a-1c9e08382ed2 | wf.hello                  |        -1 |           0 |                  1 |           1 |
+-----------------------+--------------------------------------+---------------------------+-----------+-------------+--------------------+-------------+
1 row in set (0.60 sec)

User Support Documentation

Running a MySQL Database on Nimbus