MySQL and File Archive Strategies for MOOT

Definitions

The word database is used in the MySQL sense. It consists of a collection of tables which may reference each other via foreign keys. User accounts may be granted access privileges per database (or even per table, but this shouldn't be necessary).

account and username in this context mean MySQL account and username. They have nothing to do with operating system accounts and usernames.

Database locations

A single node may have up to three databases. Names come from the set mood_test, mood_user, mood. mood is the production database. mood_user is for testing and development by MOOT users. mood_test is for developers of MOOT itself.

Ultimately — by the time the system is released for production use if not before — some SLAC node (an Offline node like glastlnxXX ?) will be master for all three. Other nodes may be mirrors for one or more. Mobile rack will be mirror for production dbs mood, but need not mirror the others. Other databases will be mirrored only if it is too much trouble to exclude them.

Replication of db

Slaves will be "pure": users will not write to any slave databases. Such databases will receive updates only via MySQL replication. This can be enforced by granting only select privilege to user accounts on the slave.

In order to set up slave replication, use procedure laid out in MySQL replication how-to, keeping in mind that

• MOOT databases consist of InnoDB tables
• Do not want to replicate mysql database, which contains user account information, since accounts on slave will be configured somewhat differently, and might not want to replicate all of the mood-like databases existing on master.
• Certainly for Mobile Rack slave, perhaps for all slaves, will need ssh tunnel.

Main points in the procedure as documented in the MySQL manual are:

• Pay attention to contents of default files on both master and slave. Need binary logging on master, unique server-id for all servers involved, etc.
• For our case (pure master, pure slave) can take snapshot — can be binary or via mysqldump — then restart or unpause master. Starting up slave for the first time can take place asynchronously as long as it's afterwards, and not so long afterwords that master has purged binary log the slave will refer to.
• Must save log checkpoint information — filename of log and position — since this information is fed back to slave in CHANGE MASTER TO statement.

File archives and their replication

The master copy of each mood-like database has associated with it a disk file archive, containing Parameter files, FSW input files and Offline Calibration files. These files may only be written by users with update and insert privilege for the associated database; however the typical user with read access to the MySQL database will also need read access to the files. [Those who only want to browse the database interactively and don't need access to the files can use the rdbBrowse program.] In fact, in order to use MOOT services, a user must have defined an environment variable, MOOT_ARCHIVE, which identifies the location of the file archive. From the archive MOOT can determine which is the correct database to connect to and whether write access is allowed. Each slave db supporting MOOT read services must have an associated mirror of the archive. Someone connecting to a slave rather than the master normally is doing so because the master is unreachable or performance is poor, and in this case the master file archive is normally also unreachable.

Files are written to a (user-writable, i.e. master) archive via MOOT, on behalf of the user, as part of the registration process. Ideally, files should be copied to slave archives before the slave database is modified to refer to them, but so far I have no idea how to do this. In practice files will probably be written only sporadically and in bunches, and readers will tend not to need access to newly written files soon after they are registered, so some simple mechanism with no guarantee of proper ordering of operations may be good enough.

MySQL Accounts

1. For each database server, slave or master, there must be a superuser-type account which can create other accounts. Such accounts need only be enabled for localhost.
2. Each database, slave or master, should have one or more MOOT-developer accounts (not to be shared among multiple developers) which can do anything with the tables, including deleting them and creating new ones, but cannot start and stop the server. TBD: whether such accounts can grant privileges to other users. These accounts may be enabled for remote access from some nodes.
3. The mood and mood_user master databases will have one or more MOOT-user accounts (not to be shared) for people who need to create or modify entries. Such accounts will have update and insert in addition to select but will not be able to delete rows or tables nor to create tables. These accounts may be enabled for remote access from SLAC nodes.
4. All databases will have an account with username glastreader, available from any host, with read-only (select) access.
5. Any MySQL installation including the master copy of a database to be replicated must have a replication account for use of replicating slave servers.