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 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:
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
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
'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.1
by 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
- 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.