Connecting Jira applications to MySQL

These instructions will help you connect Jira to a supported MySQL database.

Before you begin

  • Check whether your version of MySQL is supported. See Supported platforms.
  • Check known issues below.
  • If you are migrating Jira to another server, create an export of your data as an XML backup. You will then be able to transfer data from your old database to your new database, as described in Switching databases.
  • If you plan to set up Confluence and Jira on the same MySQL server, read the Confluence MySQL setup guide. Confluence requirements are more strict than Jira's, so you should configure MySQL to suit Confluence. This configuration will work for Jira, too.
  • Shut down Jira before you begin, unless you are running the setup wizard.

On this page:

1. Create and configure the MySQL database

  1. Create a database user which Jira will connect as (e.g. jiradbuser).
    Remember this database user name, as it will be used to configure Jira's connection to this database in subsequent steps.
  2. Create a database for JIRA to store issues in (e.g. jiradb). The database must have a character set of UTF8. Enter the following command from within the MySQL command client.
    Remember this database name, as it will be used to configure Jira's connection to this database in subsequent steps.

    CREATE DATABASE jiradb CHARACTER SET utf8 COLLATE utf8_bin;

    (if you want your database to be named jiradb). 

  3. Ensure that the user has permission to connect to the database, and permission to create and populate tables. These can be provided with the following - 
    For MySQL 5.5, MySQL 5.6, and MySQL 5.7.0 to MySQL 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 above, you 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;

    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 <USERNAME>@<JIRA_SERVER_HOSTNAME>;
  4. Edit the my.cnf file (my.ini on Windows operating systems) in your MySQL server. (Refer to MySQL Option Files for detailed instructions on editing my.cnf and my.ini.)
    Locate the [mysqld]section in the file, and add or modify the following parameters:

    • Set the default storage engine to InnoDB:

      [mysqld]
      ...
      default-storage-engine=INNODB
      ...
    • Specify the value of max_allowed_packet to be at least 256M:

      [mysqld]
      ...
      max_allowed_packet=256M
      ...
    • Specify the value of innodb_log_file_size to be at least 256M for MySQL 5.5 and below:

      [mysqld]
      ...
      innodb_log_file_size=256M
      ...
      

      NB: This should be set to at least 2G for MySQL 5.6 and above.

    • Ensure the sql_mode parameter does not specify NO_AUTO_VALUE_ON_ZERO

      // remove this if it exists
      sql_mode = NO_AUTO_VALUE_ON_ZERO
  5. Restart your MySQL server for the changes to take effect:

    • On Windows, use the Windows Services manager to restart the service.
    • On Linux:
      • Run one of the following commands, depending on your setup: '/etc/init.d/mysqld stop' or '/etc/init.d/mysql stop' or 'service mysqld stop'.
      • Then run the same command again, replacing 'stop' with 'start'.

2. Copy the MySQL JDBC driver to your application server

If you are upgrading Jira and you are using the recommended MySQL driver (Connector/J JDBC driver v5.1), you can skip the instructions in this section. The Jira upgrade task will automatically copy over your existing driver to the upgraded installation.

To copy the MySQL JDBC driver to your application server:

  1. Get the MySQL driver:
    • If you are installing Jira, download the recommended MySQL driver JDBC Connector/J 5.1.
      You can download either the .tar.gz or the .zip file by selecting the 'Platform Independent' option. Extract the jar for the driver (e.g. mysql-connector-java-5.x.x-bin.jar) from the archive.
    • If you are upgrading Jira and you are not using the recommended MySQL driver (JDBC Connector/J 5.1), back up the driver from your Jira installation before you upgrade.
      The driver will be in the <Jira installation directory>/lib/ directory.
  2. Copy the MySQL JDBC driver jar to the <Jira installation directory>/lib/ directory for your new/upgraded installation. If you are installing Jira using the Windows installer, you will need to do this step after running the Windows installer, but before running the setup wizard.
  3. Restart Jira / Jira service.
  4. If you are installing Jira, skip the rest of the instructions on this page and access Jira in your browser to run the setup wizard instead.

Please note:

  • We recommend the Connector/J driver from MySQL (linked above). A user has reported experiencing problems with the Resin JDBC driver for MySQL.

3. Configure your Jira server to connect to your MySQL database

There are two ways to configure your JIRAJiraserver to connect to your MySQL database:

  • Using the Jira setup wizard — Use this method if you have just installed Jira, and are setting it up for the first time. Your settings will be saved to the dbconfig.xml file in your Jira home directory.
  • Using the JIRA configuration tool — Use this method, if you have an existing Jira instance. Your settings will be saved to the dbconfig.xml file in your JIRA home directory.

Instructions for each configuration method

Jira setup wizard

The Jira setup wizard will display when you access Jira for the first time in your browser.

  1. In the first screen, 'Configure Language and Database', set Database Connection to My own database.
  2. Set Database Type to MySQL.
  3. Fill out the fields, as described in the Database connection fields section below.
  4. Test your connection and save.

Jira configuration tool

  1. Run the Jira configuration tool as follows:
  2. Navigate to the Database tab and set Database type to MySQL.
  3. Fill out the fields, as described in the Database connection fields section below.
  4. Test your connection and save.
  5. Restart Jira.

Database connection fields

Setup wizard / Configuration tooldbconfig.xmlDescription
Hostname

