Setup MySQL database for MIDAS: Difference between revisions

From MidasWiki
Jump to navigation Jump to search
(The midas documentation has moved to https://midas.triumf.ca/docs/)
Tag: Replaced
 
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 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 on Ubuntu 24.04 LTS ]]
* [[#Setting up MySQL using mariadb on macbook | 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. 
 
<pre>
apt install mysql-server
</pre>
 
You will find mysql has started after installation
 
<pre>
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"
</pre>
 
Default configuration only allows MySQL connections to localhost.  Good
<pre>
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
</pre>
 
Login to MySQL as root and create the MySQL accounts
 
<pre>
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;
</pre>
 
ATTENTION: change the account password listed above to something different!
 
Check the socket to connect to:
 
<pre>
root@mpmt-daq03:/home1/wheel#  grep sock /etc/mysql/my.cnf
# Port or socket location where to connect
socket = /run/mysqld/mysqld.sock
</pre>
 
=== 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
 
<pre>
-- MIDAS: Found MySQL version 8.0.41
</pre>
 
Go to online directory and create files so MIDAS knows accounts to talk to ODB
 
<pre>
 
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!
</pre>
 
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:
<pre>
set "/Logger/History/MYSQL/Active" y
</pre>
 
Restart the mlogger on the command line and see that some MySQL tables are created and columns added:
 
<pre>
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
...
</pre>
 
Login directly to the MySQL database and make sure the data is actually being logged
 
<pre>
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)
</pre>
 
Restart mhttpd and you will see the history plots starting to get data from MySQL database.
 
=== Change database location ===
 
To move the database to a different location on file do the following as root ([https://tecadmin.net/change-default-mysql-data-directory-in-linux/ source]):
<pre>
systemctl stop mysql
cp -a /var/lib/mysql /new/data/directory
</pre>
Then update the MySQL configuration file /etc/mysql/my.cnf
<pre>
[mysqld]
...
datadir=/new/data/directory
...
</pre>
You will need to either (a) adjust your apparmor settings, or (b) fully [https://daq00.triumf.ca/DaqWiki/index.php/Ubuntu#disable_apparmor disable apparmor].
Finally, restart the server:
<pre>
systemctl start mysql
</pre>
You can now delete the original database directory
<pre>
rm -rf /var/lib/mysql
</pre>
 
== 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>
 
** 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"

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.

Retrieved from "https://daq00.triumf.ca/MidasWiki/index.php?title=Setup_MySQL_database_for_MIDAS&oldid=3813"