# MySQL server running as user on Bianca
https://github.com/datacharmer/dbdeployer

## Collect the files necessary to make the initial setup.
> On a machine with Internet connection

# Download dbdeployer
$ wget https://github.com/datacharmer/dbdeployer/releases/download/v1.69.1/dbdeployer-1.69.1.linux.tar.gz

# untar
$ tar -xvf dbdeployer-1.69.1.linux.tar.gz

# download latest mysql server binary
$ ./dbdeployer-1.69.1.linux init

# list available versions
$ ./dbdeployer-1.69.1.linux downloads list

# or get specific version
$ ./dbdeployer-1.69.1.linux downloads get mysql-8.0.28-linux-glibc2.17-x86_64-minimal.tar.xz

## Installation on Bianca
> Bring `bdeployer-1.69.1.linux.tar.gz` and the `mysql-...tar.xz` binary archive to Bianca (via sftp or transit).

# Untar the dbdeployer
$ tar -xvf dbdeployer-1.69.1.linux.tar.gz

# Install the mysql binary in a sandbox
$ dbdeployer-1.69.1.linux unpack mysql-8.0.30-linux-glibc2.17-x86_64-minimal.tar.xz

## Initial setup - example

$ dbdeployer deploy single 8.0
# 8.0 => 8.0.30
Database installed in $HOME/sandboxes/msb_8_0_30
run 'dbdeployer usage single' for basic instructions'
........ sandbox server started



# Check the server
$ dbdeployer-1.69.1.linux sandboxes --full-info
.------------.--------.---------.-----------.---------------.--------.-------.--------.---------------------------.
|    name    |  type  | version |   host    |     port      | flavor | nodes | locked |          created          |
+------------+--------+---------+-----------+---------------+--------+-------+--------+---------------------------+
| msb_8_0_30 | single | 8.0.30  | 127.0.0.1 | [8030 18030 ] | mysql  |     0 |        | 2022-09-05T14:36:27+02:00 |
'------------'--------'---------'-----------'---------------'--------'-------'--------'---------------------------'

## User setup
https://github.com/datacharmer/dbdeployer/wiki/database-users

## Test
> The system `mysql` client is a bit old on Bianca (or incompatible), so you need to use the one from the package...
> Default password for user `msandbox` is `msandbox`.
> You can use the dbdeployer to "use" the database directly `./dbdeployer-1.69.1.linux use`

$ ~/opt/mysql/8.0.30/bin/mysql --protocol=tcp --port=8030 -u msandbox -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 8.0.30 MySQL Community Server - GPL
...

mysql> CREATE DATABASE cromwell;
Query OK, 1 row affected (0.39 sec)

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| cromwell           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.01 sec)

## Start, stop, restart, etc...

$ ~/sandboxes/msb_8_0_30/status 
msb_8_0_30 on

$ ~/sandboxes/msb_8_0_30/stop 
stop /home/user/sandboxes/msb_8_0_30

$ ~/sandboxes/msb_8_0_30/start 
............ sandbox server started

$ ~/sandboxes/msb_8_0_30/show_log

$ ~/sandboxes/msb_8_0_30/test_sb 
ok - version '8.0.30'
ok - port detected: 8030
ok - version is 8.0.30 as expected
ok - port is 8030 as expected
ok - query was successful for user msandbox_ro: 'select 1'
ok - query was successful for user msandbox_rw: 'select 1'
ok - query was successful for user msandbox_ro: 'use mysql; select count(*) from information_schema.tables where table_schema=schema()'
ok - query was successful for user msandbox_rw: 'use mysql; select count(*) from information_schema.tables where table_schema=schema()'
ok - query failed as expected for user msandbox_ro: 'create table if not exists test.txyz(i int)'
ok - query was successful for user msandbox_rw: 'create table if not exists test.txyz(i int)'
ok - query was successful for user msandbox_rw: 'drop table if exists test.txyz'
# Tests :    11
# PASS  :    11 
# fail  :     0
...