Located in the <url> tag (bold text in example below):
<url>jdbc:mysql://dbserver:3306/jiradb?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=default_storage_engine=InnoDB</url>

If you use an IPv6 address, the URL needs to look like this:

<url>jdbc:mysql://address=(protocol=tcp)(host=dbserver)(port=3306)/jiradb?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=default_storage_engine=InnoDB</url>

The name or IP address of the machine that the MySQL server is installed on.
Port

Located in the <url> tag (bold text in example below):
<url>jdbc:mysql://dbserver:3306/jiradb?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=default_storage_engine=InnoDB</url>

The TCP/IP port that the MySQL server is listening on. You can leave this blank to use the default port.
Database

Located in the <url> tag (bold text in example below):
<url>jdbc:mysql://dbserver:3306/jiradb?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=default_storage_engine=InnoDB</url>

The name of your MySQL database (into which JIRA will save its data). You should have created this in Step 1 above.
Username

Located in the <username> tag (see bold text in example below):
<username>jiradbuser</username>

The user that JIRA uses to connect to the MySQL server. You should have created this in Step 1 above.
PasswordLocated in the <password> tag (see bold text in example below):
<password>jiradbuser</password>
The user's password — used to authenticate with the MySQL server.

Sample dbconfig.xml file

  • For more information about the child elements of <jdbc-datasource/> beginning with pool in the dbconfig.xml file above, see Tuning database connections.
  • Both the Jira setup wizard and database configuration tool also add the element <validation-query>select 1</validation-query> to this file, which is usually required when running JIRA with default MySQL installations. See Surviving connection closures for more information.
  • The database URL in the example below assumes a UTF-8 database — i.e. that your database was created using a command similar to create database jiradb character set utf8; If you do not specify character set utf8 when creating this database, you risk getting 'Data truncation: Data too long for column' errors when importing data or corruption of non-supported characters.
  • The database URL in the example below contains the sessionVariables=default_storage_engine=InnoDB parameter. We strongly recommend adding this parameter to avoid data corruption.
<?xml version="1.0" encoding="UTF-8"?>

<jira-database-config>
  <name>defaultDS</name>
  <delegator-name>default</delegator-name>
  <database-type>mysql</database-type>
  <jdbc-datasource>
    <url>jdbc:mysql://dbserver:3306/jiradb?useUnicode=true&amp;characterEncoding=UTF8&amp;sessionVariables=default_storage_engine=InnoDB</url>
    <driver-class>com.mysql.jdbc.Driver</driver-class>
    <username>jiradbuser</username>
    <password>password</password>
    <pool-min-size>20</pool-min-size>
    <pool-max-size>20</pool-max-size>
    <pool-max-wait>30000</pool-max-wait>
    <pool-max-idle>20</pool-max-idle>
    <pool-remove-abandoned>true</pool-remove-abandoned>
    <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
 
    <validation-query>select 1</validation-query>
    <min-evictable-idle-time-millis>60000</min-evictable-idle-time-millis>
    <time-between-eviction-runs-millis>300000</time-between-eviction-runs-millis>
  
    <pool-test-while-idle>true</pool-test-while-idle>
    <pool-test-on-borrow>false</pool-test-on-borrow>
    <validation-query-timeout>3</validation-query-timeout>
  </jdbc-datasource>
</jira-database-config>

4. Start Jira

You should now have Jira configured to connect to your MySQL database. The next step is to start it up!

(tick) Congratulations, you now have Jira connected to your MySQL database.

Known issues

Here's a list of known issues for this database. Expand each of them for more details.

Hostnames in permissions are compared as strings...

If you grant permissions in MySQL to a hostname such as localhost, then you'll need to use the same string when connecting to the database from Jira. Using 127.0.0.1 won't work, even though it resolves to the same place. This mistake will result in warnings about tables not being found, because the JDBC connection didn't have permissions to create the new tables when Jira was set up.

Connection problems...

If you are using a MySQL database with any of the following, you may experience problems with your connections dropping out (see JRA-15731 for details):

  • Jira 3.13 or later,
  • version 5.5.25 or higher of Tomcat 5,
  • version 6.0.13 or higher of Tomcat 6,

For more info on how to address this, see Surviving connection closures.

Special characters for database password are not supported...

Special characters for database password are not supported, because Jira can't interpret them.

Using the InnoDB storage engine...

The default storage engine used by MySQL Server versions prior to 5.5 is MyISAM. Because of that, a Jira database running on a default configuration of a MySQL Server earlier than version 5.5 could experience problems with creating tables (JRA-24124), which may result in data corruption in Jira.

To avoid this problem, we strongly recommend specifying the sessionVariables=default_storage_engine=InnoDB parameter in your database URL (as stated above). This will ensure that tables written to Jira's MySQL database use the InnoDB storage engine, which supports 'database transactions' required by Jira.

Binary logging...

Jira uses the READ-COMMITED transaction isolation level with MySQL, which currently supports only row-based binary logging.

If you require MySQL's binary logging features, you must configure MySQL's binary logging format to be 'row-based'. Otherwise, you may encounter problems when creating issues in Jira.

4-byte characters are not supported...

Jira doesn't support using MySQL with 4-byte characters.

Last modified on Jun 30, 2020

Was this helpful?

Yes
No
Provide feedback about this article
Powered by Confluence and Scroll Viewport.