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

Problem

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:

 Host '10.0.0.20' is not allowed to connect to this MySQL server

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.

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:

    mysql -u root -p

  • In Windows, open the MySQL command line.
• Run the following queries:

  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:

  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 to MySQL.

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

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

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

  CREATE DATABASE jiradb CHARACTER SET utf8 COLLATE utf8_bin;

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

Tip:

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

SHOW GRANTS FOR <USERNAME>@<JIRA_SERVER_HOSTNAME>;