---

COMPASS Online Database

Overview

The COMPASS online database has been migrated to the new hardware and software before the start of the 2010 Run. The database is powered by the MySQL software (version 5.1.45, source distribution) that is installed on two physical servers pccodb11 and pccodb12. The 64b Scientific Linux CERN 5 is used as the operating system on database servers. Two physical servers are synchronized by the master-master replication (pccodb11 is the master of the slave pccodb12 and at the same time, pccodb12 is the master of the slave pccodb11). On the third physical server pccodb10, the MySQL Proxy software is running. Currently, the proxy forwards all queries to one physical server (usually pccodb11), the other server is kept synchronized by the replication.

Proxy

In case a higher performance is needed in future, it is possible to add more replication slave and to start the MySQL Proxy in a load balancing mode. See the /usr/local/mysql-proxy/bin/start-proxy.sh for details. However, the load balancing mode has not yet been thoroughly test.

Connecting to the database service

The clients should ALWAYS connect to the database using the virtual address pccodb00 that points to the proxy server on pccodb10. The alias is set in /etc/rc.local in pccodb10 automatically after the system boot (you can set it manualy by running /sbin/ip addr add 172.22.24.122/16 dev eth0 as a root). The proxy is listening on the default MYSQL port 3306.

Backups

All databases are backed up regularly every day at 7:00, additionaly smaller databases are backed up every hour. The backups are created on the slave server (usually pccodb12) and stored in /data/mysql_dumps Furthermore, the binary log created during the replication can be used as an incremental backup. To dump the binary log into a plaintext with SQL commands, use the mysqlbinlog(1) tool. Finally, several databases are also replicated from the pccodb11 server to the compass02 server which runs at the CERN IT center. This can be regarded as a geographical backup. Also, this server is available from outside of the COMPASS network.

Additional services

Aside from the MySQL Proxy, the pccodb10 server also hosts additinal services. The most importantly, the web server with applications for the shift crew is installed there. This webserver manages the electronic logbook, the trigger rates displays, the frontend database, the database management tool phpMyAdmin, the web interfaces of Nagios and Ganglia, and other tools. Clients should use the address http://pccodb00 to access the server. The server data is stored in the /home/www/html directory on pccodb10.

The Nagios monitoring software also runs on the pccodb10. It checks state of database servers only (pccodb10, pccodb11, pccodb12). A communication between Nagios and remote hosts is based on the NRPE daemon. If Nagios detects a failure of one physical server, it automatically tries to reconfigure the MySQL proxy software to forward all traffic to the remaining server. The expert is also notified by an e-mail. The Nagios configuration is stored under the /usr/local/nagios/etc directory, the web interface is available at http://pccodb10.

-- VladimirJary - 2011-09-29

• COMPASS online database: