Introduction:

After having tested Jira and decided to keep it for production it is very recommended to change the type of database used by Jira. The default database at delivery time is H2(local file dB) and in this HOW-TO I describe what I had to do to execute that switch under Debian Jessie.

Steps:

References:
https://confluence.atlassian.com/jira062/switching-databases-588581557.html
https://confluence.atlassian.com/adminjiraserver072/connecting-jira-applications-to-mysql-828787562.html
https://confluence.atlassian.com/jira060/connecting-jira-to-mysql-370705252.html

Backup database:
(SprocketWheelIcon)==>>System ==>>(Left menu)Backup System ==>>Filename: HP_JIRA_Backup_1.zip
Results:
eg. /var/atlassian/application-data/jira/export/HP_JIRA_Backup_1.zip

Create the new Database in MySQL:
Follow theses instructions:
1) Connect to mysql as root:
mysql -p -u root
PW: ******
2) Create the DB, user and user access rights:
CREATE DATABASE jiradb CHARACTER SET utf8 COLLATE utf8_bin;
GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on jiradb.* TO 'jiradbuser'@'localhost' IDENTIFIED BY '<DBpassword>';
flush privileges;
3) Tip: To confirm if the permissions were granted successfully, log into the DB server with the JIRA DB user and run the command below:
SHOW GRANTS FOR 'jiradbuser'@'localhost';
4) Quit Mysql:
quit;
Stop Mysql server and Jira:
service mysql stop
service jira stop
Delete the innoDB log files
IMPORTANT!! Mke sure you have no other application that uses innoDB format for its databases otherwise that deletion might do data corruption.
rm /var/lib/mysql/ib_logfile?
Edit /etc/mysql/my.cnf and add the following lines:
FOR Mysql 5.5 and below
[mysqld]
...
default_storage_engine=InnoDB
max_allowed_packet=256M
innodb_log_file_size=256M
FOR Mysql 5.6
[mysqld]
...
default_storage_engine=InnoDB
max_allowed_packet=256M
innodb_log_file_size=2G
Remove this if it exists
sql_mode = NO_AUTO_VALUE_ON_ZERO
Start MySQL server:
service mysql start
Install the MySQL JDBC driver to Jira drivers directory
cd /tmp
wget http://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.40.tar.gz
tar fvxz mysql-connector-java-5.1.40.tar.gz
cp mysql-connector-java-5.1.40/mysql-connector-java-5.1.40-bin.jar /opt/atlassian/jira/lib/
# Delete the not needed uncompressed directory
rm -rf mysql-connector-java-5.1.40
Connecting Jira to MySQL database:
Rename the dbconfig.xml file as follows:
Note: this change of filename will force Jira to start the Setup Wizzard if it doesn’t find this file.
mv /var/atlassian/application-data/jira/dbconfig.xml /var/atlassian/application-data/jira/dbconfig.xml.H2
Restart Jira:
Note: It is normally a long process that might take up to a minute before Jira can really be ready to be used.
service jira stop && service jira start && tail -f /var/atlassian/application-data/jira/log/atlassian-jira.log
Watch for errors(like ‘exception…’
When the logs are showing something like below then Jira is ready to be used to continue the migration of the database.
---------------------------------------------------------------------------------
Heap memory : Used: 196 MiB. Committed: 482 MiB. Max: 733 MiB
Non-heap memory : Used: 57 MiB. Committed: 59 MiB. Max: 1264 MiB
---------------------------------------------------------------------------------
TOTAL : Used: 253 MiB. Committed: 541 MiB. Max: 1997 MiB
---------------------------------------------------------------------------------
Connecting Jira to the MySQL database:
– Using the browser go to this Jira site and you will be presented with the Jira setup wizzard.
– Select the Manual Setup
– At database Setup page select: My Own Database and fill in the blanks
– Click on Test Connection to verify the validity of the information
– If all ok, click on Continue button.
– Enter the Company name and Select Private,and give the URL in the ‘Set up application properties’ page
– Select I have a Jira Key and paste the key in the field below the Server ID
– And fill in the following pages etc.

Migrating the database
Importing from older H2 data saved in xml backup (.zip)file
In Terminal:
Move the backup file to the import directory:
mv /var/atlassian/application-data/jira/export/HP_JIRA_Backup_1.zip /var/atlassian/application-data/jira/import/HP_JIRA_Backup_1.zip
In Jira site:
(SprocketWheelIcon)==>> System ==>> (Left menu)Restore System
Enter the filename(without path) of the backup(including the .zip extension)
Click Restore button.
IMPORTANT NOTE: This operation will overwrite all settings(except for the Database connection) which you already entered in the previous Setup Wizzard like your password/user/email/Language etc.. Therefore if this information are not exactly the same, you will have to logout and login again.

The Portfolio for Jira licence might have to be renewed at:
https://www.atlassian.com/purchase/cart

In order to correct the error, that will appear if the FLAG pool-test-while-idle is not set in the file dbconfig.xml, edit the file:
/var/atlassian/application-data/jira/dbconfig.xml and add the following line inside the settings block as follows:
<pool-test-while-idle>true</pool-test-while-idle>
eg.
<jdbc-datasource>
.....
<pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
<pool-test-while-idle>true</pool-test-while-idle>
<pool-test-on-borrow>false</pool-test-on-borrow>
.....
</jdbc-datasource>

Reason: The Set-up wizzard did not set it(inexistant) during its setting up the Database settings and the interface was complaining that it failed a database connection test. I had to make that change manually later and restart Jira.
Restart Jira and watch for errors.
service jira stop && service jira start && tail -f /var/atlassian/application-data/jira/log/atlassian-jira.log

Verifying the logs for errors:

You can check for errors via the Jira interface Log analyzer function in:
(SprocketWheelIcon)==>>System ==>>(Left menu)Support Tools==>>Log analyzer(TAB)==>>Refresh(middle right)