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

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

...........