Documentation for JIRA 6.3 EAP developer (EAP) releases only. Not using this? See below:
(JIRA 6.2.x documentation | JIRA OnDemand documentation | earlier versions of JIRA)

Skip to end of metadata
Go to start of metadata

These instructions will help you connect JIRA to a MySQL database. A version of these instructions specific to Linux and JIRA is available.

Before you begin

  • Check whether your version of MySQL is supported. See Supported Platforms.
  • 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, please read the Confluence MySQL setup guide and configure your MySQL server to suit Confluence as well as JIRA. Note that the 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.

    (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:

    Tip:

    Icon

    To confirm if the permissions were granted successfully, log into the DB server with the JIRA DB user and run the command below:

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 Connector/J JDBC driver v5.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 three ways to configure your JIRA server to connect to your MySQL database:

  • Using the JIRA setup wizard (not applicable to JIRA WAR) — 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 (not applicable to JIRA WAR) — 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.
  • Manually — Only use this method if you have a JIRA WAR instance or you have a console-only connection to your JIRA server. You will be required to manually update 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. 
 

Manually

  1. Locate 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 (shown below) into this file.
  2. Update the file, as described in the Database connection fields section below. Escape any '&' characters by adding 'amp;' to the end of each one.
    • Note, the <database-type/> element must specify your type of database, e.g. mysql. If you forget to do this and you start JIRA, your database tables may be created incorrectly. See Incorrect database type specified if this happens to you.
  3. Save the file and 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=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=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=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. See storing non-ASCII characters in MySQL for details.
  • The database URL in the example below contains the sessionVariables=storage_engine=InnoDB parameter. We strongly recommend adding this parameter to avoid data corruption. See the Installation Notes section below for more information.

4. Start JIRA

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

  • 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.

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

Installation notes

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

Known issues and troubleshooting

  • Hostnames in permissions are compared as strings - If you grant permission in MySQL to a hostname such as localhost then you must use the same string for the connecting to the database from JIRA. So using 127.0.0.1 won't work even though it resolves to the same place. This mistake produces warnings about not finding tables because the JDBC connection did not have permission to create the new tables when JIRA was set up.
  • Connection closures — 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,
  • Special characters for database password — JIRA is not able to interpret special characters for database password. Please refer to additional hints about setting password for database here.
  • Using the InnoDB storage engine — 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. We strongly recommend specifying the sessionVariables=storage_engine=InnoDB parameter in your database URL (as stated above). Doing so ensures that tables written to JIRA's MySQL database will use the InnoDB storage engine, which supports 'database transactions' required by JIRA.

  • Binary logging — 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.

  • 4 byte characters   Please note that JIRA does not support using MySQL with 4 byte characters.

23 Comments

  1. Anonymous

    I have windows 7 and used MySQL 5.1.50-win64.  I found that if you ran config.bat from the command prompt, you need to run the command prompt as administrator, otherwise you get all sorts of errors.  My user is an admin user so that wasn't enough.

  2. Anonymous

    This is missing the all-important line that's missing from entityengine.xml:

    <field-type name="mysql" loader="maincp" location="entitydefs/fieldtype-mysql.xml"/>

    (well, at least it was in our installation).

    Without this line, you get a message in the logfile along the lines of not being able to find a field-type description with name of "mysql".

  3. Anonymous

    I suggest to do the necessary changes in the conf/server.xml file instead of the dbconfig.xml, as it would never pick the latter one.

    Now it is all in one place, and works a treat.

    1. Hi there,

      Just letting you know (in case you weren't aware) that our documentation is 'versioned' (in line with major product versions).

      If you're using JIRA 4.4.x, use the procedure documented on this page. If you're using JIRA 4.3.x or earlier, use this procedure instead.

      Cheers,
      Giles.

  4. Anonymous

    Are these instructions still valid as of JIRA 4.4.1? It seems that the Setup Wizard (http://confluence.atlassian.com/display/JIRA/Running+the+Setup+Wizard) is already configuring JIRA to use MySQL.

    1. Hello there,

      These instructions were updated for the release of JIRA 4.4.1. However, if you noticed a discrepancy somewhere (or something that requires correction or clarification) in the documentation above, please do let us know so that we can make any appropriate amendments to the content.

      Kind regards,

      Giles.

      P.S. It might also help if you provide some background on your JIRA setup/scenario as it may assist us in finding out how your JIRA installation was already configured to use MySQL.

  5. You can check the current values in mysql very easily with these commands.

    mysql>  use [insertdbname];

    mysql>  show variables like "collation_database";

    mysql>  show variables like "character_set_database";

    I think you would need to delete your database and resetup JIRA to recreate your database with the correct encoding if the values aren't correct. 

  6. Hi All , 

    How do we manually upgrade mysql database backup file in JIRA 4.0 to JIRA 4.4 .

    if i took database back up of JIRA 4.0 and want to restore on database JIRA 4.4  then out of memory error coming during deployment in jboss. Is any manually procedure to upgrade database of JIRA 4.0  to JIRA 4.4 .

     

     

     

  7. Anonymous

    How can you configure a second database connection. 

    To give you some background, we have a plugin that will check our second database, which basically consists of user's info, and then will automatically fill in the assignee's, developers, manager, etc. for that issue based on certain information provided when creating the issue.

    As of now we can only get our JIRA database connected and not the second database. I've searched around for some documentation, but haven't had any luck. If you could point me in the right direction that would be great!

    1. Hello there,

      I haven't heard of anyone implementing such a database configuration with JIRA like this before.

      However, I'd recommend that you post specific questions such as these in our Atlassian Answers site via the Get Answers link below, as you're more likely to get greater exposure of such questions to a wider community of JIRA customers/admins/users and potentially obtain a faster responses.

      Kind regards,

      Giles.

  8. Is is explicitly required to set the default transaction isolation MySQL variable on the server to be READ-COMMITTED?

    The additional notes section above suggests Jira uses that specific tx isolation level, but it's not clear if it's been assumed the server has been configured that way, or if Jira specifically creates transactions at that isolation level via JDBC itself.

    If it is required, then it should probably be stated at the start of this document, rather than the end.

  9. I am getting the following error when installing JIRA on Amazon EC2. 

     


    atlassian.standalone : JIRA
    catalina.base : /opt/atlassian/jira
    catalina.home : /opt/atlassian/jira
    catalina.useNaming : true
    common.loader : ${catalina.base}/lib,
    ${catalina.base}/lib/*.jar,
    ${catalina.home}/lib,
    ${catalina.home}/lib/*.jar
    file.encoding : UTF-8
    file.encoding.pkg : sun.io
    java.awt.graphicsenv : sun.awt.X11GraphicsEnvironment
    java.awt.headless : true
    java.awt.printerjob : sun.print.PSPrinterJob
    java.class.version : 50.0
    java.home : /opt/atlassian/jira/jre
    java.io.tmpdir : /opt/atlassian/jira/temp
    java.naming.factory.initial : org.apache.naming.java.javaURLContextFactory
    java.naming.factory.url.pkgs : org.apache.naming
    java.runtime.name : Java(TM) SE Runtime Environment
    java.runtime.version : 1.6.0_26-b03
    java.specification.name : Java Platform API Specification
    java.specification.vendor : Sun Microsystems Inc.
    java.specification.version : 1.6
    java.util.logging.config.file : /opt/atlassian/jira/conf/logging.properties
    java.util.logging.manager : org.apache.juli.ClassLoaderLogManager
    java.vendor : Sun Microsystems Inc.
    java.vendor.url : http://java.sun.com/
    java.vendor.url.bug : http://java.sun.com/cgi-bin/bugreport.cgi
    java.version : 1.6.0_26
    java.vm.info : mixed mode
    java.vm.name : Java HotSpot(TM) 64-Bit Server VM
    java.vm.specification.name : Java Virtual Machine Specification
    java.vm.specification.vendor : Sun Microsystems Inc.
    java.vm.specification.version : 1.0
    java.vm.vendor : Sun Microsystems Inc.
    java.vm.version : 20.1-b02
    mail.mime.decodeparameters : true
    org.apache.jasper.runtime.BodyContentImpl.LIMIT_BUFFER : true
    os.arch : amd64
    os.name : Linux
    os.version : 2.6.32-316-ec2
    package.access : sun.,
    org.apache.catalina.,
    org.apache.coyote.,
    org.apache.tomcat.,
    org.apache.jasper.,
    sun.beans.
    package.definition : sun.,
    java.,
    org.apache.catalina.,
    org.apache.coyote.,
    org.apache.tomcat.,
    org.apache.jasper.
    server.loader :
    shared.loader :
    sun.arch.data.model : 64
    sun.boot.library.path : /opt/atlassian/jira/jre/lib/amd64
    sun.cpu.endian : little
    sun.cpu.isalist :
    sun.io.unicode.encoding : UnicodeLittle
    sun.java.command : org.apache.catalina.startup.Bootstrap start
    sun.java.launcher : SUN_STANDARD
    sun.jnu.encoding : UTF-8
    sun.management.compiler : HotSpot 64-Bit Tiered Compilers
    sun.os.patch.level : unknown
    tomcat.util.buf.StringCache.byte.enabled : true
    user.country : US
    user.dir : /opt/atlassian/jira/bin
    user.home : /home/jira
    user.language : en
    user.name : jira
    user.timezone : Etc/UTC

    2012-06-24 16:09:42,799 main INFO [atlassian.jira.startup.JiraStartupLogger] Running JIRA startup checks.
    2012-06-24 16:09:42,839 main INFO [atlassian.jira.startup.JiraHomeStartupCheck] The jira.home directory '/var/atlassian/application-data/jira' is validated and locked for exclusive use by this instance.
    2012-06-24 16:09:42,840 main INFO [atlassian.jira.startup.JiraStartupLogger] JIRA pre-database startup checks completed successfully.
    2012-06-24 16:09:43,122 main INFO [jira.config.database.SystemTenantDatabaseConfigurationLoader] Reading database configuration from /var/atlassian/application-data/jira/dbconfig.xml
    2012-06-24 16:09:43,203 main INFO [jira.config.database.DatabaseConfigurationManagerImpl] Now running Database Checklist Launcher
    2012-06-24 16:09:43,615 main INFO [atlassian.jira.startup.DatabaseChecklistLauncher] JIRA database startup checks completed successfully.
    2012-06-24 16:09:43,615 main INFO [jira.config.database.DatabaseConfigurationManagerImpl] Now running Post database-configuration launchers
    2012-06-24 16:09:45,228 main INFO [jira.config.database.SystemTenantDatabaseConfigurationLoader] Reading database configuration from /var/atlassian/application-data/jira/dbconfig.xml
    2012-06-24 16:09:46,161 main WARN [core.entity.jdbc.DatabaseUtil] Entity "Action" has no table in the database
    2012-06-24 16:09:46,169 main ERROR [core.entity.jdbc.DatabaseUtil] Could not create table "jiraaction"
    2012-06-24 16:09:46,169 main ERROR [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
    CREATE TABLE jiraaction (ID DECIMAL(18,0) NOT NULL, issueid DECIMAL(18,0), AUTHOR VARCHAR(255), actiontype VARCHAR(255), actionlevel VARCHAR(255), rolelevel DECIMAL(18,0), actionbody LONGTEXT, CREATED DATETIME, UPDATEAUTHOR VARCHAR(255), UPDATED DATETIME, actionnum DECIMAL(18,0), CONSTRAINT PK_jiraaction PRIMARY KEY (ID))
    Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: CREATE command denied to user 'jiradbuser'@'localhost' for table 'jiraaction'
    2012-06-24 16:09:46,169 main WARN [core.entity.jdbc.DatabaseUtil] Entity "Application" has no table in the database
    2012-06-24 16:09:46,170 main ERROR [core.entity.jdbc.DatabaseUtil] Could not create table "cwd_application"
    2012-06-24 16:09:46,170 main ERROR [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
    CREATE TABLE cwd_application (ID DECIMAL(18,0) NOT NULL, application_name VARCHAR(255), lower_application_name VARCHAR(255), created_date DATETIME, updated_date DATETIME, active DECIMAL(9,0), description VARCHAR(255), application_type VARCHAR(255), credential VARCHAR(255), CONSTRAINT PK_cwd_application PRIMARY KEY (ID))
    Error was: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: CREATE command denied to user 'jiradbuser'@'localhost' for table 'cwd_application'
    2012-06-24 16:09:46,171 main WARN [core.entity.jdbc.DatabaseUtil] Entity "Avatar" has no table in the database
    2012-06-24 16:09:46,171 main ERROR [core.entity.jdbc.DatabaseUtil] Could not create table "avatar"
    2012-06-24 16:09:46,171 main ERROR [core.entity.jdbc.DatabaseUtil] SQL Exception while executing the following:
    CREATE TABLE avatar (ID DECIMAL(18,0) NOT NULL, filename VARCHAR(255), contenttype VARCHAR(255), avatartype VARCHAR(60), owner VARCHAR(255), systemavatar DECIMAL(9,0), CONSTRAINT PK_avatar PRIMARY KEY (ID))

    1. Hi Vinod,

      Could you please submit customer support queries to our support site by clicking the Get Support button below.

      Alternatively, click the Get Answers button below to post your query in Atlassian Answers, where someone in the community might be able to provide you with a helpful response.

      Comments in the documentation are best reserved for queries which relate to the documentation itself.

      Cheers,

      Giles.

  10. Anonymous

    The following is written in step 5: "If you are using the JIRA WAR distribution, rebuild and redeploy the webapp in your application server."

    How do I redeploy the webapp in my application server?

  11. Anonymous

    The following is written in step 5: "If you are using the JIRA WAR distribution, rebuild and redeploy the webapp in your application server."

    How do I redeploy the webapp in my application server?

  12. In your example in point "4.2 Connecting JIRA to MySQL Manually" I think you should add "autoReconnect=true" in jdbc connection string.
    Lack of this option causes Jira crashes because connections are removed from connection pool and finally Jira has zero working connections.

  13. Anonymous

    It would help I think if you wrote some instructions for the GUI installer for MySQL.

  14. I just installed JIRA on Amazon EC2. I used the .bin installation file to install JIRA and it auto started the JIRA service.

    I want to use mysql, so I followed the steps outlined here, however, at step 3, it says restart the service. The whole documentation does not mention anywhere the actual command to use to restart JIRA service. 

    This is so annoying. Please let us know the command to restart JIRA which is running as a service

  15. I originally set up JIRA to use its own instance of memory db. Today I followed the steps to convert to MySQL but after I restarted JIRA it asked me all the same setup steps as originally, and now my JIRA instance has no data...no projects, issues or even users. 

    Where is the data stored if you use the in-memory option on install, and what format (SQLite etc).

    Can I get that data back?

    1. Hi there,

      It seems that you've changed over the MySQL without exporting the data in your HSQL database first, so the MySQL database is empty. There's a few options you've got:

      1) You could simply change back to the HSQL database and hope that your data is still there, then export that to .xml, reconnect the MySQL database, and import the .xml backup.

      2) You can simply check the folder (JRA_HOME) /export and see if there's any automated backups sitting there for you to import into your MySQL database through JIRA.

      Please try those but we cannot offer support through these comments however, if those options do not work for you please open an issue up at https://support.atlassian.com

  16. Section 2.0 error?  I thought in recent versions of JIRA the upgrade task/wizard would not automatically copy over your existing driver to the upgraded installation any more due to licensing restrictions. 

  17. The tip no 3.: "SHOW GRANTS FOR <JIRADB>@<JIRA_SERVER_HOSTNAME>;" is incorrect, it should read:


    SHOW GRANTS FOR <USERNAME>@<JIRA_SERVER_HOSTNAME>;

  18. Moreover, on Linux (RHEL6), the dbconfig.xml file is created as belonging to root.root with 0644 (readable by others). This  file containts the jiradbuser username/password, so the permissions must be fixed:

    chown jira.jira /var/atlassian/application-data/jira/dbconfig.xml
    chmod o-rwx /var/atlassian/application-data/jira/dbconfig.xml