Quick JIRA install with MySQL backend

Things are much better than the bad early JIRA 4 days of doing a tomcat installation, fixing memory leaks, and tweaking JVM settings. But having a robust MySQL backend on a separate system is still needed, especially with a SQL-query intense GreenHopper add-on.

This installation assumes CentOS/Redhat type platforms with no GUI overhead.

MySQL 5.5 is easier than 5.0

MySQL 5.5 already uses innodb as the default engine. Binary row locking is also supported. Both of these are needed on JIRA. MySQL 5.5 also takes more advantage of multicore systems, if you have one.

Create the JIRA database:

mysql> create database jiradb character set utf8;

Create the JIRA db service account and grant privileges with one command:

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on
-> jiradb.* TO 'jirauser'@'localhost' IDENTIFIED BY '<password>';
mysql> flush privileges;

Generally, the MySQL backend is a separate server, so permissions to both the service account and the JIRA system need to be granted:

mysql> update user set Host='%' where user='jirauser';
mysql> update db set host=’<JIRA server> where db='jiradb';

Test the connection from the JIRA server to ensure everything is setup correctly, otherwise, JIRA will not launch cleanly:

# mysql –h <MySQL Server> -u jirauser -p jiradb
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4526

JIRA standalone

Download the JIRA standalone file and “install” it by extracting to your data partition.

1. Build out the JIRA environment

Download the latest JAVA JRE from Sun Oracle. For CentOS/Redhat, this is best done with the rpm binary. Do NOT use the yum install java. JIRA does not play nice with openjdk. Uninstall it if it is there.
Set JAVA_HOME as needed, for example in /etc/environment:

JAVA_HOME=/usr/java/latest

Download the latest mysql-connector-java-5.x.x-bin.jar and put it in the lib folder of the installation. If there is an older connector already there, rename to something like mysql-connector-java-5.x.x-bin.jar.OLD.

Create the JIRA home directory (different from the JIRA installation directory). Adjust the jira-application.properties file to reflect JIRA home.

If needed for security, create a JIRA service account (different from the MySQL JIRA service account) and set permissions so that use can access the JIRA installation and home directories:

/usr/sbin/useradd --create-home --home-dir /usr/local/jira --shell /bin/bash jira

2. Connect JIRA to the database

The database connection properties need to be adjusted in two locations. The conf/server.xml must be adjusted:

com.mysql.jdbc.Driver
driverClassName="com.mysql.jdbc.Driver"
url="jdbc:mysql://<MySQL_Server>/jiradb?useUnicode=true&amp;characterEncoding=UTF8"

Because of potential connectivity issues, add this line to configuration file:

validationQuery="select 1"

And the atlassian-jira/WEB-INF/classes/entityengine.xml needs a change as well:

<datasource name="defaultDS" field-type-name="mysql"

Delete the schema-name="PUBLIC" line.

3. Enable SSL on JIRA (recommended)

To avoid passwords going out in the clear, SSL-ize JIRA. This is done easily and quickly with self-generated certificates. However, paid, officially recognized certificates will work as well.

To self-generate certificates, run the command:

$JAVA_HOME/bin/keytool -genkey -alias JIRA_server -keyalg RSA

and follow the prompts. “Your name” is actually the fully qualified domain name of the server. Remember the keystore password that is set so JIRA (tomcat) can access the keystore.

In conf/server.xml, uncomment the  <Connector port="8443"…/> section.  If the keystore password is not the JAVA default of ‘changeit’, then add it to the connector:

keystorePass="<password>"

Also, if the keystore is not the JAVA default (either $HOME/.keystore or $JAVA_HOME/lib/security/cacerts, this needs to be added to the connector.

Starting JIRA

JIRA should now be ready for startup. Be sure to tail –f logs/catalina.out and watch for errors during startup.

SSL errors

If using a self-generated certificate, plugins and IDEs will not trust the certificate that JIRA is using for encryption.

The error, however, will likely be cryptic:

Failed to read servers response: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

To remedy this, the client must have the certificate added to their JAVA keystore.

On the server, export the public certificate and make it generally available:

$JAVA_HOME/bin/keytool -export –alias <JIRA_Server_Name> -rfc -file jira_server.crt

Then any client system can import it into their JAVA keystore. If it is a user, that is usually $HOME/.keystore. If it is a system, such as Fisheye or Confluence integrated into JIRA, it will be the global store:  $JAVA_HOME/lib/security/cacerts

The command is this:

$JAVA_HOME/bin/keytool -import –alias <JIRA_Server> -file jira_server.crt -keystore $JAVA_HOME/lib/security/cacerts

If a server service, the service will need to restarted before the trust will come into effect.