Setup MySQL database for MIDAS

This page provides instructions for setting up a MySQL database to use with the MIDAS history logging.

Setting up the MySQL database is highly dependent on what OS and OS version you are using. Most of the setup work is just MySQL related. For this reason we provide below instructions for the MySQL database setup on a couple different OS. You will probably need to modify these instructions for your OS. Here's the some installation instructions:

• Setting up MySQL on Ubuntu 24.04 LTS
• Setting up MySQL using mariadb on macbook

Setting up MySQL on Ubuntu 24.04 LTS

This instructions assume you have root privileges and install MySQL as ROOT.

Do these steps as root user

Install the MySQL server software.

apt install mysql-server

You will find mysql has started after installation

root@mpmt-daq03:/home1/wheel# service mysql status
mysql.service - MySQL Community Server
     Loaded: loaded (/usr/lib/systemd/system/mysql.service; enabled; preset: enabled)
     Active: active (running) since Fri 2025-04-25 15:12:58 PDT; 4s ago
    Process: 153129 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exited, status=0>
   Main PID: 153137 (mysqld)
     Status: "Server is operational"

Default configuration only allows MySQL connections to localhost. Good

root@mpmt-daq03:/home1/wheel# grep bind-add /etc/mysql/mysql.conf.d/mysqld.cnf
bind-address		= 127.0.0.1
mysqlx-bind-address	= 127.0.0.1

Login to MySQL as root and create the MySQL accounts

mysql -h localhost
...
mysql> CREATE DATABASE IF NOT EXISTS history;
mysql> CREATE USER history_reader@'localhost' IDENTIFIED BY 'CHANGE_THIS_PASSWORD!';
mysql> GRANT SELECT ON history.* TO history_reader@'localhost';
mysql> CREATE USER 'history_writer'@'localhost' IDENTIFIED BY 'CHANGE_THIS_PASSWORD!';
mysql> GRANT SELECT,INSERT,CREATE,ALTER,INDEX ON history.* TO 'history_writer'@'localhost';
mysql> flush privileges;

ATTENTION: change the account password listed above to something different!

Check the socket to connect to:

root@mpmt-daq03:/home1/wheel#  grep sock /etc/mysql/my.cnf 
# Port or socket location where to connect
socket = /run/mysqld/mysqld.sock

Do these steps as the experiment user

Rebuild MIDAS with support for MySQL. If this works correctly the build will notice MySQL is installed. For instance, with cmake we will see

-- MIDAS: Found MySQL version 8.0.41

Go to online directory and create files so MIDAS knows accounts to talk to ODB

testmysql@mpmt-daq03:~/online$ cd ~/online
testmysql@mpmt-daq03:~/online$ emacs -nw mysql_reader.txt
testmysql@mpmt-daq03:~/online$ emacs -nw mysql_writer.txt
testmysql@mpmt-daq03:~/online$ more mysql_*.txt
::::::::::::::
mysql_reader.txt
::::::::::::::
database=history
socket=/run/mysqld/mysqld.sock
user=history_reader
password=CHANGE_THIS_PASSWORD!
::::::::::::::
mysql_writer.txt
::::::::::::::
database=history
socket=/run/mysqld/mysqld.sock
user=history_writer
password=CHANGE_THIS_PASSWORD!

Again, set the correct password in these files.

Change the mlogger settings so we use the MySQL for the history logging:

• disable the previous history logger
• enable the MySQL history logging:

set "/Logger/History/MYSQL/Active" y 

Restart the mlogger on the command line and see that some MySQL tables are created and columns added:

