Explanation:

The mention exception occurred while granting privileges to the MySQL instance database.

Exception:

mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.1.14';
ERROR 1045 (28000): Access denied for user 'root'@'10.0.1.14' (using password: YES)

Solution:

Login to the data tier where MYSQL server --> Login to MYSQL --> Execute the following steps.

• Grant privileges to root@10.0.1.14 

        ubuntu@ip-10-0-2-227:~$ sudo mysql -u root -p

        mysql> select current_user();
        +----------------+
        | current_user() |
        +----------------+
        | root@localhost |
        +----------------+
        1 row in set (0.00 sec)

        mysql> SELECT host,user,Grant_priv,Super_priv FROM mysql.user where user = 'root';
        +-----------+------+------------+------------+
        | host | user | Grant_priv | Super_priv |
        +-----------+------+------------+------------+
        | localhost | root | Y | Y |
        | 10.0.1.14 | root | N | Y |
        +-----------+------+------------+------------+
        2 rows in set (0.00 sec)

        mysql> UPDATE mysql.user SET Grant_priv='Y', Super_priv='Y' WHERE User='root';
        Query OK, 1 row affected (0.00 sec)
        Rows matched: 2 Changed: 1 Warnings: 0

        mysql> SELECT host,user,Grant_priv,Super_priv FROM mysql.user where user = 'root';
        +-----------+------+------------+------------+
        | host | user | Grant_priv | Super_priv |
        +-----------+------+------------+------------+
        | localhost | root | Y | Y |
        | 10.0.1.14 | root | Y | Y |
        +-----------+------+------------+------------+
        2 rows in set (0.00 sec)

        mysql> FLUSH PRIVILEGES;
        Query OK, 0 rows affected (0.00 sec)

• Try again

        mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.1.14';

Explanation:

The mention exception occurred while connecting to MySQL database instance remotely.

Exception:

ubuntu@ip-10-0-1-14:~$ sudo mysql -u root -p -h 10.0.2.227
Enter password:
ERROR 1130 (HY000): Host '10.0.1.14' is not allowed to connect to this MySQL server

Solution:

Login to the data tier where MYSQL server --> Login to MYSQL --> Execute the following steps.

• Create and Grant all privileges to root@10.0.1.14  for remote login

        ubuntu@ip-10-0-2-227:~$ sudo mysql -u root -p

        mysql> SELECT host FROM mysql.user WHERE User = 'root';
        +-----------+
        | host |
        +-----------+
        | localhost |
        +-----------+
        1 row in set (0.00 sec)

        mysql> CREATE USER 'root'@'10.0.1.14' IDENTIFIED BY '*****';
        Query OK, 0 rows affected (0.00 sec)

        mysql> GRANT ALL PRIVILEGES ON *.* TO 'root'@'10.0.1.14';
        Query OK, 0 rows affected (0.00 sec)

        mysql> FLUSH PRIVILEGES;
        Query OK, 0 rows affected (0.00 sec)

        mysql> SELECT host FROM mysql.user WHERE User = 'root';
        +-----------+
        | host |
        +-----------+
        | 10.0.1.14 |
        | localhost |
        +-----------+
        2 rows in set (0.00 sec)

• Try again

         ubuntu@ip-10-0-1-14:~$ sudo mysql -u root -p -h 10.0.2.227

Explanation:

The mention exception occurred while connecting to MySQL database instance remotely.

Exception:

ubuntu@ip-10-0-1-14:~$ sudo mysql -u root -p -h 10.0.2.227
Enter password:
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.2.227' (111)

Solution:

Login to the data tier where MYSQL server --> Login to MYSQL --> Execute the following steps.

• Comment the bind-address       

        ubuntu@ip-10-0-2-227:~$ sudo mysql -u root -p
        ubuntu@ip-10-0-2-227:~$ more /etc/mysql/mysql.cnf

        !includedir /etc/mysql/conf.d/
        !includedir /etc/mysql/mysql.conf.d/

        Note: Search for bind-address = 127.0.0.1. in /etc/mysql/mysql.cnf. If not found, search for bind-address = 127.0.0.1 in the files under included directories(!includedir).

        ubuntu@ip-10-0-2-227:~$ sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf

        Comment the bind-address as follows
        # bind-address = 127.0.0.1

        Save the /etc/mysql/mysql.conf.d/mysqld.cnf file

• Restart mysql to load the recent configuration

        ubuntu@ip-10-0-2-227:~$ sudo systemctl restart mysql
        ubuntu@ip-10-0-2-227:~$ sudo systemctl status mysql
        ● mysql.service - MySQL Community Server
         Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: en
        Active: active (running) since Wed 2019-08-21 18:31:49 UTC; 13s ago
        Process: 15835 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/m
        Process: 15813 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=exi
        Main PID: 15837 (mysqld)
        Tasks: 27 (limit: 1152)
        CGroup: /system.slice/mysql.service
        ─15837 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pi

• Try again

         ubuntu@ip-10-0-1-14:~$ sudo mysql -u root -p -h 10.0.2.227