Setup MySQL database for MIDAS: Difference between revisions

From MidasWiki
Jump to navigation Jump to search
No edit summary
(The midas documentation has moved to https://midas.triumf.ca/docs/)
Tag: Replaced
 
(24 intermediate revisions by 2 users not shown)
Line 1: Line 1:
This page provides instructions for setting up a MySQL database to use with the MIDAS history logging.
The midas documentation has moved. See [https://midas.triumf.ca/docs/develop/features/storing_data/history/configuring/#sql-databases SQL databases] in History: configuring for the new documentation about setting up a MySQL database for the history system.
 
Setting up the
 
== Setting up MySQL using mariadb on macbook ==
 
* install mariadb-10.4
<pre>
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
</pre>
* rebuild midas, check the cmake finds the correct mysql: MIDAS: Found MySQL version 10.4.13
* create $HOME/.my.cnf
<pre>
[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
</pre>
* mkdir $HOME/mysql
* start mysql:
<pre>
macos: /opt/local/lib/mariadb-10.4/bin/mysqld
</pre>
* configure the database: start the "mysql" utility (/opt/local/lib/mariadb-10.4/bin/mysql)
<pre>
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)
</pre>
* try connecting to both users:
<pre>
/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
</pre>
* create midas config files:
** mysql_writer.txt
<pre>
#server=localhost
#port=xxx
database=history
socket=/Users/olchansk/mysql/mysql.sock
user=history_writer
password=writer_password ### change this!!!
#buffer=1000
</pre>
** mysql_reader.txt
<pre>
#server=localhost
#port=xxx
database=history
socket=/Users/olchansk/mysql/mysql.sock
user=history_reader
password=reader_password ### change this!!!
</pre>
* test MIDAS connection
<pre>
mh2sql --mysql mysql_writer.txt
(there should be no errors printed)
</pre>
* if desired, import midas history from .hst files:
<pre>
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
...
</pre>
* 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
...........

Latest revision as of 22:01, 30 October 2026

The midas documentation has moved. See SQL databases in History: configuring for the new documentation about setting up a MySQL database for the history system.