## Install MariaDB flavor
- Download an appropriate for your architecture archive from [https://mariadb.org/download](https://mariadb.org/download).

# for example
$ wget https://mirror.one.com/mariadb/mariadb-10.9.2/bintar-linux-systemd-x86_64/mariadb-10.9.2-linux-systemd-x86_64.tar.gz

- Transfer the archive to Bianca
- unpack the archive

$ dbdeployer-1.69.1.linux unpack mariadb-10.9.2-linux-systemd-x86_64.tar.gz 
Unpacking tarball mariadb-10.9.2-linux-systemd-x86_64.tar.gz to $HOME/opt/mysql/10.9.2
.........100.........200.........300.........400.........500.........600.........700.........800.........900.........1000.........1100.........1200.........1300.........1400.........1500.........1600.........1700.........1800.........1900.........2000.........2100.........2200.........2300.........2400.........2500.........2600.........2700.........2800.........2900.........3000.........3100.........3200.........3300.........3400.........3500.........3600.........3700.........3800.........3900.........4000.........4100.........4200.........4300.........4400.........4500.........4600.........4700.........4800.........4900.........5000.........5100.........5200.........5300.........5400.........5500.........5600.........5700.........5800.........5900.........6000.........6100.........6200.........6300.........6400.........6500.........6600.........6700.........6800.........6900.........7000.........7100.........7200.........7300.........7400.........7500.........7600.........7700.........7800.........7900.........8000.........8100.........8200.........8300.........8400.........8500.........8600.........8700.........8800.........8900.........9000.........9100.........9200.........9300.........9400.........9500.........9600.........9700.........9800.........9900.........10000.........10100.........10200.........10300.........10400.........10500.........10600.........10700.........10800.........10900.........11000.........11100.........11200.........11300.........11400.........11500.........11600.........11700.........11800.........11900.........12000.........12100.........12200.........12300.........12400.........12500.........12600.........12700.........12800.........12900.........13000.........13100.........13200.........13300.........13400.........13500.........13600.........13700.........13800.........13900.........14000.........14100.........14200.........14300.........14400.........14500.........14600.........14700.........14800.........14900.........15000.........15100.........15200.........15300.........15400.........15500.........15600.........15700.........15800.........15900.........16000.........16100.........16200.........16300.........16400.........16500.........16600.........16700.........16800.........16900.........17000.........17100.........17200.........17300.........17400.........17500.........17600.........17700.........17800.........17900.........18000.........18100.........18200.........18300.........18400.........18500.........18600.........18700.........18800.........18900.........19000.........19100.........19200.........19300.........19400.........19500.........19600.........19700.........19800.........19900.........20000.........20100.........20200.........20300...20337
Renaming directory /home/user/opt/mysql/mariadb-10.9.2-linux-systemd-x86_64 to /home/user/opt/mysql/10.9.2

- Deploy single server

$ ./dbdeployer-1.69.1.linux deploy single 10.9.2
Database installed in $HOME/sandboxes/msb_10_9_2
run 'dbdeployer usage single' for basic instructions'
. sandbox server started

- test

$ ./dbdeployer-1.69.1.linux use
running /home/ubuntu/sandboxes/msb_10_9_2/ use
/home/ubuntu/sandboxes/msb_10_9_2/use
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 10.9.2-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost:10902] {msandbox} ((none)) > CREATE DATABASE cromwell;
Query OK, 1 row affected (0.001 sec)

mysql [localhost:10902] {msandbox} ((none)) > show databases;
+--------------------+
| Database           |
+--------------------+
| cromwell           |
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
6 rows in set (0.001 sec)

## Expose the server to other nodes
Add `--bind-address=0.0.0.0` option during deployment

$ dbdeployer-1.69.1.linux deploy single 10.9.2 --bind-address=0.0.0.0
Database installed in $HOME/sandboxes/msb_10_9_2
run 'dbdeployer usage single' for basic instructions'
... sandbox server started

$ dbdeployer-1.69.1.linux sandboxes --full-info
.------------.--------.---------.-----------.----------.---------.-------.--------.---------------------------.
|    name    |  type  | version |   host    |   port   | flavor  | nodes | locked |          created          |
+------------+--------+---------+-----------+----------+---------+-------+--------+---------------------------+
| msb_10_9_2 | single | 10.9.2  | 127.0.0.1 | [10902 ] | mariadb |     0 |        | 2022-09-06T08:13:30+02:00 |
'------------'--------'---------'-----------'----------'---------'-------'--------'---------------------------'

- check that the server is properly exposed

# with --bind-address=0.0.0.0
$ lsof -i 
COMMAND    PID   USER   FD   TYPE   DEVICE SIZE/OFF NODE NAME
mariadbd 17594   user    20u  IPv4 11385754      0t0  TCP *:10902 (LISTEN)

# with defauld localhost binding
$ lsof -i 
COMMAND   PID   USER   FD   TYPE DEVICE SIZE/OFF NODE NAME
mariadbd 5755  user   20u  IPv4  49436      0t0  TCP localhost:10902 (LISTEN)

# Contacts:
- [Pavlin Mitev](https://katalog.uu.se/profile/?id=N3-1425)
- [UPPMAX](https://www.uppmax.uu.se/)
- [SNIC AE@UPPMAX - related documentation](/8sqXISVRRquPDSw9o1DizQ)


###### tags: `UPPMAX`, `SNIC`