How To Connect Jira to MySQL Easily: Step-by-Step Explanation

Are you looking to move data from Jira? MySQL is one of the most popular and used relational databases which provides a wide range of features that make storage and analysis of data easy, efficient, and secure. Hence, it is beneficial to you and your business to move your data from Jira to MySQL.

This blog will first provide a brief introduction to Jira and MySQL and then will discuss the two methods using which you can transfer your data. It will also discuss the limitations and benefits of these methods. Read along to decide which method suits you more!

Introduction to JIRA

Jira, created by Atlassian, is a powerful tool for work management, enabling teams to collaborate effectively. Initially a bug tracker, it now supports agile workflows, test case management, and custom workflows.

Key Features of Jira

• Custom Workflows: Design workflows tailored to your projects.
• Agile Support: Built-in tools for agile development and tracking.
• Issue Tracking: Simplifies bug and issue management.
• Collaboration Tools: Enhances team communication and coordination.
• Dashboards: Offers intuitive dashboards for real-time project insights.

Introduction to MySQL

MySQL is a widely-used relational database system, offering an easy-to-use client-server model and SQL for managing and processing data efficiently. The language, structured query language (SQL), enables users to perform any required CRUD operations (create, read, update, delete).

Key Features of MySQL

• High Performance: Optimized for speed and scalability.
• SQL Support: Perform CRUD operations effortlessly.
• Open Source: Free to use, with premium features from Oracle.
• Wide Adoption: Trusted by companies like Uber, Netflix, and Airbnb.
• Cross-Platform: Compatible with various operating systems and platforms.

Prerequisites

• A clear idea of what data you need to transfer.
• A Jira account.
• A MySQL account.
• Working knowledge of JDBC connector.

Methods to Set Up JIRA to MySQL Integration

Method 1: Using Hevo Data

Step 1.1: Configure Jira as Your Source

Step 1.2: Configure MySQL as Your Destination

Following these two simple steps, Hevo Data has successfully set up your Jira MySQL connection. It is as simple as that!

Method 2: Using JDBC Connector

Step 2.1: Create and Configure your MySQL Database

1. First, create a database user to connect to Jira, for example, jiradbuser.
   
2. Next, create a database that Jira will use to store issues, for example, jiradb.
   
   It is necessary for the database to have a character set of UTF8. To set it, you can use the following command from within the MySQL command client:

CREATE DATABASE jiradb CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

NOTE: Make a note of the database name, user name, and port number while creating your database, as you will require them later to connect it to Jira.

3. Ensure that the user has the required permissions to connect to the database and create and populate tables. These permissions can be granted by using the following commands:

• For MySQL 5.7.0 – 5.7.5:

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
flush privileges;

• For MySQL 5.7.6 and later (must also include the REFERENCES permission):

GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';
flush privileges;

4. Next, you have to edit my.cnf or my.ini (Windows) file in your MySQL server. For details on this process, you can read the MySQL Option Files.
5. The following parameters must be added and/or edited in the [mysqld] section in the file:

• A default storage engine is set to InnoDB:

[mysqld]
…
default-storage-engine=INNODB
…

• Initialize the character set that the database serve user:

[mysqld]
…
character_set_server=utf8mb4
…

• The default row format is set to DYNAMIC:

[mysqld]
...
innodb_default_row_format=DYNAMIC
...

• The large prefix must be enabled:

[mysqld]
...
innodb_large_prefix=ON
...

• The InnoDB file format is set to Barracuda:

[mysqld]
...
innodb_file_format=Barracuda
...

• The value of the innodb_log_file_size should be set to be at least 2G:

[mysqld]
...
innodb_log_file_size=2G
...

• Make sure that the sql_mode parameter does not specify NO_AUTO_VALUE_ON_ZERO

// remove this if it exists
sql_mode = NO_AUTO_VALUE_ON_ZERO

6. Your MySQL server must be rebooted for the changes to take effect.

Step 2.2: Copy the MySQL JDBC Driver

1. Download the suitable MySQL driver JDBC Connector/J 5.1 for your operating system.
   
2. Copy the driver to the directory given below:
   <Jira-installation-directory>/lib
   
   NOTE: If you have installed JIRA through the Windows installer, you have to do this step after running the Windows installer, but before running the setup wizard.
   
3. Restart the Jira service.
   
4. If you are installing Jira, you can access Jira in your browser to run the setup wizard.

To learn more about the Setup Wizard, take a look at the Confluence docs.

Step 2.3: Configure Jira to Connect to the Database

The following two ways can be used to configure your Jira server to connect to the MySQL database:

a. Setup Wizard

This method can be used if you have just installed JIRA, and are setting it up for the first time. The settings will be saved in the dbconfig.xml file in your Jira home directory.

You will see the Jira setup wizard when you access Jira for the first time in your browser.

1. On the first screen, ‘Configure Language and Database,’ set “Database Connection”.
2. Set “Database Type” to MySQL 5.7+.
3. The fields should be filled out according to the database connection fields section.
4. Test whether your connection works and save.

 b. Configuration tool

You can use the configuration tool if you already have an existing Jira instance. The settings will be saved in the dbconfig.xml file in your Jira home directory.

1. Run the Jira configuration tool as follows:
   • Windows: In the command prompt, run config.bat in the bin sub-directory of the Jira installation directory.
   • Linux/Unix: In a console, execute config.sh in the bin sub-directory of the Jira installation directory.
2. Go to the “Database tab” and specify “Database type” as MySQL 5.7+.
3. The fields should be filled out according to the database connection fields section.
4. Test whether your connection works and save.
5. Restart Jira.

Your Jira account is now configured to connect to your MySQL Database. The next step is just to start it up!

Limitations of Using JDBC Connector 

1. Complex Configuration: Setting up the JDBC connector requires technical expertise and can be time-consuming.
2. Performance Issues: Transferring large datasets may result in slow performance or timeouts.
3. Lack of Automation: JDBC connectors don’t offer built-in features for scheduling or real-time data updates.
4. Error Handling: Limited capabilities to detect and resolve errors during data transfer processes.

You can take a look at how you can set up Looker Jira integration to easily analyze and visualize your Jira data.

Why Should You Move Your Data from Jira to MySQL?

1. Centralized Data Storage: Consolidate Jira data in MySQL for easier access and management alongside other business data.
2. Advanced Reporting: Use MySQL’s robust querying capabilities to create detailed reports and uncover insights.
3. Custom Integrations: Enable seamless integration with other tools and applications using MySQL as a common database.
4. Improved Performance: Offload data from Jira to MySQL for faster analytics and reduced load on Jira’s platform.
5. Scalability: Leverage MySQL’s ability to handle large datasets as your data grows over time.

Conclusion

This article introduced you to Jira and MySQL and provided a step-by-step explanation of the two methods using which you can move your Jira data to MySQL. The blog highlighted the limitations of the JDBC method. 

Hevo Data, on the other hand, helps you directly transfer data from a source like Jira to  MySQL or a Data Warehouse/Destination of your choice for free in a fully automated and secure manner without having to write the code repeatedly. 

Want to take Hevo for a spin? Sign up for the 14-day free trial and experience the feature-rich Hevo suite firsthand. You can also have a look at our unbeatable pricing that will help you choose the right plan for your business needs!

FAQs