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:

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
...

• enable MYSQL history in mlogger:
  • change ODB /Logger/History/MYSQL/Active to "y", "mysql writer" and "mysql reader" to location of these files (leave the default values if these files are in the experiment directory).
• enable MYSQL history in mhttpd:
  • change ODB /History/LoggerHistoryChannel to "MYSQL"
• restart mlogger, restart mhttpd

...........