Setting Up a MySQL Database on Linux for JIRA

The latest official documentation on configuring JIRA with MySQL can be found in the Connecting JIRA to MySQL guide.

This is a step-by-step supplement guide for setting up your MySQL database for JIRA. Although this guide assumes that your MySQL database server is running on Linux, the various procedures described below can be adapted (or may be applicable) to other similar operating systems.

Enable MySQL TCP/IP networking

Some Linux distributions (eg. Debian) disable MySQL's TCP/IP networking as a security precaution. You can test that MySQL is listening on the default port (3306) as follows:

jturner@teacup:~$ netstat -na | grep 3306
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN
tcp        0      0 127.0.0.1:48211         127.0.0.1:3306          TIME_WAIT
tcp6       1      0 ::ffff:127.0.0.1:34785  ::ffff:127.0.0.1:3306   CLOSE_WAIT

Or if netstat isn't available:

jturner@teacup:~$ telnet localhost 3306
Trying 127.0.0.1...
Connected to localhost.localdomain.
Escape character is '^]'.
D
5.0.13-rc-Debian_1-lo!X{$:;V#H!ju (press ctrl-] here)
telnet> quit
Connection closed.

On Debian, you can enable MySQL TCP connections by editing /etc/my.cnf, commenting out the 'skip-networking' flag, and restarting mysqld.

Create MySQL database and user

Create a MySQL user called 'jirauser' and database called 'jiradb':

jturner@teacup:~$ mysql --user=root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 559 to server version: 5.0.13-rc-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE USER 'jirauser'@'localhost' IDENTIFIED BY '<ChangeThisPassword>';
mysql> CREATE DATABASE jiradb CHARACTER SET utf8 COLLATE utf8_bin;
Query OK, 1 row affected (0.02 sec)

mysql> GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,ALTER,INDEX on
  jiradb.* TO 'jirauser'@'localhost' IDENTIFIED BY 'mypassword';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> quit
Bye

The 'IDENTIFIED BY' phrase sets the password for the user (in this case, 'mypassword'). Your hostname may be different; you will find out in the next steps.

Now verify that user 'jirauser' can connect:

jturner@teacup:~$ mysql \--user=jirauser \--password=mypassword \--database=jiradb
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 565 to server version: 5.0.13-rc-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

If you get errors like:

Access denied for user 'jirauser'@'localhost' (using password: YES)

You will need to adjust the 'host' field for the JIRA user record:

jturner@teacup:~$ mysql --user=root -p mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 655 to server version: 5.0.13-rc-Debian_1-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select user, host from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| debian-sys-maint | localhost |
| jirauser         | localhost |
| root             | localhost |
| root             | teacup    |
+------------------+-----------+
4 rows in set (0.00 sec)

mysql> update user set host='localhost.localdomain' where user='jirauser';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)

Tip: make it so that only one production server can connect to the database, so that when you later bring up a clone of the production server as a staging server, you will be protected from having two JIRA instances using the same database.

See also Atlassian's MySQL Tips.

If problems persist, see the MySQL Causes of Access Denied Errors page.

For more general information, see Adding New User Accounts to MySQL.

Start JIRA

Check for errors in the log files.

Again, if you see an 'Access denied' error:

Access denied for user 'jirauser'@'localhost.localdomain' (using password: YES)

Then you need to adjust your /etc/hosts so that 'localhost' comes before 'localhost.localdomain', and restart MySQL. This is a MySQL bug fixed in 5.0.11.

Run the Setup Wizard

Point a browser at http://localhost:8080/, and set up JIRA as described in the Setup Wizard.

Troubleshooting

Q: I get the following error message in MySQL, "Attempted reconnect 3 times. Giving up." What should I do?
A: MySQL error message

jdbc:mysql://localhost/test?autoReconnect=true connection error:
Server connection failure during transaction.
Attempted reconnect 3 times. Giving up.

To troubleshoot your MySQL connection, please follow the steps below:

  1. Enter the following command to connect to MySQL:

    mysql -p -u [dbuser] -h 127.0.0.1 [dbname]
    

    For example,

    mysql -p -u mydbuser -h 127.0.0.1 test
    
  2. If you cannot connect to MySQL after entering your password, login to your mysql with the root account:

    mysql -p -u root
    

    And enter following command:

    mysql> GRANT ALL PRIVILEGES ON <dbname>.* to <user>@127.0.0.1 identified by '<password>';
    mysql> FLUSH PRIVILEGES;
    

    where,
    <dbname> is your database name,
    <user> is your database user name,
    <password> is your database password.
    Do not forget the last command: 'FLUSH PRIVILEGES'

  3. If you still cannot connect, please check that your MySQL is listening on the default port of 3306 and bind in your IP, 127.0.0.1by running either of the following commands:

    netstat -a |grep mysql
    

    or,

    netstat -a |grep 3306
    

    If MySQL is listening, you should see the following message:

    tcp 0 0 *:mysql *:* LISTEN
    

    Alternatively, you also could check if your MySQL is listening on the default port by running this command:

    telnet 127.0.0.1 3306
    
  4. If you can connect successfully from the command line but JIRA cannot connect it may because a non-default port is in use. Note that the {{mysql}} tool can connect to non-standard ports without you having to specify a port, so it isn't always a guarantee of connectivity.

Was this helpful?

Thanks for your feedback!

Why was this unhelpful?

Have a question about this article?

See questions about this article

Powered by Confluence and Scroll Viewport