Connecting JIRA to MySQL

2. 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:

   CREATE DATABASE jiradb CHARACTER SET utf8 COLLATE utf8_bin;

   (if you want your database to be named jiradb). Remember this database name, as it will be used to configure JIRA's connection to this database in subsequent steps.

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:

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

3. 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 Connector/J JDBC driver v5.1.
     You can download either the .tar.gz or the .zip file. 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.

4. Configure Your JIRA Server to Connect to Your MySQL Database

There are two ways to configure your JIRA server to connect to your MySQL database:

• Using the JIRA Configuration Tool (except JIRA WAR distributions) or
• Manually

Please Note:

• The JIRA Configuration Tool requires a Java platform to be installed and configured on your operating system. We recommend using a Java platform supported by JIRA — refer to JIRA Requirements for details.
• If you use the JIRA WAR distribution or have a console-only connection to your JIRA server, you will need to configure JIRA with MySQL manually.

4.1 Connecting JIRA to MySQL Using the JIRA Configuration Tool

The JIRA Configuration Tool is not available with JIRA WAR distributions.

• Windows: Open a command prompt and run config.bat in the bin sub-directory of the JIRA Installation Directory.
• Linux/Unix: Open a console and execute config.sh in the bin sub-directory of the JIRA Installation Directory.

To connect JIRA to MySQL using the JIRA Configuration Tool:

1. Click the Database tab.
2. From the Database type drop-down choose MySQL.
   The JIRA Configuration Tool will display your current database configuration settings if any are already set.
   jira-configuration-tool-mysql-config.png
3. Fill in the connection details for your MySQL database.
   
   • Hostname — The name or IP address of the machine that the MySQL server is installed on.
   • Port — The TCP/IP port that the MySQL server is listening on. You can leave this blank to use the default port.
   • Database — The name of your MySQL database (into which JIRA will save its data).
   • Username — The user that JIRA uses to connect to the MySQL server.
   • Password — The user's password to authenticate with the MySQL server.
4. After typing in your settings, click the Test Connection button to test the connection settings. The tool will attempt to connect to the database, and give a message with the results.
5. Click Save to save your settings when you are done.
   Please Note:
   
   • The JIRA Configuration Tool will save your database configuration to a dbconfig.xml file in your JIRA Home Directory. This tool also adds 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.
   • JIRA must be restarted for your new settings to take effect.

Congratulations — you have finished! Proceed to 'Next Steps' below.

4.2 Connecting JIRA to MySQL Manually

1. Edit the dbconfig.xml file at the root of your JIRA Home Directory.
   If this file does not exist, create the file, copy and paste the example XML code below into this file and edit the pasted XML as required.
   When editing your dbconfig.xml file, escape the '&' characters by adding 'amp;' to the end of each one, as shown in the <url/>element below:

   <?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=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>
       <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-max-idle>20</pool-max-idle>
       <pool-remove-abandoned>true</pool-remove-abandoned>
       <pool-remove-abandoned-timeout>300</pool-remove-abandoned-timeout>
       <pool-test-while-idle>true</pool-test-while-idle>
       <validation-query-timeout>3</validation-query-timeout>
     </jdbc-datasource>
   </jira-database-config>

   Please Note:

   
   
   • The database URL in the example dbconfig.xml file content above 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. See storing non-ASCII characters in MySQL for details.
   • The <validation-query>select 1</validation-query> element is usually required when configuring JIRA with default MySQL installations. See Surviving Connection Closures for more information.
   • Adding the sessionVariables=storage_engine=InnoDB parameter to the database URL is strongly recommended when configuring JIRA with MySQL, to avoid data corruption. See the Additional Notes section below for more information.
   For more information about the child elements of <jdbc-datasource/> beginning with pool in the dbconfig.xml file above, or any others not described in this topic, see Tuning Database Connections.
2. Save your edited dbconfig.xml file (at the root of your JIRA Home Directory).
   JIRA must be restarted for your new settings to take effect.

Proceed to 'Next Steps' below.

4.3. Additional Notes

The default storage engine used by MySQL Server versions prior to 5.5 is MyISAM. Hence, a JIRA database running on a default configuration of a MySQL Server earlier than version 5.5, could experience table creation problems (JRA-24124), which may result in data corruption in JIRA.

Hence, specifying the sessionVariables=storage_engine=InnoDB parameter in your database URL (by following the procedures above) is strongly recommended. Doing so ensures that tables written to JIRA's MySQL database will use the InnoDB storage engine, which supports 'database transactions' required by JIRA.

Be aware that JIRA uses the 'READ-COMMITTED' transaction isolation level with MySQL, which currently only supports row-based binary logging. If you require MySQL's binary logging features, you must configure MySQL's binary logging format to be 'row-based'. If not, you may encounter problems when creating issues in JIRA. For more information, please refer to JIRA Cannot Create Issues when Using MySQL with Binary Logging.

5. Next Steps

You should now have JIRA configured to connect to your MySQL database.

• If you are using a 'recommended' distribution of JIRA, start it up and watch the logs for any errors.
• If you are using the JIRA WAR distribution, rebuild and redeploy the webapp in your application server.

Installation Notes

See JIRA and MySQL. Please also take note of the following:

• Known Issues with MySQL:
  
  • 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). Please read Surviving Connection Closures for information on how to address this.
    
    • JIRA 3.13 or above,
    • version 5.5.25 or higher of Tomcat 5,
    • version 6.0.13 or higher of Tomcat 6,
  • If you experience any other issues with using JIRA and MySQL, please refer to our additional notes for more information.
  • JIRA is not able to interpret special characters for database password. Please refer to additional hints about setting password for database here.
  • Please note that JIRA does not support using MySQL with 4 byte characters. Please use PostgreSQL instead.