Get all JIRA Cloud data in MySQL

Arun Unnikrishnan_ul3s5t

I'm looking for a solution that would help me wo get all the JIRA Cloud data into my local MySQL.

Expectations

• It should periodically automatically refresh/update the data in the local database. 
• Able to run queries to get the required data
  • eg: Get the list of all automations along with the name of the person who created it and the name of the person who has last updated it. 

I would like to have an detailed step-by-step installation process as I have tried some of the already available stuff and failed. 

Not looking for paid plug-ins.

Thanks in advance.

Evgenii

Hi, @Arun Unnikrishnan 

Not sure about automations, but to achieve your goal of getting Jira Cloud data into your local MySQL database and periodically refreshing it, you can use the following approach:

1. Set up a Jira API Integration:
 - Log in to your Jira Cloud instance as an administrator.
- Go to "Settings" > "Apps" > "API tokens" (or similar) and generate an API token for authentication.

2. Install Python and Required Libraries:
- Install Python on your local machine if not already installed.
- Install the necessary libraries: `requests` and `mysql-connector-python`. You can use pip to install them:

pip install requests mysql-connector-python


3. Create a Python Script:
 - Write a Python script that uses the Jira REST API to fetch data from Jira Cloud and inserts it into your local MySQL database. Here's an example script to get you started:

python
import requests
import mysql.connector

# Jira Cloud API credentials
jira_url = 'https://your-jira-instance.atlassian.net'
api_token = 'your-api-token'
email = 'your-email@example.com'

# MySQL database connection details
db_host = 'localhost'
db_user = 'your-db-user'
db_password = 'your-db-password'
db_name = 'your-db-name'

# Connect to MySQL database
cnx = mysql.connector.connect(
      host=db_host,
      user=db_user,
      password=db_password,
      database=db_name
)
cursor = cnx.cursor()

# Fetch Jira data using REST API
def fetch_jira_data(api_endpoint):
headers = {
'Authorization': f'Bearer {api_token}',
'Accept': 'application/json'
}
response = requests.get(f'{jira_url}/rest/api/3/{api_endpoint}', headers=headers)
return response.json()

# Insert Jira data into MySQL database
def insert_data(table_name, data):
placeholders = ', '.join(['%s'] * len(data))
columns = ', '.join(data.keys())
query = f"INSERT INTO {table_name} ({columns}) VALUES ({placeholders})"
cursor.execute(query, list(data.values()))
cnx.commit()

# Close the database connection
cursor.close()
cnx.close()


4. Customize the Script:
- Modify the script to fetch the specific Jira data you need (e.g., automations) and map it to your local MySQL table structure.
- Update the Jira Cloud API credentials with your own values.
- Adjust the MySQL database connection details according to your setup.

5. Schedule Periodic Updates:
- Use a task scheduler or cron job on your local machine to run the Python script at regular intervals (e.g., daily, hourly) to keep the data in sync between Jira Cloud and your local MySQL database.

      This approach allows you to periodically fetch data from Jira Cloud using the REST API and store it in your local MySQL database for further querying and analysis. It provides flexibility in terms of selecting the specific data you need and customizing the mapping to your database schema.

Remember to handle error cases, implement proper error handling, and ensure the security of your API token and database credentials.

      Please note that this solution requires some programming knowledge and assumes you have access to install Python and libraries on your local machine.