testmysql@mpmt-daq03:~/online$ mlogger 
[Logger,INFO] Per-variable history is enabled
[Logger,ERROR] [history_schema.cxx:1258:Mysql::Prepare,ERROR] mysql_query(SELECT event_name, table_name, itimestamp FROM _history_index WHERE table_name!='';) error 1146 (Table 'history._history_index' doesn't exist)
[Logger,INFO] Adding SQL table "myperiodicequipment_pydt"
[Logger,INFO] Adding SQL table "_history_index"
[Logger,INFO] Adding column "event_name" to SQL table "_history_index", status 1
[Logger,INFO] Adding column "table_name" to SQL table "_history_index", status 1
...
[Logger,INFO] Adding column "fluxgate0" to SQL table "myperiodicequipment_pydt", status 1
[Logger,INFO] Adding column "fluxgate1" to SQL table "myperiodicequipment_pydt", status 1
[Logger,INFO] Adding column "valvex" to SQL table "myperiodicequipment_pydt", status 1
[Logger,INFO] Adding column "valvey" to SQL table "myperiodicequipment_pydt", status 1
[Logger,INFO] Adding column "t5" to SQL table "myperiodicequipment_pydt", status 1
...

Login directly to the MySQL database and make sure the data is actually being logged

mysql> select _i_time,fluxgate0 from myperiodicequipment_pydt where _i_time <1745620795;
+------------+-----------+
| _i_time    | fluxgate0 |
+------------+-----------+
| 1745620782 |   6.20209 |
| 1745620783 |    6.3519 |
| 1745620784 |    6.5677 |
| 1745620785 |   7.81413 |
| 1745620786 |   5.41127 |
| 1745620787 |   7.31112 |
| 1745620788 |   6.14285 |
| 1745620789 |   7.20425 |
| 1745620790 |    6.9303 |
| 1745620791 |   7.08178 |
| 1745620792 |   7.44422 |
| 1745620794 |   5.32677 |
+------------+-----------+
12 rows in set (0.00 sec)

Setting up MySQL using mariadb on macbook

• install mariadb-10.4

macos:
sudo port install mariadb-10.4
ln -s ln -fs /opt/local/lib/mariadb-10.4/bin/mysql_config ~/bin/
mysql_config --version
10.4.13

• rebuild midas, check the cmake finds the correct mysql: MIDAS: Found MySQL version 10.4.13
• create $HOME/.my.cnf

[mysqld]
#port=3306
socket=/Users/olchansk/mysql/mysql.sock
datadir=/Users/olchansk/mysql/db
pid-file=/Users/olchansk/mysql/mysqld.pid

[client]
#port=3306
socket=/Users/olchansk/mysql/mysql.sock

• mkdir $HOME/mysql
• start mysql:

macos: /opt/local/lib/mariadb-10.4/bin/mysqld

• configure the database: start the "mysql" utility (/opt/local/lib/mariadb-10.4/bin/mysql)

NOTE: instructions thanks to Ben S.
NOTE: use some other passwords instead of the example "reader_password" and "writer_password"

MariaDB [(none)]> CREATE DATABASE IF NOT EXISTS history;
Query OK, 1 row affected (0.003 sec)

MariaDB [(none)]> CREATE USER history_reader@'localhost' IDENTIFIED BY 'reader_password';
Query OK, 0 rows affected (0.027 sec)

MariaDB [(none)]> GRANT SELECT ON history.* TO history_reader@'localhost';
Query OK, 0 rows affected (0.016 sec)

MariaDB [(none)]> CREATE USER 'history_writer'@'localhost' IDENTIFIED BY 'writer_password';
Query OK, 0 rows affected (0.014 sec)

MariaDB [(none)]> GRANT SELECT,INSERT,CREATE,ALTER,INDEX ON history.* TO 'history_writer'@'localhost';
Query OK, 0 rows affected (0.017 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

• try connecting to both users:

/opt/local/lib/mariadb-10.4/bin/mysql -u history_reader -p ### should ask for reader password
/opt/local/lib/mariadb-10.4/bin/mysql -u history_writer -p ### should ask for writer password

• create midas config files:
  • mysql_writer.txt

#server=localhost
#port=xxx
database=history
socket=/Users/olchansk/mysql/mysql.sock
user=history_writer
password=writer_password ### change this!!!
#buffer=1000

• • mysql_reader.txt

#server=localhost
#port=xxx
database=history
socket=/Users/olchansk/mysql/mysql.sock
user=history_reader
password=reader_password ### change this!!!

• test MIDAS connection

mh2sql --mysql mysql_writer.txt
(there should be no errors printed)

• if desired, import midas history from .hst files:

mh2sql --mysql mysql_writer.txt *.hst
Reading 130813.hst
[mh2sql,ERROR] [history_schema.cxx:1165:Mysql::Prepare,ERROR] mysql_query(SELECT event_name, table_name, itimestamp FROM _history_index WHERE table_name!='';) error 1146 (Table 'history._history_index' doesn't exist)
[mh2sql,INFO] Adding SQL table "rpcexample", status 311
[mh2sql,INFO] Adding SQL table "rpcexample_1596127680", status 1
[mh2sql,INFO] Adding SQL table "_history_index", status 1
[mh2sql,INFO] Adding column "event_name" to SQL table "_history_index", status 1
...
[mh2sql,INFO] Adding SQL table "run_transitions", status 311
[mh2sql,INFO] Adding SQL table "run_transitions_1596127681", status 1
[mh2sql,INFO] Adding column "state" to SQL table "run_transitions_1596127681", status 1
[mh2sql,INFO] Adding column "run_number" to SQL table "run_transitions_1596127681", status 1
Reading 130815.hst