MySQL Setup - QuarkNet-HEP/cima-wzh Wiki

MySQL Setup

As always, DigitalOcean has a great guide on getting MySQL up and running.

MySQL maintains its own set of user accounts that are separate from those of your operating system. This includes a MySQL 'root' user, which is distinct from your Linux 'root' user, but which plays the same role as a "superuser" within the context of MySQL. If you've just installed MySQL, you should run the $ sudo mysql_secure_installation script to set the MySQL 'root' user's password, as described in the article above.

MySQL includes its own "shell" program you can access with the mysql command. Specifically, login as

$ mysql -u root -p

where -u root means "use mysql as the 'root' user", and -p means "prompt me for the user's password". When prompted, enter the password you set for the 'root' user when you ran mysql_secure_installation. You should see a MySQL shell prompt mysql > where you can type commands. (NB: On some systems you will have to use $ sudo mysql -u root -p, then enter your sudo password, then your MySQL 'root' password. I still haven't figured out why different systems are different about this).

Once you have MySQL installed with a 'root' user and password, you need to do five things to make it work with CIMA:

  1. Create a MySQL user for CIMA
  2. Create a MySQL database for CIMA
  3. Give the user permission to use the database
  4. Create the right schema within the database, either manually or by loading a backup
  5. Configure CIMA to use this MySQL installation

1) The CIMA MySQL user

Like the Linux 'root' user, the MySQL 'root' user has elevated permissions, so you shouldn't use it for everyday work. Instead, create an ad hoc user for CIMA to access the database with. We'll call it 'cimauser', but you can name it whatever you want to.

Login to your MySQL shell as 'root' as shown above (since root permissions are required to create a new user). What MySQL considers a "user" is really a combination of a username and a hostname, where the hostname is the location from which the user is attempting to access the MySQL server. If you're running both from the same machine, the host will be localhost; if your user is on a separate server (say, i2u2-prod.crc.nd.edu), you'll use that. We'll assume localhost for the sake of discussion, in which case what we'll be creating is a MySQL user 'cimauser'@'localhost'. Note that MySQL insists on the use of single quotes around each part, as shown.

Run this command to create the user:

mysql> CREATE USER 'cimauser'@'localhost' IDENTIFIED BY '{pw}';

where {pw} is the new user's password. This can be any strong password you like, just make sure you remember it.

2) The CIMA MySQL database

Now create the database. It's common for some names and locations that will be stored in it to use non-Latin characters, so you should keep the character set and collation options shown:

mysql> CREATE DATABASE `CIMA` CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

CIMA is the name of the database you're creating; like the MySQL username and password, this can be anything you like.

3) Database permissions

Give the new MySQL user permissions to use this database:

mysql> GRANT ALL ON CIMA.* TO 'cima'@'localhost';

GRANT ALL means "give all permissions," and CIMA.* means "all tables within the CIMA database." If you named your database something other than CIMA, you should use that name instead.

4) Database schema & data

CIMA does not require any initial data to function, it needs only the right set of tables and relations (the "schema") to write to. If you don't have access to a database backup for testing, you can create your own schema as shown in "Appendix A: CIMA Schema", below.

If you do have a backup dump file, say backup.sql, you can now upload it to the database you just created, which will automatically create the schema and fill its tables with whatever data was in the database used to create the dump. You'll do this from the Linux command line, not the mysql command line, so if you're still in the mysql shell, exit with mysql> exit; and run

$ mysql -u root -p CIMA < /path/to/backup.sql

where the angle bracket is used as an "arrow" to show that the data in backup.sql is being loaded into the CIMA database that we created above. The database must already exist when you run this command; it won't create one automatically. We're using the MySQL 'root' user to avoid permissions issues, since we only need to do this once. Again, if you named your database something other than CIMA, use that name instead.

5) CIMA configuration

During the process of setting up the MySQL database, you established four parameters:

  1. The hostname for the MySQL server
  2. The MySQL username for its CIMA user
  3. The password by which this user authenticates to MySQL
  4. The name of the CIMA database in MySQL

CIMA needs to know these four parameters in order to interact with the database. This is accomplished via the mc.config file which, for security, is never placed in a directory where Apache might serve it to a user.

