Configuring database connection results in Error: Host 'xxxxxxx' is not allowed to connect to this MySQL server

Summary

While trying to connect to the database during installation of Jira you receive an error.

Steps To Reproduce

1. Start installation of Jira, on a different server than the one which hosts your database.

2. In the configuration screen for the database connection, use the "root" username and password.

The following appears on the screen:

Diagnosis

This error only occurs while using MySQL Database Server (All Supported Versions)

Cause

This error occurs due to the default configuration your MySQL database is currently using. This configuration allows connections only from the 'root' user when coming from 'localhost' and not other IP address ranges.

Solution

Workaround

The following is a workaround for the above situation in the case that you must use the "root" user, however it is not recommended as this creates a security vulnerability. For the suggested fix for this issue please see Resolution below.

• Open your MySQL Terminal;

  • In Linux:

    1 mysql -u root -p

  • In Windows, open the MySQL command line.

• Run the following queries:

  1 2 USE mysql; SELECT user,host FROM user;

  Note: You will see that the "root" user is only related to the "localhost" host

• Once it's verified that the root user only has permission to connect in localhost, run the following query:

  1 GRANT ALL PRIVILEGES ON *.* TO root@my_ip IDENTIFIED BY ‘root_password‘ WITH GRANT OPTION;

  Where "my_ip" is your JIRA server ip and "root_password" is the root user password.

• If you want to, run the first query again to verify that the root user has the connect permission with JIRA's server IP;

Resolution

Please set up a new user and database per the following recommended instructions. These instructions come directly from our official documentation: Connecting Jira applications to a database.

• Create a database user which Jira will connect as, for example jiradbuser.

  1 CREATE USER '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>' IDENTIFIED BY '<PASSWORD>';

• Create a database for Jira to store issues in, for example jiradb. The database must have a character set of UTF8. To set it, enter the following command from within the MySQL command client:

  1 CREATE DATABASE jiradb CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;

• Make sure the user has permission to connect to the database, and permission to create and populate tables. You can provide these permissions with the following commands.

  1 2 GRANT SELECT,INSERT,UPDATE,DELETE,CREATE,DROP,REFERENCES,ALTER,INDEX on <JIRADB>.* TO '<USERNAME>'@'<JIRA_SERVER_HOSTNAME>'; 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: