[MySQL Database Setup Procedures] JobServer depends on a RDBMS to store and manage its critical data. This document will cover the basic steps needed to configure your database such that it is operational for Jobserver to use. However, this document is not intended to be a thorough discussion of MySQL database configuration, administration, or tuning. For example, you will need to insure that your MySQL database is configured properly to allow for remote jdbc clients, ...etc. Tuning your database will depend, in part, on how large you expect the database tables to grow and how many concurrent users and concurrently running jobs you expect JobServer to manage. Database Requirements: ---------------------- MySQL versions 4.x and higher are supported. JobServer will operate with either the standard MySQL storage engine or with the InnoDB storage engine. Since the InnoDB supports commit/rollback it offers better data integrity but at a performance cost. Instructions for setting up MySQL JobServer database: ----------------------------------------------------- 1) First, you will need to download and install the MySQL database driver MYSQL Connector/J 3.x or higher. The download can be found at http://www.mysql.com. Place the corresponding jdbc jar into the following directory "jobserver/database/drivers". Next, from the "database" directory run the utility "driverconfig" with the name of the mysql JAR file that you just placed in the directory above. For example, if the jdbc jar you placed is named "mysql-connector.jar" then you will run the command: > driverconfig.sh mysql-connector.jar (unix/linux) > driverconfig.bat mysql-connector.jar (windows) This will install the MySQL jdbc driver into the JobServer installation. Or you can also manually place the MYSQL driver JAR into the following directories: jobserver/sys/lib/jsthird jobserver/sys/app/webapps/jobserver/WEB-INF/lib 2) Now you are ready to configure your MySQL database server. Have your database administrator create a new database in which to place the JobServer tables. You may use an existing database so long as there is no conflict with the JobServer's table names. It is, however, recommended that you create a new database to avoid potential problems, but this is not required. Make note of the hostname, port number, and database instance name. If you will be running multiple JobServer installations, each must have its own database. Next, have your administrator create a new database user "jsuser" and note the password. You may choose a different name other than "jsuser" if you like. To create the JobServer tables you can either do it manually by applying the sql file "mysql-jobserver.sql", or you may use the "dbsetup" tool which walks you interactively through the process. In both cases you will need to have access to a username/password with administrative rights to create tables, indexes, and grant rights. i) dbsetup tool: If you choose to use the "dbsetup" tool, it will walk you through the remaining steps to create the JobServer tables. Run the command "dbsetup" found in the "jobserver/database" directory and follow its instructions as shown here: > dbsetup.sh (unix) > dbsetup.bat (windows) ii) Manually run script: Have your database administrator run the database script "jobserver/database/mysql-jobserver.sql" to create the tables for JobServer. Ideally you should run the script such that the tables are owned by the the user "jsuser". If you use a different username other than "jsuser", you will need to edit the "mysql-jobserver.sql" file and replace all occurrences of "jsuser" with the database username you chose instead. 3) Once the database setup has been completed, the JobServer installation will need to be able to reference the database. The following database properties will need to be defined in the Jobserver "jobserver/conf/system-config.properties" file: dbDriverType=MYSQL /* Identify that the database used by JobServer as MYSQL */ dbHost=localhost /* Hostname where database is installed */ dbName=somename /* Name of the database used */ dbPort=3306 /* Port number to use to connect to database via JDBC */ dbUsername=jsuser /* MYSQL user used by JobServer apps to access the database. */ dbPassword=changeme /* Password corresponding to jsuser username. */ 4) Configure the database properties discussed above in the "system-config.properties". Now with the database up and running, check if the database is properly configured and can be accessed by JobServer. Run the following command: > jobserver/bin/jsmonitor.sh (unix) > jobserver/bin/jsmonitor.bat (windows) This will tell you if JobServer can detect and connect to the database. If the database status is not shown as "Active" recheck the installation steps to make sure you have configured the database and JobServer properly. [Database Maintenance] Routine Maintenance ------------------- It is recommended that you follow and apply the maintenance procedures discussed in the MySql administration docs. These include: - Updating planner statistics - Log File Maintenance - Backups Table sizes and fragmentation should be monitored and kept optimal. The tables that grow most rapidly will need the most attention. These include the tables "js_wbstatus", "js_opstatus", and "js_taudit". Table Trimming -------------- The JobServer database tables should normally be trimmed on a periodic basis, to avoid accumulating excessive amounts of outdated historical data. To do this, create a job using the pre-built TaskBean called "JobServerCleanup" that comes with the installation. Create, configure, and schedule this Job. Run this job on a regular basis (for example weekly, monthly, or as needed). It will keep the database tables trimmed to your specifications by removing outdated historical records from the various database tables including outdated log files from the filesystem. Backup ------ Backup database as necessary. Incremental backup is recommended with periodic full backup.