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?

what about username,password and database ? are they all ok ? — Manohar Khadka, Oct 26 '16 at 05:57
I think in the host you should only give the IP ie, 192.168.1.19, not the protocol(https), that is specified by the port — Rohit Ailani, Oct 26 '16 at 06:01
I also checked without `'https` But nothing happened @RohitAilani — Sumon Sarker, Oct 26 '16 at 08:25
I fixed some issue, And updated the question. Current error `Error: SQLSTATE[HY000] [2002] No route to host` @ManoharKhadka — Sumon Sarker, Oct 26 '16 at 08:38
try connection using a test file in your webroot directory and do a direct mysql_connect there and check are you able to achieve that and if you are then please post that in the question, so that we can check further the exact issue. — Rohit Ailani, Oct 26 '16 at 08:47
I already checked, There is another error `PDOException' with message 'SQLSTATE[HY000] [2002] Connection refused`, Anyway Thank you for your advice. Maybe there is server issue. I am trying to fix it @RohitAilani — Sumon Sarker, Oct 26 '16 at 08:53
I solved the problem! There was `IP` and `mysql` user permission problem @RohitAilani — Sumon Sarker, Oct 26 '16 at 10:21

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

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

MySQL remote database server connect failed

1 Answers1