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 3 · 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

score 3 · Answer 2 · answered Jun 28 '21 at 07:18

3

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

answered Jun 28 '21 at 07:18

Vidurajith Darshana

388
5
15

MySQL remote database server connect failed

2 Answers2