JIRA to MySQL: 2 Easy Methods

Are you looking to move data from JIRA to MySQL? MySQL is one of the most popular and used Relational Databases. It provides a wide range of features that makes storage and analysis of data easy, efficient, and secure. It can thus be 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 2 methods using which you can transfer your data from JIRA to MySQL. It will also discuss the limitations and benefits of these methods. Read along to decide which method suits you more!

Prerequisites

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

Introduction to JIRA

JIRA has been developed by the Australian company Atlassian. It is part of a suite of products that help teams of various types manage their work and collaborate. JIRA was originally designed to track bugs and issues. The JIRA dashboard provides a wide range of useful features and operations which make handling of issues easy. However, today, JIRA has become more than a bug tracker, it has turned into a powerful work management tool for a variety of use cases, from requirements and test case management to agile software development.

JIRA also helps you ship your products with confidence because you always have up-to-date information. It also enables you to select a suitable workflow or design a custom workflow for your project.

To learn more about JIRA, visit here.

Introduction to MySQL

MySQL is widely regarded as one of the most used Relational Database Management Systems (DBMS). It uses a simple client-server model to simplify the task of managing Relational Databases for users. The language, Structured Query Language (SQL), enables users to perform any required CRUD operations (create, read, update, delete).

Although MySQL is open source and free for everyone, it contains some additional features that Oracle only provides to customers willing to pay. Despite the fierce competition in the database market today, MySQL is still regarded as the preferred database by more than 5,000 companies, including Uber, Netflix, Pinterest, Amazon, Airbnb, Twitter, etc.

To learn more about MySQL, visit here.

Methods to Set Up JIRA to MySQL Integration

The JIRA to MySQL Integration can be set up by the following 2 methods:

• Method 1: Using JDBC Connector to Set Up JIRA to MySQL Integration
• Method 2: Using Hevo Data to Set Up JIRA to MySQL Integration

Method 1: Using JDBC Connector to Set Up JIRA to MySQL Integration

The following steps must be followed to move data from JIRA to MySQL:

• Step 1: Create and Configure your MySQL Database
• Step 2: Copy the MySQL JDBC Driver
• Step 3: Configure Jira to Connect to the Database

Step 1: Create and Configure your MySQL Database

1. In the first step, you will be creating a database user which JIRA will connect as, for example, jiradbuser.
   
2. In the next step, you will create a database in which Jira will be able to store issues, for example, jiradb.
   
   It is necessary for the database to have a character set of UTF8. In order 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 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, to 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: Copy the MySQL JDBC Driver

1. Download the suitable MySQL driver JDBC Connector/J 5.1 for your Operating System from here.
   
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, visit here.

Step 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 to My own database.
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

If you already have an existing Jira instance then you can use the configuration tool. 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 to just start it up!

Method 2: Using Hevo Data to Set Up JIRA to MySQL Integration

Hevo Data is a No-code Data Pipeline that offers a fully-managed solution to set up data integration for JIRA, MySQL, and 100+ Data Sources (including 40+ Free Data Sources) and will let you directly load data from JIRA to MySQL or a Data Warehouse/Destination of your choice. Hevo Data is fully managed and completely automates the process of not only loading data from your desired source but also enriching the data and transforming it into an analysis-ready form without having to write a single line of code. Its fault-tolerant architecture ensures that the data is handled in a secure, consistent manner with zero data loss. Hevo Data will efficiently automate the process of setting up your JIRA to MySQL connection.

Sign up Here for a 14-day Free Trial

Hevo Data takes care of all your data preprocessing needs and lets you focus on key business activities, and draw a much powerful insight on how to generate more leads, retain customers, and take your business to new heights of profitability. It provides a consistent & reliable solution to manage data in real-time and always have analysis-ready data in your desired destination. You can transfer your data from JIRA to MySQL by using the following 2 steps:

• Configure JIRA as Data Source: You need to log in to your Hevo account and authenticate JIRA as your data source. This will connect Hevo to your JIRA account as shown in the image below.

To learn more about connecting JIRA to Hevo, visit here.

• Configure MySQL as Destination: You need to set your MySQL account as the destination. This will connect it to Hevo as shown in the below image.

To learn more about connecting MySQL to Hevo, visit here.

In these 2 simple steps, Hevo Data will set up your JIRA to MySQL connection. It is as simple as that!

Check out what makes Hevo amazing:

• Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
• Connectors: Hevo supports 100+ Integrations to SaaS platforms like Jira, Files, Databases, BI tools, and Native REST API & Webhooks Connectors. It supports various destinations including Google BigQuery, Amazon Redshift, Snowflake, Firebolt, Data Warehouses; Amazon S3 Data Lakes; Databricks; and MySQL, SQL Server, TokuDB, DynamoDB, PostgreSQL Databases to name a few.  
• Real-Time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
• 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
• Scalable Infrastructure: Hevo has in-built integrations for 100+ sources (Including 40+ Free Sources like Jira) that can help you scale your data infrastructure as required.
• 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to you through chat, email, and support calls.
• Schema Management: Hevo takes away the tedious task of schema management & automatically detects the schema of incoming data and maps it to the destination schema.
• Live Monitoring: Hevo allows you to monitor the data flow so you can check where your data is at a particular point in time.

Conclusion

This article introduced you to JIRA and MySQL and also provided a step-by-step explanation of the 2 methods using which you can move data from JIRA to MySQL. The blog all discussed 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. Hevo Data with its strong integration with Jira, MySQL, and 100+ data sources(Including 40+ Free Sources like Jira), allows you to not only export & load data but also transform & enrich your data & make it analysis-ready in a jiffy.

Visit our Website to Explore Hevo

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

Have any further questions? Get in touch with us in the comments section below.