Leave a comment at the end of this page or email contact@krishagni.com
Database Configuration
Please ensure that the database user used to deploy Open Specimen has the following privileges. Failing to grant any of the mentioned privileges will result in deployment failure. The attribute username on this page defines the username of the database and not the login name used to log on to Open Specimen.
MySQL
Port
Default port: 3306
If MySQL is on a different server then you also need to open the MySQL port for communicating with OpenSpecimen
Privileges
Either the user should be a MySQL root user or have the following privileges
- ALTER
- ALTER ROUTINE
- CREATE
- CREATE ROUTINE
- CREATE VIEW
- DELETE
- DROP
- EXECUTE
- INDEX
- INSERT
- LOCK TABLES
- SELECT
- TRIGGER
- UPDATE
- REFERENCES
SQL:
GRANT ALTER, ALTER ROUTINE, CREATE, CREATE ROUTINE, CREATE VIEW, DELETE, DROP, EXECUTE, INDEX, INSERT, LOCK TABLES, SELECT, TRIGGER, UPDATE, REFERENCES ON <DATABASE-NAME>.* TO <USERNAME>
Setting INNODB
Please set the property default-storage-engine=INNODB. After the changes made, the Mysql server restart is needed.
This property is available in my.ini file in case of Windows and my.cnf in the case of Linux.
Backup
In the case of an upgrade, take a manual backup of the database, this will create a backup dump on the database server machine.
mysqldump -u<user> -p<password> --single-transaction --skip-lock-tables --routines database name | gzip > OPENSPECIMEN_`date +\%d-\%m-\%Y`.SQL.gz
Oracle
Port
Default port: 1521
If Oracle is on different servers, then you also need to open the Oracle port for communicating with OpenSpecimen.
Privileges
Either the user should be an Oracle sysdba user or have the following privileges
- CONNECT
- RESOURCE
- CREATE ANY TABLE
- DROP ANY TABLE
- CREATE ANY TRIGGER
- DROP ANY TRIGGER
- CREATE ANY VIEW
- DROP ANY VIEW
- CREATE ANY PROCEDURE
- DROP ANY PROCEDURE
- CREATE ANY SEQUENCE
- DROP ANY SEQUENCE
The use of ANY allows user to perform the operation in any schema. Droping ANY will restrict users to perform the aforementioned operations only within their own schema.
SQL:
GRANT CONNECT, RESOURCE, CREATE ANY TABLE, DROP ANY TABLE, CREATE ANY TRIGGER, DROP ANY TRIGGER, CREATE ANY VIEW, DROP ANY VIEW, CREATE ANY PROCEDURE, DROP ANY PROCEDURE, CREATE ANY SEQUENCE, DROP ANY SEQUENCE TO <
USERNAME
>
Note: If you are using the ORACLE database, it is mandatory to install the Oracle client on the machine that is hosting the Tomcat/JBoss server. Ensure that the system variable |
Backup
In case of an upgrade, take the manual of the database backup, this will create a backup dump on the database server machine.
expdp <user name>/<password here>@<database name> schemas=<user name> DIRECTORY=DATA_PUMP_DIR DUMPFILE=<filename> flashback_time=systimestamp
Import the dump and check the table count and records count per table, should match with original DB.
Best practices to follow
Set a strong password (the combination of alphanumeric and special characters) for the database user.
MySQL
- Do not use root user for connection from OpenSpecimen.
- Create a separate user for the database for OpenSpecimen and provide access to this database only. Use below command:
GRANT ALL PRIVILEGES ON <DataBase name>.* To '<user>'@'<db host>' IDENTIFIED BY '<password>'; Note: DB host could be localhost or actual server name or IP. Specify '%' to give access for any host.
Oracle:
- Create and assign separate tablespace to the OpenSpecimen user.
Leave a comment at the end of this page or email contact@krishagni.com