wiki:US3DatabaseInstantiation
close Warning:

US3 Database and Stored Procedure Instantiation

This page describes how to set up a new instance of the US3 Database.

PLEASE NOTE:

If you are a user of UltraScan III and want to set up a new US3/LIMS3 database instance for your institutions, all you need to do is request it on this page: http://www.uslims3.uthscsa.edu/uslims3_newlims/request_new_instance.php.

If you are an administrator who wishes to set up your own private database instance, follow the instructions on this page.

In the case of the US3 database there are two components that need to be set up, the database definition itself, and the stored procedures. If your objective is to learn more about how to use the stored procedures, please see the MySQL Stored Procedure API.

Prerequisites

  • Source files retrieved using instructions at Subversion Info For Ultrascan3. The database instantiation files you need are all in the sql subdirectory under the directory that subversion creates.
  • A mysql database and access to a login account with administrative rights, such as the root account.

Procedures

First, create a database and grant the US3 user access to it:

$ cd ultrascan3/sql
$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 15348
Server version: 5.1.41-community-log MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE DATABASE new_us3;
Query OK, 1 row affected (0.00 sec)

mysql> GRANT EXECUTE ON new_us3.* TO new_us3user@'%' IDENTIFIED BY 'new_password' REQUIRE SSL;
Query OK, 0 rows affected (0.01 sec)

mysql>

Please note the REQUIRE SSL clause of the GRANT EXECUTE statement. This will be explained below. For now, return to the command line and load the database definition into the new database:

mysql> exit
Bye
$ mysql -u root -p new_us3 <us3.sql
Enter password:
$               

Next, load the stored procedures. Note that although you will only execute one program, all the remaining files will be sourced:

$ mysql -u root -p new_us3 <us3_procedures.sql
Enter password:
$    

Finally, let's get back to the REQUIRE SSL clause. The easiest way to communicate with your newly-instantiated database would be simply to omit this clause when setting up your database user, making do without it. Communication would occur, but all data would be transmitted between the UltraScan user and the database in clear text. The REQUIRE SSL clause establishes a MySQL option which requires the UltraScan user to communicate with your database using a secure connection. This is very advantageous, in that all data will be transmitted over the network through a secure tunnel, protecting the security of the user's private data.

To function, the REQUIRE SSL clause requires an SSL public/private certificate pair. When communicating with one of our servers this is already set up using the public certificate ca-cert.pem, found in the etc/ subdirectory of the UltraScan program files. If users wish to communicate over SSL with your server, this public certificate on the users' systems will need to be replaced with your own public certificate. The private certificate goes on the server. Add the following lines to my.cnf and restart the server:

ssl
ssl-ca     = /path/to/ca-cert.pem
ssl-key    = /path/to/server-key.pem
ssl-cert   = /path/to/server-cert.pem
ssl-cipher = AES128-SHA

Your new database should be ready to go. The next step is to create LIMS instance following these instructions and then create a user account in LIMS.

Notes

  • The cd ultrascan3/sql command changes the current directory to the sql subdirectory under the parent directory where the UltraScan III files are located. Change as appropriate for your system.
  • Be sure to GRANT EXECUTE only, and not to add other permissions. The US3 user is required to access the database through the stored routines mentioned earlier.
  • REQUIRE SSL is required to pass information back and forth in a secure tunnel.
  • Consider increasing the max_allowed_packet variable in the MySQL my.cnf configuration file. 128M ought to be large enough. This configures the mysqld daemon, however in some instances you may find that you need to pass a similar parameter to the mysql command-line client to handle both sides of the transaction.
  • Consider increasing the memory_limit variable in the PHP php.ini configuration file. 128M should work for a production server, or you could wait and figure it out by trial-and-error.
Last modified 6 years ago Last modified on Jan 24, 2012 7:43:16 PM