MySQL remote database server connect failed

Question

I am trying to connect Cakephp 3.x remote database server from my local machine.

But it showing below error

Error: SQLSTATE[HY000] [2002] No route to host

My local database configuration in app.php is look like -

'Datasources' => [
    'default' => [
        'className' => 'Cake\Database\Connection',
        'driver' => 'Cake\Database\Driver\Mysql',
        'persistent' => false,
        'host' => '192.168.1.19',
        /**
         * CakePHP will use the default DB port based on the driver selected
         * MySQL on MAMP uses port 8889, MAMP users will want to uncomment
         * the following line and set the port accordingly
         */
        'port' => '3306',
        'username' => 'remote_db_user',
        'password' => 'my_password',
        'database' => '********',
        'encoding' => 'utf8',
        'timezone' => 'UTC',
        'flags' => [],
        'cacheMetadata' => true,
        'log' => false,
     ],
     /*Other configs*/
]

About the remote server :

• ping 192.168.1.19 is ok for this IP from my PC
• There is no name server for this IP address

How can I resolve this problem?

Answer 1

Finally I solved this problem by following some steps-

Step 1 (Edit mysql config file)

/etc/mysql/mysql.conf.d/mysqld.cnf

Find and changed below line

bind-address = 127.0.0.1

TO

bind-address = 0.0.0.0

Step 2 (Grant Database permission for User)

CREATE USER 'remote_db_user'@'%' IDENTIFIED BY 'my_password';

GRANT ALL PRIVILEGES ON *.* TO 'remote_db_user'@'%' WITH GRANT OPTION;

Step 3 (Restart Apache)

sudo service apache2 restart

Step 4 (Restart mysql)

sudo service mysql restart

And now, I am able connect remote database using CakePHP 3

Answer 2

Please check the firewall rules. for example, if your MySQL running port is 3306 check whether is it open or not. and please check this rule allowed too.

ufw allow mysql