Within the mc.config file, you should see a PHP function getDBConfig() that defines and returns an associative array of configuration parameters, $db_config, as in the sample file:

function getDBConfig(){
    $db_config = array(
        "db_host" => "{database_hostname}",
        "db_login" => "{database_user_login}",
        "db_pw" => "{database_user_password}",
        "db_name" => "{database_name}"
    );

    return $db_config;
}

For each parameter, replace the value on the right side of the => symbol with the appropriate value (keep the double quotes but not the curly braces). Save and close the file.

CIMA and MySQL should now be fully prepared to work with each other. If you get errors at any part of the process, do a web search for the error message or contact Joel.

Appendix A: CIMA Schema

Once the CIMA database is created with mysql> CREATE DATABASE CIMA CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;, the following commands will create the nine required tables.

This schema reflects the state of CIMA-WZH as of June 2022 and is highly likely to change with future development.

DROP TABLE IF EXISTS `Datasets`;
CREATE TABLE `Datasets` (
  `id` bigint unsigned NOT NULL AUTO_INCREMENT,
  `datagroup` smallint DEFAULT NULL,
  `dg_index` smallint DEFAULT NULL,
  `dataset` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=191 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `EventTables`;
CREATE TABLE `EventTables` (
  `id` int NOT NULL AUTO_INCREMENT,
  `MclassEventID` int NOT NULL,
  `tableid` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `EventTables_ibfk_1` (`MclassEventID`),
  KEY `EventTables_ibfk_2` (`tableid`),
  CONSTRAINT `EventTables_ibfk_1` FOREIGN KEY (`MclassEventID`) REFERENCES `MclassEvents` (`id`),
  CONSTRAINT `EventTables_ibfk_2` FOREIGN KEY (`tableid`) REFERENCES `Tables` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `Events`;
CREATE TABLE `Events` (
  `event_id` int NOT NULL,
  `datagroup_id` int NOT NULL,
  `g_index` int NOT NULL,
  `ev_no` int NOT NULL,
  `mass` double NOT NULL,
  `type` varchar(10) NOT NULL,
  PRIMARY KEY (`event_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `MclassEvents`;
CREATE TABLE `MclassEvents` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(30) NOT NULL,
  `active` tinyint NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `TableGroups`;
CREATE TABLE `TableGroups` (
  `id` int NOT NULL AUTO_INCREMENT,
  `tableid` int NOT NULL,
  `datagroup_id` int NOT NULL,
  `postAdded` tinyint NOT NULL,
  `dataset` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `TableGroups_ibfk_1` (`tableid`),
  CONSTRAINT `TableGroups_ibfk_1` FOREIGN KEY (`tableid`) REFERENCES `Tables` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=49 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `Tables`;
CREATE TABLE `Tables` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  `histogram_id` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  KEY `histogram_id` (`histogram_id`),
  CONSTRAINT `Tables_ibfk_1` FOREIGN KEY (`histogram_id`) REFERENCES `histograms` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `groupConnect`;
CREATE TABLE `groupConnect` (
  `gstd` int NOT NULL,
  `gbackup` int NOT NULL,
  `tableid` int NOT NULL,
  KEY `groupConnect_ibfk_1` (`tableid`),
  CONSTRAINT `groupConnect_ibfk_1` FOREIGN KEY (`tableid`) REFERENCES `Tables` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `histogram_types`;
CREATE TABLE `histogram_types` (
  `id` smallint unsigned NOT NULL AUTO_INCREMENT,
  `chart_type` varchar(15) NOT NULL,
  `x_min` smallint unsigned NOT NULL DEFAULT '1',
  `x_max` smallint unsigned NOT NULL,
  `bin` smallint unsigned NOT NULL DEFAULT '1',
  `canvas_id` varchar(15) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;


DROP TABLE IF EXISTS `histograms`;
CREATE TABLE `histograms` (
  `id` int NOT NULL AUTO_INCREMENT,
  `data` varchar(600) NOT NULL,
  `data_2l` varchar(600) NOT NULL DEFAULT '',
  `data_4l` varchar(600) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1;