changed 3 years ago  
Published Linked with GitHub

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

# 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:


tags: UPPMAX, SNIC
Select a repo