CREATE HPC access to Cloud database

Attention

This approach is only suitable for processing non-sensitive data. The security of the database relies on a single password authentication step from any other CREATE user. For processing sensitive data please contact us about the CREATE Trusted Research Environment.

In this section we talk about how to setup a simple MariaDB (MySQL fork) database that can be accessed from the CREATE HPC cluster. This is intended to be a basic "Hello, World!" type example for building such integrations between HPC and Cloud sub-systems of CREATE.This is not a MySQL setup, administration or API interaction guide, further readings on those topics should be sought in line with your design goals.

• Follow the Instance Creation doc to create a new VM.
• On the VM
  • sudo apt update && sudo apt install -y mariadb-server
  • sudo systemctl start mariadb.service
  • sudo mysql_secure_installation
    • enter for none
    • n for set root password
    • Y for everything else
  • Setup your database, for example:

ubuntu@cloudpig:~$ sudo mysql
MariaDB [(none)]> CREATE DATABASE mysql_example;
MariaDB [(none)]> use mysql_example;
MariaDB [mysql_example]> CREATE TABLE stuff (
    -> id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    -> thing VARCHAR(100) NOT NULL);
MariaDB [mysql_example]> INSERT INTO stuff (thing) VALUES ('foo'), ('bar'), ('baz');
MariaDB [mysql_example]> select * from stuff;
+----+-------+
| id | thing |
+----+-------+
|  1 | foo   |
|  2 | bar   |
|  3 | baz   |
+----+-------+
CREATE USER alice@'10.211.%' IDENTIFIED BY 'horsebatterystaple';
GRANT ALL PRIVILEGES ON mysql_example.* TO alice@'10.211.%';

• Back in the OpenStack UI:
  • 'Edit Security Groups' for your VM
  • Click the + next to 'er_mysql_from_hpc'
• On your VM instance:
  • sudo vim /etc/mysql/mariadb.conf.d/50-server.cnf
  • Change the variable bind-address = 0.0.0.0
• On CREATE HPC:
  • module load py-mysql-connector-python
  • You can then use python code within your submissions to interact with your database, e.g.:

import mysql.connector

db = mysql.connector.connect(
    host="10.211.X.Y",
    user="alice",
    password="horsebatterystaple",
    database="mysql_example"
)

cursor = db.cursor()

cursor.execute("SELECT * FROM stuff")

results = cursor.fetchall()

for result in results:
    print(result)